The article is devoted to alternative versions of Qt drivers for working with databases. By and large, there are not so many differences from native Qt drivers, just a couple: 1) UUID type support; 2) Working with the entity "Transaction" as with an independent object. But these differences led to a significant revision of the code implementation of the original Qt solutions and changed the approach to writing working code.
Primary Key: UUID or Integer?
I first got acquainted with the idea of ββusing UUID as a primary key in 2003, while working in a team of dolphists. We have developed a program for the automation of technological processes in production. The DBMS played a significant role in the project. At that time it was FireBird version 1.5. As the project grew more complex, it became difficult to use integer identifiers as primary keys. I will describe a couple of difficulties:
An architectural problem: from time to time customers sent reference data in order to include them in a new version of the distribution kit. Sometimes the directories contained the primary keys already in our database. I had to eliminate collisions in the process of data aggregation. The problems did not end there: when deploying a new distribution kit, reverse collisions periodically occurred.
: 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. , .
-
. : 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
, . , - .
, , , : ", , . ?!" , !
A lot of my work and the work of colleagues have been invested in the creation of drivers, a lot of life has been spent. Knowing the dislike of programmers for external dependencies, I have no illusion that the presented solutions will be used "as is". I admit that someone decides to "burn out with a hot iron" ALog and replace it with something of their own - I will not mind (I do this myself with other loggers;) In any case, if our solutions save someone time, or serve as a starting point for new ideas - it will be good!