| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168 |
- #ifndef MYSQL_H
- #define MYSQL_H
- //SQL
- #include <QSqlDatabase>
- #include <QSqlError>
- #include <QSqlQuery>
- #include <main.h>
- #include <m_loger.h>
- typedef struct
- {
- int id;
- QString dtype;
- QString ip;
- int port;
- QString imei;
- QString uid;
- QString pwd;
- QString clientid;
- int qos;
- int keepalive;
- QString sub;
- QString pub;
- QString offline;
- int dat_f;
- }config_mqtt_db;
- typedef struct
- {
- int id;
- QString ip;
- int port;
- QString dir;
- QString uid;
- QString pwd;
- int mode;
- }config_ftp_db;
- typedef struct
- {
- int id;
- QString lbs_lat;
- QString lbs_lng;
- QString gps_lat;
- QString gps_lng;
- QString gps_elevation;
- }config_location_db;
- typedef struct
- {
- int id;
- QString datetime;
- QString Json_data;
- QString Json_warn;
- }data_json_db;
- typedef struct{
- QString camera_type;
- QString mode;
- int stitch_photonum;
- int photo_width;
- int photo_height;
- QString auto_focus;
- QString movz_f;
- QString movz_b;
- QString movx_f;
- QString movx_b;
- }config_camera;
- typedef struct{
- int jsonCacheEnable;
- int imageCacheEnable;;
- int maxJsonEntries;
- int maxImageEntries;
- int x_init;
- int y_init;
- int fusion_enable; //景深融合开关 1=开 0=关(MQTT fusion_sw 下发)
- int focus_algo; //对焦/融合算法 0=SLView GFF 1=SLView MS 2=旧算法(MQTT focus_algo 下发)
- }config_param;
- class config_db_type
- {
- public:
- config_mqtt_db mqtt_db;
- config_ftp_db ftp_db;
- config_location_db location_db;
- data_json_db json_db;
- config_camera camera_db;
- config_param config_db;
- };
- //int mode;
- //QString lbs_lat;
- //QString lbs_lng;
- //QString gps_lat;
- //QString gps_lng;
- //QString gps_elevation;
- //QJsonArray param::mqtt_array;
- class SqliteOperator : public QObject
- {
- Q_OBJECT
- public:
- SqliteOperator(QString &dbname); //创建数据库
- //创建数据库
- bool OpenDb(); //打开数据库
- bool isTableExist(QString& tableName); //判断数据表是否存在
- int CloseDb(); //关闭数据库
- int CreateTable(QString& tableName); //创建数据表
- int InsertData(config_db_type &singledb,QString tablename); //插入数据
- void InertListConfig(QList<config_db_type> &moreData,QString tablename);
- int UpdateData(QString tablename, QString name, QString value); //更新、修改数据
- int QueryAllData(QString& table_name); //遍历查询
- int QueryData(); //条件查询
- void deleteData(int id); //删除数据
- void deleteTable(QString& tableName); //删除数据表
- private:
- QSqlDatabase db; // 用于建立和数据库的连接
- };
- bool InsertJson_mqtt();
- //旧版本数据库缺少新加的列时补列(平滑升级用):已存在则直接返回true
- //columnDef: 形如 "fusion_enable INT NOT NULL DEFAULT 1"
- bool ensureColumnExist(const QString &table, const QString &column, const QString &columnDef);
- //确保 config 表至少有一行默认配置(表被清空时兜底补行)
- bool ensureConfigRow();
- //条件查询与遍历查询的对比
- //加循环建了一个包含229300条数据,7448kb的数据库。
- //条件查询耗时:30ms
- //遍历查询耗时:100ms
- //随着数据量的增加,差距应该会更明显。
- //qml接口
- class DatabaseInterface : public QObject
- {
- Q_OBJECT
- public:
- explicit DatabaseInterface(QObject *parent = nullptr);
- Q_INVOKABLE bool openDatabase(const QString& dbName);
- Q_INVOKABLE bool closeDatabase();
- Q_INVOKABLE QString getTable_row(QString tablename);
- Q_INVOKABLE bool createTable(QString tableName);
- Q_INVOKABLE bool insertData(const QString& name, const QString& address);
- Q_INVOKABLE bool deleteData(int id);
- Q_INVOKABLE bool updateData(QString tablename,QString name,QVariant value);
- Q_INVOKABLE QJsonArray queryData(QString table_name,int count_index);
- //确保配置库(config.db)的默认连接已建立并打开(幂等)。
- //启动流程/串口参数同步/GPS 等都会在结束时 CloseDb()(removeDatabase),
- //连接被移除后写库会直接失败(MQTT 下发配置响应 "db save failed")
- bool ensureConfigDbOpen();
- private:
- // 惰性绑定到启动时 SqliteOperator 打开的 config.db 默认连接(qt_sql_default_connection)
- QSqlDatabase ensureDb();
- QSqlDatabase m_database;
- };
- #endif // MYSQL_H
|