OLD | NEW |
(Empty) | |
| 1 -- Copyright 2016 The Chromium Authors. All Rights Reserved. |
| 2 -- |
| 3 -- Use of this source code is governed by a BSD-style |
| 4 -- license that can be found in the LICENSE file or at |
| 5 -- https://developers.google.com/open-source/licenses/bsd |
| 6 |
| 7 |
| 8 -- Create issue-realted tables in monorail db. |
| 9 |
| 10 |
| 11 CREATE TABLE StatusDef ( |
| 12 id INT NOT NULL AUTO_INCREMENT, |
| 13 project_id SMALLINT UNSIGNED NOT NULL, |
| 14 status VARCHAR(80) BINARY NOT NULL, |
| 15 rank SMALLINT UNSIGNED, |
| 16 means_open BOOLEAN, |
| 17 docstring TEXT, |
| 18 deprecated BOOLEAN DEFAULT FALSE, |
| 19 |
| 20 PRIMARY KEY (id), |
| 21 UNIQUE KEY (project_id, status), |
| 22 FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| 23 ) ENGINE=INNODB; |
| 24 |
| 25 |
| 26 CREATE TABLE ComponentDef ( |
| 27 id INT NOT NULL AUTO_INCREMENT, |
| 28 project_id SMALLINT UNSIGNED NOT NULL, |
| 29 |
| 30 -- Note: parent components have paths that are prefixes of child components. |
| 31 path VARCHAR(255) BINARY NOT NULL, |
| 32 docstring TEXT, |
| 33 deprecated BOOLEAN DEFAULT FALSE, |
| 34 created INT, |
| 35 creator_id INT UNSIGNED, |
| 36 modified INT, |
| 37 modifier_id INT UNSIGNED, |
| 38 |
| 39 PRIMARY KEY (id), |
| 40 UNIQUE KEY (project_id, path), |
| 41 FOREIGN KEY (project_id) REFERENCES Project(project_id), |
| 42 FOREIGN KEY (creator_id) REFERENCES User(user_id), |
| 43 FOREIGN KEY (modifier_id) REFERENCES User(user_id) |
| 44 ) ENGINE=INNODB; |
| 45 |
| 46 |
| 47 CREATE TABLE Component2Admin ( |
| 48 component_id INT NOT NULL, |
| 49 admin_id INT UNSIGNED NOT NULL, |
| 50 |
| 51 PRIMARY KEY (component_id, admin_id), |
| 52 |
| 53 FOREIGN KEY (component_id) REFERENCES ComponentDef(id), |
| 54 FOREIGN KEY (admin_id) REFERENCES User(user_id) |
| 55 ) ENGINE=INNODB; |
| 56 |
| 57 |
| 58 CREATE TABLE Component2Cc ( |
| 59 component_id INT NOT NULL, |
| 60 cc_id INT UNSIGNED NOT NULL, |
| 61 |
| 62 PRIMARY KEY (component_id, cc_id), |
| 63 |
| 64 FOREIGN KEY (component_id) REFERENCES ComponentDef(id), |
| 65 FOREIGN KEY (cc_id) REFERENCES User(user_id) |
| 66 ) ENGINE=INNODB; |
| 67 |
| 68 |
| 69 CREATE TABLE LabelDef ( |
| 70 id INT NOT NULL AUTO_INCREMENT, |
| 71 project_id SMALLINT UNSIGNED NOT NULL, |
| 72 label VARCHAR(80) BINARY NOT NULL, |
| 73 rank SMALLINT UNSIGNED, |
| 74 docstring TEXT, |
| 75 deprecated BOOLEAN DEFAULT FALSE, |
| 76 |
| 77 PRIMARY KEY (id), |
| 78 UNIQUE KEY (project_id, label), |
| 79 FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| 80 ) ENGINE=INNODB; |
| 81 |
| 82 |
| 83 CREATE TABLE FieldDef ( |
| 84 id INT NOT NULL AUTO_INCREMENT, |
| 85 project_id SMALLINT UNSIGNED NOT NULL, |
| 86 rank SMALLINT UNSIGNED, |
| 87 |
| 88 field_name VARCHAR(80) BINARY NOT NULL, |
| 89 -- TODO(jrobbins): more types |
| 90 field_type ENUM ('enum_type', 'int_type', 'str_type', 'user_type') NOT NULL, |
| 91 applicable_type VARCHAR(80), -- No value means: offered for all issue types |
| 92 applicable_predicate TEXT, -- No value means: TRUE |
| 93 is_required BOOLEAN, -- true means required if applicable |
| 94 is_multivalued BOOLEAN, |
| 95 -- TODO(jrobbins): access controls: restrict, grant |
| 96 -- Validation for int_type fields |
| 97 min_value INT, |
| 98 max_value INT, |
| 99 -- Validation for str_type fields |
| 100 regex VARCHAR(80), |
| 101 -- Validation for user_type fields |
| 102 needs_member BOOLEAN, -- User value can only be set to users who are members |
| 103 needs_perm VARCHAR(80), -- User value can only be set to users w/ that perm |
| 104 grants_perm VARCHAR(80), -- User named in this field gains this perm in the i
ssue |
| 105 -- notification options for user_type fields |
| 106 notify_on ENUM ('never', 'any_comment') DEFAULT 'never' NOT NULL, |
| 107 |
| 108 -- TODO(jrobbins): default value |
| 109 -- TODO(jrobbins): deprecated boolean? |
| 110 docstring TEXT, |
| 111 is_deleted BOOLEAN, -- If true, reap this field def after all values reaped. |
| 112 |
| 113 PRIMARY KEY (id), |
| 114 UNIQUE KEY (project_id, field_name), |
| 115 FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| 116 ) ENGINE=INNODB; |
| 117 |
| 118 |
| 119 CREATE TABLE FieldDef2Admin ( |
| 120 field_id INT NOT NULL, |
| 121 admin_id INT UNSIGNED NOT NULL, |
| 122 |
| 123 PRIMARY KEY (field_id, admin_id), |
| 124 FOREIGN KEY (field_id) REFERENCES FieldDef(id), |
| 125 FOREIGN KEY (admin_id) REFERENCES User(user_id) |
| 126 ) ENGINE=INNODB; |
| 127 |
| 128 |
| 129 CREATE TABLE Issue ( |
| 130 id INT NOT NULL AUTO_INCREMENT, |
| 131 shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL, |
| 132 project_id SMALLINT UNSIGNED NOT NULL, |
| 133 local_id INT NOT NULL, |
| 134 |
| 135 reporter_id INT UNSIGNED NOT NULL, |
| 136 owner_id INT UNSIGNED, |
| 137 status_id INT, |
| 138 |
| 139 -- These are each timestamps in seconds since the epoch. |
| 140 modified INT NOT NULL, |
| 141 opened INT, |
| 142 closed INT, |
| 143 |
| 144 derived_owner_id INT UNSIGNED, |
| 145 derived_status_id INT, |
| 146 |
| 147 deleted BOOLEAN, |
| 148 |
| 149 -- These are denormalized fields that should be updated when child |
| 150 -- records are added or removed for stars or attachments. If they |
| 151 -- get out of sync, they can be updated via an UPDATE ... SELECT statement. |
| 152 star_count INT DEFAULT 0, |
| 153 attachment_count INT DEFAULT 0, |
| 154 |
| 155 is_spam BOOLEAN DEFAULT FALSE, |
| 156 |
| 157 PRIMARY KEY(id), |
| 158 UNIQUE KEY (project_id, local_id), |
| 159 INDEX (shard, status_id), |
| 160 INDEX (shard, project_id), |
| 161 |
| 162 FOREIGN KEY (project_id) REFERENCES Project(project_id), |
| 163 FOREIGN KEY (reporter_id) REFERENCES User(user_id), |
| 164 FOREIGN KEY (owner_id) REFERENCES User(user_id), |
| 165 FOREIGN KEY (status_id) REFERENCES StatusDef(id), |
| 166 FOREIGN KEY (derived_owner_id) REFERENCES User(user_id) |
| 167 ) ENGINE=INNODB; |
| 168 |
| 169 |
| 170 -- This is a parallel table to the Issue table because we don't want |
| 171 -- any very wide columns in the Issue table that would slow it down. |
| 172 CREATE TABLE IssueSummary ( |
| 173 issue_id INT NOT NULL, |
| 174 summary TEXT, |
| 175 |
| 176 PRIMARY KEY (issue_id), |
| 177 FOREIGN KEY (issue_id) REFERENCES Issue(id) |
| 178 ) ENGINE=INNODB; |
| 179 |
| 180 |
| 181 CREATE TABLE Issue2Component ( |
| 182 issue_id INT NOT NULL, |
| 183 issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL, |
| 184 component_id INT NOT NULL, |
| 185 derived BOOLEAN DEFAULT FALSE, |
| 186 |
| 187 PRIMARY KEY (issue_id, component_id, derived), |
| 188 INDEX (component_id, issue_shard), |
| 189 |
| 190 FOREIGN KEY (issue_id) REFERENCES Issue(id), |
| 191 FOREIGN KEY (component_id) REFERENCES ComponentDef(id) |
| 192 ) ENGINE=INNODB; |
| 193 |
| 194 |
| 195 CREATE TABLE Issue2Label ( |
| 196 issue_id INT NOT NULL, |
| 197 issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL, |
| 198 label_id INT NOT NULL, |
| 199 derived BOOLEAN DEFAULT FALSE, |
| 200 |
| 201 PRIMARY KEY (issue_id, label_id, derived), |
| 202 INDEX (label_id, issue_shard), |
| 203 |
| 204 FOREIGN KEY (issue_id) REFERENCES Issue(id), |
| 205 FOREIGN KEY (label_id) REFERENCES LabelDef(id) |
| 206 ) ENGINE=INNODB; |
| 207 |
| 208 |
| 209 CREATE TABLE Issue2FieldValue ( |
| 210 issue_id INT NOT NULL, |
| 211 issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL, |
| 212 field_id INT NOT NULL, |
| 213 |
| 214 int_value INT, |
| 215 str_value VARCHAR(1024), |
| 216 user_id INT UNSIGNED, |
| 217 |
| 218 derived BOOLEAN DEFAULT FALSE, |
| 219 |
| 220 INDEX (issue_id, field_id), |
| 221 INDEX (field_id, issue_shard, int_value), |
| 222 INDEX (field_id, issue_shard, str_value(255)), |
| 223 INDEX (field_id, issue_shard, user_id), |
| 224 |
| 225 FOREIGN KEY (issue_id) REFERENCES Issue(id), |
| 226 FOREIGN KEY (field_id) REFERENCES FieldDef(id), |
| 227 FOREIGN KEY (user_id) REFERENCES User(user_id) |
| 228 ) ENGINE=INNODB; |
| 229 |
| 230 |
| 231 CREATE TABLE Issue2Cc ( |
| 232 issue_id INT NOT NULL, |
| 233 issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL, |
| 234 cc_id INT UNSIGNED NOT NULL, |
| 235 derived BOOLEAN DEFAULT FALSE, |
| 236 |
| 237 PRIMARY KEY (issue_id, cc_id), |
| 238 INDEX (cc_id, issue_shard), |
| 239 |
| 240 FOREIGN KEY (issue_id) REFERENCES Issue(id), |
| 241 FOREIGN KEY (cc_id) REFERENCES User(user_id) |
| 242 ) ENGINE=INNODB; |
| 243 |
| 244 |
| 245 CREATE TABLE Issue2Notify ( |
| 246 issue_id INT NOT NULL, |
| 247 email VARCHAR(80) NOT NULL, |
| 248 |
| 249 PRIMARY KEY (issue_id, email), |
| 250 |
| 251 FOREIGN KEY (issue_id) REFERENCES Issue(id) |
| 252 ) ENGINE=INNODB; |
| 253 |
| 254 |
| 255 CREATE TABLE IssueStar ( |
| 256 issue_id INT NOT NULL, |
| 257 user_id INT UNSIGNED NOT NULL, |
| 258 |
| 259 PRIMARY KEY (issue_id, user_id), |
| 260 INDEX (user_id), |
| 261 FOREIGN KEY (issue_id) REFERENCES Issue(id), |
| 262 FOREIGN KEY (user_id) REFERENCES User(user_id) |
| 263 ) ENGINE=INNODB; |
| 264 |
| 265 |
| 266 CREATE TABLE IssueRelation ( |
| 267 issue_id INT NOT NULL, |
| 268 dst_issue_id INT NOT NULL, |
| 269 |
| 270 -- Read as: src issue is blocked on dst issue. |
| 271 kind ENUM ('blockedon', 'mergedinto') NOT NULL, |
| 272 |
| 273 PRIMARY KEY (issue_id, dst_issue_id, kind), |
| 274 INDEX (issue_id), |
| 275 INDEX (dst_issue_id), |
| 276 FOREIGN KEY (issue_id) REFERENCES Issue(id), |
| 277 FOREIGN KEY (dst_issue_id) REFERENCES Issue(id) |
| 278 ) ENGINE=INNODB; |
| 279 |
| 280 |
| 281 CREATE TABLE DanglingIssueRelation ( |
| 282 issue_id INT NOT NULL, |
| 283 dst_issue_project VARCHAR(80), |
| 284 dst_issue_local_id INT, |
| 285 |
| 286 -- This table uses 'blocking' so that it can guarantee the src issue |
| 287 -- always exists, while the dst issue is always the dangling one. |
| 288 kind ENUM ('blockedon', 'blocking', 'mergedinto') NOT NULL, |
| 289 |
| 290 PRIMARY KEY (issue_id, dst_issue_project, dst_issue_local_id, kind), |
| 291 INDEX (issue_id), |
| 292 FOREIGN KEY (issue_id) REFERENCES Issue(id) |
| 293 ) ENGINE=INNODB; |
| 294 |
| 295 |
| 296 CREATE TABLE Comment ( |
| 297 id INT NOT NULL AUTO_INCREMENT, |
| 298 issue_id INT NOT NULL, |
| 299 created INT NOT NULL, |
| 300 project_id SMALLINT UNSIGNED NOT NULL, |
| 301 |
| 302 commenter_id INT UNSIGNED NOT NULL, |
| 303 content TEXT, |
| 304 inbound_message TEXT, |
| 305 |
| 306 was_escaped BOOLEAN DEFAULT FALSE, |
| 307 deleted_by INT UNSIGNED, |
| 308 is_spam BOOLEAN DEFAULT FALSE, |
| 309 |
| 310 PRIMARY KEY(id), |
| 311 INDEX (is_spam, project_id, created), |
| 312 INDEX (commenter_id, created), |
| 313 |
| 314 FOREIGN KEY (project_id) REFERENCES Project(project_id), |
| 315 FOREIGN KEY (issue_id) REFERENCES Issue(id), |
| 316 FOREIGN KEY (commenter_id) REFERENCES User(user_id), |
| 317 FOREIGN KEY (deleted_by) REFERENCES User(user_id) |
| 318 ) ENGINE=INNODB; |
| 319 |
| 320 |
| 321 CREATE TABLE Attachment ( |
| 322 id INT NOT NULL AUTO_INCREMENT, |
| 323 |
| 324 issue_id INT NOT NULL, |
| 325 comment_id INT, |
| 326 |
| 327 filename VARCHAR(255) NOT NULL, |
| 328 filesize INT NOT NULL, |
| 329 mimetype VARCHAR(255) NOT NULL, |
| 330 deleted BOOLEAN, |
| 331 gcs_object_id VARCHAR(1024) NOT NULL, |
| 332 |
| 333 PRIMARY KEY (id), |
| 334 INDEX (issue_id), |
| 335 INDEX (comment_id), |
| 336 FOREIGN KEY (issue_id) REFERENCES Issue(id) |
| 337 ) ENGINE=INNODB; |
| 338 |
| 339 |
| 340 CREATE TABLE IssueUpdate ( |
| 341 id INT NOT NULL AUTO_INCREMENT, |
| 342 issue_id INT NOT NULL, |
| 343 comment_id INT, |
| 344 |
| 345 field ENUM ( |
| 346 'summary', 'status', 'owner', 'cc', 'labels', 'blockedon', 'blocking', 'merged
into', |
| 347 'project', 'components', 'custom', 'is_spam' ) NOT NULL, |
| 348 old_value TEXT, |
| 349 new_value TEXT, |
| 350 added_user_id INT UNSIGNED, |
| 351 removed_user_id INT UNSIGNED, |
| 352 custom_field_name VARCHAR(255), |
| 353 is_spam BOOLEAN DEFAULT FALSE, |
| 354 |
| 355 PRIMARY KEY (id), |
| 356 INDEX (issue_id), |
| 357 INDEX (comment_id), |
| 358 FOREIGN KEY (issue_id) REFERENCES Issue(id) |
| 359 -- FOREIGN KEY (added_user_id) REFERENCES User(user_id), |
| 360 -- FOREIGN KEY (removed_user_id) REFERENCES User(user_id) |
| 361 ) ENGINE=INNODB; |
| 362 |
| 363 |
| 364 CREATE TABLE IssueFormerLocations ( |
| 365 issue_id INT NOT NULL, |
| 366 project_id SMALLINT UNSIGNED NOT NULL, |
| 367 local_id INT NOT NULL, |
| 368 |
| 369 INDEX (issue_id), |
| 370 UNIQUE KEY (project_id, local_id), |
| 371 FOREIGN KEY (issue_id) REFERENCES Issue(id) |
| 372 ) ENGINE=INNODB; |
| 373 |
| 374 |
| 375 CREATE TABLE Template ( |
| 376 id INT NOT NULL AUTO_INCREMENT, |
| 377 project_id SMALLINT UNSIGNED NOT NULL, |
| 378 name VARCHAR(255) BINARY NOT NULL, |
| 379 |
| 380 content TEXT, |
| 381 summary TEXT, |
| 382 summary_must_be_edited BOOLEAN, |
| 383 owner_id INT UNSIGNED, |
| 384 status VARCHAR(255), |
| 385 members_only BOOLEAN, |
| 386 owner_defaults_to_member BOOLEAN, |
| 387 component_required BOOLEAN DEFAULT FALSE, |
| 388 |
| 389 PRIMARY KEY (id), |
| 390 UNIQUE KEY (project_id, name), |
| 391 FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| 392 ) ENGINE=INNODB; |
| 393 |
| 394 |
| 395 CREATE TABLE Template2Label ( |
| 396 template_id INT NOT NULL, |
| 397 label VARCHAR(255) NOT NULL, |
| 398 |
| 399 PRIMARY KEY (template_id, label), |
| 400 FOREIGN KEY (template_id) REFERENCES Template(id) |
| 401 ) ENGINE=INNODB; |
| 402 |
| 403 |
| 404 CREATE TABLE Template2Admin ( |
| 405 template_id INT NOT NULL, |
| 406 admin_id INT UNSIGNED NOT NULL, |
| 407 |
| 408 PRIMARY KEY (template_id, admin_id), |
| 409 FOREIGN KEY (template_id) REFERENCES Template(id), |
| 410 FOREIGN KEY (admin_id) REFERENCES User(user_id) |
| 411 ) ENGINE=INNODB; |
| 412 |
| 413 |
| 414 CREATE TABLE Template2FieldValue ( |
| 415 template_id INT NOT NULL, |
| 416 field_id INT NOT NULL, |
| 417 |
| 418 int_value INT, |
| 419 str_value VARCHAR(1024), |
| 420 user_id INT UNSIGNED, |
| 421 |
| 422 INDEX (template_id, field_id), |
| 423 |
| 424 FOREIGN KEY (template_id) REFERENCES Template(id), |
| 425 FOREIGN KEY (field_id) REFERENCES FieldDef(id), |
| 426 FOREIGN KEY (user_id) REFERENCES User(user_id) |
| 427 ) ENGINE=INNODB; |
| 428 |
| 429 |
| 430 CREATE TABLE Template2Component ( |
| 431 template_id INT NOT NULL, |
| 432 component_id INT NOT NULL, |
| 433 |
| 434 PRIMARY KEY (template_id, component_id), |
| 435 |
| 436 FOREIGN KEY (template_id) REFERENCES Template(id), |
| 437 FOREIGN KEY (component_id) REFERENCES ComponentDef(id) |
| 438 ) ENGINE=INNODB; |
| 439 |
| 440 |
| 441 CREATE TABLE ProjectIssueConfig ( |
| 442 project_id SMALLINT UNSIGNED NOT NULL, |
| 443 |
| 444 statuses_offer_merge VARCHAR(255) NOT NULL, |
| 445 exclusive_label_prefixes VARCHAR(255) NOT NULL, |
| 446 default_template_for_developers INT NOT NULL, |
| 447 default_template_for_users INT NOT NULL, |
| 448 |
| 449 default_col_spec TEXT, |
| 450 default_sort_spec TEXT, |
| 451 default_x_attr TEXT, |
| 452 default_y_attr TEXT, |
| 453 |
| 454 custom_issue_entry_url TEXT, |
| 455 |
| 456 PRIMARY KEY (project_id), |
| 457 FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| 458 ) ENGINE=INNODB; |
| 459 |
| 460 |
| 461 CREATE TABLE FilterRule ( |
| 462 project_id SMALLINT UNSIGNED NOT NULL, |
| 463 rank SMALLINT UNSIGNED, |
| 464 |
| 465 -- TODO: or should this be broken down into structured fields? |
| 466 predicate TEXT NOT NULL, |
| 467 -- TODO: or should this be broken down into structured fields? |
| 468 consequence TEXT NOT NULL, |
| 469 |
| 470 INDEX (project_id), |
| 471 FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| 472 ) ENGINE=INNODB; |
| 473 |
| 474 |
| 475 -- Each row in this table indicates an issue that needs to be reindexed |
| 476 -- in the GAE fulltext index by our batch indexing cron job. |
| 477 CREATE TABLE ReindexQueue ( |
| 478 issue_id INT NOT NULL, |
| 479 created TIMESTAMP, |
| 480 |
| 481 PRIMARY KEY (issue_id), |
| 482 FOREIGN KEY (issue_id) REFERENCES Issue(id) |
| 483 ) ENGINE=INNODB; |
| 484 |
| 485 |
| 486 -- This holds counters with the highest issue local_id that is |
| 487 -- already used in each project. Clients should atomically increment |
| 488 -- the value for current project and then use the new counter value |
| 489 -- when creating an issue. |
| 490 CREATE TABLE LocalIDCounter ( |
| 491 project_id SMALLINT UNSIGNED NOT NULL, |
| 492 used_local_id INT NOT NULL, |
| 493 used_spam_id INT NOT NULL, |
| 494 |
| 495 PRIMARY KEY (project_id), |
| 496 FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| 497 ) ENGINE=INNODB; |
| 498 |
| 499 |
| 500 -- This is a saved query. It can be configured by a project owner to |
| 501 -- be used by all visitors to that project. Or, it can be a a |
| 502 -- personal saved query that appears on a user's "Saved queries" page |
| 503 -- and executes in the scope of one or more projects. |
| 504 CREATE TABLE SavedQuery ( |
| 505 id INT NOT NULL AUTO_INCREMENT, |
| 506 name VARCHAR(80) NOT NULL, |
| 507 |
| 508 -- For now, we only allow saved queries to be based off ane of the built-in |
| 509 -- query scopes, and those can never be deleted, so there can be no nesting, |
| 510 -- dangling references, and thus no need for cascading deletes. |
| 511 base_query_id INT, |
| 512 query TEXT NOT NULL, |
| 513 |
| 514 PRIMARY KEY (id) |
| 515 ) ENGINE=INNODB; |
| 516 |
| 517 |
| 518 -- Rows for built-in queries. These are in the database soley so that |
| 519 -- foreign key constraints are satisfied. These rows ar never read or updated. |
| 520 INSERT IGNORE INTO SavedQuery VALUES |
| 521 (1, 'All issues', 0, ''), |
| 522 (2, 'Open issues', 0, 'is:open'), |
| 523 (3, 'Open and owned by me', 0, 'is:open owner:me'), |
| 524 (4, 'Open and reported by me', 0, 'is:open reporter:me'), |
| 525 (5, 'Open and starred by me', 0, 'is:open is:starred'), |
| 526 (6, 'New issues', 0, 'status:new'), |
| 527 (7, 'Issues to verify', 0, 'status=fixed,done'), |
| 528 (8, 'Open with comment by me', 0, 'is:open commentby:me'); |
| 529 |
| 530 -- The sole purpose of this statement is to force user defined saved queries |
| 531 -- to have IDs greater than 100 so that 1-100 are reserved for built-ins. |
| 532 INSERT IGNORE INTO SavedQuery VALUES (100, '', 0, ''); |
| 533 |
| 534 |
| 535 -- User personal queries default to executing in the context of the |
| 536 -- project where they were created, but the user can edit them to make |
| 537 -- them into cross-project queries. Project saved queries always |
| 538 -- implicitly execute in the context of a project. |
| 539 CREATE TABLE SavedQueryExecutesInProject ( |
| 540 query_id INT NOT NULL, |
| 541 project_id SMALLINT UNSIGNED NOT NULL, |
| 542 |
| 543 PRIMARY KEY (query_id, project_id), |
| 544 INDEX (project_id), |
| 545 FOREIGN KEY (project_id) REFERENCES Project(project_id), |
| 546 FOREIGN KEY (query_id) REFERENCES SavedQuery(id) |
| 547 ) ENGINE=INNODB; |
| 548 |
| 549 |
| 550 -- These are the queries edited by the project owner on the project |
| 551 -- admin pages. |
| 552 CREATE TABLE Project2SavedQuery ( |
| 553 project_id SMALLINT UNSIGNED NOT NULL, |
| 554 rank SMALLINT UNSIGNED NOT NULL, |
| 555 query_id INT NOT NULL, |
| 556 |
| 557 -- TODO(jrobbins): visibility: owners, committers, contributors, anyone |
| 558 |
| 559 PRIMARY KEY (project_id, rank), |
| 560 FOREIGN KEY (project_id) REFERENCES Project(project_id), |
| 561 FOREIGN KEY (query_id) REFERENCES SavedQuery(id) |
| 562 ) ENGINE=INNODB; |
| 563 |
| 564 |
| 565 -- These are personal saved queries. |
| 566 CREATE TABLE User2SavedQuery ( |
| 567 user_id INT UNSIGNED NOT NULL, |
| 568 rank SMALLINT UNSIGNED NOT NULL, |
| 569 query_id INT NOT NULL, |
| 570 |
| 571 -- TODO(jrobbins): daily and weekly digests, and the ability to have |
| 572 -- certain subscriptions go to username+SOMETHING@example.com. |
| 573 subscription_mode ENUM ('noemail', 'immediate') DEFAULT 'noemail' NOT NULL, |
| 574 |
| 575 PRIMARY KEY (user_id, rank), |
| 576 FOREIGN KEY (user_id) REFERENCES User(user_id), |
| 577 FOREIGN KEY (query_id) REFERENCES SavedQuery(id) |
| 578 ) ENGINE=INNODB; |
| 579 |
| 580 |
| 581 -- Created whenever a user reports an issue or comment as spam. |
| 582 -- Note this is distinct from a SpamVerdict, which is issued by |
| 583 -- the system rather than a human user. |
| 584 CREATE TABLE SpamReport ( |
| 585 -- when this report was generated |
| 586 created TIMESTAMP NOT NULL, |
| 587 -- when the reported content was generated |
| 588 -- TODO(jrobbins): needs default current_time in MySQL 5.7. |
| 589 content_created TIMESTAMP NOT NULL, |
| 590 -- id of the reporting user |
| 591 user_id INT UNSIGNED NOT NULL, |
| 592 -- id of the reported user |
| 593 reported_user_id INT UNSIGNED NOT NULL, |
| 594 -- either this or issue_id must be set |
| 595 comment_id INT, |
| 596 -- either this or comment_id must be set |
| 597 issue_id INT, |
| 598 |
| 599 INDEX (issue_id), |
| 600 INDEX (comment_id), |
| 601 FOREIGN KEY (issue_id) REFERENCES Issue(id), |
| 602 FOREIGN KEY (comment_id) REFERENCES Comment(id) |
| 603 ) ENGINE=INNODB; |
| 604 |
| 605 |
| 606 -- Any time a human or the system sets is_spam to true, |
| 607 -- or changes it from true to false, we want to have a |
| 608 -- record of who did it and why. |
| 609 CREATE TABLE SpamVerdict ( |
| 610 -- when this verdict was generated |
| 611 created TIMESTAMP NOT NULL, |
| 612 |
| 613 -- id of the reporting user, may be null if it was |
| 614 -- an automatic classification. |
| 615 user_id INT UNSIGNED, |
| 616 |
| 617 -- id of the containing project. |
| 618 project_id INT NOT NULL, |
| 619 |
| 620 -- either this or issue_id must be set. |
| 621 comment_id INT, |
| 622 |
| 623 -- either this or comment_id must be set. |
| 624 issue_id INT, |
| 625 |
| 626 -- If the classifier issued the verdict, this should be set. |
| 627 classifier_confidence FLOAT, |
| 628 |
| 629 -- This should reflect the new is_spam value that was applied |
| 630 -- by this verdict, not the value it had prior. |
| 631 is_spam BOOLEAN NOT NULL, |
| 632 |
| 633 -- owner: a project owner marked it as spam |
| 634 -- threshhold: number of SpamReports from non-members was exceeded. |
| 635 -- classifier: the automatic classifier reports it as spam. |
| 636 reason ENUM ("manual", "threshold", "classifier") NOT NULL, |
| 637 |
| 638 overruled BOOL NOT NULL, |
| 639 |
| 640 INDEX (issue_id), |
| 641 INDEX (comment_id), |
| 642 INDEX (classifier_confidence), |
| 643 FOREIGN KEY (issue_id) REFERENCES Issue(id), |
| 644 FOREIGN KEY (comment_id) REFERENCES Comment(id) |
| 645 |
| 646 ) ENGINE=INNODB; |
OLD | NEW |