You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
543 lines
14 KiB
543 lines
14 KiB
package dao |
|
|
|
import ( |
|
"bufio" |
|
"context" |
|
"database/sql" |
|
"encoding/json" |
|
"fmt" |
|
"go-common/app/job/bbq/video/conf" |
|
"go-common/app/job/bbq/video/model" |
|
video "go-common/app/service/bbq/video/api/grpc/v1" |
|
acc "go-common/app/service/main/account/api" |
|
xsql "go-common/library/database/sql" |
|
"go-common/library/log" |
|
"go-common/library/net/metadata" |
|
"io" |
|
"net/http" |
|
"net/url" |
|
"os" |
|
"strconv" |
|
"strings" |
|
"time" |
|
) |
|
|
|
const ( |
|
_userDmgCacheKey = "bbq:user:profile:%s" |
|
_userDmgCacheKeyBuvid = "bbq:device:profile:{buvid}:%s" |
|
|
|
//_userDmgCacheKeyBbq="bbq:user:profile" |
|
_userDmgCacheTimeout = 86400 |
|
_incrUpUserDmgSQL = "insert into user_statistics_hive (`mid`,`uname`,`play_total`,`fan_total`,`av_total`,`like_total`) value (?,?,?,?,?,?)" |
|
_updateUpUserDmgSQL = "update user_statistics_hive set `uname` = ? , `play_total` = ? , `fan_total` = ? , `av_total` = ? , `like_total` = ? , `mtime` = ? where `mid` = ?" |
|
_delUpUserDmgSQL = "delete from user_statistics_hive where mtime < ?" |
|
_insertOnDupUpDmg = "insert into user_statistics_hive (`mid`,`uname`,`play_total`,`fan_total`,`av_total`,`like_total`) value (?,?,?,?,?,?) on duplicate key update `uname`=?,`play_total`=?,`fan_total`=?,`av_total`=?,`like_total`=?" |
|
_selMidFromVideo = "select distinct mid from video" |
|
_queryUsersByLast = "select id,mid,uname from user_base where id > ? order by id ASC limit ?" |
|
_selMidFromUserBase = "SELECT DISTINCT mid fROM user_base limit ?, 1000" |
|
_upUserBase = "UPDATE user_base SET face = ? where mid = ?" |
|
) |
|
|
|
//getUserDmgKey . |
|
func getUserDmgKey(mid string) (key string) { |
|
return fmt.Sprintf(_userDmgCacheKey, mid) |
|
} |
|
|
|
//getUserBuvidDmgKey . |
|
func getUserBuvidDmgKey(buvid string) (key string) { |
|
return fmt.Sprintf(_userDmgCacheKeyBuvid, buvid) |
|
} |
|
|
|
// InsertOnDup ... |
|
func (d *Dao) InsertOnDup(c context.Context, upUserDmg *model.UpUserDmg) (err error) { |
|
_, err = d.db.Exec(c, _insertOnDupUpDmg, upUserDmg.MID, upUserDmg.Uname, upUserDmg.Play, upUserDmg.Fans, upUserDmg.AVs, upUserDmg.Likes, upUserDmg.Uname, upUserDmg.Play, upUserDmg.Fans, upUserDmg.AVs, upUserDmg.Likes) |
|
return |
|
} |
|
|
|
//CacheUserDmg ... |
|
func (d *Dao) CacheUserDmg(c context.Context, userDmg *model.UserDmg) (mid string, err error) { |
|
conn := d.redis.Get(c) |
|
defer conn.Close() |
|
var b []byte |
|
if b, err = json.Marshal(userDmg); err != nil { |
|
log.Error("cache user dmg marshal err(%v)", err) |
|
return |
|
} |
|
cacheKey := getUserDmgKey(userDmg.MID) |
|
fmt.Println(cacheKey) |
|
if _, err = conn.Do("SET", cacheKey, b, "EX", _userDmgCacheTimeout); err != nil { |
|
log.Error("cache user dmg redis set err(%v)", err) |
|
return |
|
} |
|
return |
|
} |
|
|
|
//CacheUserBbqDmg ... |
|
func (d *Dao) CacheUserBbqDmg(c context.Context, userBbqDmg *model.UserBbqDmg) (mid string, err error) { |
|
conn := d.redis.Get(c) |
|
defer conn.Close() |
|
tag2 := strings.Join(userBbqDmg.Tag2, ",") |
|
tag3 := strings.Join(userBbqDmg.Tag3, ",") |
|
up := strings.Join(userBbqDmg.Up, ",") |
|
cacheKey := getUserDmgKey(userBbqDmg.MID) |
|
|
|
if err = conn.Send("HSET", cacheKey, "zone", tag2); err != nil { |
|
log.Error("cache user bbq dmg redis set tag2 err(%v)", err) |
|
return |
|
} |
|
|
|
if err = conn.Send("HSET", cacheKey, "tag", tag3); err != nil { |
|
log.Error("cache user bbq dmg redis set tag3 err(%v)", err) |
|
return |
|
} |
|
|
|
if err = conn.Send("HSET", cacheKey, "up", up); err != nil { |
|
log.Error("cache user bbq dmg redis set up err(%v)", err) |
|
return |
|
} |
|
|
|
return |
|
} |
|
|
|
//CacheUserBbqDmgBuvid ... |
|
func (d *Dao) CacheUserBbqDmgBuvid(c context.Context, userBbqDmgBuvid *model.UserBbqBuvidDmg) (Buvid string, err error) { |
|
conn := d.redis.Get(c) |
|
defer conn.Close() |
|
tag2 := strings.Join(userBbqDmgBuvid.Tag2, ",") |
|
tag3 := strings.Join(userBbqDmgBuvid.Tag3, ",") |
|
up := strings.Join(userBbqDmgBuvid.Up, ",") |
|
cacheKey := getUserBuvidDmgKey(userBbqDmgBuvid.Buvid) |
|
|
|
if err = conn.Send("HSET", cacheKey, "zone", tag2); err != nil { |
|
log.Error("cache user bbq buvid dmg redis set tag2 err(%v)", err) |
|
return |
|
} |
|
|
|
if err = conn.Send("HSET", cacheKey, "tag", tag3); err != nil { |
|
log.Error("cache user bbq buvid dmg redis set tag3 err(%v)", err) |
|
return |
|
} |
|
|
|
if err = conn.Send("HSET", cacheKey, "up", up); err != nil { |
|
log.Error("cache user bbq buvid dmg redis set up err(%v)", err) |
|
return |
|
} |
|
|
|
return |
|
} |
|
|
|
// AddUpUserDmg . |
|
func (d *Dao) AddUpUserDmg(c context.Context, upUserDmg *model.UpUserDmg) (num int64, err error) { |
|
var res sql.Result |
|
if res, err = d.db.Exec(c, _incrUpUserDmgSQL, upUserDmg.MID, upUserDmg.Uname, upUserDmg.Play, upUserDmg.Fans, upUserDmg.AVs, upUserDmg.Likes); err != nil { |
|
return 0, err |
|
} |
|
return res.LastInsertId() |
|
} |
|
|
|
// UpdateUpUserDmg . |
|
func (d *Dao) UpdateUpUserDmg(c context.Context, upUserDmg *model.UpUserDmg) (num int64, err error) { |
|
t := time.Now().AddDate(0, 0, 0).Format("2006-01-02 15:04:05") |
|
var res sql.Result |
|
if res, err = d.db.Exec(c, _updateUpUserDmgSQL, upUserDmg.Uname, upUserDmg.Play, upUserDmg.Fans, upUserDmg.AVs, upUserDmg.Likes, t, upUserDmg.MID); err != nil { |
|
return 0, err |
|
} |
|
return res.RowsAffected() |
|
} |
|
|
|
// DelUpUserDmg . |
|
func (d *Dao) DelUpUserDmg(c context.Context) (num int64, err error) { |
|
t := time.Unix(time.Now().Unix(), -int64(36*time.Hour)).Format("2006-01-02 15:04:05") |
|
var res sql.Result |
|
if res, err = d.db.Exec(c, _delUpUserDmgSQL, t); err != nil { |
|
return 0, err |
|
} |
|
return res.RowsAffected() |
|
} |
|
|
|
//Download 下载文件 |
|
func (d *Dao) Download(url string, name string) (fpath string, err error) { |
|
if name == "" { |
|
u := strings.Split(url, "/") |
|
l := len(u) |
|
name = u[l-1] |
|
} |
|
t := time.Now().AddDate(0, 0, 0).Format("20060102") |
|
path := conf.Conf.Download.File + t |
|
err = d.CreateDir(path) |
|
if err != nil { |
|
log.Error("create dir(%s) err(%v)", path, err) |
|
return |
|
} |
|
fpath = path + "/" + name |
|
newFile, err := os.Create(fpath) |
|
|
|
if err != nil { |
|
log.Error("create path(%s) err(%v)", fpath, err) |
|
return |
|
} |
|
defer newFile.Close() |
|
|
|
client := http.Client{} |
|
resp, err := client.Get(url) |
|
if err != nil { |
|
log.Error("download url(%s) err(%v)", url, err) |
|
return |
|
} |
|
defer resp.Body.Close() |
|
_, err = io.Copy(newFile, resp.Body) |
|
if err != nil { |
|
log.Error("copy err(%v)", err) |
|
return |
|
} |
|
return |
|
} |
|
|
|
//CreateDir 创建文件夹 |
|
func (d *Dao) CreateDir(path string) (err error) { |
|
_, err = os.Stat(path) |
|
defer func() { |
|
if os.IsExist(err) { |
|
err = nil |
|
} |
|
}() |
|
if os.IsNotExist(err) { |
|
err = os.Mkdir(path, os.ModePerm) |
|
} |
|
return |
|
} |
|
|
|
// ReadLine 按行读取文件，hander回调 |
|
func (d *Dao) ReadLine(path string, handler func(string)) (err error) { |
|
f, err := os.Open(path) |
|
if err != nil { |
|
log.Error("open path(%s) err(%v)", path, err) |
|
return |
|
} |
|
defer f.Close() |
|
buf := bufio.NewReader(f) |
|
for { |
|
|
|
line, err := buf.ReadString('\n') |
|
if err != nil { |
|
if err == io.EOF { |
|
return nil |
|
} |
|
log.Error("read path(%s) err(%v)", path, err) |
|
return nil |
|
} |
|
line = strings.TrimSpace(line) |
|
handler(line) |
|
time.Sleep(time.Duration(1) * time.Second) |
|
} |
|
} |
|
|
|
// ReadLines 50条发起一次grpc请求 |
|
func (d *Dao) ReadLines(path string, handler func([]int64)) (err error) { |
|
f, err := os.Open(path) |
|
if err != nil { |
|
log.Error("ReadLine open path(%s) err(%v)", path, err) |
|
return |
|
} |
|
defer f.Close() |
|
buf := bufio.NewReader(f) |
|
mids := make([]int64, 0, 50) |
|
i := 0 |
|
for { |
|
line, err := buf.ReadString('\n') |
|
if err != nil { |
|
if err == io.EOF { |
|
err = nil |
|
break |
|
} |
|
log.Error("read path(%s) err(%v)", path, err) |
|
break |
|
} |
|
mid, _ := strconv.ParseInt(strings.TrimSpace(line), 10, 64) |
|
mids = append(mids, mid) |
|
i++ |
|
if i == 50 { |
|
handler(mids) |
|
mids = make([]int64, 0, 50) |
|
i = 0 |
|
time.Sleep(time.Duration(1) * time.Second) |
|
} |
|
} |
|
if len(mids) != 0 { |
|
handler(mids) |
|
} |
|
return |
|
} |
|
|
|
//HandlerUserDmg mid, gender, age, geo, content_tag, viewed_video, content_zone, content_count, follow_ups |
|
func (d *Dao) HandlerUserDmg(user string) { |
|
u := strings.Split(user, "\u0001") |
|
userDmg := &model.UserDmg{ |
|
MID: u[0], |
|
Gender: u[1], |
|
Age: u[2], |
|
Geo: u[3], |
|
ContentTag: u[4], |
|
ViewedVideo: d.HandlerViewedVideo(u[5]), |
|
ContentZone: u[6], |
|
ContentCount: u[7], |
|
FollowUps: u[8], |
|
} |
|
d.CacheUserDmg(context.Background(), userDmg) |
|
} |
|
|
|
//HandlerUserBbqDmg .. |
|
func (d *Dao) HandlerUserBbqDmg(user string) { |
|
u := strings.Split(user, ",") |
|
userBbqDmg := &model.UserBbqDmg{ |
|
MID: u[0], |
|
Tag2: strings.Split(u[1], "\u0002"), |
|
Tag3: strings.Split(u[2], "\u0002"), |
|
Up: strings.Split(u[3], "\u0002"), |
|
} |
|
d.CacheUserBbqDmg(context.Background(), userBbqDmg) |
|
} |
|
|
|
//HandlerUserBbqDmgBuvid .. |
|
func (d *Dao) HandlerUserBbqDmgBuvid(user string) { |
|
u := strings.Split(user, ",") |
|
UserBbqBuvidDmg := &model.UserBbqBuvidDmg{ |
|
Buvid: u[0], |
|
Tag2: strings.Split(u[1], "\u0002"), |
|
Tag3: strings.Split(u[2], "\u0002"), |
|
Up: strings.Split(u[3], "\u0002"), |
|
} |
|
d.CacheUserBbqDmgBuvid(context.Background(), UserBbqBuvidDmg) |
|
} |
|
|
|
// HandlerMids update userbase by mids |
|
func (d *Dao) HandlerMids(mids []int64) { |
|
res, err := d.VideoClient.SyncUserStas(context.Background(), &video.SyncMidsRequset{MIDS: mids}) |
|
if err != nil { |
|
log.Error("userbases update failes, mids(%v), err(%v)", mids, err) |
|
return |
|
} |
|
log.Info("userbases update success, affected %v rows", res.Affc) |
|
} |
|
|
|
// HandlerMid update userbase by mid |
|
func (d *Dao) HandlerMid(s string) { |
|
mid, _ := strconv.ParseInt(s, 10, 64) |
|
res, err := d.VideoClient.SyncUserSta(context.Background(), &video.SyncMidRequset{MID: mid}) |
|
if err != nil { |
|
log.Error("userbase update failes, mid(%v), err(%v)", mid, err) |
|
return |
|
} |
|
if res.Affc == 1 { |
|
log.Info("userbase insert success ,mid(%v)", mid) |
|
} else if res.Affc == 2 { |
|
log.Info("userbase update success , mid(%v)", mid) |
|
} |
|
} |
|
|
|
//HandlerViewedVideo 处理看过的视频，保存最近看过的100个 |
|
func (d *Dao) HandlerViewedVideo(v string) (res map[int64]string) { |
|
res = make(map[int64]string) |
|
var vv [][]interface{} |
|
var dd string |
|
|
|
err := json.Unmarshal([]byte(v), &vv) |
|
if err != nil { |
|
return |
|
} |
|
l := len(vv) |
|
n := 1 |
|
for i := l - 1; i >= 0; i-- { |
|
for _, a := range vv[i] { |
|
switch b := a.(type) { |
|
case string: |
|
dd = b |
|
case []interface{}: |
|
ll := len(b) |
|
for j := ll - 1; j >= 0; j-- { |
|
switch c := b[j].(type) { |
|
case float64: |
|
k := int64(c) |
|
if _, ok := res[k]; !ok { |
|
res[k] = dd |
|
n++ |
|
} |
|
} |
|
if n > 100 { |
|
return |
|
} |
|
} |
|
} |
|
} |
|
} |
|
return |
|
} |
|
|
|
// SelMidFromVideo get distinct mid list from table video |
|
func (d *Dao) SelMidFromVideo() (mids []int64, err error) { |
|
rows, err := d.db.Query(context.Background(), _selMidFromVideo) |
|
if err != nil { |
|
log.Error("SelMidFromVideo failed, err(%v)", err) |
|
return |
|
} |
|
defer rows.Close() |
|
for rows.Next() { |
|
var s string |
|
if err = rows.Scan(&s); err != nil { |
|
panic(err.Error()) |
|
} |
|
var mid int64 |
|
if mid, err = strconv.ParseInt(s, 10, 64); err != nil { |
|
log.Error("strconv.ParseInt(%s) error(%v)", s, err) |
|
return |
|
} |
|
mids = append(mids, mid) |
|
} |
|
return |
|
} |
|
|
|
//MergeUpInfo merge up info |
|
func (d *Dao) MergeUpInfo(mid int64) (err error) { |
|
var ( |
|
ctx = context.Background() |
|
params = url.Values{} |
|
req = &http.Request{} |
|
id int64 |
|
res struct { |
|
Code int |
|
Data model.UpUserInfoRes |
|
} |
|
) |
|
err = d.db.QueryRow(ctx, "select mid from user_base where mid = ?", mid).Scan(&id) |
|
if err == nil { |
|
log.Infow(ctx, "log", "already has mid in user_base", "mid", mid) |
|
return |
|
} |
|
if err == sql.ErrNoRows { |
|
params.Set("mid", strconv.FormatInt(mid, 10)) |
|
req, err = d.HTTPClient.NewRequest("GET", d.c.URLs["account"], "", params) |
|
if err != nil { |
|
log.Error("MergeUpInfo error(%v)", err) |
|
return |
|
} |
|
if err = d.HTTPClient.Do(ctx, req, &res); err != nil { |
|
log.Error("MergeUpInfo http req failed ,err:%v", err) |
|
return |
|
} |
|
res := res.Data |
|
var sex int |
|
switch res.Sex { |
|
case "男": |
|
sex = 1 |
|
case "女": |
|
sex = 2 |
|
default: |
|
sex = 3 |
|
} |
|
_, err = d.db.Exec(ctx, |
|
"insert into user_base (mid,uname,face,sex,user_type,complete_degree)values(?,?,?,?,?,?)", |
|
res.MID, |
|
res.Name, |
|
res.Face, |
|
sex, |
|
model.UserTypeUp, |
|
0) |
|
if err != nil { |
|
log.Error("MergeUpInfo insert upinfo failed,err:%v", err) |
|
return |
|
} |
|
} else { |
|
log.Error("MergeUpInfo query sql failed,err:%v", err) |
|
} |
|
if err = d.db.QueryRow(ctx, "select id from user_statistics where mid = ?", mid).Scan(&id); err != nil { |
|
if err == sql.ErrNoRows { |
|
if _, err = d.db.Exec(ctx, "insert into user_statistics (mid) values (?)", mid); err != nil { |
|
log.Error("init insert user_statistics failed,err:%v", err) |
|
} |
|
} else { |
|
log.Error("init query user_statistics failed,err:%v", err) |
|
} |
|
} |
|
return |
|
} |
|
|
|
//UsersByLast 使用lastid批量获取用户 |
|
func (d *Dao) UsersByLast(c context.Context, lastid int64) (r []*model.UserBaseDB, err error) { |
|
var rows *xsql.Rows |
|
rows, err = d.db.Query(c, _queryUsersByLast, lastid, _limitSize) |
|
if err != nil { |
|
log.Error("db _queryVideos err(%v)", err) |
|
return |
|
} |
|
for rows.Next() { |
|
u := new(model.UserBaseDB) |
|
if err = rows.Scan(&u.ID, &u.MID, &u.Uname); err != nil { |
|
log.Error("scan err(%v)", err) |
|
continue |
|
} |
|
r = append(r, u) |
|
} |
|
return |
|
} |
|
|
|
// SelMidFromUserBase get distinct mid list from table user_base |
|
func (d *Dao) SelMidFromUserBase(start int) (mids []int64, err error) { |
|
var mid int64 |
|
rows, err := d.db.Query(context.Background(), _selMidFromUserBase, start) |
|
if err != nil { |
|
log.Error("SelMidFromUserBase failed, err(%v)", err) |
|
return |
|
} |
|
defer rows.Close() |
|
for rows.Next() { |
|
var s string |
|
if err = rows.Scan(&s); err != nil { |
|
panic(err.Error()) |
|
} |
|
if mid, err = strconv.ParseInt(s, 10, 64); err != nil { |
|
log.Error("strconv.ParseInt(%s) error(%v)", s, err) |
|
return |
|
} |
|
mids = append(mids, mid) |
|
} |
|
return |
|
} |
|
|
|
// UpUserBases 根据mids更新用户基本信息 |
|
func (d *Dao) UpUserBases(c context.Context, mids []int64) (err error) { |
|
var ( |
|
tx *xsql.Tx |
|
) |
|
midsReq := &acc.MidsReq{ |
|
Mids: mids, |
|
RealIp: metadata.String(c, metadata.RemoteIP)} |
|
infosReply, err := d.AccountClient.Infos3(c, midsReq) |
|
if infosReply == nil { |
|
log.Error("查询infos3失败,err（%v）", err) |
|
fmt.Printf("查询infos3失败,err（%v）", err) |
|
return |
|
} |
|
if tx, err = d.BeginTran(c); err != nil { |
|
log.Error("begin transaction error(%v)", err) |
|
return |
|
} |
|
for _, info := range infosReply.Infos { |
|
if info.Mid != 0 { |
|
if len(info.Face) > 255 { |
|
info.Face = "http://i0.hdslb.com/bfs/bbq/video-image/userface/1558868601542006937.png" |
|
} |
|
for try := 0; try < 3; try++ { |
|
if _, err = tx.Exec(_upUserBase, info.Face, info.Mid); err == nil { |
|
break |
|
} |
|
} |
|
if err != nil { |
|
log.Error("mid(%v) update failed", info.Mid) |
|
} |
|
} |
|
} |
|
if err = tx.Commit(); err != nil { |
|
log.Error("UpUserBases commit failed err(%v)", err) |
|
} |
|
return |
|
}
|
|
|