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

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

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
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;