codepot/etc/codepot.mysql

-- ---------------------------------------------------------
-- 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;