Статьи‎ > ‎

Виртуальная версионность справочников и реестров

В статье приведен новый легкий алгоритм для организации версионности справочников, он похож на Log trigger, но более универсален и надежен. Возможно, это все было придумано до меня, но в открытых источниках мне не удалось найти описание этого подхода.

С чего все началось

В одном проекте у нас была задача вести и распространять большое количество справочников, иногда по нестабильным каналам связи, с возможными откатами времени на серверах и восстановлением их из бекапов. Спешу поделиться с вами, какие у нас возникали проблемы и как мы их решали.
По некоторым техническим и политическим причинам мы отказались от использования готового ПО, анализа существующих систем в этой статье не будет. Все делали сами, на C# и реляционных СУБД, хотя это здесь не принципиально.
Мы использовали архитектуру Master-Slave: одна Ведущая система хранит в себе эталонный образец справочника и позволяет его редактировать (через GUI или импорт файлов), Ведомые системы периодически опрашивают Ведущую на наличие обновлений. Обмен данными - через пакеты JSON и WebAPI, т.к. клиенты были от Windows Server до Android. Количество Ведомых достигало 10000, запросы обновлений шли от каждого Ведомого в среднем раз в час. Нагрузка не очень большая, но использование файлового кэша с пакетами JSON и системы изготовления diff-ов позволило запускать Ведущую систему на очень слабом железе.
Справочники были разные, самые сложные из них:
  • несколько связанных таблиц от 10 тысяч до 1 миллиона записей;
  • сразу несколько версий справочника являются актуальными (тарифы на прошлое, настоящее, будущее), у каждой версии свои даты действия;
  • ведущая система должна хранить историю изменений справочников за последние 5 лет, ведомым достаточно хранить все версии за последний год;
  • у некоторых записей справочника есть собственные даты действия;
  • новая версия справочника может готовиться несколько недель, разными людьми, с промежуточным тестированием и откатами изменений. Промежуточные результаты не должны попадать на ведомые узлы;
  • при переходе на новую версию может измениться как одна запись в справочнике, так и миллион (когда полностью меняются тарифы).
Частный случай справочников - реестры (реестр оборудования):
  • реестр состоит из одной таблицы, но может ссылаться на другие справочники;
  • только последняя версия актуальна для Ведомых;
  • при переходе на новую версию меняется только одна запись.
Для справочников и реестров был разработан единый механизм хранения и распространения, он довольно легковесный и вы можете воспользоваться им даже если в вашем проекте используются более простые структуры.

Проблемы

Первый вопрос - что считать номером версии? Вариантов не много: дата-время редактирования справочника или некое целое число - автоинкремент. "Дата-время" очень популярный, но плохой вариант даже для Реестров, т.к.:
  • время на сервере может неожиданно переводиться назад (даже если оно в UTC), случается редко, но риск есть;
  • время на ведомых машинах отличаться от времени на ведущей, и необходимо очень аккуратно прорабатывать логику запросов обновлений для справочников.
Соответственно в качестве идентификатора версии был выбран вариант с целым числом, как более надежный.
Второй вопрос - как хранить сразу несколько версий в таблицах? Ведь нужно хранить не только историю изменений реестра или справочника, но и иметь быстрый доступ сразу к нескольким версиям некоторых справочников. Здесь вариантов больше:
  • хранить полную копию каждой версии, что расточительно, ведь версии могут отличаться одной записью, а размер каждой версии больше миллиона записей;
  • хранить полную последнюю версию и "изменения" для восстановления предыдущих версий. Данный подход экономит память, но существенно усложняет доступ к предыдущим версиям справочника;
  • гибридный вариант: хранить несколько полных версий и "изменения";
  • вариант "Виртуальная версионность", описанный ниже.

Решение

Таблица для хранения информации о самих версиях:

СтолбецОписание
versionNumberНомер версии, целое число, автоинкремент, без пропусков значений.
useFrom
Дата-время, начиная с которой действует справочник. Для реестров не актуально, т.к. они начинают действовать сразу после создания версии.
status
Статус версии: формируется, согласуется, тестируется, распространяется - все что необходимо для ваших бизнес-процессов. Для реестров не всегда актуально, т.к. создание новой версии может происходить за одну транзакцию.
...
Имена пользователей, дата и время редактирования, комментарии - все что необходимо для вашего бизнес-процесса.

Таблица с данными:

СтолбецОписание
ID
Уникальный идентификатор записи, согласно вашему бизнес-процессу. Возможно в вашем случае это не один столбец, а несколько.
...
Поля вашего справочника. Опционально здесь также могут быть даты действия записи, если требуется для ваших алгоритмов.
addInVersionНомер версии, в рамках которой была создана запись.
deleteInVersionНомер версии, в рамках которой запись была удалена.

Данная структура применяется как на стороне Ведущей системы, так и на стороне Ведомых. Может отличаться только количество хранимых версий. Одна запись в таблице с данными относится сразу к нескольким версиям, поэтому метод назван "Виртуальная версионность".

Формирование справочника

При редактировании справочника в таблице с номерами версий создается новая запись. Далее вносятся изменения в таблицу с данными:
  • при добавлении новой записи в справочник в таблицу с данными вносится строка, у которой поле addInVersion = "номер создаваемой версии", deleteInVersion устанавливается как NULL.
  • при удалении записи из справочника в таблице с данными для соответствующей строки проставляется deleteInVersion = "номер создаваемой версии". Поле addInVersion не меняется.
  • при редактировании записи в справочнике существующая запись помечается как удаленная (deleteInVersion = "номер создаваемой версии"), и создается новая запись в таблице (addInVersion = "номер создаваемой версии", deleteInVersion = NULL). Т.е. операции "Редактирование" фактически нет, вместо нее одна запись удаляется и создается новая.

Например:

Name
Sex
addInVersiondeleteInVersion
Kate
female
1
NULL
Tom
male
1
3
Lisa
female
1
2
Tom
female
3
NULL

Запись Kate была создана в версии 1 и существует во всех остальных версиях.
Запись Lisa была создана в версии 1 и удалена в версии 2. Т.е. во 2 версии справочника этой записи уже нет.
Запись Tom была создана в 1 версии и отредактирована в 3. Т.е. в 3 версии справочника он уже будет определяться как "famale".

Одна строка в таблице с данными может относиться сразу к нескольким версиям, т.к. она привязана к диапазону версий между addInVersion  и deleteInVersion. Уникальный идентификатор записи в таблице с данными являются ID + addInVersion.

Если новая версия вашего справочника создается длительное время с большим количеством редактирований одних и тех же полей разными людьми, то алгоритм может быть следующий:

  1. в таблице с номерами версий создается новая запись, допустим версия = N. Новой версии присваивается статус "На редактировании". Новую версию можно создать только в том случае, если предыдущая версия уже имеет статус "на распространение".
  2. при редактировании таблиц с данными выполняются следующие правила:
    • удаление записи: если addInVersion не равно N, то в deleteInVersion прописывается N. Если addInVersion = N, то запись физически удаляется из таблицы
    • отмена удаления записи: если deleteInVersion = N, то она заменяется на NULL. Если deleteInVersionне равно N, то отмена удаления невозможна.
    • добавление новой записи: все как обычно, addInVersion = N, deleteInVersion = NULL
    • редактирование записи: если addInVersion = N, то просто вносятся изменения в поля записи. Если addInVersion не равно N, то запись необходимо пометить как удаленную (deleteInVersion = N) и создать новую (addInVersion = N, deleteInVersion = NULL).
  3. после того, как все редактирования завершены, для версии устанавливается статус "на распространении", и дальнейшие ее модификации запрещены.
  4. Ведомые получают только те версии, которые имеют статус "на распространение".

Версию "на редактировании" всегда можно безопасно откатить до последней стабильной, физически удалив все записи у которых addInVersion = N, и проставив NULL для всех полей, где deleteInVersion = N.

Недостаток данного метода: не сохраняется история формирования версии (кто какую строку внес и т.д.), но для этого могут быть применены дополнительные журналы действий пользователей.
Достоинство - не хранится промежуточный мусор в таблице с данными, промежуточные версии справочника не попадут на ведомые устройства.

Несколько связанных таблиц

Если у вас есть несколько связанных таблиц и для них требуется единая версионность, то их поля addInVersion и deleteInVersion должны ссылаться на одну таблицу с номерами версий.

Таблица Users:

Name
SexID
addInVersiondeleteInVersion
Kate
1
1
NULL
Tom
2
1
3
Lisa
1
1
2
Tom
3
3
NULL

Таблица Sex:

SexID
Sex
addInVersiondeleteInVersion
1
female
1
NULL
2
male
1
NULL
3other
3
NULL

В примере выше при создании версии 3 были отредактированы сразу две таблицы. Целостность данных на стороне клиентов не будет нарушена.

Обратите внимание, что поле SexID не уникально в таблице Sex, это не первичный ключ. Первичный ключ будет составной: SexID + addInVersion. При этом в таблице Users поле SexID не является внешним ключом, т.е. СУБД не контролирует ссылочную целостность для данных, покрытых версионностью. Ни в коем случае нельзя в таблице Users использовать составной внешний ключ SexID + addInVersion: при редактировании данных в таблице Sex составной вторичный ключ в таблице Users продолжит ссылаться на старую версию записи. Также не спасут и суррогатные ключи. При создании новой версии целостность данных должно контролировать ваше ПО.

Алгоритм добавления новых записей и редактирования существующих мало отличается от описанного в предыдущем разделе: при редактировании полей-ссылок проверяйте, что существуют соответствующие записи, у которых deleteInVersion установлено как NULL, т.е. что эти записи не были удалены из справочника.

Удаление записей: если в "Таблица Sex" одну из записей пометить как удаленную, то необходимо проверить, что на нее никто больше не ссылается в новой версии. СУБД автоматически это не проконтролирует, т.к. удаленные записи остаются в таблицах, у них только обновляется поле deleteInVersion. Пример с ошибкой:

Таблица Users:

Name
SexID
addInVersiondeleteInVersion
Kate
1
1
NULL
Tom
2
1
3
Lisa
1
1
2
Tom
3
3
NULL

Таблица Sex:

SexID
Sex
addInVersiondeleteInVersion
1
female
1
NULL
2
male
1
NULL
3other
3
4

Создали версию 4, в которой в таблице Sex удалили запись "other". В СУБД ошибка не возникнет, т.к. SexID в таблице Users продолжает спокойно ссылаться на SexID=3 в таблице Sex. В вашем ПО произойдет крах, когда в 4-ой версии справочника не обнаружится SexID=3. Пример без ошибки:

Таблица Users:

Name
SexID
addInVersiondeleteInVersion
Kate
1
1
NULL
Tom
2
1
3
Lisa
1
1
2
Tom
3
3
4

Таблица Sex:

SexID
Sex
addInVersiondeleteInVersion
1
female
1
NULL
2
male
1
NULL
3other
3
4

Запись "Tom" была удалена вместе с записью "other". Другой вариант без ошибки - запись "Tom" могла быть отредактирована в той же 4 версии с присвоением другого SexID, но видимо Tom не успел определиться :)

Если в вашем проекте есть несколько не связанных таблиц, и для каждой требуется версионность, то в вашей базе данных будет несколько таблиц с номерами версий, на каждую из которых ссылается одна или несколько таблиц с данными.

Подводный камень при использовании связанных справочников у нас был следующий: допустим, было принято решение отредактировать Таблицу Sex, данный процесс занимает 2 недели, т.к. требуется согласование с большим количеством руководителей. Создали версию 4, она находится в состоянии "на редактировании", внесли правку в Таблицу Sex, ждем... Тут неожиданно приходит команда завести новую запись в Таблице Users. Поскольку версия 4 уже создана и открыта на редактирование - просто вносим данные в Таблицу Users с addInVersion = 4. Но проблема в том, что Клиенты не получат эту запись до тех пор, пока не будет согласовано изменение в Таблице Sex, а это долго.

Решения:

  • терпеть, ждать согласования новой версии, ускорять административно;
  • откатывать все изменения в Таблице Sex, вносить новую запись в Таблицу Users, утверждать версию 4, открывать версию 5 и вновь вносить изменения в Таблицу Sex. Это довольно хлопотно, но возможно;
  • сделать для таблиц раздельную версионность и надеяться, что на стороне клиента справочники не развалятся.

Очистка устаревших версий

Старые версии можно быстро удалить из базы, для этого:
  1. удаляется строка из таблицы с номерами версий, допустим для версии = M
  2. из таблицы с данными удаляются все записи, у которых deleteInVersion = M
  3. в таблице с данными для записей addInVersion = M вы можете установить addInVersion = NULL, если хотите сохранить ссылочную целостность с таблицей с номерами версий. Можете также оставить эти записи без изменений.
Удалить можно только самую старую версию, т.е. настоятельно не рекомендуется удалять версию 5, если в базе еще есть версия 4.

Сроки хранения старых версий могут отличаться для Ведущей и Ведомых систем, например в нашем случае Ведущая хранила все версии за последние 5 лет, а ведомые не более 1 года. Если говорить о реестрах, то на Ведомых вообще хранилась только последняя версия, а на Ведущей - те же 5 лет.

Выборка из справочника

Все запросы довольно простые, не требуют сложных вычислений.

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

Поиск записи в справочнике в версии X:

SELECT Name, Sex FROM Users WHERE Name = 'Kate' AND (addInVersion <= X OR addInVersion is NULL) AND (deleteInVersion > X OR deleteInVersion is NULL)

Запрос всех записей в справочнике за версию X:

SELECT Name, Sex FROM Users WHERE (addInVersion <= X OR addInVersion is NULL) AND (deleteInVersion > X OR deleteInVersion is NULL)

Запрос всех версий справочника, начиная с версии X (предполагается, что нет не утвержденных версий в БД, иначе запрос будет сложнее):

SELECT * FROM Users WHERE deleteInVersion >= X OR deleteInVersion is NULL

Мы использовали этот запрос для первоначальной инициализации Ведомых систем.

Если же необходимо изготовить пакет изменений (diff) между версиями X и Y (где Y = X + 1) для передачи от Ведущей системы к Ведомой, то выполняются следующие два шага:

1) Получаем список записей, которые должны быть помечены на стороне Ведомой системы как добавленные:

SELECT Name, Sex, addInVersion FROM Users WHERE addInVersion=Y

2) Получаем список записей, которые должны быть помечены на стороне Ведомой системы как удаленные:

SELECT * FROM Users WHERE deleteInVersion = Y

Теперь остается только правильно сформировать пакет изменений, например в JSON.

Обмен справочниками между Ведущим и Ведомыми

Здесь все просто, Ведомые сообщают Ведущей какая версия у них сейчас есть, Ведущая отдает им ссылку на скачивание пакета изменений или говорит что ничего нового нет. Пакеты изменений изготавливаются в виде JSON запакованных в архив сразу после выпуска новой версии, либо при первом запросе.

На этом все, спасибо за внимание!

2014.10.27, Андрей Абрамов
Обновлено 2015.01.04
http://creativecommons.org/licenses/by/4.0/

Comments

Не удалось найти URL спецификации гаджета