Пошук уроків, статей та іншого контенту
Додайте created_at, updated_at та ідентифікатори авторів для відстеження життєвого циклу записів.
Аудитні поля зберігають інформацію про життєвий цикл запису:
created_at — коли запис створили;
updated_at — коли запис востаннє змінювали;
created_by — хто створив запис;
updated_by — хто востаннє змінив запис.
Такі поля допомагають:
показувати дату створення та останньої зміни;
визначати автора запису;
знаходити відповідального за останнє редагування;
аналізувати історію роботи застосунку.
Зазвичай для дат використовують тип timestamptz, щоб PostgreSQL зберігав часову зону разом із моментом часу.
Приклад таблиці з датами створення та оновлення:
CREATE TABLE documents (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);Якщо під час вставки не передати created_at або updated_at, PostgreSQL автоматично використає поточний час:
INSERT INTO documents (title)
VALUES ('Перший документ');Обидва поля отримають значення за замовчуванням.
Однак DEFAULT працює лише під час вставки. Якщо виконати UPDATE, поле updated_at автоматично не зміниться:
UPDATE documents
SET title = 'Оновлений документ'
WHERE id = 1;Щоб оновлювати updated_at автоматично, потрібен тригер.
updated_atТригер виконується PostgreSQL перед або після певної операції. Для updated_at потрібен тригер BEFORE UPDATE, який змінить новий рядок перед його збереженням.
CREATE OR REPLACE FUNCTION set_documents_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
-- Встановлюємо час безпосередньо перед збереженням зміненого рядка
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$;
CREATE TRIGGER documents_set_updated_at
BEFORE UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION set_documents_updated_at();У тригерній функції:
NEW — нова версія рядка;
OLD — попередня версія рядка;
NEW.updated_at — значення, яке буде збережено;
RETURN NEW — дозвіл зберегти змінений рядок.
Тепер під час кожного оновлення updated_at встановлюватиметься автоматично.
Для created_by та updated_by варто використовувати зовнішні ключі на таблицю користувачів.
CREATE TABLE app_users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
login text NOT NULL UNIQUE
);
CREATE TABLE documents (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint NOT NULL REFERENCES app_users(id),
updated_by bigint NOT NULL REFERENCES app_users(id)
);Зовнішній ключ гарантує, що в created_by та updated_by не можна записати ідентифікатор неіснуючого користувача.
Під час створення документа застосунок має передати автора:
INSERT INTO documents (
title,
created_by,
updated_by
)
VALUES (
'Перший документ',
1,
1
);Якщо документ створює користувач з ідентифікатором 1, він буде одночасно автором створення та останньої зміни.
Під час редагування документа потрібно змінити updated_by. Поле updated_at змінить тригер:
UPDATE documents
SET
title = 'Оновлений документ',
updated_by = 2
WHERE id = 1;У результаті:
created_at і created_by залишаться незмінними;
updated_at отримає поточний час;
updated_by стане рівним 2.
Нижче наведений приклад можна виконати в PostgreSQL. Він створює таблиці, тригер, додає користувачів і демонструє створення та оновлення документа.
-- Видаляємо об'єкти, якщо приклад запускається повторно
DROP TABLE IF EXISTS documents CASCADE;
DROP TABLE IF EXISTS app_users CASCADE;
DROP FUNCTION IF EXISTS set_documents_updated_at();
CREATE TABLE app_users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
login text NOT NULL UNIQUE
);
CREATE TABLE documents (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint NOT NULL REFERENCES app_users(id),
updated_by bigint NOT NULL REFERENCES app_users(id)
);
CREATE OR REPLACE FUNCTION set_documents_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
-- Автоматично фіксуємо час останньої зміни
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$;
CREATE TRIGGER documents_set_updated_at
BEFORE UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION set_documents_updated_at();
INSERT INTO app_users (login)
VALUES
('anna'),
('bohdan');
INSERT INTO documents (
title,
created_by,
updated_by
)
SELECT
'Перший документ',
creator.id,
creator.id
FROM app_users AS creator
WHERE creator.login = 'anna';
UPDATE documents
SET
title = 'Оновлений документ',
updated_by = (
SELECT id
FROM app_users
WHERE login = 'bohdan'
)
WHERE id = 1;
SELECT
d.id,
d.title,
d.created_at,
d.updated_at,
creator.login AS created_by_login,
editor.login AS updated_by_login
FROM documents AS d
JOIN app_users AS creator
ON creator.id = d.created_by
JOIN app_users AS editor
ON editor.id = d.updated_by;У запиті SELECT одна й та сама таблиця app_users приєднана двічі:
creator показує автора створення;
editor показує автора останньої зміни.
NULLУ прикладі використано NOT NULL, оскільки кожен документ повинен мати автора створення та автора останньої зміни.
У деяких системах updated_by може бути NULL. Наприклад, це можливо, якщо:
старі дані були імпортовані без інформації про автора;
зміни виконує автоматичний процес;
користувача було видалено або перенесено до іншої системи.
Тоді визначення поля може виглядати так:
updated_by bigint REFERENCES app_users(id)Відсутність NOT NULL дозволяє зберігати NULL. Рішення залежить від правил конкретної системи.
created_at і created_byПоля створення не потрібно змінювати під час редагування:
UPDATE documents
SET
title = 'Нова назва',
updated_by = 2
WHERE id = 1;У цьому запиті не змінюються:
created_at;
created_by.
Якщо змінити їх випадково, запис втратить правильну інформацію про своє походження. Тому на рівні застосунку варто дозволяти змінювати лише поля, які справді редагуються.
timestamp замість timestamptzТип timestamp не зберігає часову зону. У системах, де користувачі або сервери працюють у різних часових зонах, це може спричинити плутанину.
Для моментів часу зазвичай краще використовувати:
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMPDEFAULT CURRENT_TIMESTAMP встановлює значення лише під час INSERT. Для автоматичної зміни updated_at під час UPDATE потрібен тригер.
created_at під час редагуванняcreated_at описує момент створення і має залишатися незмінним. Для часу редагування використовуйте updated_at.
Недоцільно зберігати в аудитному полі текстове ім’я користувача:
created_by textКраще зберігати ідентифікатор із зовнішнім ключем:
created_by bigint REFERENCES app_users(id)Так ім’я користувача можна змінити в одному місці, не переписуючи всі документи.
updated_at вручну в кожному запитіТакий підхід легко пропустити в одному з UPDATE. Тригер централізує це правило на рівні бази даних.
created_at зберігає час створення запису.
updated_at зберігає час останньої зміни.
created_by зберігає автора створення.
updated_by зберігає автора останньої зміни.
Для дат зазвичай використовують timestamptz.
DEFAULT CURRENT_TIMESTAMP працює під час вставки.
Для автоматичного оновлення updated_at потрібен тригер.
Ідентифікатори авторів варто пов’язувати з таблицею користувачів через зовнішні ключі.
created_at і created_by не слід змінювати під час редагування запису.