mirror of https://git.sr.ht/~sircmpwn/hub.sr.ht
104 lines
3.7 KiB
SQL
104 lines
3.7 KiB
SQL
CREATE TYPE visibility AS ENUM (
|
|
'PUBLIC',
|
|
'PRIVATE',
|
|
'UNLISTED'
|
|
);
|
|
|
|
CREATE TABLE "user" (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
updated timestamp without time zone NOT NULL,
|
|
username character varying(256),
|
|
email character varying(256) NOT NULL,
|
|
user_type character varying NOT NULL,
|
|
url character varying(256),
|
|
location character varying(256),
|
|
bio character varying(4096),
|
|
suspension_notice character varying(4096),
|
|
oauth_token character varying(256),
|
|
oauth_token_expires timestamp without time zone,
|
|
oauth_token_scopes character varying,
|
|
oauth_revocation_token character varying(256)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX ix_user_username ON "user" USING btree (username);
|
|
|
|
CREATE TABLE project (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
updated timestamp without time zone NOT NULL,
|
|
owner_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
|
name character varying(128) NOT NULL,
|
|
description character varying(512) NOT NULL,
|
|
website character varying,
|
|
visibility visibility DEFAULT 'UNLISTED'::visibility NOT NULL,
|
|
checklist_complete boolean DEFAULT false NOT NULL,
|
|
summary_repo_id integer,
|
|
tags character varying(16)[] DEFAULT '{}'::character varying[] NOT NULL
|
|
);
|
|
|
|
CREATE TABLE features (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
project_id integer NOT NULL REFERENCES project(id) ON DELETE CASCADE,
|
|
summary character varying NOT NULL
|
|
);
|
|
|
|
CREATE TABLE mailing_list (
|
|
id serial PRIMARY KEY,
|
|
remote_id integer NOT NULL,
|
|
created timestamp without time zone NOT NULL,
|
|
updated timestamp without time zone NOT NULL,
|
|
project_id integer NOT NULL REFERENCES project(id) ON DELETE CASCADE,
|
|
owner_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
|
name character varying(128) NOT NULL,
|
|
description character varying,
|
|
visibility visibility DEFAULT 'UNLISTED'::visibility NOT NULL
|
|
);
|
|
|
|
CREATE TABLE source_repo (
|
|
id serial PRIMARY KEY,
|
|
remote_id integer NOT NULL,
|
|
created timestamp without time zone NOT NULL,
|
|
updated timestamp without time zone NOT NULL,
|
|
project_id integer NOT NULL REFERENCES project(id) ON DELETE CASCADE,
|
|
owner_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
|
name character varying(128) NOT NULL,
|
|
description character varying,
|
|
repo_type character varying NOT NULL,
|
|
visibility visibility DEFAULT 'UNLISTED'::visibility NOT NULL,
|
|
CONSTRAINT project_source_repo_unique UNIQUE (project_id, remote_id, repo_type)
|
|
);
|
|
|
|
ALTER TABLE project
|
|
ADD CONSTRAINT project_summary_repo_id_fkey FOREIGN KEY (summary_repo_id) REFERENCES source_repo(id) ON DELETE CASCADE;
|
|
|
|
CREATE TABLE tracker (
|
|
id serial PRIMARY KEY,
|
|
remote_id integer NOT NULL,
|
|
created timestamp without time zone NOT NULL,
|
|
updated timestamp without time zone NOT NULL,
|
|
project_id integer NOT NULL REFERENCES project(id) ON DELETE CASCADE,
|
|
owner_id integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
|
name character varying(128) NOT NULL,
|
|
description character varying,
|
|
visibility visibility DEFAULT 'UNLISTED'::visibility NOT NULL
|
|
);
|
|
|
|
CREATE TABLE event (
|
|
id serial PRIMARY KEY,
|
|
created timestamp without time zone NOT NULL,
|
|
project_id integer NOT NULL REFERENCES project(id) ON DELETE CASCADE,
|
|
user_id integer REFERENCES "user"(id) ON DELETE CASCADE,
|
|
event_type character varying NOT NULL,
|
|
source_repo_id integer REFERENCES source_repo(id) ON DELETE CASCADE,
|
|
mailing_list_id integer REFERENCES mailing_list(id) ON DELETE CASCADE,
|
|
tracker_id integer REFERENCES tracker(id) ON DELETE CASCADE,
|
|
external_source character varying,
|
|
external_summary character varying,
|
|
external_details character varying,
|
|
external_summary_plain character varying,
|
|
external_details_plain character varying,
|
|
external_url character varying
|
|
);
|