[Из песочницы] Пример простого версионирования записей PostgreSQL

Когда то видел реализацию «истории записей» — версионирования, на стороне программы, работающей с SQL базой. Перед изменением записи, из базы получалась старая версия, записывалась в XML и полученная строка XML записывалась в отдельную таблицу версий.

Изначально, в своей программе планировал версионирование сделать когда нибудь потом, какой то срочной необходимости не было. Помню, было желание где-то использовать тип данных jsonb, как только додумался до простой и лаконичной реализации версионирования на стороне SQL, не сделать не смог. Всего лишь одна таблица версий с 5 колонками и одна триггерная функция в 3 строчки кода.

Для описания реализации одной таблицы версий мало, поэтому придется описать еще несколько таблиц для примера.

Практически во всех базах данных, за редким исключением, есть таблица users – пользователи. Историю изменений — версий пользователя полезно хранить, например, для возможности откатится на старую версию, силами самого пользователя.

Пример таблицы пользователей:

Последние два поля на картинке, нужны для таблицы версий, также их можно назвать «автор версии» и «дата версии», но, при желании, можно обойтись без них.
Таблица версий:

Триггерная функция для сохранения версий:

Первые два поля заполняются из сохраняемой записи OLD.changestamp и OLD.userid.
В таблице версий, могут храниться не только записи таблицы users, третье поле MD5 хеш имени версионируемой таблицы, преобразованный в uuid.

В примерах ранее описана очень простая структура, но, как правило, у различных справочных данных могут быть дополнительные таблицы, с отношением «один ко многим».

Например, таблица «Группы пользователей».

И вторая таблица «Пользователи групп», состав группы — пользователи входящие в группу.

Для того, чтобы не усложнять простой механизм версионирования можно сделать небольшое дублирование данных в таблице групп, добавить jsonb поле, повторяющее структуру таблицы «Пользователи групп».

Для упрощения работы с задублированными данными, можно сделать дополнительную триггерную функцию, при INSERT или UPDATE, заполняющее таблицу «Пользователи групп» из jsonb поля.

Описанное выше дублирование, нужно только, когда требуется часто и максимально быстро получить данные из таблицы. Например, если часто делается запрос к таблице «Пользователи групп» для определения входит ли пользователь в группу Администраторы. В остальных случаях, данные можно получать запросом прямо из поля jsonb, и не использовать дублирующую таблицу.

Полный код примера по ссылке

Let's block ads! (Why?)