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