| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905 |
- #include <mysql.h>
- #include <cameramanager.h>
- class param;
- QString param::dtype;
- QString param::imei;
- QString param::iccid;
- QString param::csq = "0";
- QString param::mqtt_main_ip;
- QString param::mqtt_other_ip;
- int param::mqtt_main_port;
- int param::mqtt_other_port;
- int param::mqtt_main_keepalive;
- int param::mqtt_other_keepalive;
- int param::mqtt_main_qos;
- int param::mqtt_other_qos;
- QString param::mqtt_main_clientid;
- QString param::mqtt_other_clientid;
- QString param::mqtt_main_uid;
- QString param::mqtt_other_uid;
- QString param::mqtt_main_pwd;
- QString param::mqtt_other_pwd;
- QString param::mqtt_main_sub;
- QString param::mqtt_other_sub;
- QString param::mqtt_main_pub;
- QString param::mqtt_other_pub;
- QString param::mqtt_main_offline;
- QString param::mqtt_other_offline;
- QString param::ftp_main_ip;
- QString param::ftp_other_ip;
- int param::ftp_main_port;
- int param::ftp_other_port;
- QString param::ftp_main_dir;
- QString param::ftp_other_dir;
- QString param::ftp_main_uid;
- QString param::ftp_other_uid;
- QString param::ftp_main_pwd;
- QString param::ftp_other_pwd;
- int param::ftp_main_mode;
- int param::ftp_other_mode;
- int param::dat_f_main;
- int param::dat_f_other;
- QString param::gps_state;
- QString param::lbs_lat;
- QString param::lbs_lng;
- QString param::gps_lat;
- QString param::gps_lng;
- QString param::gps_elevation;
- QString param::camera_type;
- QString param::stitch_mode;
- int param::stitch_photonum;
- int param::photo_height;
- int param::photo_width;
- QString param::auto_focus;
- QString param::movZ_f_l;
- QString param::movZ_b_l;
- QString param::movX_f_l;
- QString param::movX_b_l;
- int param::camera_tc = 0;
- QStringList param::ptlist;
- QStringList param::ptlist_res;
- int param::jsonCacheEnable;
- int param::imageCacheEnable;
- int param::maxJsonEntries;
- int param::maxImageEntries;
- int param::x_init;
- int param::y_init;
- bool param::net_alive;
- QString param::net_name;
- QString param::net_description;
- QString param::net_mac;
- QString param::net_ipv4;
- int param::datupload_remain;
- SqliteOperator::SqliteOperator(QString &dbname)
- {
- LOG("实例化:SqliteOperator:{}",dbname.toStdString());
- if(QSqlDatabase::contains("qt_sql_default_connection"))
- {
- db = QSqlDatabase::database("qt_sql_default_connection");
- }
- else
- {
- #if ANDROID
- QString db_Path = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation);
- QString db_name = db_Path + "/" + dbname;
- #else
- QString db_Path = "./";
- QString db_name = db_Path + dbname;
- #endif
- db = QSqlDatabase::addDatabase("QSQLITE","qt_sql_default_connection");
- db.setDatabaseName(db_name);
- // db.setUserName("admin");
- // db.setPassword("yf123456");
- }
- }
- bool InsertJson_mqtt()
- {
- #if ANDROID
- QString db_Path = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation);
- QString db_name = db_Path + "/" + "data.db";
- #else
- QString db_Path = "./";
- QString db_name = "./data.db";
- #endif
- QSqlDatabase mqtt_db;
- if(QSqlDatabase::contains("qt_sql_default_connection"))
- {
- mqtt_db = QSqlDatabase::addDatabase("QSQLITE");
- }
- QSqlQuery sql_mq;
- mqtt_db.setDatabaseName(db_name);
- if(!mqtt_db.open())
- {
- qDebug() << "Error: Failed to connect database." << mqtt_db.lastError();
- return false;
- }
- //判断表是否存在
- if(!mqtt_db.tables().contains("data"))
- {
- sql_mq.prepare(QString("CREATE TABLE data (\
- id INT PRIMARY KEY NOT NULL,\
- datetime TEXT NOT NULL,\
- Json_data TEXT NOT NULL,\
- Json_warn TEXT NOT NULL)"));
- if(!sql_mq.exec())
- {
- qDebug() << "Error: Fail to create table." << sql_mq.lastError();
- return false;
- }
- else
- {
- //qDebug() <<"mqtt data Table created!";
- LOG("mqtt data Table created!");
- }
- }
- //获取游标对象
- //查询数据数量是否超过设定值
- sql_mq.prepare("SELECT COUNT(*) FROM data;");
- if(sql_mq.exec()&&sql_mq.next())
- {
- int row_count = sql_mq.value(0).toInt();
- qDebug() << "Number of rows in table: " << row_count;
- }
- // config_db_type mqtt_class_history;
- // QJsonObject json_dat;
- // json_dat.insert("vbat","24.01");
- // json_dat.insert("imei","123132123");
- // for(int dbi = 0;dbi <10000; dbi++)
- // {
- // QDateTime dateTime= QDateTime::currentDateTime();//获取系统当前的时间
- // mqtt_class_history.json_db.id = dbi;
- // mqtt_class_history.json_db.datetime = dateTime .toString("yyyy-MM-dd hh:mm:ss");
- // mqtt_class_history.json_db.Json_data = QString(QJsonDocument(json_dat).toJson());
- // mqtt_class_history.json_db.Json_warn = QString(QJsonDocument(json_dat).toJson());
- // sql_mq.prepare(QString("INSERT INTO data VALUES(:id,:datetime,:Json_data,:Json_warn)"));
- // sql_mq.bindValue(":id", mqtt_class_history.json_db.id);
- // sql_mq.bindValue(":datetime", mqtt_class_history.json_db.datetime);
- // sql_mq.bindValue(":Json_data", mqtt_class_history.json_db.Json_data);
- // sql_mq.bindValue(":Json_warn", mqtt_class_history.json_db.Json_warn);
- // LOG("write json to data db:{}",dbi);
- // if(!sql_mq.exec())
- // {
- // qDebug() <<"Error: Fail to insert data:"<< sql_mq.lastError();
- // }
- // else
- // {
- // LOG("InsertJson Success!");
- // }
- // }
- mqtt_db.close();
- return true;
- }
- bool SqliteOperator::OpenDb()
- {
- QString db_Path = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation);
- QString db_name = db_Path + "/" + "config.db";
- if(QSqlDatabase::contains("qt_sql_default_connection"))
- {
- db = QSqlDatabase::database("qt_sql_default_connection");
- }else{
- db = QSqlDatabase::addDatabase("QSQLITE","qt_sql_default_connection");
- db.setDatabaseName(db_name);
- }
- if(!db.open())
- {
- qDebug() << "Error: Failed to connect database." << db.lastError();
- return false;
- }
- return true;
- }
- bool SqliteOperator::isTableExist(QString& tableName)
- {
- QSqlDatabase database = QSqlDatabase::database();
- if(database.tables().contains(tableName))
- {
- return true;
- }
- return false;
- }
- int SqliteOperator::CloseDb()
- {
- QSqlDatabase::removeDatabase("qt_sql_default_connection");
- //db.close();
- return 0;
- }
- int SqliteOperator::CreateTable(QString& tableName)
- {
- QSqlQuery sql_query;
- QString creat_sql;
- if(tableName == QString::fromLocal8Bit("mqtt"))
- {
- creat_sql = QString("CREATE TABLE mqtt (\
- id INT PRIMARY KEY NOT NULL,\
- dtype TEXT NOT NULL,\
- ip TEXT NOT NULL,\
- port INT NOT NULL,\
- imei TEXT NOT NULL,\
- uid TEXT NOT NULL,\
- pwd TEXT NOT NULL,\
- clientid TEXT,\
- qos INT NOT NULL,\
- keepalive INT NOT NULL,\
- sub TEXT NOT NULL,\
- pub TEXT NOT NULL,\
- offline TEXT NOT NULL,\
- dat_f INT NOT NULL)");
- }
- if(tableName == QString::fromLocal8Bit("mqtt_other"))
- {
- creat_sql = QString("CREATE TABLE mqtt_other (\
- id INT PRIMARY KEY NOT NULL,\
- dtype TEXT NOT NULL,\
- ip TEXT NOT NULL,\
- port INT NOT NULL,\
- imei TEXT NOT NULL,\
- uid TEXT NOT NULL,\
- pwd TEXT NOT NULL,\
- clientid TEXT,\
- qos INT NOT NULL,\
- keepalive INT NOT NULL,\
- sub TEXT NOT NULL,\
- pub TEXT NOT NULL,\
- offline TEXT NOT NULL,\
- dat_f INT NOT NULL)");
- }
- if(tableName == QString::fromLocal8Bit("ftp"))
- {
- creat_sql = QString("CREATE TABLE ftp (\
- id INT PRIMARY KEY NOT NULL,\
- ip TEXT NOT NULL,\
- port INT NOT NULL,\
- dir TEXT NOT NULL,\
- uid TEXT NOT NULL,\
- pwd TEXT NOT NULL,\
- mode INT NOT NULL)");
- }
- if(tableName == QString::fromLocal8Bit("ftp_other"))
- {
- creat_sql = QString("CREATE TABLE ftp_other (\
- id INT PRIMARY KEY NOT NULL,\
- ip TEXT NOT NULL,\
- port INT NOT NULL,\
- dir TEXT NOT NULL,\
- uid TEXT NOT NULL,\
- pwd TEXT NOT NULL,\
- mode INT NOT NULL)");
- }
- if(tableName==QString::fromLocal8Bit("location"))
- {
- creat_sql = QString("CREATE TABLE location (\
- id INT PRIMARY KEY NOT NULL,\
- lbs_lat TEXT NOT NULL,\
- lbs_lng TEXT NOT NULL,\
- gps_lat TEXT NOT NULL,\
- gps_lng TEXT NOT NULL,\
- gps_elevation TEXT NOT NULL)");
- }
- if(tableName==QString::fromLocal8Bit("camera"))
- {
- creat_sql = QString("CREATE TABLE camera (\
- camera_type TEXT NOT NULL,\
- mode TEXT NOT NULL,\
- stitch_photonum INT NOT NULL,\
- photo_width INT NOT NULL,\
- photo_height INT NOT NULL,\
- auto_focus TEXT NOT NULL,\
- movZ_f TEXT NOT NULL,\
- movZ_b TEXT NOT NULL,\
- movX_f TEXT NOT NULL,\
- movX_b TEXT NOT NULL)");
- }
- if(tableName==QString::fromLocal8Bit("config"))
- {
- creat_sql = QString("CREATE TABLE config (\
- jsonCacheEnable INT NOT NULL,\
- imageCacheEnable INT NOT NULL,\
- maxJsonEntries INT NOT NULL,\
- maxImageEntries INT NOT NULL,\
- x_init INT NOT NULL,\
- y_init TEXT NOT NULL)");
- }
- sql_query.prepare(creat_sql);
- if(!sql_query.exec())
- {
- qDebug() << "Error: Fail to create table." << sql_query.lastError();
- }
- else
- {
- qDebug() << "Table created!" << creat_sql;
- }
- return 0;
- }
- int SqliteOperator::InsertData(config_db_type &singledb,QString tablename)
- {
- QSqlQuery sql_query;
- if(tablename == QString::fromLocal8Bit("mqtt")||tablename == QString::fromLocal8Bit("mqtt_other"))
- {
- 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));
- sql_query.bindValue(":id", singledb.mqtt_db.id);
- sql_query.bindValue(":dtype", singledb.mqtt_db.dtype);
- sql_query.bindValue(":ip", singledb.mqtt_db.ip);
- sql_query.bindValue(":port", singledb.mqtt_db.port);
- sql_query.bindValue(":imei",singledb.mqtt_db.imei);
- sql_query.bindValue(":uid",singledb.mqtt_db.uid);
- sql_query.bindValue(":pwd",singledb.mqtt_db.pwd);
- sql_query.bindValue(":clientid",singledb.mqtt_db.clientid);
- sql_query.bindValue(":qos",singledb.mqtt_db.qos);
- sql_query.bindValue(":keepalive",singledb.mqtt_db.keepalive);
- sql_query.bindValue(":sub",singledb.mqtt_db.sub);
- sql_query.bindValue(":pub",singledb.mqtt_db.pub);
- sql_query.bindValue(":offline",singledb.mqtt_db.offline);
- sql_query.bindValue(":dat_f",singledb.mqtt_db.dat_f);
- }
- else if(tablename == QString::fromLocal8Bit("ftp")||tablename == QString::fromLocal8Bit("ftp_other"))
- {
- sql_query.prepare(QString("INSERT INTO %1 VALUES(:id,:ip,:port,:dir,:uid,:pwd,:mode)").arg(tablename));
- sql_query.bindValue(":id", singledb.ftp_db.id);
- sql_query.bindValue(":ip", singledb.ftp_db.ip);
- sql_query.bindValue(":port", singledb.ftp_db.port);
- sql_query.bindValue(":dir",singledb.ftp_db.dir);
- sql_query.bindValue(":uid",singledb.ftp_db.uid);
- sql_query.bindValue(":pwd",singledb.ftp_db.pwd);
- sql_query.bindValue(":mode", singledb.ftp_db.mode);
- }
- else if(tablename == QString::fromLocal8Bit("location"))
- {
- sql_query.prepare(QString("INSERT INTO %1 VALUES(:id,:lbs_lat,:lbs_lng,:gps_lat,:gps_lng,:gps_elevation)").arg(tablename));
- sql_query.bindValue(":id", singledb.location_db.id);
- sql_query.bindValue(":lbs_lat", singledb.location_db.lbs_lat);
- sql_query.bindValue(":lbs_lng", singledb.location_db.lbs_lng);
- sql_query.bindValue(":gps_lat", singledb.location_db.gps_lat);
- sql_query.bindValue(":gps_lng", singledb.location_db.gps_lng);
- sql_query.bindValue(":gps_elevation", singledb.location_db.gps_elevation);
- }else if(tablename == QString::fromLocal8Bit("camera")){
- 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));
- sql_query.bindValue(":camera_type", singledb.camera_db.camera_type);
- sql_query.bindValue(":mode", singledb.camera_db.mode);
- sql_query.bindValue(":stitch_photonum", singledb.camera_db.stitch_photonum);
- sql_query.bindValue(":photo_width", singledb.camera_db.photo_width);
- sql_query.bindValue(":photo_height", singledb.camera_db.photo_height);
- sql_query.bindValue(":auto_focus",singledb.camera_db.auto_focus);
- sql_query.bindValue(":movZ_f",singledb.camera_db.movz_f);
- sql_query.bindValue(":movZ_b",singledb.camera_db.movz_b);
- sql_query.bindValue(":movX_f",singledb.camera_db.movx_f);
- sql_query.bindValue(":movX_b",singledb.camera_db.movx_b);
- }else if(tablename == QString::fromLocal8Bit("config")){
- sql_query.prepare(QString("INSERT INTO %1 VALUES(:jsonCacheEnable,:imageCacheEnable,:maxJsonEntries,:maxImageEntries,:x_init,:y_init)").arg(tablename));
- sql_query.bindValue(":jsonCacheEnable", singledb.config_db.jsonCacheEnable);
- sql_query.bindValue(":imageCacheEnable", singledb.config_db.imageCacheEnable);
- sql_query.bindValue(":maxJsonEntries", singledb.config_db.maxJsonEntries);
- sql_query.bindValue(":maxImageEntries", singledb.config_db.maxImageEntries);
- sql_query.bindValue(":x_init", singledb.config_db.x_init);
- sql_query.bindValue(":y_init",singledb.config_db.y_init);
- }
- if(!sql_query.exec())
- {
- qDebug() <<"Error: Fail to insert data:"<< tablename << sql_query.lastError();
- }
- else
- {
- LOG("InsertData Success!");
- }
- return 0;
- }
- void InertListConfig(QList<config_mqtt_db> &moreData,QString tablename)
- {
- // 进行多个数据的插入时,可以利用绑定进行批处理
- QSqlQuery sqlQuery;
- sqlQuery.prepare("INSERT INFO mqtt VALUES(?,?,?)");
- QVariantList idList;
- }
- int SqliteOperator::UpdateData(QString tablename, QString name, QString value)
- {
- LOG("UpdateData:{}-{}-{}",tablename.toStdString(),name.toStdString(),value.toStdString());
- //QString update_sql = "UPDATE mqtt SET ip = :ip WHERE id = :id";
- QSqlQuery sql_query;
- sql_query.prepare(QString("UPDATE %1 SET %2=?").arg(tablename,name));
- sql_query.addBindValue(value);
- if(!sql_query.exec())
- {
- ERROR("update Failed!");
- }else{
- LOG("updated Success!");
- }
- return 0;
- }
- int SqliteOperator::QueryAllData(QString& table_name)
- {
- QSqlQuery sql_query;
- int data_num = 0;
- sql_query.exec(QString("SELECT * FROM %1").arg(table_name));
- if(!sql_query.exec())
- {
- qDebug() << "Error: Fail to query table. " << sql_query.lastError();
- }
- else
- {
- while(sql_query.next())
- {
- data_num ++;
- if(table_name == QString::fromLocal8Bit("mqtt"))
- {
- //param_mqtt_main["id"] = sql_query.value(0).toInt();
- param::dtype = sql_query.value(1).toString();
- param::mqtt_main_ip = sql_query.value(2).toString();
- param::mqtt_main_port = sql_query.value(3).toInt();
- if(param::imei.size()<10)
- param::imei = sql_query.value(4).toString();
- param::mqtt_main_uid = sql_query.value(5).toString();
- param::mqtt_main_pwd = sql_query.value(6).toString();
- param::mqtt_main_clientid = sql_query.value(7).toString();
- param::mqtt_main_qos = sql_query.value(8).toInt();
- param::mqtt_main_keepalive = sql_query.value(9).toInt();
- param::mqtt_main_sub = sql_query.value(10).toString();
- param::mqtt_main_pub = sql_query.value(11).toString();
- param::mqtt_main_offline = sql_query.value(12).toString();
- param::dat_f_main = sql_query.value(13).toInt();
- LOG("ip:{}",param::mqtt_main_ip.toStdString());
- LOG("dat_f:{}",param::dat_f_main);
- }
- else if(table_name == QString::fromLocal8Bit("mqtt_other"))
- {
- //param_mqtt_other["id"] = sql_query.value(0).toInt();
- //param_mqtt_other["dtype"] = sql_query.value(1).toString();
- param::mqtt_other_ip = sql_query.value(2).toString();
- param::mqtt_other_port = sql_query.value(3).toInt();
- //param_mqtt_other["imei"] = sql_query.value(4).toString();
- param::mqtt_other_uid = sql_query.value(5).toString();
- param::mqtt_other_pwd = sql_query.value(6).toString();
- param::mqtt_other_clientid = sql_query.value(7).toString();
- param::mqtt_other_qos = sql_query.value(8).toInt();
- param::mqtt_other_keepalive = sql_query.value(9).toInt();
- param::mqtt_other_sub = sql_query.value(10).toString();
- param::mqtt_other_pub = sql_query.value(11).toString();
- param::mqtt_other_offline = sql_query.value(12).toString();
- param::dat_f_other = sql_query.value(13).toInt();
- }
- else if(table_name == QString::fromLocal8Bit("ftp"))
- {
- param::ftp_main_ip = sql_query.value(1).toString();
- param::ftp_main_port = sql_query.value(2).toInt();
- param::ftp_main_dir = sql_query.value(3).toString();
- param::ftp_main_uid = sql_query.value(4).toString();
- param::ftp_main_pwd = sql_query.value(5).toString();
- param::ftp_main_mode = sql_query.value(6).toInt();
- }
- else if(table_name == QString::fromLocal8Bit("ftp_opther"))
- {
- param::ftp_other_ip = sql_query.value(1).toString();
- param::ftp_other_port = sql_query.value(2).toInt();
- param::ftp_other_dir = sql_query.value(3).toString();
- param::ftp_other_uid = sql_query.value(4).toString();
- param::ftp_other_pwd = sql_query.value(5).toString();
- param::ftp_other_mode = sql_query.value(6).toInt();
- }
- else if(table_name == QString::fromLocal8Bit("location"))
- {
- param::lbs_lat = sql_query.value(1).toString();
- param::lbs_lng = sql_query.value(2).toString();
- param::gps_lat = sql_query.value(3).toString();
- param::gps_lng = sql_query.value(4).toString();
- param::gps_elevation = sql_query.value(5).toString();
- }else if(table_name == QString::fromLocal8Bit("camera"))
- {
- param::camera_type = sql_query.value(0).toString();
- param::stitch_mode = sql_query.value(1).toString();
- param::stitch_photonum = sql_query.value(2).toInt();
- param::photo_width = sql_query.value(3).toInt();
- param::photo_height = sql_query.value(4).toInt();
- param::auto_focus = sql_query.value(5).toString();
- param::movZ_f_l = sql_query.value(6).toString();
- param::movZ_b_l = sql_query.value(7).toString();
- param::movX_f_l = sql_query.value(8).toString();
- param::movX_b_l = sql_query.value(9).toString();
- LOG("camera_type:{}",param::camera_type.toStdString());
- LOG("stitch_mode:{}",param::stitch_mode.toStdString());
- LOG("stitch_photonum:{}",param::stitch_photonum);
- LOG("photo_width:{}",param::photo_width);
- LOG("photo_height:{}",param::photo_height);
- if(param::movZ_f_l.isEmpty()||param::movZ_f_l==""){
- param::movZ_f_l = movZ_f;
- }
- if(param::movZ_b_l.isEmpty()||param::movZ_b_l==""){
- param::movZ_b_l = movZ_b;
- }
- if(param::movX_f_l.isEmpty()||param::movX_f_l==""){
- param::movX_f_l = movX_f;
- }
- if(param::movX_b_l.isEmpty()||param::movX_b_l==""){
- param::movX_b_l = movX_b;
- }
- LOG("movZ_f_l:{}",param::movZ_f_l.toStdString());
- LOG("movZ_b_l:{}",param::movZ_b_l.toStdString());
- LOG("movX_f_l:{}",param::movX_f_l.toStdString());
- LOG("movX_b_l:{}",param::movX_b_l.toStdString());
- }else if(table_name == QString::fromLocal8Bit("config"))
- {
- param::jsonCacheEnable = sql_query.value(0).toInt();
- param::imageCacheEnable = sql_query.value(1).toInt();
- param::maxJsonEntries = sql_query.value(2).toInt();
- param::maxImageEntries = sql_query.value(3).toInt();
- param::x_init = sql_query.value(4).toInt();
- param::y_init = sql_query.value(5).toInt();
- LOG("jsonCacheEnable:{}",param::jsonCacheEnable);
- LOG("imageCacheEnable:{}",param::imageCacheEnable);
- LOG("maxJsonEntries:{}",param::maxJsonEntries);
- LOG("maxImageEntries:{}",param::maxImageEntries);
- LOG("x_init:{}",param::x_init);
- LOG("y_init:{}",param::y_init);
- }
- LOG("data_num:{}",data_num);
- }
- }
- return 0;
- }
- int SqliteOperator::QueryData()
- {
- QString select_sql = QString("select * from student where name = '%1' and (age = '%2' or age = '%3')")
- .arg("Wang")
- .arg(30)
- .arg(25);
- QSqlQuery sql_query;
- if(!sql_query.exec(select_sql))
- {
- qDebug()<<sql_query.lastError();
- }
- else
- {
- while(sql_query.next())
- {
- int id = sql_query.value(0).toInt();
- QString name = sql_query.value(1).toString();
- qDebug()<<QString("id:%1 name:%2").arg(id).arg(name);
- }
- }
- return 0;
- }
- DatabaseInterface::DatabaseInterface(QObject *parent) : QObject(parent)
- {
- }
- bool DatabaseInterface::openDatabase(const QString &dbName)
- {
- #if ANDROID
- QString db_Path = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation);
- QString db_name = db_Path + "/" + dbName;
- #else
- QString db_Path = "./";
- QString db_name = db_Path + dbName;
- #endif
- m_database = QSqlDatabase::addDatabase("QSQLITE");
- m_database.setDatabaseName(db_name);
- if (!m_database.open()) {
- qDebug() << "Error: Failed to connect database." << m_database.lastError();
- return false;
- }
- return true;
- }
- bool DatabaseInterface::closeDatabase()
- {
- m_database.close();
- return true;
- }
- QString DatabaseInterface::getTable_row(QString tablename)
- {
- QSqlQuery query(m_database);
- if(tablename == QString::fromLocal8Bit("data"))
- {
- query.prepare("SELECT COUNT(*) FROM data;");
- if(query.exec()&&query.next())
- {
- return query.value(0).toString();
- }
- }
- return 0;
- }
- bool DatabaseInterface::createTable(QString tableName)
- {
- /* 删除表格 */
- // QString clear_sql = "drop table " + tableName; /* 没有用where给出限制,就会删除所有内容 */
- // sql_query.prepare(clear_sql);
- // if(!sql_query.exec())
- // {
- // qDebug() << sql_query.lastError();
- // }
- // else
- // {
- // qDebug() << "table" << tableName << "cleared";
- // }
- QSqlQuery sql_query(m_database);
- QString creat_sql;
- if(tableName == QString::fromLocal8Bit("mqtt"))
- {
- creat_sql = QString("CREATE TABLE mqtt (\
- id INT PRIMARY KEY NOT NULL,\
- dtype TEXT NOT NULL,\
- ip TEXT NOT NULL,\
- port INT NOT NULL,\
- imei TEXT NOT NULL,\
- uid TEXT NOT NULL,\
- pwd TEXT NOT NULL,\
- clientid TEXT,\
- qos INT NOT NULL,\
- keepalive INT NOT NULL,\
- sub TEXT NOT NULL,\
- pub TEXT NOT NULL,\
- offline TEXT NOT NULL,\
- dat_f INT NOT NULL)");
- }
- if(tableName == QString::fromLocal8Bit("mqtt_other"))
- {
- creat_sql = QString("CREATE TABLE mqtt_other (\
- id INT PRIMARY KEY NOT NULL,\
- dtype TEXT NOT NULL,\
- ip TEXT NOT NULL,\
- port INT NOT NULL,\
- imei TEXT NOT NULL,\
- uid TEXT NOT NULL,\
- pwd TEXT NOT NULL,\
- clientid TEXT,\
- qos INT NOT NULL,\
- keepalive INT NOT NULL,\
- sub TEXT NOT NULL,\
- pub TEXT NOT NULL,\
- offline TEXT NOT NULL,\
- dat_f INT NOT NULL)");
- }
- if(tableName == QString::fromLocal8Bit("ftp"))
- {
- creat_sql = QString("CREATE TABLE ftp (\
- id INT PRIMARY KEY NOT NULL,\
- ip TEXT NOT NULL,\
- port INT NOT NULL,\
- dir TEXT NOT NULL,\
- uid TEXT NOT NULL,\
- pwd TEXT NOT NULL,\
- mode INT NOT NULL)");
- }
- if(tableName == QString::fromLocal8Bit("ftp_other"))
- {
- creat_sql = QString("CREATE TABLE ftp_other (\
- id INT PRIMARY KEY NOT NULL,\
- ip TEXT NOT NULL,\
- port INT NOT NULL,\
- dir TEXT NOT NULL,\
- uid TEXT NOT NULL,\
- pwd TEXT NOT NULL,\
- mode INT NOT NULL)");
- }
- if(tableName==QString::fromLocal8Bit("location"))
- {
- creat_sql = QString("CREATE TABLE location (\
- id INT PRIMARY KEY NOT NULL,\
- lbs_lat TEXT NOT NULL,\
- lbs_lng TEXT NOT NULL,\
- gps_lat TEXT NOT NULL,\
- gps_lng TEXT NOT NULL,\
- gps_elevation TEXT NOT NULL)");
- }
- if(tableName==QString::fromLocal8Bit("camera"))
- {
- creat_sql = QString("CREATE TABLE camera (\
- camera_type TEXT NOT NULL,\
- mode TEXT NOT NULL,\
- stitch_photonum INT NOT NULL,\
- photo_width INT NOT NULL,\
- photo_height INT NOT NULL,\
- auto_focus TEXT NOT NULL)");
- }
- sql_query.prepare(creat_sql);
- if(!sql_query.exec())
- {
- qDebug() << "Error: Fail to create table." << sql_query.lastError();
- return false;
- }else{
- qDebug() << "Table created!" << creat_sql;
- }
- return true;
- }
- bool DatabaseInterface::insertData(const QString &name, const QString &address)
- {
- QSqlQuery query(m_database);
- query.prepare("INSERT INTO Info (Name, Address) VALUES (:name, :address)");
- query.bindValue(":name", name);
- query.bindValue(":address", address);
- if (!query.exec()) {
- qDebug() << "Error: Failed to insert data." << query.lastError();
- return false;
- }
- return true;
- }
- bool DatabaseInterface::deleteData(int id)
- {
- QSqlQuery query(m_database);
- query.prepare("DELETE FROM Info WHERE ID = :id");
- query.bindValue(":id", id);
- if (!query.exec()) {
- qDebug() << "Error: Failed to delete data." << query.lastError();
- return false;
- }
- return true;
- }
- bool DatabaseInterface::updateData(QString tablename, QString name, QVariant value)
- {
- QSqlQuery query(m_database);
- qDebug() << "Table name:" <<tablename << name << value;
- query.prepare(QString("UPDATE %1 SET %2=?").arg(tablename,name));
- query.addBindValue(value);
- if (!query.exec()) {
- qDebug() << "Error: Failed to update data." << query.lastError();
- return false;
- }
- return true;
- }
- QJsonArray DatabaseInterface::queryData(QString table_name,int count_index)
- {
- //清空json数组
- // for(int i=0;i<param::mqtt_array.size();i++)
- // {
- // param::mqtt_array.removeFirst();
- // }
- QJsonArray mqtt_array;
- QJsonObject param_mqtt_main,param_mqtt_other,param_ftp_main,param_ftp_other,param_location,param_camera,json_mqtt;
- QSqlQuery sql_query;
- int data_num = 0;
- if(table_name==QString::fromLocal8Bit("data"))
- {
- sql_query.exec(QString("SELECT * FROM %1 WHERE id = ?").arg(table_name));
- for(data_num=(count_index-1)*10;data_num <10*count_index;data_num++)
- {
- sql_query.bindValue(0,data_num);
- if (sql_query.exec())
- {
- while(sql_query.next())
- {
- json_mqtt["id"] = sql_query.value(0).toInt();
- json_mqtt["datetime"] = sql_query.value(1).toString();
- json_mqtt["Json_data"] = sql_query.value(2).toString();
- json_mqtt["Json_warn"] = sql_query.value(3).toString();
- mqtt_array.append(json_mqtt);
- }
- }
- }
- qDebug() <<"json data:"<< mqtt_array;
- return mqtt_array;
- }
- sql_query.exec(QString("SELECT * FROM %1").arg(table_name));
- if(!sql_query.exec())
- {
- qDebug() << "Error: Fail to query table. " << sql_query.lastError();
- }
- else
- {
- while(sql_query.next())
- {
- data_num ++;
- if(table_name == QString::fromLocal8Bit("mqtt"))
- {
- qDebug() << "mqtt";
- param_mqtt_main["id"] = sql_query.value(0).toInt();
- param_mqtt_main["dtype"] = sql_query.value(1).toString();
- param_mqtt_main["ip"] = sql_query.value(2).toString();
- param_mqtt_main["port"] = sql_query.value(3).toInt();
- param_mqtt_main["imei"] = sql_query.value(4).toString();
- param_mqtt_main["uid"] = sql_query.value(5).toString();
- param_mqtt_main["pwd"] = sql_query.value(6).toString();
- param_mqtt_main["clientid"] = sql_query.value(7).toString();
- param_mqtt_main["qos"] = sql_query.value(8).toInt();
- param_mqtt_main["keepalive"] = sql_query.value(9).toInt();
- param_mqtt_main["sub"] = sql_query.value(10).toString();
- param_mqtt_main["pub"] = sql_query.value(11).toString();
- param_mqtt_main["offline"] = sql_query.value(12).toString();
- mqtt_array.append(param_mqtt_main);
- }
- else if(table_name == QString::fromLocal8Bit("mqtt_other"))
- {
- qDebug() << "mqtt_other";
- param_mqtt_other["id"] = sql_query.value(0).toInt();
- param_mqtt_other["dtype"] = sql_query.value(1).toString();
- param_mqtt_other["ip"] = sql_query.value(2).toString();
- param_mqtt_other["port"] = sql_query.value(3).toInt();
- param_mqtt_other["imei"] = sql_query.value(4).toString();
- param_mqtt_other["uid"] = sql_query.value(5).toString();
- param_mqtt_other["pwd"] = sql_query.value(6).toString();
- param_mqtt_other["clientid"] = sql_query.value(7).toString();
- param_mqtt_other["qos"] = sql_query.value(8).toInt();
- param_mqtt_other["keepalive"] = sql_query.value(9).toInt();
- param_mqtt_other["sub"] = sql_query.value(10).toString();
- param_mqtt_other["pub"] = sql_query.value(11).toString();
- param_mqtt_other["offline"] = sql_query.value(12).toString();
- mqtt_array.append(param_mqtt_other);
- }
- else if(table_name == QString::fromLocal8Bit("ftp"))
- {
- param_ftp_main["id"] = sql_query.value(0).toInt();
- param_ftp_main["ip"] = sql_query.value(1).toString();
- param_ftp_main["port"] = sql_query.value(2).toInt();
- param_ftp_main["dir"] = sql_query.value(3).toString();
- param_ftp_main["uid"] = sql_query.value(4).toString();
- param_ftp_main["pwd"] = sql_query.value(5).toString();
- param_ftp_main["mode"] = sql_query.value(6).toInt();
- mqtt_array.append(param_ftp_main);
- }
- else if(table_name == QString::fromLocal8Bit("ftp_other"))
- {
- param_ftp_other["id"] = sql_query.value(0).toInt();
- param_ftp_other["ip"] = sql_query.value(1).toString();
- param_ftp_other["port"] = sql_query.value(2).toInt();
- param_ftp_other["dir"] = sql_query.value(3).toString();
- param_ftp_other["uid"] = sql_query.value(4).toString();
- param_ftp_other["pwd"] = sql_query.value(5).toString();
- param_ftp_other["mode"] = sql_query.value(6).toInt();
- mqtt_array.append(param_ftp_other);
- }
- else if(table_name == QString::fromLocal8Bit("location"))
- {
- param_location["id"] = sql_query.value(0).toInt();
- param_location["lbs_lat"] = sql_query.value(1).toString();
- param_location["lbs_lng"] = sql_query.value(2).toString();
- param_location["gps_lat"] = sql_query.value(3).toString();
- param_location["gps_lng"] = sql_query.value(4).toString();
- param_location["gps_elevation"] = sql_query.value(5).toString();
- mqtt_array.append(param_location);
- }else if(table_name == QString::fromLocal8Bit("camera"))
- {
- param_camera["camera_type"] = sql_query.value(0).toString();
- param_camera["mode"] = sql_query.value(1).toString();
- param_camera["stitch_photonum"] = sql_query.value(2).toInt();
- param_camera["photo_width"] = sql_query.value(3).toInt();
- param_camera["photo_height"] = sql_query.value(4).toInt();
- param_camera["auto_focus"] = sql_query.value(5).toString();
- param_camera["movz_f"] = sql_query.value(6).toString().isEmpty() ? param::movZ_f_l : "null";
- param_camera["movz_b"] = sql_query.value(7).toString().isEmpty() ? param::movZ_b_l : "null";
- param_camera["movx_f"] = sql_query.value(8).toString().isNull() ? param::movX_f_l : "null";
- param_camera["movx_b"] = sql_query.value(9).toString().isEmpty() ? param::movX_b_l : "null";
- mqtt_array.append(param_camera);
- }
- qDebug() << "data num: " << data_num;
- }
- qDebug() <<"json data:"<< mqtt_array;
- }
- return mqtt_array;
- }
|