schema.sql

--- Author : Corentin POUPRY (HelloEdit)


CREATE TABLE public.branch (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(25) NOT NULL,
    locale VARCHAR(5) UNIQUE NOT NULL,
    url VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

INSERT INTO public.branch (id, name, locale, url, created_at) VALUES
    (66711, 'SCP Foundation', 'en', 'http://www.scp-wiki.net/', '2008-07-24 11:00:22+00:00'),
    (486864, 'SCP 재단', 'ko', 'http://ko.scp-wiki.net/', '2012-08-10 10:01:31+00:00'),
    (530812, 'SCP基金会', 'cn', 'http://scp-wiki-cn.wikidot.com/', '2013-01-30 17:36:27+00:00'),
    (464696, 'Fondation SCP', 'fr', 'http://fondationscp.wikidot.com/', '2012-03-21 14:35:40+00:00'),
    (647733, 'Fundacja SCP', 'pl', 'http://scp-wiki.net.pl/', '2014-04-25 12:14:57+00:00'),
    (560484, 'La Fundación SCP', 'es', 'http://lafundacionscp.wikidot.com/', '2013-05-05 14:43:20+00:00'),
    (547203, 'สถาบัน SCP', 'th', 'http://scp-th.wikidot.com/', '2013-04-05 09:04:34+00:00'),
    (578002, 'SCP財団', 'jp', 'http://scp-jp.wikidot.com/', '2013-07-08 11:09:46+00:00'),
    (1269857, 'SCP auf Deutsch', 'de', 'http://scp-wiki-de.wikidot.com/', '2016-04-05 21:04:44+00:00'),
    (530167, 'Fondazione SCP', 'it', 'http://fondazionescp.wikidot.com/', '2013-01-26 15:51:12+00:00'),
    (1398197, 'Фонд SCP', 'ua', 'http://scp-ukrainian.wikidot.com/', '2016-11-10 08:10:32+00:00'),
    (783633, 'Fundação SCP', 'pt-br', 'http://scp-pt-br.wikidot.com/', '2015-08-24 13:40:14+00:00'),
    (1427610, 'SCP International', 'int', 'http://scp-int.wikidot.com/', '2017-01-30 07:08:17+00:00'),
    (169125, 'Фонд SCP', 'ru', 'http://scp-ru.wikidot.com/', '2010-06-27 17:11:41+00:00');

CREATE TABLE public.pass (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    branch_id INTEGER NOT NULL REFERENCES public.branch(id) ON DELETE CASCADE ON UPDATE CASCADE,
    subject VARCHAR(25) NOT NULL,
    started_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    ended_at TIMESTAMP WITH TIME ZONE,
    pending BOOLEAN NOT NULL DEFAULT TRUE,
    successful BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE public.user (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username VARCHAR(99) UNIQUE NOT NULL,
    slug VARCHAR(99) UNIQUE NOT NULL
);

CREATE TABLE public.membership (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    branch_id INTEGER NOT NULL REFERENCES public.branch(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    user_id INTEGER NOT NULL REFERENCES public.user(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    member_since TIMESTAMP WITH TIME ZONE NOT NULL,
    pass_id INTEGER NOT NULL REFERENCES public.pass(id) ON DELETE RESTRICT ON UPDATE CASCADE,
        UNIQUE(user_id, branch_id)
);

CREATE TABLE public.page (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title VARCHAR(256) NOT NULL,
    subtitle VARCHAR(256),
    preview VARCHAR(500),
    branch_id INTEGER NOT NULL REFERENCES public.branch(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    slug VARCHAR(256) NOT NULL,
    tags VARCHAR(50)[] NOT NULL DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    created_by INTEGER REFERENCES public.user(id) ON DELETE SET NULL ON UPDATE CASCADE,
    pass_id INTEGER NOT NULL REFERENCES public.pass(id) ON DELETE RESTRICT ON UPDATE CASCADE,
        UNIQUE(branch_id, slug)
);

CREATE TABLE public.vote (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES public.user(id) ON DELETE CASCADE ON UPDATE CASCADE,
    page_id INTEGER NOT NULL REFERENCES public.page(id) ON DELETE CASCADE ON UPDATE CASCADE,
    vote SMALLINT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    pass_id INTEGER NOT NULL REFERENCES public.pass(id) ON DELETE RESTRICT ON UPDATE CASCADE,
        UNIQUE(user_id, page_id)
);


CREATE OR REPLACE FUNCTION add_member(branch_id INTEGER, user_id INTEGER, slug TEXT, username TEXT, member_since TIMESTAMP WITH TIME ZONE, pass_id INTEGER) RETURNS void AS $$
    INSERT INTO public.user VALUES (user_id, username, slug)
        ON CONFLICT (id) DO UPDATE SET
            username = EXCLUDED.username,
            slug = EXCLUDED.slug;

    INSERT INTO public.membership VALUES (DEFAULT, branch_id, user_id, member_since, pass_id)
        ON CONFLICT (user_id, branch_id) DO UPDATE SET
            pass_id = EXCLUDED.pass_id;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION add_page(page_id INTEGER, branch_id INTEGER, title TEXT, preview TEXT, slug TEXT, tags TEXT[], created_by INTEGER, created_at TIMESTAMP WITH TIME ZONE, updated_at TIMESTAMP WITH TIME ZONE, pass_id INTEGER) RETURNS void AS $$
    INSERT INTO public.page VALUES (page_id, title, NULL, preview, branch_id, slug, tags, created_at, updated_at, created_by, pass_id)
    ON CONFLICT (id) DO UPDATE SET
        pass_id = EXCLUDED.pass_id,
        preview = EXCLUDED.preview,
        title = EXCLUDED.title,
        slug = EXCLUDED.slug;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION add_title(subtitle TEXT, slug TEXT, branch_id INTEGER) RETURNS void AS $$
    UPDATE public.page SET subtitle = $1 WHERE 
        slug = $2 AND
        branch_id = $3;
$$ LANGUAGE sql;

CREATE FUNCTION public.check_public_page_user_id() RETURNS trigger AS $$
    BEGIN
        NEW.created_by = (SELECT id FROM public.user WHERE id = NEW.created_by);
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER public_page_user_id_insert
    BEFORE INSERT OR UPDATE ON public.page
    FOR EACH ROW
    WHEN (NEW.created_by IS NOT NULL)
    EXECUTE FUNCTION public.check_public_page_user_id();