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

annotate database/schema/subscription.sql @ 14:dfd62aad74f4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
note that the test environment hasn't been merged into this tree yet. (from last Feb.)
author David A. Holland
date Mon, 03 Sep 2012 13:41:57 -0400
parents 68cc276ac118
children cd36b49f4437
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 -- subscription lists
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 -- Users subscribe to PRs to be notified when the PR is modified.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
5 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
6 -- If "batch" is set, notices and copies of messages are sent out
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
7 -- in batches.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
8 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
9 -- If "reporter" is set, the user is also nagged when the PR is in
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
10 -- feedback. If "responsible" is set, the user is nagged when the PR
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
11 -- is not in feedback.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
12 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
13 -- The original submitter is automatically subscribed as a reporter.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
14
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
15 CREATE TABLE subscriptions (
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
16 pr bigint not null references PRs (id),
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
17 user bigint not null references users (id),
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
18 batch boolean not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
19 reporter boolean not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
20 responsible boolean not null
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
21 )
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
22 WITHOUT OIDS;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
23
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
24 -- Intended constraints:
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
25 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
26 -- SELECT id, responsible FROM PRs, subscriptions, users
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
27 -- WHERE PRs.id = subscriptions.id
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
28 -- AND subscriptions.user = users.id
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
29 -- AND PRs.state <> 'closed'
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
30 -- AND subscriptions.responsible
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
31 -- AND NOT users.responsible
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
32 -- ;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
33 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
34 -- and
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
35 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
36 -- SELECT id, responsible FROM PRs, subscriptions, users
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
37 -- WHERE PRs.id = subscriptions.id
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
38 -- AND subscriptions.user = users.id
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
39 -- AND PRs.state = 'closed'
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
40 -- AND subscriptions.responsible
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
41 -- AND NOT users.oldresponsible
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
42 -- ;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
43 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
44 -- should always return nothing.