Mercurial > ~dholland > hg > swallowtail > index.cgi

annotate database/schema/messages.sql @ 40:44c1509055c7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
improvements in message handling
author David A. Holland
date Sun, 15 Jun 2014 17:54:11 -0400
parents edf081d0b282
children e1017d556437
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
1 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
2 -- Messages.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
3 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
4
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
5 CREATE SEQUENCE next_rawmsgid;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
6
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
7 -- all incoming mail in original form, for reference
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
8 -- (should be pruned periodically)
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
9 CREATE TABLE rawmail (
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
10 id bigint primary key default nextval('next_rawmsgid'),
40
44c1509055c7 improvements in message handling
David A. Holland
parents: 37
diff changeset
11 posttime timestamp not null,
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
12 data text not null
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
13 )
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
14 WITHOUT OIDS;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
15
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
16 CREATE SEQUENCE next_msgid;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
17
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
18 -- comments
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
19 CREATE TABLE messages (
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
20 id bigint primary key default nextval('next_msgid'),
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
21 pr bigint not null references prs (id),
40
44c1509055c7 improvements in message handling
David A. Holland
parents: 37
diff changeset
22 number_in_pr bigint not null,
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
23 who bigint not null references users (id),
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
24 parent_id bigint null references messages (id),
29
David A. Holland
parents: 13
diff changeset
25 posttime timestamp not null,
40
44c1509055c7 improvements in message handling
David A. Holland
parents: 37
diff changeset
26 mimetype text not null,
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
27 body text not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
28
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
29 -- we don't keep these directly, they go into an admin log entry
40
44c1509055c7 improvements in message handling
David A. Holland
parents: 37
diff changeset
30 -- XXX: we may need to keep the external message-id
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
31 --from_address text not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
32 --mail_subject text not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
33 --message_id text not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
34 rawid bigint null references rawmail (id)
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
35
13
5bf0d6f732c1 fix thinko.
David A. Holland
parents: 12
diff changeset
36 check (parent_id != id)
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
37 )
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
38 WITHOUT OIDS;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
39
40
44c1509055c7 improvements in message handling
David A. Holland
parents: 37
diff changeset
40 CREATE SEQUENCE next_attachid;
44c1509055c7 improvements in message handling
David A. Holland
parents: 37
diff changeset
41
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
42 -- for patches and mime-attachments
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
43 CREATE TABLE attachments (
40
44c1509055c7 improvements in message handling
David A. Holland
parents: 37
diff changeset
44 id bigint primary key default nextval('next_attachid'),
44c1509055c7 improvements in message handling
David A. Holland
parents: 37
diff changeset
45 number_in_pr bigint not null,
44c1509055c7 improvements in message handling
David A. Holland
parents: 37
diff changeset
46 msgid bigint not null references messages (id),
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
47 mimetype text not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
48 body text not null
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
49 )
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
50 WITHOUT OIDS;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
51