JavaScript
- 29 ответов
- 0 вопросов
35
Вклад в тег
-- Начнем со схемы данных. На всякий случай проиллюстрирую как может выглядеть идея генерации guid базой, с которой
-- я в самом начале и начал. Но вам, наверное, не будет смысла эту функцию использовать, т.к. сценарий использования
-- у вас другой.
--
-- Обратите также внимание, что я поменял тип на BIGINT. У меня была гипотеза, что вы используете TEXT, т.к., возможно,
-- не знали про BIGINT. Недостаток TEXT в том, что Postgres не построит по нему индекс и выборки по id будут все
-- медленнее и медленнее. Я бы порекомендовал сразу отрефакторить все так, чтобы использовался именно BIGINT.
--
-- Все поля также назвал английскими словами, т.к. это можно считать индустриальным стандартом. NOT NULL добавил
-- по наитию: логика подсказывает, что PRIMARY KEY у каждой таблице обязателен, как и данные. Просто для полноты.
-- Начнем с функции, которая будет генерировать нам ID для примера.
CREATE OR REPLACE FUNCTION guid()
RETURNS BIGINT AS
$BODY$
BEGIN
-- Количество секунд с начала эпохи Линукса и домножаем на какой-то множитель, чтобы увеличить точность
-- и получить 1580307917143.431 вместо 1580307917.143431
RETURN CAST(EXTRACT(EPOCH FROM NOW()) * 1000 AS BIGINT);
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-- Теперь перейдем к самой схеме данных и создадим ее.
CREATE TABLE users (
id BIGINT PRIMARY KEY NOT NULL DEFAULT guid(),
pseudonym TEXT NOT NULL
);
CREATE TABLE posts (
id BIGINT PRIMARY KEY NOT NULL DEFAULT guid(),
content TEXT NOT NULL
);
CREATE TABLE comments (
id BIGINT PRIMARY KEY NOT NULL DEFAULT guid(),
text TEXT NOT NULL
);
-- Для того чтобы повесить CONSTRAINT на id целевых таблиц, понадобится сделать VIEW, котоый будет содержать все
-- id из всех этих таблиц, а также функцию, которую мы сможем использовать для CONSTRAINT. По сути, делаем то же,
-- что делали бы на уровне приложения, но на уровне БД.
CREATE OR REPLACE VIEW all_ids AS
SELECT id FROM users UNION
SELECT id FROM posts UNION
SELECT id FROM comments;
-- Теперь перейдем к функции, которая и будет выполнять всю грязную работу.
CREATE OR REPLACE FUNCTION is_unique_id (BIGINT)
RETURNS BOOLEAN AS 'SELECT CASE WHEN
(SELECT 1
FROM all_ids
WHERE id = $1) > 0
THEN FALSE ELSE TRUE END'
LANGUAGE 'sql' WITH (isstrict);
-- Осталось только повесить CONSTRAINT
ALTER TABLE users ADD CONSTRAINT id CHECK (is_unique_id(id));
ALTER TABLE posts ADD CONSTRAINT id CHECK (is_unique_id(id));
ALTER TABLE comments ADD CONSTRAINT id CHECK (is_unique_id(id));
-- А теперь внимание. Теперь вам придется быть очень внимательным при добавлении таблиц, в рамках которых id должен
-- быть уникален. При добавлении новой таблцы будет необходимо:
-- 1. Пересоздать VIEW, дополнив запрос новыми таблицами.
-- 2. Не забыть повесить аналогичный CONSTRAINT на новую таблицу.
--
-- Также обратите внимание, что при вставке новой записи в любую из таблиц будет проверяться весь созданный VIEW
-- и очень важно чтобы это был не полнотекстовый поиск, а работали индексы, поэтому так важно отрефакторить все в BIGINT.
-- Пришло время тестирования. Вставляем данные.
INSERT INTO users (pseudonym) VALUES ('Первый');
INSERT INTO users (pseudonym) VALUES ('Второй');
INSERT INTO posts (content) VALUES ('О том как надо');
INSERT INTO posts (content) VALUES ('О том как не надо');
INSERT INTO comments (text) VALUES ('Я думаю что решение...');
INSERT INTO comments (text) VALUES ('Хорошо я пропробую сделать...');
-- И глянем что получилось.
SELECT * FROM users;
-- 1580326610797 Первый
-- 1580326611809 Второй
SELECT * FROM posts;
-- 1580326613690 О том как надо
-- 1580326613712 О том как не надо
SELECT * FROM comments;
-- 1580326613779 Я думаю что решение...
-- 1580326613797 Хорошо я пропробую сделать...
-- Время X: тестируем нашу проверку, пытаясь вставить в таблицу users id из таблицы comments:
INSERT INTO users (id, pseudonym) VALUES (1580326613779, 'tiabc');
-- [23514] ERROR: new row for relation "users" violates check constraint "id" Detail: Failing row contains (1580326613779, tiabc)
-- Profit!