mysql.h 4.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163
  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. }config_param;
  73. class config_db_type
  74. {
  75. public:
  76. config_mqtt_db mqtt_db;
  77. config_ftp_db ftp_db;
  78. config_location_db location_db;
  79. data_json_db json_db;
  80. config_camera camera_db;
  81. config_param config_db;
  82. };
  83. //int mode;
  84. //QString lbs_lat;
  85. //QString lbs_lng;
  86. //QString gps_lat;
  87. //QString gps_lng;
  88. //QString gps_elevation;
  89. //QJsonArray param::mqtt_array;
  90. class SqliteOperator : public QObject
  91. {
  92. Q_OBJECT
  93. public:
  94. SqliteOperator(QString &dbname); //创建数据库
  95. //创建数据库
  96. bool OpenDb(); //打开数据库
  97. bool isTableExist(QString& tableName); //判断数据表是否存在
  98. int CloseDb(); //关闭数据库
  99. int CreateTable(QString& tableName); //创建数据表
  100. int InsertData(config_db_type &singledb,QString tablename); //插入数据
  101. void InertListConfig(QList<config_db_type> &moreData,QString tablename);
  102. int UpdateData(QString tablename, QString name, QString value); //更新、修改数据
  103. int QueryAllData(QString& table_name); //遍历查询
  104. int QueryData(); //条件查询
  105. void deleteData(int id); //删除数据
  106. void deleteTable(QString& tableName); //删除数据表
  107. private:
  108. QSqlDatabase db; // 用于建立和数据库的连接
  109. };
  110. bool InsertJson_mqtt();
  111. //旧版本数据库缺少新加的列时补列(平滑升级用):已存在则直接返回true
  112. //columnDef: 形如 "fusion_enable INT NOT NULL DEFAULT 1"
  113. bool ensureColumnExist(const QString &table, const QString &column, const QString &columnDef);
  114. //确保 config 表至少有一行默认配置(表被清空时兜底补行)
  115. bool ensureConfigRow();
  116. //条件查询与遍历查询的对比
  117. //加循环建了一个包含229300条数据,7448kb的数据库。
  118. //条件查询耗时:30ms
  119. //遍历查询耗时:100ms
  120. //随着数据量的增加,差距应该会更明显。
  121. //qml接口
  122. class DatabaseInterface : public QObject
  123. {
  124. Q_OBJECT
  125. public:
  126. explicit DatabaseInterface(QObject *parent = nullptr);
  127. Q_INVOKABLE bool openDatabase(const QString& dbName);
  128. Q_INVOKABLE bool closeDatabase();
  129. Q_INVOKABLE QString getTable_row(QString tablename);
  130. Q_INVOKABLE bool createTable(QString tableName);
  131. Q_INVOKABLE bool insertData(const QString& name, const QString& address);
  132. Q_INVOKABLE bool deleteData(int id);
  133. Q_INVOKABLE bool updateData(QString tablename,QString name,QVariant value);
  134. Q_INVOKABLE QJsonArray queryData(QString table_name,int count_index);
  135. private:
  136. // 惰性绑定到启动时 SqliteOperator 打开的 config.db 默认连接(qt_sql_default_connection)
  137. QSqlDatabase ensureDb();
  138. QSqlDatabase m_database;
  139. };
  140. #endif // MYSQL_H