Advice on adding threading in database application

panosk

I have started to investigate ways that can help me add threads and keep the gui responsive. My use case is as follows:

The user works on a QTableView using a QSqlRelationalTableModel. When they move to a new row, a signal is emitted from the view with the index which is caught by a slot in another model (QSqlTableModel). The data from the index is used to run the same query in an arbitrary number of databases in the QSqlTableModel, the results fill this model's separate view, and some data are emitted back to the QSqlRelationalTableModel.

Although the query and all the involved operations run pretty fast, it is possible that the user may add many databases which have to be queried simultaneously, so I would like to run this query in parallel in all databases and in another thread so the user can move instantly to a new row even if the query results may appear with a small delay. Currently, the slot that accepts the index and runs the query in the QSqlTableModel looks like this:

void MySearchModel::searchDatabase(const QModelIndex &index) {
     if (!index.isValid())
         .....
     foreach (MyDatabase *db, m_Databases) {//m_Databases is a list of objects with a QSqlDatabase
         QSqlQuery query(db->database());
         .....
        //(the internal data structure of the model is filled with the query results from all dbs)
        emit signalThatGoesBackToQSqlRelationalTableModel(QSqlRecord); // One of the results 
                                                               // is sent back to the other model

For starters, it looks tempting to try with QtConcurrent::run and friends (QFutureWatcher, etc), as it seems that I can get return values and use signals/slots.

Any ideas and suggestions would be much appreciated.

SGaist

.Hi,

Will you be running several queries at the same time on the same databases ?

Also are you maintaing open connections to all the databases ? If so, what for ?

panosk

Hi,
@SGaist said:

Will you be running several queries at the same time on the same databases ?

I will be running the same query on the same set of databases. Now there is 1 set of databases but I will add at least one more set. Each set can contain an arbitrary number of databases (although more than 5 will be uncommon).

Also are you maintaing open connections to all the databases ? If so, what for ?

Yes, once the user adds a database, the connection stays open because the database is queried every few seconds. Would it be better to open and close the connections constantly? FWIW, the connections can be local (sqlite) or remote (postgre and/or mysql).

SGaist

I'd say that the open/close connection vs permanent connection is rather a question of environment e.g. if you are on a very bad connection.

So if I understand correctly you more or less continuously query these DB but you will also start your search query in parallel to that, correct ?

panosk

@SGaist said:

So if I understand correctly you more or less continuously query these DB but you will also start your search query in parallel to that, correct ?

Yes, that's the idea, plus I want all this to happen in a separate thread so the GUI stays responsive. As you can see in my initial post, right now I just loop through the set of databases and execute the query sequentially on each of them.

kshegunov

@panosk

I'd put a separate QObject (not visible in the public part of the API) that communicates with the databases in it's own thread, and signal the model from it. For example through a model slot that exposes the setData override to this "controller" object. The exact technology of threading is more a matter of preference, but I usually use the "low-level API" - QThread and friends, but should be possible with any of the others as well.

panosk

@kshegunov
Thanks for the suggestion. I did study a bit about threading in Qt and got a basic grasp of it, so I started a naive implementation that works as follows:
I created a worker object that accepts a signal with the query string from the model and signals the results back to the model. Then, in the constructor of my model, I create a separate thread and a separate worker for each database. This is the general idea:

MyDbModel::MyDbModel(const QList<MyDatabaseObject *> &Databases,...)
	:QSqlTableModel(parent), m_Databases(Databases)...
{
            ....
	foreach (MyDatabaseObject *db, m_Databases) {
		//if(!db->connectDb()) { // Previously: Setup and connect each db to the model
		//    ...
		QThread *t = new QThread;
		MyDbWorker *worker = new MyDbWorker(db); // The constructor of MyDbWorker
		connect....                              // now setups the db
		connect....
		worker->moveToThread(t);
		t->start();
		}
	}
}

As I warned, this is naive, but I just wanted to verify proper connection and functionality of signals and slots, and happily everything seems to work correctly. Now, my big issue is that the list of databases (m_Databases ) can be updated on runtime by the user (the model has all necessary functionality for this), so the model also acts as a resource handler. How can I handle properly adding and removing databases? To be precise, even with this first naive implementation it seems I can add databases (I simply copied the code from the constructor above to the add function), but I can't figure out how to change the design so I can destroy the worker objects (and their threads?) when a database is removed.

Thanks in advance for any hints.

kshegunov

@panosk said:

Now, my big issue is that the list of databases (m_Databases ) can be updated on runtime by the user (the model has all necessary functionality for this), so the model also acts as a resource handler.

It's not its place to do that. Otherwise it'd have been called MyModelAndDatabaseManager, and if you have and in the class name, then you need to split the class in two. :)
The joke-ish comment aside, if your worker object is going to use and query the database, then it's its responsibility to know about it. Think of the model as a middle-man in this case, it only stores the data and "requests" some data to be fetched, when the data's ready, it's "notified" of that.

So, what I suggest is the following: When a database is added, a signal can be raised with the connection name. The worker can then "fetch" the database object with QSqlDatabase::database() by name. Again, if database is removed, a signal can be emitted and the worker can subscribe to that and knows the db is no-longer valid (and/or remove the db itself).

but I can't figure out how to change the design so I can destroy the worker objects (and their threads?)

The worker object's destruction you can connect to the thread's finished signal. That is:

QObject::connect(thread, SIGNAL(finished()), worker, SLOT(deleteLater()));

As for the thread object, you can do the same:

QObject::connect(thread, SIGNAL(finished()), thread, SLOT(deleteLater()));

The thread you can stop, by calling (usually by means of a signal-slot connection) the QThread::quit() slot. This doesn't mean the thread exits immediately, but only that a quit event is posted on the event queue. Do not call terminate(), it might seem tempting, but forceful termination is another kettle of fish entirely, probably books can be written about it.

Now, there's a special case when exiting your application, it's a good idea not to exit before all the threads have finished. You should keep a list of the threads, and call QThread::wait() for each of them, but there's somewhat more sophisticated way of doing it. Consider this simple example:

class ThreadManager : public QObject
{
    Q_OBJECT

    ThreadManager()
        : lock(0)
    {
        QObject::connect(qApp, SIGNAL(aboutToQuit()), this, SLOT(wait()), Qt::DirectConnection);
    }

public slots:
    void threadStarted()
    {
        activeThreads.fetchAndAddRelaxed(1);
    }

    void threadFinished()
    {
        lock.release();        
    }

    void wait()
    {
        int resources = activeThreads.fetchAndStoreOrdered(0);
        // This will block in waiting for the threads to exit, but providing timeout, we don't want to block forever on exit
        lock.tryAcquire(resources, 5000); 
    }

private:
    QAtomicInt activeThreads;
    QSemaphore lock;
};

So, how to use that sugarly-goody class:
You create an object and connect the thread's signals to the class, and that's pretty much it. Like this:

ThreadManager * manager; //< Create it for example in main, and pass the pointer to wherever you'll need to create the threads.

QThread * thread = new QThread();
QObject::connect(thread, SIGNAL(finished()), thread, SLOT(deleteLater())); //< This takes care of the thread object
// We want to make sure the thread gets the point, the application is exiting and the thread should quit as well.
QObjct::connect(qApp, SIGNAL(aboutToQuit()), thread, SLOT(quit()));
// So these two connections keep track of our running threads, ultimately we'll wait for the threads to finish before exiting the app
// Notice these are (and are supposed to be) direct connections
QObject::connect(thread, SIGNAL(started()), manager, SLOT(threadStarted()), Qt::DirectConnection);
QObject::connect(thread, SIGNAL(finished()), manager, SLOT(threadFinished()), Qt::DirectConnection);
// Starting up the thread
thread->start();

ThreadWorker * worker = new ThreadWorker();
worker->moveToThread(thread);

QObject::connect(thread, SIGNAL(finished()), worker, SLOT(deleteLater())); // This takes care of the worker object

So I hope that's of help. Also it might be worth considering using QThreadPool and QRunnable, to have easily managed set of reusable threads.

Kind regards.

panosk

@kshegunov
Thanks a lot for the useful suggestions, I appreciate it.

It's not its place to do that. Otherwise it'd have been called MyModelAndDatabaseManager, and if you have and in the class name, then you need to split the class in two. :)
The joke-ish comment aside, if your worker object is going to use and query the database, then it's its responsibility to know about it. Think of the model as a middle-man in this case, it only stores the data and "requests" some data to be fetched, when the data's ready, it's "notified" of that.

Indeed, the need for a separate database manager became obvious as soon as I brought the workers into the scene and to be honest I had already started work on this ;)

I think I have enough information now to continue with the redesign, so I'll mark this thread as solved, but I may come back some time in the future to ask more advice.

Thanks again :)

kshegunov

@panosk

and to be honest I had already started work on this

You already have that in QSqlDatabase, you only need to keep track of the connections' names.

Good luck!