282 lines
9.1 KiB
SQL
282 lines
9.1 KiB
SQL
-- ---------------------------------------------------------
|
|
-- This file is the Codepot database schema file for MySQL.
|
|
-- Note this file doesn't mandate which database to use.
|
|
-- ---------------------------------------------------------
|
|
|
|
CREATE TABLE site (
|
|
id VARCHAR(32) PRIMARY KEY,
|
|
name VARCHAR(128) NOT NULL,
|
|
summary VARCHAR(255) NOT NULL,
|
|
text TEXT NOT NULL,
|
|
|
|
createdon DATETIME NOT NULL,
|
|
updatedon DATETIME NOT NULL,
|
|
createdby VARCHAR(32) NOT NULL,
|
|
updatedby VARCHAR(32) NOT NULL
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE project (
|
|
id VARCHAR(32) PRIMARY KEY,
|
|
name VARCHAR(255) UNIQUE NOT NULL,
|
|
summary VARCHAR(255) NOT NULL,
|
|
description TEXT NOT NULL,
|
|
webhooks TEXT NOT NULL,
|
|
commitable CHAR(1) NOT NULL DEFAULT 'Y',
|
|
public CHAR(1) NOT NULL DEFAULT 'Y',
|
|
codecharset VARCHAR(32),
|
|
|
|
createdon DATETIME NOT NULL,
|
|
updatedon DATETIME NOT NULL,
|
|
createdby VARCHAR(32) NOT NULL,
|
|
updatedby VARCHAR(32) NOT NULL
|
|
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE project_membership (
|
|
projectid VARCHAR(32) NOT NULL,
|
|
userid VARCHAR(32) NOT NULL,
|
|
priority INTEGER NOT NULL,
|
|
UNIQUE KEY membership (projectid, userid),
|
|
KEY userid_index (userid),
|
|
KEY projectid_index (projectid),
|
|
CONSTRAINT membership_projectid FOREIGN KEY (projectid) REFERENCES project(id)
|
|
ON DELETE CASCADE ON UPDATE CASCADE
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE wiki (
|
|
projectid VARCHAR(32) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
text TEXT NOT NULL,
|
|
doctype CHAR(1) NOT NULL DEFAULT 'C',
|
|
columns INT NOT NULL DEFAULT 1,
|
|
|
|
createdon DATETIME NOT NULL,
|
|
updatedon DATETIME NOT NULL,
|
|
createdby VARCHAR(32) NOT NULL,
|
|
updatedby VARCHAR(32) NOT NULL,
|
|
|
|
UNIQUE KEY wiki_id (projectid, name),
|
|
|
|
CONSTRAINT wiki_projectid FOREIGN KEY (projectid) REFERENCES project(id)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE wiki_attachment (
|
|
projectid VARCHAR(32) NOT NULL,
|
|
wikiname VARCHAR(255) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
encname VARCHAR(255) NOT NULL,
|
|
|
|
createdon DATETIME NOT NULL,
|
|
createdby VARCHAR(32) NOT NULL,
|
|
|
|
UNIQUE KEY wiki_attachment_id (projectid, wikiname, name),
|
|
|
|
CONSTRAINT wiki_attachment_projectid FOREIGN KEY (projectid) REFERENCES project(id)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
|
|
CONSTRAINT wiki_attachment_wikiid FOREIGN KEY (projectid,wikiname) REFERENCES wiki(projectid,name)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE issue (
|
|
projectid VARCHAR(32) NOT NULL,
|
|
id BIGINT NOT NULL,
|
|
summary VARCHAR(255) NOT NULL,
|
|
description TEXT NOT NULL,
|
|
|
|
type VARCHAR(32) NOT NULL,
|
|
status VARCHAR(32) NOT NULL,
|
|
owner VARCHAR(255) NOT NULL,
|
|
priority VARCHAR(32) NOT NULL,
|
|
|
|
createdon DATETIME NOT NULL,
|
|
updatedon DATETIME NOT NULL,
|
|
createdby VARCHAR(32) NOT NULL,
|
|
updatedby VARCHAR(32) NOT NULL,
|
|
|
|
PRIMARY KEY (projectid, id),
|
|
KEY issue_status_type_summary (projectid, status, type, summary),
|
|
KEY issue_summary (projectid, summary),
|
|
|
|
CONSTRAINT issue_projectid FOREIGN KEY (projectid) REFERENCES project(id)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE issue_file_list (
|
|
projectid VARCHAR(32) NOT NULL,
|
|
issueid BIGINT NOT NULL,
|
|
filename VARCHAR(255) NOT NULL,
|
|
encname VARCHAR(255) NOT NULL,
|
|
md5sum CHAR(32) NOT NULL,
|
|
description VARCHAR(255) NOT NULL,
|
|
|
|
createdon DATETIME NOT NULL,
|
|
updatedon DATETIME NOT NULL,
|
|
createdby VARCHAR(32) NOT NULL,
|
|
updatedby VARCHAR(32) NOT NULL,
|
|
|
|
UNIQUE KEY issue_file_list_id (projectid, issueid, filename),
|
|
UNIQUE KEY (encname),
|
|
|
|
CONSTRAINT issue_file_list_projectid FOREIGN KEY (projectid) REFERENCES project(id)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
|
|
CONSTRAINT issue_file_list_issueid FOREIGN KEY (projectid,issueid) REFERENCES issue(projectid,id)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE issue_change (
|
|
projectid VARCHAR(32) NOT NULL,
|
|
id BIGINT NOT NULL,
|
|
sno BIGINT NOT NULL,
|
|
|
|
type VARCHAR(32) NOT NULL,
|
|
status VARCHAR(32) NOT NULL,
|
|
owner VARCHAR(255) NOT NULL,
|
|
priority VARCHAR(32) NOT NULL,
|
|
comment TEXT NOT NULL,
|
|
|
|
createdon DATETIME NOT NULL,
|
|
updatedon DATETIME NOT NULL,
|
|
createdby VARCHAR(32) NOT NULL,
|
|
updatedby VARCHAR(32) NOT NULL,
|
|
|
|
PRIMARY KEY (projectid, id, sno),
|
|
KEY issue_update_time (projectid, id, updatedon),
|
|
|
|
CONSTRAINT issue_change_id FOREIGN KEY (projectid,id) REFERENCES issue(projectid,id)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE
|
|
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE issue_change_file_list (
|
|
projectid VARCHAR(32) NOT NULL,
|
|
issueid BIGINT NOT NULL,
|
|
issuesno BIGINT NOT NULL,
|
|
filename VARCHAR(255) NOT NULL,
|
|
encname VARCHAR(255) NOT NULL,
|
|
|
|
createdon DATETIME NOT NULL,
|
|
updatedon DATETIME NOT NULL,
|
|
createdby VARCHAR(32) NOT NULL,
|
|
updatedby VARCHAR(32) NOT NULL,
|
|
|
|
UNIQUE KEY issue_change_file_list_id (projectid, issueid, filename),
|
|
|
|
CONSTRAINT issue_change_file_list_projectid FOREIGN KEY (projectid) REFERENCES project(id)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
|
|
CONSTRAINT issue_change_file_list_issueidsno FOREIGN KEY (projectid,issueid,issuesno) REFERENCES issue_change(projectid,id,sno)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE issue_coderev (
|
|
projectid VARCHAR(32) NOT NULL,
|
|
issueid BIGINT NOT NULL,
|
|
|
|
codeproid VARCHAR(32) NOT NULL,
|
|
coderev VARCHAR(64) NOT NULL, -- git commit id is 40 characters. subversion revision is a number.
|
|
|
|
UNIQUE KEY issue_coderev_uid(projectid, issueid, codeproid, coderev),
|
|
|
|
KEY issue_coderev_codekey (codeproid, coderev),
|
|
KEY issue_coderev_issuekey (projectid, issueid),
|
|
|
|
CONSTRAINT issue_coderev_projectid FOREIGN KEY (projectid) REFERENCES project(id)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
|
|
CONSTRAINT issue_coderev_codeproid FOREIGN KEY (codeproid) REFERENCES project(id)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE
|
|
|
|
-- Commit message is typically a free text. Its issue reference could be error-prone.
|
|
-- So i won't have this constraint enforced.
|
|
-- CONSTRAINT issue_coderev_issueid FOREIGN KEY (projectid,issueid) REFERENCES issue(projectid,id)
|
|
-- ON DELETE RESTRICT ON UPDATE CASCADE
|
|
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE file (
|
|
projectid VARCHAR(32) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
tag VARCHAR(54) NOT NULL,
|
|
description TEXT NOT NULL,
|
|
|
|
createdon DATETIME NOT NULL,
|
|
updatedon DATETIME NOT NULL,
|
|
createdby VARCHAR(32) NOT NULL,
|
|
updatedby VARCHAR(32) NOT NULL,
|
|
|
|
UNIQUE KEY file_id (projectid, name),
|
|
INDEX file_tagged_name (projectid, tag, name),
|
|
|
|
CONSTRAINT file_projectid FOREIGN KEY (projectid) REFERENCES project(id)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE file_list (
|
|
projectid VARCHAR(32) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
filename VARCHAR(255) NOT NULL,
|
|
encname VARCHAR(255) NOT NULL,
|
|
md5sum CHAR(32) NOT NULL,
|
|
description VARCHAR(255) NOT NULL,
|
|
|
|
createdon DATETIME NOT NULL,
|
|
updatedon DATETIME NOT NULL,
|
|
createdby VARCHAR(32) NOT NULL,
|
|
updatedby VARCHAR(32) NOT NULL,
|
|
|
|
INDEX file_list_id (projectid, name),
|
|
UNIQUE KEY file_list_fileid (projectid, filename),
|
|
UNIQUE KEY (encname),
|
|
|
|
CONSTRAINT file_list_projectid FOREIGN KEY (projectid,name) REFERENCES file(projectid,name)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE code_review (
|
|
projectid VARCHAR(32) NOT NULL,
|
|
rev BIGINT NOT NULL,
|
|
sno BIGINT NOT NULL,
|
|
comment TEXT NOT NULL,
|
|
|
|
createdon DATETIME NOT NULL,
|
|
createdby VARCHAR(32) NOT NULL,
|
|
|
|
updatedon DATETIME NOT NULL,
|
|
updatedby VARCHAR(32) NOT NULL,
|
|
|
|
UNIQUE KEY code_review_id (projectid, rev, sno),
|
|
|
|
CONSTRAINT code_review_projectid FOREIGN KEY (projectid) REFERENCES project(id)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE log (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
projectid VARCHAR(32) NOT NULL,
|
|
type VARCHAR(16) NOT NULL,
|
|
action VARCHAR(16) NOT NULL,
|
|
userid VARCHAR(32) NOT NULL,
|
|
message TEXT NOT NULL,
|
|
createdon DATETIME NOT NULL,
|
|
INDEX timed_project_type_action (createdon, projectid, type, action)
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE user_settings (
|
|
userid VARCHAR(32) PRIMARY KEY,
|
|
code_hide_line_num CHAR(1) NOT NULL,
|
|
code_hide_metadata CHAR(1) NOT NULL,
|
|
icon_name VARCHAR(255) UNIQUE NULL,
|
|
user_summary VARCHAR(255) NULL
|
|
) charset=utf8 engine=InnoDB;
|
|
|
|
CREATE TABLE user_account (
|
|
userid VARCHAR(32) PRIMARY KEY,
|
|
passwd VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255),
|
|
enabled CHAR(1) NOT NULL DEFAULT 'N' CHECK(enabled in ('Y', 'N'))
|
|
) charset=utf8 engine=InnoDB;
|
|
|