Golang db.DB类代码示例

func upgrade2to3(db *db.DB) error {
	tx, err := db.Beginx()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	t := time.Now().AddDate(0, -1, 0)
	_, err = tx.Exec(upgrade2To3SplitStatesToUnread, t)
	if err != nil {
		return err
	}

	_, err = tx.Exec(upgrade2To3SplitStatesToFavorite)
	if err != nil {
		return err
	}

	_, err = tx.Exec("DROP TABLE users_articles_states")
	if err != nil {
		return err
	}

	return tx.Commit()
}

func getArticles(u content.User, dbo *db.DB, logger webfw.Logger, opts data.ArticleQueryOptions, sorting content.ArticleSorting, join, where string, args []interface{}) (ua []content.UserArticle) {
	if u.HasErr() {
		return
	}

	var err error
	if getArticlesTemplate == nil {
		getArticlesTemplate, err = template.New("read-state-update-sql").
			Parse(dbo.SQL().User.GetArticlesTemplate)

		if err != nil {
			u.Err(fmt.Errorf("Error generating get-articles-update template: %v", err))
			return
		}
	}

	/* Much faster than using 'ORDER BY read'
	 * TODO: potential overall improvement for fetching pages other than the
	 * first by using the unread count and moving the offset based on it
	 */
	if opts.UnreadFirst && opts.Offset == 0 {
		originalUnreadOnly := opts.UnreadOnly

		opts.UnreadFirst = false
		opts.UnreadOnly = true

		ua = internalGetArticles(u, dbo, logger, opts, sorting, join, where, args)

		if !originalUnreadOnly && (opts.Limit == 0 || opts.Limit > len(ua)) {
			if opts.Limit > 0 {
				opts.Limit -= len(ua)
			}
			opts.UnreadOnly = false
			opts.ReadOnly = true

			readOnly := internalGetArticles(u, dbo, logger, opts, sorting, join, where, args)

			ua = append(ua, readOnly...)
		}

		return
	}

	return internalGetArticles(u, dbo, logger, opts, sorting, join, where, args)
}

func updateArticle(a content.Article, tx *sqlx.Tx, db *db.DB, logger webfw.Logger) {
	if a.HasErr() {
		return
	}

	if err := a.Validate(); err != nil {
		a.Err(err)
		return
	}

	logger.Infof("Updating article %s\n", a)

	d := a.Data()
	s := db.SQL()

	stmt, err := tx.Preparex(s.Article.Update)
	if err != nil {
		a.Err(err)
		return
	}
	defer stmt.Close()

	res, err := stmt.Exec(d.Title, d.Description, d.Date, d.Guid, d.Link, d.FeedId)
	if err != nil {
		a.Err(err)
		return
	}

	if num, err := res.RowsAffected(); err != nil && err == sql.ErrNoRows || num == 0 {
		logger.Infof("Creating article %s\n", a)

		aId, err := db.CreateWithId(tx, s.Article.Create, d.FeedId, d.Link, d.Guid,
			d.Title, d.Description, d.Date)

		if err != nil {
			a.Err(fmt.Errorf("Error updating article %s (guid - %v, link - %s): %v", a, d.Guid, d.Link, err))
			return
		}

		d.Id = data.ArticleId(aId)
		d.IsNew = true
		a.Data(d)
	}
}

func upgrade3to4(db *db.DB) error {
	table := "users_feeds_tags"
	var tabledef string

	for _, s := range initSQL {
		if strings.Contains(s, table) {
			tabledef = s
		}
	}

	if tabledef == "" {
		return errors.New("Definition for users_feeds_tags not found")
	}

	tx, err := db.Beginx()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	_, err = tx.Exec("ALTER TABLE " + table + " RENAME TO " + table + "2")
	if err != nil {
		return err
	}

	_, err = tx.Exec(tabledef)
	if err != nil {
		return err
	}

	_, err = tx.Exec(upgrade3To4PopulateTags)
	if err != nil {
		return err
	}

	_, err = tx.Exec(upgrade3To4PopulateUsersFeedsTags)
	if err != nil {
		return err
	}

	_, err = tx.Exec("DROP TABLE " + table + "2")
	if err != nil {
		return err
	}

	return tx.Commit()
}

func upgrade1to2(db *db.DB) error {
	tx, err := db.Beginx()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	_, err = tx.Exec(upgrade1To2MergeReadAndFav)
	if err != nil {
		return err
	}

	_, err = tx.Exec("DROP TABLE users_articles_read")
	if err != nil {
		return err
	}

	_, err = tx.Exec("DROP TABLE users_articles_fav")
	if err != nil {
		return err
	}

	return tx.Commit()
}

func readState(u content.User, dbo *db.DB, logger webfw.Logger, opts data.ArticleUpdateStateOptions, read bool, join, joinPredicate, deleteJoin, deleteWhere string, insertArgs, deleteArgs []interface{}) {
	if u.HasErr() {
		return
	}

	s := dbo.SQL()

	var err error
	if readStateInsertTemplate == nil {
		readStateInsertTemplate, err = template.New("read-state-insert-sql").
			Parse(s.User.ReadStateInsertTemplate)

		if err != nil {
			u.Err(fmt.Errorf("Error generating read-state-insert template: %v", err))
			return
		}
	}
	if readStateDeleteTemplate == nil {
		readStateDeleteTemplate, err = template.New("read-state-delete-sql").
			Parse(s.User.ReadStateDeleteTemplate)

		if err != nil {
			u.Err(fmt.Errorf("Error generating read-state-delete template: %v", err))
			return
		}
	}

	tx, err := dbo.Beginx()
	if err != nil {
		u.Err(err)
		return
	}
	defer tx.Rollback()

	if read {
		args := append([]interface{}{u.Data().Login}, deleteArgs...)

		buf := util.BufferPool.GetBuffer()
		defer util.BufferPool.Put(buf)

		data := readStateDeleteData{}

		if deleteJoin != "" {
			data.Join = deleteJoin
		}

		if opts.FavoriteOnly {
			data.Join += s.User.ReadStateDeleteFavoriteJoin
		}

		if opts.UntaggedOnly {
			data.Join += s.User.ReadStateDeleteUntaggedJoin
		}

		where := []string{}

		if deleteWhere != "" {
			where = append(where, deleteWhere)
		}

		if !opts.BeforeDate.IsZero() {
			where = append(where, fmt.Sprintf("(a.date IS NULL OR a.date < $%d)", len(args)+1))
			args = append(args, opts.BeforeDate)
		}
		if !opts.AfterDate.IsZero() {
			where = append(where, fmt.Sprintf("a.date > $%d", len(args)+1))
			args = append(args, opts.AfterDate)
		}

		if opts.BeforeId > 0 {
			where = append(where, fmt.Sprintf("a.id < $%d", len(args)+1))
			args = append(args, opts.BeforeId)
		}
		if opts.AfterId > 0 {
			where = append(where, fmt.Sprintf("a.id > $%d", len(args)+1))
			args = append(args, opts.AfterId)
		}

		if opts.FavoriteOnly {
			where = append(where, "af.article_id IS NOT NULL")
		}

		if opts.UntaggedOnly {
			where = append(where, "uft.feed_id IS NULL")
		}

		if len(where) > 0 {
			data.Where = " WHERE " + strings.Join(where, " AND ")
		}

		if err := readStateDeleteTemplate.Execute(buf, data); err != nil {
			u.Err(fmt.Errorf("Error executing read-state-delete template: %v", err))
			return
		}

		sql := buf.String()
		logger.Debugf("Read state delete SQL:\n%s\nArgs:%v\n", sql, args)

		stmt, err := tx.Preparex(sql)

//.........这里部分代码省略.........

func articleCount(u content.User, dbo *db.DB, logger webfw.Logger, opts data.ArticleCountOptions, join, where string, args []interface{}) (count int64) {
	if u.HasErr() {
		return
	}

	s := dbo.SQL()
	var err error
	if articleCountTemplate == nil {
		articleCountTemplate, err = template.New("article-count-sql").
			Parse(s.User.ArticleCountTemplate)

		if err != nil {
			u.Err(fmt.Errorf("Error generating article-count template: %v", err))
			return
		}
	}

	renderData := articleCountData{}
	containsUserFeeds := !opts.UnreadOnly && !opts.FavoriteOnly

	if containsUserFeeds {
		renderData.Join += s.User.ArticleCountUserFeedsJoin
	} else {
		if opts.UnreadOnly {
			renderData.Join += s.User.ArticleCountUnreadJoin
		}
		if opts.FavoriteOnly {
			renderData.Join += s.User.ArticleCountFavoriteJoin
		}
	}

	if opts.UntaggedOnly {
		renderData.Join += s.User.ArticleCountUntaggedJoin
	}

	if join != "" {
		renderData.Join += " " + join
	}

	args = append([]interface{}{u.Data().Login}, args...)

	whereSlice := []string{}

	if opts.UnreadOnly {
		whereSlice = append(whereSlice, "au.article_id IS NOT NULL AND au.user_login = $1")
	}
	if opts.FavoriteOnly {
		whereSlice = append(whereSlice, "af.article_id IS NOT NULL AND af.user_login = $1")
	}
	if opts.UntaggedOnly {
		whereSlice = append(whereSlice, "uft.feed_id IS NULL")
	}

	if where != "" {
		whereSlice = append(whereSlice, where)
	}

	if opts.BeforeId > 0 {
		whereSlice = append(whereSlice, fmt.Sprintf("a.id < $%d", len(args)+1))
		args = append(args, opts.BeforeId)
	}
	if opts.AfterId > 0 {
		whereSlice = append(whereSlice, fmt.Sprintf("a.id > $%d", len(args)+1))
		args = append(args, opts.AfterId)
	}

	if !opts.BeforeDate.IsZero() {
		whereSlice = append(whereSlice, fmt.Sprintf("(a.date IS NULL OR a.date < $%d)", len(args)+1))
		args = append(args, opts.BeforeDate)
	}

	if !opts.AfterDate.IsZero() {
		whereSlice = append(whereSlice, fmt.Sprintf("a.date > $%d", len(args)+1))
		args = append(args, opts.AfterDate)
	}

	if len(whereSlice) > 0 {
		renderData.Where = "WHERE " + strings.Join(whereSlice, " AND ")
	}

	buf := util.BufferPool.GetBuffer()
	defer util.BufferPool.Put(buf)

	if err := articleCountTemplate.Execute(buf, renderData); err != nil {
		u.Err(fmt.Errorf("Error executing article-count template: %v", err))
		return
	}

	sql := buf.String()

	logger.Debugf("Article count SQL:\n%s\nArgs:%v\n", sql, args)
	if err := dbo.Get(&count, sql, args...); err != nil {
		u.Err(err)
		return
	}

	return
}

func internalGetArticles(u content.User, dbo *db.DB, logger webfw.Logger, opts data.ArticleQueryOptions, sorting content.ArticleSorting, join, where string, args []interface{}) (ua []content.UserArticle) {
	renderData := getArticlesData{}
	s := dbo.SQL()
	if opts.IncludeScores {
		renderData.Columns += ", asco.score"
		renderData.Join += s.User.GetArticlesScoreJoin
	}

	if opts.UntaggedOnly {
		renderData.Join += s.User.GetArticlesUntaggedJoin
	}

	if join != "" {
		renderData.Join += " " + join
	}

	args = append([]interface{}{u.Data().Login}, args...)

	whereSlice := []string{}

	if opts.UnreadOnly {
		whereSlice = append(whereSlice, "au.article_id IS NOT NULL")
	} else if opts.ReadOnly {
		whereSlice = append(whereSlice, "au.article_id IS NULL")
	}

	if opts.UntaggedOnly {
		whereSlice = append(whereSlice, "uft.feed_id IS NULL")
	}

	if where != "" {
		whereSlice = append(whereSlice, where)
	}

	if opts.BeforeId > 0 {
		whereSlice = append(whereSlice, fmt.Sprintf("a.id < $%d", len(args)+1))
		args = append(args, opts.BeforeId)
	}
	if opts.AfterId > 0 {
		whereSlice = append(whereSlice, fmt.Sprintf("a.id > $%d", len(args)+1))
		args = append(args, opts.AfterId)
	}

	if opts.FavoriteOnly {
		whereSlice = append(whereSlice, "af.article_id IS NOT NULL")
	}

	if !opts.BeforeDate.IsZero() {
		whereSlice = append(whereSlice, fmt.Sprintf("(a.date IS NULL OR a.date < $%d)", len(args)+1))
		args = append(args, opts.BeforeDate)
	}

	if !opts.AfterDate.IsZero() {
		whereSlice = append(whereSlice, fmt.Sprintf("a.date > $%d", len(args)+1))
		args = append(args, opts.AfterDate)
	}

	if len(whereSlice) > 0 {
		renderData.Where = "WHERE " + strings.Join(whereSlice, " AND ")
	}

	sortingField := sorting.Field()
	sortingOrder := sorting.Order()

	fields := []string{}

	if opts.IncludeScores && opts.HighScoredFirst {
		field := "asco.score"
		if sortingOrder == data.DescendingOrder {
			field += " DESC"
		}
		fields = append(fields, field)
	}

	if opts.UnreadFirst {
		fields = append(fields, "read")
	}

	switch sortingField {
	case data.SortById:
		fields = append(fields, "a.id")
	case data.SortByDate:
		fields = append(fields, "a.date")
	}
	if len(fields) > 0 {
		renderData.Order = " ORDER BY " + strings.Join(fields, ", ")

		if sortingOrder == data.DescendingOrder {
			renderData.Order += " DESC"
		}
	}

	if opts.Limit > 0 {
		renderData.Limit = fmt.Sprintf(" LIMIT $%d OFFSET $%d", len(args)+1, len(args)+2)
		args = append(args, opts.Limit, opts.Offset)
	}

	buf := util.BufferPool.GetBuffer()
	defer util.BufferPool.Put(buf)

//.........这里部分代码省略.........

func getArticles(u content.User, dbo *db.DB, logger webfw.Logger, sorting content.ArticleSorting, columns, join, where, order string, args []interface{}, paging ...int) (ua []content.UserArticle) {
	if u.HasErr() {
		return
	}

	sql := dbo.SQL("get_article_columns")
	if columns != "" {
		sql += ", " + columns
	}

	sql += dbo.SQL("get_article_tables")
	if join != "" {
		sql += " " + join
	}

	sql += dbo.SQL("get_article_joins")

	args = append([]interface{}{u.Data().Login}, args...)
	if where != "" {
		sql += " AND " + where
	}

	sortingField := sorting.Field()
	sortingOrder := sorting.Order()

	fields := []string{}
	if order != "" {
		fields = append(fields, order)
	}
	switch sortingField {
	case data.SortById:
		fields = append(fields, "a.id")
	case data.SortByDate:
		fields = append(fields, "a.date")
	}
	if len(fields) > 0 {
		sql += " ORDER BY "

		sql += strings.Join(fields, ",")

		if sortingOrder == data.DescendingOrder {
			sql += " DESC"
		}
	}

	if len(paging) > 0 {
		limit, offset := pagingLimit(paging)

		sql += fmt.Sprintf(" LIMIT $%d OFFSET $%d", len(args)+1, len(args)+2)
		args = append(args, limit, offset)
	}

	var data []data.Article
	logger.Debugf("Articles SQL:\n%s\nArgs:%q\n", sql, args)
	if err := dbo.Select(&data, sql, args...); err != nil {
		u.Err(err)
		return
	}

	ua = make([]content.UserArticle, len(data))
	for i := range data {
		ua[i] = u.Repo().UserArticle(u)
		ua[i].Data(data[i])
	}

	return
}