Golang beego_study-db.NewDB类(方法)实例源码

func LastArticle() (entities.Article, error) {
	var err error
	var article entities.Article
	db := db.NewDB()
	err = db.QueryTable("article").OrderBy("-id").One(&article, "id", "user_id", "title", "tag", "content", "created_at", "updated_at")
	return article, err
}

func testPagination(t *testing.T) {
	fmt.Println("1111")
	pagination, _ := db.NewDB().From("user").Select("id", "name").Pagination(&[]entities.User{}, 1, 10)
	for _, value := range pagination.Data {
		fmt.Println("id", value.(entities.User).Id, "name", value.(entities.User).Name)
	}
}

func UpdateArticle(article *entities.Article) error {

	var err error
	db := db.NewDB()
	db.Begin()

	sql := "update article set title = ? ,tags=?,categories=?, content=?, updated_at=now() where user_id = ? and  id = ? "

	_, err = db.Raw(sql, []interface{}{article.Title, article.Tags, article.Categories, article.Content, article.UserId, article.Id}).Exec()

	if nil == err {
		var categories []entities.Category
		if len(article.Categories) > 0 {
			categoryNames := strings.Split(article.Categories, ",")
			categories = entities.NewCategories(article.UserId, categoryNames)
		}
		BatchSaveOrUpdateCategory(db, categories)
	}

	if nil == err {
		db.Commit()
	} else {
		db.Rollback()
	}

	return err
}

func Articles(page int) ([]entities.Article, error) {
	var err error
	var articles []entities.Article
	db := db.NewDB()
	_, err = db.QueryTable("article").All(&articles, "id", "user_id", "title", "tag", "content", "created_at", "updated_at")
	return articles, err
}

func ArticleByIdAndUserId(articleId int64, userId int64) (*entities.Article, error) {
	var err error
	var article entities.Article
	db := db.NewDB()
	err = db.QueryTable("article").Filter("user_id", userId).Filter("id", articleId).One(&article)
	return &article, err
}

func SaveArticle(article *entities.Article) error {

	var err error
	db := db.NewDB()
	db.Begin()

	bBuffer := bytes.NewBufferString("insert into article (user_id,title, tags,categories, content, created_at) ")
	bBuffer.WriteString("values(?,?,?,?,?,now())")

	_, err = db.Raw(bBuffer.String(), []interface{}{article.UserId, article.Title, article.Tags, article.Categories, article.Content}).Exec()

	if nil == err {
		var categories []entities.Category
		if len(article.Categories) > 0 {
			categoryNames := strings.Split(article.Categories, ",")
			categories = entities.NewCategories(article.UserId, categoryNames)
		}
		BatchSaveOrUpdateCategory(db, categories)
	}

	if nil == err {
		db.Commit()
	} else {
		db.Rollback()
	}

	return err
}

func LastArticle() (entities.Article, error) {
	var err error
	var article entities.Article
	db := db.NewDB()
	err = db.QueryTable("article").OrderBy("-id").One(&article)
	return article, err
}

func TestSave(t *testing.T) {
	article := entities.Article{UserId: 1, Title: "title", Tags: "go,reis", Categories: "go3,go4", Content: "content", CreatedAt: time.Now()}
	services.SaveArticle(&article)
	db := db.NewDB()
	db.Insert(&article)

	fmt.Println("&article:", &article)
}

func ArticleLikeLogs(userId int64, articleIds interface{}) []int64 {
	var signs []int64
	articleIdStr := utils.SliceToString(articleIds, ",")
	sql := "select article_id from article_like where user_id = ? and valid=1 and article_id in (" + articleIdStr + ") "
	db := db.NewDB()
	db.Raw(sql, userId).QueryRows(&signs)
	return signs
}

func ValidSystemMail() (entities.SystemMail, error) {

	var mail entities.SystemMail

	db := db.NewDB()

	err := db.QueryTable("system_mail").Filter("valid", entities.SYSTEM_MAIL_VALID_YES).One(&mail)

	return mail, err
}

func TestIn(t *testing.T) {

	var slice = []interface{}{1, 2, 3, 4, 5, 6}

	db := db.NewDB()
	query := db.From("article").In("id", slice)

	fmt.Print(query.ToSql())

}

func UserMap(ids []interface{}) map[int64]entities.User {
	var userMap map[int64]entities.User = make(map[int64]entities.User)
	var users []entities.User
	db := db.NewDB()
	db.From("user").Select("id", "name", "nick", "view_count", "head").In("id", ids).All(&users)

	for _, user := range users {
		userMap[user.Id] = user
	}
	return userMap
}

func TopLikeArticles() []entities.Article {

	var articles []entities.Article
	sql := "select id,user_id,title from article where like_count >0 and  created_at > date_sub(now(),interval ? DAY) order by like_count desc limit 10"

	dayRange := ParameterIntValue("like_article_day_range")

	db := db.NewDB()
	db.Raw(sql, []interface{}{dayRange}).QueryRows(&articles)
	return articles
}

func SaveOrUpdateOpenUser(openUser *entities.OpenUser) error {
	sql := bytes.NewBufferString("insert ignore into open_user(open_id,user_id,type,nick,head,sex,age,province,city,created_at) ")
	sql.WriteString("values(?,?,?,?,?,?,?,?,?,now()) ")
	sql.WriteString("on duplicate key update nick =?,head=?,sex=?,province=?,city=?")
	db := db.NewDB()
	params := []interface{}{openUser.OpenId, openUser.UserId, openUser.Type, openUser.Nick}
	params = append(params, openUser.Head, openUser.Sex, openUser.Age, openUser.Province, openUser.City)
	params = append(params, openUser.Nick, openUser.Head, openUser.Sex, openUser.Province, openUser.City)
	_, err := db.Execute(sql.String(), params)
	return err
}

func Categories() ([]entities.Category, error) {
	var err error
	var categories []entities.Category
	var categoriesKey = constants.CATEGORY_KEY
	err = redis_util.Get(categoriesKey, &categories)
	if err == nil {
		return categories, nil;
	}
	db := db.NewDB()
	_, err = db.QueryTable("category").OrderBy("order").All(&categories, "id", "name", "order", "created_at", "updated_at")
	return categories, err
}

func (c *ArticleController) ArticleDetail() {

	id, _ := c.GetInt64(":id")
	ip := c.Ip()
	userId, _ := c.GetInt64(":userId")

	c.TplName = "article_detail.html"

	if id <= 0 {
		c.StringError("文章不存在")
		c.Ctx.Redirect(302, "/")
		return
	}

	host := c.Host()
	url := c.Ctx.Request.RequestURI

	reqUri := fmt.Sprintf("%s%s", host, url)

	c.Data["reqUri"] = reqUri

	article, error := services.ArticleById(id)

	if error == nil {
		writer, _ := services.User(article.UserId)
		article.User = writer
	}

	if userId > 0 {
		hasLike, err := services.HasLikeArticle(id, userId, db.NewDB())
		if nil == err {
			article.HasLike = hasLike
		}
	}

	if nil != error {
		c.StringError("文章不存在")
	} else {
		currUserId := c.CurrentUserId()
		success, _ := services.IncrViewCount(id, currUserId, ip)
		if success {
			article.ViewCount = article.ViewCount + 1
		}
		c.Data["article"] = article

		c.SetCategories(article.UserId)

		c.SetKeywords(article.Categories + "," + article.Tags)
		var subLength = services.ParameterIntValue("seo-description-length")
		c.SetDescription(article.ShortContent(subLength)).SetTitle(article.Title)
	}
}

func TestIn(t *testing.T) {

	var slice = []interface{}{1, 2, 3, 4, 5, 6}

	var articles []entities.Article
	db := db.NewDB()
	db.From("article").In("id", slice).All(&articles)

	for _, value := range articles {
		fmt.Print(value)
	}

}

func TestTransaction(t *testing.T) {
	db := db.NewDB()

	err := db.Begin()
	var sql = "update article set view_count=view_count+1  where user_id = ? and id = ? "
	db.Execute(sql, []interface{}{1, 35120})

	if err == nil {
		db.Commit()
	} else {
		db.Rollback()
	}
}

func IncrLikeCount(articleId int64, userId int64) (int, error) {
	db := db.NewDB()
	err := db.Begin()

	hasLiked, err := HasLikeArticle(articleId, userId, db)
	if nil != err {
		db.Rollback()
		return 0, err
	}

	article, err := ArticleById(articleId)

	articleOwnerId := article.UserId
	if err != nil {
		db.Rollback()
		return 0, err
	}

	if articleOwnerId <= 0 {
		db.Rollback()
		return 0, errors.New(exception.NOT_EXIST_ARTICLE_ERROR.Error())
	}

	var sql = "update article set like_count=like_count+? where user_id = ? and id = ? "
	var incrCount, valid int = 1, 1
	if hasLiked {
		incrCount = -1
		valid = 0
	}
	_, err = db.Execute(sql, []interface{}{incrCount, articleOwnerId, articleId})
	if nil == err {
		var articleLike = entities.ArticleLike{UserId: userId, ArticleId: articleId, Valid: valid, CreatedAt: time.Now()}
		err = SaveOrUpdate(articleLike, db)
	}

	if nil == err {
		sql = "update user set like_count=like_count+1  where id = ? "
		_, err = db.Execute(sql, []interface{}{articleOwnerId})
	}

	if nil == err {
		err = db.Commit()
	} else {
		incrCount = 0
		err = db.Rollback()
	}

	return incrCount, err
}

func AllArticles(userId int64, pagination *db.Pagination) {
	db := db.NewDB()
	var articles []entities.Article
	query := db.From("article")

	if userId > 0 {
		query.Where("user_id", userId)
	}

	query.OrderBy("created_at desc").FillPagination(&articles, pagination)

	articles = setUser(articles)

	pagination.SetData(articles)

}