QSqlDatabase best practices with long-running application

[redBeard]

I have a Qt-based application that communicates with both MySQL and SQLServer. The application is long-running (started 4 - 5 times/daily for 90 minutes at a time) and the users interact with the database periodically - mostly reading, but some updates.

The production environment consists of one DB server and 1 - 10 versions of the software running simultaneously on multiple computers connected to the same DB server.

I have a question about QSqlDatabase best practices n database connections.

Is it best to open a database only when necessary and close it quickly after the user action (e.g., viewing and updating a record)?

Or is it OK to keep the DB open for the duration of the life of the application?

I have a data object graph which I need to create and persist during the application. The data model is pretty basic - lots of master-detail type of relationships. I traverse the object graph and map n insert the objects to the appropriate DB tables. The 'insert or update' DB operation can be entered at any point in the object graph. Consequently, each type of object manages (via a simple class hierarchy) the connection to the DB (open on DAO creation and close on destruction). I create a transaction for the 'insert or update' DB operation as well.

I'm wondering if I need to use some sort of reference counter for DB open/close operations to ensure the DB only closed after the last DAO class exits...

Ideas?

[codeman]

This is a very interesting issue. Have you meanwhile any suggestions or directions to this situation?
I am also interested in solving this issue, cause I am in the same situation. I would like to program a client with access to a server DB. So 1 or 5 seems to be no prob but what about 100 clients? What are the Strategies on the App side and on the other hand the DB side??

[wysota]

Keep the database open if your database engine is configured to handle simoultanous connections from all the clients.

[redBeard]

This is a very interesting issue. Have you meanwhile any suggestions or directions to this situation?
No, no other suggestions.

I implemented short-lived database connections. Works well. In general:

- 'main() starts n I 'QSqlDatabase::addDatabase()' and set connection properties and 'open()' it.
- call QApplication::exec();
- in main(), after the 'a.exec()' returns, I call 'QSqlDatabase::removeDatabase()'

In the application, I call 'QSqlDatabase::database(name, true)' at the point I need to perform some DB activity. After the DB activity, I call 'QSqlDatabase::close()'.

Within the application when I want to update or insert objects from my data graph to the DB, I have very small data access objects (DAOs) that mirror my data graph objects and traverse the tree n update or insert objects at each point in the graph.

Since I can 'persist' the graph starting at any point (assuming the parent of the starting point is already persisted and I have a key), I implemented a map of databases n reference counters (map<string, QAtomicInt>). As I get or open a DB I increment the reference counter for the DB. As I close it, I decrement the counter. when the counter reaches zero, I actually do the QSqlDatabase::close().


I would like to program a client with access to a server DB. So 1 or 5 seems to be no prob but what about 100 clients? What are the Strategies on the App side and on the other hand the DB side??
There are multiple strategies here. If you have lots of CRUD operations on your DB, you may want to consider a single point of entry into the DB (e.g., some single service like a web services). That way accesses to the DB are serialized.

We don't do that so we have to very carefully code our application to ensure that whatever object instance (e.g., record in the DB) is to be updated we start a transaction, retrieve it, update it and flush it back to the DB then commit the transaction.

Hope that helps. I've been working with Java n Hibernate for many years and I sort of like what Hibernate does so I implemented a Hibernate-lite-lite-lite DB infrastructure using Qt.

[codeman]

Thank you very much for sharing your experiences. But what in detail means this:



Since I can 'persist' the graph starting at any point (assuming the parent of the starting point is already persisted and I have a key), I implemented a map of databases n reference counters (map<string, QAtomicInt>). As I get or open a DB I increment the reference counter for the DB. As I close it, I decrement the counter. when the counter reaches zero, I actually do the QSqlDatabase::close().

Do you connect from one client to n DB´s?? Perhaps my english is bad ;o)) I attach a sample. What do you think about this aproach.

[redBeard]

Thank you very much for sharing your experiences. But what in detail means this:

Here:


QSqlDatabase
MyDatabase::getDatabase (const string & name)
throw (FlowException)
{
QSqlDatabase _db = QSqlDatabase::database(QString::fromStdString(name ), true);
if ( _db.isValid() && _db.isOpen()) {
DB_NAME_MAP::iterator dItr = _dbCountMap.find (name);
if (dItr != _dbCountMap.end()) {
bool refRet = dItr->second.ref();
} else {
QAtomicInt newCounter;
newCounter.ref();
_dbCountMap[name] = newCounter;
}
return _db;
}
throw DataAccessLayerException("database '" + name + "' not open or valid");
}

void
MyDatabase::close(const string & name)
{
QSqlDatabase _db = QSqlDatabase::database(QString::fromStdString(name ), false);

// only close if reference counter is 0.

bool derefRet = false;
DB_NAME_MAP::iterator dItr = _dbCountMap.find (name);
if (dItr != _dbCountMap.end()) {
derefRet = dItr->second.deref();
} else {
throw DataAccessLayerException ("Unknown database");
}
if (! derefRet) {
_db.close();
_dbCountMap.erase (name);
}
}



Do you connect from one client to n DB´s??
Yes. I support multiple database connections w/in the same running application.

[codeman]

hmm ok thank you very much.