| # Copyright 2016 The Chromium Authors. All rights reserved. |
| # Use of this source code is govered by a BSD-style |
| # license that can be found in the LICENSE file or at |
| # https://developers.google.com/open-source/licenses/bsd |
| |
| This file contains a log of ALTER TABLE statements that need to be executed |
| to bring a Monorail SQL database up to the current schema. |
| |
| ================================================================ |
| 2012-05-24: Added more Project fields. |
| |
| ALTER TABLE Project ADD COLUMN read_only_reason VARCHAR(80); |
| ALTER TABLE Project ADD COLUMN issue_notify_address VARCHAR(80); |
| ALTER TABLE Project ADD COLUMN attachment_bytes_used INT DEFAULT 0; |
| ALTER TABLE Project ADD COLUMN attachment_quota INT DEFAULT 52428800; |
| ALTER TABLE Project ADD COLUMN moved_to VARCHAR(250); |
| ALTER TABLE Project ADD COLUMN process_inbound_email BOOLEAN DEFAULT FALSE; |
| |
| ================================================================ |
| 2012-06-01: Added inbound_message for issue comments |
| |
| ALTER TABLE Comment ADD COLUMN inbound_message TEXT; |
| |
| |
| ================================================================ |
| 2012-06-05: Removed send_notifications_from_user because Monorail will |
| not offer that feature any time soon. |
| |
| ALTER TABLE ProjectIssueConfig DROP COLUMN send_notifications_from_user; |
| |
| |
| ================================================================ |
| 2012-06-05: Add initial subscription options. |
| |
| ALTER TABLE User2SavedQuery ADD COLUMN subscription_mode |
| ENUM ('noemail', 'immediate') DEFAULT 'noemail' NOT NULL; |
| |
| |
| ================================================================ |
| 2012-07-02: Revised project states and added state_reason and delete_time |
| |
| ALTER TABLE Project MODIFY COLUMN state ENUM ('live', 'archived', 'deletable') |
| NOT NULL; |
| |
| ALTER TABLE Project ADD COLUMN state_reason VARCHAR(80); |
| ALTER TABLE Project ADD COLUMN delete_time INT; |
| |
| |
| ================================================================ |
| 2012-07-05: Added action limits and dismissed cues |
| |
| CREATE TABLE ActionLimit ( |
| user_id INT NOT NULL AUTO_INCREMENT, |
| action_kind ENUM ( |
| 'project_creation', 'issue_comment', 'issue_attachment', |
| 'issue_bulk_edit'), |
| recent_count INT, |
| reset_timestamp INT, |
| lifetime_count INT, |
| lifetime_limit INT, |
| |
| PRIMARY KEY (user_id, action_kind) |
| ) ENGINE=INNODB; |
| |
| |
| CREATE TABLE DismissedCues ( |
| user_id INT NOT NULL AUTO_INCREMENT, |
| cue VARCHAR(40), -- names of the cue cards that the user has dismissed. |
| |
| INDEX (user_id) |
| ) ENGINE=INNODB; |
| |
| |
| ALTER TABLE User ADD COLUMN ignore_action_limits BOOLEAN DEFAULT FALSE; |
| |
| ================================================================ |
| 2012-07-11: No longer using Counter table. |
| |
| DROP TABLE Counter; |
| |
| ================================================================ |
| 2012-09-06: Drop AttachmentContent, put blobkey in Attachment |
| and drop some redundant columns. |
| |
| Note: This loses attachment data that might currently be in your |
| instance. Good thing these schema refinements are getting done |
| before launch. |
| |
| ALTER TABLE Attachment DROP COLUMN attachment_id; |
| ALTER TABLE Attachment DROP COLUMN comment_created; |
| ALTER TABLE Attachment ADD COLUMN blobkey VARCHAR(1024) NOT NULL; |
| |
| DROP TABLE AttachmentContent; |
| |
| ALTER TABLE IssueUpdate DROP COLUMN comment_created; |
| |
| |
| ================================================================ |
| 2012-11-01: Add Components to IssueUpdate enum. |
| |
| alter table IssueUpdate modify field ENUM ('summary', 'status', 'owner', |
| 'cc', 'labels', 'blockedon', 'blocking', 'mergedinto', 'project', |
| 'components') NOT NULL; |
| |
| |
| ================================================================ |
| 2012-12-10: Add template admins and field admins |
| |
| |
| CREATE TABLE FieldDef2Admin ( |
| field_id INT NOT NULL, |
| admin_id INT 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 Template2Admin ( |
| template_id INT NOT NULL, |
| admin_id INT 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; |
| |
| |
| ================================================================ |
| 2012-12-14: Add a table of custom field values |
| |
| ALTER TABLE FieldDef MODIFY field_type ENUM ( |
| 'enum_type', 'int_type', 'str_type', 'user_type') NOT NULL; |
| |
| CREATE TABLE Issue2FieldValue ( |
| iid INT NOT NULL, |
| field_id INT NOT NULL, |
| |
| int_value INT, |
| str_value VARCHAR(1024), |
| user_id INT, |
| |
| derived BOOLEAN DEFAULT FALSE, |
| |
| INDEX (iid, field_id), |
| INDEX (field_id, int_value), |
| INDEX (field_id, str_value), |
| INDEX (field_id, user_id), |
| |
| FOREIGN KEY (iid) REFERENCES Issue(id), |
| -- FOREIGN KEY (field_id) REFERENCES FieldDef(id), |
| FOREIGN KEY (user_id) REFERENCES User(user_id) |
| ) ENGINE=INNODB; |
| |
| |
| ================================================================ |
| 2012-12-18: persistence for update objects on custom fields |
| |
| ALTER TABLE IssueUpdate MODIFY field ENUM ( |
| 'summary', 'status', 'owner', 'cc', 'labels', 'blockedon', 'blocking', 'mergedinto', |
| 'project', 'components', 'custom' ) NOT NULL; |
| |
| ALTER TABLE IssueUpdate ADD custom_field_name VARCHAR(255); |
| |
| |
| ================================================================ |
| 2012-12-27: Rename component owner to component admin |
| |
| DROP TABLE Component2Owner; |
| |
| CREATE TABLE Component2Admin ( |
| component_id SMALLINT UNSIGNED NOT NULL, |
| admin_id INT 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; |
| |
| |
| ================================================================ |
| 2013-01-20: add field applicability predicate |
| |
| ALTER TABLE FieldDef ADD applicable_type VARCHAR(80); |
| ALTER TABLE FieldDef ADD applicable_predicate TEXT; |
| |
| ================================================================ |
| 2013-01-25: add field validation details |
| |
| ALTER TABLE FieldDef ADD max_value INT; |
| ALTER TABLE FieldDef ADD min_value INT; |
| ALTER TABLE FieldDef ADD regex VARCHAR(80); |
| ALTER TABLE FieldDef ADD needs_member BOOLEAN; |
| ALTER TABLE FieldDef ADD needs_perm VARCHAR(80); |
| |
| |
| ================================================================ |
| 2013-02-11: add grant and notify to user-valued fields |
| |
| ALTER TABLE FieldDef ADD grants_perm VARCHAR(80); |
| ALTER TABLE FieldDef ADD notify_on ENUM ('never', 'any_comment') DEFAULT 'never' NOT NULL; |
| |
| |
| ================================================================ |
| 2013-03-17: Add Template2FieldValue |
| |
| CREATE TABLE Template2FieldValue ( |
| template_id INT NOT NULL, |
| field_id INT NOT NULL, |
| |
| int_value INT, |
| str_value VARCHAR(1024), |
| user_id INT, |
| |
| 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; |
| |
| |
| ================================================================ |
| 2013-05-08: eliminated same_org_only |
| |
| -- This needs to be done on all shards. |
| UPDATE Project SET access = 'members_only' WHERE access = 'same_org_only'; |
| ALTER TABLE Project MODIFY COLUMN access ENUM ('anyone', 'members_only'); |
| |
| ================================================================ |
| 2013-05-08: implemented recent activity timestamp |
| |
| -- This needs to be done on all shards. |
| ALTER TABLE Project ADD recent_activity_timestamp INT; |
| |
| ================================================================ |
| 2013-07-01: use BIGINT for Invalidate timesteps |
| |
| ALTER TABLE Invalidate MODIFY COLUMN timestep BIGINT NOT NULL AUTO_INCREMENT; |
| |
| |
| ================================================================ |
| 2013-07-23: renamed to avoid "participant" |
| |
| RENAME TABLE ParticipantDuty TO MemberDuty; |
| RENAME TABLE ParticipantNotes TO MemberNotes; |
| |
| ================================================================ |
| 2013-08-22: renamed issue_id to local_id |
| |
| -- On master and all shards |
| ALTER TABLE Issue CHANGE issue_id local_id INT NOT NULL; |
| |
| -- On master only |
| ALTER TABLE IssueFormerLocations CHANGE issue_id local_id INT NOT NULL; |
| |
| ================================================================ |
| 2013-08-24: renamed iid to issue_id |
| |
| -- On master and all shards |
| |
| ALTER TABLE IssueSummary DROP FOREIGN KEY IssueSummary_ibfk_1; |
| ALTER TABLE IssueSummary CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE IssueSummary ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| ALTER TABLE Issue2Label DROP FOREIGN KEY Issue2Label_ibfk_1; |
| ALTER TABLE Issue2Label CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE Issue2Label ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| ALTER TABLE Issue2Component DROP FOREIGN KEY Issue2Component_ibfk_1; |
| ALTER TABLE Issue2Component CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE Issue2Component ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| ALTER TABLE Issue2Cc DROP FOREIGN KEY Issue2Cc_ibfk_1; |
| ALTER TABLE Issue2Cc CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE Issue2Cc ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| ALTER TABLE Issue2Notify DROP FOREIGN KEY Issue2Notify_ibfk_1; |
| ALTER TABLE Issue2Notify CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE Issue2Notify ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| ALTER TABLE IssueStar DROP FOREIGN KEY IssueStar_ibfk_1; |
| ALTER TABLE IssueStar CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE IssueStar ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| ALTER TABLE IssueRelation DROP FOREIGN KEY IssueRelation_ibfk_1; |
| ALTER TABLE IssueRelation CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE IssueRelation ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| ALTER TABLE IssueRelation CHANGE dst_iid dst_issue_id INT NOT NULL; |
| |
| ALTER TABLE Issue2FieldValue DROP FOREIGN KEY Issue2FieldValue_ibfk_1; |
| ALTER TABLE Issue2FieldValue CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE Issue2FieldValue ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| -- On master only |
| ALTER TABLE Comment DROP FOREIGN KEY Comment_ibfk_2; |
| ALTER TABLE Comment CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE Comment ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| ALTER TABLE Attachment DROP FOREIGN KEY Attachment_ibfk_1; |
| ALTER TABLE Attachment CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE Attachment ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| ALTER TABLE IssueUpdate DROP FOREIGN KEY IssueUpdate_ibfk_1; |
| ALTER TABLE IssueUpdate CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE IssueUpdate ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| -- I was missing a foreign key constraint here. Adding now. |
| ALTER TABLE IssueFormerLocations CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE IssueFormerLocations ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| -- I was missing a foreign key constraint here. Adding now. |
| ALTER TABLE ReindexQueue CHANGE iid issue_id INT NOT NULL; |
| ALTER TABLE ReindexQueue ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| |
| |
| ================================================================ |
| 2013-08-30: added per-project email sending flag |
| |
| -- On master and all shards |
| ALTER TABLE Project ADD COLUMN deliver_outbound_email BOOLEAN DEFAULT FALSE; |
| |
| |
| ================================================================ |
| 2013-10-30: renamed prompts to templates |
| |
| ALTER TABLE ProjectIssueConfig |
| CHANGE default_prompt_for_developers default_template_for_developers INT NOT NULL; |
| |
| ALTER TABLE ProjectIssueConfig |
| CHANGE default_prompt_for_users default_template_for_users INT NOT NULL; |
| |
| ALTER TABLE Template |
| CHANGE prompt_name name VARCHAR(255) NOT NULL, |
| CHANGE prompt_text content TEXT, |
| CHANGE prompt_summary summary TEXT, |
| CHANGE prompt_summary_must_be_edited summary_must_be_edited BOOLEAN, |
| CHANGE prompt_owner_id owner_id INT, |
| CHANGE prompt_status status VARCHAR(255), |
| CHANGE prompt_members_only members_only BOOLEAN; |
| |
| |
| ================================================================ |
| 2013-11-18: add LocalIDCounter to master DB only, and fill in values. |
| |
| CREATE TABLE LocalIDCounter ( |
| project_id SMALLINT UNSIGNED NOT NULL, |
| used_local_id INT NOT NULL, |
| |
| PRIMARY KEY (project_id), |
| FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| ) ENGINE=INNODB; |
| |
| |
| -- Note: this ignores former issue locations, so it can only be run |
| -- now, before the "move issue" feature is offered. |
| REPLACE INTO LocalIDCounter |
| SELECT project_id, MAX(local_id) |
| FROM Issue |
| GROUP BY project_id; |
| |
| ================================================================ |
| 2015-06-12: add issue_id to Invalidate's enum for kind. |
| |
| ALTER TABLE Invalidate CHANGE kind kind ENUM('user', 'project', 'issue', 'issue_id'); |
| |
| ================================================================ |
| 2015-07-24: Rename blobkey to gcs_object_id because we are using |
| Google Cloud storage now. |
| |
| ALTER TABLE Attachment CHANGE blobkey gcs_object_id VARCHAR(1024) NOT NULL; |
| |
| =============================================================== |
| 2015-08-14: Use MurmurHash3 to deterministically generate user ids. |
| |
| -- First, drop foreign key constraints, then alter the keys, then |
| -- add back the foreign key constraints. |
| |
| ALTER TABLE User2Project DROP FOREIGN KEY user2project_ibfk_2; |
| ALTER TABLE ExtraPerm DROP FOREIGN KEY extraperm_ibfk_2; |
| ALTER TABLE MemberNotes DROP FOREIGN KEY membernotes_ibfk_2; |
| ALTER TABLE UserStar DROP FOREIGN KEY userstar_ibfk_1; |
| ALTER TABLE UserStar DROP FOREIGN KEY userstar_ibfk_2; |
| ALTER TABLE ProjectStar DROP FOREIGN KEY projectstar_ibfk_1; |
| ALTER TABLE UserGroup DROP FOREIGN KEY usergroup_ibfk_1; |
| ALTER TABLE UserGroup DROP FOREIGN KEY usergroup_ibfk_2; |
| ALTER TABLE UserGroupSettings DROP FOREIGN KEY usergroupsettings_ibfk_1; |
| ALTER TABLE QuickEditHistory DROP FOREIGN KEY quickedithistory_ibfk_2; |
| ALTER TABLE QuickEditMostRecent DROP FOREIGN KEY quickeditmostrecent_ibfk_2; |
| ALTER TABLE Issue DROP FOREIGN KEY issue_ibfk_2; |
| ALTER TABLE Issue DROP FOREIGN KEY issue_ibfk_3; |
| ALTER TABLE Issue DROP FOREIGN KEY issue_ibfk_4; |
| ALTER TABLE Issue2Cc DROP FOREIGN KEY issue2cc_ibfk_2; |
| ALTER TABLE IssueStar DROP FOREIGN KEY issuestar_ibfk_1; -- ? |
| ALTER TABLE Issue2FieldValue DROP FOREIGN KEY issue2fieldvalue_ibfk_2; |
| ALTER TABLE Comment DROP FOREIGN KEY comment_ibfk_3; |
| ALTER TABLE Comment DROP FOREIGN KEY comment_ibfk_4; |
| ALTER TABLE FieldDef2Admin DROP FOREIGN KEY fielddef2admin_ibfk_2; |
| ALTER TABLE Template2Admin DROP FOREIGN KEY template2admin_ibfk_2; |
| ALTER TABLE Template2FieldValue DROP FOREIGN KEY template2fieldvalue_ibfk_2; |
| ALTER TABLE Component2Admin DROP FOREIGN KEY component2admin_ibfk_2; |
| ALTER TABLE Component2Cc DROP FOREIGN KEY component2cc_ibfk_2; |
| ALTER TABLE User2SavedQuery DROP FOREIGN KEY user2savedquery_ibfk_1; |
| |
| |
| ALTER TABLE User MODIFY user_id INT UNSIGNED NOT NULL; |
| ALTER TABLE ActionLimit MODIFY user_id INT UNSIGNED NOT NULL; |
| ALTER TABLE DismissedCues MODIFY user_id INT UNSIGNED NOT NULL; |
| ALTER TABLE User2Project MODIFY user_id INT UNSIGNED NOT NULL; |
| ALTER TABLE ExtraPerm MODIFY user_id INT UNSIGNED NOT NULL; |
| ALTER TABLE MemberNotes MODIFY user_id INT UNSIGNED NOT NULL; |
| ALTER TABLE UserStar MODIFY starred_user_id INT UNSIGNED NOT NULL, |
| MODIFY user_id INT UNSIGNED NOT NULL; |
| ALTER TABLE ProjectStar MODIFY user_id INT UNSIGNED NOT NULL; |
| ALTER TABLE UserGroup MODIFY user_id INT UNSIGNED NOT NULL; |
| ALTER TABLE UserGroup MODIFY group_id INT UNSIGNED NOT NULL; |
| ALTER TABLE UserGroupSettings MODIFY group_id INT UNSIGNED NOT NULL; |
| ALTER TABLE QuickEditHistory MODIFY user_id INT UNSIGNED NOT NULL; |
| ALTER TABLE QuickEditMostRecent MODIFY user_id INT UNSIGNED NOT NULL; |
| ALTER TABLE Issue MODIFY reporter_id INT UNSIGNED NOT NULL, |
| MODIFY owner_id INT UNSIGNED, |
| MODIFY derived_owner_id INT UNSIGNED; |
| ALTER TABLE Issue2Cc MODIFY cc_id INT UNSIGNED NOT NULL; |
| ALTER TABLE IssueStar MODIFY user_id INT UNSIGNED NOT NULL; |
| ALTER TABLE Issue2FieldValue MODIFY user_id INT UNSIGNED; |
| ALTER TABLE Comment MODIFY commenter_id INT UNSIGNED NOT NULL; |
| ALTER TABLE Comment MODIFY deleted_by INT UNSIGNED; |
| ALTER TABLE IssueUpdate MODIFY added_user_id INT UNSIGNED, |
| MODIFY removed_user_id INT UNSIGNED; |
| ALTER TABLE Template MODIFY owner_id INT UNSIGNED; |
| ALTER TABLE FieldDef2Admin MODIFY admin_id INT UNSIGNED NOT NULL; |
| ALTER TABLE Template2Admin MODIFY admin_id INT UNSIGNED NOT NULL; |
| ALTER TABLE Template2FieldValue MODIFY user_id INT UNSIGNED; |
| ALTER TABLE Component2Admin MODIFY admin_id INT UNSIGNED NOT NULL; |
| ALTER TABLE Component2Cc MODIFY cc_id INT UNSIGNED NOT NULL; |
| ALTER TABLE User2SavedQuery MODIFY user_id INT UNSIGNED NOT NULL; |
| |
| ALTER TABLE User2Project ADD CONSTRAINT user2project_ibfk_2 FOREIGN KEY (user_id) REFERENCES User(user_id); |
| ALTER TABLE ExtraPerm ADD CONSTRAINT extraperm_ibfk_2 FOREIGN KEY (user_id) REFERENCES User(user_id); |
| ALTER TABLE MemberNotes ADD CONSTRAINT membernotes_ibfk_2 FOREIGN KEY (user_id) REFERENCES User(user_id); |
| ALTER TABLE UserStar ADD CONSTRAINT userstar_ibfk_1 FOREIGN KEY (user_id) REFERENCES User(user_id); |
| ALTER TABLE UserStar ADD CONSTRAINT userstar_ibfk_2 FOREIGN KEY (starred_user_id) REFERENCES User(user_id); |
| ALTER TABLE ProjectStar ADD CONSTRAINT projectstar_ibfk_1 FOREIGN KEY (user_id) REFERENCES User(user_id); |
| ALTER TABLE UserGroup ADD CONSTRAINT usergroup_ibfk_1 FOREIGN KEY (user_id) REFERENCES User(user_id); |
| ALTER TABLE UserGroup ADD CONSTRAINT usergroup_ibfk_2 FOREIGN KEY (group_id) REFERENCES User(user_id); |
| ALTER TABLE UserGroupSettings ADD CONSTRAINT usergroupsettings_ibfk_1 FOREIGN KEY (group_id) REFERENCES User(user_id); |
| ALTER TABLE QuickEditHistory ADD CONSTRAINT quickedithistory_ibfk_2 FOREIGN KEY (user_id) REFERENCES User(user_id); |
| ALTER TABLE QuickEditMostRecent ADD CONSTRAINT quickeditmostrecent_ibfk_2 FOREIGN KEY (user_id) REFERENCES User(user_id); |
| ALTER TABLE Issue ADD CONSTRAINT issue_ibfk_2 FOREIGN KEY (reporter_id) REFERENCES User(user_id); |
| ALTER TABLE Issue ADD CONSTRAINT issue_ibfk_3 FOREIGN KEY (owner_id) REFERENCES User(user_id); |
| ALTER TABLE Issue ADD CONSTRAINT issue_ibfk_4 FOREIGN KEY (derived_owner_id) REFERENCES User(user_id); |
| ALTER TABLE Issue2Cc ADD CONSTRAINT issue2cc_ibfk_2 FOREIGN KEY (cc_id) REFERENCES User(user_id); |
| ALTER TABLE IssueStar ADD CONSTRAINT issuestar_ibfk_1 FOREIGN KEY (user_id) REFERENCES User(user_id); |
| ALTER TABLE Issue2FieldValue ADD CONSTRAINT issue2fieldvalue_ibfk_2 FOREIGN KEY (user_id) REFERENCES User(user_id); |
| ALTER TABLE Comment ADD CONSTRAINT comment_ibfk_3 FOREIGN KEY (commenter_id) REFERENCES User(user_id); |
| ALTER TABLE Comment ADD CONSTRAINT comment_ibfk_4 FOREIGN KEY (deleted_by) REFERENCES User(user_id); |
| ALTER TABLE FieldDef2Admin ADD CONSTRAINT fielddef2admin_ibfk_2 FOREIGN KEY (admin_id) REFERENCES User(user_id); |
| ALTER TABLE Template2Admin ADD CONSTRAINT template2admin_ibfk_2 FOREIGN KEY (admin_id) REFERENCES User(user_id); |
| ALTER TABLE Template2FieldValue ADD CONSTRAINT template2fieldvalue_ibfk_2 FOREIGN KEY (user_id) REFERENCES User(user_id); |
| ALTER TABLE Component2Admin ADD CONSTRAINT component2admin_ibfk_2 FOREIGN KEY (admin_id) REFERENCES User(user_id); |
| ALTER TABLE Component2Cc ADD CONSTRAINT component2cc_ibfk_2 FOREIGN KEY (cc_id) REFERENCES User(user_id); |
| ALTER TABLE User2SavedQuery ADD CONSTRAINT user2savedquery_ibfk_1 FOREIGN KEY (user_id) REFERENCES User(user_id); |
| |
| ================================================================ |
| 2015-08-20: Add obscure_email column to User. |
| |
| ALTER TABLE User ADD obscure_email BOOLEAN DEFAULT TRUE; |
| |
| ================================================================ |
| 2015-09-14: Add role column to UserGroup. |
| |
| ALTER TABLE UserGroup ADD COLUMN role ENUM ('owner', 'member') NOT NULL DEFAULT 'member'; |
| |
| ================================================================ |
| 2015-09-14: Remove via_id column from UserGroup. |
| |
| ALTER TABLE UserGroup DROP COLUMN via_id; |
| |
| ================================================================ |
| 2015-09-14: Add foreign key constraints to Issue2Foo tables |
| |
| ALTER TABLE Issue ADD CONSTRAINT issue_ibfk_5 FOREIGN KEY (status_id) REFERENCES StatusDef(id); |
| ALTER TABLE Issue2Component ADD CONSTRAINT issue2component_ibfk_2 FOREIGN KEY (component_id) REFERENCES ComponentDef(id); |
| ALTER TABLE Issue2Label ADD CONSTRAINT issue2label_ibfk_2 FOREIGN KEY (label_id) REFERENCES LabelDef(id); |
| ALTER TABLE Issue2FieldValue ADD CONSTRAINT issue2fieldvalue_ibfk_3 FOREIGN KEY (field_id) REFERENCES FieldDef(id); |
| |
| ================================================================ |
| 2015-09-16: Use Binary collation on Varchar unique keys |
| |
| ALTER TABLE StatusDef MODIFY status VARCHAR(80) BINARY NOT NULL; |
| ALTER TABLE ComponentDef MODIFY path VARCHAR(255) BINARY NOT NULL; |
| ALTER TABLE LabelDef MODIFY label VARCHAR(80) BINARY NOT NULL; |
| ALTER TABLE FieldDef MODIFY field_name VARCHAR(80) BINARY NOT NULL; |
| ALTER TABLE Template MODIFY name VARCHAR(255) BINARY NOT NULL; |
| |
| ================================================================ |
| 2015-09-16: Have components use the same ID schema as Labels/Statuses |
| |
| ALTER TABLE ComponentDef MODIFY id INT NOT NULL AUTO_INCREMENT; |
| ALTER TABLE Component2Admin MODIFY component_id INT NOT NULL; |
| ALTER TABLE Component2Cc MODIFY component_id INT NOT NULL; |
| ALTER TABLE Issue2Component MODIFY component_id INT NOT NULL; |
| |
| ================================================================ |
| 2015-09-17: Introduce DanglingIssueRelation table |
| |
| ALTER TABLE IssueRelation ADD CONSTRAINT issuerelation_ibfk_2 FOREIGN KEY (dst_issue_id) REFERENCES Issue(id); |
| |
| 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), |
| INDEX (issue_id), |
| FOREIGN KEY (issue_id) REFERENCES Issue(id) |
| ) ENGINE=INNODB; |
| |
| ================================================================ |
| 2015-09-18: Convert table char encodings to utf8. |
| |
| ALTER DATABASE monorail CHARACTER SET = utf8 COLLATE = utf8_unicode_ci; |
| ALTER TABLE Comment CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| ALTER TABLE ComponentDef CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| ALTER TABLE FieldDef CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| ALTER TABLE IssueSummary CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| ALTER TABLE LabelDef CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| ALTER TABLE MemberNotes CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| ALTER TABLE Project CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| ALTER TABLE StatusDef CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| |
| ================================================================ |
| 2015-09-22: Make IssueRelation primary key more specific |
| |
| ALTER TABLE IssueRelation DROP PRIMARY KEY, ADD PRIMARY KEY (issue_id, dst_issue_id, kind); |
| ALTER TABLE DanglingIssueRelation DROP PRIMARY KEY, ADD PRIMARY KEY (issue_id, dst_issue_project, dst_issue_local_id, kind); |
| |
| ================================================================ |
| 2015-09-29: Make cache_key unsigned so unsigned user ids can be invalidated. |
| |
| ALTER TABLE Invalidate MODIFY cache_key INT UNSIGNED NOT NULL; |
| |
| ================================================================ |
| 2015-09-29: Add external_group_type and external_group_name to UserGroupSettings |
| |
| ALTER TABLE UserGroupSettings ADD COLUMN external_group_type ENUM ('chrome_infra_auth', 'mdb'); |
| ALTER TABLE UserGroupSettings ADD COLUMN last_sync_time INT; |
| |
| ================================================================ |
| 2015-10-27: Eliminate Project.deliver_outbound_email because we have separate staging and prod instances. |
| |
| ALTER TABLE Project DROP COLUMN deliver_outbound_email; |
| |
| ================================================================ |
| 2015-10-27: Add SpamReport and is_spam fields to Issue and Comment |
| |
| ALTER TABLE Issue ADD COLUMN is_spam BOOL DEFAULT FALSE; |
| ALTER TABLE Issue ADD INDEX (is_spam, project_id); |
| |
| ALTER TABLE Comment ADD COLUMN is_spam BOOL DEFAULT FALSE; |
| ALTER TABLE Comment ADD INDEX (is_spam, project_id, created); |
| |
| -- 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 |
| 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) |
| ); |
| |
| ================================================================ |
| 2015-11-03: Add new external group type chromium_committers |
| |
| ALTER TABLE UserGroupSettings MODIFY COLUMN external_group_type ENUM ('chrome_infra_auth', 'mdb', 'chromium_committers'); |
| |
| ================================================================ |
| 2015-11-4: Add SpamVerdict table. |
| |
| -- 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, |
| |
| -- 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), |
| |
| -- 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 |
| ); |
| |
| ALTER TABLE LocalIDCounter ADD used_spam_id int(11) NOT NULL; |
| |
| ================================================================ |
| 2015-11-13: Add Template2Component table. |
| |
| 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; |
| |
| ================================================================ |
| 2015-11-13: Add new external group type baggins |
| |
| ALTER TABLE UserGroupSettings MODIFY COLUMN external_group_type ENUM ('chrome_infra_auth', 'mdb', 'chromium_committers', 'baggins'); |
| |
| ================================================================ |
| 2015-11-18: Add new action kind api_request in ActionLimit |
| |
| ALTER TABLE ActionLimit MODIFY COLUMN action_kind ENUM ('project_creation', 'issue_comment', 'issue_attachment', 'issue_bulk_edit', 'api_request'); |
| |
| ================================================================ |
| 2015-11-24: Add shard column to Issue, add indexes, and UPDATE existing rows. |
| |
| ALTER TABLE Issue ADD COLUMN shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL; |
| |
| UPDATE Issue set shard = id % 10; |
| |
| ALTER TABLE Issue ADD INDEX (shard, status_id); |
| ALTER TABLE Issue ADD INDEX (shard, project_id); |
| |
| ================================================================ |
| 2015-11-25: Remove external group type chromium_committers |
| |
| ALTER TABLE UserGroupSettings MODIFY COLUMN external_group_type ENUM ('chrome_infra_auth', 'mdb', 'baggins'); |
| |
| ================================================================ |
| 2015-12-08: Modify handling of hidden well-known labels/statuses |
| |
| ALTER TABLE StatusDef ADD COLUMN hidden BOOLEAN DEFAULT FALSE; |
| ALTER TABLE LabelDef ADD COLUMN hidden BOOLEAN DEFAULT FALSE; |
| |
| UPDATE StatusDef SET status=TRIM(LEADING '#' FROM status), hidden=TRUE WHERE status COLLATE UTF8_GENERAL_CI LIKE '#%'; |
| UPDATE LabelDef SET label=TRIM(LEADING '#' FROM label), hidden=TRUE WHERE label COLLATE UTF8_GENERAL_CI LIKE '#%'; |
| |
| ================================================================ |
| 2015-12-11: Speed up moderation queue queries. |
| |
| ALTER TABLE SpamVerdict ADD INDEX(classifier_confidence); |
| |
| ================================================================ |
| 2015-12-14: Give components 'deprecated' col to match labels/statuses |
| |
| ALTER TABLE StatusDef CHANGE hidden deprecated BOOLEAN DEFAULT FALSE; |
| ALTER TABLE LabelDef CHANGE hidden deprecated BOOLEAN DEFAULT FALSE; |
| ALTER TABLE ComponentDef ADD COLUMN deprecated BOOLEAN DEFAULT FALSE; |
| |
| ================================================================ |
| 2015-12-14: Add table Group2Project |
| |
| CREATE TABLE Group2Project ( |
| group_id INT UNSIGNED NOT NULL, |
| project_id SMALLINT UNSIGNED NOT NULL, |
| |
| PRIMARY KEY (group_id, project_id), |
| |
| FOREIGN KEY (group_id) REFERENCES UserGroupSettings(group_id), |
| FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| ) ENGINE=INNODB; |
| |
| ================================================================ |
| 2015-12-15: Increase maximum attachment quota bytes |
| |
| ALTER TABLE Project MODIFY attachment_bytes_used BIGINT DEFAULT 0; |
| ALTER TABLE Project MODIFY attachment_quota BIGINT DEFAULT 0; |
| |
| ================================================================ |
| 2015-12-15: Simplify moderation queue queries. |
| |
| ALTER TABLE SpamVerdict ADD COLUMN overruled BOOL NOT NULL; |
| ALTER TABLE SpamVerdict ADD COLUMN project_id INT NOT NULL; |
| UPDATE SpamVerdict s JOIN Issue i ON i.id=s.issue_id SET s.project_id=i.project_id; |
| |
| ================================================================ |
| 2015-12-17: Add cols home_page and logo to table Project |
| |
| ALTER TABLE Project ADD COLUMN home_page VARCHAR(250); |
| ALTER TABLE Project ADD COLUMN logo_gcs_id VARCHAR(250); |
| ALTER TABLE Project ADD COLUMN logo_file_name VARCHAR(250); |
| |
| ================================================================ |
| 2015-12-28: Add component_required col to table Template; |
| |
| ALTER TABLE Template ADD component_required BOOLEAN DEFAULT FALSE; |
| |
| ================================================================ |
| 2016-01-05: Add issue_shard column to Issue2Label, Issue2Component, |
| add indexes, and UPDATE existing rows. |
| |
| ALTER TABLE Issue2Component ADD COLUMN issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL; |
| UPDATE Issue2Component set issue_shard = issue_id % 10; |
| ALTER TABLE Issue2Component ADD INDEX (component_id, issue_shard); |
| |
| ALTER TABLE Issue2Label ADD COLUMN issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL; |
| UPDATE Issue2Label set issue_shard = issue_id % 10; |
| ALTER TABLE Issue2Label ADD INDEX (label_id, issue_shard); |
| |
| ================================================================ |
| 2016-01-06: Add period_soft_limit and period_hard_limit columns to ActionLimit |
| |
| ALTER TABLE ActionLimit ADD COLUMN period_soft_limit INT; |
| ALTER TABLE ActionLimit ADD COLUMN period_hard_limit INT; |
| |
| ================================================================ |
| 2016-01-08: Add issue_shard column to Issue2FieldValue, Issue2Cc, |
| add indexes, and UPDATE existing rows. |
| |
| ALTER TABLE Issue2FieldValue ADD COLUMN issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL; |
| UPDATE Issue2FieldValue SET issue_shard = issue_id % 10; |
| ALTER TABLE Issue2FieldValue ADD INDEX (field_id, issue_shard, int_value); |
| ALTER TABLE Issue2FieldValue ADD INDEX (field_id, issue_shard, str_value(255)); |
| ALTER TABLE Issue2FieldValue ADD INDEX (field_id, issue_shard, user_id); |
| |
| ALTER TABLE Issue2Cc ADD COLUMN issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL; |
| UPDATE Issue2Cc SET issue_shard = issue_id % 10; |
| ALTER TABLE Issue2Cc ADD INDEX (cc_id, issue_shard); |
| |
| ================================================================ |
| 2015-12-17: Add documentation forwarding for /wiki urls |
| |
| ALTER TABLE Project ADD COLUMN docs_url VARCHAR(250); |
| |
| ================================================================ |
| 2015-12-17: Ensure SavedQueries never have null ids |
| |
| ALTER TABLE SavedQuery MODIFY id INT NOT NULL AUTO INCREMENT; |
| |
| ================================================================ |
| 2016-02-04: Add created, creator_id, modified, modifier_id for components |
| |
| ALTER TABLE ComponentDef ADD COLUMN created INT; |
| ALTER TABLE ComponentDef ADD COLUMN creator_id INT UNSIGNED; |
| ALTER TABLE ComponentDef ADD FOREIGN KEY (creator_id) REFERENCES User(user_id); |
| ALTER TABLE ComponentDef ADD COLUMN modified INT; |
| ALTER TABLE ComponentDef ADD COLUMN modifier_id INT UNSIGNED; |
| ALTER TABLE ComponentDef ADD FOREIGN KEY (modifier_id) REFERENCES User(user_id); |
| |
| ================================================================ |
| 2016-02-19: Opt all privileged accounts into displaying full email. |
| |
| UPDATE User SET obscure_email = FALSE WHERE email LIKE "%@chromium.org"; |
| UPDATE User SET obscure_email = FALSE WHERE email LIKE "%@webrtc.org"; |
| UPDATE User SET obscure_email = FALSE WHERE email LIKE "%@google.com"; |
| |
| ================================================================ |
| 2016-04-11: Increase email length limit to 255 |
| |
| ALTER TABLE User MODIFY email VARCHAR(255); |