Mercurial > ~dholland > hg > swallowtail > index.cgi
annotate database/schema/classify.sql @ 54:36d91dfe017f
use valid sql syntax, mostly from yetoo on freenode
author | David A. Holland |
---|---|
date | Sun, 10 Apr 2022 17:41:24 -0400 |
parents | cd36b49f4437 |
children | 40f64a96481f |
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 -- Classification. |
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 -- |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
5 -- Sorting and classifying bug reports usefully is a nontrivial |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
6 -- problem. For this reason, rather than bake in any particular |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
7 -- taxonomy, we allow multiple arbitrary classification schemes, |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
8 -- all of which are created equal. (Administratively, some will |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
9 -- be more equal than others, but that does not concern the |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
10 -- database.) |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
11 -- |
11 | 12 -- There are several classification schemes/taxonomies that already |
13 -- exist or have been invented but not yet deployed. These fall into | |
14 -- the following categories: | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
15 -- |
11 | 16 -- 1. Hierarchical taxonomy. The only existing hierarchical taxonomy |
17 -- is the one dholland uses in the out-of-GNATS PR lists, which | |
18 -- classifies PRs by location in the system. A second projected one is | |
19 -- classification by consequences (crashes, hangs, etc.) The possible | |
20 -- classifications are defined in advance and stored in the database. | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
21 -- |
11 | 22 -- 2. Flat taxonomy, that is, a selection from an enumeration of |
23 -- values. Existing schemes of this type include: | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
24 -- |
11 | 25 -- * the old GNATS scheme of categories (bin, kern, lib, etc.) |
26 -- * the old GNATS scheme of classes (sw-bug, doc-bug, etc.) | |
27 -- * the old GNATS severity field (critical, serious, non-critical) | |
28 -- * the old GNATS priority field (high, medium, low) | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
29 -- |
11 | 30 -- For these the possible values are defined in advance and stored in |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
31 -- the database. |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
32 -- |
11 | 33 -- 3. String value. Most of the time this is really a flat taxonomy |
34 -- where the number of possible values is large or changes dynamically | |
35 -- with external circumstances, or both, such that storing a copy of | |
23 | 36 -- the legal values in the database would create a maintenance hassle. |
11 | 37 -- There are none of these at present; the only projected one at the |
38 -- moment is FreeBSD's idea of "the nearest man page to where the | |
39 -- problem appears to be". | |
40 -- | |
41 -- 4. Systems of tags, that is, zero or more selections from a list | |
42 -- of possible values. Current schemes of this type include the | |
43 -- [456]-ONLY tags, the 6-CRITICAL/6-IMPORTANT/etc. release branch | |
44 -- tags, the STUCK tag, EASY, PATCH, PULLUPS-NEEDED, etc. Some of | |
45 -- these should be separate flat taxonomy schemes rather than tags, | |
46 -- and some of them should maybe be states rather than tags too. | |
47 -- (And, perhaps some of the current states should be tags...) | |
48 -- In addition we want to allow every developer to have their own | |
49 -- private tags that aren't exposed. And, we might want to have | |
50 -- project-only tags that other developers can see but users can't. | |
51 -- (It remains to be seen if that's really a good idea.) | |
52 -- | |
53 -- Note also that a list of PRs is equivalent to a tag on those PRs. | |
54 -- | |
55 -- It is also possible that there may be additional classifications | |
56 -- (possibly of any of the above types, but most likely tags) that are | |
57 -- views rather than data, that is, derived from other information. | |
58 -- One example of this is "PRs in feedback more than 6 months". | |
59 -- | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
60 -- |
11 | 61 -- We assume that new classification schemes will be added on the fly, |
62 -- not frequently, but frequently enough that the list of schemes | |
63 -- should not be hard-coded into programs that access the database, | |
64 -- and we don't want to have to create a new table for each new scheme | |
65 -- either. | |
66 -- | |
67 -- Therefore, the way I'll do this is to create one table (or rather, | |
68 -- one family of tables) for each type of classification scheme, and | |
69 -- make them able to handle arbitrary instances. | |
70 -- | |
71 -- For hierarchical schemes there is no point trying to encode the | |
72 -- hierarchical structure in SQL; that is a waste of time. Instead | |
73 -- we'll expand the tree of allowed values and rely on the access | |
74 -- software to present the schemes sensibly. | |
75 -- | |
76 -- It remains unclear what views ought to be defined. | |
77 -- | |
78 -- | |
79 -- Notes on the representations: | |
80 -- * a scheme is "total" if it should be defined on all PRs | |
81 -- * if the data contains a null value for a particular scheme, | |
82 -- that means "not specified yet". | |
83 -- * the hierarchical and flat schemes are no different schema- | |
84 -- wise but are separate to allow different handling in software. | |
85 -- * the ordering field in the values tables should be used so that | |
86 -- ORDER BY ordering ASCENDING produces the desired output order | |
87 -- of the legal values. | |
88 -- * the obsolete field for values is for entries that are allowed | |
89 -- to still exist in the database but that should not be used | |
90 -- with new PRs or for new classifications of old PRs. | |
91 -- * the ordering field for the classifications themselves should | |
92 -- be used so that ORDER BY ordering ASCENDING produces the | |
93 -- desired output order of the classification schemes. The | |
94 -- numbers are global across classification scheme types so | |
95 -- schemes of the same type do not need to be sorted together. | |
96 -- | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
97 -- |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
98 |
11 | 99 ------------------------------------------------------------ |
100 -- Hierarchical schemes. | |
101 | |
102 -- names and properties of the hierarchical schemes | |
103 CREATE TABLE hierclass_names ( | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
104 name text primary key, |
11 | 105 ordering int not null, |
106 total boolean not null, | |
107 description text not null | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
108 ) |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
109 WITHOUT OIDS; |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
110 |
11 | 111 -- allowed values of the hierarchical schemes |
112 CREATE TABLE hierclass_values ( | |
113 scheme text not null references hierclass_names (name), | |
114 value text not null, | |
115 ordering int not null, | |
116 obsolete boolean not null, | |
117 description text not null, | |
118 primary key (scheme, value) | |
119 ) | |
120 WITHOUT OIDS; | |
121 | |
122 -- classification of PRs according to the hierarchical schemes | |
123 CREATE TABLE hierclass_data ( | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
124 pr bigint not null references PRs (id), |
11 | 125 scheme text not null references hierclass_names (name), |
126 value text , | |
127 primary key (pr, scheme), | |
128 -- | |
29 | 129 -- Ok, the intended semantics here are: |
130 -- * For PRs that the classification doesn't apply to, there | |
131 -- should be no row at all in the *class_data table. | |
132 -- * For PRs where the classification is undetermined or | |
133 -- unassigned, the value column should be null, and if | |
134 -- that's allowed there should be a null entry in | |
135 -- the *class_values table. | |
136 -- I believe "MATCH SIMPLE" to be capable of enforcing this, | |
137 -- but that's from reading postgres docs and not from actually | |
138 -- checking it. From the description, "MATCH FULL" will do the | |
139 -- wrong thing, and "MATCH PARTIAL" isn't supported. | |
11 | 140 -- |
29 | 141 foreign key (scheme, value) references hierclass_values (scheme, value) |
142 MATCH FULL | |
143 ON DELETE RESTRICT | |
144 ON UPDATE RESTRICT | |
11 | 145 ) |
146 WITHOUT OIDS; | |
147 | |
148 ------------------------------------------------------------ | |
149 -- Enumerated flat schemes. | |
150 | |
151 -- names and properties of the flat schemes | |
152 CREATE TABLE flatclass_names ( | |
153 name text primary key, | |
154 ordering int not null, | |
155 total boolean not null, | |
156 description text not null | |
157 ) | |
158 WITHOUT OIDS; | |
159 | |
160 -- allowed values of the flat schemes | |
161 CREATE TABLE flatclass_values ( | |
162 scheme text not null references flatclass_names (name), | |
163 value text not null, | |
164 ordering int not null, | |
165 obsolete boolean not null, | |
166 description text not null, | |
167 primary key (scheme, value) | |
168 ) | |
169 WITHOUT OIDS; | |
170 | |
171 -- classification of PRs according to the flat schemes | |
172 CREATE TABLE flatclass_data ( | |
173 pr bigint not null references PRs (id), | |
174 scheme text not null references flatclass_names (name), | |
175 value text , | |
176 primary key (pr, scheme), | |
177 -- as above | |
29 | 178 foreign key (scheme, value) references flatclass_values (scheme, value) |
179 MATCH FULL | |
180 ON DELETE RESTRICT | |
181 ON UPDATE RESTRICT | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
182 ) |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
183 WITHOUT OIDS; |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
184 |
11 | 185 ------------------------------------------------------------ |
186 -- Text schemes. | |
187 | |
188 -- names and properties of the text schemes | |
189 CREATE TABLE textclass_names ( | |
190 name text primary key, | |
191 ordering int not null, | |
192 total boolean not null, | |
193 description text not null | |
194 ) | |
195 WITHOUT OIDS; | |
196 | |
197 -- classification of PRs according to the text schemes | |
198 CREATE TABLE textclass_data ( | |
199 pr bigint not null references PRs (id), | |
200 scheme text not null references textclass_names (name), | |
201 value text , | |
29 | 202 primary key (pr, scheme) |
11 | 203 ) |
204 WITHOUT OIDS; | |
205 | |
206 ------------------------------------------------------------ | |
207 -- Tag schemes. | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
208 |
11 | 209 -- names and properties of the tag schemes |
210 -- (total does not make sense here) | |
211 CREATE TABLE tagclass_names ( | |
212 name text primary key, | |
213 ordering int not null, | |
214 description text not null | |
215 ) | |
216 WITHOUT OIDS; | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
217 |
11 | 218 -- allowed values of the tag schemes |
219 -- (each PR can reference zero or more of these) | |
220 CREATE TABLE tagclass_values ( | |
221 scheme text not null references tagclass_names (name), | |
222 value text not null, | |
223 ordering int not null, | |
224 obsolete boolean not null, | |
225 description text not null, | |
226 primary key (scheme, value) | |
227 ) | |
228 WITHOUT OIDS; | |
229 | |
230 -- classification of PRs according to the tag schemes | |
231 -- (each PR/scheme pair is listed once for each tag attached to it) | |
232 CREATE TABLE tagclass_data ( | |
233 pr bigint not null references PRs (id), | |
234 scheme text not null references tagclass_names (name), | |
235 value text , | |
236 -- as above | |
29 | 237 foreign key (scheme, value) references tagclass_values (scheme, value) |
238 MATCH FULL | |
239 ON DELETE RESTRICT | |
240 ON UPDATE RESTRICT | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
241 ) |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
242 WITHOUT OIDS; |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
243 |
11 | 244 -- view where tags are collected into a string |
29 | 245 -- string_agg() concatenates the value strings |
11 | 246 CREATE VIEW tagclass_stringdata AS |
29 | 247 SELECT pr, |
248 tagclass_data.scheme as scheme, | |
249 string_agg(tagclass_data.value, ' ' | |
250 ORDER BY tagclass_values.ordering) as value | |
251 FROM tagclass_data, tagclass_values | |
252 WHERE | |
253 tagclass_data.scheme = tagclass_values.scheme | |
254 AND tagclass_data.value = tagclass_values.value | |
255 GROUP BY pr, tagclass_data.scheme | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
256 ; |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
257 |
11 | 258 ------------------------------------------------------------ |
259 -- some views | |
260 | |
261 -- views that pull in the ordering fields. | |
262 -- | |
263 -- (If you use only one of the ordering fields, as is likely to be | |
264 -- the case in practice, I think we can rely on the query optimizer | |
265 -- to drop the join that collects in the other ordering field. If | |
266 -- not, maybe this should be restructured.) | |
267 -- | |
268 -- XXX what do these do when _data.value is null? | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
269 |
11 | 270 CREATE VIEW hierclass_data_ordered AS |
271 SELECT hierclass_data.pr as pr, | |
272 hierclass_data.scheme as scheme, | |
273 hierclass_names.ordering as schemeordering, | |
274 hierclass_data.value as value, | |
29 | 275 hierclass_values.ordering as valueordering |
11 | 276 FROM hierclass_data, hierclass_names, hierclass_values |
277 WHERE | |
278 hierclass_data.scheme = hierclass_names.name | |
279 AND hierclass_data.scheme = hierclass_values.scheme | |
280 AND hierclass_data.value = hierclass_values.value | |
281 ; | |
282 | |
283 CREATE VIEW flatclass_data_ordered AS | |
284 SELECT flatclass_data.pr as pr, | |
285 flatclass_data.scheme as scheme, | |
286 flatclass_names.ordering as schemeordering, | |
287 flatclass_data.value as value, | |
29 | 288 flatclass_values.ordering as valueordering |
11 | 289 FROM flatclass_data, flatclass_names, flatclass_values |
290 WHERE | |
291 flatclass_data.scheme = flatclass_names.name | |
292 AND flatclass_data.scheme = flatclass_values.scheme | |
293 AND flatclass_data.value = flatclass_values.value | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
294 ; |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
295 |
11 | 296 -- |
297 -- XXX what I'd like to do here is produce textclass_data.value as | |
298 -- valueordering; then sorting by valueordering would produce the | |
299 -- desired results (the entries sorted by text order of the values). | |
300 -- That produces a type conflict though if we combine this view | |
301 -- with the other ordered views where the ordering is a number. | |
302 -- | |
303 -- Next best would be to order by textclass_data.value in this view | |
304 -- and produce the row number of the result (or some other fresh | |
305 -- sequence) as valueordering. However, I don't think that's possible. | |
306 -- | |
307 -- What's here (using a fixed value of 1) will run but it's not | |
308 -- particularly desirable. | |
309 -- | |
310 CREATE VIEW textclass_data_ordered AS | |
311 SELECT textclass_data.pr as pr, | |
312 textclass_data.scheme as scheme, | |
313 textclass_names.ordering as schemeordering, | |
314 textclass_data.value as value, | |
29 | 315 1 as valueordering |
11 | 316 FROM textclass_data, textclass_names |
317 WHERE | |
318 textclass_data.scheme = textclass_names.name | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
319 ; |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
320 |
11 | 321 CREATE VIEW tagclass_data_ordered AS |
322 SELECT tagclass_data.pr as pr, | |
323 tagclass_data.scheme as scheme, | |
324 tagclass_names.ordering as schemeordering, | |
325 tagclass_data.value as value, | |
29 | 326 tagclass_values.ordering as valueordering |
11 | 327 FROM tagclass_data, tagclass_names, tagclass_values |
328 WHERE | |
329 tagclass_data.scheme = tagclass_names.name | |
330 AND tagclass_data.scheme = tagclass_values.scheme | |
331 AND tagclass_data.value = tagclass_values.value | |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
332 ; |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
333 |
11 | 334 -- ordered version of tagclass_stringdata |
29 | 335 -- |
336 -- Note that schemeordering is uniquely defined by the group-by | |
337 -- and the min() on it is there to satisfy the cookiemonster. | |
338 -- | |
11 | 339 CREATE VIEW tagclass_stringdata_ordered AS |
29 | 340 SELECT pr, scheme, min(schemeordering), |
341 string_agg(value, ' ' ORDER BY valueordering) as value, | |
342 min(valueordering) as valueordering | |
343 FROM tagclass_data_ordered | |
11 | 344 GROUP BY pr, scheme |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
345 ; |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
346 |
11 | 347 |
348 -- a view that combines all the classification data. | |
349 | |
350 CREATE VIEW classifications AS | |
351 SELECT * FROM hierclass_data_ordered | |
352 UNION SELECT * FROM flatclass_data_ordered | |
353 UNION SELECT * FROM textclass_data_ordered | |
354 UNION SELECT * FROM tagclass_stringdata_ordered | |
355 ORDER BY schemeordering | |
356 ; | |
357 |