--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/init/database.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,4 @@
+-- Database initialization.
+-- Needs to be done as database superuser.
+
+create database swallowtail;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/init/users.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,8 @@
+-- Database user initialization.
+-- Needs to be done as database superuser.
+--
+-- The 'swallowtail' user will have ordinary access to the database;
+-- the 'swallowtail-admin' user will have full access to the database.
+
+create user swallowtail;
+create user swallowtail-admin;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/primary-init.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,5 @@
+-- Primary database initialization.
+-- Needs to be done as database superuser.
+
+\i init/database.sql
+\i init/users.sql

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,19 @@
+begin;
+
+\i schema/version.sql
+\i schema/config.sql
+
+\i schema/users.sql
+
+\i schema/bugs.sql
+\i schema/messages.sql
+\i schema/subscription.sql
+
+\i schema/classify.sql
+
+\i schema/logs.sql
+
+commit;
+
+
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/TODO	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,12 @@
+-- Does not handle:
+--
+-- * Should be able to merge duplicates.
+-- * Should allow recording dependencies between bugs.
+-- * Category should list the default 'responsible' value.
+-- * Need to know what relevance entries to create based on the category.
+-- * There should be an incoming queue for stuff submitted via the web
+--   interface, because a lot of it will be spam.
+-- * There should be a whitelist/blacklist for that queue, by IP address
+--   or netblock.
+-- * Should support automatic feedback timeout.
+-- * should store pullup numbers

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/bugs.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,86 @@
+--
+-- PR data.
+--
+-- PRs is the primary table of bug info, with one row per problem
+-- report.
+--
+
+CREATE SEQUENCE next_PR;
+
+CREATE TABLE PRs (
+	id bigint		primary key default nextval('next_PR'),
+
+	-- basic description
+	synopsis text		not null,
+	confidential boolean	not null,
+
+	-- states
+	--
+	-- there are 64 combinations but only these 15 are valid:
+	--    (un)locked closed			("closed")
+	--    (un)locked closed analyzed	("closed")
+	--    (un)locked closed invalid		("dead")
+	--    unlocked open			("open")
+	--    unlocked open analyzed		("analyzed")
+	--    unlocked open analyzed? feedback	("feedback")
+	--    unlocked open analyzed? suspended	("suspended")
+	--    unlocked open analyzed? feedback suspended ("stuck")
+	--    unlocked open invalid feedback	("incomplete")
+	--
+	-- gnats states map to: open	inval.	anal.	feedb.	susp.
+	--   open		open
+	--   analyzed		open analyzed
+	--   feedback		open feedback
+	--   (pullups-needed)	open (*)
+	--   pending-pullups	open (*)
+	--   suspended		open analyzed suspended
+	--   (stuck)		open feedback suspended
+	--   closed		-
+	--   dead		invalid
+	--
+	-- The cases marked (*) are distinguished from open by the
+	-- branchstate in the relevance table.
+
+	locked boolean		not null,	-- deny modifications
+	open boolean		not null,	-- master switch
+
+	invalid boolean		not null,	-- report is no good
+	analyzed boolean	not null,	-- issue is believed understood
+	feedback boolean	not null,	-- feedback required
+	suspended boolean	not null,	-- work halted
+
+	-- feedback and suspended imply open
+	check NOT (NOT open AND (feedback OR suspended)),
+	-- invalid precludes analyzed and suspended
+	check NOT (invalid AND (analyzed OR suspended)),
+	-- open and invalid implies feedback
+	check NOT (NOT feedback AND open AND invalid),
+	-- locked implies not open
+	check NOT (open AND locked),
+
+	-- fixed-size history
+	arrival_schemaversion int  not null,
+	arrival_date date	not null,
+	closed_date date	,
+
+	-- original submitter
+	originator bigint	references users (id),
+
+	-- original submission
+	-- we don't keep this as such - these items go into an
+	-- entry in the admin log instead, and the submitter is
+	-- automatically subscribed to the bug.
+	-- "Submitted by joe@schmoe, Message-Id <3@schmoe>, Subject: foo"
+	--from_address text	not null,
+	--mail_subject text	not null,
+	--mail_msgid text	not null,
+
+	-- contents
+	release text		,
+	environment text	,
+	description text	,
+	how_to_repeat text	,
+	fix text		,
+	unformatted text	
+
+) without oids;

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

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/config.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,39 @@
+--
+-- These tables record the allowable values for the various
+-- enumerated settings.
+--
+-- Each entry has the value string (the name), a description field,
+-- and a flag to mark the entry obsolete. Obsolete values are still
+-- allowed to exist in the database, but new uses are not permitted.
+--
+
+-- Severity ratings of bugs
+CREATE TABLE severities (
+	name text		primary key,
+	obsolete boolean	not null,
+	description text
+)
+WITHOUT OIDS;
+
+-- Priority ratings of bugs
+CREATE TABLE priorities (
+	name text		primary key,
+	obsolete boolean	not null,
+	description text
+)
+WITHOUT OIDS;
+
+-- Types of bugs (sw-bug, doc-bug, etc.)
+create table classes (
+	name text		primary key,
+	obsolete boolean	not null,
+	description text
+) without oids;
+
+-- Categories of bugs (bin, kern, pkg, etc.)
+create table categories (
+	name text		primary key,
+	obsolete boolean	not null,
+	description text	
+) without oids;
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/logs.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,13 @@
+-- log of administrative changes over time
+CREATE TABLE adminlog (
+	-- who (null means "swallowtail"), where, when, what, why
+	who bigint		null references users (id),
+	pr bigint		not null references PRs (id),
+	change text		not null,       
+	when date		not null,
+	comment text		not null
+
+	-- if a message was posted, this is the one
+	msgid bigint		null references messages (id),
+)
+WITHOUT OIDS;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/messages.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,52 @@
+--
+-- Messages.
+--
+
+CREATE SEQUENCE next_rawmsgid;
+
+-- all incoming mail in original form, for reference
+-- (should be pruned periodically)
+CREATE TABLE rawmail (
+	id bigint		primary key default nextval('next_rawmsgid'),
+	data text		not null
+)
+WITHOUT OIDS;
+
+CREATE SEQUENCE next_msgid;
+
+-- comments
+CREATE TABLE messages (
+	id bigint		primary key default nextval('next_msgid'),
+	pr bigint		not null references prs (id),
+	who bigint		not null references users (id),
+	parent_id bigint	null references messages (id),
+	when date		not null,
+	body text		not null,
+
+	-- we don't keep these directly, they go into an admin log entry
+	--from_address text	not null,
+	--mail_subject text	not null,
+	--message_id text	not null,
+	rawid bigint		null references rawmail (id)
+
+	check (parent_id != commentid)
+)
+WITHOUT OIDS;
+
+-- for patches and mime-attachments
+-- if msgid is null, attachment came with original PR
+CREATE TABLE attachments (
+	pr bigint		not null references PRs (id),
+	msgid bigint		null references messages (id),
+	mimetype text		not null,
+	body text		not null
+)
+WITHOUT OIDS;
+
+-- intended constraint:
+--   SELECT messages.pr, attachments.pr
+--   FROM messages, attachments
+--   WHERE messages.id = attachments.msgid AND messages.pr <> attachments.pr
+-- should always be empty.
+-- (XXX this is gross.)
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/subscription.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,44 @@
+--
+-- subscription lists
+-- 
+-- Users subscribe to PRs to be notified when the PR is modified.
+--
+-- If "batch" is set, notices and copies of messages are sent out
+-- in batches.
+--
+-- If "reporter" is set, the user is also nagged when the PR is in
+-- feedback. If "responsible" is set, the user is nagged when the PR
+-- is not in feedback.
+--
+-- The original submitter is automatically subscribed as a reporter.
+
+CREATE TABLE subscriptions (
+	pr bigint		not null references PRs (id),
+	user bigint		not null references users (id),
+	batch boolean		not null,
+	reporter boolean	not null,
+	responsible boolean	not null
+)
+WITHOUT OIDS;
+
+-- Intended constraints:
+--
+--    SELECT id, responsible FROM PRs, subscriptions, users
+--    WHERE PRs.id = subscriptions.id
+--      AND subscriptions.user = users.id
+--      AND PRs.state <> 'closed'
+--      AND subscriptions.responsible
+--      AND NOT users.responsible
+--    ;
+--
+-- and
+--
+--    SELECT id, responsible FROM PRs, subscriptions, users
+--    WHERE PRs.id = subscriptions.id
+--      AND subscriptions.user = users.id
+--      AND PRs.state = 'closed'
+--      AND subscriptions.responsible
+--      AND NOT users.oldresponsible
+--    ;
+--
+-- should always return nothing.

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/users.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,75 @@
+--
+-- Users.
+--
+-- Rather than trying to have different kinds of users (as in the last
+-- rev of this stuff) I think we will just have user permissions.
+-- Otherwise we end up in various kinds of trouble if users change
+-- type. This means we cannot use foreign key constraints to e.g.
+-- ensure that non-developers are not responsible for bugs; however,
+-- I guess we can cope.
+--
+-- The permissions are:
+--    mailto		we can mail to the user's e-mail address on file
+--    oldresponsible	allowed to be responsible for closed PRs
+--    responsible	allowed to be responsible for PRs
+--    editpr		can do edit-pr things
+--    admin		can do destructive things
+--
+-- The following types of users should have permissions as follows:
+--    peon              none or mailto
+--    submitter         mailto
+--    role              mailto|oldresponsible|responsible
+--    developer         mailto|oldresponsible|responsible|editpr
+--    administrator     mailto|oldresponsible|responsible|editpr|admin
+--    retired devel     mailto|oldresponsible
+--    deceased devel           oldresponsible
+--
+-- However, this may vary; e.g. users whose mail address bounces might
+-- have their mailto permission revoked.
+--
+-- The "webpassword" is for use logging in over unsecured HTTP to do
+-- things submitters can do. It is assumed that developers (and
+-- administrators, who should all be developers) authenticate some
+-- other way.
+-- 
+
+CREATE SEQUENCE next_user;
+
+CREATE TABLE users (
+	id bigint		primary key default nextval('next_user'),
+	username text		,
+	realname text		,
+
+	-- permissions --
+	mailto boolean		not null,
+	oldresponsible boolean	not null,
+	responsible boolean	not null,
+	editpr boolean		not null,
+	admin boolean		not null
+
+	-- responsible implies oldresponsible
+	check NOT (responsible AND NOT oldresponsible)
+)
+WITHOUT OIDS;
+
+CREATE TABLE mailaddresses (
+	id bigint		not null references users (id),
+	selected boolean	not null,
+	email text		not null,
+	organization text	,
+	webpassword text	,
+	lastheardfrom date
+)
+WITHOUT OIDS;
+
+CREATE VIEW usermail AS
+   SELECT id, username, realname, email,
+          mailto, responsible, editpr, admin
+   FROM users, mailaddresses
+   WHERE users.id = mailaddresses.id AND mailaddresses.selected
+;
+
+-- Intended constraint:
+-- (oldresponsible OR responsible OR editpr OR admin)
+-- implies username not null.
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/version.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,13 @@
+--
+-- Schema info.
+--
+-- The schema version is stored in every PR when the PR is
+-- entered; this helps reconstruct history.
+--
+-- The schema version is also used for future-proofing.
+
+-- Should have only one row.
+CREATE TABLE schemainfo (
+       version int	not null
+)
+WITHOUT OIDS;