--
-- 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
;