mysql.h 4.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168
  1. #ifndef MYSQL_H
  2. #define MYSQL_H
  3. //SQL
  4. #include <QSqlDatabase>
  5. #include <QSqlError>
  6. #include <QSqlQuery>
  7. #include <main.h>
  8. #include <m_loger.h>
  9. typedef struct
  10. {
  11. int id;
  12. QString dtype;
  13. QString ip;
  14. int port;
  15. QString imei;
  16. QString uid;
  17. QString pwd;
  18. QString clientid;
  19. int qos;
  20. int keepalive;
  21. QString sub;
  22. QString pub;
  23. QString offline;
  24. int dat_f;
  25. }config_mqtt_db;
  26. typedef struct
  27. {
  28. int id;
  29. QString ip;
  30. int port;
  31. QString dir;
  32. QString uid;
  33. QString pwd;
  34. int mode;
  35. }config_ftp_db;
  36. typedef struct
  37. {
  38. int id;
  39. QString lbs_lat;
  40. QString lbs_lng;
  41. QString gps_lat;
  42. QString gps_lng;
  43. QString gps_elevation;
  44. }config_location_db;
  45. typedef struct
  46. {
  47. int id;
  48. QString datetime;
  49. QString Json_data;
  50. QString Json_warn;
  51. }data_json_db;
  52. typedef struct{
  53. QString camera_type;
  54. QString mode;
  55. int stitch_photonum;
  56. int photo_width;
  57. int photo_height;
  58. QString auto_focus;
  59. QString movz_f;
  60. QString movz_b;
  61. QString movx_f;
  62. QString movx_b;
  63. }config_camera;
  64. typedef struct{
  65. int jsonCacheEnable;
  66. int imageCacheEnable;;
  67. int maxJsonEntries;
  68. int maxImageEntries;
  69. int x_init;
  70. int y_init;
  71. int fusion_enable; //景深融合开关 1=开 0=关(MQTT fusion_sw 下发)
  72. int focus_algo; //对焦/融合算法 0=SLView GFF 1=SLView MS 2=旧算法(MQTT focus_algo 下发)
  73. }config_param;
  74. class config_db_type
  75. {
  76. public:
  77. config_mqtt_db mqtt_db;
  78. config_ftp_db ftp_db;
  79. config_location_db location_db;
  80. data_json_db json_db;
  81. config_camera camera_db;
  82. config_param config_db;
  83. };
  84. //int mode;
  85. //QString lbs_lat;
  86. //QString lbs_lng;
  87. //QString gps_lat;
  88. //QString gps_lng;
  89. //QString gps_elevation;
  90. //QJsonArray param::mqtt_array;
  91. class SqliteOperator : public QObject
  92. {
  93. Q_OBJECT
  94. public:
  95. SqliteOperator(QString &dbname); //创建数据库
  96. //创建数据库
  97. bool OpenDb(); //打开数据库
  98. bool isTableExist(QString& tableName); //判断数据表是否存在
  99. int CloseDb(); //关闭数据库
  100. int CreateTable(QString& tableName); //创建数据表
  101. int InsertData(config_db_type &singledb,QString tablename); //插入数据
  102. void InertListConfig(QList<config_db_type> &moreData,QString tablename);
  103. int UpdateData(QString tablename, QString name, QString value); //更新、修改数据
  104. int QueryAllData(QString& table_name); //遍历查询
  105. int QueryData(); //条件查询
  106. void deleteData(int id); //删除数据
  107. void deleteTable(QString& tableName); //删除数据表
  108. private:
  109. QSqlDatabase db; // 用于建立和数据库的连接
  110. };
  111. bool InsertJson_mqtt();
  112. //旧版本数据库缺少新加的列时补列(平滑升级用):已存在则直接返回true
  113. //columnDef: 形如 "fusion_enable INT NOT NULL DEFAULT 1"
  114. bool ensureColumnExist(const QString &table, const QString &column, const QString &columnDef);
  115. //确保 config 表至少有一行默认配置(表被清空时兜底补行)
  116. bool ensureConfigRow();
  117. //条件查询与遍历查询的对比
  118. //加循环建了一个包含229300条数据,7448kb的数据库。
  119. //条件查询耗时:30ms
  120. //遍历查询耗时:100ms
  121. //随着数据量的增加,差距应该会更明显。
  122. //qml接口
  123. class DatabaseInterface : public QObject
  124. {
  125. Q_OBJECT
  126. public:
  127. explicit DatabaseInterface(QObject *parent = nullptr);
  128. Q_INVOKABLE bool openDatabase(const QString& dbName);
  129. Q_INVOKABLE bool closeDatabase();
  130. Q_INVOKABLE QString getTable_row(QString tablename);
  131. Q_INVOKABLE bool createTable(QString tableName);
  132. Q_INVOKABLE bool insertData(const QString& name, const QString& address);
  133. Q_INVOKABLE bool deleteData(int id);
  134. Q_INVOKABLE bool updateData(QString tablename,QString name,QVariant value);
  135. Q_INVOKABLE QJsonArray queryData(QString table_name,int count_index);
  136. //确保配置库(config.db)的默认连接已建立并打开(幂等)。
  137. //启动流程/串口参数同步/GPS 等都会在结束时 CloseDb()(removeDatabase),
  138. //连接被移除后写库会直接失败(MQTT 下发配置响应 "db save failed")
  139. bool ensureConfigDbOpen();
  140. private:
  141. // 惰性绑定到启动时 SqliteOperator 打开的 config.db 默认连接(qt_sql_default_connection)
  142. QSqlDatabase ensureDb();
  143. QSqlDatabase m_database;
  144. };
  145. #endif // MYSQL_H