Working with databases in Qt in a multi-threaded environment

Everyone who develops applications on Qt sooner or later encounters working with a database in a multi-threaded environment. And if you carelessly read the Assistant, then you can come across some very interesting rake.

Description of the environment

Consider a typical problem. You write an application that works with the database, and you need to access the database from several threads (it doesn’t matter at the same time or not). For example, this happens if you write the server part of a certain application or you just have a logging stream with a record in the database.

Rake

Rake Description

If we carefully read the assistant (that is, not only the description of classes, but also general articles), then we will see the following inscription in the description of multi-threaded programming under Qt: “Connection can be used only by the thread that created it. At the same time, transferring the connection itself or using requests from other flows is not supported ”(free translation).

Rake explanation

Thus, the banal mutex planned by us for connecting to the database, unfortunately, will not work. Also (if we don’t need to divide the connection all the time between several threads, and in the first half of the program it should use one thread, and then the other), the moveToThread () method will not work so well when working with sockets, for example.

Trying to reflect on a problem

Ok, we cannot share a single connection between multiple threads. But how can we get around this? I see two ways:

1. Do not steam and make your connection in each thread
2. Refer to the good old Singleton pattern

Well, the first option is too simple and not suitable for the option when threads are created, do something with the database and die almost immediately (there will be an overhead for connection costs). Although for some cases, the first option fits perfectly;)
So, the second way.

A bit of theory about the pattern

The Singleton pattern implies that we can have only one object of a certain class, and any call will execute code within this object. Further in the article we will slightly depart from the canonical form of this pattern, but more on that later.

Singleton implementation in C ++

To implement this pattern, we must prohibit the following for the class:

1. Create a new object
2. Creating a copy of the object
3. Object Assignment Operation

We must also give the opportunity to get this very single instance of the class.
The class is called DatabaseAccessor. Let's write a minimal singleton implementation.

//databaseaccessor.h

class DatabaseAccessor
{
public:
    static DatabaseAccessor* getInstance();

private:
    DatabaseAccessor();
    DatabaseAccessor(const DatabaseAccessor& );
    DatabaseAccessor& operator=(const DatabaseAccessor& );
};

//databaseaccessor.cpp

DatabaseAccessor::DatabaseAccessor()
{
}

DatabaseAccessor* DatabaseAccessor::getInstance()
{
    static DatabaseAccessor instance;
    return &instance;
}

* This source code was highlighted with Source Code Highlighter.

That is, we just at the first call to DatabaseAccessor :: getInstance () create an object and return it. In the future, we return the same object.

Add a database connection

Well, everything is simple, we add a database connection to the constructor.

//databaseaccessor.h

class DatabaseAccessor
{
public:
    static DatabaseAccessor* getInstance();
    static QString dbHost;
    static QString dbName;
    static QString dbUser;
    static QString dbPass;
private:

    DatabaseAccessor();
    DatabaseAccessor(const DatabaseAccessor& );
    DatabaseAccessor& operator=(const DatabaseAccessor& );
    QSqlDatabase db;
};

//databaseaccessor.cpp

DatabaseAccessor::DatabaseAccessor()
{

    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName(dbHost);
    db.setDatabaseName(dbName);
    db.setUserName(dbUser);
    db.setPassword(dbPass);
    if (db.open())
    {
        qDebug("connected to database");
    }
    else
    {
        qDebug("Error occured in connection to database");
    }
}

DatabaseAccessor* DatabaseAccessor::getInstance()
{
    static DatabaseAccessor instance;
    return &instance;
}

//main.cpp

int main(int argc, char *argv[])
{
//...
    DatabaseAccessor::dbHost = "localhost";
    DatabaseAccessor::dbName = "our_db";
    DatabaseAccessor::dbUser = "root";
    DatabaseAccessor::dbPass = "";
    DatabaseAccessor::getInstance();
//...

}

* This source code was highlighted with Source Code Highlighter.

When initializing the program, we simply registered the necessary data for access to the database and created an object for connecting to the database.

So, what is next?

Now we need to give the opportunity to work with this database. To begin with, we implement the possibility of a simple request without receiving data back (update, delete, insert without the need to know a new id).

//databaseaccessor.h

class DatabaseAccessor
{
public:
    static DatabaseAccessor* getInstance();
    static QString dbHost;
    static QString dbName;
    static QString dbUser;
    static QString dbPass;

public slots:
    void executeSqlQuery(QString);

private:

    DatabaseAccessor();
    DatabaseAccessor(const DatabaseAccessor& );
    DatabaseAccessor& operator=(const DatabaseAccessor& );
    QSqlDatabase db;
};

//databaseaccessor.cpp

DatabaseAccessor::DatabaseAccessor()
{

    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName(dbHost);
    db.setDatabaseName(dbName);
    db.setUserName(dbUser);
    db.setPassword(dbPass);
    if (db.open())
    {
        qDebug("connected to database");
    }
    else
    {
        qDebug("Error occured in connection to database");
    }
}

DatabaseAccessor* DatabaseAccessor::getInstance()
{
    static DatabaseAccessor instance;
    return &instance;
}

void DatabaseAccessor::executeSqlQuery(QString query)
{
    QSqlQuery sqlQuery(query, db);
}

//ourthread.h

class OurThread : public QThread
{
    Q_OBJECT
//...
signals:
    void executeSqlQuery(QString);
//...
}

//ourthread.cpp

OurThread::OurThread()
{
    connect(this, SIGNAL(executeSqlQuery(QString)), DatabaseAccessor::getInstance(), SLOT(executeSqlQuery(QString)));
}

void OurThread::run()
{
    emit executeSqlQuery("DELETE FROM users WHERE uid=5");
}

* This source code was highlighted with Source Code Highlighter.

Here we create a public slot in our singleton, which takes a query string and sends it to the database. In a typical stream, we create a signal and connect it to the singleton slot. When the thread starts, we send a request to delete the user with id 5.

But how to get the result of the query?

First, we need to first decide what we want from our singleton. Either we want it to execute a large number of various queries (an analog of the usual class for working with the database), or we have a certain set of typical queries that we need to fulfill. In the second option, we can transfer the entire validation to our singleton and thereby reduce the amount of code in the project;) According to the old tradition, we will implement the second option;) We will add a method that will check the username / password of the user.

//databaseaccessor.h

class DatabaseAccessor
{
public:
    static DatabaseAccessor* getInstance();
    static QString dbHost;
    static QString dbName;
    static QString dbUser;
    static QString dbPass;

public slots:
    void executeSqlQuery(QString);
    void validateUser(QString, QString);

private:

    DatabaseAccessor();
    DatabaseAccessor(const DatabaseAccessor& );
    DatabaseAccessor& operator=(const DatabaseAccessor& );
    QSqlDatabase db;
};

//databaseaccessor.cpp

DatabaseAccessor::DatabaseAccessor()
{

    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName(dbHost);
    db.setDatabaseName(dbName);
    db.setUserName(dbUser);
    db.setPassword(dbPass);
    if (db.open())
    {
        qDebug("connected to database");
    }
    else
    {
        qDebug("Error occured in connection to database");
    }
}

DatabaseAccessor* DatabaseAccessor::getInstance()
{
    static DatabaseAccessor instance;
    return &instance;
}

void DatabaseAccessor::executeSqlQuery(QString query)
{
    QSqlQuery sqlQuery(query, db);
}

void DatabaseAccessor::validateUser(QString login, QString pass)
{
    login.remove(QRegExp("['\"]"));
    pass.remove(QRegExp("['\"]"));
    QString query = "SELECT IFNULL(uid, -1) as user_id FROM users WHERE username='"+login+"' AND password='"+pass+"'";
    QSqlQuery sqlQuery(query, db);
    if (sqlQuery.first())
    {
        long userId = sqlQuery.value(0).toInt();
        QMetaObject::invokeMethod(sender(), "setUserId", Qt::DirectConnection, Q_ARG(long, userId));
    }
    else
    {
        QMetaObject::invokeMethod(sender(), "setUserId", Qt::DirectConnection, Q_ARG(long, -1));
    }
}

//ourthread.h

class OurThread : public QThread
{
    Q_OBJECT
//...
signals:
    void executeSqlQuery(QString);
    void validateUser(QString, QString);

public slots:
    void setUserId(long);

private:
    bool lastResultQueryIsReallyLast;
    long userId;
    bool checkUser(const char*, const char*);

//...
}

//ourthread.cpp

OurThread::OurThread()
{
    lastResultQueryIsReallyLast = false;
    connect(this, SIGNAL(validateUser(QString,QString)), DatabaseAccessor::getInstance(), SLOT(validateUser(QString,QString)), Qt::BlockingQueuedConnection);
    connect(this, SIGNAL(executeSqlQuery(QString)), DatabaseAccessor::getInstance(), SLOT(executeSqlQuery(QString)));
}

void OurThread::run()
{
    checkUser("user", "password");
}

bool OurThread::checkUser(const char* login, const char* pass)
{
    emit validateUser(login, pass);
    while (!lastResultQueryIsReallyLast)
    {
        msleep(1);
    }
    lastResultQueryIsReallyLast = false;
    return (userId > 0);
}

void OurThread::setUserId(long userId)
{
    this->userId = userId;
    lastResultQueryIsReallyLast = true;
}

* This source code was highlighted with Source Code Highlighter.

Here we added another slot to our singleton, which takes 2 parameters (username and password). Also in our stream, we connected it to the signal in the “queue with blocking” mode (that is, the slot will be executed in the context of the stream with a singleton, but our stream will wait until the signal reaches the destination). We also added a slot to our stream that accepts the id of the found user. At start, the thread emits a signal for user verification and waits until an answer arrives (the lastResultQueryIsReallyLast variable is responsible for this). Naturally, the singleton does not know all of its user threads, so the invokeMethod () method is used to call the method on the object that sender () will return (this is the method that returns the sender of the signal if we are in the slot). Moreover, the sender method is called directly so as not to wait for the next pass of the event loop.
In principle, the first method (when we make more general methods of access to the database) is easily obtained from the second. You just need to go through all the rows returned by the database in the singleton method and stuff them into some QList, which is returned to the request sender.

Finally

In principle, it turned out not difficult and quite nice.
Plus, we have the opportunity to break into several connections (remember, I spoke about the deviation from the pattern). In this case, we need to slightly rewrite the method of obtaining the instance (we need to add balancing across several instances and return the least busy plus of course we need to remember who took which instance), we also need to add the name of the connection to the database creation (for example, the name can be generated by the first object that received access to this instance) and add a method that will return the desired connection name based on the sender of the request.