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

annotate database/schema/admin.sql @ 55:40f64a96481f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Adjust database user arrangements and grant schema privs.
author David A. Holland
date Sun, 10 Apr 2022 19:36:29 -0400
parents 36d91dfe017f
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
43
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
1 --
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
2 -- Queue of administrative requests.
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
3 --
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
4 -- The following things appear in the admin queue:
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
5 -- * comments filed on locked PRs
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
6 -- * bounce mail (for feedback messages, for other messages)
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
7 -- * junk mail (unrecognized incoming mail)
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
8 --
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
9 -- These all are associated with incoming messages and refer to the
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
10 -- rawmail table. The pr and user fields are not null if we can figure
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
11 -- out what it's about, which we sometimes can but often can't.
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
12 --
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
13
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
14 CREATE TABLE adminmailtypes (
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
15 type text primary key,
54
36d91dfe017f use valid sql syntax, mostly from yetoo on freenode
David A. Holland
parents: 44
diff changeset
16 description text not null
43
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
17 )
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
18 WITHOUT OIDS;
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
19
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
20 -- this is not configurable as the logic for recognizing these is open-coded
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
21 -- XXX should probably use an enum type for this instead
44
812c956dd0e9 Add another admin queue type for comments on nonexistent PRs.
David A. Holland
parents: 43
diff changeset
22 INSERT INTO adminmailtypes VALUES ('locked', 'Comments on locked PRs');
812c956dd0e9 Add another admin queue type for comments on nonexistent PRs.
David A. Holland
parents: 43
diff changeset
23 INSERT INTO adminmailtypes VALUES ('nonexistent', 'Comments on nonexistent PRs');
812c956dd0e9 Add another admin queue type for comments on nonexistent PRs.
David A. Holland
parents: 43
diff changeset
24 INSERT INTO adminmailtypes VALUES ('fbounces', 'Feedback nag-mail bounces');
812c956dd0e9 Add another admin queue type for comments on nonexistent PRs.
David A. Holland
parents: 43
diff changeset
25 INSERT INTO adminmailtypes VALUES ('rbounces', 'Responsible nag-mail bounces');
812c956dd0e9 Add another admin queue type for comments on nonexistent PRs.
David A. Holland
parents: 43
diff changeset
26 INSERT INTO adminmailtypes VALUES ('bounces', 'Other bounces');
812c956dd0e9 Add another admin queue type for comments on nonexistent PRs.
David A. Holland
parents: 43
diff changeset
27 INSERT INTO adminmailtypes VALUES ('junk', 'Unrecognized mail traffic');
43
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
28
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
29 CREATE TABLE adminmailqueue (
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
30 rawmsg bigint not null references rawmail,
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
31 type text not null references adminmailtypes,
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
32 pr bigint null references PRs,
54
36d91dfe017f use valid sql syntax, mostly from yetoo on freenode
David A. Holland
parents: 44
diff changeset
33 userid bigint null references users
43
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
34 )
debc55088b4f add administrative queue
David A. Holland
parents:
diff changeset
35 WITHOUT OIDS;
55
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
36
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
37 -- adminmailtypes is fixed
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
38 -- adminmailqueue is append-only except to admins
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
39 GRANT SELECT, INSERT, UPDATE, DELETE ON adminmailqueue TO swallowtail_admin;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
40 GRANT INSERT ON attachments TO swallowtail_writer;