SQLite management of multiple databases
-
I have it when I use a separate QSqlDatabase variable and connection name in each class, I also manage it jointly in a separate database class. After all, it makes sense when you use open() . It's just a first definition. In scope, the same connection can be used with open close. removeDatabase can be used in the class destructor or in a separate function.
void removeConnection(const QString& sConnectionName) { QStringList sl = QSqlDatabase::connectionNames(); for (int a = 0; a < sl.size(); a++) if (sl[a] == sConnectionName) QSqlDatabase::removeDatabase(sConnectionName); } //or void removeConnection(const QString& sConnectionName) { for (auto &a: QSqlDatabase::connectionNames()) if (a == sConnectionName) QSqlDatabase::removeDatabase(sConnectionName); } removeConnection("name"); conn = QSqlDatabase::addDatabase("QSQLITE", "name"); conn.setDatabaseName(DB_PATH_NAME);@A-A-SEZEN What is the reason one would enumerate the connections for closing one of them? From the documentation of addDatabase one can read, that there can only be one connection with a specific name. Re-adding will close the first one. Removing a non-existing connection will do no harm. So you can omit the loop.
Btw: The closing of a connection, if it already exists and gets re-opened will invalidate any QSqlDatabase and QSqlQuery object to this connection. These are exactly the issues, why I would avoid all the closing and opening of connections. If you get a deeper and deeper call stack this will become more and more difficult to maintain. One can create RAII objects for this - but it will gain only some overhead.(talking about all this: I found the same loop in my own code - I think I should clean this up ;) )
Best regards
Holger -
@A-A-SEZEN What is the reason one would enumerate the connections for closing one of them? From the documentation of addDatabase one can read, that there can only be one connection with a specific name. Re-adding will close the first one. Removing a non-existing connection will do no harm. So you can omit the loop.
Btw: The closing of a connection, if it already exists and gets re-opened will invalidate any QSqlDatabase and QSqlQuery object to this connection. These are exactly the issues, why I would avoid all the closing and opening of connections. If you get a deeper and deeper call stack this will become more and more difficult to maintain. One can create RAII objects for this - but it will gain only some overhead.(talking about all this: I found the same loop in my own code - I think I should clean this up ;) )
Best regards
Holger@HoMa It was enough for me that in the QtCreator Application output window, the second time the same connection was used, the information was "duplicated, so the old connection removed". In my opinion, this is a snag. When I was done, I started deleting it myself.
-
@HoMa It was enough for me that in the QtCreator Application output window, the second time the same connection was used, the information was "duplicated, so the old connection removed". In my opinion, this is a snag. When I was done, I started deleting it myself.
@A-A-SEZEN I understand your concerns. Resource management is key in programming and especially in C++. You might investigate, when exactly the error popped up. You write the second time the same connection was used - if with "using" you mean "addDatabase" this is exactly the problem I wanted to make you aware of. Instead of re-adding you should just access a connection with QSqlDatabase::database( connectionname)
I recommend reading the details of the documentation of QSqlDatabase::addDatabase, database(), ::open and ::removeDatabase. There some really helpful glues are given.
Best regards
HolgerPS: if the flow through your code does not ensure, that a connection is open, you can always check a connection with the isValid() function.
-
@A-A-SEZEN I understand your concerns. Resource management is key in programming and especially in C++. You might investigate, when exactly the error popped up. You write the second time the same connection was used - if with "using" you mean "addDatabase" this is exactly the problem I wanted to make you aware of. Instead of re-adding you should just access a connection with QSqlDatabase::database( connectionname)
I recommend reading the details of the documentation of QSqlDatabase::addDatabase, database(), ::open and ::removeDatabase. There some really helpful glues are given.
Best regards
HolgerPS: if the flow through your code does not ensure, that a connection is open, you can always check a connection with the isValid() function.
@A-A-SEZEN said in SQLite management of multiple databases:
I have it when I use a separate QSqlDatabase variable and connection name in each class, I also manage it jointly in a separate database class.
I commend your effort. Thank you.
I thought I made it clear above. It means it didn't happen. "Google Translate" english... excuse me.
However, in practice, we encounter different scenarios. When building a class, I prefer to say "delete if there is already a connection with the same name" instead of a "use if already open connection" check. I can also do this in destructor. However, in terms of legibility in coding, I adopt the combination of related processes if possible.
For example, there is this situation in the constructor of the "Sansur" class below.connect(ui->pushSansur, &QPushButton::clicked, this, [&]() { auto * form = new Sansur(); form->setModal(true); form->exec(); delete form; });Sansur::Sansur(QWidget *parent) : QDialog(parent), ui(new Ui::Sansur) { ui->setupUi(this); this->setWindowIcon(QIcon("://imaj/warning.png")); this->setWindowTitle("Sansür Çalışması"); QString sBaglantiAdi = "sansur"; for (auto &a: QSqlDatabase::connectionNames()) { if (a == sBaglantiAdi) QSqlDatabase::removeDatabase(a); } QString sYol = QString("%1/Asoza/Sozluk").arg(QStandardPaths::standardLocations(QStandardPaths::DocumentsLocation).at(0)); vt = QSqlDatabase::addDatabase("QSQLITE", sBaglantiAdi); vt.setDatabaseName(QString("%1/sozluk.db3").arg(sYol)); vt.open(); vt.exec("PRAGMA journal_mode=MEMORY"); vt.exec("PRAGMA CacheSize=10240"); ..... -
@A-A-SEZEN said in SQLite management of multiple databases:
I have it when I use a separate QSqlDatabase variable and connection name in each class, I also manage it jointly in a separate database class.
I commend your effort. Thank you.
I thought I made it clear above. It means it didn't happen. "Google Translate" english... excuse me.
However, in practice, we encounter different scenarios. When building a class, I prefer to say "delete if there is already a connection with the same name" instead of a "use if already open connection" check. I can also do this in destructor. However, in terms of legibility in coding, I adopt the combination of related processes if possible.
For example, there is this situation in the constructor of the "Sansur" class below.connect(ui->pushSansur, &QPushButton::clicked, this, [&]() { auto * form = new Sansur(); form->setModal(true); form->exec(); delete form; });Sansur::Sansur(QWidget *parent) : QDialog(parent), ui(new Ui::Sansur) { ui->setupUi(this); this->setWindowIcon(QIcon("://imaj/warning.png")); this->setWindowTitle("Sansür Çalışması"); QString sBaglantiAdi = "sansur"; for (auto &a: QSqlDatabase::connectionNames()) { if (a == sBaglantiAdi) QSqlDatabase::removeDatabase(a); } QString sYol = QString("%1/Asoza/Sozluk").arg(QStandardPaths::standardLocations(QStandardPaths::DocumentsLocation).at(0)); vt = QSqlDatabase::addDatabase("QSQLITE", sBaglantiAdi); vt.setDatabaseName(QString("%1/sozluk.db3").arg(sYol)); vt.open(); vt.exec("PRAGMA journal_mode=MEMORY"); vt.exec("PRAGMA CacheSize=10240"); .....@A-A-SEZEN said in SQLite management of multiple databases:
vt = QSqlDatabase::addDatabase("QSQLITE", sBaglantiAdi);
Do NOT store QSqlDatabase instances as member variables!
This is explained in red color in the documentation (https://doc.qt.io/qt-6/qsqldatabase.html):
"Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database(). If you chose to have a QSqlDatabase member variable, this needs to be deleted before the QCoreApplication instance is deleted, otherwise it may lead to undefined behavior." -
@A-A-SEZEN said in SQLite management of multiple databases:
I have it when I use a separate QSqlDatabase variable and connection name in each class, I also manage it jointly in a separate database class.
I commend your effort. Thank you.
I thought I made it clear above. It means it didn't happen. "Google Translate" english... excuse me.
However, in practice, we encounter different scenarios. When building a class, I prefer to say "delete if there is already a connection with the same name" instead of a "use if already open connection" check. I can also do this in destructor. However, in terms of legibility in coding, I adopt the combination of related processes if possible.
For example, there is this situation in the constructor of the "Sansur" class below.connect(ui->pushSansur, &QPushButton::clicked, this, [&]() { auto * form = new Sansur(); form->setModal(true); form->exec(); delete form; });Sansur::Sansur(QWidget *parent) : QDialog(parent), ui(new Ui::Sansur) { ui->setupUi(this); this->setWindowIcon(QIcon("://imaj/warning.png")); this->setWindowTitle("Sansür Çalışması"); QString sBaglantiAdi = "sansur"; for (auto &a: QSqlDatabase::connectionNames()) { if (a == sBaglantiAdi) QSqlDatabase::removeDatabase(a); } QString sYol = QString("%1/Asoza/Sozluk").arg(QStandardPaths::standardLocations(QStandardPaths::DocumentsLocation).at(0)); vt = QSqlDatabase::addDatabase("QSQLITE", sBaglantiAdi); vt.setDatabaseName(QString("%1/sozluk.db3").arg(sYol)); vt.open(); vt.exec("PRAGMA journal_mode=MEMORY"); vt.exec("PRAGMA CacheSize=10240"); .....@A-A-SEZEN said in SQLite management of multiple databases:
QString sBaglantiAdi = "sansur";
for (auto &a: QSqlDatabase::connectionNames()) {
if (a == sBaglantiAdi)
QSqlDatabase::removeDatabase(a);
}Simply call
QSqlDatabase::removeDatabase(sBaglantiAdi); -
@A-A-SEZEN said in SQLite management of multiple databases:
QString sBaglantiAdi = "sansur";
for (auto &a: QSqlDatabase::connectionNames()) {
if (a == sBaglantiAdi)
QSqlDatabase::removeDatabase(a);
}Simply call
QSqlDatabase::removeDatabase(sBaglantiAdi); -
Hi together,
sorry for the belated reply.
Well, I see that I am not the only one who discusses about the right way to deal with that.
I decided now to have a class called "configMain" and in that class, by calling the constructor while instantiating, I create a connection (addDatabase) and in the destructor I remove the connection (removeDatabase). So the connection stays as long as the mainConfig object is alive (the class that handles the database).
In the program code, I open and close this connection each time I need access to the database (x.open(), x.close()). I get access to the database connection with the static method QSqlDatabase::database(identifier) everywhere global in the code, what is really well so I dont need (and, as jsulm correctly mentioned) also shouldnt create a member variable of the QSqldatabase object.
Well, what do you think about that way to deal with it? Good or bad?
-
Hi together,
sorry for the belated reply.
Well, I see that I am not the only one who discusses about the right way to deal with that.
I decided now to have a class called "configMain" and in that class, by calling the constructor while instantiating, I create a connection (addDatabase) and in the destructor I remove the connection (removeDatabase). So the connection stays as long as the mainConfig object is alive (the class that handles the database).
In the program code, I open and close this connection each time I need access to the database (x.open(), x.close()). I get access to the database connection with the static method QSqlDatabase::database(identifier) everywhere global in the code, what is really well so I dont need (and, as jsulm correctly mentioned) also shouldnt create a member variable of the QSqldatabase object.
Well, what do you think about that way to deal with it? Good or bad?
@Binary91 said in SQLite management of multiple databases:
Well, what do you think about that way to deal with it? Good or bad?
Good, just not sure about this part: "In the program code, I open and close this connection each time I need access to the database". You wrote that you open the connection once in the constructtor of configMain and close it again in the destructor, so how does this match with what you wrote in the cited text?
-
@Binary91 said in SQLite management of multiple databases:
Well, what do you think about that way to deal with it? Good or bad?
Good, just not sure about this part: "In the program code, I open and close this connection each time I need access to the database". You wrote that you open the connection once in the constructtor of configMain and close it again in the destructor, so how does this match with what you wrote in the cited text?
@jsulm Hi,
what I meant was the differentiation of "creating" the connection with QSqlDatabase::addDatabase(xxx) in the constructor (and so removeDatabase(xxx) in the destructor) and opening / closing it with the database object's methods db.open() and db.close() each time I need the connection for access to the database... And I would do it that way for any database I have. Creating the connections early and hold it to the end, but the opening/closing of these connections is done repetitively while runtime each time I need access to the database.
Is that the correct way of use?
-
@jsulm Hi,
what I meant was the differentiation of "creating" the connection with QSqlDatabase::addDatabase(xxx) in the constructor (and so removeDatabase(xxx) in the destructor) and opening / closing it with the database object's methods db.open() and db.close() each time I need the connection for access to the database... And I would do it that way for any database I have. Creating the connections early and hold it to the end, but the opening/closing of these connections is done repetitively while runtime each time I need access to the database.
Is that the correct way of use?
-
@Binary91 Opening and closing database connections is expensive. So, if you use the connection often you should open it once and close when your application terminates or you really do not need the connection anymore.
@jsulm Thank you! That is what I wanted to know. So there is no performance leak having one or more connections continously opened while runtime? And it doesn't matter if there were multiple open connections (I don't need that right now, just to know it..) to one database? It can only have one connection direct read/write access at one time point and other commands are queued, right?
-
Hi together,
sorry for the belated reply.
Well, I see that I am not the only one who discusses about the right way to deal with that.
I decided now to have a class called "configMain" and in that class, by calling the constructor while instantiating, I create a connection (addDatabase) and in the destructor I remove the connection (removeDatabase). So the connection stays as long as the mainConfig object is alive (the class that handles the database).
In the program code, I open and close this connection each time I need access to the database (x.open(), x.close()). I get access to the database connection with the static method QSqlDatabase::database(identifier) everywhere global in the code, what is really well so I dont need (and, as jsulm correctly mentioned) also shouldnt create a member variable of the QSqldatabase object.
Well, what do you think about that way to deal with it? Good or bad?
@Binary91 said in SQLite management of multiple databases:
(x.open(), x.close()).
I don't think so. Do the open() / close() also in your long lived config and only ask for the connection with QSqlDatabase::database(connection) if you need one. I see no reason to do more. I have the impression, that the layout of the QSqlDatabase layout is ment to be used this way.
Again: The details in the documentation of the opening and closing calls are important.
