blob: 82bbde8aadac7b8ac96412d3ff97ed2db9c2e51a [file] [log] [blame]
-- Copyright 2016 The Chromium Authors
-- Use of this source code is governed by a BSD-style license that can be
-- found in the LICENSE file.
-- Create project-related tables in monorail db.
-- The User table has the mapping from user_id to email addresses, and
-- user settings information that is needed almost every time that
-- we load a user. E.g., when showing issue owners on the list page.
CREATE TABLE User (
user_id INT UNSIGNED NOT NULL,
email VARCHAR(255) NOT NULL, -- lowercase
is_site_admin BOOLEAN DEFAULT FALSE,
obscure_email BOOLEAN DEFAULT TRUE,
-- TODO(jrobbins): Move some of these to UserPrefs.
notify_issue_change BOOLEAN DEFAULT TRUE, -- Pref
notify_starred_issue_change BOOLEAN DEFAULT TRUE, -- Pref
email_compact_subject BOOLEAN DEFAULT FALSE, -- Pref
email_view_widget BOOLEAN DEFAULT TRUE, -- Pref
notify_starred_ping BOOLEAN DEFAULT FALSE, -- Pref
banned VARCHAR(80),
after_issue_update ENUM (
'up_to_list', 'stay_same_issue', 'next_in_list'), -- Pref
keep_people_perms_open BOOLEAN DEFAULT FALSE, -- Pref
preview_on_hover BOOLEAN DEFAULT TRUE, -- Pref
ignore_action_limits BOOLEAN DEFAULT FALSE,
last_visit_timestamp INT,
email_bounce_timestamp INT,
vacation_message VARCHAR(80),
PRIMARY KEY (user_id),
UNIQUE KEY (email)
) ENGINE=INNODB;
-- Row to represent all deleted users i Monorail.
INSERT IGNORE INTO User (user_id, email) VALUES (1, '');
-- The UserPrefs table has open-ended key/value pairs that affect how
-- we present information to that user when we generate a web page for
-- that user or send an email to that user. E.g., ("code_font",
-- "true") would mean that issue content should be shown to that user
-- in a monospace font. Only non-default preference values are
-- stored: users who have never set any preferences will have no rows.
CREATE TABLE UserPrefs (
user_id INT UNSIGNED NOT NULL,
name VARCHAR(40),
value VARCHAR(80),
UNIQUE KEY (user_id, name)
) ENGINE=INNODB;
CREATE TABLE UserCommits (
commit_sha VARCHAR(40),
author_id INT UNSIGNED NOT NULL,
commit_time INT NOT NULL,
commit_message TEXT,
commit_repo_url VARCHAR(255),
PRIMARY KEY (commit_sha),
INDEX (author_id, commit_time),
INDEX (commit_time)
) ENGINE=INNODB;
CREATE TABLE Project (
project_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
project_name VARCHAR(80) NOT NULL,
summary TEXT,
description TEXT,
state ENUM ('live', 'archived', 'deletable') NOT NULL,
access ENUM ('anyone', 'members_only') NOT NULL,
read_only_reason VARCHAR(80), -- normally empty for read-write.
state_reason VARCHAR(80), -- optional reason for doomed project.
delete_time INT, -- if set, automatically transition to state deletable.
issue_notify_address VARCHAR(80),
attachment_bytes_used BIGINT DEFAULT 0,
attachment_quota BIGINT DEFAULT 0, -- 50 MB default set in python code.
cached_content_timestamp INT,
recent_activity_timestamp INT,
moved_to VARCHAR(250),
process_inbound_email BOOLEAN DEFAULT FALSE,
only_owners_remove_restrictions BOOLEAN DEFAULT FALSE,
only_owners_see_contributors BOOLEAN DEFAULT FALSE,
revision_url_format VARCHAR(250),
home_page VARCHAR(250),
docs_url VARCHAR(250),
source_url VARCHAR(250),
logo_gcs_id VARCHAR(250),
logo_file_name VARCHAR(250),
issue_notify_always_detailed BOOLEAN DEFAULT FALSE,
PRIMARY KEY (project_id),
UNIQUE KEY (project_name)
) ENGINE=INNODB;
CREATE TABLE User2Project (
project_id SMALLINT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
role_name ENUM ('owner', 'committer', 'contributor'),
PRIMARY KEY (project_id, user_id),
INDEX (user_id),
FOREIGN KEY (project_id) REFERENCES Project(project_id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE LinkedAccount (
parent_id INT UNSIGNED NOT NULL,
child_id INT UNSIGNED NOT NULL,
KEY (parent_id),
UNIQUE KEY (child_id),
FOREIGN KEY (parent_id) REFERENCES User(user_id),
FOREIGN KEY (child_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE LinkedAccountInvite (
parent_id INT UNSIGNED NOT NULL,
child_id INT UNSIGNED NOT NULL,
KEY (parent_id),
UNIQUE KEY (child_id),
FOREIGN KEY (parent_id) REFERENCES User(user_id),
FOREIGN KEY (child_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE ExtraPerm (
project_id SMALLINT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
perm VARCHAR(80),
PRIMARY KEY (project_id, user_id, perm),
FOREIGN KEY (project_id) REFERENCES Project(project_id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE MemberNotes (
project_id SMALLINT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
notes TEXT,
PRIMARY KEY (project_id, user_id),
FOREIGN KEY (project_id) REFERENCES Project(project_id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE AutocompleteExclusion (
project_id SMALLINT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
ac_exclude BOOLEAN DEFAULT TRUE,
no_expand BOOLEAN DEFAULT FALSE,
PRIMARY KEY (project_id, user_id),
FOREIGN KEY (project_id) REFERENCES Project(project_id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE UserStar (
starred_user_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
PRIMARY KEY (starred_user_id, user_id),
INDEX (user_id),
FOREIGN KEY (user_id) REFERENCES User(user_id),
FOREIGN KEY (starred_user_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE ProjectStar (
project_id SMALLINT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
PRIMARY KEY (project_id, user_id),
INDEX (user_id),
FOREIGN KEY (user_id) REFERENCES User(user_id),
FOREIGN KEY (project_id) REFERENCES Project(project_id)
) ENGINE=INNODB;
CREATE TABLE UserGroup (
user_id INT UNSIGNED NOT NULL,
group_id INT UNSIGNED NOT NULL,
role ENUM ('owner', 'member') NOT NULL DEFAULT 'member',
PRIMARY KEY (user_id, group_id),
INDEX (group_id),
FOREIGN KEY (user_id) REFERENCES User(user_id),
FOREIGN KEY (group_id) REFERENCES User(user_id)
) ENGINE=INNODB;
CREATE TABLE UserGroupSettings (
group_id INT UNSIGNED NOT NULL,
who_can_view_members ENUM ('owners', 'members', 'anyone'),
external_group_type ENUM (
'chrome_infra_auth', 'mdb', 'baggins', 'computed'),
-- timestamps in seconds since the epoch.
last_sync_time INT,
notify_members BOOL DEFAULT TRUE,
notify_group BOOL DEFAULT FALSE,
PRIMARY KEY (group_id),
FOREIGN KEY (group_id) REFERENCES User(user_id)
) ENGINE=INNODB;
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;
-- These are quick-edit commands that the user can easily repeat.
CREATE TABLE QuickEditHistory (
user_id INT UNSIGNED NOT NULL,
project_id SMALLINT UNSIGNED NOT NULL,
slot_num SMALLINT UNSIGNED NOT NULL,
command VARCHAR(255) NOT NULL,
comment TEXT NOT NULL,
PRIMARY KEY (user_id, project_id, slot_num),
FOREIGN KEY (project_id) REFERENCES Project(project_id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;
-- This allows us to offer the most recent command to the user again
-- as the default quick-edit command for next time.
CREATE TABLE QuickEditMostRecent (
user_id INT UNSIGNED NOT NULL,
project_id SMALLINT UNSIGNED NOT NULL,
slot_num SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (user_id, project_id),
FOREIGN KEY (project_id) REFERENCES Project(project_id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=INNODB;