Qt编写物联网管理平台47-通用数据库设置

一、前言

为了做这个通用的数据库组件，专门安装了虚拟机来安装各种版本的不同类型的数据库做测试，包括编译对应的数据库插件，我一直坚信的是一切从实际出发+有实际采用发言权，包括不同Qt版本编译mysql、oracle数据库插件，以及不同数据库在查询数据、分页显示数据、清空数据、批量插入数据、批量删除数据等操作，经历过至少半个月的疯狂实战，光mysql就安装了5.7和8.0版本，postgresql安装了9.0、10.0、13.0三个版本，oracle安装了11.0，国产数据库安装了kingbaseV8，反复将物联网管理平台、视频监控系统、可视化大屏电子看板系统，在各种数据库下进行测试，优化了大大小小上百个细节，而且同时支持直连数据库和ODBC数据源方式连接数据库两种形式，比如通过oci库搭配qoci插件连接oracle数据库、通过odbc数据源搭配qodbc插件连接oracle数据库，才形成现在的版本。

通用的数据库组件基本功能：

1. 探测数据库连接是否正常，一般是在不指定数据库名称的前提下探测，因为此时数据库并不存在，需要连接后再去新建。
2. 执行sql脚本初始化数据库，包括新建数据库、新建数据库表、初始化记录等。
3. 通用数据库连接和管理类，负责数据库的打开、关闭、执行sql语句、批量执行、自动重连、实时检测连接状态等。
4. 通用数据库记录清理类，负责清理指定数据库表的记录。
5. 通用数据库记录翻页类，不同数据库类型对应不同的查询翻页显示记录语句。
6. 绑定数据库表到数据模型QSqlTableModel。
7. 获取查询记录数语句，不同数据库类型不同sql语句。
8. 查询数据库所有表名、所有字段名，不同数据库类型不同sql语句。
9. 通用日期范围sql语句。

二、功能特点

2.1 软件模块

1. 设备监控模块，包括数据监控（表格形式展示）、设备面板（面板形式展示）、地图监控（地图形式展示）、曲线监控（曲线形式展示）。
2. 数据查询模块，包括报警记录、运行记录、操作记录。
3. 系统设置模块，包括基本设置、端口管理、控制器管理、探测器管理、报警联动、类型设置等。
4. 其他设置模块，包括用户管理、地图管理、位置调整、组态设计、设备调试等。

2.2 基础功能

1. 设备数据采集，支持串口、网络，串口可设置串口号、波特率，网络可设置IP地址、通讯端口。
2. 每个端口支持采集周期时间，默认1秒钟一个设备。
3. 支持设置通讯超时次数，默认3次。
4. 支持最大重连时间，用于重新读取离线的设备。
5. 控制器信息，能够添加控制器名称，选择控制器地址、控制器型号，设置该控制器下面的探测器数量。
6. 探测器信息，能够添加位号、探测器型号、气体种类、气体符号、高报值、低报值、缓冲值、清零值、是否启用、报警声音、背景地图、存储周期、数值换算小数点位数、报警延时时间、报警的类型（HH,LL,HL）等。
7. 类型管理可配置控制器型号、探测器型号、气体种类、气体符号等。
8. 地图支持导入和删除，所有的探测器在地图上的位置可自由拖动保存。
9. 端口信息、控制器信息、探测器信息、类型信息、用户信息等，都支持导入、导出、导出到excel、打印。
10. 运行记录、报警记录、操作记录，都支持多条件组合查询，比如时间段、控制器、探测器等，所有记录支持导出到excel/pdf和打印。
11. 运行记录、报警记录、操作记录都可删除指定时间范围内的数据。
12. 系统设置可选择对应表最大保存记录数，自动清理早期数据，留出足够的空间存储重要的数据。
13. 报警短信转发，支持多个接收手机号码，可设定发送间隔，比如即时发送或者6个小时发送一次所有的报警信息，短信内容过长，自动拆分多条短信。
14. 报警邮件转发，支持多个接收邮箱，可设定发送间隔，比如即时发送或者6个小时发送一次所有的报警信息，支持附件发送。
15. 设置软件的中文标题、英文标题、logo路径、版权所有等。
16. 开关设置开机运行、报警声音、自动登录、记住密码等。
17. 报警声音可设置播放次数，界面风格样式提供18套皮肤文件选择。
18. 用户管理，包括用户权限配置，不同用户可以有不同模块的权限。
19. 用户登录和用户退出，可以记住密码和自动登录，超过三次报错提示并关闭程序。
20. 四种监控模式，设备面板监控、地图监控、表格数据监控、曲线数据监控，可自由切换，四种模式下都实时展示采集到的数据，报警闪烁等。
21. 报警继电器联动，一个位号可以跨串口联动多个模块和继电器号，支持多对多。

2.3 特色功能

1. 通信协议支持modbus_com、modbus_tcp_rtu，后期拓展mqtt等协议。
2. 数据源除了真实的硬件设备采集，还可选数据库采集，这样用户可以安排其他程序员比如java程序员将前端采集好的数据放到数据库，本系统直接从数据库采集即可。数据库采集模式可以作为通用的系统使用，更适合多人多系统协作。
3. 智能跳过超时的设备，加快对在线设备的采集速度，当设备数量很多的时候尤其有用。
4. 对智能跳过的超时的设备，在设定的重连时间自动采集一次，以便探测设备是否又重新上线。
5. 每个探测器可控是否启用，不启用则不会采集，也不会在界面显示，相当于运行阶段临时关闭。
6. 探测器可设置缓冲值和报警延时时间，在该值附近波动产生的报警，不计入报警，只有持续处于报警值且超过报警延时时间才算真正报警，这样可以规避很多波动导致的误报。
7. 探测器可设置存储周期，按照设定的时间来存储一条运行记录，可以按照重要程度对重要性高的设定存储周期短一些，不重要的设定大一些，这样可以节省不少的存储空间，也保证了重要的数据及时存储。
8. 探测器可设置清零值，在一些高精度高灵敏的设备可能出厂的时候默认值未必是0，需要设定清零值来表示初始值。
9. 探测器可设置小数点，用于计算后的真实数据控制小数点点位显示，相当于除以10、除以100、除以1000，这样大部分的探测器数据直接通过小数点位设置控制真实换算后的值，极个别的需要特殊转换的可以在通信协议中约定。
10. 探测器报警的类型支持多种，有些设备是高于某个值高报，低于某个值低报，而有些设备是在最小值最大值范围内是高报，低于最小值低报，高于最大值正常。这样可以分情况处理，涵盖各种报警类型。
11. 原创数据导入、导出、打印机制，跨平台不依赖任何组件，瞬间导出数据。
12. 导出到excel的记录支持所有excel、wps等表格文件版本，不依赖excel等软件。
13. 高报颜色、低报颜色、正常颜色、默认值颜色等，都可以自由设置。
14. 支持云端数据同步，将本地采集到的数据实时同步到云端。
15. 支持网络转发和网络接收，网络接收开启后，软件从udp接收数据进行解析。网络转发支持多个目标IP，这样就实现了本地采集的软件，自由将数据转到客户端，随时查看采集到的数据。
16. 自动记住用户最后停留的界面以及其他配置信息，重启后自动应用。
17. 报警自动切换到对应的地图，探测器按钮闪烁，表格数据对应颜色显示。
18. 双击探测器图标，弹出对应探测器详细信息，可以根据需要定制回控操作。
19. 数据库支持多种，包括sqlite、mysql、sqlserver、postgresql、oracle、人大金仓等。
20. 本地设备采集到的数据实时上传到云端，以便手机APP或者web等其他方式提取。
21. 自带设备模拟工具，支持不同型号的多个设备数据模拟，同时还带数据库数据模拟，以便在没有设备的时候测试数据。
22. 标准modbus协议，各种控制器类型、探测器类型、种类、符号等全部自定义，非常灵活和强大，通信协议示例数据非常完整，通用各种modbus协议系统，适用于各种应用场景接入。
23. 同时集成了串口通信、网络通信、数据库通信、数据导入导出打印、通信协议解析、界面UI、全局换肤等众多组件和知识点，非常适合新手入门和进阶。
24. 支持xp、win7、win10、、win11、linux、mac、各种国产系统（UOS、中标麒麟、银河麒麟等）、嵌入式linux等系统。
25. 注释完整，项目结构清晰，超级详细完整的使用开发手册，精确到每个代码文件的功能说明，不断持续迭代版本。

三、体验地址

1. 国内站点：https://gitee.com/feiyangqingyun
2. 国际站点：https://github.com/feiyangqingyun
3. 个人主页：https://blog.csdn.net/feiyangqingyun
4. 知乎主页：https://www.zhihu.com/people/feiyangqingyun
5. 产品主页：https://blog.csdn.net/feiyangqingyun/article/details/97565652
6. 在线文档：https://feiyangqingyun.gitee.io/qwidgetdemo/iotsystem/
7. 体验地址：https://pan.baidu.com/s/1ZxG-oyUKe286LPMPxOrO2A 提取码：o05q 文件名：bin_iotsystem.zip。
8. 文章导航：https://qtchina.blog.csdn.net/article/details/121330922

四、效果图

五、相关代码

void frmConfigDb::on_btnConnect_clicked()
{
    {
        //初始化数据库连接信息结构体数据
        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("数据库文件不存在!", 5);
                return;
            }
        }

        QSqlDatabase database;
        if (DbHelper::initDatabase(true, dbType, database, dbInfo)) {
            if (database.open()) {
                database.close();
                QUIHelper::showMessageBoxInfo("打开数据库成功!", 3);
            } else {
                QString error = database.lastError().text();
                QUIHelper::showMessageBoxError("打开数据库失败!\n" + error, 3);
            }
        } else {
            QString error = database.lastError().text();
            QUIHelper::showMessageBoxError("连接数据库失败!\n" + error, 3);
        }
    }

    QSqlDatabase::removeDatabase(connName);
}

void frmConfigDb::on_btnInit_clicked()
{
    if (QUIHelper::showMessageBoxQuestion("确定要初始化数据库吗? 会全部清空数据并且不可还原!") != 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("数据库脚本文件打开失败!", 3);
        return;
    }

    QElapsedTimer time;
    time.start();
    {
        //初始化数据库连接信息结构体数据
        DbInfo dbInfo;
        initDbInfo(dbInfo, connName);

        QString dbType = AppConfig::LocalDbType.toUpper();
        if (dbType == "SQLITE") {
            dbInfo.dbName = DbHelper::getDbDefaultFile(connFlag);
            //如果文件存在则先删除原来的数据库文件,貌似win上不行
            QFile file(dbInfo.dbName);
            if (file.exists()) {
                bool ok = file.remove();
                if (!ok) {
                    qDebug() << TIMEMS << "remove error" << dbInfo.dbName;
                }

                //清空所有表
                QStringList tables = QSqlDatabase::database().tables();
                foreach (QString table, tables) {
                    DbHelper::clearTable(table, dbType);
                    qDebug() << TIMEMS << "clearTable" << table;
                }

                //关闭默认数据库连接
                QSqlDatabase::database().close();
            }
        }

        //初始化数据库连接并打开数据库
        QSqlDatabase database;
        if (!DbHelper::initDatabase(true, dbType, database, dbInfo)) {
            QString error = database.lastError().text();
            QUIHelper::showMessageBoxError("连接数据库失败!\n" + error, 3);
            return;
        }
        if (!database.open()) {
            QString error = database.lastError().text();
            QUIHelper::showMessageBoxError("打开数据库失败!\n" + error, 3);
            return;
        }

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

        //第一步:删除原有数据库
        QString sql = QString("DROP DATABASE %1;").arg(dbInfo.dbName);
        qDebug() << TIMEMS << "sql:" << sql << "result:" << query.exec(sql);

        //第二步:新建数据库
        sql = QString("CREATE DATABASE %1;").arg(dbInfo.dbName);
        qDebug() << TIMEMS << "sql:" << sql << "result:" << query.exec(sql);

        //第三步:切换到新建的数据库库并执行建表语句
        database.close();
        if (!DbHelper::initDatabase(false, dbType, database, dbInfo)) {
            QString error = database.lastError().text();
            QUIHelper::showMessageBoxError("连接数据库失败!\n" + error, 3);
            return;
        }
        if (!database.open()) {
            QString error = database.lastError().text();
            QUIHelper::showMessageBoxError("打开数据库失败!\n" + error, 3);
            return;
        }

        //将执行出错的sql语句输出到文件方便查看
        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", "");

            //有些数据库不支持的语句跳过去
            if (DbHelper::existNoSupportSql(sql)) {
                continue;
            }

            //重新纠正sql语句
            DbHelper::checkSql(dbType, sql);

            if (!query2.exec(sql)) {
                //打印及输出错误信息
                QString error = query2.lastError().text();
                qDebug() << TIMEMS << "sql:" << sql << error;

                //没打开则先打开
                if (!file2.isOpen()) {
                    file2.open(QFile::WriteOnly | QFile::Append);
                }

                QString msg = QString("时间[%1]  语句: %2  错误: %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 (dbType == "SQLITE") {
            DbHelper::execSql("VACUUM;");
        }
    }

    QSqlDatabase::removeDatabase(connName);
    double ms = time.elapsed();
    QString info = QString("数据库脚本执行成功, 总共用时 %1 秒!\n记得重新启动程序!").arg(QString::number(ms / 1000, 'f', 1));
    QUIHelper::showMessageBoxInfo(info, 3);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164