//
|
// Created by pans on 4/28/18.
|
//
|
#include <iostream>
|
|
#include <QtSql/QSql>
|
#include <QtSql/QSqlDatabase>
|
#include <QtSql/QSqlError>
|
#include <QtSql/QSqlQuery>
|
#include <QtCore/QString>
|
#include <QtCore/QDebug>
|
#include <QtCore/QVariantList>
|
#include <basic/debug/Debug.h>
|
|
#include "SqliteFaceEncap.h"
|
|
using namespace std;
|
|
|
static QSqlDatabase g_db = QSqlDatabase::addDatabase("QSQLITE", "GBond");
|
|
SqliteFaceEncap::SqliteFaceEncap(std::string t_string) : sqliteEncapsulation(t_string.c_str()) {
|
/* Open database */
|
int rc;
|
string sql;
|
bool initRet = false;
|
bool *test = &initRet;
|
//--------------init db-------------------
|
rc = sqlite3_open(t_string.c_str(), &db);
|
string initRetSql = "select count(*) from sqlite_master where type='table' and name = '" + g_tableName + "';";
|
rc = sqlite3_exec(db, initRetSql.c_str(), isExist, (void *) test, &zErrMsg);
|
if (rc != SQLITE_OK) {
|
SQLERR(zErrMsg);
|
ERR(initRetSql);
|
sqlite3_free(zErrMsg);
|
} else {
|
if (!initRet) {
|
/* Create SQL statement */
|
sql = getInitDBSql();
|
/* Execute SQL statement */
|
rc = sqlite3_exec(db, sql.c_str(), NULL, 0, &zErrMsg);
|
if (rc != SQLITE_OK) {
|
SQLERR(zErrMsg);
|
ERR(sql);
|
sqlite3_free(zErrMsg);
|
} else {
|
fprintf(stdout, "sys_o_tables created successfully\n");
|
}
|
fprintf(stdout, "Operation done successfully\n");
|
}
|
}
|
//--------------init db-------------------
|
g_db.setDatabaseName(t_string.c_str());
|
g_db.open();
|
|
}
|
|
//#todo
|
bool SqliteFaceEncap::createTable(std::string t_tableName, FieldValues &fieldValues) {
|
int rc;
|
//--------------create table-------------------
|
/* Create SQL statement */
|
string sql = getCreateFaceTableSql(t_tableName);
|
|
/* Execute SQL statement */
|
// cout << sql << endl;
|
rc = sqlite3_exec(db, sql.c_str(), NULL, 0, &zErrMsg);
|
if (rc != SQLITE_OK) {
|
SQLERR(zErrMsg);
|
ERR(sql);
|
sqlite3_free(zErrMsg);
|
return false;
|
} else {
|
fprintf(stdout, "Table created successfully\n");
|
// INSERT INTO sys_o_tables(name,people_name,type,color,table_name,table_type,is_self,is_long_control)
|
// VALUES ('在逃库','在逃人员','人','red','taseee',2,0,0);
|
// FieldValues fieldValues;
|
// fieldValues.insert(std::make_pair("name","在逃库"));
|
// fieldValues.insert(std::make_pair("people_name","在逃人员"));
|
// fieldValues.insert(std::make_pair("type","人"));
|
// fieldValues.insert(std::make_pair("color","red"));
|
// fieldValues.insert(std::make_pair("table_name",t_tableName));
|
// fieldValues.insert(std::make_pair("table_type","2"));
|
// fieldValues.insert(std::make_pair("is_self","0"));
|
// fieldValues.insert(std::make_pair("is_long_control","0"));
|
string insertSql = getInsertSql("sys_o_tables", fieldValues);
|
/* Execute SQL statement */
|
rc = sqlite3_exec(db, insertSql.c_str(), NULL, 0, &zErrMsg);
|
if (rc != SQLITE_OK) {
|
SQLERR(zErrMsg);
|
ERR(insertSql);
|
sqlite3_free(zErrMsg);
|
//#todo throw exp
|
// throw exception("insert fail");
|
} else {
|
fprintf(stdout, " insert data successfully\n");
|
}
|
}
|
//--------------create table-------------------
|
return true;
|
}
|
|
|
bool SqliteFaceEncap::updateTable(std::string t_tableName, FieldValues &fieldValues) {
|
int rc;
|
//--------------create table-------------------
|
/* Create SQL statement */
|
//#todo
|
string sql = getUpdateFaceTableSql(t_tableName, fieldValues);
|
|
//#todo create facefea
|
|
/* Execute SQL statement */
|
// cout << sql << endl;
|
rc = sqlite3_exec(db, sql.c_str(), NULL, 0, &zErrMsg);
|
if (rc != SQLITE_OK) {
|
SQLERR(zErrMsg);
|
ERR(sql);
|
sqlite3_free(zErrMsg);
|
return false;
|
} else {
|
fprintf(stdout, "updateTable successfully\n");
|
}
|
//--------------create table-------------------
|
return true;
|
}
|
|
//#todo backup data?
|
bool SqliteFaceEncap::deleteTable(std::string t_tableName) {
|
string delTableSql = getDeleteFaceTableSql(t_tableName);
|
int rc = sqlite3_exec(db, delTableSql.c_str(), NULL, 0, &zErrMsg);
|
if (rc != SQLITE_OK) {
|
SQLERR(zErrMsg);
|
ERR(delTableSql);
|
sqlite3_free(zErrMsg);
|
return false;
|
} else {
|
fprintf(stdout, "Table deleted successfully\n");
|
FieldValues fieldValues;
|
fieldValues.insert(std::make_pair("tableName", t_tableName));
|
string deleteSql = getDeleteSql("sys_o_tables", fieldValues);
|
// cout << deleteSql << endl;
|
/* Execute SQL statement */
|
rc = sqlite3_exec(db, deleteSql.c_str(), NULL, 0, &zErrMsg);
|
if (rc != SQLITE_OK) {
|
SQLERR(zErrMsg);
|
ERR(deleteSql);
|
sqlite3_free(zErrMsg);
|
//#todo throw exp
|
// throw exception("insert fail");
|
} else {
|
fprintf(stdout, " delete data successfully\n");
|
}
|
}
|
return true;
|
}
|
|
|
bool SqliteFaceEncap::updateFace(std::string t_tableName, AddFaceData &faceData, FieldValues &fieldValues) {
|
|
// updateFaceData
|
bool ret = updateFaceData(t_tableName + "_fea", faceData);
|
updateFaceInfo(t_tableName, fieldValues);
|
return ret;
|
}
|
|
bool SqliteFaceEncap::updateFaceData(std::string t_tableName, AddFaceData &faceData) {
|
|
QByteArray qFea((char *) faceData.feature.data(), faceData.feature.size());
|
QSqlQuery query(g_db);
|
QString strSql = "update ";
|
strSql.append(t_tableName.c_str());
|
if (faceData.feature.size() > 0 || faceData.faceUrl.size() > 0) {
|
strSql.append(" set ");
|
|
if (faceData.feature.size() > 0) {
|
strSql.append(" feature = :feature ,");
|
}
|
if (faceData.faceUrl.size() > 0) {
|
strSql.append(" faceUrl = :faceUrl ,");
|
}
|
// if (faceData.enabled.size() > 0) {
|
// strSql.append(" enabled = :enabled ,");
|
// }
|
|
//#todo
|
strSql.append(" update_time = '2010-01-03 01:01:00' ");
|
|
strSql.append(" where del_flag = 0 and uuid = '" + QString::fromStdString(faceData.uuid) + "';");
|
query.prepare(strSql);
|
// query.addBindValue(QString::fromStdString(faceData.uuid));
|
if (faceData.feature.size() > 0) {
|
query.addBindValue(qFea);
|
}
|
if (faceData.faceUrl.size() > 0) {
|
query.addBindValue(QString::fromStdString(faceData.faceUrl));
|
}
|
// if (faceData.enabled.size() > 0) {
|
// query.addBindValue(QString::fromStdString(faceData.enabled));
|
// }
|
|
if (!query.exec()) {
|
ERR("updateFaceData fail");
|
qDebug() << "updateFaceData" << __FILE__ << __LINE__ <<
|
query.lastQuery() << " " << query.lastError();
|
} else {
|
DBG("更新记录成功 " << faceData.uuid);
|
return true;
|
}
|
}
|
|
return false;
|
}
|
|
//#wait todo
|
bool SqliteFaceEncap::updateFaceInfo(std::string t_tableName, FieldValues &fieldValues) {
|
std::string sql = getUpdateFaceTableSql(t_tableName, fieldValues);
|
int rc = sqlite3_exec(db, sql.c_str(), NULL, 0, &zErrMsg);
|
if (rc != SQLITE_OK) {
|
SQLERR(zErrMsg)
|
sqlite3_free(zErrMsg);
|
return false;
|
} else {
|
fprintf(stdout, "addFaceInfo successfully\n");
|
}
|
return true;
|
}//#todo
|
|
std::string SqliteFaceEncap::addFace(std::string t_tableName, AddFaceData &faceData, FieldValues &fieldValues) {
|
std::string uuid = addFaceData(t_tableName + "_fea", faceData);
|
if (uuid.size() > 0) {
|
fieldValues["uuid"] = uuid;
|
addFaceInfo(t_tableName, fieldValues);
|
}
|
return uuid;
|
}
|
|
//INSERT INTO aaaFace (id, feature, del_flag ) VALUES (12,'sdfasdfasdfasdf',0);
|
std::string SqliteFaceEncap::addFaceData(std::string t_tableName, AddFaceData &faceData) {
|
|
QByteArray qFea((char *) faceData.feature.data(), faceData.feature.size());
|
QSqlQuery query(g_db);
|
QString strSql = "INSERT INTO ";
|
strSql.append(t_tableName.c_str());
|
|
strSql.append("(uuid,feature,faceUrl) VALUES (:uuid,:feature,:faceUrl);");
|
query.prepare(strSql);
|
query.addBindValue(QString::fromStdString(faceData.uuid));
|
query.addBindValue(qFea);
|
// cout << faceData.faceUrl << endl;
|
query.addBindValue(QString::fromStdString(faceData.faceUrl));
|
|
if (!query.exec()) {
|
ERR("addFaceData fail");
|
qDebug() << "addFaceData" << __FILE__ << __LINE__ << query.lastError();
|
return "";
|
} else {
|
DBG("插入记录成功 " << faceData.uuid);
|
}
|
return faceData.uuid;
|
}
|
|
bool SqliteFaceEncap::addFaceInfo(std::string t_tableName, FieldValues &fieldValues) {
|
std::string sql = getInsertSql(t_tableName, fieldValues);
|
int rc = sqlite3_exec(db, sql.c_str(), NULL, 0, &zErrMsg);
|
if (rc != SQLITE_OK) {
|
SQLERR(zErrMsg)
|
sqlite3_free(zErrMsg);
|
return false;
|
} else {
|
fprintf(stdout, "addFaceInfo successfully\n");
|
}
|
return true;
|
}
|
|
bool SqliteFaceEncap::deleteFace(std::string t_tableName, std::string uuid) {
|
deleteFaceData(t_tableName, uuid);
|
std::string t_tmp(t_tableName);
|
t_tmp.append("_fea");
|
bool ret = deleteFaceData(t_tmp.c_str(), uuid);
|
|
return ret;
|
}
|
|
//DELETE FROM aaaFace where 1=1 and id = 122
|
//#todo update del_flag ?
|
bool SqliteFaceEncap::deleteFaceData(std::string t_tableName, std::string uuid) {
|
// FieldValues fieldValues;
|
// fieldValues.insert(std::make_pair("uuid", uuid));
|
// string deleteSql = getDeleteSql(t_tableName, fieldValues);
|
string deleteSql = "update ";
|
deleteSql.append(t_tableName + " set del_flag = 1 ,update_time = '" + AppUtil::getTimeSecString() + "' ");
|
deleteSql.append(" where uuid = '" + uuid + "' ");
|
// cout << deleteSql << endl;
|
/* Execute SQL statement */
|
int rc = sqlite3_exec(db, deleteSql.c_str(), NULL, 0, &zErrMsg);
|
if (rc != SQLITE_OK) {
|
SQLERR(zErrMsg)
|
sqlite3_free(zErrMsg);
|
//#todo throw exp
|
// throw exception("insert fail");
|
return false;
|
} else {
|
fprintf(stdout, " delete data successfully\n");
|
}
|
return true;
|
}
|
|
std::vector<string> SqliteFaceEncap::getTableNameList(std::string type) {
|
std::vector<string> tables;
|
QString sql = QString::fromStdString(getTableListSqlWithType(type));
|
QSqlQuery query(g_db);
|
// qDebug() << strSql;
|
query.prepare(sql);
|
if (!query.exec()) {
|
qDebug() << "getTableNameList" << __FILE__ << __LINE__ << query.lastError();
|
} else {
|
while (query.next()) {
|
string tableName = query.value(0).toString().toStdString();
|
tables.push_back(tableName);
|
}
|
}
|
return tables;
|
}
|
|
FeatureDBWithUrlCache *SqliteFaceEncap::getFacesFromTable(std::string tableName) {
|
ClockTimer clk("loading " + tableName);
|
FeatureDBWithUrlCache &t_db = m_dbCache[tableName];
|
QString sql = QString::fromStdString(getFacesFromTableSql(tableName));
|
QSqlQuery query(g_db);
|
query.prepare(sql);
|
if (!query.exec()) {
|
qDebug() << "getFacesFromTable" << __FILE__ << __LINE__ << query.lastError();
|
} else {
|
while (query.next()) {
|
int del_flag = query.value(4).toInt();
|
std::string uuid = query.value(0).toString().toStdString();
|
if (del_flag) {
|
t_db.erase(uuid);
|
} else {
|
FaceFeatureWithUrl &t_feas = t_db[uuid];
|
t_feas.uuid = uuid;
|
QByteArray t_fea = query.value(1).toByteArray();
|
// std::cout << t_fea.size() << std::endl;
|
t_feas.faceFeature.resize(t_fea.size());
|
memcpy(t_feas.faceFeature.data(), t_fea.data(), t_fea.size());
|
// string create_time = query.value(2).toString().toStdString();
|
t_feas.faceurl = query.value(3).toString().toStdString();
|
}
|
}
|
}
|
return &t_db;
|
}
|
|
|
FeatureDBWithUrlCache SqliteFaceEncap::getFacesFromTableWhereDate(std::string tableName, std::string date) {
|
//#todo not used del_flag
|
|
return FeatureDBWithUrlCache();
|
}
|
|
TableInfos SqliteFaceEncap::getTableInfos() {
|
TableInfos tableInfos;
|
QString sql = QString::fromStdString(getTableInfosSql());
|
qDebug() << sql;
|
QSqlQuery query(g_db);
|
query.prepare(sql);
|
if (!query.exec()) {
|
qDebug() << "getTableInfos" << __FILE__ << __LINE__ << query.lastError() << " " << query.lastQuery();
|
} else {
|
while (query.next()) {
|
TableInfo tableInfo;
|
tableInfo.uuid = query.value(0).toString().toStdString();
|
tableInfo.tableName = query.value(1).toString().toStdString();
|
tableInfo.tableType = query.value(2).toString().toStdString();
|
tableInfo.bwType = query.value(3).toString().toStdString();
|
tableInfo.startTime = query.value(4).toString().toStdString();
|
tableInfo.endTime = query.value(5).toString().toStdString();
|
tableInfo.uploadFlag = query.value(6).toString().toStdString();
|
tableInfo.cmpThreshold = query.value(7).toString().toStdString();
|
tableInfo.enabled = query.value(8).toString().toStdString();
|
tableInfo.createBy = query.value(9).toString().toStdString();
|
|
tableInfos.push_back(tableInfo);
|
}
|
}
|
return tableInfos;
|
}
|
|
FaceInfosCache SqliteFaceEncap::getFaceInfoFromTable(std::string tableName) {
|
FaceInfosCache faceInfosCache;
|
QString sql = QString::fromStdString(getTableInfosSql(tableName));
|
std::lock_guard<std::mutex> dataGuard(dataMtx);
|
QSqlQuery query(g_db);
|
query.prepare(sql);
|
if (!query.exec()) {
|
qDebug() << "getTableNameList" << __FILE__ << __LINE__ << query.lastError();
|
} else {
|
while (query.next()) {
|
int del_falg = query.value(9).toInt();
|
if (del_falg == 1) {
|
continue;
|
}
|
FaceInfo tableInfo;
|
tableInfo.uuid = query.value(0).toString().toStdString();
|
tableInfo.personName = query.value(1).toString().toStdString();
|
tableInfo.age = query.value(2).toString().toStdString();
|
tableInfo.sex = query.value(3).toString().toStdString();
|
tableInfo.idCard = query.value(4).toString().toStdString();
|
tableInfo.phoneNum = query.value(5).toString().toStdString();
|
tableInfo.enable = query.value(10).toString().toStdString();
|
tableInfo.monLevel = query.value(11).toString().toStdString();
|
faceInfosCache.insert(std::make_pair(tableInfo.uuid, tableInfo));
|
}
|
}
|
return faceInfosCache;
|
}
|
