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

annotate database/schema/messages.sql @ 49:4b7f0ee35994

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use != in python and <> in sql.
author David A. Holland
date Sat, 02 Apr 2022 18:10:30 -0400
parents e1017d556437
children 36d91dfe017f
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
42
e1017d556437 add unique indexes on number_in_pr
David A. Holland
parents: 40
diff changeset
52 -- Create an index for number_in_pr both to enforce uniqueness and
e1017d556437 add unique indexes on number_in_pr
David A. Holland
parents: 40
diff changeset
53 -- to enable lookup.
e1017d556437 add unique indexes on number_in_pr
David A. Holland
parents: 40
diff changeset
54 CREATE UNIQUE INDEX ON messages (pr, number_in_pr);
e1017d556437 add unique indexes on number_in_pr
David A. Holland
parents: 40
diff changeset
55 -- XXX: does this work?
e1017d556437 add unique indexes on number_in_pr
David A. Holland
parents: 40
diff changeset
56 CREATE UNIQUE INDEX ON
e1017d556437 add unique indexes on number_in_pr
David A. Holland
parents: 40
diff changeset
57 SELECT messages.pr, attachments.number_in_pr
e1017d556437 add unique indexes on number_in_pr
David A. Holland
parents: 40
diff changeset
58 FROM messages, attachments
e1017d556437 add unique indexes on number_in_pr
David A. Holland
parents: 40
diff changeset
59 WHERE messages.id = attachments.msgid
e1017d556437 add unique indexes on number_in_pr
David A. Holland
parents: 40
diff changeset
60 ;