Неожиданная необходимость в версионности данных

Когда на проекте ближе к концу разработки становится понятно, что без версионности данных не обойтись, ведь любой пользователь может зайти и удалить то, что создавалось на протяжении долгого времени десятками других людей, приходится искать решение, требующее минимальных трудозатрат. Не вникая в подробности конкретного проекта, на котором появилась такая необходимость, представим себе google docs spreadsheets документ доступный для редактирования любому посетителю сайта.



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

documents
--id
--name
--creator_id

sheets
--id
--document_id
--name

rows
--number
--sheet_id
--height

columns
--number
--sheet_id
--width

cells
--id
--sheet_id
--color
--content
--row_number
--col_number
--creator_id

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

Требования к структуре

Обязательные

• Возможность сохранения предыдущих значений ячеек
• Возможность просмотра предыдущих ревизий онлайн документа
• Возможность отката на предыдущую версию
• Некоторые параметры могут не иметь разных версий (id, sheet_id)
• Внедрение в используемую ORM

Желательные

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

Реализация

После долгих раздумий была выведена «формула». Что если сделать дополнительную таблицу и заджойнить основную таблицу и новоиспечённую таблицу, содержащую разные версии значений.

Прежде всего нам понадобится таблица с ревизиями:

revisions
--id
--parent_id
--document_id
--created_date

Разносим таблицу cells на 2, так чтобы в cells оставалась информация, не требующая версионности, а в cells_data — изменяемая пользователями информация. Кроме того, к таблице cells_data добавляем поля created_in_revision_id, deleted_in_revision_id ну и айдишник пользователя, который внёс изменения.

cells
--id
--sheet_id
--row_number
--col_number
--creator_id

cells_data
--cell_id
--color
--content
--data_creator_id
--created_in_revision_id
--deleted_in_revision_id
(primary ключ на cell_id + created_in_revision_id)

В коде у себя к объекту Document (если мы программируем объектно) добавляем метод getRevisionCondition($revisionId=false), который должен возвращать приставку к SQL квэре наподобие " created_in_revision_id in (0,100,300,301) and deleted_in_revision_id not in (0,100,300,301)". Т.е. содержать текущую ревизию и все её паренты в конструкции «in (...)» и «not in (...)»

Выборка

Далее квэря, которая раньше выглядела так:

select * from cells where row_number=3 and col_number=2

превращается в:

select c.*,cd.* from cells c,cells_data cd where row_number=3 and col_number=2 and id=cell_id and $revisionCondition

Конечно, названия полей этих таблиц не должны дублироваться.

Вставка новой записи

Здесь всё так же просто как и при выборке. Проверяем просрочилась ли ревизия и берём последнюю. Например так:

$revision=$document->updateRevisionIfExpired()

Делаем вначале insert в основную таблицу (cells), потом в таблицу с версионными данными (cells_data). В поле created_in_revision_id записываем айдишник последней ревизии.

Удаление записи

Здесь мы попробуем сэкономить дисковое пространство. Если, к примеру, мы зададим 30 минут в качестве времени жизни ревизии — то сравним ревизию удаляемой записи с текущей ревизией и:

• в случае если они совпадают — просто удаляем из базы данных
• если же ревизия уже поменялась, то просто записываем айдишник свежей ревизии в поле таблицы cells_data — deleted_in_revision_id

Для node.js это выглядело бы так:

if(cellRevisionId==currentRevision.getId()){
	db.online.query("delete from cells_data where cell_id="+cellId+" and created_in_revision_id="+cellRevisionId)
}else{
	db.online.update('cells_data',{'deleted_in_revision_id':currentRevision.getId()},{'cell_id': cellId, 'created_in_revision_id' : cellRevisionId})
}

Обновление записи

Изменение данных чем-то похоже на удаление. Если ревизия не поменялась, просто происходит обновление данных, если же поменалась — обновляется поле deleted_in_revision_id и вставляется новая запись в таблицу cells_data c айдишником новой ревизии в created_in_revision_id.

Вместо заключения

Т.к. большинство таблиц, нуждающихся в версионности к настоящему моменту уже было переведено в новую структуру — могу выделить достотинства и недостатки:

Достоинства

• Все выборки и вставки (другими словами, селекты и инсерты) переделались максимально быстро и без каких-либо осложнений
• Делает всё что требовалось и практически всё что желалось

Недостатки

• Удаление, обновление записи все же происходит немного черезчур накручено и не помешал бы более автоматизированный скрипт
• Возможно, нужно что-то сделать с запросами типа in (...) и not in(...), т.к. они перечисляют все ревизии для документа