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

# Copyright 2016 The Chromium Authors
# Use of this source code is governed by a BSD-style license that can be
# found in the LICENSE file.

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 primary and all shards
ALTER TABLE Issue CHANGE issue_id local_id INT NOT NULL;

-- On primary only
ALTER TABLE IssueFormerLocations CHANGE issue_id local_id INT NOT NULL;

================================================================
2013-08-24: renamed iid to issue_id

-- On primary 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 primary 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 primary 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 primary 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);

================================================================
2016-04-14: Add forwarding for /source urls

ALTER TABLE Project ADD COLUMN source_url VARCHAR(250);

================================================================
2016-04-27: Add prefs for compact email subject lines

ALTER TABLE User ADD COLUMN email_compact_subject BOOLEAN DEFAULT FALSE;
ALTER TABLE User ADD COLUMN email_view_widget BOOLEAN DEFAULT TRUE;

================================================================
2016-05-13: Add component labels

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;

================================================================
2016-05-23: Add default search for members

ALTER TABLE ProjectIssueConfig ADD COLUMN member_default_query TEXT;

================================================================
2016-06-17: Add is_description column to Comment

Local:
% pt-online-schema-change --alter "ADD COLUMN is_description BOOLEAN DEFAULT FALSE" D=monorail,t=Comment --host=localhost --user=root --alter-foreign-keys-method=rebuild_constraints --execute

Staging/Production:
% pt-online-schema-change --alter "ADD COLUMN is_description BOOLEAN DEFAULT FALSE" D=monorail,t=Comment,h=<primary IP address>,u=$USER,p=test --alter-forieign-keys-method=rebuild_constraints --recursion-method=hosts --execute

================================================================
2016-05-13: Add table AutocompleteExclusion

CREATE TABLE AutocompleteExclusion (
  project_id SMALLINT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,

  PRIMARY KEY (project_id, user_id),
  FOREIGN KEY (project_id) REFERENCES Project(project_id),
  FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;

===============================================================
2016-06-30: Update table character encodings to allow Emoji support.

/* DO NOT RUN THESE STATEMENTS ON PROD OR STAGING. They are fine for localhost
but be warned they will lock the db for some time if you have gigs of data in
these tables */

ALTER TABLE `monorail`.`Comment` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `monorail`.`IssueUpdate` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `monorail`.`IssueSummary` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

/* This is what I ran on production: */
% pt-online-schema-change --alter "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" D=monorail,t=Comment,h=<primary IP> --alter-foreign-keys-method=rebuild_constraints --no-drop-old-table --recursion-method=hosts --check-slave-lag=h=<one of the replicas' IP> --print --execute

% pt-online-schema-change --alter "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" D=monorail,t=IssueUpdate,h=<primary IP> --alter-foreign-keys-method=rebuild_constraints --no-drop-old-table --recursion-method=hosts --check-slave-lag=h=<one of the replicas' IP> --print --execute

% pt-online-schema-change --alter "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" D=monorail,t=IssueSummary,h=<primary IP> --alter-foreign-keys-method=rebuild_constraints --no-drop-old-table --recursion-method=hosts --check-slave-lag=h=<one of the replicas' IP> --print --execute

/* And then these two which ran very quickly: */
ALTER TABLE `monorail`.`Template` CHANGE `content` `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `monorail`.`Template` CHANGE `summary` `summary` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

===============================================================
2016-07-07: Add rank to IssueRelation

ALTER TABLE IssueRelation ADD COLUMN rank BIGINT;

==============================================================
2016-07-13: Set default rank for blockedon relations

UPDATE IssueRelation SET rank = 0 WHERE kind = 'blockedon';

================================================================
2016-08-01: Add timestamps for issue field changes

DO NOT RUN THIS STATEMENT ON PROD OR STAGING.  It is fine for localhost
but be warned that it will lock the db for some time if you have gigs of data in
these tables.
ALTER TABLE Issue
  ADD COLUMN owner_modified INT,
  ADD COLUMN status_modified INT,
  ADD COLUMN component_modified INT;

Staging/Production:
% pt-online-schema-change \
  --alter "ADD COLUMN owner_modified INT, ADD COLUMN status_modified INT, ADD COLUMN component_modified INT" \
  D=monorail,t=Issue,h=<primary IP address>,u=$USER,p=<your mysql password> \
  --alter-foreign-keys-method=rebuild_constraints --recursion-method=hosts --execute

==============================================================
2016-08-05: Add tables Hotlist, Hotlist2Issue, Hotlist2User

CREATE TABLE Hotlist (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,

  summary TEXT,
  description TEXT,

  is_private BOOLEAN DEFAULT FALSE,

  PRIMARY KEY (id)
) ENGINE=INNODB;


CREATE TABLE Hotlist2Issue (
  hotlist_id INT UNSIGNED NOT NULL,
  issue_id INT NOT NULL,

  rank BIGINT NOT NULL,

  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)
) ENGINE=INNODB;


CREATE TABLE Hotlist2User (
  hotlist_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,

  role_name ENUM ('owner', 'member', '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;

==============================================================
2016-08-10: Improve Hotlist schema

ALTER TABLE Hotlist ADD COLUMN default_col_spec TEXT;

ALTER TABLE Hotlist2User CHANGE role_name
  role_name ENUM('owner', 'editor', 'follower');

==============================================================
2016-08-15: Add hotlist to Invalidate table

ALTER TABLE Invalidate CHANGE kind
  kind ENUM('user', 'project', 'issue', 'issue_id', 'hotlist');

================================================================
2016-09-21: Create the CommentContent table with emoji support.

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),
  UNIQUE KEY (comment_id)  -- TODO: drop this too.
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

To copy comment strings from Comment to CommentContent, use
the SQL procedure in monorail/tools/copy-comment-to-commentcontent.sql.

If you need to roll back, you can reverse the process by reading
and carefully using the SQL procedure in
monorail/tools/copy-new-commentcontent-back-to-comment.sql.

Optionally, after you have all comment content strings in
CommentContent, you can reduce the size of the Comment table by using
the procedure in monorail/tools/null-comment-table-strings.sql.
This can make it faster to make more changes to the Comment table.

================================================================
2016-09-29: Drop was_escaped after Comment table is made smaller

ALTER TABLE Comment DROP COLUMN was_escaped;

================================================================
2016-10-03: Add date-type custom fields

ALTER TABLE Issue2FieldValue ADD date_value INT;
ALTER TABLE Issue2FieldValue ADD INDEX (field_id, issue_shard, date_value);
ALTER TABLE Template2FieldValue ADD date_value INT;
ALTER TABLE FieldDef CHANGE field_type field_type ENUM (
    'enum_type', 'int_type', 'str_type', 'user_type', 'date_type') NOT NULL;

================================================================
2016-10-13: Follow-up on splitting the Comment table

ALTER TABLE Comment
    DROP COLUMN content,
    DROP COLUMN inbound_message,
    ADD COLUMN commentcontent_id INT;

ALTER TABLE Comment
     ADD FOREIGN KEY (commentcontent_id) REFERENCES CommentContent(id);

After making those schema changes, run the commands in
tools/backfill-commentcontent-id.sql to fill in commentcontent_id
for existing comments.

================================================================
2016-10-13: Add new User fields

ALTER TABLE User
    ADD COLUMN last_visit_timestamp INT,
    ADD COLUMN email_bounce_timestamp INT,
    ADD COLUMN vacation_message VARCHAR(80);

================================================================
2016-11-30: Drop unique key constraint on CommentContent.comment_id.
This is a prerequiste for deleting the code that sets a value for
that column.  This resolves one TODO from 2016-09-21.  Later the
column itself can be dropped, which is the other TODO from 2016-09-21.

ALTER TABLE CommentContent DROP INDEX comment_id;

================================================================
2016-12-20: Add a table to keep track of hotlists that users have
starred.

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;

================================================================
2017-12-04: Add two new columns to Hotlist2Issue.

ALTER TABLE Hotlist2Issue
      ADD COLUMN adder_id INT UNSIGNED,
      ADD COLUMN added INT,
      ADD FOREIGN KEY (adder_id) REFERENCES User(user_id);

================================================================
2017-01-30: Add one new column to SpamVerdict.

ALTER TABLE SpamVerdict CHANGE reason
  reason ENUM ("manual", "threshold", "classifier", "fail_open") NOT NULL;

================================================================
2017-02-1: Add two tables to keep track of hotlists and bugs
that users have visited

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 IssueVisitHistory (
  issue_id INT NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  viewed INT NOT NULL,

  PRIMARY KEY (user_id, issue_id),
  FOREIGN KEY (issue_id) REFERENCES Issue(id),
  FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;


================================================================
2017-02-16: Add 'note' column to Hotlist2Issue table.

ALTER TABLE Hotlist2Issue ADD COLUMN note TEXT;


================================================================
2017-02-23: Add 'is_niche' column to FieldDef table.

ALTER TABLE FieldDef ADD COLUMN is_niche BOOLEAN;


================================================================
2017-03-05: Add 'ping_who' column to FieldDef table.

ALTER TABLE FieldDef
  ADD COLUMN date_action ENUM ('no_action', 'ping_owner_only', 'ping_participants');


================================================================
2017-05-02: Add index to make commentby: query term faster.

ALTER TABLE Comment ADD INDEX (commenter_id, deleted_by, issue_id);


================================================================
2017-05-12: Add user preference to ping issue starrers.

ALTER TABLE User ADD COLUMN notify_starred_ping BOOLEAN DEFAULT FALSE;

================================================================
2017-06-15: Add table to map @google.com to @chromium.org accounts.

CREATE TABLE LinkedAccount (
  parent_email VARCHAR(255) NOT NULL,  -- lowercase
  child_email VARCHAR(255) NOT NULL,  -- lowercase

  KEY (parent_email),
  UNIQUE KEY (child_email)
) ENGINE=INNODB;

================================================================
2017-11-14: Add field_type ENUM url_type to FieldDef.

ALTER TABLE FieldDef MODIFY field_type ENUM (
  'enum_type', 'int_type', 'str_type', 'user_type', 'date_type', 'url_type') NOT NULL;

ALTER TABLE Issue2FieldValue ADD COLUMN url_value VARCHAR(1024);
ALTER TABLE Issue2FieldValue ADD INDEX (field_id, url_value);

================================================================
2017-11-22: Add url_value column to Template2FieldValue table.

ALTER TABLE Template2FieldValue ADD COLUMN url_value VARCHAR(1024);

================================================================
2018-01-22: Add table to keep track of the latest timestamp that issues with
their component data were collected and uploaded to GCS.

CREATE TABLE ComponentIssueClosedIndex (
  closed_index INT NOT NULL,
  PRIMARY KEY (closed_index)
) ENGINE=INNODB;

================================================================
2018-01-22: Add approval tables and approval_type to FieldDef.

ALTER TABLE FieldDef MODIFY field_type ENUM (
  'enum_type', 'int_type', 'str_type', 'user_type', 'date_type', 'url_type', 'approval_type') NOT NULL;

CREATE TABLE ApprovalStatusDef (
  id INT NOT NULL AUTO_INCREMENT,
  field_id INT NOT NULL,
  status VARCHAR(80) BINARY NOT NULL,
  docstring TEXT,

  PRIMARY KEY (id),
  UNIQUE KEY (field_id, status),
  FOREIGN KEY (field_id) REFERENCES FieldDef(id)
) ENGINE=INNODB;

CREATE TABLE Issue2ApprovalValue (
  issue_id INT NOT NULL,
  issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
  field_id INT NOT NULL,
  status_id INT NOT NULL,
  setter_id INT UNSIGNED,
  set_on INT,

  PRIMARY KEY (issue_id, field_id),
  INDEX (field_id, issue_shard, status_id),
  INDEX (field_id, issue_shard, setter_id),
  INDEX (field_id, issue_shard, set_on),

  FOREIGN KEY (issue_id) REFERENCES Issue(id),
  FOREIGN KEY (field_id) REFERENCES FieldDef(id),
  FOREIGN KEY (status_id) REFERENCES ApprovalStatusDef(id),
  FOREIGN KEY (setter_id) REFERENCES User(user_id)
) ENGINE=INNODB;

CREATE TABLE Approval2Approvers (
  field_id INT NOT NULL,
  approver_id INT UNSIGNED NOT NULL,
  issue_id INT,
  issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,

  PRIMARY KEY (issue_id, field_id, approver_id),
  INDEX (approver_id, field_id, issue_shard),

  FOREIGN KEY (field_id) REFERENCES FieldDef(id),
  FOREIGN KEY (approver_id) REFERENCES User(user_id),
  FOREIGN KEY (issue_id) REFERENCES Issue(id)
) ENGINE=INNODB;

================================================================
2018-01-29: Add is_deleted column to ComponentDef table and remove
uniqueness constraint for component names in a project.

ALTER TABLE ComponentDef ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;
ALTER TABLE ComponentDef ADD INDEX project_id2 (project_id, path);
ALTER TABLE ComponentDef DROP INDEX project_id;

================================================================
2018-01-30: Add IssueSnapshot table and join tables

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 NOT NULL,
  period_start INT NOT NULL,
  period_end INT 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),
  UNIQUE 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;

===============================================================
2018-01-29: Add approval_id column to FieldDef table

ALTER TABLE FieldDef ADD COLUMN approval_id INT;

===============================================================
2018-02-08: Drop previous approval tables and add default approvers table

DROP TABLE ApprovalStatusDef;
DROP TABLE Approval2Approver;
DROP TABLE Issue2ApprovalValue;

CREATE TABLE ApprovalDef2Approver (
  approval_id INT NOT NULL,
  approver_id INT UNSIGNED NOT NULL,

  PRIMARY KEY (approval_id, approver_id),

  FOREIGN KEY (approval_id) REFERENCES FieldDef(id),
  FOREIGN KEY (approver_id) REFERENCES User(user_id)
) ENGINE=INNODB;

==================================================================
2018-02-09: Add project_id column to default approvers table

ALTER TABLE ApprovalDef2Approver ADD project_id SMALLINT UNSIGNED NOT NULL;
ALTER TABLE ApprovalDef2Approver ADD CONSTRAINT ApprovalDef2Approver_ibfk_3 FOREIGN KEY (project_id) REFERENCES Project(project_id);

==================================================================
2018-02-14: Expand IssueSnapshot time columns from INT to INT UNSIGNED
ALTER TABLE IssueSnapshot MODIFY period_start INT UNSIGNED NOT NULL;
ALTER TABLE IssueSnapshot MODIFY period_end INT UNSIGNED NOT NULL;


================================================================
2018-02-22: Relax some constraints on issue snapshots

ALTER TABLE IssueSnapshot MODIFY status_id int;
ALTER TABLE IssueSnapshot DROP INDEX issue_id;
ALTER TABLE IssueSnapshot ADD INDEX (`issue_id`,`period_start`,`period_end`);

================================================================
2018-03-12: Add launch template milestones and approval tables

CREATE TABLE Template2Milestone (
  id INT NOT NULL AUTO_INCREMENT,
  template_id INT NOT NULL,
  name VARCHAR(255) BINARY NOT NULL,
  rank SMALLINT UNSIGNED,

  PRIMARY KEY (id, template_id),
  FOREIGN KEY (template_id) REFERENCES Template(id)
) ENGINE=INNODB;

CREATE TABLE Template2ApprovalValue (
  approval_id INT NOT NULL,
  template_id INT NOT NULL,
  milestone_id INT NOT NULL,
  launch_status ENUM ('NA', 'review_requested', 'started', 'need_info', 'approved', 'not_approved'),

  PRIMARY KEY (approval_id, template_id, milestone_id),

  FOREIGN KEY (approval_id) REFERENCES FieldDef(id),
  FOREIGN KEY (template_id) REFERENCES Template(id),
  FOREIGN KEY (milestone_id) REFERENCES Template2Milestone(id)
) ENGINE=INNODB;


================================================================
2018-03-13: Edit approval state enum

ALTER TABLE Template2ApprovalValue CHANGE launch_status status ENUM (
  'needs_review', 'na', 'review_requested', 'started', 'need_info', 'approved', 'not_approved');


================================================================
2018-03-14: Edit approval state enum *AGAIN*

ALTER TABLE Template2ApprovalValue MODIFY status ENUM (
  'needs_review', 'na', 'review_requested', 'started', 'need_info', 'approved', 'not_approved', 'not_set');


================================================================
2018-03-15: Add Issue Approval and Mileston tables

DROP TABLE IF EXISTS Approval2Approver;
DROP TABLE IF EXISTS Issue2ApprovalValue;

CREATE TABLE Issue2Milestone (
  id INT NOT NULL AUTO_INCREMENT,
  issue_id INT NOT NULL,
  name VARCHAR(255) BINARY NOT NULL,
  rank SMALLINT UNSIGNED,

  PRIMARY KEY (id, issue_id),
  FOREIGN KEY (issue_id) REFERENCES Issue(id)
) ENGINE=INNODB;

CREATE TABLE Issue2ApprovalValue (
  issue_id INT NOT NULL,
  approval_id INT NOT NULL,
  milestone_id INT NOT NULL,
  status ENUM ('needs_review', 'na', 'review_requested', '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, milestone_id),
  FOREIGN KEY (issue_id) REFERENCES Issue(id),
  FOREIGN KEY (approval_id) REFERENCES FieldDef(id),
  FOREIGN KEY (milestone_id) REFERENCES Issue2Milestone(id),
  FOREIGN KEY (setter_id) REFERENCES User(user_id)
) ENGINE=INNODB;

CREATE TABLE IssueApproval2Approvers (
  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;

ALTER TABLE Template2ApprovalValue MODIFY status ENUM (
  'needs_review', 'na', 'review_requested', 'started', 'need_info', 'approved', 'not_approved', 'not_set') DEFAULT 'not_set' NOT NULL;

================================================================
2018-03-15: Soft-delete Hotlists.
ALTER TABLE Hotlist ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

===============================================================
2018-03-19: Rename issue approvers table.

RENAME TABLE IssueApproval2Approvers TO IssueApproval2Approver;

================================================================
2018-03-22: Add Hotlist support to IssueSnapshots.

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;

================================================================
2018-03-23: Add ApprovalDef2Survey table.

CREATE TABLE ApprovalDef2Survey (
  approval_id INT NOT NULL,
  survey TEXT,
  project_id SMALLINT UNSIGNED NOT NULL,

  PRIMARY KEY (approval_id, project_id),

  FOREIGN KEY (approval_id) REFERENCES FieldDef(id),
  FOREIGN KEY (project_id) REFERENCES Project(project_id)
) ENGINE=INNODB;

===============================================================
2018-03-24: Add IssueApproval2Comment table.

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;

===============================================================
2018-03-29: Rename Milestones to Phases.

CREATE TABLE Issue2Phase (
  id INT NOT NULL AUTO_INCREMENT,
  issue_id INT NOT NULL,
  name VARCHAR(255) BINARY NOT NULL,
  rank SMALLINT UNSIGNED,

  PRIMARY KEY (id, issue_id),
  FOREIGN KEY (issue_id) REFERENCES Issue(id)
) ENGINE=INNODB;

CREATE TABLE Template2Phase (
  id INT NOT NULL AUTO_INCREMENT,
  template_id INT NOT NULL,
  name VARCHAR(255) BINARY NOT NULL,
  rank SMALLINT UNSIGNED,

  PRIMARY KEY (id, template_id),
  FOREIGN KEY (template_id) REFERENCES Template(id)
) ENGINE=INNODB;

ALTER TABLE Issue2ApprovalValue DROP FOREIGN KEY Issue2ApprovalValue_ibfk_4;
ALTER TABLE Issue2ApprovalValue ADD COLUMN phase_id int NOT NULL;
CREATE INDEX IF NOT EXISTS phase_id ON Issue2ApprovalValue (phase_id);
ALTER TABLE Issue2ApprovalValue ADD FOREIGN KEY (phase_id) REFERENCES Issue2Phase(id);

ALTER TABLE Template2ApprovalValue DROP FOREIGN KEY Template2ApprovalValue_ibfk_3;
ALTER TABLE Template2ApprovalValue ADD COLUMN phase_id int NOT NULL;
CREATE INDEX IF NOT EXISTS phase_id ON Template2ApprovalValue (phase_id);
ALTER TABLE Template2ApprovalValue ADD FOREIGN KEY (phase_id) REFERENCES Template2Phase(id);

================================================================
2018-04-18: Drop all milestone schema.

ALTER TABLE Template2ApprovalValue DROP COLUMN milestone_id;
ALTER TABLE Issue2ApprovalValue DROP COLUMN milestone_id;

DROP TABLE Template2Milestone;
DROP TABLE Issue2Milestone;

================================================================
2018-04-25: Add phase_id to X2ApprovalValue tables' primary keys.

ALTER TABLE Template2ApprovalValue DROP PRIMARY KEY, ADD PRIMARY KEY(approval_id, template_id, phase_id);
ALTER TABLE Issue2ApprovalValue DROP PRIMARY KEY, ADD PRIMARY KEY(issue_id, approval_id, phase_id);

==================================================================
2018-04-30: Rename Issue2Phase table to IssuePhaseDef: Part One

CREATE TABLE IssuePhaseDef (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) BINARY NOT NULL,
  rank SMALLINT UNSIGNED,

  PRIMARY KEY (id)
) ENGINE=INNODB;

ALTER TABLE Issue2ApprovalValue DROP FOREIGN KEY Issue2ApprovalValue_ibfk_4;
ALTER TABLE Issue2ApprovalValue ADD FOREIGN KEY (phase_id) REFERENCES IssuePhaseDef(id);

==================================================================
2018-05-02: Add phase_id to Issue2FieldValue table.

ALTER TABLE Issue2FieldValue ADD COLUMN phase_id INT;
ALTER TABLE Issue2FieldValue ADD FOREIGN KEY (phase_id) REFERENCES IssuePhaseDef(id);

===================================================================
2018-5-01: Add is_phase_field to FieldDef.

ALTER TABLE FieldDef ADD COLUMN is_phase_field BOOLEAN DEFAULT FALSE;

===================================================================
2018-5-11: Rename Issue2Phase table to IssuePhaseDef: Part Two, drop Issue2Phase

DROP TABLE Issue2Phase;
==================================================================
2018-05-11: Restrict size of index field in Issue2FieldValue

ALTER TABLE Issue2FieldValue DROP INDEX field_id_5;
ALTER TABLE Issue2FieldValue ADD INDEX (field_id, issue_shard, url_value(255));

==================================================================
2018-05-18: Replace Template2Phase FK with IssuePhaseDef.

TRUNCATE TABLE Template2ApprovalValue;
ALTER TABLE Template2ApprovalValue DROP FOREIGN KEY Template2ApprovalValue_ibfk_3;
ALTER TABLE Template2ApprovalValue ADD FOREIGN KEY (phase_id) REFERENCES IssuePhaseDef(id);

================================================================
2018-05-22: Add boolean columns to control autocomplete exclusions.

ALTER TABLE AutocompleteExclusion
  ADD COLUMN ac_exclude BOOLEAN DEFAULT TRUE,
  ADD COLUMN no_expand BOOLEAN DEFAULT FALSE;

==================================================================
2018-05-30: Add comment to Invalidate table

ALTER TABLE Invalidate CHANGE kind
  kind ENUM('user', 'project', 'issue', 'issue_id', 'hotlist',
      'comment');

=================================================================
2018-06-05: Drop Template2Phase tbl and NOT NULL constraint for approval value phase_id columns.

DROP TABLE Template2Phase;

ALTER TABLE Issue2ApprovalValue DROP PRIMARY KEY, ADD PRIMARY KEY(issue_id, approval_id);
ALTER TABLE Issue2ApprovalValue MODIFY COLUMN phase_id INT;

ALTER TABLE Template2ApprovalValue DROP PRIMARY KEY, ADD PRIMARY KEY(approval_id, template_id);
ALTER TABLE Template2ApprovalValue MODIFY COLUMN phase_id INT;

=================================================================
2018-06-22: Add 'template' to Invalidate.kind_enum

ALTER TABLE Invalidate MODIFY COLUMN kind enum('user', 'project', 'issue', 'issue_id', 'hotlist', 'comment', 'template') NOT NULL;

=================================================================
2018-07-02: Add UserCommits table to keep track of commits.

CREATE TABLE UserCommits (
  commit_sha VARCHAR(40),
  parent_sha VARCHAR(40),
  author_id INT UNSIGNED NOT NULL,
  commit_time INT NOT NULL,
  commit_message TEXT,
  commit_repo VARCHAR(255),

  PRIMARY KEY (commit_sha),
  INDEX (author_id, commit_time),
  INDEX (commit_time)
) ENGINE=INNODB;


=================================================================
2018-07-16: Drop parent_sha because it isn't needed in this table and give commit_repo a clearer name.

ALTER TABLE UserCommits DROP COLUMN parent_sha;
ALTER TABLE UserCommits CHANGE commit_repo commit_repo_url VARCHAR(255);


================================================================
2018-08-27: Allow computed external user groups, e.g., everyone@google.com.

ALTER TABLE UserGroupSettings
  MODIFY COLUMN
  external_group_type ENUM ('chrome_infra_auth', 'mdb', 'baggins', 'computed');


================================================================
2018-09-24: Add 'usergroup to Invalidate.kind enum

ALTER TABLE Invalidate MODIFY COLUMN kind enum(
    'user', 'usergroup', 'project', 'issue', 'issue_id',
    'hotlist', 'comment', 'template') NOT NULL;

================================================================
2018-10-30: Fix ApprovalValue status enum for 'review_started'

ALTER TABLE Template2ApprovalValue MODIFY status ENUM (
  'needs_review', 'na', 'review_requested', 'review_started', 'need_info', 'approved', 'not_approved', 'not_set') DEFAULT 'not_set' NOT NULL;

ALTER TABLE Issue2ApprovalValue MODIFY status ENUM (
  'needs_review', 'na', 'review_requested', 'review_started', 'need_info', 'approved', 'not_approved', 'not_set') DEFAULT 'not_set' NOT NULL;


================================================================
2018-11-02: Redo LinkedAccount table.

DROP TABLE LinkedAccount;
CREATE TABLE LinkedAccount (
  parent_id INT UNSIGNED NOT NULL,
  child_id INT UNSIGNED NOT NULL,

  KEY (parent_id),
  UNIQUE KEY (child_id),
  FOREIGN KEY (parent_id) REFERENCES User(user_id),
  FOREIGN KEY (child_id) REFERENCES User(user_id)
) ENGINE=INNODB;

================================================================
2018-12-03: Create LinkedAccountInvite table.

CREATE TABLE LinkedAccountInvite (
  parent_id INT UNSIGNED NOT NULL,
  child_id INT UNSIGNED NOT NULL,

  KEY (parent_id),
  UNIQUE KEY (child_id),
  FOREIGN KEY (parent_id) REFERENCES User(user_id),
  FOREIGN KEY (child_id) REFERENCES User(user_id)
) ENGINE=INNODB;

==================================================================================
2018-1-15: Add notify_group and notify_members bool col to UserGroupSettings table.

ALTER TABLE UserGroupSettings ADD COLUMN notify_members BOOLEAN DEFAULT TRUE;
ALTER TABLE UserGroupSettings ADD COLUMN notify_group BOOLEAN DEFAULT FALSE;

=================================================================
2019-01-23: Add two new indexes to IssueSnapshot for performance.

CREATE INDEX by_period_start ON IssueSnapshot (shard, project_id, status_id, period_start);
CREATE INDEX by_period_end ON IssueSnapshot (shard, project_id, status_id, period_end);


================================================================
2019-01-25: Start a more flexible way of storing user preferences.

CREATE TABLE UserPrefs (
  user_id INT UNSIGNED NOT NULL,
  name VARCHAR(40),
  value VARCHAR(80),

  UNIQUE KEY (user_id, name)
) ENGINE=INNODB;

================================================================
2019-04-10: Set UserPrefs that indicate that privacy click-through was seen.
This is part of phasing out DismissedCues.

INSERT IGNORE INTO UserPrefs (user_id, name, value)
SELECT user_id, cue, 'true'
FROM DismissedCues;

================================================================
2019-05-13: Drop unused ActionLimit table.

DROP TABLE ActionLimit;

================================================================
2019-05-24: Add ext_issue_identifier column to DanglingIssueRelation table.

ALTER TABLE DanglingIssueRelation ADD COLUMN ext_issue_identifier VARCHAR(2048);
ALTER TABLE DanglingIssueRelation ADD INDEX (ext_issue_identifier);

================================================================
2019-06-06: Allow full unicode labels.

ALTER TABLE LabelDef CHANGE label label VARCHAR(80) BINARY NOT NULL COLLATE utf8mb4_unicode_ci;

================================================================
2019-06-07: Add indexes to reduce cases of using filesort

ALTER TABLE HotlistVisitHistory ADD INDEX (user_id, viewed);
ALTER TABLE ReindexQueue ADD INDEX (created);

================================================================
2019-06-13: Add ext_issue_identifier to DanglingIssueRelation PRIMARY KEY.

ALTER TABLE DanglingIssueRelation MODIFY COLUMN ext_issue_identifier VARCHAR(255);
ALTER TABLE DanglingIssueRelation DROP PRIMARY KEY, ADD PRIMARY KEY(issue_id, dst_issue_project, dst_issue_local_id, kind, ext_issue_identifier);

================================================================
2019-06-25: Add unique constraint on SpamReport.

ALTER IGNORE TABLE SpamReport ADD UNIQUE (user_id, comment_id, issue_id);

================================================================
2019-07-03: Add CommentImporter table.

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;


================================================================
2019-10-09: Drop DismissedCues because that data has been in UserPrefs since April.

DROP TABLE DismissedCues;

================================================================
2019-10-24: Insert row representing deleted user.

INSERT IGNORE INTO User (user_id, email) VALUES (1, '');

==================================================================
2019-11-21: Add hotlist_id to Invalidate table.

ALTER TABLE Invalidate CHANGE kind
  kind ENUM('user', 'usergroup', 'project', 'issue', 'issue_id', 'hotlist', 'comment', 'template', 'hotlist_id');


================================================================
2019-12-30: Set custom revision_url_format for pigweed project.

UPDATE Project SET revision_url_format = 'https://pigweed-review.git.corp.google.com/q/{revnum}' WHERE project_name='pigweed';

================================================================
2020-02-19: Create table for editors of a field. Also, add column in FieldDef
to indicate if the editors of that field are being restricted.

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;


ALTER TABLE FieldDef ADD COLUMN is_restricted_field BOOL DEFAULT FALSE;

================================================================
2020-05-14: Add option to force detailed notifications for projects.

ALTER TABLE Project ADD COLUMN issue_notify_always_detailed BOOLEAN DEFAULT FALSE;

================================================================
2022-12-27: Added more IssueUpdate fields.

ALTER TABLE IssueUpdate ADD COLUMN added_component_id VARCHAR(80);
ALTER TABLE IssueUpdate ADD COLUMN removed_component_id VARCHAR(80);

================================================================
2023-09-11: Add new modified timestamp. See: go/monorail-enhanced-modified-time

ALTER TABLE Issue ADD COLUMN migration_modified INT;