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 project-realted tables in monorail db. |
| 9 |
| 10 |
| 11 CREATE TABLE User ( |
| 12 user_id INT UNSIGNED NOT NULL, |
| 13 email VARCHAR(255) NOT NULL, -- lowercase |
| 14 |
| 15 is_site_admin BOOLEAN DEFAULT FALSE, |
| 16 obscure_email BOOLEAN DEFAULT TRUE, |
| 17 notify_issue_change BOOLEAN DEFAULT TRUE, |
| 18 notify_starred_issue_change BOOLEAN DEFAULT TRUE, |
| 19 banned VARCHAR(80), |
| 20 after_issue_update ENUM ('up_to_list', 'stay_same_issue', 'next_in_list'), |
| 21 keep_people_perms_open BOOLEAN DEFAULT FALSE, |
| 22 preview_on_hover BOOLEAN DEFAULT TRUE, |
| 23 ignore_action_limits BOOLEAN DEFAULT FALSE, |
| 24 |
| 25 PRIMARY KEY (user_id), |
| 26 UNIQUE KEY (email) |
| 27 ) ENGINE=INNODB; |
| 28 |
| 29 |
| 30 |
| 31 CREATE TABLE Project ( |
| 32 project_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 33 project_name VARCHAR(80) NOT NULL, |
| 34 |
| 35 summary TEXT, |
| 36 description TEXT, |
| 37 |
| 38 state ENUM ('live', 'archived', 'deletable') NOT NULL, |
| 39 access ENUM ('anyone', 'members_only') NOT NULL, |
| 40 read_only_reason VARCHAR(80), -- normally empty for read-write. |
| 41 state_reason VARCHAR(80), -- optional reason for doomed project. |
| 42 delete_time INT, -- if set, automatically transition to state deletable. |
| 43 |
| 44 issue_notify_address VARCHAR(80), |
| 45 attachment_bytes_used BIGINT DEFAULT 0, |
| 46 attachment_quota BIGINT DEFAULT 0, -- 50 MB default set in python code. |
| 47 |
| 48 cached_content_timestamp INT, |
| 49 recent_activity_timestamp INT, |
| 50 moved_to VARCHAR(250), |
| 51 process_inbound_email BOOLEAN DEFAULT FALSE, |
| 52 |
| 53 only_owners_remove_restrictions BOOLEAN DEFAULT FALSE, |
| 54 only_owners_see_contributors BOOLEAN DEFAULT FALSE, |
| 55 |
| 56 revision_url_format VARCHAR(250), |
| 57 |
| 58 home_page VARCHAR(250), |
| 59 docs_url VARCHAR(250), |
| 60 logo_gcs_id VARCHAR(250), |
| 61 logo_file_name VARCHAR(250), |
| 62 |
| 63 PRIMARY KEY (project_id), |
| 64 UNIQUE KEY (project_name) |
| 65 ) ENGINE=INNODB; |
| 66 |
| 67 |
| 68 CREATE TABLE ActionLimit ( |
| 69 user_id INT UNSIGNED NOT NULL, |
| 70 action_kind ENUM ( |
| 71 'project_creation', 'issue_comment', 'issue_attachment', |
| 72 'issue_bulk_edit', 'api_request'), |
| 73 recent_count INT, |
| 74 reset_timestamp INT, |
| 75 lifetime_count INT, |
| 76 lifetime_limit INT, |
| 77 period_soft_limit INT, |
| 78 period_hard_limit INT, |
| 79 |
| 80 PRIMARY KEY (user_id, action_kind) |
| 81 ) ENGINE=INNODB; |
| 82 |
| 83 |
| 84 CREATE TABLE DismissedCues ( |
| 85 user_id INT UNSIGNED NOT NULL, |
| 86 cue VARCHAR(40), -- names of the cue cards that the user has dismissed. |
| 87 |
| 88 INDEX (user_id) |
| 89 ) ENGINE=INNODB; |
| 90 |
| 91 |
| 92 CREATE TABLE User2Project ( |
| 93 project_id SMALLINT UNSIGNED NOT NULL, |
| 94 user_id INT UNSIGNED NOT NULL, |
| 95 role_name ENUM ('owner', 'committer', 'contributor'), |
| 96 |
| 97 PRIMARY KEY (project_id, user_id), |
| 98 INDEX (user_id), |
| 99 FOREIGN KEY (project_id) REFERENCES Project(project_id), |
| 100 FOREIGN KEY (user_id) REFERENCES User(user_id) |
| 101 ) ENGINE=INNODB; |
| 102 |
| 103 |
| 104 CREATE TABLE ExtraPerm ( |
| 105 project_id SMALLINT UNSIGNED NOT NULL, |
| 106 user_id INT UNSIGNED NOT NULL, |
| 107 perm VARCHAR(80), |
| 108 |
| 109 PRIMARY KEY (project_id, user_id, perm), |
| 110 FOREIGN KEY (project_id) REFERENCES Project(project_id), |
| 111 FOREIGN KEY (user_id) REFERENCES User(user_id) |
| 112 ) ENGINE=INNODB; |
| 113 |
| 114 |
| 115 CREATE TABLE MemberNotes ( |
| 116 project_id SMALLINT UNSIGNED NOT NULL, |
| 117 user_id INT UNSIGNED NOT NULL, |
| 118 notes TEXT, |
| 119 |
| 120 PRIMARY KEY (project_id, user_id), |
| 121 FOREIGN KEY (project_id) REFERENCES Project(project_id), |
| 122 FOREIGN KEY (user_id) REFERENCES User(user_id) |
| 123 ) ENGINE=INNODB; |
| 124 |
| 125 |
| 126 CREATE TABLE UserStar ( |
| 127 starred_user_id INT UNSIGNED NOT NULL, |
| 128 user_id INT UNSIGNED NOT NULL, |
| 129 |
| 130 PRIMARY KEY (starred_user_id, user_id), |
| 131 INDEX (user_id), |
| 132 FOREIGN KEY (user_id) REFERENCES User(user_id), |
| 133 FOREIGN KEY (starred_user_id) REFERENCES User(user_id) |
| 134 ) ENGINE=INNODB; |
| 135 |
| 136 |
| 137 CREATE TABLE ProjectStar ( |
| 138 project_id SMALLINT UNSIGNED NOT NULL, |
| 139 user_id INT UNSIGNED NOT NULL, |
| 140 |
| 141 PRIMARY KEY (project_id, user_id), |
| 142 INDEX (user_id), |
| 143 FOREIGN KEY (user_id) REFERENCES User(user_id), |
| 144 FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| 145 ) ENGINE=INNODB; |
| 146 |
| 147 |
| 148 CREATE TABLE UserGroup ( |
| 149 user_id INT UNSIGNED NOT NULL, |
| 150 group_id INT UNSIGNED NOT NULL, |
| 151 role ENUM ('owner', 'member') NOT NULL DEFAULT 'member', |
| 152 |
| 153 PRIMARY KEY (user_id, group_id), |
| 154 INDEX (group_id), |
| 155 FOREIGN KEY (user_id) REFERENCES User(user_id), |
| 156 FOREIGN KEY (group_id) REFERENCES User(user_id) |
| 157 |
| 158 ) ENGINE=INNODB; |
| 159 |
| 160 |
| 161 CREATE TABLE UserGroupSettings ( |
| 162 group_id INT UNSIGNED NOT NULL, |
| 163 |
| 164 who_can_view_members ENUM ('owners', 'members', 'anyone'), |
| 165 |
| 166 external_group_type ENUM ('chrome_infra_auth', 'mdb', 'baggins'), |
| 167 -- timestamps in seconds since the epoch. |
| 168 last_sync_time INT, |
| 169 |
| 170 PRIMARY KEY (group_id), |
| 171 FOREIGN KEY (group_id) REFERENCES User(user_id) |
| 172 ) ENGINE=INNODB; |
| 173 |
| 174 |
| 175 CREATE TABLE Group2Project ( |
| 176 group_id INT UNSIGNED NOT NULL, |
| 177 project_id SMALLINT UNSIGNED NOT NULL, |
| 178 |
| 179 PRIMARY KEY (group_id, project_id), |
| 180 |
| 181 FOREIGN KEY (group_id) REFERENCES UserGroupSettings(group_id), |
| 182 FOREIGN KEY (project_id) REFERENCES Project(project_id) |
| 183 ) ENGINE=INNODB; |
| 184 |
| 185 |
| 186 -- These are quick-edit commands that the user can easily repeat. |
| 187 CREATE TABLE QuickEditHistory ( |
| 188 user_id INT UNSIGNED NOT NULL, |
| 189 project_id SMALLINT UNSIGNED NOT NULL, |
| 190 slot_num SMALLINT UNSIGNED NOT NULL, |
| 191 |
| 192 command VARCHAR(255) NOT NULL, |
| 193 comment TEXT NOT NULL, |
| 194 |
| 195 PRIMARY KEY (user_id, project_id, slot_num), |
| 196 FOREIGN KEY (project_id) REFERENCES Project(project_id), |
| 197 FOREIGN KEY (user_id) REFERENCES User(user_id) |
| 198 ) ENGINE=INNODB; |
| 199 |
| 200 |
| 201 -- This allows us to offer the most recent command to the user again |
| 202 -- as the default quick-edit command for next time. |
| 203 CREATE TABLE QuickEditMostRecent ( |
| 204 user_id INT UNSIGNED NOT NULL, |
| 205 project_id SMALLINT UNSIGNED NOT NULL, |
| 206 slot_num SMALLINT UNSIGNED NOT NULL, |
| 207 |
| 208 PRIMARY KEY (user_id, project_id), |
| 209 FOREIGN KEY (project_id) REFERENCES Project(project_id), |
| 210 FOREIGN KEY (user_id) REFERENCES User(user_id) |
| 211 ) ENGINE=INNODB; |
OLD | NEW |