labmem
c812cfb663
- 添加通用查询选项、分类过滤选项及动态缓存键生成 - 新增分类创建与更新时间字段 当前实现尚未完成，留待后续继续设计： - 明确通用查询选项、模型过滤条件与缓存策略的边界 - 补齐数据库回源，修正分类返回类型及过滤条件生效问题 - 修正分页偏移计算，增加参数边界校验和稳定排序 - 完善缓存故障降级、空结果缓存及写后失效策略 - 修正缓存键参数编码歧义，补齐查询名和版本前缀 - 补充测试并验证编译与查询行为
133 lines
3.5 KiB
Go
133 lines
3.5 KiB
Go
package model
|
|
|
|
import (
|
|
"context"
|
|
"fmt"
|
|
"time"
|
|
|
|
"lyra/internal/cache"
|
|
"lyra/internal/pkg/constant"
|
|
"lyra/internal/pkg/query"
|
|
"lyra/internal/util"
|
|
|
|
sq "github.com/Masterminds/squirrel"
|
|
"github.com/jmoiron/sqlx"
|
|
)
|
|
|
|
type Category struct {
|
|
ID int64 `db:"id"`
|
|
Slug string `db:"slug"`
|
|
Title string `db:"title"`
|
|
Sort int `db:"sort"`
|
|
CreatedAt time.Time `db:"created_at"`
|
|
UpdatedAt time.Time `db:"updated_at"`
|
|
}
|
|
|
|
type CategoryModel interface {
|
|
FindAll(ctx context.Context) ([]*Category, error)
|
|
ListWithOptions(ctx context.Context, queryOpt query.Option, modelOpts ...ModelOption) (resp []*Link, err error)
|
|
}
|
|
type defaultCategoryModel struct {
|
|
db *sqlx.DB
|
|
table string
|
|
cache *cache.Cache
|
|
}
|
|
|
|
func NewCategoryModel(db *sqlx.DB, cache *cache.Cache) CategoryModel {
|
|
return &defaultCategoryModel{db: db, table: "categories", cache: cache}
|
|
}
|
|
|
|
const categoryListCacheTTL = 5 * time.Minute
|
|
|
|
func (m *defaultCategoryModel) FindAll(ctx context.Context) ([]*Category, error) {
|
|
cached, hit, err := m.cache.Get[[]*Category](ctx, constant.CategoryListCacheKey)
|
|
if err != nil {
|
|
util.Logger(ctx).Error("读取分类列表缓存失败", "key", constant.CategoryListCacheKey, "error", err)
|
|
}
|
|
if hit {
|
|
return cached, nil
|
|
}
|
|
|
|
query, args, err := sq.
|
|
Select("id", "slug", "title", "sort").
|
|
From("public.categories").
|
|
Where(sq.GtOrEq{"sort": 1}).
|
|
OrderBy("sort ASC", "id ASC").
|
|
Limit(10).
|
|
PlaceholderFormat(sq.Dollar).
|
|
ToSql()
|
|
if err != nil {
|
|
return nil, fmt.Errorf("squirrel 出错: %w", err)
|
|
}
|
|
rows := make([]*Category, 0)
|
|
err = m.db.SelectContext(ctx, &rows, query, args...)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("数据库查询出错: %w", err)
|
|
}
|
|
if err := m.cache.Set(ctx, cache.Entry[[]*Category]{
|
|
Key: constant.CategoryListCacheKey,
|
|
Value: rows,
|
|
TTL: categoryListCacheTTL,
|
|
}); err != nil {
|
|
util.Logger(ctx).Error("写入分类列表缓存失败", "key", constant.CategoryListCacheKey, "error", err)
|
|
}
|
|
return rows, nil
|
|
}
|
|
|
|
type ModelOption func(*sq.SelectBuilder)
|
|
|
|
func (m *defaultCategoryModel) ListWithOptions(ctx context.Context, queryOpt query.Option, modelOpts ...ModelOption) (resp []*Link, err error) {
|
|
// 1.先构建查询语句(还没有deleted字段)
|
|
// 2.根据queryOpt的参数，设置分页、排序等
|
|
sqBuilder := sq.Select("id", "slug", "title", "sort").
|
|
From("categories").Limit(uint64(queryOpt.PageSize)).
|
|
Offset(uint64(queryOpt.Page)).
|
|
PlaceholderFormat(sq.Dollar)
|
|
for _, opt := range modelOpts {
|
|
opt(new(sqBuilder))
|
|
}
|
|
|
|
query, args, err := sqBuilder.ToSql()
|
|
if err != nil {
|
|
return nil, fmt.Errorf("squirrel 构造SQL出错: %w", err)
|
|
}
|
|
cacheKey, err := cache.GenCacheKey(m.table, query, args...)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
result, hit, err := m.cache.Get[[]*Link](ctx, cacheKey)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if hit {
|
|
return result, nil
|
|
}
|
|
if queryOpt.WithCacheWrite {
|
|
if err := m.cache.Set(ctx, cache.Entry[[]*Link]{
|
|
Key: cacheKey,
|
|
Value: result,
|
|
TTL: queryOpt.CacheTTL,
|
|
}); err != nil {
|
|
return nil, err
|
|
}
|
|
}
|
|
return result, nil
|
|
}
|
|
|
|
func WithCategorySlug(slug string) ModelOption {
|
|
return func(builder *sq.SelectBuilder) {
|
|
*builder = builder.Where(sq.Eq{"categories.slug": slug})
|
|
}
|
|
}
|
|
func WithCategoryTitle(title string) ModelOption {
|
|
return func(builder *sq.SelectBuilder) {
|
|
*builder = builder.Where(sq.Eq{"categories.title": title})
|
|
}
|
|
}
|
|
|
|
func WithCategorySort(sort int) ModelOption {
|
|
return func(builder *sq.SelectBuilder) {
|
|
*builder = builder.Where(sq.Eq{"categories.sort": sort})
|
|
}
|
|
}
|