blob: cb3cd4297cee9e95f7f308f1f56bfe3b97b19b63 [file] [log] [blame]
Copybara854996b2021-09-07 19:36:02 +00001-- 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-related tables in monorail db.
9
10
11-- The User table has the mapping from user_id to email addresses, and
12-- user settings information that is needed almost every time that
13-- we load a user. E.g., when showing issue owners on the list page.
14CREATE TABLE User (
15 user_id INT UNSIGNED NOT NULL,
16 email VARCHAR(255) NOT NULL, -- lowercase
17
18 is_site_admin BOOLEAN DEFAULT FALSE,
19 obscure_email BOOLEAN DEFAULT TRUE,
20
21 -- TODO(jrobbins): Move some of these to UserPrefs.
22 notify_issue_change BOOLEAN DEFAULT TRUE, -- Pref
23 notify_starred_issue_change BOOLEAN DEFAULT TRUE, -- Pref
24 email_compact_subject BOOLEAN DEFAULT FALSE, -- Pref
25 email_view_widget BOOLEAN DEFAULT TRUE, -- Pref
26 notify_starred_ping BOOLEAN DEFAULT FALSE, -- Pref
27 banned VARCHAR(80),
28 after_issue_update ENUM (
29 'up_to_list', 'stay_same_issue', 'next_in_list'), -- Pref
30 keep_people_perms_open BOOLEAN DEFAULT FALSE, -- Pref
31 preview_on_hover BOOLEAN DEFAULT TRUE, -- Pref
32 ignore_action_limits BOOLEAN DEFAULT FALSE,
33 last_visit_timestamp INT,
34 email_bounce_timestamp INT,
35 vacation_message VARCHAR(80),
36
37 PRIMARY KEY (user_id),
38 UNIQUE KEY (email)
39) ENGINE=INNODB;
40
41-- Row to represent all deleted users i Monorail.
42INSERT IGNORE INTO User (user_id, email) VALUES (1, '');
43
44-- The UserPrefs table has open-ended key/value pairs that affect how
45-- we present information to that user when we generate a web page for
46-- that user or send an email to that user. E.g., ("code_font",
47-- "true") would mean that issue content should be shown to that user
48-- in a monospace font. Only non-default preference values are
49-- stored: users who have never set any preferences will have no rows.
50CREATE TABLE UserPrefs (
51 user_id INT UNSIGNED NOT NULL,
52 name VARCHAR(40),
53 value VARCHAR(80),
54
55 UNIQUE KEY (user_id, name)
56) ENGINE=INNODB;
57
58
59CREATE TABLE UserCommits (
60 commit_sha VARCHAR(40),
61 author_id INT UNSIGNED NOT NULL,
62 commit_time INT NOT NULL,
63 commit_message TEXT,
64 commit_repo_url VARCHAR(255),
65
66 PRIMARY KEY (commit_sha),
67 INDEX (author_id, commit_time),
68 INDEX (commit_time)
69) ENGINE=INNODB;
70
71CREATE TABLE Project (
72 project_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
73 project_name VARCHAR(80) NOT NULL,
74
75 summary TEXT,
76 description TEXT,
77
78 state ENUM ('live', 'archived', 'deletable') NOT NULL,
79 access ENUM ('anyone', 'members_only') NOT NULL,
80 read_only_reason VARCHAR(80), -- normally empty for read-write.
81 state_reason VARCHAR(80), -- optional reason for doomed project.
82 delete_time INT, -- if set, automatically transition to state deletable.
83
84 issue_notify_address VARCHAR(80),
85 attachment_bytes_used BIGINT DEFAULT 0,
86 attachment_quota BIGINT DEFAULT 0, -- 50 MB default set in python code.
87
88 cached_content_timestamp INT,
89 recent_activity_timestamp INT,
90 moved_to VARCHAR(250),
91 process_inbound_email BOOLEAN DEFAULT FALSE,
92
93 only_owners_remove_restrictions BOOLEAN DEFAULT FALSE,
94 only_owners_see_contributors BOOLEAN DEFAULT FALSE,
95
96 revision_url_format VARCHAR(250),
97
98 home_page VARCHAR(250),
99 docs_url VARCHAR(250),
100 source_url VARCHAR(250),
101 logo_gcs_id VARCHAR(250),
102 logo_file_name VARCHAR(250),
103
104 issue_notify_always_detailed BOOLEAN DEFAULT FALSE,
105
106 PRIMARY KEY (project_id),
107 UNIQUE KEY (project_name)
108) ENGINE=INNODB;
109
110
111CREATE TABLE User2Project (
112 project_id SMALLINT UNSIGNED NOT NULL,
113 user_id INT UNSIGNED NOT NULL,
114 role_name ENUM ('owner', 'committer', 'contributor'),
115
116 PRIMARY KEY (project_id, user_id),
117 INDEX (user_id),
118 FOREIGN KEY (project_id) REFERENCES Project(project_id),
119 FOREIGN KEY (user_id) REFERENCES User(user_id)
120) ENGINE=INNODB;
121
122
123CREATE TABLE LinkedAccount (
124 parent_id INT UNSIGNED NOT NULL,
125 child_id INT UNSIGNED NOT NULL,
126
127 KEY (parent_id),
128 UNIQUE KEY (child_id),
129 FOREIGN KEY (parent_id) REFERENCES User(user_id),
130 FOREIGN KEY (child_id) REFERENCES User(user_id)
131) ENGINE=INNODB;
132
133
134CREATE TABLE LinkedAccountInvite (
135 parent_id INT UNSIGNED NOT NULL,
136 child_id INT UNSIGNED NOT NULL,
137
138 KEY (parent_id),
139 UNIQUE KEY (child_id),
140 FOREIGN KEY (parent_id) REFERENCES User(user_id),
141 FOREIGN KEY (child_id) REFERENCES User(user_id)
142) ENGINE=INNODB;
143
144
145CREATE TABLE ExtraPerm (
146 project_id SMALLINT UNSIGNED NOT NULL,
147 user_id INT UNSIGNED NOT NULL,
148 perm VARCHAR(80),
149
150 PRIMARY KEY (project_id, user_id, perm),
151 FOREIGN KEY (project_id) REFERENCES Project(project_id),
152 FOREIGN KEY (user_id) REFERENCES User(user_id)
153) ENGINE=INNODB;
154
155
156CREATE TABLE MemberNotes (
157 project_id SMALLINT UNSIGNED NOT NULL,
158 user_id INT UNSIGNED NOT NULL,
159 notes TEXT,
160
161 PRIMARY KEY (project_id, user_id),
162 FOREIGN KEY (project_id) REFERENCES Project(project_id),
163 FOREIGN KEY (user_id) REFERENCES User(user_id)
164) ENGINE=INNODB;
165
166
167CREATE TABLE AutocompleteExclusion (
168 project_id SMALLINT UNSIGNED NOT NULL,
169 user_id INT UNSIGNED NOT NULL,
170 ac_exclude BOOLEAN DEFAULT TRUE,
171 no_expand BOOLEAN DEFAULT FALSE,
172
173 PRIMARY KEY (project_id, user_id),
174 FOREIGN KEY (project_id) REFERENCES Project(project_id),
175 FOREIGN KEY (user_id) REFERENCES User(user_id)
176) ENGINE=INNODB;
177
178
179CREATE TABLE UserStar (
180 starred_user_id INT UNSIGNED NOT NULL,
181 user_id INT UNSIGNED NOT NULL,
182
183 PRIMARY KEY (starred_user_id, user_id),
184 INDEX (user_id),
185 FOREIGN KEY (user_id) REFERENCES User(user_id),
186 FOREIGN KEY (starred_user_id) REFERENCES User(user_id)
187) ENGINE=INNODB;
188
189
190CREATE TABLE ProjectStar (
191 project_id SMALLINT UNSIGNED NOT NULL,
192 user_id INT UNSIGNED NOT NULL,
193
194 PRIMARY KEY (project_id, user_id),
195 INDEX (user_id),
196 FOREIGN KEY (user_id) REFERENCES User(user_id),
197 FOREIGN KEY (project_id) REFERENCES Project(project_id)
198) ENGINE=INNODB;
199
200
201CREATE TABLE UserGroup (
202 user_id INT UNSIGNED NOT NULL,
203 group_id INT UNSIGNED NOT NULL,
204 role ENUM ('owner', 'member') NOT NULL DEFAULT 'member',
205
206 PRIMARY KEY (user_id, group_id),
207 INDEX (group_id),
208 FOREIGN KEY (user_id) REFERENCES User(user_id),
209 FOREIGN KEY (group_id) REFERENCES User(user_id)
210
211) ENGINE=INNODB;
212
213
214CREATE TABLE UserGroupSettings (
215 group_id INT UNSIGNED NOT NULL,
216
217 who_can_view_members ENUM ('owners', 'members', 'anyone'),
218
219 external_group_type ENUM (
220 'chrome_infra_auth', 'mdb', 'baggins', 'computed'),
221 -- timestamps in seconds since the epoch.
222 last_sync_time INT,
223 notify_members BOOL DEFAULT TRUE,
224 notify_group BOOL DEFAULT FALSE,
225
226 PRIMARY KEY (group_id),
227 FOREIGN KEY (group_id) REFERENCES User(user_id)
228) ENGINE=INNODB;
229
230
231CREATE TABLE Group2Project (
232 group_id INT UNSIGNED NOT NULL,
233 project_id SMALLINT UNSIGNED NOT NULL,
234
235 PRIMARY KEY (group_id, project_id),
236
237 FOREIGN KEY (group_id) REFERENCES UserGroupSettings(group_id),
238 FOREIGN KEY (project_id) REFERENCES Project(project_id)
239) ENGINE=INNODB;
240
241
242-- These are quick-edit commands that the user can easily repeat.
243CREATE TABLE QuickEditHistory (
244 user_id INT UNSIGNED NOT NULL,
245 project_id SMALLINT UNSIGNED NOT NULL,
246 slot_num SMALLINT UNSIGNED NOT NULL,
247
248 command VARCHAR(255) NOT NULL,
249 comment TEXT NOT NULL,
250
251 PRIMARY KEY (user_id, project_id, slot_num),
252 FOREIGN KEY (project_id) REFERENCES Project(project_id),
253 FOREIGN KEY (user_id) REFERENCES User(user_id)
254) ENGINE=INNODB;
255
256
257-- This allows us to offer the most recent command to the user again
258-- as the default quick-edit command for next time.
259CREATE TABLE QuickEditMostRecent (
260 user_id INT UNSIGNED NOT NULL,
261 project_id SMALLINT UNSIGNED NOT NULL,
262 slot_num SMALLINT UNSIGNED NOT NULL,
263
264 PRIMARY KEY (user_id, project_id),
265 FOREIGN KEY (project_id) REFERENCES Project(project_id),
266 FOREIGN KEY (user_id) REFERENCES User(user_id)
267) ENGINE=INNODB;