schema.sql

CREATE TABLE public."user" (
    id integer NOT NULL,
    username character varying(255),
    password character varying(255),
    role character varying(255)
);
ALTER TABLE ONLY public."user"
    ADD CONSTRAINT user_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public."user"
    ADD CONSTRAINT user_username_unique UNIQUE (username);



CREATE TABLE public.category (
    id integer NOT NULL,
    type character varying(255) NOT NULL,
    value character varying(255) NOT NULL
);
ALTER TABLE ONLY public.category
    ADD CONSTRAINT category_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.category
    ADD CONSTRAINT category_value_unique UNIQUE (value);



CREATE TABLE public.content (
    id integer NOT NULL,
    "authorId" integer NOT NULL,
    type character varying(255) NOT NULL,
    "parentId" integer,
    "postId" integer,
    title text,
    link character varying(255),
    thumbnail character varying(255),
    text text NOT NULL
);
ALTER TABLE ONLY public.content
    ADD CONSTRAINT content_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.content
    ADD CONSTRAINT content_authorid_foreign FOREIGN KEY ("authorId") REFERENCES public."user"(id);
ALTER TABLE ONLY public.content
    ADD CONSTRAINT content_parentid_foreign FOREIGN KEY ("parentId") REFERENCES public.content(id);
ALTER TABLE ONLY public.content
    ADD CONSTRAINT content_postid_foreign FOREIGN KEY ("postId") REFERENCES public.content(id);




CREATE TABLE public.vote (
    id integer NOT NULL,
    "voterId" integer NOT NULL,
    "contentId" integer NOT NULL,
    "categoryId" integer NOT NULL
);
ALTER TABLE ONLY public.vote
    ADD CONSTRAINT vote_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.vote
    ADD CONSTRAINT vote_voterid_contentid_categoryid_unique UNIQUE ("voterId", "contentId", "categoryId");
ALTER TABLE ONLY public.vote
    ADD CONSTRAINT vote_categoryid_foreign FOREIGN KEY ("categoryId") REFERENCES public.category(id);
ALTER TABLE ONLY public.vote
    ADD CONSTRAINT vote_contentid_foreign FOREIGN KEY ("contentId") REFERENCES public.content(id);
ALTER TABLE ONLY public.vote
    ADD CONSTRAINT vote_voterid_foreign FOREIGN KEY ("voterId") REFERENCES public."user"(id);




CREATE TABLE public.subscription (
    id integer NOT NULL,
    "subscriberId" integer NOT NULL,
    "idolId" integer,
    "categoryId" integer,
    rank integer DEFAULT 0
);
ALTER TABLE ONLY public.subscription
    ADD CONSTRAINT subscription_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.subscription
    ADD CONSTRAINT subscription_categoryid_foreign FOREIGN KEY ("categoryId") REFERENCES public.category(id);
ALTER TABLE ONLY public.subscription
    ADD CONSTRAINT subscription_idolid_foreign FOREIGN KEY ("idolId") REFERENCES public."user"(id);
ALTER TABLE ONLY public.subscription
    ADD CONSTRAINT subscription_subscriberid_foreign FOREIGN KEY ("subscriberId") REFERENCES public."user"(id);