rhodecode-upgrade-db.sql

ALTER TABLE changeset_comments RENAME TO changeset_comments_old;

CREATE TABLE changeset_comments (
	comment_id INTEGER NOT NULL, 
	repo_id INTEGER NOT NULL, 
	revision VARCHAR(40), 
	pull_request_id INTEGER, 
	line_no VARCHAR(10), 
	f_path VARCHAR(1000), 
	user_id INTEGER NOT NULL, 
	text VARCHAR(25000) NOT NULL, 
	created_on DATETIME NOT NULL, 
	modified_at DATETIME NOT NULL, 
	PRIMARY KEY (comment_id), 
	FOREIGN KEY(repo_id) REFERENCES repositories (repo_id), 
	FOREIGN KEY(pull_request_id) REFERENCES pull_requests (pull_request_id), 
	FOREIGN KEY(user_id) REFERENCES users (user_id)
);

INSERT INTO changeset_comments SELECT comment_id, repo_id, revision, "", line_no, f_path, user_id, text, modified_at, modified_at FROM changeset_comments_old;

ALTER TABLE repositories RENAME TO repositories_old;

CREATE TABLE repositories (
	repo_id INTEGER NOT NULL, 
	repo_name VARCHAR(255) NOT NULL, 
	clone_uri VARCHAR(255), 
	repo_type VARCHAR(255) NOT NULL, 
	user_id INTEGER NOT NULL, 
	private BOOLEAN, 
	statistics BOOLEAN, 
	downloads BOOLEAN, 
	description VARCHAR(10000), 
	created_on DATETIME, 
	landing_revision VARCHAR(255) NOT NULL, 
	fork_id INTEGER, 
	group_id INTEGER, 
	PRIMARY KEY (repo_id), 
	UNIQUE (repo_name), 
	UNIQUE (repo_id), 
	UNIQUE (repo_name), 
	FOREIGN KEY(user_id) REFERENCES users (user_id), 
	CHECK (private IN (0, 1)), 
	CHECK (statistics IN (0, 1)), 
	CHECK (downloads IN (0, 1)), 
	FOREIGN KEY(fork_id) REFERENCES repositories (repo_id), 
	FOREIGN KEY(group_id) REFERENCES groups (group_id)
);

INSERT INTO repositories select repo_id, repo_name, clone_uri, repo_type, user_id, private, statistics, downloads, description, created_on, "", fork_id, group_id FROM repositories_old;


ALTER TABLE users RENAME TO users_old;

CREATE TABLE users (
	user_id INTEGER NOT NULL, 
	username VARCHAR(255), 
	password VARCHAR(255), 
	active BOOLEAN, 
	admin BOOLEAN, 
	firstname VARCHAR(255), 
	lastname VARCHAR(255), 
	email VARCHAR(255), 
	last_login DATETIME, 
	ldap_dn VARCHAR(255), 
	api_key VARCHAR(255), 
	inherit_default_permissions BOOLEAN NOT NULL, 
	PRIMARY KEY (user_id), 
	UNIQUE (username), 
	UNIQUE (email), 
	UNIQUE (user_id), 
	CHECK (active IN (0, 1)), 
	CHECK (admin IN (0, 1)), 
	CHECK (inherit_default_permissions IN (0, 1))
);

INSERT INTO users select user_id, username, password, active, admin, name, lastname, email, last_login, ldap_dn, api_key, 1 FROM users_old;


ALTER TABLE users_groups RENAME TO users_groups_old;

CREATE TABLE users_groups (
	users_group_id INTEGER NOT NULL, 
	users_group_name VARCHAR(255) NOT NULL, 
	users_group_active BOOLEAN, 
	users_group_inherit_default_permissions BOOLEAN NOT NULL, 
	PRIMARY KEY (users_group_id), 
	UNIQUE (users_group_id), 
	UNIQUE (users_group_name), 
	CHECK (users_group_active IN (0, 1)), 
	CHECK (users_group_inherit_default_permissions IN (0, 1))
);

INSERT INTO users_groups select users_group_id, users_group_name, users_group_active, 1 FROM users_groups_old;

CREATE TABLE changeset_statuses (
	changeset_status_id INTEGER NOT NULL, 
	repo_id INTEGER NOT NULL, 
	user_id INTEGER NOT NULL, 
	revision VARCHAR(40) NOT NULL, 
	status VARCHAR(128) NOT NULL, 
	changeset_comment_id INTEGER, 
	modified_at DATETIME NOT NULL, 
	version INTEGER NOT NULL, 
	pull_request_id INTEGER, 
	PRIMARY KEY (changeset_status_id), 
	UNIQUE (repo_id, revision, version), 
	FOREIGN KEY(repo_id) REFERENCES repositories (repo_id), 
	FOREIGN KEY(user_id) REFERENCES users (user_id), 
	FOREIGN KEY(changeset_comment_id) REFERENCES changeset_comments (comment_id), 
	FOREIGN KEY(pull_request_id) REFERENCES pull_requests (pull_request_id)
);

CREATE TABLE pull_request_reviewers (
	pull_requests_reviewers_id INTEGER NOT NULL, 
	pull_request_id INTEGER NOT NULL, 
	user_id INTEGER, 
	PRIMARY KEY (pull_requests_reviewers_id), 
	FOREIGN KEY(pull_request_id) REFERENCES pull_requests (pull_request_id), 
	FOREIGN KEY(user_id) REFERENCES users (user_id)
);

CREATE TABLE pull_requests (
	pull_request_id INTEGER NOT NULL, 
	title VARCHAR(256), 
	description TEXT, 
	status VARCHAR(256) NOT NULL, 
	created_on DATETIME NOT NULL, 
	updated_on DATETIME NOT NULL, 
	user_id INTEGER NOT NULL, 
	revisions TEXT, 
	org_repo_id INTEGER NOT NULL, 
	org_ref VARCHAR(256) NOT NULL, 
	other_repo_id INTEGER NOT NULL, 
	other_ref VARCHAR(256) NOT NULL, 
	PRIMARY KEY (pull_request_id), 
	FOREIGN KEY(user_id) REFERENCES users (user_id), 
	FOREIGN KEY(org_repo_id) REFERENCES repositories (repo_id), 
	FOREIGN KEY(other_repo_id) REFERENCES repositories (repo_id)
);

CREATE TABLE user_email_map (
	email_id INTEGER NOT NULL, 
	user_id INTEGER, 
	email VARCHAR(255), 
	PRIMARY KEY (email_id), 
	UNIQUE (email), 
	UNIQUE (email_id), 
	FOREIGN KEY(user_id) REFERENCES users (user_id)
);

CREATE INDEX cc_revision_idx ON changeset_comments (revision);
CREATE INDEX cs_revision_idx ON changeset_statuses (revision);
CREATE INDEX cs_version_idx ON changeset_statuses (version);
CREATE INDEX key_idx ON cache_invalidation (cache_key);
CREATE INDEX notification_type_idx ON notifications (type);
CREATE INDEX p_perm_name_idx ON permissions (permission_name);
CREATE INDEX uem_email_idx ON user_email_map (email);

INSERT INTO permissions (permission_name, permission_longname) VALUES ('hg.fork.none', 'hg.fork.none');
INSERT INTO permissions (permission_name, permission_longname) VALUES ('hg.fork.repository', 'hg.fork.repository');