Планируя полный и максимально кошмарный рефакторинг своего говнокода, написанного несколько лет назад на Lazarus и реализующего небольшую, но очень функциональную информационную систему под названием "Помощник патентного поверенного", я решил задействовать возможности самой СУБД, в которой хранились данные. Для этого надлежало нормализовать и архитектуру самой базы данных, о чем я расскажу как-нибудь в следующий раз.
В данном тексте я хочу поделиться тем, как при помощи языка SQL и встроенных в MySQL 5.7х функций, поддерживаемых в запросах, хранимых процедурах и представлениях, можно забрать на сторону сервера и СУБД часть логики.
Как было раньше
В первом (и громоздком) приложении, написанном на freepascal, были использованы стандартные возможности визуальных компонентов среды lazarus для создания форм и программирования бизнес-логики. В том числе создание непосредственно в визуальной среде вычисляемых полей для таблиц.
Приведу сразу пример. Поскольку речь у нас идет о хранении информации про патенты, сведения о которых включают дату подачи заявки (дату приоритета) и сведения о последнем оплаченном годе действия патента, неплохо было бы иметь наглядную информацию о текущем годе действия патента для того, чтобы не пропустить подходящий срок для перечисления ежегодной пошлины.
В первоначальном варианте в таблицу модуля данных lazarus, которая была подключена непосредственно к таблице БД, было добавлено вычисляемое поле. Функция вычисления была следующей:
function CalcCurrentYear (Priority: TDateTime): Integer;
var
DC,MC,YC,D,M,Y: Word;
YearC: Integer;
begin
DecodeDate(Priority, Y,M,D);
DecodeDate(Now(),YC,MC,DC);
YearC:=YC-Y;
if (M<MC) OR ((M=MC) AND (D<DC)) then Inc(YearC);
Result:=YearC;
end;
Если не понятно, как работала функция, то примерно так: сначала вычислялась разница лет между текущим годом и годом из даты приоритета. Затем по разнице месяцев и дней вычислялось, закончился ли уже календарный год действия патента на текущую дату.
Как стало теперь
В обновленной архитектуре базы данных прямой доступ к таблицам из приложения был исключен. Чтение данных трех разных видов объектов интеллектуальной собственности - товарных знаков, изобретений и промышленных образцов, осуществляется с помощью представлений (view), хранящихся на сервере СУБД.
Помимо выдачи трех разных наборов полей из одной и той же таблицы данных, представления еще и позволяют на лету силами СУБД добавлять вычисляемые поля. То есть затраты на программирование бизнес-логики в клиентском приложении уже снижаются.
При этом оказалось, что набор функций для работы с датой/временем в MySQL значителен. Во всяком случае, ни чуть не меньше, чем в том же freepascal.
В поисках по интернету и руководству MySQL я нашел замечательную функцию timestampdiff, которая в формате TIMESTAMP выдает разницу между двумя датами:
К примеру, сегодня 31 января 2019 года. Посмотрим, какие результаты дадут следующие запросы:
select (timestampdiff(year,date '2019-01-31',curdate())); -> 0
# при разнице 0 дней между датами, разумеется, и 0 лет разницы
select (timestampdiff(year,date '2018-01-31',curdate())); -> 1
# между датами ровно один календарный год
select (timestampdiff(year,date '2018-02-01',curdate())); -> 0
# до одного года не хватает одного дня
Первый аргумент функции задает формат, в котором должна быть вычислена разница между датами, в моем случае использована разница в годах.
Для целей же определения года действия патента к результату этой функции следует прибавить 1, т.к. действующий год патента - это текущий, еще не истекший год.
Подробное описание функции timestampdiff - в руководстве MySQL.
В итоге, в коде на языке SQL, который формирует запрос для представления, мы легко и в одну строчку добавляем функцию, вычисляющую год действия патента, в клиентском же коде для этого не написано ни единого символа. Приложение лишь формирует запрос к БД на получение результатов представления, к примеру так: select * from inventions; а уже в таблице, полученной от запроса, будет вычисленное поле с годом действия патента.
Перемещение кода, вычисляющего данные, на сторону БД и сервера имеет свои аргументы:
- Разделение обязанностей: данные, включая вычисленные, остаются данными. В парадигме MVC и принципах SOLID данные разделены с бизнес-логикой и визуальным представлением; клиентское приложение должно получать данные, а не вычислять их само;
- Соблюдение контракта: функцию можно быстро поменять на сервере, не затрагивая код клиентских приложений, то есть обновление архитектуры БД не затронет функционирование клиентских приложений, если не будет нарушен контракт между запросом клиента и представлением (view), формируемым сервером;
- Повышение скорости: с помощью языка SQL и функций, встроенных в MySQL, можно делать достаточно сложные запросы, которые оперируют данными из разных таблиц и могут иметь большое количество встроенных подзапросов. Если такой запрос будет оптимизирован и одномоментно исполнен самим сервером, то отпадает необходимость создавать большой траффик и плодить запросы от клиента к серверу. То есть в конечном результате может существенно вырасти скорость выполнения запроса.
Не забывайте изучать и использовать все возможности, которые предоставляют СУБД, ведь именно для того они и были разработаны.
