Статья посвящена альтернативным версиям Qt-драйверов для работы с базами данных. По большому счету отличий от нативных Qt-драйверов не так много, всего пара: 1) Поддержка типа UUID; 2) Работа с сущностью "Транзакция" как с самостоятельным объектом. Но эти отличия привели к существенному пересмотру кодовой реализации исходных Qt-решений и изменили подход к написанию рабочего кода.
Первичный ключ: UUID или Integer?
Впервые с идеей использовать UUID в качестве первичного ключа я познакомился в 2003 году, работая в команде дельфистов. Мы разрабатывали программу для автоматизации технологических процессов на производстве. СУБД в проекте отводилась существенная роль. На тот момент это была FireBird версии 1.5. По мере усложнения проекта появились трудности с использованием целочисленных идентификаторов в качестве первичных ключей. Опишу пару сложностей:
Проблема архитектурная: периодически заказчики присылали справочные данные с целью включения их в новую версию дистрибутива. Иногда справочники содержали первичные ключи уже имеющиеся в нашей базе. Приходилось устранять коллизии в процессе агрегирования данных. На этом проблемы не заканчивались: при разворачивании нового дистрибутива периодически возникали обратные коллизии.
Проблема программная: чтобы получить доступ к вставленной записи нужно было выполнить дополнительный SELECT-запрос, который возвращал максимальное значение первичного ключа (значение для только что вставленной записи). Причем этот процесс должен был проходить в пределах одной транзакции. Далее можно было обновлять или корректировать запись. Это сейчас я знаю, что некоторые драйверы БД возвращают значение первичного ключа для вставленной записи, но в 2003 году мы такими знаниями не обладали, да и не припомню что бы Делфи-компоненты возвращали что-то подобное.
Использование UUID-ов в качестве первичных ключей сводило к минимуму архитектурную проблему, и полностью решало программную. UUID-ключ генерировался перед началом вставки записи на стороне программы, а не в недрах сервера БД, таким образом дополнительный SELECT-запрос стал не нужен, и требование единой транзакции утратило актуальность. FireBird версии 1.5 не имел нативной поддержки UUID-ов, поэтому использовались строковые поля длинной в 32 символа (дефисы из UUID-ов удалялись). Факт использования строковых полей в качестве первичных ключей нисколько не смущал, нам не терпелось опробовать новый подход при работе с данными.
У UUID-ов есть свои минусы: 1) Существенный объем; 2) Более низкая скорость работы по сравнению с целочисленными идентификаторами. В рамках проекта достоинства оказались более значимы, чем указанные недостатки. В целом, опыт оказался положительным, поэтому в последующих решениях при создании реляционных связей предпочтение отдавалось именно UUID-ам.
Примечание: Более подробный анализ UUID vs Integer для СУБД MS SQL можно посмотреть в статье "Первичный ключ – GUID или автоинкремент?"
Первый драйвер для FireBird
В 2012 году мне снова довелось поработать с FireBird. Нужно было создать небольшую программу по анализу данных. Разработка велась с использованием QtFramework. Примерно в это же время у FireBird вышла версия 2.5 с нативной поддержкой UUID-ов. Я подумал: "Почему бы не добавить в Qt-драйвер для FireBird поддержку типа QUuid?" Так появилась первая версия Qt-драйвера с поддержкой UUID-ов. Этот вариант не сильно отличался от оригинальной версии драйвера и, в основном, был ориентирован на использование в однопоточных приложениях.
Появление сущности "Транзакция"
Следующая модификация Qt-драйвера для FireBird произошла в конце 2018 года. Наша фирма взялась за разработку проекта по анализу данных большого объема. Для фирмы выросшей из стартап-а эта работа была очень важна, как с финансовой, так и с репутацио́нной точек зрения. Сроки исполнения были весьма жесткие. В качестве СУБД была выбрана FireBird, несмотря на определенные сомнения в ее пригодности. Хорошим вариантом могла бы стать PostgreSQL, но у нашей команды на тот момент отсутствовал опыт эксплуатации данной СУБД.
Архитектура программы предполагала подключение к базе данных из разных потоков. Нативный Qt-FireBird драйвер не очень подходил для такого режима работы. К тому же, у концепции Qt-драйверов, на мой взгляд, есть один существенный недостаток: управление транзакциями (точнее одной транзакцией) происходит на уровне объекта подключения к базе данных (сущность "Транзакция" инкапсулирована внутри объекта Driver). То есть при работе с нативным Qt-драйвером в один момент времени можно оперировать только одной транзакцией. Почему так сделано, в принципе, понятно: достаточно много популярных СУБД работают по этой схеме (одно подключение - одна транзакция). В качестве примера можно назвать Oracle, PostgreSQL, MS SQL при работе через ODBC. Но FireBird не такой, его API позволяет оперировать сразу несколькими транзакциями в контексте одного подключения. Обладая этими знаниями, я начал адаптацию Qt-FireBird драйвера для работы в многопоточном приложении.
Первоначальная концепция предполагала небольшое (2-3) подключений к базе данных, при этом каждый поток приложения должен был иметь возможность работать со своей персональной транзакцией. Таким образом у одного подключения могло быть несколько активных транзакций выполняющихся в разных потоках. Для достижения этой цели код драйвера пришлось основательно переработать. К сожалению, в конце меня ждал неприятный сюрприз: при нагрузочном тестировании выяснилось, что если от одного подключения создать несколько транзакций, и выполнять их в разных потоках, то фактически, в один момент времени будет выполняться только одно sql-утверждение в рамках одной транзакции, а остальные транзакции будут находиться в состоянии ожидания. В общем, концепция параллельных транзакций с треском провалилась. Пришлось в срочном порядке переходить к решению "один поток - одно подключение". В плане параллельного выполнения sql-запросов этот вариант работал значительно бодрее.
Новая концепция не нуждалась в сущности "транзакция", как в самостоятельной единице, тем не менее, я не стал ее упразднять. Дальнейшая эксплуатация показала, что наличие объекта "транзакция" делает работу с базой данных более гибкой, дает больше инвариантов при написании кода. Например, разработчик может передать объект "Транзакция" в функцию в качестве параметра, явно говоря таким образом, что внутри нужно работать в контексте указанной транзакции. В функции можно проверить активна транзакция или нет, можно выполнить COMMIT или ROLLBACK. Для вспомогательных операций можно создавать альтернативную транзакцию, не затрагивающую основную. Таких возможностей нет у нативных Qt-драйверов.
Ниже приведен пример с тремя функциями, которые последовательно вызываю друг друга. Каждая функция запрашивает объект подключения к базе данных (Driver) у пула коннектов. Так как функции вызываются в одном потоке, они получают объект коннекта, ссылающийся на одно и тоже подключение к БД. Далее в каждой функции создается собственный независимый объект транзакции и все последующие запросы будут выполняются в его контексте.
Приведенный пример не будет работать с нативным Qt-драйвером, причина описана выше: ограничение на одно подключение и одну транзакцию
Кто-то скажет: "Зачем все так усложнять?! Вот есть же понятная концепция 'Один коннект - одна транзакция', с ней легко и просто работать!" Действительно, в большинстве случаев этого будет достаточно, но я хорошо помню, еще по команде дельфистов, как эта "простота" выходила нам боком при работе с ODBC драйверами. Поэтому считаю, что наличие инвариантов - лучше, чем их отсутствие.
void function3(int value3)
{
db::firebird::Driver::Ptr dbcon = fbpool().connect();
db::firebird::Transaction::Ptr transact3 = dbcon->createTransact();
QSqlQuery q3 {db::firebird::createResult(transact3)};
if (!transact3->begin())
return;
if (!q3.prepare("INSERT INTO TABLE3 (VALUE3) VALUES (:VALUE3)"))
return;
sql::bindValue(q3, ":VALUE3" , value3);
if (!q3.exec())
return;
transact3->commit();
}
void function2(int value2)
{
db::firebird::Driver::Ptr dbcon = fbpool().connect();
db::firebird::Transaction::Ptr transact2 = dbcon->createTransact();
QSqlQuery q2 {db::firebird::createResult(transact2)};
if (!transact2->begin())
return;
if (!q2.prepare("SELECT * FROM TABLE2 WHERE VALUE2 = :VALUE2"))
return;
sql::bindValue(q2, ":VALUE2 " , value2);
if (!q2.exec())
return;
while (q2.next())
{
qint32 value3;
sql::assignValue(value3, q2.record(), "VALUE3");
function3(value3);
}
}
void function1()
{
db::firebird::Driver::Ptr dbcon = db::firebird::pool().connect();
db::firebird::Transaction::Ptr transact1 = dbcon->createTransact();
QSqlQuery q1 {db::firebird::createResult(transact1)};
if (!transact1->begin())
return;
if (!sql::exec(q1, "SELECT * FROM TABLE1"))
return;
while (q1.next())
{
QSqlRecord r = q1.record();
QUuidEx id;
qint32 value1;
qint32 value2;
sql::assignValue(id , r, "ID ");
sql::assignValue(value1 , r, "VALUE1 ");
sql::assignValue(value2 , r, "VALUE2 ");
...
function2(value2);
}
}В примере экземпляры транзакций (1-3) созданы для наглядности. В рабочем коде их можно опустить. В этом случае транзакции будут создаваться неявно внутри объекта QSqlQuery. Неявные транзакции всегда завершаются ROLLBACK-ом для SELECT-запросов и попыткой COMMIT-а для всех остальных.
Ниже показано как можно использовать одну транзакцию для трех sql-запросов. Подтвердить или откатить транзакцию можно в любой из трех функций.
void function3(db::firebird::Transaction::Ptr transact, int value3)
{
QSqlQuery q3 {db::firebird::createResult(transact)};
// Тут что-то делаем
}
void function2(db::firebird::Transaction::Ptr transact, int value2)
{
QSqlQuery q2 {db::firebird::createResult(transact)};
// Тут что-то делаем
function3(transact, value3);
}
void function1()
{
db::firebird::Driver::Ptr dbcon = db::firebird::pool().connect();
db::firebird::Transaction::Ptr transact = dbcon->createTransact();
QSqlQuery q1 {db::firebird::createResult(transact)};
if (!transact->begin())
return;
while (q1.next())
{
// Тут что-то делаем
function2(transact, value2);
}
transact->commit();
}Драйвер для PostgreSQL
В начале 2020 года мы приступили к новому объемному проекту. Требования к СУБД заказчик сформулировал однозначно: PostgreSQL. В отличие от ситуации с проектом 18-го года, сейчас время на изучение матчасти было. Для PostgreSQL хотелось создать драйвер похожий по поведению и функционалу на FireBird. Первой мыслью было подглядеть решение в Qt, но взять оттуда получилось только знание о том, что этот вариант нам не подходит. Работа Qt-драйвера построена на двух командах: PREPARE и EXECUTE. Эти команды могут работать только со строковым представлением, а это означает, что любые бинарные данные придется преобразовывать к строкам. Подумав, что "это не наш путь", я принялся отсматривать существующие решения и документацию по PostgreSQL API. Из библиотеки libpqxx получилось взять концепцию уровней изоляции для транзакций, все остальное было написано с нуля. Не обошлось без "ложки дегтя". Выяснилось, что для одного подключения к БД в один момент времени можно создать только одну транзакцию. Ограничение концептуальное, существует на уровне движка СУБД. Пример с тремя функциями, описанный выше, в PostgreSQL работать не будет. Были попытки посмотреть в сторону субтранзакций, но элегантного решения найти не удалось. Нивелировать проблему получилось переработкой пула коннектов. В него было добавлено свойство singleConnect(), определяющее режим создания нового подключения к базе данных. По умолчанию пул коннектов создает в одном потоке исполнения только одно подключение к БД. Свойство singleConnect() установленное в FALSE позволяет создавать новое подключение при каждом обращении к пулу. Таким образом, ограничение в одну транзакцию на одно подключение удалось обойти. Обратной стороной этого решения является большое количество подключений к базе. Но так как пул коннектов может использовать повторно уже существующие подключения, их количество не будет расти неконтролируемо и со временем придет к равновесному состоянию. Теперь пример с тремя функциями работает.
Драйвер для MS SQL
Пару раз на переговорах с заказчиками возникал вопрос по поводу интеграции с их системами через MS SQL. Чтобы опять не оказаться в ситуации цейтнота при разработке нового драйвера, было принято решение сделать его загодя. Драйвер для MS SQL реализован с использованием ODBC. В плане работы с транзакциями он похож на PostgreSQL: одно подключение - одна транзакция. Возможно, в драйвере OLE DB для MS SQL этого ограничения нет, но ODBC инвариантов не допускает. Сейчас драйвер имеет статус демонстрационного решения, в "бою" не применялся. На текущий момент, не реализованным остался биндинг и запись NULL-значений. Полагаю, к очередному проекту эту недоработку закроем.
Чего нет в классе Driver
Описываемые здесь драйверы не повторяют один в один функционал Qt-решений. В классе оставлены следующие методы:
beginTransaction();
commitTransaction();
rollbackTransaction().
С введением сущности "Транзакция" они утратили актуальность и нужны исключительно для отладки и диагностирования их вызовов из Qt-компонентов.
Ряд функций не используются нами в работе, поэтому они либо не реализованы, либо реализованы и помечены внутри программными точками останова, то есть разработчику при первом вызове придется их отладить. Вот эти функции:
tables();
record();
primaryIndex();
formatValue();
escapeIdentifier().
Заморожена поддержка механизма событий. Обсудив с коллегами этот функционал, мы пришли к заключению, что на данном этапе в нем нет необходимости. Возможно, в будущем решение будет пересмотрено, но пока у нас нет серьезных доводов в пользу событийного механизма.
Еще один момент, на который хотелось бы обратить внимание: по умолчанию все драйверы работают в режиме "Forward Only". Точнее сказать это единственный режим, в котором работаю драйверы, при получении данных с сервера СУБД. Тем не менее, механизм кэширования имеется, он реализован при помощи класса SqlCachedResult. Основное назначение механизма - отображение данных в визуальных Qt-компонентах.
Новые функции
В классе Driver добавлена функция abortOperation(), она дает возможность асинхронно прерывать тяжелые sql-запросы, тем самым предотвращая "замерзание" приложения. В классе Result появилась сервисная функция size2(), она возвращает количество записей для подготовленного sql-запроса. Функция size2() расположена с защищенной секции, доступ к ней осуществляется через глобальную функцию resultSize(const QSqlQuery&). Знание о количестве записей бывает полезно при реализации механизма пагинации.
Лицензионные ограничения
Все драйверы могу использоваться только под лицензиями GPL/LGPL 2.1. Драйверы зависят от класса SqlCachedResult, который принадлежит компании Qt и распространяется под вышеозначенными лицензиями. Это налагает запрет на прямое включение кода драйверов в закрытые коммерческие проекты. Даже драйвер PostgreSQL, который фактически написан с нуля, подпадает под указанное ограничение (заражен копилефтом). Тем не менее, ситуация вполне разрешима: можно собрать драйвер как динамическую библиотеку и использовать ее в коммерческом продукте под лицензией LGPL. В этом случае, все формальности будут соблюдены.
Зависимости
В реализации драйверов используется система логирования ALog, которая является составной частью библиотеки общего назначения SharedTools.
Демо-примеры
Специально для этой статьи был создан демонстрационный проект. Он содержит примеры работы с тремя СУБД: FireBird, PostgreSQL, MS SQL. Репозиторий с драйверами расположен здесь, он подключен в проект как субмодуль. Библиотека SharedTools так же подключена как субмодуль.
Проект создан с использованием QtCreator, сборочная система QBS. Есть четыре сборочных сценария:
db_demo_project.qbs - демо примеры для всех СУБД (содержит пункты 2-4);
db_demo_firebird.qbs - демо пример для FireBird (требуется FireBird-клиент);
db_demo_postgres.qbs - демо пример для PostgreSQL (требуется пакет libpq-dev);
db_demo_mssql.qbs - демо пример для MS SQL.
Драйвера в первую очередь разрабатывались для работы в Linux, поэтому эксплуатационное тестирование выполнялось именно для этой ОС. В Windows будет работать FireBird-драйвер (проверено), для остальных драйверов тестирование не проводилось.
Демо-примеры записывают следующие логи:
/tmp/db-demo-firebird.log
/tmp/db-demo-mssql.log
/tmp/db-demo-postgres.log
При первом запуске, примеры проверяют наличие тестовой базы данных. Если базы не обнаружено, в лог-файл будет выведен скрипт для ее создания.
Заключение
Черновой вариант статьи не предполагал наличие этого раздела, за что старый товарищ и, по совместительству, корректор подверг меня критике: "Мол, непонятна мотивация, целеполагание неясно. Зачем ты вообще писал эту статью?!" Что ж, исправляюсь!
В создание драйверов вложено много моего труда и труда коллег, потрачено время жизни. Зная нелюбовь программистов к внешним зависимостям, я не питаю иллюзий по поводу того, что представленные решения будут использоваться "как есть". Допускаю, что кто-то решит "выжечь каленым железом" ALog и заменит его на нечто свое - я не буду против (сам так поступаю с другими логгерами ;) В любом случае, если наши решения сэкономят кому-то время, или послужат отправной точной для новых идей - будет хорошо!
