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

annotate database/schema/bugs.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
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 -- PR data.
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 -- PRs is the primary table of bug info, with one row per problem
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
5 -- report.
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
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
8 CREATE SEQUENCE next_PR;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
9
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
10 CREATE TABLE PRs (
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
11 id bigint primary key default nextval('next_PR'),
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 -- basic description
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
14 synopsis text not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
15 confidential boolean not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
16
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
17 -- states
29
David A. Holland
parents: 12
diff changeset
18 state text not null references states (name),
10
1720f45dd495 Replace insane handling of states with something more likely workable.
David A. Holland
parents: 8
diff changeset
19 locked boolean not null, -- deny modifications
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
20
10
1720f45dd495 Replace insane handling of states with something more likely workable.
David A. Holland
parents: 8
diff changeset
21 -- intended constraint:
1720f45dd495 Replace insane handling of states with something more likely workable.
David A. Holland
parents: 8
diff changeset
22 -- select * from PRs, states where PRs.state = states.name
1720f45dd495 Replace insane handling of states with something more likely workable.
David A. Holland
parents: 8
diff changeset
23 -- and states.closed = false and PRs.locked = true
1720f45dd495 Replace insane handling of states with something more likely workable.
David A. Holland
parents: 8
diff changeset
24 -- should always return empty.
1720f45dd495 Replace insane handling of states with something more likely workable.
David A. Holland
parents: 8
diff changeset
25 -- (no PR should be locked unless it is closed)
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
26
38
68a4f2d8930e Add stuff for automatic feedback timeout.
David A. Holland
parents: 32
diff changeset
27 -- Timeouts cause bugs to automatically change state in the
68a4f2d8930e Add stuff for automatic feedback timeout.
David A. Holland
parents: 32
diff changeset
28 -- future. This is intended to be used for e.g. "feedback
68a4f2d8930e Add stuff for automatic feedback timeout.
David A. Holland
parents: 32
diff changeset
29 -- timeout".
68a4f2d8930e Add stuff for automatic feedback timeout.
David A. Holland
parents: 32
diff changeset
30 --
68a4f2d8930e Add stuff for automatic feedback timeout.
David A. Holland
parents: 32
diff changeset
31 -- States that should have a timeout installed are tagged
68a4f2d8930e Add stuff for automatic feedback timeout.
David A. Holland
parents: 32
diff changeset
32 -- accordingly in the states table. When changing the state of
68a4f2d8930e Add stuff for automatic feedback timeout.
David A. Holland
parents: 32
diff changeset
33 -- a PR, if the new state expects a timeout the PR should be
68a4f2d8930e Add stuff for automatic feedback timeout.
David A. Holland
parents: 32
diff changeset
34 -- given a non-null timeout; otherwise, the timeout field
68a4f2d8930e Add stuff for automatic feedback timeout.
David A. Holland
parents: 32
diff changeset
35 -- should be nulled.
68a4f2d8930e Add stuff for automatic feedback timeout.
David A. Holland
parents: 32
diff changeset
36 timeout_date timestamp null,
68a4f2d8930e Add stuff for automatic feedback timeout.
David A. Holland
parents: 32
diff changeset
37 timeout_state text null references states (name),
54
36d91dfe017f use valid sql syntax, mostly from yetoo on freenode
David A. Holland
parents: 41
diff changeset
38 check ((timeout_date is null) = (timeout_state is null)),
39
45bb7b516edb document previous better
David A. Holland
parents: 38
diff changeset
39
45bb7b516edb document previous better
David A. Holland
parents: 38
diff changeset
40 -- intended constraint:
45bb7b516edb document previous better
David A. Holland
parents: 38
diff changeset
41 -- select * from PRs, states where PRs.state = states.name
45bb7b516edb document previous better
David A. Holland
parents: 38
diff changeset
42 -- and states.timeout <> PRs.timeout_date is not null
45bb7b516edb document previous better
David A. Holland
parents: 38
diff changeset
43 -- should always return empty.
45bb7b516edb document previous better
David A. Holland
parents: 38
diff changeset
44 -- (All PRs in timeout states should have timeouts, and those
45bb7b516edb document previous better
David A. Holland
parents: 38
diff changeset
45 -- not in timeout states should not.)
38
68a4f2d8930e Add stuff for automatic feedback timeout.
David A. Holland
parents: 32
diff changeset
46
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
47 -- fixed-size history
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
48 arrival_schemaversion int not null,
12
d4c3bd255653 "timestamp", not "date". from asau
David A. Holland
parents: 10
diff changeset
49 arrival_date timestamp not null,
32
7458a278cb64 forgot modified_date
David A. Holland
parents: 29
diff changeset
50 modified_date timestamp not null,
12
d4c3bd255653 "timestamp", not "date". from asau
David A. Holland
parents: 10
diff changeset
51 closed_date timestamp ,
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
52
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
53 -- original submitter
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
54 originator bigint references users (id),
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
55
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
56 -- original submission
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
57 -- we don't keep this as such - these items go into an
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
58 -- entry in the admin log instead, and the submitter is
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
59 -- automatically subscribed to the bug.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
60 -- "Submitted by joe@schmoe, Message-Id <3@schmoe>, Subject: foo"
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
61 --from_address text not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
62 --mail_subject text not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
63 --mail_msgid text not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
64
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
65 -- contents
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
66 release text ,
41
81851564f552 The initial description, how_to_repeat, and fix go in the first message.
David A. Holland
parents: 39
diff changeset
67 environment text
81851564f552 The initial description, how_to_repeat, and fix go in the first message.
David A. Holland
parents: 39
diff changeset
68
81851564f552 The initial description, how_to_repeat, and fix go in the first message.
David A. Holland
parents: 39
diff changeset
69 -- these appear as a message
81851564f552 The initial description, how_to_repeat, and fix go in the first message.
David A. Holland
parents: 39
diff changeset
70 --description text ,
81851564f552 The initial description, how_to_repeat, and fix go in the first message.
David A. Holland
parents: 39
diff changeset
71 --how_to_repeat text ,
81851564f552 The initial description, how_to_repeat, and fix go in the first message.
David A. Holland
parents: 39
diff changeset
72 --fix text ,
81851564f552 The initial description, how_to_repeat, and fix go in the first message.
David A. Holland
parents: 39
diff changeset
73 --unformatted text
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
74
29
David A. Holland
parents: 12
diff changeset
75 )
David A. Holland
parents: 12
diff changeset
76 WITHOUT OIDS;
55
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
77
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
78 -- Only the admin can delete PRs.
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
79 GRANT SELECT, INSERT, UPDATE, DELETE ON PRs TO swallowtail_admin;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
80 GRANT SELECT, INSERT, UPDATE ON PRs TO swallowtail_writer;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
81 GRANT SELECT ON PRs TO swallowtail_reader;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 54
diff changeset
82 GRANT SELECT ON PRs TO swallowtail_public;