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

comparison database/schema/classify.sql @ 8:68cc276ac118

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SQL material from old tree, split up for accessibility.
author David A. Holland
date Sun, 19 Feb 2012 19:54:48 -0500
parents
children d42c0db81e28
comparison
equal deleted inserted replaced
7:c013fb703183 8:68cc276ac118
1 --
2 -- Classification.
3 --
4 --
5 -- Sorting and classifying bug reports usefully is a nontrivial
6 -- problem. For this reason, rather than bake in any particular
7 -- taxonomy, we allow multiple arbitrary classification schemes,
8 -- all of which are created equal. (Administratively, some will
9 -- be more equal than others, but that does not concern the
10 -- database.)
11 --
12 -- The classification schemes/taxonomies that already exist or are
13 -- projected to be wanted, as of this writing, are:
14 --
15 -- 1. The old GNATS system of categories. A PR has one category, which
16 -- is taken from a fixed list of keywords, currently these:
17 -- bin install kern lib misc pkg port-* security standards
18 -- toolchain xsrc y2k
19 -- Also the old "class" field.
20 --
21 -- 2. The old GNATS severity and priority fields, which are still
22 -- useful despite being broad.
23 --
24 -- 3. The nearest man page to where the problem appears to be. This is
25 -- FreeBSD's idea and seems to work fairly well for many things.
26 --
27 -- 4. The taxonomy dholland uses in the out-of-gnats PR lists, which
28 -- is related to the previous (it uses man pages for drivers and
29 -- programs) but is three-layer hierarchical.
30 --
31 -- 5. Assorted flat tags (e.g. PRs releng considers critical for the
32 -- 6.0 release), or equivalently, arbitrary hand-maintained lists.
33 -- It is anticipated that every developer will be able to make their
34 -- own lists.
35 --
36 -- 6. Properties derivable from other data that behave like flat tags
37 -- (e.g. "PRs in feedback more than 6 months") - these should not be
38 -- stored as tags in the database but it may be desirable to be able
39 -- to present them the same way as tags.
40 --
41 -- 7. Other hierarchical schemes that have yet to be invented.
42 --
43 -- Some of these classification schemes have additional special
44 -- semantics.
45 --
46 -- It is furthermore assumed that new classification schemes will be
47 -- added on the fly, not frequently, but frequently enough that the
48 -- list of schemes should not be hard-coded into programs that access
49 -- the database.
50 --
51 -- This creates something of a problem; encoding hierarchical data in
52 -- SQL is fun enough when the hierarchy is fixed. We need to be able
53 -- to handle hierarchical data whose layout hasn't been invented yet,
54 -- and that's really pushing it.
55 --
56 -- Arbitrary flat tags we can do. For the rest, I think we need to set
57 -- up a system that allows, essentially, extensions to the schema,
58 -- which can be arbitrary SQL, and which provide views that allow
59 -- nonspecialized client software to read (and maybe update, but maybe
60 -- not) the available material in a standardized way.
61 --
62 -- We will then have to have an additional set of views that take the
63 -- union of all the extensions; these views will need to be recreated
64 -- when a new classification scheme is added.
65 --
66
67 -- Tags.
68 CREATE TABLE tags (
69 name text primary key,
70 confidential boolean not null
71 -- XXX should have an owner
72 )
73 WITHOUT OIDS;
74
75 CREATE TABLE has_tags (
76 pr bigint not null references PRs (id),
77 tag text not null references tags (name),
78 )
79 WITHOUT OIDS;
80
81 -- Classifications.
82 --
83 -- Each classification scheme should define whatever tables and
84 -- goop it needs. It should also define a text view, as follows
85 -- assuming the scheme is called "foo":
86 --
87 -- . The name of the view should be "foo_classification_text".
88 -- . The view should produce tuples (scheme, order, pr, value).
89 -- . The types should be (text, int, bigint, text).
90 -- . The "scheme" column should be the scheme name, suitable
91 -- for display, e.g. "Foo".
92 -- . The "order" column should contain the canonical display
93 -- order number for this scheme. Used with ORDER BY ASCENDING
94 -- to get things to appear in a consistent order.
95 -- . The "pr" column should have pr numbers.
96 -- . The "value" column should have a text representation of the
97 -- value.
98 --
99 -- Choose the canonical display order number for a new scheme based on
100 -- the existing values in use. See below for the numbers used by the
101 -- built-in schemes.
102 --
103 -- Ideally it should be possible to update the value through the text
104 -- view. Ideally it would also be possible to insert through it, but
105 -- that will never work... have to think about how to handle that
106 -- best. (XXX?)
107 --
108
109 --------------------------------------------------------------
110
111 -- The "category" classification.
112 -- Each PR has one category that must be taken from the list in the
113 -- categories table. The category is used to select the default
114 -- responsible entity for incoming PRs.
115 CREATE TABLE has_categories (
116 pr bigint unique not null references PRs (id),
117 category text not null references categories (name)
118 )
119 WITHOUT OIDS;
120
121 CREATE VIEW category_classification_text AS
122 SELECT "Category" as scheme, 100 as order, pr, category as value
123 FROM has_categories
124 ;
125
126 -- The "class" classification.
127 -- Each PR has one class that must be taken from the list in the
128 -- classes table.
129 CREATE TABLE has_classes (
130 pr bigint unique not null references PRs (id),
131 class text not null references classes (name)
132 )
133 WITHOUT OIDS;
134
135 CREATE VIEW class_classification_text AS
136 SELECT "Class" as scheme, 200 as order, pr, class as value
137 FROM has_classes
138 ;
139
140 -- The "severity" classification.
141 -- Each PR has one severity that must be taken from the list in the
142 -- severities table.
143 CREATE TABLE has_severities (
144 pr bigint unique not null references PRs (id),
145 severity text not null references severities (name)
146 )
147 WITHOUT OIDS;
148
149 CREATE VIEW severity_classification_text AS
150 SELECT "Severity" as scheme, 300 as order, pr, severity as value
151 FROM has_severities
152 ;
153
154 -- The "priority" classification.
155 -- Each PR has one priority that must be taken from the list in the
156 -- priorities table.
157 CREATE TABLE has_priorities (
158 pr bigint unique not null references PRs (id),
159 priority text not null references priorities (name)
160 )
161 WITHOUT OIDS;
162
163 CREATE VIEW priority_classification_text AS
164 SELECT "Priority" as scheme, 400 as order, pr, priority as value
165 FROM has_priorities
166 ;
167
168 --------------------------------------------------------------
169
170 --
171 -- The collected view of all classifications.
172 --
173 -- (This does not include tags, as tags should be presented
174 -- differently.)
175 --
176 CREATE VIEW classification_text AS
177 SELECT * from category_classification_text
178 UNION SELECT * from class_classification_text
179 UNION SELECT * from severity_classification_text
180 UNION SELECT * from priority_classification_text
181 ;
182