--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/classify.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,182 @@
+--
+-- Classification.
+--
+--
+-- Sorting and classifying bug reports usefully is a nontrivial
+-- problem. For this reason, rather than bake in any particular
+-- taxonomy, we allow multiple arbitrary classification schemes,
+-- all of which are created equal. (Administratively, some will
+-- be more equal than others, but that does not concern the
+-- database.)
+--
+-- The classification schemes/taxonomies that already exist or are
+-- projected to be wanted, as of this writing, are:
+--
+-- 1. The old GNATS system of categories. A PR has one category, which
+-- is taken from a fixed list of keywords, currently these:
+--    bin install kern lib misc pkg port-* security standards
+--    toolchain xsrc y2k
+-- Also the old "class" field.
+--
+-- 2. The old GNATS severity and priority fields, which are still
+-- useful despite being broad.
+--
+-- 3. The nearest man page to where the problem appears to be. This is
+-- FreeBSD's idea and seems to work fairly well for many things.
+--
+-- 4. The taxonomy dholland uses in the out-of-gnats PR lists, which
+-- is related to the previous (it uses man pages for drivers and
+-- programs) but is three-layer hierarchical.
+--
+-- 5. Assorted flat tags (e.g. PRs releng considers critical for the
+-- 6.0 release), or equivalently, arbitrary hand-maintained lists.
+-- It is anticipated that every developer will be able to make their
+-- own lists.
+--
+-- 6. Properties derivable from other data that behave like flat tags
+-- (e.g. "PRs in feedback more than 6 months") - these should not be
+-- stored as tags in the database but it may be desirable to be able
+-- to present them the same way as tags.
+--
+-- 7. Other hierarchical schemes that have yet to be invented.
+--
+-- Some of these classification schemes have additional special
+-- semantics.
+--
+-- It is furthermore assumed that new classification schemes will be
+-- added on the fly, not frequently, but frequently enough that the
+-- list of schemes should not be hard-coded into programs that access
+-- the database.
+--
+-- This creates something of a problem; encoding hierarchical data in
+-- SQL is fun enough when the hierarchy is fixed. We need to be able
+-- to handle hierarchical data whose layout hasn't been invented yet,
+-- and that's really pushing it.
+-- 
+-- Arbitrary flat tags we can do. For the rest, I think we need to set
+-- up a system that allows, essentially, extensions to the schema,
+-- which can be arbitrary SQL, and which provide views that allow
+-- nonspecialized client software to read (and maybe update, but maybe
+-- not) the available material in a standardized way.
+--
+-- We will then have to have an additional set of views that take the
+-- union of all the extensions; these views will need to be recreated
+-- when a new classification scheme is added.
+--
+
+-- Tags.
+CREATE TABLE tags (
+	name text		primary key,
+	confidential boolean	not null
+	-- XXX should have an owner
+)
+WITHOUT OIDS;
+
+CREATE TABLE has_tags (
+	pr bigint		not null references PRs (id),
+	tag text		not null references tags (name),
+)
+WITHOUT OIDS;
+
+-- Classifications.
+--
+-- Each classification scheme should define whatever tables and
+-- goop it needs. It should also define a text view, as follows
+-- assuming the scheme is called "foo":
+--
+--   . The name of the view should be "foo_classification_text".
+--   . The view should produce tuples (scheme, order, pr, value).
+--   . The types should be (text, int, bigint, text).
+--   . The "scheme" column should be the scheme name, suitable
+--     for display, e.g. "Foo".
+--   . The "order" column should contain the canonical display
+--     order number for this scheme. Used with ORDER BY ASCENDING
+--     to get things to appear in a consistent order.
+--   . The "pr" column should have pr numbers.
+--   . The "value" column should have a text representation of the
+--     value.
+--
+-- Choose the canonical display order number for a new scheme based on
+-- the existing values in use. See below for the numbers used by the
+-- built-in schemes.
+--
+-- Ideally it should be possible to update the value through the text
+-- view. Ideally it would also be possible to insert through it, but
+-- that will never work... have to think about how to handle that
+-- best. (XXX?)
+--
+
+--------------------------------------------------------------
+
+-- The "category" classification.
+-- Each PR has one category that must be taken from the list in the
+-- categories table. The category is used to select the default
+-- responsible entity for incoming PRs.
+CREATE TABLE has_categories (
+	pr bigint		unique not null references PRs (id),
+	category text		not null references categories (name)
+)
+WITHOUT OIDS;
+
+CREATE VIEW category_classification_text AS
+   SELECT "Category" as scheme, 100 as order, pr, category as value
+   FROM has_categories
+;
+
+-- The "class" classification.
+-- Each PR has one class that must be taken from the list in the
+-- classes table.
+CREATE TABLE has_classes (
+	pr bigint		unique not null references PRs (id),
+	class text		not null references classes (name)
+)
+WITHOUT OIDS;
+
+CREATE VIEW class_classification_text AS
+   SELECT "Class" as scheme, 200 as order, pr, class as value
+   FROM has_classes
+;
+
+-- The "severity" classification.
+-- Each PR has one severity that must be taken from the list in the
+-- severities table.
+CREATE TABLE has_severities (
+	pr bigint		unique not null references PRs (id),
+	severity text		not null references severities (name)
+)
+WITHOUT OIDS;
+
+CREATE VIEW severity_classification_text AS
+   SELECT "Severity" as scheme, 300 as order, pr, severity as value
+   FROM has_severities
+;
+
+-- The "priority" classification.
+-- Each PR has one priority that must be taken from the list in the
+-- priorities table.
+CREATE TABLE has_priorities (
+	pr bigint		unique not null references PRs (id),
+	priority text		not null references priorities (name)
+)
+WITHOUT OIDS;
+
+CREATE VIEW priority_classification_text AS
+   SELECT "Priority" as scheme, 400 as order, pr, priority as value
+   FROM has_priorities
+;
+
+--------------------------------------------------------------
+
+--
+-- The collected view of all classifications.
+--
+-- (This does not include tags, as tags should be presented
+-- differently.)
+--
+CREATE VIEW classification_text AS
+         SELECT * from category_classification_text
+   UNION SELECT * from class_classification_text
+   UNION SELECT * from severity_classification_text
+   UNION SELECT * from priority_classification_text
+;
+