From 1e180d22fdb13399e9caf31da97a1b5554123102 Mon Sep 17 00:00:00 2001
From: zhangzengfei <zhangzengfei@smartai.com>
Date: 星期四, 26 九月 2024 21:09:27 +0800
Subject: [PATCH] fix db
---
db/base.go | 16 +++--
db/person.go | 70 ++++++++---------------
cache/cache.go | 58 ++++++++++---------
3 files changed, 65 insertions(+), 79 deletions(-)
diff --git a/cache/cache.go b/cache/cache.go
index 91dd9df..72c8d62 100644
--- a/cache/cache.go
+++ b/cache/cache.go
@@ -12,12 +12,12 @@
"basic.com/valib/logger.git"
)
-var querynum = flag.Int("querynum", 3000, "the query number from database")
-var threadnum = flag.Int("threadnum", 32, "the number of thread to deal data.")
+var peerSizeNum = flag.Int("peerSize", 3000, "the query number from database")
+var threadNum = flag.Int("threadNum", 32, "the number of thread to deal data.")
var (
- Unfiled = "unfiled"
- PRE_DBTABLE = "dbTable_"
+ Unfilled = "unfilled"
+ PreTableName = "dbTable_"
)
type AreaMapItem struct {
@@ -26,8 +26,8 @@
}
var CaptureDbMap *AreaMapItem
-var RealNameDbMap = shardmap.New(uint8(*threadnum))
-var KeyPersonDbMap = shardmap.New(uint8(*threadnum))
+var RealNameDbMap = shardmap.New(uint8(*threadNum))
+var KeyPersonDbMap = shardmap.New(uint8(*threadNum))
var doOnce sync.Once
@@ -40,7 +40,7 @@
}
// 鍒濆鍖栨湭鍒嗙被, 娌℃湁灏忓尯id鐨勬。妗�
- CaptureDbMap.Area[Unfiled] = shardmap.New(uint8(*threadnum))
+ CaptureDbMap.Area[Unfilled] = shardmap.New(uint8(*threadNum))
})
initDbTablePersonsCache()
@@ -66,7 +66,7 @@
func initDbTablePersonsCache() {
// 缂撳瓨搴曞簱涓殑鍏ㄩ儴浜哄憳淇℃伅
var dbpApi db.DbPersons
- total, e := dbpApi.GetPersonTotal("")
+ total, e := dbpApi.GetPersonTotal()
// 鏆傛椂鍘绘帀鍒拌灏忓尯杩囨护
//var psApi db.PersonStatus
@@ -74,16 +74,18 @@
logger.Debugf("鎶撴媿妗ｆ搴撳叡鏈�%d鏉¤褰�", total)
if e == nil && total > 0 {
- queryEachNum := *querynum
- qn := int(total) / *threadnum
- if *querynum < qn {
+ queryEachNum := *peerSizeNum
+ qn := int(total) / *threadNum
+ if *peerSizeNum < qn {
queryEachNum = qn
}
+
queryT := int(total) / queryEachNum
if int(total)%queryEachNum > 0 {
queryT++
}
- temptime := time.Now()
+
+ startTime := time.Now()
var wg sync.WaitGroup
for i := 0; i < queryT; i++ {
@@ -91,12 +93,13 @@
wg.Add(1)
go func(qs int) {
defer wg.Done()
- dbPersons, err := dbpApi.GetPersonsCompareCacheBase(j, queryEachNum)
+ dbPersons, err := dbpApi.GetPersonsCacheBase(j, queryEachNum)
if err != nil {
logger.Error(err)
return
}
- logger.Debugf("eachNum:%d, 鑾峰彇%d鏉′汉鍛樹俊鎭�", queryEachNum, len(dbPersons))
+
+ logger.Debugf("thread:%d, 鑾峰彇%d鏉′汉鍛樹俊鎭�", queryEachNum, len(dbPersons))
CaptureDbMap.Lock()
areaId := ""
@@ -104,14 +107,14 @@
areaId = value.AreaId
// 娌℃湁灏忓尯id鐨勪汉鍛�
if areaId == "" {
- CaptureDbMap.Area[Unfiled].Set(value.Id, value)
- CaptureDbMap.Area[Unfiled].Settime()
+ CaptureDbMap.Area[Unfilled].Set(value.Id, value)
+ CaptureDbMap.Area[Unfilled].Settime()
continue
}
//for _, areaId := range accessAreas[value.Id] {
if _, ok := CaptureDbMap.Area[areaId]; !ok {
- CaptureDbMap.Area[areaId] = shardmap.New(uint8(*threadnum))
+ CaptureDbMap.Area[areaId] = shardmap.New(uint8(*threadNum))
}
CaptureDbMap.Area[areaId].Set(value.Id, value)
@@ -124,10 +127,11 @@
}(j)
}
wg.Wait()
- logger.Debug("鎶撴媿妗ｆ搴撲汉鍛樼紦瀛樺畬鎴愮敤鏃�:", time.Since(temptime))
+
+ logger.Debug("鎶撴媿妗ｆ搴撲汉鍛樼紦瀛樺畬鎴愮敤鏃�:", time.Since(startTime))
for k, v := range CaptureDbMap.Area {
- logger.Debugf("Cache area %s item len %d ", k, v.GetLen())
+ logger.Debugf("Cache area %s items len %d ", k, v.GetLen())
}
}
}
@@ -163,7 +167,7 @@
// UpdateDbPersonsCacheById 鏇存柊缂撳瓨涓殑鍏ㄩ儴浜哄憳淇℃伅
func UpdateDbPersonsCacheById(id string) {
var dbpApi db.DbPersons
- info, err := dbpApi.GetPersonsCompareCacheById(id)
+ info, err := dbpApi.GetPersonsById(id)
if err != nil {
logger.Error(err)
return
@@ -172,7 +176,7 @@
if _, ok := CaptureDbMap.Area[info.AreaId]; !ok {
CaptureDbMap.Lock()
defer CaptureDbMap.Unlock()
- CaptureDbMap.Area[info.AreaId] = shardmap.New(uint8(*threadnum))
+ CaptureDbMap.Area[info.AreaId] = shardmap.New(uint8(*threadNum))
}
CaptureDbMap.Area[info.AreaId].Set(info.Id, info)
CaptureDbMap.Area[info.AreaId].Settime()
@@ -189,7 +193,7 @@
CaptureDbMap.Lock()
defer CaptureDbMap.Unlock()
if _, ok := CaptureDbMap.Area[tableId]; !ok {
- CaptureDbMap.Area[tableId] = shardmap.New(uint8(*threadnum))
+ CaptureDbMap.Area[tableId] = shardmap.New(uint8(*threadNum))
}
var ei = protomsg.Esinfo{
Id: id,
@@ -203,7 +207,7 @@
}
func RealTimeDelPersonFromCache(tableId string, id string) {
- logger.Debug("DelPersonFromCache,tableId:", tableId, ",id:", id)
+ logger.Debug("Delete person from cache, tableId:", tableId, ",id:", id)
CaptureDbMap.Lock()
defer CaptureDbMap.Unlock()
if _, ok := CaptureDbMap.Area[tableId]; ok {
@@ -219,7 +223,7 @@
CaptureDbMap.Lock()
defer CaptureDbMap.Unlock()
- if dtM, ok := CaptureDbMap.Area[PRE_DBTABLE]; ok {
+ if dtM, ok := CaptureDbMap.Area[PreTableName]; ok {
dtM.Del(tableId)
}
if _, ok := CaptureDbMap.Area[tableId]; ok {
@@ -233,10 +237,10 @@
CaptureDbMap.Lock()
defer CaptureDbMap.Unlock()
- if _, ok := CaptureDbMap.Area[PRE_DBTABLE]; !ok {
- CaptureDbMap.Area[PRE_DBTABLE] = shardmap.New(uint8(*threadnum))
+ if _, ok := CaptureDbMap.Area[PreTableName]; !ok {
+ CaptureDbMap.Area[PreTableName] = shardmap.New(uint8(*threadNum))
}
- CaptureDbMap.Area[PRE_DBTABLE].Set(tableId, enable == 1)
+ CaptureDbMap.Area[PreTableName].Set(tableId, enable == 1)
}
func UpdateCache(changeMsg *protomsg.EsPersonCacheChange) {
diff --git a/db/base.go b/db/base.go
index ed77248..83214e2 100644
--- a/db/base.go
+++ b/db/base.go
@@ -1,12 +1,15 @@
package db
+import (
+ "gorm.io/gorm"
+ "time"
+)
+
type BaseEntity struct {
- Id string `gorm:"primary_key;column:id" json:"id" example:""`
- CreateTime string `gorm:"column:createTime" json:"createTime,omitempty" example:""`
- UpdateTime string `gorm:"column:updateTime" json:"updateTime,omitempty" example:""`
- CreateBy string `gorm:"column:createBy" json:"createBy,omitempty" example:""`
- IsDelete int `gorm:"column:isDelete" json:"isDelete" example:"0 鏈垹闄� 1宸插垹闄�"`
- Enable int `gorm:"column:enable" json:"enable" example:" 1鐢熸晥 0鏈敓鏁�"`
+ ID string `gorm:"primary_key;column:id;type:varchar(255);" json:"id"`
+ CreatedAt time.Time
+ UpdatedAt time.Time
+ DeletedAt gorm.DeletedAt `gorm:"index" json:"-"`
}
type FeatureCacheBase struct {
@@ -14,5 +17,4 @@
AreaId string
TableId string
FaceFeature []float32
- Enable int32
}
diff --git a/db/person.go b/db/person.go
index 2dfadeb..eab0654 100644
--- a/db/person.go
+++ b/db/person.go
@@ -9,48 +9,26 @@
type DbPersons struct {
BaseEntity
- TableId string `gorm:"column:tableId" json:"tableId" example:"搴撹〃id"`
- FaceFeature string `gorm:"column:faceFeature" json:"faceFeature" example:"浜鸿劯鐗瑰緛鍊硷紙杞︿富鐗瑰緛锛�"`
- PersonPicUrl string `gorm:"column:personPicUrl" json:"personPicUrl" example:"鍥剧墖璺緞,锛堣溅涓荤収鐗囷級"`
- PersonName string `gorm:"column:personName" json:"personName" example:"浜哄憳濮撳悕锛岋紙杞︿富濮撳悕锛�"`
- Age string `gorm:"column:age" json:"age" example:"骞撮緞"`
- Sex string `gorm:"column:sex" json:"sex" example:"鎬у埆 鐢� 濂筹紙杞︿富鎬у埆锛�"`
- IdCard string `gorm:"column:idCard" json:"idCard" example:"韬唤璇侊紙杞︿富韬唤璇侊級"`
- PhoneNum string `gorm:"column:phoneNum" json:"phoneNum" example:"鎵嬫満鍙风爜"`
- MonitorLevel string `gorm:"column:monitorLevel" json:"monitorLevel" example:"绛夌骇"`
- PicDesc string `gorm:"column:picDesc" json:"picDesc" example:"鐓х墖鏍囪瘑"`
- Reserved string `gorm:"column:reserved" json:"reserved" example:"鍏朵粬"`
- FromServerId string `gorm:"column:fromServerId" json:"fromServerId,omitempty" example:"鍏ュ簱serverId"`
- ResidentialArea string `gorm:"column:residential_area;type:varchar(255)" json:"residentialArea" example:"灏忓尯"`
- Community string `gorm:"column:community;type:varchar(255)" json:"community" example:"绀惧尯"`
- LastAppearanceTime int64 `gorm:"column:last_appearance_time;type:int;not null;default:0" json:"lastAppearanceTime" example:"123456789"`
- SnapshotCount int `gorm:"column:snapshot_count;type:varchar(255)" json:"snapshotCount" example:"10" comment:"鎶撴媿娆℃暟"`
- DaysAppeared int `gorm:"column:days_appeared;type:int(11);not null;default:0" json:"daysAppeared" example:"5" comment:"鍑虹幇澶╂暟"`
- Location string `gorm:"column:location;type:varchar(255)" json:"location" example:"寤烘。鍦扮偣" comment:"寤烘。鍦扮偣"`
- LastLocation string `gorm:"column:last_location;type:varchar(255)" json:"lastLocation" example:"鏈�鍚庡嚭鐜板湴鐐�" comment:"鏈�鍚庡嚭鐜板湴鐐�"`
- FaceAngleYaw int `gorm:"column:face_angle_yaw;type:int(11)" json:"faceAngleYaw" example:"15" comment:"浜鸿劯瑙掑害鍋忚埅瑙�"`
- FaceAngleRoll int `gorm:"column:face_angle_roll;type:int(11)" json:"faceAngleRoll" example:"16" comment:"浜鸿劯瑙掑害婊氳浆瑙�"`
- FaceAnglePitch int `gorm:"column:face_angle_pitch;type:int(11)" json:"faceAnglePitch" example:"15" comment:"浜鸿劯瑙掑害淇话瑙�"`
- PersonalStatusName string `gorm:"column:personal_status;type:varchar(31);comment:AI鏍囩" json:"-"` //AI鏍囩
- PersonalStatus string `gorm:"-" json:"personalStatus"` //AI鏍囩瀵瑰簲鍚嶇О
- Labels []string `gorm:"-" json:"labels"` //鎵嬪姩娣诲姞鐨勬爣绛惧搴斿悕绉�
- AreaID string `json:"areaID" gorm:"index;column:communityID;type:varchar(299);"` //甯搁┗灏忓尯 domain unit ID
- OrgID string `json:"orgID" gorm:"index;column:org_id;type:varchar(299);"` //甯搁┗娲惧嚭鎵� domain unit ID
+ TableId string `gorm:"column:tableId"`
+ FaceFeature string `gorm:"column:face_feature"`
+ CommunityID string `gorm:"column:community_id"` // 甯镐綇灏忓尯 domain unit ID
+ OrgID string `gorm:"column:org_id"` // 甯镐綇娲惧嚭鎵� domain unit ID
+ OrgName string `gorm:"column:community"`
}
-func (dbp *DbPersons) GetPersonTotal(tableId string) (total int64, err error) {
- sql := "select count(1) as total from dbtablepersons where isDelete=0 and tableId in (select id from dbtables where isDelete=0)"
- if tableId != "" {
- sql += " and tableId='" + tableId + "'"
- }
+func (dbp *DbPersons) GetPersonTotal() (total int64, err error) {
+ sql := "select id from person where is_delete = 0 and tableId = 'system'"
+
err = db.Raw(sql).Count(&total).Error
+
return
}
-func (dbp *DbPersons) GetPersonsCompareCacheBase(from int, size int) (arr []*FeatureCacheBase, err error) {
+func (dbp *DbPersons) GetPersonsCacheBase(from int, size int) (arr []*FeatureCacheBase, err error) {
var persons []DbPersons
- sql := "select id, tableId, faceFeature, communityID, enable from dbtablepersons where isDelete=0 and tableId in (select id from dbtables where isDelete=0)"
+ sql := "select id, tableId, face_feature, community_id from person where is_delete = 0 and tableId = 'system'"
sql += " order by id asc limit " + strconv.Itoa(from) + "," + strconv.Itoa(size)
+
err = db.Raw(sql).Find(&persons).Error
if err != nil {
return nil, err
@@ -60,28 +38,30 @@
if p.FaceFeature != "" {
byteFeat, err := base64.StdEncoding.DecodeString(p.FaceFeature)
if err != nil {
- logger.Errorf("Person %s feature is invalid", p.Id)
+ logger.Errorf("Person %s feature is invalid", p.ID)
continue
}
arr = append(arr, &FeatureCacheBase{
- Id: p.Id,
+ Id: p.ID,
TableId: p.TableId,
- AreaId: p.AreaID,
+ AreaId: p.CommunityID,
FaceFeature: util.ByteSlice2float32Slice(byteFeat),
- Enable: int32(p.Enable),
})
} else {
- logger.Errorf("Person %s feature is empty %s", p.Id)
+ logger.Warnf("Person %s feature is empty %s", p.ID)
}
}
+
return
}
-func (dbp *DbPersons) GetPersonsCompareCacheById(id string) (info *FeatureCacheBase, err error) {
- sql := "select id, tableId, faceFeature, communityID, enable from dbtablepersons where id = \"" + id + "\""
+func (dbp *DbPersons) GetPersonsById(id string) (info *FeatureCacheBase, err error) {
var p DbPersons
- err = db.Raw(sql).First(&p).Error
+
+ err = db.Table("person").
+ Select("id", "tableId", "face_feature", "community_id").
+ First(&p, "id = ?", id).Error
if err != nil {
return nil, err
}
@@ -92,12 +72,12 @@
return nil, err
}
info = &FeatureCacheBase{
- Id: p.Id,
+ Id: p.ID,
TableId: p.TableId,
- AreaId: p.AreaID,
+ AreaId: p.CommunityID,
FaceFeature: util.ByteSlice2float32Slice(byteFeat),
- Enable: int32(p.Enable),
}
}
+
return
}
--
Gitblit v1.8.0