# HG changeset patch # User David A. Holland # Date 1329699288 18000 # Node ID 68cc276ac11834c922df9b14c36a8465316b3bf7 # Parent c013fb70318362be6113762b0433856ccb41bef7 SQL material from old tree, split up for accessibility. diff -r c013fb703183 -r 68cc276ac118 database/init/database.sql --- /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; diff -r c013fb703183 -r 68cc276ac118 database/init/users.sql --- /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; diff -r c013fb703183 -r 68cc276ac118 database/primary-init.sql --- /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 diff -r c013fb703183 -r 68cc276ac118 database/schema.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; + + + diff -r c013fb703183 -r 68cc276ac118 database/schema/TODO --- /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 diff -r c013fb703183 -r 68cc276ac118 database/schema/bugs.sql --- /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; diff -r c013fb703183 -r 68cc276ac118 database/schema/classify.sql --- /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 +; + diff -r c013fb703183 -r 68cc276ac118 database/schema/config.sql --- /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; + diff -r c013fb703183 -r 68cc276ac118 database/schema/logs.sql --- /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; diff -r c013fb703183 -r 68cc276ac118 database/schema/messages.sql --- /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.) + diff -r c013fb703183 -r 68cc276ac118 database/schema/subscription.sql --- /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. diff -r c013fb703183 -r 68cc276ac118 database/schema/users.sql --- /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. + diff -r c013fb703183 -r 68cc276ac118 database/schema/version.sql --- /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;