blob: b4451290e3ec4c3f76970ce8184cfd6689c38990 [file] [log] [blame]
-- Copyright 2016 The Chromium Authors. All Rights Reserved.
--
-- Use of this source code is governed by a BSD-style
-- license that can be found in the LICENSE file or at
-- https://developers.google.com/open-source/licenses/bsd
-- Create issue-realted tables in monorail db.
CREATE TABLE StatusDef (
id INT NOT NULL AUTO_INCREMENT,
project_id SMALLINT UNSIGNED NOT NULL,
status VARCHAR(80) BINARY NOT NULL,
rank SMALLINT UNSIGNED,
means_open BOOLEAN,
docstring TEXT,
deprecated BOOLEAN DEFAULT FALSE,
PRIMARY KEY (id),
UNIQUE KEY (project_id, status),
FOREIGN KEY (project_id) REFERENCES Project(project_id)
) ENGINE=INNODB;
CREATE TABLE ComponentDef (
id INT NOT NULL AUTO_INCREMENT,
project_id SMALLINT UNSIGNED NOT NULL,
-- Note: parent components have paths that are prefixes of child components.
path VARCHAR(255) BINARY NOT NULL,
docstring TEXT,
deprecated BOOLEAN DEFAULT FALSE,
created INT,
creator_id INT UNSIGNED,
modified INT,
modifier_id INT UNSIGNED,
is_deleted BOOLEAN DEFAULT FALSE,
PRIMARY KEY (id),
FOREIGN KEY (project_id) REFERENCES Project(project_id),
FOREIGN KEY (creator_id) REFERENCES User(user_id),
FOREIGN KEY (modifier_id) REFERENCES User(user_id),
INDEX project_id2 (project_id, path)
) ENGINE=INNODB;
CREATE TABLE Component2Admin (
component_id INT NOT NULL,
admin_id INT UNSIGNED NOT NULL,
PRIMARY KEY (component_id, admin_id),
FOREIGN KEY (component_id) REFERENCES ComponentDef(id),
FOREIGN KEY (admin_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE Component2Cc (
component_id INT NOT NULL,
cc_id INT UNSIGNED NOT NULL,
PRIMARY KEY (component_id, cc_id),
FOREIGN KEY (component_id) REFERENCES ComponentDef(id),
FOREIGN KEY (cc_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE LabelDef (
id INT NOT NULL AUTO_INCREMENT,
project_id SMALLINT UNSIGNED NOT NULL,
label VARCHAR(80) BINARY NOT NULL COLLATE utf8mb4_unicode_ci,
rank SMALLINT UNSIGNED,
docstring TEXT,
deprecated BOOLEAN DEFAULT FALSE,
PRIMARY KEY (id),
UNIQUE KEY (project_id, label),
FOREIGN KEY (project_id) REFERENCES Project(project_id)
) ENGINE=INNODB;
CREATE TABLE Component2Label (
component_id INT NOT NULL,
label_id INT NOT NULL,
PRIMARY KEY (component_id, label_id),
FOREIGN KEY (component_id) REFERENCES ComponentDef(id),
FOREIGN KEY (label_id) REFERENCES LabelDef(id)
) ENGINE=INNODB;
CREATE TABLE FieldDef (
id INT NOT NULL AUTO_INCREMENT,
project_id SMALLINT UNSIGNED NOT NULL,
rank SMALLINT UNSIGNED,
field_name VARCHAR(80) BINARY NOT NULL,
-- TODO(jrobbins): more types
field_type ENUM ('enum_type', 'int_type', 'str_type', 'user_type', 'date_type', 'url_type', 'approval_type') NOT NULL,
applicable_type VARCHAR(80), -- No value means: offered for all issue types
applicable_predicate TEXT, -- No value means: TRUE
is_required BOOLEAN, -- true means required if applicable
is_niche BOOLEAN, -- true means user must click to reveal widget
is_multivalued BOOLEAN,
-- TODO(jrobbins): access controls: restrict, grant
-- Validation for int_type fields
min_value INT,
max_value INT,
-- Validation for str_type fields
regex VARCHAR(80),
-- Validation for user_type fields
needs_member BOOLEAN, -- User value can only be set to users who are members
needs_perm VARCHAR(80), -- User value can only be set to users w/ that perm
grants_perm VARCHAR(80), -- User named in this field gains this perm in the issue
-- notification options for user_type fields
notify_on ENUM ('never', 'any_comment') DEFAULT 'never' NOT NULL,
-- notification options for date_type fields
date_action ENUM ('no_action', 'ping_owner_only', 'ping_participants'),
-- TODO(jrobbins): default value
-- TODO(jrobbins): deprecated boolean?
docstring TEXT,
is_deleted BOOLEAN, -- If true, reap this field def after all values reaped.
approval_id INT,
is_phase_field BOOLEAN DEFAULT FALSE,
is_restricted_field BOOLEAN DEFAULT FALSE, -- If true, editors are restricted to the FieldDef2Editors tbl.
PRIMARY KEY (id),
UNIQUE KEY (project_id, field_name),
FOREIGN KEY (project_id) REFERENCES Project(project_id)
) ENGINE=INNODB;
CREATE TABLE FieldDef2Admin (
field_id INT NOT NULL,
admin_id INT UNSIGNED NOT NULL,
PRIMARY KEY (field_id, admin_id),
FOREIGN KEY (field_id) REFERENCES FieldDef(id),
FOREIGN KEY (admin_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE FieldDef2Editor (
field_id INT NOT NULL,
editor_id INT UNSIGNED NOT NULL,
PRIMARY KEY (field_id, editor_id),
FOREIGN KEY (field_id) REFERENCES FieldDef(id),
FOREIGN KEY (editor_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE Issue (
id INT NOT NULL AUTO_INCREMENT,
shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
project_id SMALLINT UNSIGNED NOT NULL,
local_id INT NOT NULL,
reporter_id INT UNSIGNED NOT NULL,
owner_id INT UNSIGNED,
status_id INT,
-- These are each timestamps in seconds since the epoch.
modified INT NOT NULL,
opened INT,
closed INT,
owner_modified INT,
status_modified INT,
component_modified INT,
derived_owner_id INT UNSIGNED,
derived_status_id INT,
deleted BOOLEAN,
-- These are denormalized fields that should be updated when child
-- records are added or removed for stars or attachments. If they
-- get out of sync, they can be updated via an UPDATE ... SELECT statement.
star_count INT DEFAULT 0,
attachment_count INT DEFAULT 0,
is_spam BOOLEAN DEFAULT FALSE,
PRIMARY KEY(id),
UNIQUE KEY (project_id, local_id),
INDEX (shard, status_id),
INDEX (shard, project_id),
FOREIGN KEY (project_id) REFERENCES Project(project_id),
FOREIGN KEY (reporter_id) REFERENCES User(user_id),
FOREIGN KEY (owner_id) REFERENCES User(user_id),
FOREIGN KEY (status_id) REFERENCES StatusDef(id),
FOREIGN KEY (derived_owner_id) REFERENCES User(user_id)
) ENGINE=INNODB;
-- This is a parallel table to the Issue table because we don't want
-- any very wide columns in the Issue table that would slow it down.
CREATE TABLE IssueSummary (
issue_id INT NOT NULL,
summary mediumtext COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (issue_id),
FOREIGN KEY (issue_id) REFERENCES Issue(id)
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE Issue2Component (
issue_id INT NOT NULL,
issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
component_id INT NOT NULL,
derived BOOLEAN DEFAULT FALSE,
PRIMARY KEY (issue_id, component_id, derived),
INDEX (component_id, issue_shard),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (component_id) REFERENCES ComponentDef(id)
) ENGINE=INNODB;
CREATE TABLE Issue2Label (
issue_id INT NOT NULL,
issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
label_id INT NOT NULL,
derived BOOLEAN DEFAULT FALSE,
PRIMARY KEY (issue_id, label_id, derived),
INDEX (label_id, issue_shard),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (label_id) REFERENCES LabelDef(id)
) ENGINE=INNODB;
CREATE TABLE IssuePhaseDef (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) BINARY NOT NULL,
rank SMALLINT UNSIGNED,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE Issue2FieldValue (
issue_id INT NOT NULL,
issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
field_id INT NOT NULL,
int_value INT,
str_value VARCHAR(1024),
user_id INT UNSIGNED,
date_value INT,
url_value VARCHAR(1024),
derived BOOLEAN DEFAULT FALSE,
phase_id INT,
INDEX (issue_id, field_id),
INDEX (field_id, issue_shard, int_value),
INDEX (field_id, issue_shard, str_value(255)),
INDEX (field_id, issue_shard, user_id),
INDEX (field_id, issue_shard, date_value),
INDEX (field_id, issue_shard, url_value(255)),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (field_id) REFERENCES FieldDef(id),
FOREIGN KEY (user_id) REFERENCES User(user_id),
FOREIGN KEY (phase_id) REFERENCES IssuePhaseDef(id)
) ENGINE=INNODB;
CREATE TABLE Issue2Cc (
issue_id INT NOT NULL,
issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
cc_id INT UNSIGNED NOT NULL,
derived BOOLEAN DEFAULT FALSE,
PRIMARY KEY (issue_id, cc_id),
INDEX (cc_id, issue_shard),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (cc_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE Issue2Notify (
issue_id INT NOT NULL,
email VARCHAR(80) NOT NULL,
PRIMARY KEY (issue_id, email),
FOREIGN KEY (issue_id) REFERENCES Issue(id)
) ENGINE=INNODB;
CREATE TABLE IssueVisitHistory (
issue_id INT NOT NULL,
user_id INT UNSIGNED NOT NULL,
viewed INT NOT NULL,
PRIMARY KEY (user_id, issue_id),
INDEX (user_id, viewed),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE IssueStar (
issue_id INT NOT NULL,
user_id INT UNSIGNED NOT NULL,
PRIMARY KEY (issue_id, user_id),
INDEX (user_id),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE IssueRelation (
issue_id INT NOT NULL,
dst_issue_id INT NOT NULL,
-- Read as: src issue is blocked on dst issue.
kind ENUM ('blockedon', 'mergedinto') NOT NULL,
rank BIGINT,
PRIMARY KEY (issue_id, dst_issue_id, kind),
INDEX (issue_id),
INDEX (dst_issue_id),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (dst_issue_id) REFERENCES Issue(id)
) ENGINE=INNODB;
CREATE TABLE DanglingIssueRelation (
issue_id INT NOT NULL,
dst_issue_project VARCHAR(80),
dst_issue_local_id INT,
ext_issue_identifier VARCHAR(255),
-- This table uses 'blocking' so that it can guarantee the src issue
-- always exists, while the dst issue is always the dangling one.
kind ENUM ('blockedon', 'blocking', 'mergedinto') NOT NULL,
PRIMARY KEY (issue_id, dst_issue_project, dst_issue_local_id, kind, ext_issue_identifier),
INDEX (issue_id),
INDEX (ext_issue_identifier),
FOREIGN KEY (issue_id) REFERENCES Issue(id)
) ENGINE=INNODB;
CREATE TABLE CommentContent (
id INT NOT NULL AUTO_INCREMENT,
-- TODO(jrobbins): drop comment_id after Comment.commentcontent_id is added.
comment_id INT NOT NULL, -- Note: no forign key reference.
content MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
inbound_message MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (id)
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE Comment (
id INT NOT NULL AUTO_INCREMENT,
issue_id INT NOT NULL,
created INT NOT NULL,
project_id SMALLINT UNSIGNED NOT NULL,
commenter_id INT UNSIGNED NOT NULL,
commentcontent_id INT, -- TODO(jrobbins) make this NOT NULL.
deleted_by INT UNSIGNED,
is_spam BOOLEAN DEFAULT FALSE,
-- TODO(lukasperaza) Update first comments SET is_description=TRUE
is_description BOOLEAN DEFAULT FALSE,
PRIMARY KEY(id),
INDEX (is_spam, project_id, created),
INDEX (commenter_id, created),
INDEX (commenter_id, deleted_by, issue_id),
FOREIGN KEY (project_id) REFERENCES Project(project_id),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (commenter_id) REFERENCES User(user_id),
FOREIGN KEY (deleted_by) REFERENCES User(user_id),
FOREIGN KEY (commentcontent_id) REFERENCES CommentContent(id)
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE CommentImporter (
comment_id INT NOT NULL,
importer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (comment_id),
FOREIGN KEY (comment_id) REFERENCES Comment(id),
FOREIGN KEY (importer_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE Attachment (
id INT NOT NULL AUTO_INCREMENT,
issue_id INT NOT NULL,
comment_id INT,
filename VARCHAR(255) NOT NULL,
filesize INT NOT NULL,
mimetype VARCHAR(255) NOT NULL,
deleted BOOLEAN,
gcs_object_id VARCHAR(1024) NOT NULL,
PRIMARY KEY (id),
INDEX (issue_id),
INDEX (comment_id),
FOREIGN KEY (issue_id) REFERENCES Issue(id)
) ENGINE=INNODB;
CREATE TABLE IssueUpdate (
id INT NOT NULL AUTO_INCREMENT,
issue_id INT NOT NULL,
comment_id INT,
field ENUM (
'summary', 'status', 'owner', 'cc', 'labels', 'blockedon', 'blocking', 'mergedinto',
'project', 'components', 'custom', 'is_spam' ) NOT NULL,
old_value MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
new_value MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
added_user_id INT UNSIGNED,
removed_user_id INT UNSIGNED,
custom_field_name VARCHAR(255),
is_spam BOOLEAN DEFAULT FALSE,
PRIMARY KEY (id),
INDEX (issue_id),
INDEX (comment_id),
FOREIGN KEY (issue_id) REFERENCES Issue(id)
-- FOREIGN KEY (added_user_id) REFERENCES User(user_id),
-- FOREIGN KEY (removed_user_id) REFERENCES User(user_id)
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IssueFormerLocations (
issue_id INT NOT NULL,
project_id SMALLINT UNSIGNED NOT NULL,
local_id INT NOT NULL,
INDEX (issue_id),
UNIQUE KEY (project_id, local_id),
FOREIGN KEY (issue_id) REFERENCES Issue(id)
) ENGINE=INNODB;
CREATE TABLE Template (
id INT NOT NULL AUTO_INCREMENT,
project_id SMALLINT UNSIGNED NOT NULL,
name VARCHAR(255) BINARY NOT NULL,
content TEXT,
summary TEXT,
summary_must_be_edited BOOLEAN,
owner_id INT UNSIGNED,
status VARCHAR(255),
members_only BOOLEAN,
owner_defaults_to_member BOOLEAN,
component_required BOOLEAN DEFAULT FALSE,
PRIMARY KEY (id),
UNIQUE KEY (project_id, name),
FOREIGN KEY (project_id) REFERENCES Project(project_id)
) ENGINE=INNODB;
CREATE TABLE Template2Label (
template_id INT NOT NULL,
label VARCHAR(255) NOT NULL,
PRIMARY KEY (template_id, label),
FOREIGN KEY (template_id) REFERENCES Template(id)
) ENGINE=INNODB;
CREATE TABLE Template2Admin (
template_id INT NOT NULL,
admin_id INT UNSIGNED NOT NULL,
PRIMARY KEY (template_id, admin_id),
FOREIGN KEY (template_id) REFERENCES Template(id),
FOREIGN KEY (admin_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE Template2FieldValue (
template_id INT NOT NULL,
field_id INT NOT NULL,
int_value INT,
str_value VARCHAR(1024),
user_id INT UNSIGNED,
date_value INT,
url_value VARCHAR(1024),
INDEX (template_id, field_id),
FOREIGN KEY (template_id) REFERENCES Template(id),
FOREIGN KEY (field_id) REFERENCES FieldDef(id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE Template2Component (
template_id INT NOT NULL,
component_id INT NOT NULL,
PRIMARY KEY (template_id, component_id),
FOREIGN KEY (template_id) REFERENCES Template(id),
FOREIGN KEY (component_id) REFERENCES ComponentDef(id)
) ENGINE=INNODB;
CREATE TABLE Template2ApprovalValue (
approval_id INT NOT NULL,
template_id INT NOT NULL,
phase_id INT,
status ENUM ('needs_review', 'na', 'review_requested', 'review_started', 'need_info', 'approved', 'not_approved', 'not_set') DEFAULT 'not_set' NOT NULL,
PRIMARY KEY (approval_id, template_id),
FOREIGN KEY (approval_id) REFERENCES FieldDef(id),
FOREIGN KEY (template_id) REFERENCES Template(id),
FOREIGN KEY (phase_id) REFERENCES IssuePhaseDef(id)
) ENGINE=INNODB;
CREATE TABLE ProjectIssueConfig (
project_id SMALLINT UNSIGNED NOT NULL,
statuses_offer_merge VARCHAR(255) NOT NULL,
exclusive_label_prefixes VARCHAR(255) NOT NULL,
default_template_for_developers INT NOT NULL,
default_template_for_users INT NOT NULL,
default_col_spec TEXT,
default_sort_spec TEXT,
default_x_attr TEXT,
default_y_attr TEXT,
member_default_query TEXT,
custom_issue_entry_url TEXT,
PRIMARY KEY (project_id),
FOREIGN KEY (project_id) REFERENCES Project(project_id)
) ENGINE=INNODB;
CREATE TABLE FilterRule (
project_id SMALLINT UNSIGNED NOT NULL,
rank SMALLINT UNSIGNED,
-- TODO: or should this be broken down into structured fields?
predicate TEXT NOT NULL,
-- TODO: or should this be broken down into structured fields?
consequence TEXT NOT NULL,
INDEX (project_id),
FOREIGN KEY (project_id) REFERENCES Project(project_id)
) ENGINE=INNODB;
-- Each row in this table indicates an issue that needs to be reindexed
-- in the GAE fulltext index by our batch indexing cron job.
CREATE TABLE ReindexQueue (
issue_id INT NOT NULL,
created TIMESTAMP,
PRIMARY KEY (issue_id),
INDEX (created),
FOREIGN KEY (issue_id) REFERENCES Issue(id)
) ENGINE=INNODB;
-- This holds counters with the highest issue local_id that is
-- already used in each project. Clients should atomically increment
-- the value for current project and then use the new counter value
-- when creating an issue.
CREATE TABLE LocalIDCounter (
project_id SMALLINT UNSIGNED NOT NULL,
used_local_id INT NOT NULL,
used_spam_id INT NOT NULL,
PRIMARY KEY (project_id),
FOREIGN KEY (project_id) REFERENCES Project(project_id)
) ENGINE=INNODB;
-- This is a saved query. It can be configured by a project owner to
-- be used by all visitors to that project. Or, it can be a a
-- personal saved query that appears on a user's "Saved queries" page
-- and executes in the scope of one or more projects.
CREATE TABLE SavedQuery (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(80) NOT NULL,
-- For now, we only allow saved queries to be based off ane of the built-in
-- query scopes, and those can never be deleted, so there can be no nesting,
-- dangling references, and thus no need for cascading deletes.
base_query_id INT,
query TEXT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
-- Rows for built-in queries. These are in the database soley so that
-- foreign key constraints are satisfied. These rows ar never read or updated.
INSERT IGNORE INTO SavedQuery VALUES
(1, 'All issues', 0, ''),
(2, 'Open issues', 0, 'is:open'),
(3, 'Open and owned by me', 0, 'is:open owner:me'),
(4, 'Open and reported by me', 0, 'is:open reporter:me'),
(5, 'Open and starred by me', 0, 'is:open is:starred'),
(6, 'New issues', 0, 'status:new'),
(7, 'Issues to verify', 0, 'status=fixed,done'),
(8, 'Open with comment by me', 0, 'is:open commentby:me');
-- The sole purpose of this statement is to force user defined saved queries
-- to have IDs greater than 100 so that 1-100 are reserved for built-ins.
INSERT IGNORE INTO SavedQuery VALUES (100, '', 0, '');
-- User personal queries default to executing in the context of the
-- project where they were created, but the user can edit them to make
-- them into cross-project queries. Project saved queries always
-- implicitly execute in the context of a project.
CREATE TABLE SavedQueryExecutesInProject (
query_id INT NOT NULL,
project_id SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (query_id, project_id),
INDEX (project_id),
FOREIGN KEY (project_id) REFERENCES Project(project_id),
FOREIGN KEY (query_id) REFERENCES SavedQuery(id)
) ENGINE=INNODB;
-- These are the queries edited by the project owner on the project
-- admin pages.
CREATE TABLE Project2SavedQuery (
project_id SMALLINT UNSIGNED NOT NULL,
rank SMALLINT UNSIGNED NOT NULL,
query_id INT NOT NULL,
-- TODO(jrobbins): visibility: owners, committers, contributors, anyone
PRIMARY KEY (project_id, rank),
FOREIGN KEY (project_id) REFERENCES Project(project_id),
FOREIGN KEY (query_id) REFERENCES SavedQuery(id)
) ENGINE=INNODB;
-- These are personal saved queries.
CREATE TABLE User2SavedQuery (
user_id INT UNSIGNED NOT NULL,
rank SMALLINT UNSIGNED NOT NULL,
query_id INT NOT NULL,
-- TODO(jrobbins): daily and weekly digests, and the ability to have
-- certain subscriptions go to username+SOMETHING@example.com.
subscription_mode ENUM ('noemail', 'immediate') DEFAULT 'noemail' NOT NULL,
PRIMARY KEY (user_id, rank),
FOREIGN KEY (user_id) REFERENCES User(user_id),
FOREIGN KEY (query_id) REFERENCES SavedQuery(id)
) ENGINE=INNODB;
-- Created whenever a user reports an issue or comment as spam.
-- Note this is distinct from a SpamVerdict, which is issued by
-- the system rather than a human user.
CREATE TABLE SpamReport (
-- when this report was generated
created TIMESTAMP NOT NULL,
-- when the reported content was generated
-- TODO(jrobbins): needs default current_time in MySQL 5.7.
content_created TIMESTAMP NOT NULL,
-- id of the reporting user
user_id INT UNSIGNED NOT NULL,
-- id of the reported user
reported_user_id INT UNSIGNED NOT NULL,
-- either this or issue_id must be set
comment_id INT,
-- either this or comment_id must be set
issue_id INT,
INDEX (issue_id),
INDEX (comment_id),
UNIQUE (user_id, comment_id, issue_id),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (comment_id) REFERENCES Comment(id)
) ENGINE=INNODB;
-- Any time a human or the system sets is_spam to true,
-- or changes it from true to false, we want to have a
-- record of who did it and why.
CREATE TABLE SpamVerdict (
-- when this verdict was generated
created TIMESTAMP NOT NULL,
-- id of the reporting user, may be null if it was
-- an automatic classification.
user_id INT UNSIGNED,
-- id of the containing project.
project_id INT NOT NULL,
-- either this or issue_id must be set.
comment_id INT,
-- either this or comment_id must be set.
issue_id INT,
-- If the classifier issued the verdict, this should be set.
classifier_confidence FLOAT,
-- This should reflect the new is_spam value that was applied
-- by this verdict, not the value it had prior.
is_spam BOOLEAN NOT NULL,
-- manual: a project owner marked it as spam.
-- threshhold: number of SpamReports from non-members was exceeded.
-- classifier: the automatic classifier reports it as spam.
-- fail_open: the classifier failed, resulting in a ham decision.
reason ENUM ("manual", "threshold", "classifier", "fail_open") NOT NULL,
overruled BOOL NOT NULL,
-- True indicates that the prediction service PRC failed and we gave up.
fail_open BOOL DEFAULT FALSE,
INDEX (issue_id),
INDEX (comment_id),
INDEX (classifier_confidence),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (comment_id) REFERENCES Comment(id)
) ENGINE=INNODB;
-- These are user-curated lists of issues which can be re-ordered to
-- prioritize work.
CREATE TABLE Hotlist (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(80) NOT NULL,
summary TEXT,
description TEXT,
is_private BOOLEAN DEFAULT FALSE,
is_deleted BOOLEAN DEFAULT FALSE,
default_col_spec TEXT,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE Hotlist2Issue (
hotlist_id INT UNSIGNED NOT NULL,
issue_id INT NOT NULL,
rank BIGINT NOT NULL,
adder_id INT UNSIGNED,
added INT,
note TEXT,
PRIMARY KEY (hotlist_id, issue_id),
INDEX (hotlist_id),
INDEX (issue_id),
FOREIGN KEY (hotlist_id) REFERENCES Hotlist(id),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (adder_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE Hotlist2User (
hotlist_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
role_name ENUM ('owner', 'editor', 'follower') NOT NULL,
PRIMARY KEY (hotlist_id, user_id),
INDEX (hotlist_id),
INDEX (user_id),
FOREIGN KEY (hotlist_id) REFERENCES Hotlist(id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE HotlistStar (
hotlist_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
PRIMARY KEY (hotlist_id, user_id),
INDEX (user_id),
FOREIGN KEY (hotlist_id) REFERENCES Hotlist(id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE HotlistVisitHistory (
hotlist_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
viewed INT NOT NULL,
PRIMARY KEY (user_id, hotlist_id),
FOREIGN KEY (hotlist_id) REFERENCES Hotlist(id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE ComponentIssueClosedIndex (
closed_index INT NOT NULL,
PRIMARY KEY (closed_index)
) ENGINE=INNODB;
CREATE TABLE ApprovalDef2Approver (
approval_id INT NOT NULL,
approver_id INT UNSIGNED NOT NULL,
project_id SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (approval_id, approver_id),
FOREIGN KEY (approval_id) REFERENCES FieldDef(id),
FOREIGN KEY (approver_id) REFERENCES User(user_id),
FOREIGN KEY (project_id) REFERENCES Project(project_id)
) ENGINE=INNODB;
CREATE TABLE ApprovalDef2Survey (
approval_id INT NOT NULL,
survey TEXT,
project_id SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (approval_id),
FOREIGN KEY (approval_id) REFERENCES FieldDef(id),
FOREIGN KEY (project_id) REFERENCES Project(project_id)
) ENGINE=INNODB;
CREATE TABLE Issue2ApprovalValue (
issue_id INT NOT NULL,
approval_id INT NOT NULL,
phase_id INT,
status ENUM ('needs_review', 'na', 'review_requested', 'review_started', 'need_info', 'approved', 'not_approved', 'not_set') DEFAULT 'not_set' NOT NULL,
setter_id INT UNSIGNED,
set_on INT,
PRIMARY KEY (issue_id, approval_id),
FOREIGN KEY (setter_id) REFERENCES User(user_id),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (approval_id) REFERENCES FieldDef(id),
FOREIGN KEY (phase_id) REFERENCES IssuePhaseDef(id)
) ENGINE=INNODB;
CREATE TABLE IssueApproval2Approver (
issue_id INT NOT NULL,
approval_id INT NOT NULL,
approver_id INT UNSIGNED NOT NULL,
PRIMARY KEY (issue_id, approval_id, approver_id),
FOREIGN KEY (issue_id) REFERENCES Issue(id),
FOREIGN KEY (approval_id) REFERENCES FieldDef(id),
FOREIGN KEY (approver_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE IssueApproval2Comment (
approval_id INT NOT NULL,
comment_id INT NOT NULL,
PRIMARY KEY (comment_id),
INDEX (approval_id),
FOREIGN KEY (approval_id) REFERENCES FieldDef(id),
FOREIGN KEY (comment_id) REFERENCES Comment(id)
) ENGINE=INNODB;
CREATE TABLE IssueSnapshot (
id INT NOT NULL AUTO_INCREMENT,
issue_id INT NOT NULL,
shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
project_id SMALLINT UNSIGNED NOT NULL,
local_id INT NOT NULL,
reporter_id INT UNSIGNED NOT NULL,
owner_id INT UNSIGNED,
status_id INT,
period_start INT UNSIGNED NOT NULL,
period_end INT UNSIGNED NOT NULL,
is_open BOOLEAN DEFAULT TRUE,
PRIMARY KEY (id),
FOREIGN KEY (project_id) REFERENCES Project(project_id),
FOREIGN KEY (reporter_id) REFERENCES User(user_id),
FOREIGN KEY (owner_id) REFERENCES User(user_id),
FOREIGN KEY (status_id) REFERENCES StatusDef(id),
INDEX (shard, project_id, period_start, period_end),
INDEX by_period_start (shard, project_id, status_id, period_start),
INDEX by_period_end (shard, project_id, status_id, period_end),
KEY (issue_id, period_start, period_end)
) ENGINE=INNODB;
CREATE TABLE IssueSnapshot2Component (
issuesnapshot_id INT NOT NULL,
component_id INT NOT NULL,
PRIMARY KEY (issuesnapshot_id, component_id),
FOREIGN KEY (issuesnapshot_id) REFERENCES IssueSnapshot(id),
FOREIGN KEY (component_id) REFERENCES ComponentDef(id)
) ENGINE=INNODB;
CREATE TABLE IssueSnapshot2Label(
issuesnapshot_id INT NOT NULL,
label_id INT NOT NULL,
PRIMARY KEY (issuesnapshot_id, label_id),
FOREIGN KEY (issuesnapshot_id) REFERENCES IssueSnapshot(id),
FOREIGN KEY (label_id) REFERENCES LabelDef(id)
) ENGINE=INNODB;
CREATE TABLE IssueSnapshot2Cc(
issuesnapshot_id INT NOT NULL,
cc_id INT UNSIGNED NOT NULL,
PRIMARY KEY (issuesnapshot_id, cc_id),
FOREIGN KEY (issuesnapshot_id) REFERENCES IssueSnapshot(id),
FOREIGN KEY (cc_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE IssueSnapshot2Hotlist(
issuesnapshot_id INT NOT NULL,
hotlist_id INT UNSIGNED NOT NULL,
PRIMARY KEY (issuesnapshot_id, hotlist_id),
FOREIGN KEY (issuesnapshot_id) REFERENCES IssueSnapshot(id),
FOREIGN KEY (hotlist_id) REFERENCES Hotlist(id)
) ENGINE=INNODB;