365 lines
9.7 KiB
PL/PgSQL
365 lines
9.7 KiB
PL/PgSQL
--
|
||
-- PostgreSQL database dump
|
||
--
|
||
|
||
-- Dumped from database version 14.15 (Ubuntu 14.15-0ubuntu0.22.04.1)
|
||
-- Dumped by pg_dump version 15.0
|
||
|
||
SET statement_timeout = 0;
|
||
SET lock_timeout = 0;
|
||
SET idle_in_transaction_session_timeout = 0;
|
||
SET client_encoding = 'UTF8';
|
||
SET standard_conforming_strings = on;
|
||
SELECT pg_catalog.set_config('search_path', '', false);
|
||
SET check_function_bodies = false;
|
||
SET xmloption = content;
|
||
SET client_min_messages = warning;
|
||
SET row_security = off;
|
||
|
||
--
|
||
-- Name: public; Type: SCHEMA; Schema: -; Owner: -
|
||
--
|
||
|
||
-- *not* creating schema, since initdb creates it
|
||
|
||
|
||
--
|
||
-- Name: pgcrypto; Type: EXTENSION; Schema: -; Owner: -
|
||
--
|
||
|
||
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;
|
||
|
||
|
||
--
|
||
-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
|
||
--
|
||
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
|
||
|
||
|
||
--
|
||
-- Name: user_role; Type: TYPE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TYPE public.user_role AS ENUM (
|
||
'admin',
|
||
'editor',
|
||
'viewer'
|
||
);
|
||
|
||
|
||
--
|
||
-- Name: quote; Type: TYPE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TYPE public.quote AS (
|
||
id uuid,
|
||
text text,
|
||
author character varying(256),
|
||
datetime timestamp with time zone,
|
||
creator_id uuid,
|
||
creator_name character varying(32),
|
||
creator_login character varying(32),
|
||
creator_role public.user_role,
|
||
creator_telegram_id bigint
|
||
);
|
||
|
||
|
||
--
|
||
-- Name: user; Type: TYPE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TYPE public."user" AS (
|
||
id uuid,
|
||
name character varying(32),
|
||
login character varying(32),
|
||
role public.user_role,
|
||
telegram_id bigint
|
||
);
|
||
|
||
|
||
--
|
||
-- Name: quote_add(uuid, text, character varying, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE FUNCTION public.quote_add(user_id uuid, quote_text text, quote_author character varying DEFAULT NULL::character varying, quote_datetime timestamp with time zone DEFAULT statement_timestamp(), OUT new_quote public.quote) RETURNS public.quote
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
DECLARE
|
||
curr_user_role user_role;
|
||
new_quote_id uuid;
|
||
BEGIN
|
||
SELECT "role" FROM users WHERE id=user_id INTO curr_user_role;
|
||
IF curr_user_role IS NULL THEN
|
||
RAISE '401:Чел, ты кто? Я тебя не знаю';
|
||
END IF;
|
||
IF curr_user_role='viewer' THEN
|
||
RAISE '403:Увы и ах, такие права у тебя ещё не скачаны :(';
|
||
END IF;
|
||
INSERT INTO quotes (text, author, datetime, creator_id)
|
||
VALUES (quote_text, quote_author, quote_datetime, user_id)
|
||
RETURNING id INTO new_quote_id;
|
||
SELECT * FROM quote_get(user_id, new_quote_id) INTO new_quote;
|
||
END
|
||
$$;
|
||
|
||
|
||
--
|
||
-- Name: quote_delete(uuid, uuid); Type: PROCEDURE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE PROCEDURE public.quote_delete(IN user_id uuid, IN quote_id uuid)
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
DECLARE
|
||
quote_temp "quote";
|
||
curr_user_role user_role;
|
||
BEGIN
|
||
SELECT "role" FROM users WHERE id=user_id INTO curr_user_role;
|
||
IF curr_user_role IS NULL THEN
|
||
RAISE '401:Чел, ты кто? Я тебя не знаю';
|
||
END IF;
|
||
SELECT * FROM quote_get(user_id, quote_id) INTO quote_temp;
|
||
IF quote_temp IS NULL THEN
|
||
RAISE '404:Такую цитату ещё не сказанули';
|
||
END IF;
|
||
IF curr_user_role='viewer' OR quote_temp.creator_id!=user_id AND curr_user_role!='admin' THEN
|
||
RAISE '403:Увы и ах, такие права у тебя ещё не скачаны :(';
|
||
END IF;
|
||
UPDATE quotes
|
||
SET is_removed=true
|
||
WHERE id=quote_id;
|
||
END
|
||
$$;
|
||
|
||
|
||
--
|
||
-- Name: quote_get(uuid, uuid); Type: FUNCTION; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE FUNCTION public.quote_get(user_id uuid, quote_id uuid, OUT ret_quote public.quote) RETURNS public.quote
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
BEGIN
|
||
PERFORM 1 FROM users WHERE id=user_id;
|
||
IF NOT FOUND THEN
|
||
RAISE '401:Чел, ты кто? Я тебя не знаю';
|
||
END IF;
|
||
SELECT * FROM quotes_get(user_id) WHERE id=quote_id INTO ret_quote;
|
||
if ret_quote IS NULL THEN
|
||
RAISE '404:Такую цитату ещё не сказанули';
|
||
END IF;
|
||
END
|
||
$$;
|
||
|
||
|
||
--
|
||
-- Name: quote_update(uuid, uuid, text, character varying, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE FUNCTION public.quote_update(user_id uuid, quote_id uuid, new_text text DEFAULT NULL::text, new_author character varying DEFAULT NULL::character varying, new_datetime timestamp with time zone DEFAULT NULL::timestamp with time zone, OUT updated_quote public.quote) RETURNS public.quote
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
DECLARE
|
||
quote_temp "quote";
|
||
curr_user_role user_role;
|
||
BEGIN
|
||
SELECT "role" FROM users WHERE id=user_id INTO curr_user_role;
|
||
IF curr_user_role IS NULL THEN
|
||
RAISE '401:Чел, ты кто? Я тебя не знаю';
|
||
END IF;
|
||
SELECT * FROM quote_get(user_id, quote_id) INTO quote_temp;
|
||
IF quote_temp IS NULL THEN
|
||
RAISE '404:Такую цитату ещё не сказанули';
|
||
END IF;
|
||
IF curr_user_role='viewer' OR quote_temp.creator_id!=user_id AND curr_user_role!='admin' THEN
|
||
RAISE '403:Увы и ах, такие права у тебя ещё не скачаны :(';
|
||
END IF;
|
||
UPDATE quotes
|
||
SET
|
||
text=coalesce(new_text, text),
|
||
author=coalesce(new_author, author),
|
||
datetime=coalesce(new_datetime, datetime)
|
||
WHERE id=quote_id;
|
||
SELECT * FROM quote_get(user_id, quote_id) INTO updated_quote;
|
||
END
|
||
$$;
|
||
|
||
|
||
--
|
||
-- Name: quotes_get(uuid); Type: FUNCTION; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE FUNCTION public.quotes_get(user_id uuid) RETURNS SETOF public.quote
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
BEGIN
|
||
PERFORM 1 FROM users WHERE id=user_id;
|
||
IF NOT FOUND THEN
|
||
RAISE '401:Чел, ты кто? Я тебя не знаю';
|
||
END IF;
|
||
RETURN QUERY SELECT q.id, q.text, q.author, q.datetime, u.id, u.name, u.login, u.role, u.telegram_id
|
||
FROM quotes q
|
||
JOIN users u ON u.id=q.creator_id
|
||
WHERE NOT q.is_removed;
|
||
END
|
||
$$;
|
||
|
||
|
||
--
|
||
-- Name: user_auth(character varying, character varying); Type: FUNCTION; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE FUNCTION public.user_auth(user_login character varying, user_password character varying, OUT ret_user public."user") RETURNS public."user"
|
||
LANGUAGE plpgsql STABLE PARALLEL SAFE
|
||
AS $$
|
||
BEGIN
|
||
SELECT u.id, u.name, u.login, u.role, u.telegram_id
|
||
FROM users u
|
||
WHERE u.login=user_login AND u.password=crypt(user_password, u.password)
|
||
INTO ret_user;
|
||
IF ret_user IS NULL THEN
|
||
RAISE '401:Чел, ты кто? Я тебя не знаю';
|
||
END IF;
|
||
END
|
||
$$;
|
||
|
||
|
||
--
|
||
-- Name: user_get(uuid); Type: FUNCTION; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE FUNCTION public.user_get(user_id uuid, OUT ret_user public."user") RETURNS public."user"
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
BEGIN
|
||
SELECT id, name, login, role, telegram_id FROM users WHERE id=user_id INTO ret_user;
|
||
IF ret_user IS NULL THEN
|
||
RAISE '401:Чел, ты кто? Я тебя не знаю';
|
||
END IF;
|
||
END
|
||
$$;
|
||
|
||
|
||
--
|
||
-- Name: user_update(uuid, character varying, character varying, bigint, character varying); Type: FUNCTION; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE FUNCTION public.user_update(user_id uuid, new_name character varying DEFAULT NULL::character varying, new_login character varying DEFAULT NULL::character varying, new_telegram_id bigint DEFAULT NULL::bigint, new_password character varying DEFAULT NULL::character varying, OUT updated_user public."user") RETURNS public."user"
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
BEGIN
|
||
PERFORM 1 FROM users WHERE id=user_id;
|
||
IF NOT FOUND THEN
|
||
RAISE '401:Чел, ты кто? Я тебя не знаю';
|
||
END IF;
|
||
UPDATE users
|
||
SET
|
||
name=coalesce(new_name, name),
|
||
login=coalesce(new_login, login),
|
||
telegram_id=coalesce(new_telegram_id, telegram_id),
|
||
password=coalesce(crypt(new_password, gen_salt('bf')), password)
|
||
WHERE id=user_id;
|
||
SELECT * FROM user_get(user_id) WHERE id=user_id INTO updated_user;
|
||
END
|
||
$$;
|
||
|
||
|
||
SET default_table_access_method = heap;
|
||
|
||
--
|
||
-- Name: quotes; Type: TABLE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TABLE public.quotes (
|
||
id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
|
||
text text NOT NULL,
|
||
datetime timestamp with time zone DEFAULT now() NOT NULL,
|
||
author character varying(256) NOT NULL,
|
||
creator_id uuid NOT NULL,
|
||
is_removed boolean DEFAULT false NOT NULL
|
||
);
|
||
|
||
|
||
--
|
||
-- Name: users; Type: TABLE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TABLE public.users (
|
||
id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
|
||
name character varying(32) NOT NULL,
|
||
telegram_id bigint NOT NULL,
|
||
is_editor boolean DEFAULT false NOT NULL,
|
||
login character varying(32) NOT NULL,
|
||
password text NOT NULL,
|
||
role public.user_role DEFAULT 'viewer'::public.user_role NOT NULL
|
||
);
|
||
|
||
|
||
--
|
||
-- Name: quotes prm__quotes; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.quotes
|
||
ADD CONSTRAINT prm__quotes PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: users prm__users; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.users
|
||
ADD CONSTRAINT prm__users PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: users uni__users__login; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.users
|
||
ADD CONSTRAINT uni__users__login UNIQUE (login);
|
||
|
||
|
||
--
|
||
-- Name: users uni__users__name; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.users
|
||
ADD CONSTRAINT uni__users__name UNIQUE (name);
|
||
|
||
|
||
--
|
||
-- Name: users uni__users__telegram_uid; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.users
|
||
ADD CONSTRAINT uni__users__telegram_uid UNIQUE (telegram_id);
|
||
|
||
|
||
--
|
||
-- Name: idx__quotes__creator_id; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE INDEX idx__quotes__creator_id ON public.quotes USING hash (creator_id);
|
||
|
||
|
||
--
|
||
-- Name: idx__quotes__date; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE INDEX idx__quotes__date ON public.quotes USING btree (datetime DESC NULLS LAST);
|
||
|
||
|
||
--
|
||
-- Name: quotes frn__quotes__creator_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.quotes
|
||
ADD CONSTRAINT frn__quotes__creator_id FOREIGN KEY (creator_id) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE RESTRICT;
|
||
|
||
|
||
--
|
||
-- PostgreSQL database dump complete
|
||
--
|
||
|