Handling Database on Multiple Thread

mvsri

Hey there,
I am working on a project where I have to read data from hardware and display it on GUI as well as store it in a database.

So basically I used Two Threads, One for Serial Communication and Another for Database Updation.

I will upload a basic code here which i have tested and is working as exepected

MainWindow.h

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <serialworker.h>
#include <workerthread.h>
#include <QDebug>

QT_BEGIN_NAMESPACE
namespace Ui { class MainWindow; }
QT_END_NAMESPACE

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

private:
    Ui::MainWindow *ui;
    SerialWorker serial;
    WorkerThread worker;

private slots:
    void startCapturing();
    void stopCapturing();
    void slotUpdate(const QString username, const int userlevel, const QString reason);
};
#endif // MAINWINDOW_H

MainWindow.cpp

#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    connect(ui->pushButton_start, &QPushButton::clicked, this, &MainWindow::startCapturing);
    connect(ui->pushButton_stop, &QPushButton::clicked, this,  &MainWindow::stopCapturing);

    connect(&serial, &SerialWorker::serialSignal, this, &MainWindow::slotUpdate);
}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::startCapturing()
{
    serial.startSending();
    statusBar()->showMessage("Started Capturing", 2000);
}

void MainWindow::stopCapturing()
{
    serial.isSerialRunning = false;
    statusBar()->showMessage("Stopped Capturing", 2000);
}

void MainWindow::slotUpdate(const QString username, const int userlevel, const QString reason)
{
    worker.UpdateAuditTrail(username, userlevel, reason);
    ui->label_username->setText(username);
    ui->label_userlevel->setText(QString::number(userlevel));
    ui->label_reason->setText(reason);
}

SerialWorker.h

#ifndef SERIALWORKER_H
#define SERIALWORKER_H

#include <QThread>
#include <QObject>
#include <QDebug>
#include <QDateTime>

class SerialWorker : public QThread
{
    Q_OBJECT
public:
    explicit SerialWorker(QObject *parent = nullptr);

public:
    void startSending();
    bool isSerialRunning = false;

private:
    void run() override;

signals:
    void serialSignal(const QString username, const int userlevel, const QString reason);

};

#endif // SERIALWORKER_H

SerialWorker.cpp

#include "serialworker.h"

SerialWorker::SerialWorker(QObject *parent) : QThread(parent)
{

}

void SerialWorker::startSending()
{
    isSerialRunning = true;
    if(!isRunning())
    {
        start();
    }
}

void SerialWorker::run()
{
   // qDebug() << " ==> Serial Worker Run Function: " << QThread::currentThreadId();
    while(isSerialRunning)
    {
        emit serialSignal("Sup1", 1, "dumb reason");
        QThread::sleep(1);
    }

}

WorkerThread.h

#ifndef WORKERTHREAD_H
#define WORKERTHREAD_H

#include <QThread>
#include <QObject>
#include <QDebug>
#include <QtSql/QSql>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QSqlError>
#include <QDateTime>

class WorkerThread : public QThread
{
    Q_OBJECT
public:
    explicit WorkerThread(QObject *parent = nullptr);

public:
    void UpdateTrail(const QString username, const int userlevel, const QString reason);

signals:

private:
    void run() override;
    QString Username;
    QString Usertype;
    int     Userlevel;
    QString Reason;

public slots:
};

#endif // WORKERTHREAD_H

WorkerThread.cpp

#include "workerthread.h"

WorkerThread::WorkerThread(QObject *parent) : QThread(parent)
{

}

void WorkerThread::UpdateTrail(const QString username, const int userlevel, const QString reason)
{
    this->Username = username;
    this->Userlevel = userlevel;
    this->Reason = reason;

    if(!isRunning())
    {
        start();
    }
}

void WorkerThread::run()
{
    qDebug() << " ==> Database Worker Thread Address: " << QThread::currentThreadId();
    QSqlDatabase db;
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("dataname");
    db.setUserName("user");
    db.setPassword("passowrd");
    if(!db.open())
    {
        qDebug() << " ==> Error Connecting Mysql in Trail ";
    }
    else
    {
        qDebug() << " ==> Database Opened Successfully";
        QSqlQuery QRY;
        QRY.prepare("insert into Trails(username, usertype, times, reason) values(:user, :level, CURRENT_TIMESTAMP(), :quer)");
        QRY.bindValue(":user", this->Username);
        QRY.bindValue(":level", this->Usertype);
        QRY.bindValue(":quer", this->Reason);

        if(!QRY.exec())
        {
            qDebug()<<" ==> Database update error in Trail thread "<< QRY.lastError();
        }

        // Close
        QString connection;
        connection = db.connectionName();
        db.close();
        db = QSqlDatabase();
        db.removeDatabase(connection);
    }
}

Basically in SerialThread.CPP - Run Function I have used a signal and added a delay in that section my serial code will be there.

But overall this is the structure I am using now and it is working as expected.

I need some advice on whether this code is leakproof or is there any other way i can improve my code's logic and any changes in the same code would be appreciated.

Christian Ehrlicher

Using a QThread for a single insert statement doesn't look very effective. The same goes for the serial communication - QSerialPort is async - no thread needed.

mvsri

@Christian-Ehrlicher There are multiple insert statements and I am using open close principal for database connection.
Regarding Serialport Qt serial blocking example
I referred this blocking example and I read and write data from serial thread and also planning to use wait conditions.

Kent-Dorfman

delay logic is troublesome because it makes assumptions about response times, sometimes needlessly, and other times not long enough. Avoid that kind of programming when possible, and favor event sycronization instead.

mvsri

@Kent-Dorfman For serial communication most preferable way is to use async code with signals and slots. Understood the point here.
But can you tell me when is preferred way to use above blocking example.

Regarding Database connection can I use the thread where there are multiple insert statements and I’m using open close method here.

Thank you!

Kent-Dorfman

open/close is expensive. insert/select based on prepared statements is cheap. move the open/close logic out of the thread and do mutext blocked sql functions if you chose a single connect. otherwise create a connection pool and have your thread use a free connection from the pool for every concurrent sql transaction.

implementation is left as an exercise for the OP.

Christian Ehrlicher

Even two or thread statements don't need a separate thread. Especially when the open/close is done in the run function as @Kent-Dorfman pointed out.
Write a proper class which opens the db in the ctor and closes it in the dtor, implement the insert statements in a slot() and after you found out that this causes performance problems (which I doubt) then move this object to another thread.

mvsri

@Christian-Ehrlicher Thank you for the suggestion I will implement it and check the result.
Although before proceeding my query is since I'm reading data from serialthread and emitting it to mainscreen and in mainscreen I'm displaying the values as well as storing the values in the database using databasethread. My point here is should I use a locking mechanism here when handling the data between threads?

mvsri

@Kent-Dorfman I will take your suggestions as well as @Christian-Ehrlicher suggestion and implement them.

Christian Ehrlicher

@mvsri said in Handling Database on Multiple Thread:

My point here is should I use a locking mechanism here when handling the data between threads?

Don't use threads and you don't need a locking.
Use signals and slots properly and you don't need a locking even between threads.

https://doc.qt.io/qt-5/signalsandslots.html

mvsri

@Christian-Ehrlicher I rewrote the code and implemented everything with signals and slots, thank you for the suggestions. It pretty much sums up the questions I had.

I just need one suggestion though what is the real use case of blocking serial communication method Qt serial blocking example. When do we use such kind of code then?

Christian Ehrlicher

@mvsri said in Handling Database on Multiple Thread:

When do we use such kind of code then?

I would say - mostly never. Maybe if there is really much data coming in from the serial port (e.g. in high-speed modes when you can get ~8MBit/s) to make sure all the data can be handled without killing the gui interaction. But as I said earlier - don't use threads until you really need them :)

Kent-Dorfman

to continue the point @Christian-Ehrlicher mentioned above, blocking serial tasks usually exist on server daemon applications, but not on anything with user interaction or a GUI.