Open Source Monorail

appengine/monorail/sql/alter-table-log.txt

+# 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);