From 99221e4721a1246883465c2b52fbff14eb9aaa7a Mon Sep 17 00:00:00 2001
From: pans <pansen626@sina.com>
Date: 星期六, 30 三月 2019 12:12:02 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/yangwu1.3' into yangwu1.3
---
QiaoJiaSystem/LocalDBTool/SqliteToolkit.hpp | 118 ++++++++++++++++++++++++++++++++++++++++++++++-------------
1 files changed, 92 insertions(+), 26 deletions(-)
diff --git a/QiaoJiaSystem/LocalDBTool/SqliteToolkit.hpp b/QiaoJiaSystem/LocalDBTool/SqliteToolkit.hpp
index 06dfd59..9c893a7 100644
--- a/QiaoJiaSystem/LocalDBTool/SqliteToolkit.hpp
+++ b/QiaoJiaSystem/LocalDBTool/SqliteToolkit.hpp
@@ -1,6 +1,10 @@
//
// Created by pans on 4/28/18.
//
+/***
+ * 璇存槑锛屾湰鏂囦欢涓昏鏄痵ql璇彞鎷兼帴鍙婅幏鍙栧凡瀹氫箟鐨剆ql璇彞
+ * 鐩墠浠呮敮鎸佸崟琛╯ql璇彞鎷兼帴
+ */
#ifndef TESTSQLITE_SQLITETOOLKIT_HPP
#define TESTSQLITE_SQLITETOOLKIT_HPP
@@ -24,12 +28,19 @@
*/
typedef std::vector<unsigned char> FaceFeature;
+/***
+ * 娣诲姞浜鸿劯鐨勬暟鎹粨鏋�
+ */
struct AddFaceData {
std::string uuid;
FaceFeature feature;
std::string faceUrl;
+ std::string feature_base64;
};
+/***
+ * 浜鸿劯淇℃伅鐨勬暟鎹粨鏋�
+ */
struct FaceInfo {
std::string uuid;
std::string personName;
@@ -37,10 +48,15 @@
std::string sex;
std::string idCard;
std::string phoneNum;
+ std::string enable;
+ std::string monLevel;
};
typedef std::vector<FaceInfo> FaceInfos;
typedef std::map<std::string, FaceInfo> FaceInfosCache;
+/***
+ * 琛ㄤ俊鎭暟鎹粨鏋�
+ */
struct TableInfo {
std::string uuid;
std::string tableName;
@@ -49,6 +65,10 @@
std::string startTime;
std::string endTime;
std::string createBy;
+
+ std::string uploadFlag;
+ std::string cmpThreshold;
+ std::string enabled;
};
typedef std::vector<TableInfo> TableInfos;
typedef std::map<std::string, TableInfo> TableInfosCache;
@@ -57,10 +77,15 @@
//typedef std::map<std::string, FaceFeatures> FeatureDBCache;
+/***
+ * 甯︽湁URL鍜屽叾浠栧睘鎬х殑浜鸿劯鐗瑰緛鏁版嵁缁撴瀯
+ */
struct FaceFeatureWithUrl {
std::string uuid;
std::string faceurl;
FaceFeature faceFeature;
+ std::string enable;
+ std::string monLevel;
};
typedef std::map<std::string, FaceFeatureWithUrl> FeatureDBWithUrlCache;
@@ -72,39 +97,59 @@
#define SQLERR(message) fprintf(stderr, "%s->%d-> SQLite error: %s\n",__FILE__,__LINE__, message);
- //#todo 鏁版嵁搴搘enjian鍚嶇О
+ //#todo 鏁版嵁搴撴枃浠跺悕绉�
+ //鏄惁闇�瑕佷粠閰嶇疆鏂囦欢璇诲彇锛�
static std::string file_dbName = "TestFaceDB.db";
//#todo 鏁版嵁搴撳悕绉�
static std::string g_dbName = "main";
//#todo 绠＄悊琛ㄧ殑琛ㄥ悕
static std::string g_tableName = "sys_o_tables";
- //
+ /***
+ * 鑾峰彇浜鸿劯鐗瑰緛鏌ヨ鐨剆qlite璇彞
+ * @param tableName 琛ㄥ悕锛屼笉甯� _fea
+ * @return
+ */
std::string getFacesFromTableSql(std::string tableName) {
+// #todo get monLevel 鑱斿悎鏌ヨ
std::string sql =
- "select uuid,feature,create_time,faceUrl,del_flag from " + tableName +
- "_fea where feature is not null ";
+ "select uuid,feature,create_time,faceUrl,del_flag from '" + tableName +
+ "_fea' where feature is not null ";
return sql;
}
+ /***
+ * 鏍规嵁琛ㄧ被鍨嬭幏鍙栨暟鎹〃鍒楄〃
+ * @param type
+ * @return
+ */
std::string getTableListSqlWithType(std::string type) {
- std::string sql = "select tableName,del_flag from " + g_tableName + " where del_flag = 0";
+ std::string sql = "select tableName,del_flag from '" + g_tableName + "' where del_flag = 0";
if (type.size() > 0) {
sql.append(" and tableType = '" + type + "'");
}
return sql;
}
+ /***
+ * 鏌ヨ浠绘剰琛ㄧ殑鎵�鏈夊瓧娈�
+ * @param tableName
+ * @return
+ */
std::string getTableInfosSql(std::string tableName) {
- std::string sql = "select * from " + tableName;// + " where del_flag = 0";
+ std::string sql = "select * from '" + tableName + "';";// + " where del_flag = 0";
return sql;
}
-
+ /***
+ * 鑾峰彇褰撳墠宸插垱寤虹殑琛�
+ * @return
+ */
std::string getTableInfosSql() {
//uuid,tableName,tableDesc,tableType,bwType,startTime,endTime
- std::string sql = "select uuid,tableName,tableType,bwType,startTime,endTime from " + g_tableName +
- " where del_flag = 0";
+ std::string sql =
+ "select uuid,tableName,tableType,bwType,startTime,endTime,uploadFlag,cmpThreshold,enabled,create_by from " +
+ g_tableName + " where del_flag = 0";
return sql;
}
@@ -124,7 +169,7 @@
str_fea.assign(feature.begin(), feature.end());
std::stringstream sql;//= ;
- sql << "INSERT INTO " << tableName << " (";
+ sql << "INSERT INTO '" << tableName << "' (";
//std::string sqlTemp = sql;
std::string sql2 = ") VALUES(\"";
if (id >= 0) {
@@ -149,7 +194,7 @@
//#todo errInfo
return "tableName or fieldValues is NULL";
}
- std::string sql = "DELETE from " + tableName + " where 1 = 1 ";
+ std::string sql = "DELETE from '" + tableName + "' where 1 = 1 ";
for (auto item : fieldValues) {
if (item.first.size() != 0) {
sql.append("and " + item.first + " = '" + item.second + "' ");
@@ -169,11 +214,11 @@
//#todo errInfo
return "tableName or fieldValues is NULL";
}
- std::string sql = "INSERT INTO " + tableName + " (";
+ std::string sql = "INSERT INTO '" + tableName + "' (";
std::string sqlTemp = sql;
std::string sql2 = ") VALUES('";
for (auto item : fieldValues) {
- if (item.first.size() != 0) {
+ if (item.first.size() != 0 && item.second.size() != 0) {
sql.append(item.first + ",");
sql2.append(item.second + "','");
}
@@ -182,7 +227,7 @@
sql = sql.substr(0, sql.length() - 1);
//sql2 delete 2 ,' add )
sql2 = sql2.substr(0, sql2.length() - 2);
- sql2.append(" )");
+ sql2.append(" );");
sql.append(sql2);
return sql;
}
@@ -193,6 +238,9 @@
* @return sql
*/
std::string getInitDBSql() {
+// %% ALTER TABLE sys_o_tables ADD COLUMN uploadFlag varchar(255) DEFAULT 0;
+// %% ALTER TABLE sys_o_tables ADD COLUMN cmpThreshold varchar(255) DEFAULT 60;
+// %% ALTER TABLE sys_o_tables ADD COLUMN enabled varchar(255) DEFAULT 1;
std::string sql = "CREATE TABLE \"main\".\"sys_o_tables\" (";
sql.append(" uuid varchar(255) PRIMARY KEY, ");
// sql.append(" ClusterName varchar(255) DEFAULT NULL,");//鏈湴搴撲笉闇�瑕�
@@ -205,7 +253,10 @@
sql.append(" create_time BLOB default (datetime('now', 'localtime')),");
sql.append(" update_time varchar(255) DEFAULT NULL,");
sql.append(" create_by varchar(255) DEFAULT NULL,");
- sql.append(" del_flag varchar(255) DEFAULT 0");
+ sql.append(" del_flag varchar(255) DEFAULT 0,");
+ sql.append(" uploadFlag varchar(255) DEFAULT 0,");
+ sql.append(" cmpThreshold varchar(255) DEFAULT 60,");
+ sql.append(" enabled varchar(255) DEFAULT 1");
sql.append(");");
return sql;
}
@@ -222,9 +273,9 @@
return "tableName is NULL";
}
// 浜哄憳淇℃伅琛�
- std::string sql = "CREATE TABLE " + g_dbName + ".";
+ std::string sql = "CREATE TABLE " + g_dbName + ".'";
sql.append(tableName);
- sql.append(" ( uuid varchar(255) PRIMARY KEY,");
+ sql.append("' ( uuid varchar(255) PRIMARY KEY,");
sql.append("personName varchar(255) DEFAULT NULL,");
sql.append("age varchar(255) DEFAULT NULL,");
sql.append("sex varchar(255) DEFAULT NULL,");
@@ -233,11 +284,13 @@
sql.append("create_time BLOB DEFAULT (datetime('now', 'localtime')),");
sql.append("update_time DATETIME DEFAULT NULL,");
sql.append("create_by varchar(255) DEFAULT NULL,");
- sql.append("del_flag INTEGER DEFAULT 0");
+ sql.append("del_flag INTEGER DEFAULT 0,");
+ sql.append("monitorLevel varchar(255) DEFAULT 0,");
+ sql.append("enabled varchar(255) DEFAULT 1");
sql.append(");");
// 浜鸿劯鐗瑰緛琛�
- sql.append("CREATE TABLE " + g_dbName + ".");
- sql.append(tableName + "_fea");
+ sql.append("CREATE TABLE " + g_dbName + ".'");
+ sql.append(tableName + "_fea'");
sql.append(" ( uuid varchar(255) PRIMARY KEY,");
sql.append(" feature BLOB NOT NULL,");
sql.append(" faceUrl BLOB NOT NULL,");
@@ -249,7 +302,12 @@
return sql;
}
-
+ /***
+ * 鑾峰彇鏇存柊浜鸿劯涓氬姟淇℃伅琛ㄧ殑sql璇彞
+ * @param tableName
+ * @param fieldValues
+ * @return
+ */
std::string getUpdateFaceTableSql(std::string tableName, FieldValues &fieldValues) {
if (tableName.size() == 0) {
//#todo errInfo
@@ -265,25 +323,33 @@
SQLERR("fieldValues size is error");
return "";
}
- std::string sql = "update ";
- sql.append(tableName + " set ");
+ std::string sql = "update '";
+ sql.append(tableName + "' set ");
for (auto &item :fieldValues) {
+ if (item.second.size() == 0) {
+ continue;
+ }
sql.append(item.first + " = '" + item.second + "',");
}
sql.append(" update_time ='" + AppUtil::getTimeSecString() + "'");
- sql.append(" where uuid ='" + uuid + "'");
+ sql.append(" where uuid ='" + uuid + "';");
return sql;
}
//#TODO 浜鸿劯琛ㄩ渶瑕佸垹闄や袱涓�
+ /***
+ * 鑾峰彇鍒犻櫎浜鸿劯琛ㄧ殑sql璇彞
+ * @param tableName
+ * @return
+ */
std::string getDeleteFaceTableSql(std::string tableName) {
if (tableName.size() == 0) {
//#todo errInfo
return "tableName is NULL";
}
- std::string sql = "DROP TABLE " + g_dbName + "." + tableName + ";";
- sql.append("DROP TABLE " + g_dbName + "." + tableName + "_fea;");
+ std::string sql = "DROP TABLE " + g_dbName + ".'" + tableName + "';";
+ sql.append("DROP TABLE " + g_dbName + ".'" + tableName + "_fea';");
return sql;
}
--
Gitblit v1.8.0