//
|
// Created by pans on 4/28/18.
|
//
|
/***
|
* 说明，本文件主要是sql语句拼接及获取已定义的sql语句
|
* 目前仅支持单表sql语句拼接
|
*/
|
|
#ifndef TESTSQLITE_SQLITETOOLKIT_HPP
|
#define TESTSQLITE_SQLITETOOLKIT_HPP
|
|
#include <iostream>
|
#include <map>
|
#include <vector>
|
#include <string>
|
#include <cstring>
|
#include <sstream>
|
#include <basic/util/app/AppUtil.h>
|
|
/**
|
* 字段的集合
|
* @key FieldName
|
* @value Value
|
*/
|
typedef std::map<std::string, std::string> FieldValues;
|
/***
|
* 人脸特征
|
*/
|
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;
|
std::string age;
|
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;
|
std::string tableType;
|
std::string bwType;
|
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;
|
|
//typedef std::vector<FaceFeature> FaceFeatures;
|
|
//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;
|
|
namespace {
|
|
#define INITCOPY(_DEST, _SRC) _DEST.resize(_SRC.size());memcpy(_DEST.data(),_SRC.data(), _SRC.size());
|
#define COPY(NAME1, NAME2) memcpy(&NAME1.data(), &NAME2.data(), NAME2.size);
|
#define SQLERR(message) fprintf(stderr, "%s->%d-> SQLite error: %s\n",__FILE__,__LINE__, message);
|
|
|
//#todo 数据库文件名称
|
//是否需要从配置文件读取？
|
static std::string file_dbName = "TestFaceDB.db";
|
//#todo 数据库名称
|
static std::string g_dbName = "main";
|
//#todo 管理表的表名
|
static std::string g_tableName = "sys_o_tables";
|
|
/***
|
* 获取人脸特征查询的sqlite语句
|
* @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 ";
|
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";
|
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";
|
return sql;
|
}
|
|
/***
|
* 获取当前已创建的表
|
* @return
|
*/
|
std::string getTableInfosSql() {
|
//uuid,tableName,tableDesc,tableType,bwType,startTime,endTime
|
std::string sql =
|
"select uuid,tableName,tableType,bwType,startTime,endTime,uploadFlag,cmpThreshold,enabled,create_by from " +
|
g_tableName + " where del_flag = 0";
|
return sql;
|
}
|
|
/**
|
* not used have bug
|
* @param tableName
|
* @param id
|
* @param feature
|
* @return
|
*/
|
char *getAddFaceDataSql(std::string tableName, int id, FaceFeature &feature) {
|
if (tableName.size() == 0 || feature.size() == 0) {
|
//#todo errInfo
|
return "tableName or FaceFeature is NULL";
|
}
|
std::string str_fea;
|
str_fea.assign(feature.begin(), feature.end());
|
|
std::stringstream sql;//= ;
|
sql << "INSERT INTO '" << tableName << "' (";
|
//std::string sqlTemp = sql;
|
std::string sql2 = ") VALUES(\"";
|
if (id >= 0) {
|
sql << "id,";
|
sql2.append(std::to_string(id) + "\",\"");
|
}
|
sql << "feature " << sql2 << str_fea << "\")";
|
|
//sql1 delete ,
|
//sql2 delete 2 ,' add )
|
return const_cast<char *>(str_fea.c_str());
|
}
|
|
/**
|
* 删除数据
|
* @param tableName 表名
|
* @param fieldValues 删除的条件组合
|
* @return
|
*/
|
std::string getDeleteSql(std::string tableName, FieldValues &fieldValues) {
|
if (tableName.size() == 0 || fieldValues.size() == 0) {
|
//#todo errInfo
|
return "tableName or fieldValues is NULL";
|
}
|
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 + "' ");
|
}
|
}
|
return sql;
|
}
|
|
/**
|
* 添加数据 || 创建人脸表之后，将该记录插入管理表
|
* @param tableName 表名
|
* @param fieldValues 将要插入字段的集合
|
* @return
|
*/
|
std::string getInsertSql(std::string tableName, FieldValues &fieldValues) {
|
if (tableName.size() == 0 || fieldValues.size() == 0) {
|
//#todo errInfo
|
return "tableName or fieldValues is NULL";
|
}
|
std::string sql = "INSERT INTO '" + tableName + "' (";
|
std::string sqlTemp = sql;
|
std::string sql2 = ") VALUES('";
|
for (auto item : fieldValues) {
|
if (item.first.size() != 0 && item.second.size() != 0) {
|
sql.append(item.first + ",");
|
sql2.append(item.second + "','");
|
}
|
}
|
//sql1 delete ,
|
sql = sql.substr(0, sql.length() - 1);
|
//sql2 delete 2 ,' add )
|
sql2 = sql2.substr(0, sql2.length() - 2);
|
sql2.append(" );");
|
sql.append(sql2);
|
return sql;
|
}
|
|
/**
|
* 创建管理表的sql语句
|
*
|
* @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,");//本地库不需要
|
sql.append(" tableName varchar(255) UNIQUE,");
|
sql.append(" tableDesc varchar(255) DEFAULT NULL,");
|
sql.append(" tableType varchar(255) DEFAULT NULL,");
|
sql.append(" bwType varchar(255) DEFAULT NULL,");
|
sql.append(" startTime varchar(255) DEFAULT NULL,");
|
sql.append(" endTime varchar(255) DEFAULT NULL,");
|
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(" uploadFlag varchar(255) DEFAULT 0,");
|
sql.append(" cmpThreshold varchar(255) DEFAULT 60,");
|
sql.append(" enabled varchar(255) DEFAULT 1");
|
sql.append(");");
|
return sql;
|
}
|
|
|
/**
|
* 创建人脸表的sql语句
|
* @param tableName 想要创建的表名
|
* @return
|
*/
|
std::string getCreateFaceTableSql(std::string tableName) {
|
if (tableName.size() == 0) {
|
//#todo errInfo
|
return "tableName is NULL";
|
}
|
// 人员信息表
|
std::string sql = "CREATE TABLE " + g_dbName + ".'";
|
sql.append(tableName);
|
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,");
|
sql.append("idCard varchar(255) DEFAULT NULL,");
|
sql.append("phoneNum varchar(255) DEFAULT NULL,");
|
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("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(" ( uuid varchar(255) PRIMARY KEY,");
|
sql.append(" feature BLOB NOT NULL,");
|
sql.append(" faceUrl BLOB NOT NULL,");
|
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 INTEGER DEFAULT 0");
|
sql.append(");");
|
return sql;
|
}
|
|
/***
|
* 获取更新人脸业务信息表的sql语句
|
* @param tableName
|
* @param fieldValues
|
* @return
|
*/
|
std::string getUpdateFaceTableSql(std::string tableName, FieldValues &fieldValues) {
|
if (tableName.size() == 0) {
|
//#todo errInfo
|
return "tableName is NULL";
|
}
|
std::string uuid = fieldValues["uuid"];
|
if (uuid.size() <= 0) {
|
SQLERR("uuid size is error");
|
return "";
|
}
|
fieldValues.erase("uuid");
|
if (fieldValues.size() <= 0) {
|
SQLERR("fieldValues size is error");
|
return "";
|
}
|
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 + "';");
|
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';");
|
return sql;
|
}
|
|
/**
|
* 根据表名判断数据表是否存在
|
* @param existRet true is exist.
|
* @param nCount 字段数量
|
* @param pValue 字段值数组
|
* @param azColName 字段名称数组
|
* @return
|
*/
|
static int isExist(void *existRet, int nCount, char **pValue, char **azColName) {
|
bool *testRet = (bool *) existRet;
|
int num = atoi(pValue[0]);
|
if (num > 0) {
|
*testRet = true;
|
} else {
|
*testRet = false;
|
}
|
return 0;
|
}
|
|
}
|
|
|
#endif //TESTSQLITE_SQLITETOOLKIT_HPP
|
