1. Preface

As a management system, the database is absolutely indispensable. Qt's built-in sqlite database is enough, and I can test the amount of data personally to support hundreds of millions of levels, rather than tens of millions of levels as many people on the Internet say. I have simulated more than 100 million data, and still can query very well, but in a form, the single file is large, more than 2G, performance is relative toMillions, certainly worse, but this kind of billion-level situation is still very rare, it is also recommended to store to tens of millions of levels, more than do automatic cleanup of early data.

For compatibility and extensibility, this system not only supports the default SQLite database, but also supports MySQL database. As for sql server database, PostgreSQL database and so on, there are corresponding interfaces, which have not been tested. At present, more than 30 Qt versions and various VS versions have been installed on the computer, there are enough VS versions, there is no plan to install several other databases to test.This switching support for multiple databases is mainly based on the encapsulated database classes in Qt, which is the work of Qt. The only thing to note is the subtle differences between different databases, such as when querying records, time range conditions, datetime used by sqlite, and unix_timestamp used by mysql. This subtle difference can be found inWrite the judgment processing at the specific code.

Where the database is set up, two buttons have been added deliberately, one is the connection test, which is used to test whether the database server is working, to determine if the database server information is filled in correctly, and all the database client software also has this function, so that the incorrect information can be excluded during the filling phase; the other is to initialize the database, thanks toTo generate a new database file, execute the sql script, delete the original database, and then execute the script to generate a new database file.

Skin Open Source: https://gitee.com/feiyangqingyun/QWidgetDemo https://github.com/feiyangqingyun/QWidgetDemo File name: styledemo

Experience address: https://gitee.com/feiyangqingyun/QWidgetExe https://github.com/feiyangqingyun/QWidgetExe File name: bin_sams.zip

2. Functional features

1. Collect data port, support serial port + network port, support free serial port + baud rate, network support free set IP address + communication port, each port supports collection cycle, default 1 second address, support set communication timeout, default 3 times, support maximum reconnection time, for re-reading offline devices.
2. Controller information, the ability to add a controller name, select a controller address + controller model, and set the number of detectors under the controller.
3. Detector information, can add bit number, freely select the type of detector, gas type, gas symbol, high report value, low report value, buffer value, zero value, whether enabled, alarm sound, background map, storage period, numeric conversion of decimal places, alarm delay time, alarm type (HH,LL,HL), etc.
4. Controller model + detector model + gas type + gas symbol, can be freely configured.
5. Maps support import and delete, and all detectors are free to drag and save map locations.
6. Port information + Controller information + Detector information, support import export + export to excel + print.
7. Run records + alarm records + user records, support multi-conditional combination queries, such as time period + controller + detector, all records support export to excel + print.
8. Records exported to excel support all forms file versions such as excel+wps and do not rely on software such as excel.
9. Data within a specified time range can be deleted, early data can be automatically cleaned up, and the maximum number of records can be saved.
10. Supports alarm text message forwarding, supports multiple receiving mobile phone numbers, and can set the sending interval, such as sending all alarm messages instantly or once in 6 hours, the text message content is too long, and automatically splits multiple text messages.
11. Supports alarm mail forwarding, supports multiple receiving mailboxes, and can set sending intervals, such as sending all alarm messages instantly or once in six hours, and supports attachment sending.
12. High report color + low report color + normal color + 0 value color + curve background + curve color, etc., can be chosen freely.
13. The Chinese title, English title, logo path and copyright of the software can be set freely.
14. Provides switch settings for on-off operation + alarm sound + automatic logon + password remembering, etc.
15. The alarm sound sets the number of playback times and the interface provides 17 skin file choices.
16. Supports cloud data synchronization by setting up information about cloud databases, such as database name, user name + password, etc.
17. Supports network forwarding and network reception. After network reception is turned on, the software receives data from udp for analysis.Network forwarding supports multiple target IP, which enables locally collected software to freely transfer data to the client and view detector data at any time.
18. Automatically remember the user's last remaining interface + other information and apply it automatically after restart.
19. The alarm automatically switches to the corresponding map, and the detector button flashes.
20. Double-click the detector icon for control.
21. Support user rights management, administrator + operator two categories, user login + user exit, can remember password and automatic login, more than three error prompts and close the program.
22. Supports four monitoring modes, device panel monitoring + map monitoring + tabular data monitoring + curve data monitoring, can switch freely, and four simultaneous applications.
23. Supports alarm relay linkage, a bit number can link multiple modules and relay numbers across the serial port, supporting many-to-many.
24. Local data stores support sqlite+mysql and remote data synchronization to cloud databases.Automatic reconnection.
25. Data collected by local devices is uploaded to the cloud in real time for extraction by other means such as mobile APP or web.
26. Two kinds of data sources are supported, one is serial port and network to collect device data through protocol, the other is database collection.The database collection mode can be used as a common system.
27. The built-in device emulation tool supports 16 device data emulation as well as database data emulation to test data when there is no device.
28. The default communication protocol uses the modbus protocol, and later adds the support of Internet of Things protocols such as mqtt to make a universal system.
29. Supports all windows operating systems + linux operating systems and other operating systems.

3. Effect Charts

4. Core Code

#pragma execution_character_set("utf-8")
#include "dblocalthread.h"

QScopedPointer<DbLocalThread> DbLocalThread::self;
DbLocalThread *DbLocalThread::Instance()
{
    if (self.isNull()) {
        static QMutex mutex;
        QMutexLocker locker(&mutex);
        if (self.isNull()) {
            self.reset(new DbLocalThread);
        }
    }

    return self.data();
}

DbLocalThread::DbLocalThread(QObject *parent) : QThread(parent)
{
    stopped = false;

    dbOkLocal = false;
    errorCount = 0;
    lastCheckTimeLocal = QDateTime::currentDateTime();

    checkConn = false;
    checkInterval = 30;

    dbTypeLocal = DbType_MySql;
    connNameLocal = "qt_sql_default_connection";
    hostNameLocal = "127.0.0.1";
    portLocal = 3306;
    dbNameLocal = "db_mysql";
    userNameLocal = "root";
    userPwdLocal = "root";
}

DbLocalThread::~DbLocalThread()
{
    this->stop();
    this->wait();
}

void DbLocalThread::run()
{
    while (!stopped) {
        //Perform a periodic check to see if the database connection is working
        QDateTime now = QDateTime::currentDateTime();
        if (checkConn && lastCheckTimeLocal.secsTo(now) >= checkInterval) {
            checkDb();
            lastCheckTimeLocal = now;
            continue;
        }

        //Processing data if database connection is normal and reconnecting database if it is not
        if (!dbOkLocal && errorCount >= 3) {
            closeDb();
            msleep(3000);
            openDb();
            emit debug(QString("Reconnect local database%1").arg(dbOkLocal ? "Success" : "fail"));            
            msleep(3000);
        } else {
            msleep(100);
        }
    }

    stopped = false;
}

bool DbLocalThread::openDb()
{
    //You can add support for other databases yourself
    if (dbTypeLocal == DbType_Sqlite) {
        dbConnLocal = QSqlDatabase::addDatabase("QSQLITE", connNameLocal);
        dbConnLocal.setDatabaseName(dbNameLocal);
    } else if (dbTypeLocal == DbType_MySql) {
        dbConnLocal = QSqlDatabase::addDatabase("QMYSQL", connNameLocal);
        dbConnLocal.setConnectOptions("MYSQL_OPT_RECONNECT=1;MYSQL_OPT_CONNECT_TIMEOUT=1;MYSQL_OPT_READ_TIMEOUT=1;MYSQL_OPT_WRITE_TIMEOUT=1");
        dbConnLocal.setHostName(hostNameLocal);
        dbConnLocal.setPort(portLocal);
        dbConnLocal.setDatabaseName(dbNameLocal);
        dbConnLocal.setUserName(userNameLocal);
        dbConnLocal.setPassword(userPwdLocal);
    }

    errorCount = 0;
    dbOkLocal = dbConnLocal.open();
    return dbOkLocal;
}

bool DbLocalThread::checkDb()
{
    QDateTime dtStart = QDateTime::currentDateTime();

    QString sql = "select 1";
    QSqlQuery query(dbConnLocal);
    dbOkLocal = query.exec(sql);
    dbOkLocal ? (errorCount = 0) : errorCount++;

    QDateTime dtEnd = QDateTime::currentDateTime();
    double ms = dtStart.msecsTo(dtEnd);
    emit debug(QString("Check local database connections(common %1 strip/Time-consuming %2 second)").arg(1).arg(QString::number(ms / 1000, 'f', 3)));

    return dbOkLocal;
}

void DbLocalThread::closeDb()
{
    dbConnLocal.close();
    QSqlDatabase::removeDatabase(connNameLocal);
    dbOkLocal = false;
    emit debug("Close local database");
}

void DbLocalThread::stop()
{
    stopped = true;
}

void DbLocalThread::setConnInfo(DbLocalThread::DbType dbType, const QString &connName, const QString &hostName, int port, const QString &dbName, const QString &userName, const QString &userPwd)
{
    this->dbTypeLocal = dbType;
    this->connNameLocal = connName;
    this->hostNameLocal = hostName;
    this->portLocal = port;
    this->dbNameLocal = dbName;
    this->userNameLocal = userName;
    this->userPwdLocal = userPwd;
}

void DbLocalThread::setCheckConn(bool checkConn)
{
    this->checkConn = checkConn;
}

void DbLocalThread::setCheckInterval(int checkInterval)
{
    if (checkInterval > 5 && this->checkInterval != checkInterval) {
        this->checkInterval = checkInterval;
    }
}

QSqlDatabase DbLocalThread::getDb()
{
    return dbConnLocal;
}

bool DbLocalThread::getOk()
{
    return dbOkLocal;
}