annotate database/schema/messages.sql @ 56:42d7888272a0 default tip

Implement fetch_classifications().
author David A. Holland
date Sun, 10 Apr 2022 19:37:18 -0400
parents 40f64a96481f
children
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 msgid bigint not null references messages (id),
54
36d91dfe017f use valid sql syntax, mostly from yetoo on freenode
David A. Holland
parents: 42
diff changeset
46 number_in_msg bigint not null,
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
54
36d91dfe017f use valid sql syntax, mostly from yetoo on freenode
David A. Holland
parents: 42
diff changeset
52 -- Create indexes for number_in_pr and number_in_msg both to enforce
36d91dfe017f use valid sql syntax, mostly from yetoo on freenode
David A. Holland
parents: 42
diff changeset
53 -- uniqueness and to enable lookup.
42
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);
54
36d91dfe017f use valid sql syntax, mostly from yetoo on freenode
David A. Holland
parents: 42
diff changeset
55 CREATE UNIQUE INDEX ON attachments (msgid, number_in_msg);
55
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
56
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
57 -- The raw mail queue is append-only except to admins.
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
58 GRANT SELECT, INSERT, UPDATE, DELETE ON rawmail TO swallowtail_admin;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
59 GRANT INSERT ON rawmail TO swallowtail_writer;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
60
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
61 -- Messages and attachments are generically accessible, but we don't
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
62 -- allow ordinary writers to modify them. (XXX: is that silly?)
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
63 GRANT SELECT, INSERT, UPDATE, DELETE ON messages TO swallowtail_admin;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
64 GRANT SELECT, INSERT, UPDATE, DELETE ON attachments TO swallowtail_admin;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
65 GRANT SELECT, INSERT ON messages TO swallowtail_writer;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
66 GRANT SELECT, INSERT ON attachments TO swallowtail_writer;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
67 GRANT SELECT ON messages, attachments TO swallowtail_reader;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
68 GRANT SELECT ON messages, attachments TO swallowtail_public;