QT writing IOT management platform 47 general database settings

One 、 Preface

In order to make this general database component , Virtual machines are specially installed to install various versions of different types of databases for testing , Including compiling the corresponding database plug-in , I have always believed that everything should proceed from reality + Have the actual right to speak , Including differences Qt Version compilation mysql、oracle Database plug-ins , And different databases are querying data 、 Display data in pages 、 Empty data 、 Bulk insert data 、 Batch delete data and other operations , Experienced at least half a month of crazy practice , light mysql It's installed 5.7 and 8.0 edition ,postgresql Installed 9.0、10.0、13.0 Three versions ,oracle Installed 11.0, Domestic databases are installed kingbaseV8, Repeatedly integrate the IOT management platform 、 Video monitoring system 、 Visual large screen electronic Kanban system , Test under various databases , Optimized hundreds of details, large and small , It also supports direct database connection and ODBC There are two forms of data source connection to the database , Such as through oci Library collocation qoci The plug-in connection oracle database 、 adopt odbc Data source matching qodbc The plug-in connection oracle database , To form the current version .

Basic functions of general database components ：

1. Detect whether the database connection is normal , It is usually detected without specifying the database name , Because the database does not exist at this time , You need to connect before you create a new .
2. perform sql Script initializes the database , Including creating a new database 、 New database table 、 Initialization record, etc .
3. General database connection and management class , Be responsible for opening the database 、 close 、 perform sql sentence 、 Batch execution 、 Automatic reconnection 、 Real time detection of connection status, etc .
4. General database record cleaning class , Be responsible for clearing the records of the specified database table .
5. General database record page turning class , Different database types correspond to different query page turning display record statements .
6. Bind database tables to the data model QSqlTableModel.
7. Get the number of query records , Different database types are different sql sentence .
8. Query all table names in the database 、 All field names , Different database types are different sql sentence .
9. General date range sql sentence .

Two 、 Functional characteristics

2.1 Software modules

1. Equipment monitoring module , Including data monitoring （ Show... In tabular form ）、 Device panel （ Display in panel form ）、 Map monitoring （ Display in map form ）、 Curve monitoring （ Show in curve form ）.
2. Data query module , Including alarm records 、 Operation record 、 Operation record .
3. System setup module , Including basic settings 、 Port Management 、 Controller management 、 Detector Management 、 Alarm linkage 、 Type setting, etc .
4. Other setting modules , Including user management 、 Map Management 、 Position adjustment 、 Configuration design 、 Equipment commissioning, etc .

2.2 Basic function

1. Device data acquisition , Support serial port 、 The Internet , Serial port can set serial port number 、 Baud rate , The network can be set IP Address 、 Communication port .
2. Each port supports acquisition cycle time , Default 1 One device per second .
3. Support to set communication timeout times , Default 3 Time .
4. Support maximum reconnection time , For rereading offline devices .
5. Controller information , Can add controller name , Select the controller address 、 Controller model , Set the number of detectors under the controller .
6. Detector information , Can add tag number 、 Detector model 、 Gas type 、 Gas symbol 、 High reported value 、 Under reported value 、 Buffer value 、 Zero value 、 Is it enabled? 、 Alarm sound 、 Background map 、 Storage cycle 、 The number of decimal places for numerical conversion 、 Alarm delay time 、 Type of alarm （HH,LL,HL） etc. .
7. Type management configurable controller model 、 Detector model 、 Gas type 、 Gas symbols, etc .
8. Map supports importing and deleting , The positions of all detectors on the map can be dragged and saved freely .
9. Port information 、 Controller information 、 Detector information 、 Type information 、 User information, etc , Both support import 、 export 、 Export to excel、 Print .
10. Operation record 、 Alarm records 、 Operation record , Both support multi condition combined query , For example, time period 、 controller 、 Detector, etc , All records support export to excel/pdf And print .
11. Operation record 、 Alarm records 、 All operation records can delete data within a specified time range .
12. The system can select the maximum number of records saved in the corresponding table , Automatically clean up early data , Leave enough space for important data .
13. Alarm SMS forwarding , Support multiple receiving mobile numbers , The sending interval can be set , Like instant delivery or 6 Send all alarm messages once an hour , The message is too long , Automatically split multiple messages .
14. Alarm mail forwarding , Support multiple receiving mailboxes , The sending interval can be set , Like instant delivery or 6 Send all alarm messages once an hour , Support attachment sending .
15. Set the Chinese title of the software 、 English title 、logo route 、 All rights reserved .
16. The switch is set for startup and operation 、 Alarm sound 、 automatic logon 、 Remember the password, etc .
17. Alarm sound can be set to play times , Interface style provides 18 Set skin file selection .
18. User management , Including user permission configuration , Different users can have different module permissions .
19. User login and user logout , Can remember password and auto login , More than three error prompts and close the program .
20. Four monitoring modes , Equipment panel monitoring 、 Map monitoring 、 Form data monitoring 、 Curve data monitoring , Free to switch , The collected data is displayed in real time in all four modes , Alarm flashing, etc .
21. Alarm relay linkage , A tag number can link multiple modules and relay numbers across serial ports , Support many to many .

2.3 Special function

1. Communication protocol supports modbus_com、modbus_tcp_rtu, Later expansion mqtt Such agreement .
2. The data source is in addition to the real hardware device collection , Optional database acquisition , In this way, users can arrange other programmers, such as java The programmer puts the data collected by the front end into the database , The system can collect data directly from the database . Database collection mode can be used as a general system , More suitable for multi person and multi system cooperation .
3. Smart skip timeout device , Speed up the acquisition of online devices , Especially useful when there are a large number of devices .
4. For smart skip timeout devices , Automatically collect once at the set reconnection time , In order to detect whether the equipment is online again .
5. Each detector can be controlled whether it is enabled , If it is not enabled, it will not collect , It will not be displayed on the interface , It is equivalent to temporary shutdown in the operation phase .
6. The detector can set the buffer value and alarm delay time , An alarm generated by fluctuations near this value , Not included in the alarm , Only when it is continuously at the alarm value and exceeds the alarm delay time can it be considered as a real alarm , This can avoid many false positives caused by fluctuations .
7. The detector can set the storage cycle , Store an operation record according to the set time , The storage cycle can be shorter for those with high importance according to the degree of importance , The unimportant setting is bigger , This can save a lot of storage space , It also ensures the timely storage of important data .
8. The detector can set the zero clearing value , When some high-precision and sensitive equipment may leave the factory, the default value may not be 0, The reset value needs to be set to represent the initial value .
9. The detector can set the decimal point , It is used to control the display of decimal places for the calculated real data , Equivalent to divided by 10、 Divide 100、 Divide 1000, In this way, most of the detector data directly control the real converted value through the decimal point setting , If special conversion is required, it can be agreed in the communication protocol .
10. The type of detector alarm supports multiple types , Some devices are higher than a certain value and report high , Under reporting below a certain value , And some equipment is in the range of minimum and maximum value, which is high alarm , Under reporting below the minimum value , Above maximum normal . In this way, it can be handled according to the situation , Covers various alarm types .
11. Original data import 、 export 、 Printer system , Cross platform does not rely on any components , Export data instantaneously .
12. Export to excel The records support all excel、wps Wait for the form file version , Do not rely on excel Such as software .
13. High color 、 Low color 、 Normal color 、 Default values: color, etc , Can be set freely .
14. Support cloud data synchronization , Synchronize the data collected locally to the cloud in real time .
15. Support network forwarding and network receiving , When network reception is turned on , Software from udp Receive data for parsing . Network forwarding supports multiple targets IP, In this way, the local acquisition software is realized , Free to transfer data to client , Check the collected data at any time .
16. Automatically remember the last user interface and other configuration information , Automatic application after restart .
17. The alarm automatically switches to the corresponding map , The detector button flashes , The table data is displayed in the corresponding color .
18. Double click the probe Icon , Pop up the corresponding detector details , The back control operation can be customized as required .
19. The database supports a variety of , Include sqlite、mysql、sqlserver、postgresql、oracle、 People's Congress, Jincang, etc .
20. The data collected by the local device is uploaded to the cloud in real time , So that cell phones APP perhaps web And so on .
21. Built in device simulation tools , Support data simulation of multiple devices of different models , At the same time, it also has database data simulation , In order to test the data when there is no equipment .
22. standard modbus agreement , Various controller types 、 Detector type 、 species 、 All custom symbols, etc , Very flexible and powerful , The communication protocol sample data is very complete , General various modbus Protocol system , It is applicable to various application scenarios .
23. At the same time, it integrates serial communication 、 Network communication 、 Database communication 、 Data import / export printing 、 Communication protocol analysis 、 Interface UI、 Many components and knowledge points such as global skin change , Very suitable for beginners and advanced .
24. Support xp、win7、win10、、win11、linux、mac、 All kinds of domestic systems （UOS、 Winning Qilin 、 Galaxy unicorn, etc ）、 The embedded linux Such as system .
25. Note complete , The project structure is clear , Super detailed complete use of the development manual , Accurate to the function description of each code file , Keep iterating over versions .

3、 ... and 、 Experience address

1. Domestic site ：https://gitee.com/feiyangqingyun
2. International sites ：https://github.com/feiyangqingyun
3. Personal home page ：https://blog.csdn.net/feiyangqingyun
4. Zhihu Homepage ：https://www.zhihu.com/people/feiyangqingyun
5. The product page ：https://blog.csdn.net/feiyangqingyun/article/details/97565652
6. Online document ：https://feiyangqingyun.gitee.io/qwidgetdemo/iotsystem/
7. Experience address ：https://pan.baidu.com/s/1ZxG-oyUKe286LPMPxOrO2A Extraction code ：o05q file name ：bin_iotsystem.zip.
8. Article navigation ：https://qtchina.blog.csdn.net/article/details/121330922

Four 、 design sketch

5、 ... and 、 Related codes

void frmConfigDb::on_btnConnect_clicked()
{
    
    {
    
        // Initialize database connection information structure data 
        DbInfo dbInfo;
        initDbInfo(dbInfo, connName);

        QString dbType = AppConfig::LocalDbType.toUpper();
        if (dbType == "SQLITE") {
    
            dbInfo.dbName = DbHelper::getDbDefaultFile(connFlag);
            if (QFile(dbInfo.dbName).size() <= 4) {
    
                QUIHelper::showMessageBoxError(" Database file does not exist !", 5);
                return;
            }
        }

        QSqlDatabase database;
        if (DbHelper::initDatabase(true, dbType, database, dbInfo)) {
    
            if (database.open()) {
    
                database.close();
                QUIHelper::showMessageBoxInfo(" Open database successfully !", 3);
            } else {
    
                QString error = database.lastError().text();
                QUIHelper::showMessageBoxError(" Failed to open database !\n" + error, 3);
            }
        } else {
    
            QString error = database.lastError().text();
            QUIHelper::showMessageBoxError(" Failed to connect to database !\n" + error, 3);
        }
    }

    QSqlDatabase::removeDatabase(connName);
}

void frmConfigDb::on_btnInit_clicked()
{
    
    if (QUIHelper::showMessageBoxQuestion(" Are you sure you want to initialize the database ?  All data will be cleared and cannot be restored !") != QMessageBox::Yes) {
    
        return;
    }

    QString sqlName = QString("%1/db/%2.sql").arg(QUIHelper::appPath()).arg(connFlag);
    QFile file(sqlName);
    if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) {
    
        QUIHelper::showMessageBoxError(" Database script file open failed !", 3);
        return;
    }

    QElapsedTimer time;
    time.start();
    {
    
        // Initialize database connection information structure data 
        DbInfo dbInfo;
        initDbInfo(dbInfo, connName);

        QString dbType = AppConfig::LocalDbType.toUpper();
        if (dbType == "SQLITE") {
    
            dbInfo.dbName = DbHelper::getDbDefaultFile(connFlag);
            // If the file exists, delete the original database file first , Looks like win Not on 
            QFile file(dbInfo.dbName);
            if (file.exists()) {
    
                bool ok = file.remove();
                if (!ok) {
    
                    qDebug() << TIMEMS << "remove error" << dbInfo.dbName;
                }

                // Empty all tables 
                QStringList tables = QSqlDatabase::database().tables();
                foreach (QString table, tables) {
    
                    DbHelper::clearTable(table, dbType);
                    qDebug() << TIMEMS << "clearTable" << table;
                }

                // Close the default database connection 
                QSqlDatabase::database().close();
            }
        }

        // Initialize the database connection and open the database 
        QSqlDatabase database;
        if (!DbHelper::initDatabase(true, dbType, database, dbInfo)) {
    
            QString error = database.lastError().text();
            QUIHelper::showMessageBoxError(" Failed to connect to database !\n" + error, 3);
            return;
        }
        if (!database.open()) {
    
            QString error = database.lastError().text();
            QUIHelper::showMessageBoxError(" Failed to open database !\n" + error, 3);
            return;
        }

        QSqlQuery query(QSqlDatabase::database(connName));

        // First step : Delete the original database 
        QString sql = QString("DROP DATABASE %1;").arg(dbInfo.dbName);
        qDebug() << TIMEMS << "sql:" << sql << "result:" << query.exec(sql);

        // The second step : New database 
        sql = QString("CREATE DATABASE %1;").arg(dbInfo.dbName);
        qDebug() << TIMEMS << "sql:" << sql << "result:" << query.exec(sql);

        // The third step : Switch to the newly created database and execute the CREATE TABLE statement 
        database.close();
        if (!DbHelper::initDatabase(false, dbType, database, dbInfo)) {
    
            QString error = database.lastError().text();
            QUIHelper::showMessageBoxError(" Failed to connect to database !\n" + error, 3);
            return;
        }
        if (!database.open()) {
    
            QString error = database.lastError().text();
            QUIHelper::showMessageBoxError(" Failed to open database !\n" + error, 3);
            return;
        }

        // An error will be executed sql Statement is output to a file for easy viewing 
        QString fileName2 = QString("%1/db/error.sql").arg(QUIHelper::appPath());
        QFile file2(fileName2);

        QSqlQuery query2(QSqlDatabase::database(connName));

        sql = "BEGIN;";
        qDebug() << TIMEMS << "sql:" << sql << "result:" << query2.exec(sql);

        while (!file.atEnd()) {
    
            sql = QString::fromUtf8(file.readLine());
            sql.replace("\n", "");

            // Some statements that are not supported by the database jump over 
            if (DbHelper::existNoSupportSql(sql)) {
    
                continue;
            }

            // Re correct sql sentence 
            DbHelper::checkSql(dbType, sql);

            if (!query2.exec(sql)) {
    
                // Print and output error messages 
                QString error = query2.lastError().text();
                qDebug() << TIMEMS << "sql:" << sql << error;

                // If you don't open it, open it first 
                if (!file2.isOpen()) {
    
                    file2.open(QFile::WriteOnly | QFile::Append);
                }

                QString msg = QString(" Time [%1]  sentence : %2  error : %3\n").arg(DATETIME).arg(sql).arg(error);
                file2.write(msg.toUtf8());
            }
        }

        sql = "COMMIT;";
        qDebug() << TIMEMS << "sql:" << sql << "result:" << query2.exec(sql);
        database.close();

        //sqlite If the database is compressed, reduce the volume 
        if (dbType == "SQLITE") {
    
            DbHelper::execSql("VACUUM;");
        }
    }

    QSqlDatabase::removeDatabase(connName);
    double ms = time.elapsed();
    QString info = QString(" Database script executed successfully ,  Total time  %1  second !\n Remember to restart the program !").arg(QString::number(ms / 1000, 'f', 1));
    QUIHelper::showMessageBoxInfo(info, 3);
}