// 加(减)表里面的统计数据 // @table: 要操作数据库表名 // @field: 要修改的字段 // @inc: 要增加或减少的值 func IncCountById(db *goku.MysqlDB, table string, id int64, field string, inc int) (sql.Result, error) { r, err := db.Exec(fmt.Sprintf("UPDATE `%s` SET %s=%s+? WHERE id=?;", table, field, field), inc, id) if err != nil { goku.Logger().Errorln(err.Error()) } return r, err }
//获取link和comment的投票记录 func GetVoteRecordByUser(userId int64, page int, pagesize int) { /* sql := `SELECT * FROM ( (SELECT L.id, 'link' AS record_type,LSR.score AS vote_score,LSR.vote_time FROM link_support_record LSR INNER JOIN link L ON LSR.link_id=L.id AND LSR.user_id=1 ORDER BY vote_time DESC LIMIT 0,200) UNION ALL (SELECT C.id,'comment' AS record_type,CSR.score AS vote_score,CSR.vote_time FROM comment_support_record CSR INNER JOIN comment C ON CSR.comment_id=C.id AND CSR.user_id=1 ORDER BY CSR.vote_time DESC LIMIT 0,200))T ORDER BY T.vote_time DESC LIMIT ?,?` */ if page < 1 { page = 1 } page = page - 1 if pagesize == 0 { pagesize = 20 } var db *goku.MysqlDB = GetDB() defer db.Close() //rows, err := db.Query(sql, pagesize * page, pagesize) //if err != nil { //goku.Logger().Errorln(err.Error()) //return nil, err //} }
// 获取由用户发布的评论 // @page: 从1开始 func Comment_ByUser(userId int64, page, pagesize int) []Comment { if page < 1 { page = 1 } page = page - 1 if pagesize == 0 { pagesize = 20 } var db *goku.MysqlDB = GetDB() defer db.Close() qi := goku.SqlQueryInfo{} qi.Limit = pagesize qi.Offset = page * pagesize qi.Where = "`user_id`=?" qi.Params = []interface{}{userId} qi.Order = "id desc" var comments []Comment err := db.GetStructs(&comments, qi) if err != nil { goku.Logger().Errorln(err.Error()) return nil } return comments }
// 获取由用户收藏的link // @page: 从1开始 func FavoriteLink_ByUser(userId int64, page, pagesize int) []Link { var db *goku.MysqlDB = GetDB() defer db.Close() page, pagesize = utils.PageCheck(page, pagesize) qi := goku.SqlQueryInfo{} qi.Fields = "l.id, l.user_id, l.title, l.context, l.topics, l.vote_up, l.vote_down, l.view_count, l.comment_count, l.create_time" qi.Join = " ufl INNER JOIN `link` l ON ufl.link_id=l.id" qi.Where = "ufl.user_id=?" qi.Params = []interface{}{userId} qi.Limit = pagesize qi.Offset = pagesize * page qi.Order = "ufl.create_time desc" rows, err := db.Select("user_favorite_link", qi) if err != nil { goku.Logger().Errorln(err.Error()) return nil } links := make([]Link, 0) for rows.Next() { link := Link{} err = rows.Scan(&link.Id, &link.UserId, &link.Title, &link.Context, &link.Topics, &link.VoteUp, &link.VoteDown, &link.ViewCount, &link.CommentCount, &link.CreateTime) if err != nil { goku.Logger().Errorln(err.Error()) return nil } links = append(links, link) } return links }
func Topic_SearchByName(name string) ([]Topic, error) { var db *goku.MysqlDB = GetDB() // db.Debug = true defer db.Close() qi := goku.SqlQueryInfo{} qi.Fields = "`id`,`name`,`name_lower`,`description`,`pic`,`click_count`,`follower_count`,`link_count`" qi.Where = "name_lower LIKE ?" //"name_lower LIKE '%" + strings.ToLower(name) + "%'" qi.Params = []interface{}{strings.ToLower(name) + "%"} //"%" + qi.Limit = 10 qi.Offset = 0 qi.Order = "link_count DESC" rows, err := db.Select("topic", qi) topics := make([]Topic, 0) if err != nil { goku.Logger().Errorln(err.Error()) return topics, err } for rows.Next() { topic := Topic{} err = rows.Scan(&topic.Id, &topic.Name, &topic.NameLower, &topic.Description, &topic.Pic, &topic.ClickCount, &topic.FollowerCount, &topic.LinkCount) if err != nil { goku.Logger().Errorln(err.Error()) return topics, err } topics = append(topics, topic) } return topics, nil }
//删除link func DelUserFavorite(userId int64, linkId int64) error { var db *goku.MysqlDB = GetDB() defer db.Close() _, err := db.Delete("user_favorite_link", "`user_id`=? AND `link_id`=?", userId, linkId) return err }
//模糊搜索用户 func User_SearchByName(name string, ctx *goku.HttpContext) ([]*VUser, error) { var db *goku.MysqlDB = GetDB() defer db.Close() qi := goku.SqlQueryInfo{} qi.Fields = "`id`,`name`,`email`,`description`,`user_pic`,`friend_count`,`topic_count`,`ftopic_count`,`status`,`follower_count`,`link_count`,`create_time`" qi.Where = "name_lower LIKE ?" qi.Params = []interface{}{strings.ToLower(name) + "%"} qi.Limit = 10 qi.Offset = 0 qi.Order = "link_count DESC" rows, err := db.Select("user", qi) if err != nil { goku.Logger().Errorln(err.Error()) return nil, err } users := make([]User, 0) for rows.Next() { user := User{} err = rows.Scan(&user.Id, &user.Name, &user.Email, &user.Description, &user.UserPic, &user.FriendCount, &user.TopicCount, &user.FtopicCount, &user.Status, &user.FollowerCount, &user.LinkCount, &user.CreateTime) if err != nil { goku.Logger().Errorln(err.Error()) return nil, err } users = append(users, user) } return User_ToVUsers(users, ctx), nil }
// 获取关注topicId的用户列表 func Topic_GetFollowers(topicId int64, page, pagesize int) ([]User, error) { var db *goku.MysqlDB = GetDB() defer db.Close() page, pagesize = utils.PageCheck(page, pagesize) qi := goku.SqlQueryInfo{} qi.Fields = "u.id, u.name, u.email, u.user_pic" qi.Join = " tf INNER JOIN `user` u ON tf.user_id=u.id" qi.Where = "tf.topic_id=?" qi.Params = []interface{}{topicId} qi.Limit = pagesize qi.Offset = pagesize * page qi.Order = "u.id desc" rows, err := db.Select("topic_follow", qi) if err != nil { goku.Logger().Errorln(err.Error()) return nil, err } defer rows.Close() users := make([]User, 0) for rows.Next() { user := User{} err = rows.Scan(&user.Id, &user.Name, &user.Email, &user.UserPic) if err != nil { goku.Logger().Errorln(err.Error()) return nil, err } users = append(users, user) } return users, nil }
// 用户userId 取消关注 话题topicId func Topic_UnFollow(userId, topicId int64) (bool, error) { if userId < 1 || topicId < 1 { return false, errors.New("参数错误") } var db *goku.MysqlDB = GetDB() defer db.Close() r, err := db.Delete("topic_follow", "`user_id`=? AND `topic_id`=?", userId, topicId) if err != nil { goku.Logger().Errorln(err.Error()) return false, err } var afrow int64 afrow, err = r.RowsAffected() if err != nil { goku.Logger().Errorln(err.Error()) return false, err } if afrow > 0 { // 取消关注话题成功,将话题的链接从用户的推送列表中移除 LinkForUser_UnFollowTopic(userId, topicId) // 更新用户关注话题的数量 User_IncCount(db, userId, "ftopic_count", -1) // 更新话题的关注用户数 Topic_IncCount(db, topicId, "follower_count", -1) return true, nil } return false, nil }
//收藏link func SaveUserFavorite(f map[string]interface{}) error { var db *goku.MysqlDB = GetDB() defer db.Close() _, err := db.Insert("user_favorite_link", f) return err }
func User_SaveMap(m map[string]interface{}) (sql.Result, error) { var db *goku.MysqlDB = GetDB() defer db.Close() m["email_lower"] = strings.ToLower(m["email"].(string)) r, err := db.Insert("user", m) return r, err }
/** * 删除tui_link_for_handle已经处理的数据 */ func delete_tui_link_for_handle(handleTime time.Time, db *goku.MysqlDB) error { sql := "DELETE FROM tui_link_for_handle WHERE `insert_time`<=? " _, err := db.Query(sql, handleTime) return err }
/** * 链接推送给话题(热议) 2:这个小时;3:今天;4:这周;5:这个月;6:今年 */ func link_for_topic_hop_time(timeType int, handleTime time.Time, db *goku.MysqlDB) error { var t time.Time switch { case timeType == 2: t = utils.ThisHour() case timeType == 3: t = utils.ThisDate() case timeType == 4: t = utils.ThisWeek() case timeType == 5: t = utils.ThisMonth() case timeType == 6: t = utils.ThisYear() } sql := `INSERT ignore INTO tui_link_for_topic_hot(topic_id,link_id,create_time,dispute_score,time_type) ( SELECT TL.topic_id,H.link_id,H.create_time,L.dispute_score,? AS time_type FROM tui_link_for_handle H INNER JOIN topic_link TL ON H.insert_time<=? AND H.create_time>=? AND H.link_id=TL.link_id INNER JOIN link L ON L.id=H.link_id );` _, err := db.Query(sql, timeType, handleTime, t) return err }
// userId 取消关注 followId func User_UnFollow(userId, followId int64) (bool, error) { if userId < 1 || followId < 1 { return false, errors.New("参数错误") } if userId == followId { return false, errors.New("不能取消关注自己") } var db *goku.MysqlDB = GetDB() defer db.Close() r, err := db.Delete("user_follow", "`user_id`=? AND `follow_id`=?", userId, followId) if err != nil { goku.Logger().Errorln(err.Error()) return false, err } var afrow int64 afrow, err = r.RowsAffected() if err != nil { goku.Logger().Errorln(err.Error()) return false, err } if afrow > 0 { LinkForUser_UnFollowUser(userId, followId) // 更新粉丝数 User_IncCount(db, userId, "friend_count", -1) // 更新关注数 User_IncCount(db, followId, "follower_count", -1) return true, nil } return false, nil }
func Del_link_for_topic_all(db *goku.MysqlDB) error { err := del_link_for_topic_later_top("tui_link_for_topic_top", "reddit_score DESC,link_id DESC", db) if err == nil { err = del_link_for_topic_later_top("tui_link_for_topic_later", "link_id DESC", db) } if err == nil { _, err = db.Query(`DELETE FROM tui_link_for_topic_hot WHERE (time_type=2 AND create_time<?) OR (time_type=3 AND create_time<?) OR (time_type=4 AND create_time<?) OR (time_type=5 AND create_time<?) OR (time_type=6 AND create_time<?)`, utils.ThisHour(), utils.ThisDate(), utils.ThisWeek(), utils.ThisMonth(), utils.ThisYear()) if err == nil { err = del_link_for_topic_hot_vote("tui_link_for_topic_hot", "dispute_score DESC,link_id DESC", db) } } if err == nil { _, err = db.Query(`DELETE FROM tui_link_for_topic_vote WHERE (time_type=2 AND create_time<?) OR (time_type=3 AND create_time<?) OR (time_type=4 AND create_time<?) OR (time_type=5 AND create_time<?) OR (time_type=6 AND create_time<?)`, utils.ThisHour(), utils.ThisDate(), utils.ThisWeek(), utils.ThisMonth(), utils.ThisYear()) if err == nil { err = del_link_for_topic_hot_vote("tui_link_for_topic_vote", "vote DESC,link_id DESC", db) } } return err }
func thirdPartyUser_SearchOneBy(criteria string, values ...interface{}) (u *ThirdPartyUser) { var db *goku.MysqlDB = GetDB() defer db.Close() sql := "SELECT `user_id`, `third_party`, `third_party_user_id`, `third_party_email`, `access_token`, `refresh_token`, `token_expire_time`, `create_time`, `last_active_time`, `avatar_url`, `link` FROM `third_party_user` WHERE " + criteria + " limit 1" thirdPartyUserRow, err := db.Query(sql, values...) if err != nil { return } if thirdPartyUserRow == nil { return } if thirdPartyUserRow.Next() { u = &ThirdPartyUser{} err = thirdPartyUserRow.Scan( &u.UserId, &u.ThirdParty, &u.ThirdPartyUserId, &u.ThirdPartyEmail, &u.AccessToken, &u.RefreshToken, &u.TokenExpireTime, &u.CreateTime, &u.LastActiveTime, &u.AvatarUrl, &u.Link) } if err != nil { u = nil } return }
// 关注好友的最新链接的未读数 func NewestLinkUnread_Friends(userId, lastReadLinkId int64) (int64, error) { if userId < 1 { return 0, nil } var db *goku.MysqlDB = GetDB() defer db.Close() qi := goku.SqlQueryInfo{} qi.Where = "`user_id`=? and `link_id`>?" qi.Params = []interface{}{userId, lastReadLinkId} qi.Fields = "count(*)" tableName := LinkForUser_TableName(userId) rows, err := db.Select(tableName, qi) var unreadCount int64 if err != nil { goku.Logger().Errorln(err.Error()) return 0, err } if rows.Next() { err = rows.Scan(&unreadCount) if err != nil { goku.Logger().Errorln(err.Error()) return 0, err } } return unreadCount, nil }
// 减少DB操作 // @t: 推送类型, 1:关注的用户, 2:关注的话题 func linkForUser_AddWithDb(db *goku.MysqlDB, userId, linkId int64, t int) error { m := map[string]interface{}{ "user_id": userId, "link_id": linkId, "create_time": time.Now(), } if t == 1 { m["user_count"] = 1 } else { m["topic_count"] = 1 } _, err := db.Insert(LinkForUser_TableName(userId), m) if err != nil { if strings.Index(err.Error(), "Duplicate entry") > -1 { m := map[string]interface{}{} if t == 1 { m["user_count"] = 1 } else { m["topic_count"] = 1 } _, err = db.Update(LinkForUser_TableName(userId), m, "user_id=? and link_id=?", userId, linkId) if err != nil { goku.Logger().Errorln(err.Error()) } } else { goku.Logger().Errorln(err.Error()) } } return err }
// 获取用户关注的话题列表 func User_GetFollowTopics(userId int64, page, pagesize int) ([]Topic, error) { var db *goku.MysqlDB = GetDB() defer db.Close() page, pagesize = utils.PageCheck(page, pagesize) qi := goku.SqlQueryInfo{} qi.Fields = "t.id, t.name, t.description, t.pic" qi.Join = " tf INNER JOIN `topic` t ON tf.topic_id=t.id" qi.Where = "tf.user_id=?" qi.Params = []interface{}{userId} qi.Limit = pagesize qi.Offset = pagesize * page qi.Order = "t.id desc" rows, err := db.Select("topic_follow", qi) if err != nil { goku.Logger().Errorln(err.Error()) return nil, err } defer rows.Close() topics := make([]Topic, 0) for rows.Next() { topic := Topic{} err = rows.Scan(&topic.Id, &topic.Name, &topic.Description, &topic.Pic) if err != nil { goku.Logger().Errorln(err.Error()) return nil, err } topics = append(topics, topic) } return topics, nil }
func User_GetByTicket(ticket string) (*User, error) { redisClient := GetRedis() defer redisClient.Quit() id, err := redisClient.Get(ticket) if err != nil { return nil, err } if id.String() == "" { return nil, nil } var db *goku.MysqlDB = GetDB() defer db.Close() var user *User = new(User) err = db.GetStruct(user, "id=?", id.String()) if err != nil { fmt.Printf("err: %v\n", err) return nil, err } if user.Id > 0 { return user, nil } return nil, nil }
func GetTodo(id int) (Todo, error) { var db *goku.MysqlDB = GetDB() defer db.Close() var todo Todo = Todo{} err := db.GetStruct(&todo, "id=?", id) return todo, err }
func GetTodoLists() (*[]Todo, error) { var db *goku.MysqlDB = GetDB() defer db.Close() qi := goku.SqlQueryInfo{} qi.Order = "finished asc, id desc" var todos []Todo err := db.GetStructs(&todos, qi) return &todos, err }
// 加(减)用户信息里面的统计数据 // @field: 要修改的字段 // @inc: 要增加或减少的值 func User_IncCount(db *goku.MysqlDB, userid int64, field string, inc int) (sql.Result, error) { // m := map[string]interface{}{field: fmt.Sprintf("%v+%v", field, inc)} // r, err := db.Update("user", m, "id=?", userid) r, err := db.Exec(fmt.Sprintf("UPDATE `user` SET %s=%s+? WHERE id=?;", field, field), inc, userid) if err != nil { goku.Logger().Errorln(err.Error()) } return r, err }
//更新邀请码 func UpdateIsRegister(invite *RegisterInvite) { if !golink.Invite_Enabled { return } var db *goku.MysqlDB = GetDB() defer db.Close() db.Query("UPDATE `register_invite` SET `is_register`=1 WHERE `Guid`=?", invite.Guid) }
func (ur *UserRecovery) Update() (sql.Result, error) { m := make(map[string]interface{}) m["active"] = ur.Active m["recovery_time"] = ur.RecoveryTime var db *goku.MysqlDB = GetDB() defer db.Close() r, err := db.Update("user_recovery", m, "`user_id`=? AND `token`=?", ur.UserId, ur.Token) return r, err }
// 更新话题的图片地址(只存相对路径) func Topic_UpdatePic(id int64, pic string) (sql.Result, error) { var db *goku.MysqlDB = GetDB() defer db.Close() m := map[string]interface{}{"pic": pic} r, err := db.Update("topic", m, "id=?", id) if err != nil { goku.Logger().Errorln(err.Error()) } return r, err }
/** * 链接推送给话题(最新) */ func Link_for_topic_later(handleTime time.Time, db *goku.MysqlDB) error { sql := `INSERT ignore INTO tui_link_for_topic_later(topic_id,link_id,create_time) ( SELECT TL.topic_id,H.link_id,H.create_time FROM tui_link_for_handle H INNER JOIN topic_link TL ON H.data_type=1 AND H.insert_time<=? AND H.link_id=TL.link_id );` _, err := db.Query(sql, handleTime) return err }
func Comment_DelById(id int64) error { var db *goku.MysqlDB = GetDB() defer db.Close() _, err := db.Query("UPDATE `comment` SET status=2 WHERE id=?", id) if err != nil { goku.Logger().Errorln(err.Error()) return err } return nil }
/** * 链接推送给网站首页(热门) */ func Link_for_home_top(handleTime time.Time, db *goku.MysqlDB) error { sql := `INSERT ignore INTO tui_link_for_home(link_id,create_time,data_type,score) ( SELECT H.link_id,H.create_time,2,L.reddit_score FROM tui_link_for_handle H INNER JOIN link L ON H.insert_time<=? AND L.id=H.link_id ); ` _, err := db.Query(sql, handleTime) return err }
func (ur *UserRecovery) Save() (sql.Result, error) { var db *goku.MysqlDB = GetDB() defer db.Close() m := make(map[string]interface{}) m["user_id"] = ur.UserId m["token"] = ur.Token m["active"] = ur.Active m["create_time"] = ur.CreateTime m["recovery_time"] = ur.RecoveryTime r, err := db.Insert("user_recovery", m) return r, err }