Статья посвящена альтернативным версиям 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 и заменит его на нечто свое - я не буду против (сам так поступаю с другими логгерами ;) В любом случае, если наши решения сэкономят кому-то время, или послужат отправной точной для новых идей - будет хорошо!