mysql.cpp 38 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905
  1. #include <mysql.h>
  2. #include <cameramanager.h>
  3. class param;
  4. QString param::dtype;
  5. QString param::imei;
  6. QString param::iccid;
  7. QString param::csq = "0";
  8. QString param::mqtt_main_ip;
  9. QString param::mqtt_other_ip;
  10. int param::mqtt_main_port;
  11. int param::mqtt_other_port;
  12. int param::mqtt_main_keepalive;
  13. int param::mqtt_other_keepalive;
  14. int param::mqtt_main_qos;
  15. int param::mqtt_other_qos;
  16. QString param::mqtt_main_clientid;
  17. QString param::mqtt_other_clientid;
  18. QString param::mqtt_main_uid;
  19. QString param::mqtt_other_uid;
  20. QString param::mqtt_main_pwd;
  21. QString param::mqtt_other_pwd;
  22. QString param::mqtt_main_sub;
  23. QString param::mqtt_other_sub;
  24. QString param::mqtt_main_pub;
  25. QString param::mqtt_other_pub;
  26. QString param::mqtt_main_offline;
  27. QString param::mqtt_other_offline;
  28. QString param::ftp_main_ip;
  29. QString param::ftp_other_ip;
  30. int param::ftp_main_port;
  31. int param::ftp_other_port;
  32. QString param::ftp_main_dir;
  33. QString param::ftp_other_dir;
  34. QString param::ftp_main_uid;
  35. QString param::ftp_other_uid;
  36. QString param::ftp_main_pwd;
  37. QString param::ftp_other_pwd;
  38. int param::ftp_main_mode;
  39. int param::ftp_other_mode;
  40. int param::dat_f_main;
  41. int param::dat_f_other;
  42. QString param::gps_state;
  43. QString param::lbs_lat;
  44. QString param::lbs_lng;
  45. QString param::gps_lat;
  46. QString param::gps_lng;
  47. QString param::gps_elevation;
  48. QString param::camera_type;
  49. QString param::stitch_mode;
  50. int param::stitch_photonum;
  51. int param::photo_height;
  52. int param::photo_width;
  53. QString param::auto_focus;
  54. QString param::movZ_f_l;
  55. QString param::movZ_b_l;
  56. QString param::movX_f_l;
  57. QString param::movX_b_l;
  58. int param::camera_tc = 0;
  59. QStringList param::ptlist;
  60. QStringList param::ptlist_res;
  61. int param::jsonCacheEnable;
  62. int param::imageCacheEnable;
  63. int param::maxJsonEntries;
  64. int param::maxImageEntries;
  65. int param::x_init;
  66. int param::y_init;
  67. bool param::net_alive;
  68. QString param::net_name;
  69. QString param::net_description;
  70. QString param::net_mac;
  71. QString param::net_ipv4;
  72. int param::datupload_remain;
  73. SqliteOperator::SqliteOperator(QString &dbname)
  74. {
  75. LOG("实例化:SqliteOperator:{}",dbname.toStdString());
  76. if(QSqlDatabase::contains("qt_sql_default_connection"))
  77. {
  78. db = QSqlDatabase::database("qt_sql_default_connection");
  79. }
  80. else
  81. {
  82. #if ANDROID
  83. QString db_Path = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation);
  84. QString db_name = db_Path + "/" + dbname;
  85. #else
  86. QString db_Path = "./";
  87. QString db_name = db_Path + dbname;
  88. #endif
  89. db = QSqlDatabase::addDatabase("QSQLITE","qt_sql_default_connection");
  90. db.setDatabaseName(db_name);
  91. // db.setUserName("admin");
  92. // db.setPassword("yf123456");
  93. }
  94. }
  95. bool InsertJson_mqtt()
  96. {
  97. #if ANDROID
  98. QString db_Path = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation);
  99. QString db_name = db_Path + "/" + "data.db";
  100. #else
  101. QString db_Path = "./";
  102. QString db_name = "./data.db";
  103. #endif
  104. QSqlDatabase mqtt_db;
  105. if(QSqlDatabase::contains("qt_sql_default_connection"))
  106. {
  107. mqtt_db = QSqlDatabase::addDatabase("QSQLITE");
  108. }
  109. QSqlQuery sql_mq;
  110. mqtt_db.setDatabaseName(db_name);
  111. if(!mqtt_db.open())
  112. {
  113. qDebug() << "Error: Failed to connect database." << mqtt_db.lastError();
  114. return false;
  115. }
  116. //判断表是否存在
  117. if(!mqtt_db.tables().contains("data"))
  118. {
  119. sql_mq.prepare(QString("CREATE TABLE data (\
  120. id INT PRIMARY KEY NOT NULL,\
  121. datetime TEXT NOT NULL,\
  122. Json_data TEXT NOT NULL,\
  123. Json_warn TEXT NOT NULL)"));
  124. if(!sql_mq.exec())
  125. {
  126. qDebug() << "Error: Fail to create table." << sql_mq.lastError();
  127. return false;
  128. }
  129. else
  130. {
  131. //qDebug() <<"mqtt data Table created!";
  132. LOG("mqtt data Table created!");
  133. }
  134. }
  135. //获取游标对象
  136. //查询数据数量是否超过设定值
  137. sql_mq.prepare("SELECT COUNT(*) FROM data;");
  138. if(sql_mq.exec()&&sql_mq.next())
  139. {
  140. int row_count = sql_mq.value(0).toInt();
  141. qDebug() << "Number of rows in table: " << row_count;
  142. }
  143. // config_db_type mqtt_class_history;
  144. // QJsonObject json_dat;
  145. // json_dat.insert("vbat","24.01");
  146. // json_dat.insert("imei","123132123");
  147. // for(int dbi = 0;dbi <10000; dbi++)
  148. // {
  149. // QDateTime dateTime= QDateTime::currentDateTime();//获取系统当前的时间
  150. // mqtt_class_history.json_db.id = dbi;
  151. // mqtt_class_history.json_db.datetime = dateTime .toString("yyyy-MM-dd hh:mm:ss");
  152. // mqtt_class_history.json_db.Json_data = QString(QJsonDocument(json_dat).toJson());
  153. // mqtt_class_history.json_db.Json_warn = QString(QJsonDocument(json_dat).toJson());
  154. // sql_mq.prepare(QString("INSERT INTO data VALUES(:id,:datetime,:Json_data,:Json_warn)"));
  155. // sql_mq.bindValue(":id", mqtt_class_history.json_db.id);
  156. // sql_mq.bindValue(":datetime", mqtt_class_history.json_db.datetime);
  157. // sql_mq.bindValue(":Json_data", mqtt_class_history.json_db.Json_data);
  158. // sql_mq.bindValue(":Json_warn", mqtt_class_history.json_db.Json_warn);
  159. // LOG("write json to data db:{}",dbi);
  160. // if(!sql_mq.exec())
  161. // {
  162. // qDebug() <<"Error: Fail to insert data:"<< sql_mq.lastError();
  163. // }
  164. // else
  165. // {
  166. // LOG("InsertJson Success!");
  167. // }
  168. // }
  169. mqtt_db.close();
  170. return true;
  171. }
  172. bool SqliteOperator::OpenDb()
  173. {
  174. QString db_Path = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation);
  175. QString db_name = db_Path + "/" + "config.db";
  176. if(QSqlDatabase::contains("qt_sql_default_connection"))
  177. {
  178. db = QSqlDatabase::database("qt_sql_default_connection");
  179. }else{
  180. db = QSqlDatabase::addDatabase("QSQLITE","qt_sql_default_connection");
  181. db.setDatabaseName(db_name);
  182. }
  183. if(!db.open())
  184. {
  185. qDebug() << "Error: Failed to connect database." << db.lastError();
  186. return false;
  187. }
  188. return true;
  189. }
  190. bool SqliteOperator::isTableExist(QString& tableName)
  191. {
  192. QSqlDatabase database = QSqlDatabase::database();
  193. if(database.tables().contains(tableName))
  194. {
  195. return true;
  196. }
  197. return false;
  198. }
  199. int SqliteOperator::CloseDb()
  200. {
  201. QSqlDatabase::removeDatabase("qt_sql_default_connection");
  202. //db.close();
  203. return 0;
  204. }
  205. int SqliteOperator::CreateTable(QString& tableName)
  206. {
  207. QSqlQuery sql_query;
  208. QString creat_sql;
  209. if(tableName == QString::fromLocal8Bit("mqtt"))
  210. {
  211. creat_sql = QString("CREATE TABLE mqtt (\
  212. id INT PRIMARY KEY NOT NULL,\
  213. dtype TEXT NOT NULL,\
  214. ip TEXT NOT NULL,\
  215. port INT NOT NULL,\
  216. imei TEXT NOT NULL,\
  217. uid TEXT NOT NULL,\
  218. pwd TEXT NOT NULL,\
  219. clientid TEXT,\
  220. qos INT NOT NULL,\
  221. keepalive INT NOT NULL,\
  222. sub TEXT NOT NULL,\
  223. pub TEXT NOT NULL,\
  224. offline TEXT NOT NULL,\
  225. dat_f INT NOT NULL)");
  226. }
  227. if(tableName == QString::fromLocal8Bit("mqtt_other"))
  228. {
  229. creat_sql = QString("CREATE TABLE mqtt_other (\
  230. id INT PRIMARY KEY NOT NULL,\
  231. dtype TEXT NOT NULL,\
  232. ip TEXT NOT NULL,\
  233. port INT NOT NULL,\
  234. imei TEXT NOT NULL,\
  235. uid TEXT NOT NULL,\
  236. pwd TEXT NOT NULL,\
  237. clientid TEXT,\
  238. qos INT NOT NULL,\
  239. keepalive INT NOT NULL,\
  240. sub TEXT NOT NULL,\
  241. pub TEXT NOT NULL,\
  242. offline TEXT NOT NULL,\
  243. dat_f INT NOT NULL)");
  244. }
  245. if(tableName == QString::fromLocal8Bit("ftp"))
  246. {
  247. creat_sql = QString("CREATE TABLE ftp (\
  248. id INT PRIMARY KEY NOT NULL,\
  249. ip TEXT NOT NULL,\
  250. port INT NOT NULL,\
  251. dir TEXT NOT NULL,\
  252. uid TEXT NOT NULL,\
  253. pwd TEXT NOT NULL,\
  254. mode INT NOT NULL)");
  255. }
  256. if(tableName == QString::fromLocal8Bit("ftp_other"))
  257. {
  258. creat_sql = QString("CREATE TABLE ftp_other (\
  259. id INT PRIMARY KEY NOT NULL,\
  260. ip TEXT NOT NULL,\
  261. port INT NOT NULL,\
  262. dir TEXT NOT NULL,\
  263. uid TEXT NOT NULL,\
  264. pwd TEXT NOT NULL,\
  265. mode INT NOT NULL)");
  266. }
  267. if(tableName==QString::fromLocal8Bit("location"))
  268. {
  269. creat_sql = QString("CREATE TABLE location (\
  270. id INT PRIMARY KEY NOT NULL,\
  271. lbs_lat TEXT NOT NULL,\
  272. lbs_lng TEXT NOT NULL,\
  273. gps_lat TEXT NOT NULL,\
  274. gps_lng TEXT NOT NULL,\
  275. gps_elevation TEXT NOT NULL)");
  276. }
  277. if(tableName==QString::fromLocal8Bit("camera"))
  278. {
  279. creat_sql = QString("CREATE TABLE camera (\
  280. camera_type TEXT NOT NULL,\
  281. mode TEXT NOT NULL,\
  282. stitch_photonum INT NOT NULL,\
  283. photo_width INT NOT NULL,\
  284. photo_height INT NOT NULL,\
  285. auto_focus TEXT NOT NULL,\
  286. movZ_f TEXT NOT NULL,\
  287. movZ_b TEXT NOT NULL,\
  288. movX_f TEXT NOT NULL,\
  289. movX_b TEXT NOT NULL)");
  290. }
  291. if(tableName==QString::fromLocal8Bit("config"))
  292. {
  293. creat_sql = QString("CREATE TABLE config (\
  294. jsonCacheEnable INT NOT NULL,\
  295. imageCacheEnable INT NOT NULL,\
  296. maxJsonEntries INT NOT NULL,\
  297. maxImageEntries INT NOT NULL,\
  298. x_init INT NOT NULL,\
  299. y_init TEXT NOT NULL)");
  300. }
  301. sql_query.prepare(creat_sql);
  302. if(!sql_query.exec())
  303. {
  304. qDebug() << "Error: Fail to create table." << sql_query.lastError();
  305. }
  306. else
  307. {
  308. qDebug() << "Table created!" << creat_sql;
  309. }
  310. return 0;
  311. }
  312. int SqliteOperator::InsertData(config_db_type &singledb,QString tablename)
  313. {
  314. QSqlQuery sql_query;
  315. if(tablename == QString::fromLocal8Bit("mqtt")||tablename == QString::fromLocal8Bit("mqtt_other"))
  316. {
  317. sql_query.prepare(QString("INSERT INTO %1 VALUES(:id,:dtype,:ip,:port,:imei,:uid,:pwd,:clientid,:qos,:keepalive,:sub,:pub,:offline,:dat_f)").arg(tablename));
  318. sql_query.bindValue(":id", singledb.mqtt_db.id);
  319. sql_query.bindValue(":dtype", singledb.mqtt_db.dtype);
  320. sql_query.bindValue(":ip", singledb.mqtt_db.ip);
  321. sql_query.bindValue(":port", singledb.mqtt_db.port);
  322. sql_query.bindValue(":imei",singledb.mqtt_db.imei);
  323. sql_query.bindValue(":uid",singledb.mqtt_db.uid);
  324. sql_query.bindValue(":pwd",singledb.mqtt_db.pwd);
  325. sql_query.bindValue(":clientid",singledb.mqtt_db.clientid);
  326. sql_query.bindValue(":qos",singledb.mqtt_db.qos);
  327. sql_query.bindValue(":keepalive",singledb.mqtt_db.keepalive);
  328. sql_query.bindValue(":sub",singledb.mqtt_db.sub);
  329. sql_query.bindValue(":pub",singledb.mqtt_db.pub);
  330. sql_query.bindValue(":offline",singledb.mqtt_db.offline);
  331. sql_query.bindValue(":dat_f",singledb.mqtt_db.dat_f);
  332. }
  333. else if(tablename == QString::fromLocal8Bit("ftp")||tablename == QString::fromLocal8Bit("ftp_other"))
  334. {
  335. sql_query.prepare(QString("INSERT INTO %1 VALUES(:id,:ip,:port,:dir,:uid,:pwd,:mode)").arg(tablename));
  336. sql_query.bindValue(":id", singledb.ftp_db.id);
  337. sql_query.bindValue(":ip", singledb.ftp_db.ip);
  338. sql_query.bindValue(":port", singledb.ftp_db.port);
  339. sql_query.bindValue(":dir",singledb.ftp_db.dir);
  340. sql_query.bindValue(":uid",singledb.ftp_db.uid);
  341. sql_query.bindValue(":pwd",singledb.ftp_db.pwd);
  342. sql_query.bindValue(":mode", singledb.ftp_db.mode);
  343. }
  344. else if(tablename == QString::fromLocal8Bit("location"))
  345. {
  346. sql_query.prepare(QString("INSERT INTO %1 VALUES(:id,:lbs_lat,:lbs_lng,:gps_lat,:gps_lng,:gps_elevation)").arg(tablename));
  347. sql_query.bindValue(":id", singledb.location_db.id);
  348. sql_query.bindValue(":lbs_lat", singledb.location_db.lbs_lat);
  349. sql_query.bindValue(":lbs_lng", singledb.location_db.lbs_lng);
  350. sql_query.bindValue(":gps_lat", singledb.location_db.gps_lat);
  351. sql_query.bindValue(":gps_lng", singledb.location_db.gps_lng);
  352. sql_query.bindValue(":gps_elevation", singledb.location_db.gps_elevation);
  353. }else if(tablename == QString::fromLocal8Bit("camera")){
  354. sql_query.prepare(QString("INSERT INTO %1 VALUES(:camera_type,:mode,:stitch_photonum,:photo_width,:photo_height,:auto_focus,:movZ_f,:movZ_b,:movX_f,:movX_b)").arg(tablename));
  355. sql_query.bindValue(":camera_type", singledb.camera_db.camera_type);
  356. sql_query.bindValue(":mode", singledb.camera_db.mode);
  357. sql_query.bindValue(":stitch_photonum", singledb.camera_db.stitch_photonum);
  358. sql_query.bindValue(":photo_width", singledb.camera_db.photo_width);
  359. sql_query.bindValue(":photo_height", singledb.camera_db.photo_height);
  360. sql_query.bindValue(":auto_focus",singledb.camera_db.auto_focus);
  361. sql_query.bindValue(":movZ_f",singledb.camera_db.movz_f);
  362. sql_query.bindValue(":movZ_b",singledb.camera_db.movz_b);
  363. sql_query.bindValue(":movX_f",singledb.camera_db.movx_f);
  364. sql_query.bindValue(":movX_b",singledb.camera_db.movx_b);
  365. }else if(tablename == QString::fromLocal8Bit("config")){
  366. sql_query.prepare(QString("INSERT INTO %1 VALUES(:jsonCacheEnable,:imageCacheEnable,:maxJsonEntries,:maxImageEntries,:x_init,:y_init)").arg(tablename));
  367. sql_query.bindValue(":jsonCacheEnable", singledb.config_db.jsonCacheEnable);
  368. sql_query.bindValue(":imageCacheEnable", singledb.config_db.imageCacheEnable);
  369. sql_query.bindValue(":maxJsonEntries", singledb.config_db.maxJsonEntries);
  370. sql_query.bindValue(":maxImageEntries", singledb.config_db.maxImageEntries);
  371. sql_query.bindValue(":x_init", singledb.config_db.x_init);
  372. sql_query.bindValue(":y_init",singledb.config_db.y_init);
  373. }
  374. if(!sql_query.exec())
  375. {
  376. qDebug() <<"Error: Fail to insert data:"<< tablename << sql_query.lastError();
  377. }
  378. else
  379. {
  380. LOG("InsertData Success!");
  381. }
  382. return 0;
  383. }
  384. void InertListConfig(QList<config_mqtt_db> &moreData,QString tablename)
  385. {
  386. // 进行多个数据的插入时,可以利用绑定进行批处理
  387. QSqlQuery sqlQuery;
  388. sqlQuery.prepare("INSERT INFO mqtt VALUES(?,?,?)");
  389. QVariantList idList;
  390. }
  391. int SqliteOperator::UpdateData(QString tablename, QString name, QString value)
  392. {
  393. LOG("UpdateData:{}-{}-{}",tablename.toStdString(),name.toStdString(),value.toStdString());
  394. //QString update_sql = "UPDATE mqtt SET ip = :ip WHERE id = :id";
  395. QSqlQuery sql_query;
  396. sql_query.prepare(QString("UPDATE %1 SET %2=?").arg(tablename,name));
  397. sql_query.addBindValue(value);
  398. if(!sql_query.exec())
  399. {
  400. ERROR("update Failed!");
  401. }else{
  402. LOG("updated Success!");
  403. }
  404. return 0;
  405. }
  406. int SqliteOperator::QueryAllData(QString& table_name)
  407. {
  408. QSqlQuery sql_query;
  409. int data_num = 0;
  410. sql_query.exec(QString("SELECT * FROM %1").arg(table_name));
  411. if(!sql_query.exec())
  412. {
  413. qDebug() << "Error: Fail to query table. " << sql_query.lastError();
  414. }
  415. else
  416. {
  417. while(sql_query.next())
  418. {
  419. data_num ++;
  420. if(table_name == QString::fromLocal8Bit("mqtt"))
  421. {
  422. //param_mqtt_main["id"] = sql_query.value(0).toInt();
  423. param::dtype = sql_query.value(1).toString();
  424. param::mqtt_main_ip = sql_query.value(2).toString();
  425. param::mqtt_main_port = sql_query.value(3).toInt();
  426. if(param::imei.size()<10)
  427. param::imei = sql_query.value(4).toString();
  428. param::mqtt_main_uid = sql_query.value(5).toString();
  429. param::mqtt_main_pwd = sql_query.value(6).toString();
  430. param::mqtt_main_clientid = sql_query.value(7).toString();
  431. param::mqtt_main_qos = sql_query.value(8).toInt();
  432. param::mqtt_main_keepalive = sql_query.value(9).toInt();
  433. param::mqtt_main_sub = sql_query.value(10).toString();
  434. param::mqtt_main_pub = sql_query.value(11).toString();
  435. param::mqtt_main_offline = sql_query.value(12).toString();
  436. param::dat_f_main = sql_query.value(13).toInt();
  437. LOG("ip:{}",param::mqtt_main_ip.toStdString());
  438. LOG("dat_f:{}",param::dat_f_main);
  439. }
  440. else if(table_name == QString::fromLocal8Bit("mqtt_other"))
  441. {
  442. //param_mqtt_other["id"] = sql_query.value(0).toInt();
  443. //param_mqtt_other["dtype"] = sql_query.value(1).toString();
  444. param::mqtt_other_ip = sql_query.value(2).toString();
  445. param::mqtt_other_port = sql_query.value(3).toInt();
  446. //param_mqtt_other["imei"] = sql_query.value(4).toString();
  447. param::mqtt_other_uid = sql_query.value(5).toString();
  448. param::mqtt_other_pwd = sql_query.value(6).toString();
  449. param::mqtt_other_clientid = sql_query.value(7).toString();
  450. param::mqtt_other_qos = sql_query.value(8).toInt();
  451. param::mqtt_other_keepalive = sql_query.value(9).toInt();
  452. param::mqtt_other_sub = sql_query.value(10).toString();
  453. param::mqtt_other_pub = sql_query.value(11).toString();
  454. param::mqtt_other_offline = sql_query.value(12).toString();
  455. param::dat_f_other = sql_query.value(13).toInt();
  456. }
  457. else if(table_name == QString::fromLocal8Bit("ftp"))
  458. {
  459. param::ftp_main_ip = sql_query.value(1).toString();
  460. param::ftp_main_port = sql_query.value(2).toInt();
  461. param::ftp_main_dir = sql_query.value(3).toString();
  462. param::ftp_main_uid = sql_query.value(4).toString();
  463. param::ftp_main_pwd = sql_query.value(5).toString();
  464. param::ftp_main_mode = sql_query.value(6).toInt();
  465. }
  466. else if(table_name == QString::fromLocal8Bit("ftp_opther"))
  467. {
  468. param::ftp_other_ip = sql_query.value(1).toString();
  469. param::ftp_other_port = sql_query.value(2).toInt();
  470. param::ftp_other_dir = sql_query.value(3).toString();
  471. param::ftp_other_uid = sql_query.value(4).toString();
  472. param::ftp_other_pwd = sql_query.value(5).toString();
  473. param::ftp_other_mode = sql_query.value(6).toInt();
  474. }
  475. else if(table_name == QString::fromLocal8Bit("location"))
  476. {
  477. param::lbs_lat = sql_query.value(1).toString();
  478. param::lbs_lng = sql_query.value(2).toString();
  479. param::gps_lat = sql_query.value(3).toString();
  480. param::gps_lng = sql_query.value(4).toString();
  481. param::gps_elevation = sql_query.value(5).toString();
  482. }else if(table_name == QString::fromLocal8Bit("camera"))
  483. {
  484. param::camera_type = sql_query.value(0).toString();
  485. param::stitch_mode = sql_query.value(1).toString();
  486. param::stitch_photonum = sql_query.value(2).toInt();
  487. param::photo_width = sql_query.value(3).toInt();
  488. param::photo_height = sql_query.value(4).toInt();
  489. param::auto_focus = sql_query.value(5).toString();
  490. param::movZ_f_l = sql_query.value(6).toString();
  491. param::movZ_b_l = sql_query.value(7).toString();
  492. param::movX_f_l = sql_query.value(8).toString();
  493. param::movX_b_l = sql_query.value(9).toString();
  494. LOG("camera_type:{}",param::camera_type.toStdString());
  495. LOG("stitch_mode:{}",param::stitch_mode.toStdString());
  496. LOG("stitch_photonum:{}",param::stitch_photonum);
  497. LOG("photo_width:{}",param::photo_width);
  498. LOG("photo_height:{}",param::photo_height);
  499. if(param::movZ_f_l.isEmpty()||param::movZ_f_l==""){
  500. param::movZ_f_l = movZ_f;
  501. }
  502. if(param::movZ_b_l.isEmpty()||param::movZ_b_l==""){
  503. param::movZ_b_l = movZ_b;
  504. }
  505. if(param::movX_f_l.isEmpty()||param::movX_f_l==""){
  506. param::movX_f_l = movX_f;
  507. }
  508. if(param::movX_b_l.isEmpty()||param::movX_b_l==""){
  509. param::movX_b_l = movX_b;
  510. }
  511. LOG("movZ_f_l:{}",param::movZ_f_l.toStdString());
  512. LOG("movZ_b_l:{}",param::movZ_b_l.toStdString());
  513. LOG("movX_f_l:{}",param::movX_f_l.toStdString());
  514. LOG("movX_b_l:{}",param::movX_b_l.toStdString());
  515. }else if(table_name == QString::fromLocal8Bit("config"))
  516. {
  517. param::jsonCacheEnable = sql_query.value(0).toInt();
  518. param::imageCacheEnable = sql_query.value(1).toInt();
  519. param::maxJsonEntries = sql_query.value(2).toInt();
  520. param::maxImageEntries = sql_query.value(3).toInt();
  521. param::x_init = sql_query.value(4).toInt();
  522. param::y_init = sql_query.value(5).toInt();
  523. LOG("jsonCacheEnable:{}",param::jsonCacheEnable);
  524. LOG("imageCacheEnable:{}",param::imageCacheEnable);
  525. LOG("maxJsonEntries:{}",param::maxJsonEntries);
  526. LOG("maxImageEntries:{}",param::maxImageEntries);
  527. LOG("x_init:{}",param::x_init);
  528. LOG("y_init:{}",param::y_init);
  529. }
  530. LOG("data_num:{}",data_num);
  531. }
  532. }
  533. return 0;
  534. }
  535. int SqliteOperator::QueryData()
  536. {
  537. QString select_sql = QString("select * from student where name = '%1' and (age = '%2' or age = '%3')")
  538. .arg("Wang")
  539. .arg(30)
  540. .arg(25);
  541. QSqlQuery sql_query;
  542. if(!sql_query.exec(select_sql))
  543. {
  544. qDebug()<<sql_query.lastError();
  545. }
  546. else
  547. {
  548. while(sql_query.next())
  549. {
  550. int id = sql_query.value(0).toInt();
  551. QString name = sql_query.value(1).toString();
  552. qDebug()<<QString("id:%1 name:%2").arg(id).arg(name);
  553. }
  554. }
  555. return 0;
  556. }
  557. DatabaseInterface::DatabaseInterface(QObject *parent) : QObject(parent)
  558. {
  559. }
  560. bool DatabaseInterface::openDatabase(const QString &dbName)
  561. {
  562. #if ANDROID
  563. QString db_Path = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation);
  564. QString db_name = db_Path + "/" + dbName;
  565. #else
  566. QString db_Path = "./";
  567. QString db_name = db_Path + dbName;
  568. #endif
  569. m_database = QSqlDatabase::addDatabase("QSQLITE");
  570. m_database.setDatabaseName(db_name);
  571. if (!m_database.open()) {
  572. qDebug() << "Error: Failed to connect database." << m_database.lastError();
  573. return false;
  574. }
  575. return true;
  576. }
  577. bool DatabaseInterface::closeDatabase()
  578. {
  579. m_database.close();
  580. return true;
  581. }
  582. QString DatabaseInterface::getTable_row(QString tablename)
  583. {
  584. QSqlQuery query(m_database);
  585. if(tablename == QString::fromLocal8Bit("data"))
  586. {
  587. query.prepare("SELECT COUNT(*) FROM data;");
  588. if(query.exec()&&query.next())
  589. {
  590. return query.value(0).toString();
  591. }
  592. }
  593. return 0;
  594. }
  595. bool DatabaseInterface::createTable(QString tableName)
  596. {
  597. /* 删除表格 */
  598. // QString clear_sql = "drop table " + tableName; /* 没有用where给出限制,就会删除所有内容 */
  599. // sql_query.prepare(clear_sql);
  600. // if(!sql_query.exec())
  601. // {
  602. // qDebug() << sql_query.lastError();
  603. // }
  604. // else
  605. // {
  606. // qDebug() << "table" << tableName << "cleared";
  607. // }
  608. QSqlQuery sql_query(m_database);
  609. QString creat_sql;
  610. if(tableName == QString::fromLocal8Bit("mqtt"))
  611. {
  612. creat_sql = QString("CREATE TABLE mqtt (\
  613. id INT PRIMARY KEY NOT NULL,\
  614. dtype TEXT NOT NULL,\
  615. ip TEXT NOT NULL,\
  616. port INT NOT NULL,\
  617. imei TEXT NOT NULL,\
  618. uid TEXT NOT NULL,\
  619. pwd TEXT NOT NULL,\
  620. clientid TEXT,\
  621. qos INT NOT NULL,\
  622. keepalive INT NOT NULL,\
  623. sub TEXT NOT NULL,\
  624. pub TEXT NOT NULL,\
  625. offline TEXT NOT NULL,\
  626. dat_f INT NOT NULL)");
  627. }
  628. if(tableName == QString::fromLocal8Bit("mqtt_other"))
  629. {
  630. creat_sql = QString("CREATE TABLE mqtt_other (\
  631. id INT PRIMARY KEY NOT NULL,\
  632. dtype TEXT NOT NULL,\
  633. ip TEXT NOT NULL,\
  634. port INT NOT NULL,\
  635. imei TEXT NOT NULL,\
  636. uid TEXT NOT NULL,\
  637. pwd TEXT NOT NULL,\
  638. clientid TEXT,\
  639. qos INT NOT NULL,\
  640. keepalive INT NOT NULL,\
  641. sub TEXT NOT NULL,\
  642. pub TEXT NOT NULL,\
  643. offline TEXT NOT NULL,\
  644. dat_f INT NOT NULL)");
  645. }
  646. if(tableName == QString::fromLocal8Bit("ftp"))
  647. {
  648. creat_sql = QString("CREATE TABLE ftp (\
  649. id INT PRIMARY KEY NOT NULL,\
  650. ip TEXT NOT NULL,\
  651. port INT NOT NULL,\
  652. dir TEXT NOT NULL,\
  653. uid TEXT NOT NULL,\
  654. pwd TEXT NOT NULL,\
  655. mode INT NOT NULL)");
  656. }
  657. if(tableName == QString::fromLocal8Bit("ftp_other"))
  658. {
  659. creat_sql = QString("CREATE TABLE ftp_other (\
  660. id INT PRIMARY KEY NOT NULL,\
  661. ip TEXT NOT NULL,\
  662. port INT NOT NULL,\
  663. dir TEXT NOT NULL,\
  664. uid TEXT NOT NULL,\
  665. pwd TEXT NOT NULL,\
  666. mode INT NOT NULL)");
  667. }
  668. if(tableName==QString::fromLocal8Bit("location"))
  669. {
  670. creat_sql = QString("CREATE TABLE location (\
  671. id INT PRIMARY KEY NOT NULL,\
  672. lbs_lat TEXT NOT NULL,\
  673. lbs_lng TEXT NOT NULL,\
  674. gps_lat TEXT NOT NULL,\
  675. gps_lng TEXT NOT NULL,\
  676. gps_elevation TEXT NOT NULL)");
  677. }
  678. if(tableName==QString::fromLocal8Bit("camera"))
  679. {
  680. creat_sql = QString("CREATE TABLE camera (\
  681. camera_type TEXT NOT NULL,\
  682. mode TEXT NOT NULL,\
  683. stitch_photonum INT NOT NULL,\
  684. photo_width INT NOT NULL,\
  685. photo_height INT NOT NULL,\
  686. auto_focus TEXT NOT NULL)");
  687. }
  688. sql_query.prepare(creat_sql);
  689. if(!sql_query.exec())
  690. {
  691. qDebug() << "Error: Fail to create table." << sql_query.lastError();
  692. return false;
  693. }else{
  694. qDebug() << "Table created!" << creat_sql;
  695. }
  696. return true;
  697. }
  698. bool DatabaseInterface::insertData(const QString &name, const QString &address)
  699. {
  700. QSqlQuery query(m_database);
  701. query.prepare("INSERT INTO Info (Name, Address) VALUES (:name, :address)");
  702. query.bindValue(":name", name);
  703. query.bindValue(":address", address);
  704. if (!query.exec()) {
  705. qDebug() << "Error: Failed to insert data." << query.lastError();
  706. return false;
  707. }
  708. return true;
  709. }
  710. bool DatabaseInterface::deleteData(int id)
  711. {
  712. QSqlQuery query(m_database);
  713. query.prepare("DELETE FROM Info WHERE ID = :id");
  714. query.bindValue(":id", id);
  715. if (!query.exec()) {
  716. qDebug() << "Error: Failed to delete data." << query.lastError();
  717. return false;
  718. }
  719. return true;
  720. }
  721. bool DatabaseInterface::updateData(QString tablename, QString name, QVariant value)
  722. {
  723. QSqlQuery query(m_database);
  724. qDebug() << "Table name:" <<tablename << name << value;
  725. query.prepare(QString("UPDATE %1 SET %2=?").arg(tablename,name));
  726. query.addBindValue(value);
  727. if (!query.exec()) {
  728. qDebug() << "Error: Failed to update data." << query.lastError();
  729. return false;
  730. }
  731. return true;
  732. }
  733. QJsonArray DatabaseInterface::queryData(QString table_name,int count_index)
  734. {
  735. //清空json数组
  736. // for(int i=0;i<param::mqtt_array.size();i++)
  737. // {
  738. // param::mqtt_array.removeFirst();
  739. // }
  740. QJsonArray mqtt_array;
  741. QJsonObject param_mqtt_main,param_mqtt_other,param_ftp_main,param_ftp_other,param_location,param_camera,json_mqtt;
  742. QSqlQuery sql_query;
  743. int data_num = 0;
  744. if(table_name==QString::fromLocal8Bit("data"))
  745. {
  746. sql_query.exec(QString("SELECT * FROM %1 WHERE id = ?").arg(table_name));
  747. for(data_num=(count_index-1)*10;data_num <10*count_index;data_num++)
  748. {
  749. sql_query.bindValue(0,data_num);
  750. if (sql_query.exec())
  751. {
  752. while(sql_query.next())
  753. {
  754. json_mqtt["id"] = sql_query.value(0).toInt();
  755. json_mqtt["datetime"] = sql_query.value(1).toString();
  756. json_mqtt["Json_data"] = sql_query.value(2).toString();
  757. json_mqtt["Json_warn"] = sql_query.value(3).toString();
  758. mqtt_array.append(json_mqtt);
  759. }
  760. }
  761. }
  762. qDebug() <<"json data:"<< mqtt_array;
  763. return mqtt_array;
  764. }
  765. sql_query.exec(QString("SELECT * FROM %1").arg(table_name));
  766. if(!sql_query.exec())
  767. {
  768. qDebug() << "Error: Fail to query table. " << sql_query.lastError();
  769. }
  770. else
  771. {
  772. while(sql_query.next())
  773. {
  774. data_num ++;
  775. if(table_name == QString::fromLocal8Bit("mqtt"))
  776. {
  777. qDebug() << "mqtt";
  778. param_mqtt_main["id"] = sql_query.value(0).toInt();
  779. param_mqtt_main["dtype"] = sql_query.value(1).toString();
  780. param_mqtt_main["ip"] = sql_query.value(2).toString();
  781. param_mqtt_main["port"] = sql_query.value(3).toInt();
  782. param_mqtt_main["imei"] = sql_query.value(4).toString();
  783. param_mqtt_main["uid"] = sql_query.value(5).toString();
  784. param_mqtt_main["pwd"] = sql_query.value(6).toString();
  785. param_mqtt_main["clientid"] = sql_query.value(7).toString();
  786. param_mqtt_main["qos"] = sql_query.value(8).toInt();
  787. param_mqtt_main["keepalive"] = sql_query.value(9).toInt();
  788. param_mqtt_main["sub"] = sql_query.value(10).toString();
  789. param_mqtt_main["pub"] = sql_query.value(11).toString();
  790. param_mqtt_main["offline"] = sql_query.value(12).toString();
  791. mqtt_array.append(param_mqtt_main);
  792. }
  793. else if(table_name == QString::fromLocal8Bit("mqtt_other"))
  794. {
  795. qDebug() << "mqtt_other";
  796. param_mqtt_other["id"] = sql_query.value(0).toInt();
  797. param_mqtt_other["dtype"] = sql_query.value(1).toString();
  798. param_mqtt_other["ip"] = sql_query.value(2).toString();
  799. param_mqtt_other["port"] = sql_query.value(3).toInt();
  800. param_mqtt_other["imei"] = sql_query.value(4).toString();
  801. param_mqtt_other["uid"] = sql_query.value(5).toString();
  802. param_mqtt_other["pwd"] = sql_query.value(6).toString();
  803. param_mqtt_other["clientid"] = sql_query.value(7).toString();
  804. param_mqtt_other["qos"] = sql_query.value(8).toInt();
  805. param_mqtt_other["keepalive"] = sql_query.value(9).toInt();
  806. param_mqtt_other["sub"] = sql_query.value(10).toString();
  807. param_mqtt_other["pub"] = sql_query.value(11).toString();
  808. param_mqtt_other["offline"] = sql_query.value(12).toString();
  809. mqtt_array.append(param_mqtt_other);
  810. }
  811. else if(table_name == QString::fromLocal8Bit("ftp"))
  812. {
  813. param_ftp_main["id"] = sql_query.value(0).toInt();
  814. param_ftp_main["ip"] = sql_query.value(1).toString();
  815. param_ftp_main["port"] = sql_query.value(2).toInt();
  816. param_ftp_main["dir"] = sql_query.value(3).toString();
  817. param_ftp_main["uid"] = sql_query.value(4).toString();
  818. param_ftp_main["pwd"] = sql_query.value(5).toString();
  819. param_ftp_main["mode"] = sql_query.value(6).toInt();
  820. mqtt_array.append(param_ftp_main);
  821. }
  822. else if(table_name == QString::fromLocal8Bit("ftp_other"))
  823. {
  824. param_ftp_other["id"] = sql_query.value(0).toInt();
  825. param_ftp_other["ip"] = sql_query.value(1).toString();
  826. param_ftp_other["port"] = sql_query.value(2).toInt();
  827. param_ftp_other["dir"] = sql_query.value(3).toString();
  828. param_ftp_other["uid"] = sql_query.value(4).toString();
  829. param_ftp_other["pwd"] = sql_query.value(5).toString();
  830. param_ftp_other["mode"] = sql_query.value(6).toInt();
  831. mqtt_array.append(param_ftp_other);
  832. }
  833. else if(table_name == QString::fromLocal8Bit("location"))
  834. {
  835. param_location["id"] = sql_query.value(0).toInt();
  836. param_location["lbs_lat"] = sql_query.value(1).toString();
  837. param_location["lbs_lng"] = sql_query.value(2).toString();
  838. param_location["gps_lat"] = sql_query.value(3).toString();
  839. param_location["gps_lng"] = sql_query.value(4).toString();
  840. param_location["gps_elevation"] = sql_query.value(5).toString();
  841. mqtt_array.append(param_location);
  842. }else if(table_name == QString::fromLocal8Bit("camera"))
  843. {
  844. param_camera["camera_type"] = sql_query.value(0).toString();
  845. param_camera["mode"] = sql_query.value(1).toString();
  846. param_camera["stitch_photonum"] = sql_query.value(2).toInt();
  847. param_camera["photo_width"] = sql_query.value(3).toInt();
  848. param_camera["photo_height"] = sql_query.value(4).toInt();
  849. param_camera["auto_focus"] = sql_query.value(5).toString();
  850. param_camera["movz_f"] = sql_query.value(6).toString().isEmpty() ? param::movZ_f_l : "null";
  851. param_camera["movz_b"] = sql_query.value(7).toString().isEmpty() ? param::movZ_b_l : "null";
  852. param_camera["movx_f"] = sql_query.value(8).toString().isNull() ? param::movX_f_l : "null";
  853. param_camera["movx_b"] = sql_query.value(9).toString().isEmpty() ? param::movX_b_l : "null";
  854. mqtt_array.append(param_camera);
  855. }
  856. qDebug() << "data num: " << data_num;
  857. }
  858. qDebug() <<"json data:"<< mqtt_array;
  859. }
  860. return mqtt_array;
  861. }