Chromium Code Reviews
chromiumcodereview-hr@appspot.gserviceaccount.com (chromiumcodereview-hr) | Please choose your nickname with Settings | Help | Chromium Project | Gerrit Changes | Sign out
(277)

Unified Diff: appengine/monorail/sql/tracker.sql

Issue 1868553004: Open Source Monorail (Closed) Base URL: https://chromium.googlesource.com/infra/infra.git@master
Patch Set: Rebase Created 4 years, 8 months ago
Use n/p to move between diff chunks; N/P to move between comments. Draft comments are only viewable by you.
Jump to:
View side-by-side diff with in-line comments
Download patch
« no previous file with comments | « appengine/monorail/sql/queries.sql ('k') | appengine/monorail/static/css/d_sb.css » ('j') | no next file with comments »
Expand Comments ('e') | Collapse Comments ('c') | Show Comments Hide Comments ('s')
Index: appengine/monorail/sql/tracker.sql
diff --git a/appengine/monorail/sql/tracker.sql b/appengine/monorail/sql/tracker.sql
new file mode 100644
index 0000000000000000000000000000000000000000..27c9e444106fbfa5f77822b154be407af1065271
--- /dev/null
+++ b/appengine/monorail/sql/tracker.sql
@@ -0,0 +1,646 @@
+-- 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,
+
+ PRIMARY KEY (id),
+ UNIQUE KEY (project_id, path),
+ FOREIGN KEY (project_id) REFERENCES Project(project_id),
+ FOREIGN KEY (creator_id) REFERENCES User(user_id),
+ FOREIGN KEY (modifier_id) REFERENCES User(user_id)
+) 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,
+ 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 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') 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_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,
+
+ -- TODO(jrobbins): default value
+ -- TODO(jrobbins): deprecated boolean?
+ docstring TEXT,
+ is_deleted BOOLEAN, -- If true, reap this field def after all values reaped.
+
+ 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 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,
+
+ 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 TEXT,
+
+ PRIMARY KEY (issue_id),
+ FOREIGN KEY (issue_id) REFERENCES Issue(id)
+) ENGINE=INNODB;
+
+
+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 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,
+
+ derived BOOLEAN DEFAULT FALSE,
+
+ 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),
+
+ FOREIGN KEY (issue_id) REFERENCES Issue(id),
+ FOREIGN KEY (field_id) REFERENCES FieldDef(id),
+ FOREIGN KEY (user_id) REFERENCES User(user_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 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,
+
+ 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,
+
+ -- 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),
+ INDEX (issue_id),
+ FOREIGN KEY (issue_id) REFERENCES Issue(id)
+) ENGINE=INNODB;
+
+
+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,
+ content TEXT,
+ inbound_message TEXT,
+
+ was_escaped BOOLEAN DEFAULT FALSE,
+ deleted_by INT UNSIGNED,
+ is_spam BOOLEAN DEFAULT FALSE,
+
+ PRIMARY KEY(id),
+ INDEX (is_spam, project_id, created),
+ INDEX (commenter_id, created),
+
+ 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)
+) 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 TEXT,
+ new_value TEXT,
+ 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;
+
+
+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,
+
+ 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 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,
+
+ 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),
+ 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),
+ 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,
+
+ -- owner: a project owner marked it as spam
+ -- threshhold: number of SpamReports from non-members was exceeded.
+ -- classifier: the automatic classifier reports it as spam.
+ reason ENUM ("manual", "threshold", "classifier") NOT NULL,
+
+ overruled BOOL NOT NULL,
+
+ 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;
« no previous file with comments | « appengine/monorail/sql/queries.sql ('k') | appengine/monorail/static/css/d_sb.css » ('j') | no next file with comments »

Powered by Google App Engine
This is Rietveld 408576698