OLD | NEW |
(Empty) | |
| 1 # Copyright 2016 The Chromium Authors. All rights reserved. |
| 2 # Use of this source code is govered by a BSD-style |
| 3 # license that can be found in the LICENSE file or at |
| 4 # https://developers.google.com/open-source/licenses/bsd |
| 5 |
| 6 This file contains a log of ALTER TABLE statements that need to be executed |
| 7 to bring a Monorail SQL database up to the current schema. |
| 8 |
| 9 ================================================================ |
| 10 2012-05-24: Added more Project fields. |
| 11 |
| 12 ALTER TABLE Project ADD COLUMN read_only_reason VARCHAR(80); |
| 13 ALTER TABLE Project ADD COLUMN issue_notify_address VARCHAR(80); |
| 14 ALTER TABLE Project ADD COLUMN attachment_bytes_used INT DEFAULT 0; |
| 15 ALTER TABLE Project ADD COLUMN attachment_quota INT DEFAULT 52428800; |
| 16 ALTER TABLE Project ADD COLUMN moved_to VARCHAR(250); |
| 17 ALTER TABLE Project ADD COLUMN process_inbound_email BOOLEAN DEFAULT FALSE; |
| 18 |
| 19 ================================================================ |
| 20 2012-06-01: Added inbound_message for issue comments |
| 21 |
| 22 ALTER TABLE Comment ADD COLUMN inbound_message TEXT; |
| 23 |
| 24 |
| 25 ================================================================ |
| 26 2012-06-05: Removed send_notifications_from_user because Monorail will |
| 27 not offer that feature any time soon. |
| 28 |
| 29 ALTER TABLE ProjectIssueConfig DROP COLUMN send_notifications_from_user; |
| 30 |
| 31 |
| 32 ================================================================ |
| 33 2012-06-05: Add initial subscription options. |
| 34 |
| 35 ALTER TABLE User2SavedQuery ADD COLUMN subscription_mode |
| 36 ENUM ('noemail', 'immediate') DEFAULT 'noemail' NOT NULL; |
| 37 |
| 38 |
| 39 ================================================================ |
| 40 2012-07-02: Revised project states and added state_reason and delete_time |
| 41 |
| 42 ALTER TABLE Project MODIFY COLUMN state ENUM ('live', 'archived', 'deletable') |
| 43 NOT NULL; |
| 44 |
| 45 ALTER TABLE Project ADD COLUMN state_reason VARCHAR(80); |
| 46 ALTER TABLE Project ADD COLUMN delete_time INT; |
| 47 |
| 48 |
| 49 ================================================================ |
| 50 2012-07-05: Added action limits and dismissed cues |
| 51 |
| 52 CREATE TABLE ActionLimit ( |
| 53 user_id INT NOT NULL AUTO_INCREMENT, |
| 54 action_kind ENUM ( |
| 55 'project_creation', 'issue_comment', 'issue_attachment', |
| 56 'issue_bulk_edit'), |
| 57 recent_count INT, |
| 58 reset_timestamp INT, |
| 59 lifetime_count INT, |
| 60 lifetime_limit INT, |
| 61 |
| 62 PRIMARY KEY (user_id, action_kind) |
| 63 ) ENGINE=INNODB; |
| 64 |
| 65 |
| 66 CREATE TABLE DismissedCues ( |
| 67 user_id INT NOT NULL AUTO_INCREMENT, |
| 68 cue VARCHAR(40), -- names of the cue cards that the user has dismissed. |
| 69 |
| 70 INDEX (user_id) |
| 71 ) ENGINE=INNODB; |
| 72 |
| 73 |
| 74 ALTER TABLE User ADD COLUMN ignore_action_limits BOOLEAN DEFAULT FALSE; |
| 75 |
| 76 ================================================================ |
| 77 2012-07-11: No longer using Counter table. |
| 78 |
| 79 DROP TABLE Counter; |
| 80 |
| 81 ================================================================ |
| 82 2012-09-06: Drop AttachmentContent, put blobkey in Attachment |
| 83 and drop some redundant columns. |
| 84 |
| 85 Note: This loses attachment data that might currently be in your |
| 86 instance. Good thing these schema refinements are getting done |
| 87 before launch. |
| 88 |
| 89 ALTER TABLE Attachment DROP COLUMN attachment_id; |
| 90 ALTER TABLE Attachment DROP COLUMN comment_created; |
| 91 ALTER TABLE Attachment ADD COLUMN blobkey VARCHAR(1024) NOT NULL; |
| 92 |
| 93 DROP TABLE AttachmentContent; |
| 94 |
| 95 ALTER TABLE IssueUpdate DROP COLUMN comment_created; |
| 96 |
| 97 |
| 98 ================================================================ |
| 99 2012-11-01: Add Components to IssueUpdate enum. |
| 100 |
| 101 alter table IssueUpdate modify field ENUM ('summary', 'status', 'owner', |
| 102 'cc', 'labels', 'blockedon', 'blocking', 'mergedinto', 'project', |
| 103 'components') NOT NULL; |
| 104 |
| 105 |
| 106 ================================================================ |
| 107 2012-12-10: Add template admins and field admins |
| 108 |
| 109 |
| 110 CREATE TABLE FieldDef2Admin ( |
| 111 field_id INT NOT NULL, |
| 112 admin_id INT NOT NULL, |
| 113 |
| 114 PRIMARY KEY (field_id, admin_id), |
| 115 FOREIGN KEY (field_id) REFERENCES FieldDef(id), |
| 116 FOREIGN KEY (admin_id) REFERENCES User(user_id) |
| 117 ) ENGINE=INNODB; |
| 118 |
| 119 CREATE TABLE Template2Admin ( |
| 120 template_id INT NOT NULL, |
| 121 admin_id INT NOT NULL, |
| 122 |
| 123 PRIMARY KEY (template_id, admin_id), |
| 124 FOREIGN KEY (template_id) REFERENCES Template(id), |
| 125 FOREIGN KEY (admin_id) REFERENCES User(user_id) |
| 126 ) ENGINE=INNODB; |
| 127 |
| 128 |
| 129 ================================================================ |
| 130 2012-12-14: Add a table of custom field values |
| 131 |
| 132 ALTER TABLE FieldDef MODIFY field_type ENUM ( |
| 133 'enum_type', 'int_type', 'str_type', 'user_type') NOT NULL; |
| 134 |
| 135 CREATE TABLE Issue2FieldValue ( |
| 136 iid INT NOT NULL, |
| 137 field_id INT NOT NULL, |
| 138 |
| 139 int_value INT, |
| 140 str_value VARCHAR(1024), |
| 141 user_id INT, |
| 142 |
| 143 derived BOOLEAN DEFAULT FALSE, |
| 144 |
| 145 INDEX (iid, field_id), |
| 146 INDEX (field_id, int_value), |
| 147 INDEX (field_id, str_value), |
| 148 INDEX (field_id, user_id), |
| 149 |
| 150 FOREIGN KEY (iid) REFERENCES Issue(id), |
| 151 -- FOREIGN KEY (field_id) REFERENCES FieldDef(id), |
| 152 FOREIGN KEY (user_id) REFERENCES User(user_id) |
| 153 ) ENGINE=INNODB; |
| 154 |
| 155 |
| 156 ================================================================ |
| 157 2012-12-18: persistence for update objects on custom fields |
| 158 |
| 159 ALTER TABLE IssueUpdate MODIFY field ENUM ( |
| 160 'summary', 'status', 'owner', 'cc', 'labels', 'blockedon', 'blocking', 'merged
into', |
| 161 'project', 'components', 'custom' ) NOT NULL; |
| 162 |
| 163 ALTER TABLE IssueUpdate ADD custom_field_name VARCHAR(255); |
| 164 |
| 165 |
| 166 ================================================================ |
| 167 2012-12-27: Rename component owner to component admin |
| 168 |
| 169 DROP TABLE Component2Owner; |
| 170 |
| 171 CREATE TABLE Component2Admin ( |
| 172 component_id SMALLINT UNSIGNED NOT NULL, |
| 173 admin_id INT NOT NULL, |
| 174 |
| 175 PRIMARY KEY (component_id, admin_id), |
| 176 |
| 177 FOREIGN KEY (component_id) REFERENCES ComponentDef(id), |
| 178 FOREIGN KEY (admin_id) REFERENCES User(user_id) |
| 179 ) ENGINE=INNODB; |
| 180 |
| 181 |
| 182 ================================================================ |
| 183 2013-01-20: add field applicability predicate |
| 184 |
| 185 ALTER TABLE FieldDef ADD applicable_type VARCHAR(80); |
| 186 ALTER TABLE FieldDef ADD applicable_predicate TEXT; |
| 187 |
| 188 ================================================================ |
| 189 2013-01-25: add field validation details |
| 190 |
| 191 ALTER TABLE FieldDef ADD max_value INT; |
| 192 ALTER TABLE FieldDef ADD min_value INT; |
| 193 ALTER TABLE FieldDef ADD regex VARCHAR(80); |
| 194 ALTER TABLE FieldDef ADD needs_member BOOLEAN; |
| 195 ALTER TABLE FieldDef ADD needs_perm VARCHAR(80); |
| 196 |
| 197 |
| 198 ================================================================ |
| 199 2013-02-11: add grant and notify to user-valued fields |
| 200 |
| 201 ALTER TABLE FieldDef ADD grants_perm VARCHAR(80); |
| 202 ALTER TABLE FieldDef ADD notify_on ENUM ('never', 'any_comment') DEFAULT 'never'
NOT NULL; |
| 203 |
| 204 |
| 205 ================================================================ |
| 206 2013-03-17: Add Template2FieldValue |
| 207 |
| 208 CREATE TABLE Template2FieldValue ( |
| 209 template_id INT NOT NULL, |
| 210 field_id INT NOT NULL, |
| 211 |
| 212 int_value INT, |
| 213 str_value VARCHAR(1024), |
| 214 user_id INT, |
| 215 |
| 216 INDEX (template_id, field_id), |
| 217 |
| 218 FOREIGN KEY (template_id) REFERENCES Template(id), |
| 219 -- FOREIGN KEY (field_id) REFERENCES FieldDef(id), |
| 220 FOREIGN KEY (user_id) REFERENCES User(user_id) |
| 221 ) ENGINE=INNODB; |
| 222 |
| 223 |
| 224 ================================================================ |
| 225 2013-05-08: eliminated same_org_only |
| 226 |
| 227 -- This needs to be done on all shards. |
| 228 UPDATE Project SET access = 'members_only' WHERE access = 'same_org_only'; |
| 229 ALTER TABLE Project MODIFY COLUMN access ENUM ('anyone', 'members_only'); |
| 230 |
| 231 ================================================================ |
| 232 2013-05-08: implemented recent activity timestamp |
| 233 |
| 234 -- This needs to be done on all shards. |
| 235 ALTER TABLE Project ADD recent_activity_timestamp INT; |
| 236 |
| 237 ================================================================ |
| 238 2013-07-01: use BIGINT for Invalidate timesteps |
| 239 |
| 240 ALTER TABLE Invalidate MODIFY COLUMN timestep BIGINT NOT NULL AUTO_INCREMENT; |
| 241 |
| 242 |
| 243 ================================================================ |
| 244 2013-07-23: renamed to avoid "participant" |
| 245 |
| 246 RENAME TABLE ParticipantDuty TO MemberDuty; |
| 247 RENAME TABLE ParticipantNotes TO MemberNotes; |
| 248 |
| 249 ================================================================ |
| 250 2013-08-22: renamed issue_id to local_id |
| 251 |
| 252 -- On master and all shards |
| 253 ALTER TABLE Issue CHANGE issue_id local_id INT NOT NULL; |
| 254 |
| 255 -- On master only |
| 256 ALTER TABLE IssueFormerLocations CHANGE issue_id local_id INT NOT NULL; |
| 257 |
| 258 ================================================================ |
| 259 2013-08-24: renamed iid to issue_id |
| 260 |
| 261 -- On master and all shards |
| 262 |
| 263 ALTER TABLE IssueSummary DROP FOREIGN KEY IssueSummary_ibfk_1; |
| 264 ALTER TABLE IssueSummary CHANGE iid issue_id INT NOT NULL; |
| 265 ALTER TABLE IssueSummary ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| 266 |
| 267 ALTER TABLE Issue2Label DROP FOREIGN KEY Issue2Label_ibfk_1; |
| 268 ALTER TABLE Issue2Label CHANGE iid issue_id INT NOT NULL; |
| 269 ALTER TABLE Issue2Label ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| 270 |
| 271 ALTER TABLE Issue2Component DROP FOREIGN KEY Issue2Component_ibfk_1; |
| 272 ALTER TABLE Issue2Component CHANGE iid issue_id INT NOT NULL; |
| 273 ALTER TABLE Issue2Component ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| 274 |
| 275 ALTER TABLE Issue2Cc DROP FOREIGN KEY Issue2Cc_ibfk_1; |
| 276 ALTER TABLE Issue2Cc CHANGE iid issue_id INT NOT NULL; |
| 277 ALTER TABLE Issue2Cc ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| 278 |
| 279 ALTER TABLE Issue2Notify DROP FOREIGN KEY Issue2Notify_ibfk_1; |
| 280 ALTER TABLE Issue2Notify CHANGE iid issue_id INT NOT NULL; |
| 281 ALTER TABLE Issue2Notify ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| 282 |
| 283 ALTER TABLE IssueStar DROP FOREIGN KEY IssueStar_ibfk_1; |
| 284 ALTER TABLE IssueStar CHANGE iid issue_id INT NOT NULL; |
| 285 ALTER TABLE IssueStar ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| 286 |
| 287 ALTER TABLE IssueRelation DROP FOREIGN KEY IssueRelation_ibfk_1; |
| 288 ALTER TABLE IssueRelation CHANGE iid issue_id INT NOT NULL; |
| 289 ALTER TABLE IssueRelation ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| 290 |
| 291 ALTER TABLE IssueRelation CHANGE dst_iid dst_issue_id INT NOT NULL; |
| 292 |
| 293 ALTER TABLE Issue2FieldValue DROP FOREIGN KEY Issue2FieldValue_ibfk_1; |
| 294 ALTER TABLE Issue2FieldValue CHANGE iid issue_id INT NOT NULL; |
| 295 ALTER TABLE Issue2FieldValue ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| 296 |
| 297 -- On master only |
| 298 ALTER TABLE Comment DROP FOREIGN KEY Comment_ibfk_2; |
| 299 ALTER TABLE Comment CHANGE iid issue_id INT NOT NULL; |
| 300 ALTER TABLE Comment ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| 301 |
| 302 ALTER TABLE Attachment DROP FOREIGN KEY Attachment_ibfk_1; |
| 303 ALTER TABLE Attachment CHANGE iid issue_id INT NOT NULL; |
| 304 ALTER TABLE Attachment ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| 305 |
| 306 ALTER TABLE IssueUpdate DROP FOREIGN KEY IssueUpdate_ibfk_1; |
| 307 ALTER TABLE IssueUpdate CHANGE iid issue_id INT NOT NULL; |
| 308 ALTER TABLE IssueUpdate ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| 309 |
| 310 -- I was missing a foreign key constraint here. Adding now. |
| 311 ALTER TABLE IssueFormerLocations CHANGE iid issue_id INT NOT NULL; |
| 312 ALTER TABLE IssueFormerLocations ADD FOREIGN KEY (issue_id) REFERENCES Issue(id)
; |
| 313 |
| 314 -- I was missing a foreign key constraint here. Adding now. |
| 315 ALTER TABLE ReindexQueue CHANGE iid issue_id INT NOT NULL; |
| 316 ALTER TABLE ReindexQueue ADD FOREIGN KEY (issue_id) REFERENCES Issue(id); |
| 317 |
| 318 |
| 319 ================================================================ |
| 320 2013-08-30: added per-project email sending flag |
| 321 |
| 322 -- On master and all shards |
| 323 ALTER TABLE Project ADD COLUMN deliver_outbound_email BOOLEAN DEFAULT FALSE; |
| 324 |
| 325 |
| 326 ================================================================ |
| 327 2013-10-30: renamed prompts to templates |
| 328 |
| 329 ALTER TABLE ProjectIssueConfig |
| 330 CHANGE default_prompt_for_developers default_template_for_developers INT NOT NUL
L; |
| 331 |
| 332 ALTER TABLE ProjectIssueConfig |
| 333 CHANGE default_prompt_for_users default_template_for_users INT NOT NULL; |
| 334 |
| 335 ALTER TABLE Template |
| 336 CHANGE prompt_name name VARCHAR(255) NOT NULL, |
| 337 CHANGE prompt_text content TEXT, |
| 338 CHANGE prompt_summary summary TEXT, |
| 339 CHANGE prompt_summary_must_be_edited summary_must_be_edited BOOLEAN, |
| 340 CHANGE prompt_owner_id owner_id INT, |
| 341 CHANGE prompt_status status VARCHAR(255), |
| 342 CHANGE prompt_members_only members_only BOOLEAN; |
| 343 |
| 344 |
| 345 ================================================================ |
| 346 2013-11-18: add LocalIDCounter to master DB only, and fill in values. |
| 347 |
| 348 CREATE TABLE LocalIDCounter ( |
| 349 project_id SMALLINT UNSIGNED NOT NULL, |
| 350 used_local_id INT NOT NULL, |
| 351 |
| 352 PRIMARY KEY (project_id), |
| 353 FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| 354 ) ENGINE=INNODB; |
| 355 |
| 356 |
| 357 -- Note: this ignores former issue locations, so it can only be run |
| 358 -- now, before the "move issue" feature is offered. |
| 359 REPLACE INTO LocalIDCounter |
| 360 SELECT project_id, MAX(local_id) |
| 361 FROM Issue |
| 362 GROUP BY project_id; |
| 363 |
| 364 ================================================================ |
| 365 2015-06-12: add issue_id to Invalidate's enum for kind. |
| 366 |
| 367 ALTER TABLE Invalidate CHANGE kind kind ENUM('user', 'project', 'issue', 'issue_
id'); |
| 368 |
| 369 ================================================================ |
| 370 2015-07-24: Rename blobkey to gcs_object_id because we are using |
| 371 Google Cloud storage now. |
| 372 |
| 373 ALTER TABLE Attachment CHANGE blobkey gcs_object_id VARCHAR(1024) NOT NULL; |
| 374 |
| 375 =============================================================== |
| 376 2015-08-14: Use MurmurHash3 to deterministically generate user ids. |
| 377 |
| 378 -- First, drop foreign key constraints, then alter the keys, then |
| 379 -- add back the foreign key constraints. |
| 380 |
| 381 ALTER TABLE User2Project DROP FOREIGN KEY user2project_ibfk_2; |
| 382 ALTER TABLE ExtraPerm DROP FOREIGN KEY extraperm_ibfk_2; |
| 383 ALTER TABLE MemberNotes DROP FOREIGN KEY membernotes_ibfk_2; |
| 384 ALTER TABLE UserStar DROP FOREIGN KEY userstar_ibfk_1; |
| 385 ALTER TABLE UserStar DROP FOREIGN KEY userstar_ibfk_2; |
| 386 ALTER TABLE ProjectStar DROP FOREIGN KEY projectstar_ibfk_1; |
| 387 ALTER TABLE UserGroup DROP FOREIGN KEY usergroup_ibfk_1; |
| 388 ALTER TABLE UserGroup DROP FOREIGN KEY usergroup_ibfk_2; |
| 389 ALTER TABLE UserGroupSettings DROP FOREIGN KEY usergroupsettings_ibfk_1; |
| 390 ALTER TABLE QuickEditHistory DROP FOREIGN KEY quickedithistory_ibfk_2; |
| 391 ALTER TABLE QuickEditMostRecent DROP FOREIGN KEY quickeditmostrecent_ibfk_2; |
| 392 ALTER TABLE Issue DROP FOREIGN KEY issue_ibfk_2; |
| 393 ALTER TABLE Issue DROP FOREIGN KEY issue_ibfk_3; |
| 394 ALTER TABLE Issue DROP FOREIGN KEY issue_ibfk_4; |
| 395 ALTER TABLE Issue2Cc DROP FOREIGN KEY issue2cc_ibfk_2; |
| 396 ALTER TABLE IssueStar DROP FOREIGN KEY issuestar_ibfk_1; -- ? |
| 397 ALTER TABLE Issue2FieldValue DROP FOREIGN KEY issue2fieldvalue_ibfk_2; |
| 398 ALTER TABLE Comment DROP FOREIGN KEY comment_ibfk_3; |
| 399 ALTER TABLE Comment DROP FOREIGN KEY comment_ibfk_4; |
| 400 ALTER TABLE FieldDef2Admin DROP FOREIGN KEY fielddef2admin_ibfk_2; |
| 401 ALTER TABLE Template2Admin DROP FOREIGN KEY template2admin_ibfk_2; |
| 402 ALTER TABLE Template2FieldValue DROP FOREIGN KEY template2fieldvalue_ibfk_2; |
| 403 ALTER TABLE Component2Admin DROP FOREIGN KEY component2admin_ibfk_2; |
| 404 ALTER TABLE Component2Cc DROP FOREIGN KEY component2cc_ibfk_2; |
| 405 ALTER TABLE User2SavedQuery DROP FOREIGN KEY user2savedquery_ibfk_1; |
| 406 |
| 407 |
| 408 ALTER TABLE User MODIFY user_id INT UNSIGNED NOT NULL; |
| 409 ALTER TABLE ActionLimit MODIFY user_id INT UNSIGNED NOT NULL; |
| 410 ALTER TABLE DismissedCues MODIFY user_id INT UNSIGNED NOT NULL; |
| 411 ALTER TABLE User2Project MODIFY user_id INT UNSIGNED NOT NULL; |
| 412 ALTER TABLE ExtraPerm MODIFY user_id INT UNSIGNED NOT NULL; |
| 413 ALTER TABLE MemberNotes MODIFY user_id INT UNSIGNED NOT NULL; |
| 414 ALTER TABLE UserStar MODIFY starred_user_id INT UNSIGNED NOT NULL, |
| 415 MODIFY user_id INT UNSIGNED NOT NULL; |
| 416 ALTER TABLE ProjectStar MODIFY user_id INT UNSIGNED NOT NULL; |
| 417 ALTER TABLE UserGroup MODIFY user_id INT UNSIGNED NOT NULL; |
| 418 ALTER TABLE UserGroup MODIFY group_id INT UNSIGNED NOT NULL; |
| 419 ALTER TABLE UserGroupSettings MODIFY group_id INT UNSIGNED NOT NULL; |
| 420 ALTER TABLE QuickEditHistory MODIFY user_id INT UNSIGNED NOT NULL; |
| 421 ALTER TABLE QuickEditMostRecent MODIFY user_id INT UNSIGNED NOT NULL; |
| 422 ALTER TABLE Issue MODIFY reporter_id INT UNSIGNED NOT NULL, |
| 423 MODIFY owner_id INT UNSIGNED, |
| 424 MODIFY derived_owner_id INT UNSIGNED; |
| 425 ALTER TABLE Issue2Cc MODIFY cc_id INT UNSIGNED NOT NULL; |
| 426 ALTER TABLE IssueStar MODIFY user_id INT UNSIGNED NOT NULL; |
| 427 ALTER TABLE Issue2FieldValue MODIFY user_id INT UNSIGNED; |
| 428 ALTER TABLE Comment MODIFY commenter_id INT UNSIGNED NOT NULL; |
| 429 ALTER TABLE Comment MODIFY deleted_by INT UNSIGNED; |
| 430 ALTER TABLE IssueUpdate MODIFY added_user_id INT UNSIGNED, |
| 431 MODIFY removed_user_id INT UNSIGNED; |
| 432 ALTER TABLE Template MODIFY owner_id INT UNSIGNED; |
| 433 ALTER TABLE FieldDef2Admin MODIFY admin_id INT UNSIGNED NOT NULL; |
| 434 ALTER TABLE Template2Admin MODIFY admin_id INT UNSIGNED NOT NULL; |
| 435 ALTER TABLE Template2FieldValue MODIFY user_id INT UNSIGNED; |
| 436 ALTER TABLE Component2Admin MODIFY admin_id INT UNSIGNED NOT NULL; |
| 437 ALTER TABLE Component2Cc MODIFY cc_id INT UNSIGNED NOT NULL; |
| 438 ALTER TABLE User2SavedQuery MODIFY user_id INT UNSIGNED NOT NULL; |
| 439 |
| 440 ALTER TABLE User2Project ADD CONSTRAINT user2project_ibfk_2 FOREIGN KEY (user_id
) REFERENCES User(user_id); |
| 441 ALTER TABLE ExtraPerm ADD CONSTRAINT extraperm_ibfk_2 FOREIGN KEY (user_id) REF
ERENCES User(user_id); |
| 442 ALTER TABLE MemberNotes ADD CONSTRAINT membernotes_ibfk_2 FOREIGN KEY (user_id)
REFERENCES User(user_id); |
| 443 ALTER TABLE UserStar ADD CONSTRAINT userstar_ibfk_1 FOREIGN KEY (user_id) REFER
ENCES User(user_id); |
| 444 ALTER TABLE UserStar ADD CONSTRAINT userstar_ibfk_2 FOREIGN KEY (starred_user_i
d) REFERENCES User(user_id); |
| 445 ALTER TABLE ProjectStar ADD CONSTRAINT projectstar_ibfk_1 FOREIGN KEY (user_id)
REFERENCES User(user_id); |
| 446 ALTER TABLE UserGroup ADD CONSTRAINT usergroup_ibfk_1 FOREIGN KEY (user_id) REF
ERENCES User(user_id); |
| 447 ALTER TABLE UserGroup ADD CONSTRAINT usergroup_ibfk_2 FOREIGN KEY (group_id) RE
FERENCES User(user_id); |
| 448 ALTER TABLE UserGroupSettings ADD CONSTRAINT usergroupsettings_ibfk_1 FOREIGN K
EY (group_id) REFERENCES User(user_id); |
| 449 ALTER TABLE QuickEditHistory ADD CONSTRAINT quickedithistory_ibfk_2 FOREIGN KEY
(user_id) REFERENCES User(user_id); |
| 450 ALTER TABLE QuickEditMostRecent ADD CONSTRAINT quickeditmostrecent_ibfk_2 FOREI
GN KEY (user_id) REFERENCES User(user_id); |
| 451 ALTER TABLE Issue ADD CONSTRAINT issue_ibfk_2 FOREIGN KEY (reporter_id) REFEREN
CES User(user_id); |
| 452 ALTER TABLE Issue ADD CONSTRAINT issue_ibfk_3 FOREIGN KEY (owner_id) REFERENCES
User(user_id); |
| 453 ALTER TABLE Issue ADD CONSTRAINT issue_ibfk_4 FOREIGN KEY (derived_owner_id) RE
FERENCES User(user_id); |
| 454 ALTER TABLE Issue2Cc ADD CONSTRAINT issue2cc_ibfk_2 FOREIGN KEY (cc_id) REFEREN
CES User(user_id); |
| 455 ALTER TABLE IssueStar ADD CONSTRAINT issuestar_ibfk_1 FOREIGN KEY (user_id) REF
ERENCES User(user_id); |
| 456 ALTER TABLE Issue2FieldValue ADD CONSTRAINT issue2fieldvalue_ibfk_2 FOREIGN KEY
(user_id) REFERENCES User(user_id); |
| 457 ALTER TABLE Comment ADD CONSTRAINT comment_ibfk_3 FOREIGN KEY (commenter_id) RE
FERENCES User(user_id); |
| 458 ALTER TABLE Comment ADD CONSTRAINT comment_ibfk_4 FOREIGN KEY (deleted_by) REFE
RENCES User(user_id); |
| 459 ALTER TABLE FieldDef2Admin ADD CONSTRAINT fielddef2admin_ibfk_2 FOREIGN KEY (ad
min_id) REFERENCES User(user_id); |
| 460 ALTER TABLE Template2Admin ADD CONSTRAINT template2admin_ibfk_2 FOREIGN KEY (ad
min_id) REFERENCES User(user_id); |
| 461 ALTER TABLE Template2FieldValue ADD CONSTRAINT template2fieldvalue_ibfk_2 FOREI
GN KEY (user_id) REFERENCES User(user_id); |
| 462 ALTER TABLE Component2Admin ADD CONSTRAINT component2admin_ibfk_2 FOREIGN KEY (
admin_id) REFERENCES User(user_id); |
| 463 ALTER TABLE Component2Cc ADD CONSTRAINT component2cc_ibfk_2 FOREIGN KEY (cc_id)
REFERENCES User(user_id); |
| 464 ALTER TABLE User2SavedQuery ADD CONSTRAINT user2savedquery_ibfk_1 FOREIGN KEY (
user_id) REFERENCES User(user_id); |
| 465 |
| 466 ================================================================ |
| 467 2015-08-20: Add obscure_email column to User. |
| 468 |
| 469 ALTER TABLE User ADD obscure_email BOOLEAN DEFAULT TRUE; |
| 470 |
| 471 ================================================================ |
| 472 2015-09-14: Add role column to UserGroup. |
| 473 |
| 474 ALTER TABLE UserGroup ADD COLUMN role ENUM ('owner', 'member') NOT NULL DEFAULT
'member'; |
| 475 |
| 476 ================================================================ |
| 477 2015-09-14: Remove via_id column from UserGroup. |
| 478 |
| 479 ALTER TABLE UserGroup DROP COLUMN via_id; |
| 480 |
| 481 ================================================================ |
| 482 2015-09-14: Add foreign key constraints to Issue2Foo tables |
| 483 |
| 484 ALTER TABLE Issue ADD CONSTRAINT issue_ibfk_5 FOREIGN KEY (status_id) REFERENCE
S StatusDef(id); |
| 485 ALTER TABLE Issue2Component ADD CONSTRAINT issue2component_ibfk_2 FOREIGN KEY (
component_id) REFERENCES ComponentDef(id); |
| 486 ALTER TABLE Issue2Label ADD CONSTRAINT issue2label_ibfk_2 FOREIGN KEY (label_id
) REFERENCES LabelDef(id); |
| 487 ALTER TABLE Issue2FieldValue ADD CONSTRAINT issue2fieldvalue_ibfk_3 FOREIGN KEY
(field_id) REFERENCES FieldDef(id); |
| 488 |
| 489 ================================================================ |
| 490 2015-09-16: Use Binary collation on Varchar unique keys |
| 491 |
| 492 ALTER TABLE StatusDef MODIFY status VARCHAR(80) BINARY NOT NULL; |
| 493 ALTER TABLE ComponentDef MODIFY path VARCHAR(255) BINARY NOT NULL; |
| 494 ALTER TABLE LabelDef MODIFY label VARCHAR(80) BINARY NOT NULL; |
| 495 ALTER TABLE FieldDef MODIFY field_name VARCHAR(80) BINARY NOT NULL; |
| 496 ALTER TABLE Template MODIFY name VARCHAR(255) BINARY NOT NULL; |
| 497 |
| 498 ================================================================ |
| 499 2015-09-16: Have components use the same ID schema as Labels/Statuses |
| 500 |
| 501 ALTER TABLE ComponentDef MODIFY id INT NOT NULL AUTO_INCREMENT; |
| 502 ALTER TABLE Component2Admin MODIFY component_id INT NOT NULL; |
| 503 ALTER TABLE Component2Cc MODIFY component_id INT NOT NULL; |
| 504 ALTER TABLE Issue2Component MODIFY component_id INT NOT NULL; |
| 505 |
| 506 ================================================================ |
| 507 2015-09-17: Introduce DanglingIssueRelation table |
| 508 |
| 509 ALTER TABLE IssueRelation ADD CONSTRAINT issuerelation_ibfk_2 FOREIGN KEY (dst_
issue_id) REFERENCES Issue(id); |
| 510 |
| 511 CREATE TABLE DanglingIssueRelation ( |
| 512 issue_id INT NOT NULL, |
| 513 dst_issue_project VARCHAR(80), |
| 514 dst_issue_local_id INT, |
| 515 |
| 516 -- This table uses 'blocking' so that it can guarantee the src issue |
| 517 -- always exists, while the dst issue is always the dangling one. |
| 518 kind ENUM ('blockedon', 'blocking', 'mergedinto') NOT NULL, |
| 519 |
| 520 PRIMARY KEY (issue_id, dst_issue_project, dst_issue_local_id), |
| 521 INDEX (issue_id), |
| 522 FOREIGN KEY (issue_id) REFERENCES Issue(id) |
| 523 ) ENGINE=INNODB; |
| 524 |
| 525 ================================================================ |
| 526 2015-09-18: Convert table char encodings to utf8. |
| 527 |
| 528 ALTER DATABASE monorail CHARACTER SET = utf8 COLLATE = utf8_unicode_ci; |
| 529 ALTER TABLE Comment CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| 530 ALTER TABLE ComponentDef CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| 531 ALTER TABLE FieldDef CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| 532 ALTER TABLE IssueSummary CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| 533 ALTER TABLE LabelDef CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| 534 ALTER TABLE MemberNotes CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| 535 ALTER TABLE Project CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| 536 ALTER TABLE StatusDef CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
| 537 |
| 538 ================================================================ |
| 539 2015-09-22: Make IssueRelation primary key more specific |
| 540 |
| 541 ALTER TABLE IssueRelation DROP PRIMARY KEY, ADD PRIMARY KEY (issue_id, dst_issue
_id, kind); |
| 542 ALTER TABLE DanglingIssueRelation DROP PRIMARY KEY, ADD PRIMARY KEY (issue_id, d
st_issue_project, dst_issue_local_id, kind); |
| 543 |
| 544 ================================================================ |
| 545 2015-09-29: Make cache_key unsigned so unsigned user ids can be invalidated. |
| 546 |
| 547 ALTER TABLE Invalidate MODIFY cache_key INT UNSIGNED NOT NULL; |
| 548 |
| 549 ================================================================ |
| 550 2015-09-29: Add external_group_type and external_group_name to UserGroupSettings |
| 551 |
| 552 ALTER TABLE UserGroupSettings ADD COLUMN external_group_type ENUM ('chrome_infra
_auth', 'mdb'); |
| 553 ALTER TABLE UserGroupSettings ADD COLUMN last_sync_time INT; |
| 554 |
| 555 ================================================================ |
| 556 2015-10-27: Eliminate Project.deliver_outbound_email because we have separate st
aging and prod instances. |
| 557 |
| 558 ALTER TABLE Project DROP COLUMN deliver_outbound_email; |
| 559 |
| 560 ================================================================ |
| 561 2015-10-27: Add SpamReport and is_spam fields to Issue and Comment |
| 562 |
| 563 ALTER TABLE Issue ADD COLUMN is_spam BOOL DEFAULT FALSE; |
| 564 ALTER TABLE Issue ADD INDEX (is_spam, project_id); |
| 565 |
| 566 ALTER TABLE Comment ADD COLUMN is_spam BOOL DEFAULT FALSE; |
| 567 ALTER TABLE Comment ADD INDEX (is_spam, project_id, created); |
| 568 |
| 569 -- Created whenever a user reports an issue or comment as spam. |
| 570 -- Note this is distinct from a SpamVerdict, which is issued by |
| 571 -- the system rather than a human user. |
| 572 CREATE TABLE SpamReport ( |
| 573 -- when this report was generated |
| 574 created TIMESTAMP NOT NULL, |
| 575 -- when the reported content was generated |
| 576 content_created TIMESTAMP NOT NULL, |
| 577 -- id of the reporting user |
| 578 user_id INT UNSIGNED NOT NULL, |
| 579 -- id of the reported user |
| 580 reported_user_id INT UNSIGNED NOT NULL, |
| 581 -- either this or issue_id must be set |
| 582 comment_id INT, |
| 583 -- either this or comment_id must be set |
| 584 issue_id INT, |
| 585 |
| 586 INDEX (issue_id), |
| 587 INDEX (comment_id), |
| 588 FOREIGN KEY (issue_id) REFERENCES Issue(id), |
| 589 FOREIGN KEY (comment_id) REFERENCES Comment(id) |
| 590 ); |
| 591 |
| 592 ================================================================ |
| 593 2015-11-03: Add new external group type chromium_committers |
| 594 |
| 595 ALTER TABLE UserGroupSettings MODIFY COLUMN external_group_type ENUM ('chrome_in
fra_auth', 'mdb', 'chromium_committers'); |
| 596 |
| 597 ================================================================ |
| 598 2015-11-4: Add SpamVerdict table. |
| 599 |
| 600 -- Any time a human or the system sets is_spam to true, |
| 601 -- or changes it from true to false, we want to have a |
| 602 -- record of who did it and why. |
| 603 CREATE TABLE SpamVerdict ( |
| 604 -- when this verdict was generated |
| 605 created TIMESTAMP NOT NULL, |
| 606 |
| 607 -- id of the reporting user, may be null if it was |
| 608 -- an automatic classification. |
| 609 user_id INT UNSIGNED, |
| 610 |
| 611 -- either this or issue_id must be set |
| 612 comment_id INT, |
| 613 |
| 614 -- either this or comment_id must be set |
| 615 issue_id INT, |
| 616 |
| 617 INDEX (issue_id), |
| 618 INDEX (comment_id), |
| 619 FOREIGN KEY (issue_id) REFERENCES Issue(id), |
| 620 FOREIGN KEY (comment_id) REFERENCES Comment(id), |
| 621 |
| 622 -- If the classifier issued the verdict, this should |
| 623 -- be set. |
| 624 classifier_confidence FLOAT, |
| 625 |
| 626 -- This should reflect the new is_spam value that was applied |
| 627 -- by this verdict, not the value it had prior. |
| 628 is_spam BOOLEAN NOT NULL, |
| 629 |
| 630 -- owner: a project owner marked it as spam |
| 631 -- threshhold: number of SpamReports from non-members was exceeded. |
| 632 -- classifier: the automatic classifier reports it as spam. |
| 633 reason ENUM ("manual", "threshold", "classifier") NOT NULL |
| 634 ); |
| 635 |
| 636 ALTER TABLE LocalIDCounter ADD used_spam_id int(11) NOT NULL; |
| 637 |
| 638 ================================================================ |
| 639 2015-11-13: Add Template2Component table. |
| 640 |
| 641 CREATE TABLE Template2Component ( |
| 642 template_id INT NOT NULL, |
| 643 component_id INT NOT NULL, |
| 644 |
| 645 PRIMARY KEY (template_id, component_id), |
| 646 |
| 647 FOREIGN KEY (template_id) REFERENCES Template(id), |
| 648 FOREIGN KEY (component_id) REFERENCES ComponentDef(id) |
| 649 ) ENGINE=INNODB; |
| 650 |
| 651 ================================================================ |
| 652 2015-11-13: Add new external group type baggins |
| 653 |
| 654 ALTER TABLE UserGroupSettings MODIFY COLUMN external_group_type ENUM ('chrome_in
fra_auth', 'mdb', 'chromium_committers', 'baggins'); |
| 655 |
| 656 ================================================================ |
| 657 2015-11-18: Add new action kind api_request in ActionLimit |
| 658 |
| 659 ALTER TABLE ActionLimit MODIFY COLUMN action_kind ENUM ('project_creation', 'iss
ue_comment', 'issue_attachment', 'issue_bulk_edit', 'api_request'); |
| 660 |
| 661 ================================================================ |
| 662 2015-11-24: Add shard column to Issue, add indexes, and UPDATE existing rows. |
| 663 |
| 664 ALTER TABLE Issue ADD COLUMN shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL; |
| 665 |
| 666 UPDATE Issue set shard = id % 10; |
| 667 |
| 668 ALTER TABLE Issue ADD INDEX (shard, status_id); |
| 669 ALTER TABLE Issue ADD INDEX (shard, project_id); |
| 670 |
| 671 ================================================================ |
| 672 2015-11-25: Remove external group type chromium_committers |
| 673 |
| 674 ALTER TABLE UserGroupSettings MODIFY COLUMN external_group_type ENUM ('chrome_in
fra_auth', 'mdb', 'baggins'); |
| 675 |
| 676 ================================================================ |
| 677 2015-12-08: Modify handling of hidden well-known labels/statuses |
| 678 |
| 679 ALTER TABLE StatusDef ADD COLUMN hidden BOOLEAN DEFAULT FALSE; |
| 680 ALTER TABLE LabelDef ADD COLUMN hidden BOOLEAN DEFAULT FALSE; |
| 681 |
| 682 UPDATE StatusDef SET status=TRIM(LEADING '#' FROM status), hidden=TRUE WHERE sta
tus COLLATE UTF8_GENERAL_CI LIKE '#%'; |
| 683 UPDATE LabelDef SET label=TRIM(LEADING '#' FROM label), hidden=TRUE WHERE label
COLLATE UTF8_GENERAL_CI LIKE '#%'; |
| 684 |
| 685 ================================================================ |
| 686 2015-12-11: Speed up moderation queue queries. |
| 687 |
| 688 ALTER TABLE SpamVerdict ADD INDEX(classifier_confidence); |
| 689 |
| 690 ================================================================ |
| 691 2015-12-14: Give components 'deprecated' col to match labels/statuses |
| 692 |
| 693 ALTER TABLE StatusDef CHANGE hidden deprecated BOOLEAN DEFAULT FALSE; |
| 694 ALTER TABLE LabelDef CHANGE hidden deprecated BOOLEAN DEFAULT FALSE; |
| 695 ALTER TABLE ComponentDef ADD COLUMN deprecated BOOLEAN DEFAULT FALSE; |
| 696 |
| 697 ================================================================ |
| 698 2015-12-14: Add table Group2Project |
| 699 |
| 700 CREATE TABLE Group2Project ( |
| 701 group_id INT UNSIGNED NOT NULL, |
| 702 project_id SMALLINT UNSIGNED NOT NULL, |
| 703 |
| 704 PRIMARY KEY (group_id, project_id), |
| 705 |
| 706 FOREIGN KEY (group_id) REFERENCES UserGroupSettings(group_id), |
| 707 FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| 708 ) ENGINE=INNODB; |
| 709 |
| 710 ================================================================ |
| 711 2015-12-15: Increase maximum attachment quota bytes |
| 712 |
| 713 ALTER TABLE Project MODIFY attachment_bytes_used BIGINT DEFAULT 0; |
| 714 ALTER TABLE Project MODIFY attachment_quota BIGINT DEFAULT 0; |
| 715 |
| 716 ================================================================ |
| 717 2015-12-15: Simplify moderation queue queries. |
| 718 |
| 719 ALTER TABLE SpamVerdict ADD COLUMN overruled BOOL NOT NULL; |
| 720 ALTER TABLE SpamVerdict ADD COLUMN project_id INT NOT NULL; |
| 721 UPDATE SpamVerdict s JOIN Issue i ON i.id=s.issue_id SET s.project_id=i.project_
id; |
| 722 |
| 723 ================================================================ |
| 724 2015-12-17: Add cols home_page and logo to table Project |
| 725 |
| 726 ALTER TABLE Project ADD COLUMN home_page VARCHAR(250); |
| 727 ALTER TABLE Project ADD COLUMN logo_gcs_id VARCHAR(250); |
| 728 ALTER TABLE Project ADD COLUMN logo_file_name VARCHAR(250); |
| 729 |
| 730 ================================================================ |
| 731 2015-12-28: Add component_required col to table Template; |
| 732 |
| 733 ALTER TABLE Template ADD component_required BOOLEAN DEFAULT FALSE; |
| 734 |
| 735 ================================================================ |
| 736 2016-01-05: Add issue_shard column to Issue2Label, Issue2Component, |
| 737 add indexes, and UPDATE existing rows. |
| 738 |
| 739 ALTER TABLE Issue2Component ADD COLUMN issue_shard SMALLINT UNSIGNED DEFAULT 0 N
OT NULL; |
| 740 UPDATE Issue2Component set issue_shard = issue_id % 10; |
| 741 ALTER TABLE Issue2Component ADD INDEX (component_id, issue_shard); |
| 742 |
| 743 ALTER TABLE Issue2Label ADD COLUMN issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT N
ULL; |
| 744 UPDATE Issue2Label set issue_shard = issue_id % 10; |
| 745 ALTER TABLE Issue2Label ADD INDEX (label_id, issue_shard); |
| 746 |
| 747 ================================================================ |
| 748 2016-01-06: Add period_soft_limit and period_hard_limit columns to ActionLimit |
| 749 |
| 750 ALTER TABLE ActionLimit ADD COLUMN period_soft_limit INT; |
| 751 ALTER TABLE ActionLimit ADD COLUMN period_hard_limit INT; |
| 752 |
| 753 ================================================================ |
| 754 2016-01-08: Add issue_shard column to Issue2FieldValue, Issue2Cc, |
| 755 add indexes, and UPDATE existing rows. |
| 756 |
| 757 ALTER TABLE Issue2FieldValue ADD COLUMN issue_shard SMALLINT UNSIGNED DEFAULT 0
NOT NULL; |
| 758 UPDATE Issue2FieldValue SET issue_shard = issue_id % 10; |
| 759 ALTER TABLE Issue2FieldValue ADD INDEX (field_id, issue_shard, int_value); |
| 760 ALTER TABLE Issue2FieldValue ADD INDEX (field_id, issue_shard, str_value(255)); |
| 761 ALTER TABLE Issue2FieldValue ADD INDEX (field_id, issue_shard, user_id); |
| 762 |
| 763 ALTER TABLE Issue2Cc ADD COLUMN issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL
; |
| 764 UPDATE Issue2Cc SET issue_shard = issue_id % 10; |
| 765 ALTER TABLE Issue2Cc ADD INDEX (cc_id, issue_shard); |
| 766 |
| 767 ================================================================ |
| 768 2015-12-17: Add documentation forwarding for /wiki urls |
| 769 |
| 770 ALTER TABLE Project ADD COLUMN docs_url VARCHAR(250); |
| 771 |
| 772 ================================================================ |
| 773 2015-12-17: Ensure SavedQueries never have null ids |
| 774 |
| 775 ALTER TABLE SavedQuery MODIFY id INT NOT NULL AUTO INCREMENT; |
| 776 |
| 777 ================================================================ |
| 778 2016-02-04: Add created, creator_id, modified, modifier_id for components |
| 779 |
| 780 ALTER TABLE ComponentDef ADD COLUMN created INT; |
| 781 ALTER TABLE ComponentDef ADD COLUMN creator_id INT UNSIGNED; |
| 782 ALTER TABLE ComponentDef ADD FOREIGN KEY (creator_id) REFERENCES User(user_id); |
| 783 ALTER TABLE ComponentDef ADD COLUMN modified INT; |
| 784 ALTER TABLE ComponentDef ADD COLUMN modifier_id INT UNSIGNED; |
| 785 ALTER TABLE ComponentDef ADD FOREIGN KEY (modifier_id) REFERENCES User(user_id); |
| 786 |
| 787 ================================================================ |
| 788 2016-02-19: Opt all privileged accounts into displaying full email. |
| 789 |
| 790 UPDATE User SET obscure_email = FALSE WHERE email LIKE "%@chromium.org"; |
| 791 UPDATE User SET obscure_email = FALSE WHERE email LIKE "%@webrtc.org"; |
| 792 UPDATE User SET obscure_email = FALSE WHERE email LIKE "%@google.com"; |
| 793 |
| 794 ================================================================ |
| 795 2016-04-11: Increase email length limit to 255 |
| 796 |
| 797 ALTER TABLE User MODIFY email VARCHAR(255); |
OLD | NEW |