perms-system-server/internal/model/role/sysRoleModel.go

package role

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"

	"perms-system-server/internal/consts"

	"github.com/zeromicro/go-zero/core/stores/cache"
	"github.com/zeromicro/go-zero/core/stores/sqlx"
)

var ErrUpdateConflict = errors.New("update conflict: data has been modified by another operation")

var _ SysRoleModel = (*customSysRoleModel)(nil)

type (
	SysRoleModel interface {
		sysRoleModel
		FindListByProductCode(ctx context.Context, productCode string, page, pageSize int64) ([]*SysRole, int64, error)
		FindByIds(ctx context.Context, ids []int64) ([]*SysRole, error)
		FindMinPermsLevelByUserIdAndProductCode(ctx context.Context, userId int64, productCode string) (int64, error)
		UpdateWithOptLock(ctx context.Context, data *SysRole, expectedUpdateTime int64) error
		// LockByIdTx 在当前事务里锁住 sys_role 行（SELECT ... FOR UPDATE），用于把"同一 role 的
		// BindRolePerms 并发覆盖"串行化，消除"existing 在事务外读 + 事务内 delete/insert"
		// 造成的第三态合并问题（见审计 M-R10-2）。
		LockByIdTx(ctx context.Context, session sqlx.Session, id int64) (*SysRole, error)
	}

	customSysRoleModel struct {
		*defaultSysRoleModel
	}
)

func NewSysRoleModel(conn sqlx.SqlConn, c cache.CacheConf, cachePrefix string, opts ...cache.Option) SysRoleModel {
	return &customSysRoleModel{
		defaultSysRoleModel: newSysRoleModel(conn, c, cachePrefix, opts...),
	}
}

func (m *customSysRoleModel) FindListByProductCode(ctx context.Context, productCode string, page, pageSize int64) ([]*SysRole, int64, error) {
	var total int64
	countQuery := fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE `productCode` = ?", m.table)
	if err := m.QueryRowNoCacheCtx(ctx, &total, countQuery, productCode); err != nil {
		return nil, 0, err
	}

	var list []*SysRole
	query := fmt.Sprintf("SELECT %s FROM %s WHERE `productCode` = ? ORDER BY `permsLevel` ASC, id DESC LIMIT ?,?", sysRoleRows, m.table)
	if err := m.QueryRowsNoCacheCtx(ctx, &list, query, productCode, (page-1)*pageSize, pageSize); err != nil {
		return nil, 0, err
	}

	return list, total, nil
}

func (m *customSysRoleModel) FindByIds(ctx context.Context, ids []int64) ([]*SysRole, error) {
	if len(ids) == 0 {
		return nil, nil
	}
	args := make([]interface{}, len(ids))
	marks := make([]string, len(ids))
	for i, id := range ids {
		args[i] = id
		marks[i] = "?"
	}
	var list []*SysRole
	query := fmt.Sprintf("SELECT %s FROM %s WHERE `id` IN (%s)", sysRoleRows, m.table, strings.Join(marks, ","))
	if err := m.QueryRowsNoCacheCtx(ctx, &list, query, args...); err != nil {
		return nil, err
	}
	return list, nil
}

func (m *customSysRoleModel) UpdateWithOptLock(ctx context.Context, data *SysRole, expectedUpdateTime int64) error {
	sysRoleIdKey := fmt.Sprintf("%s%v", cacheSysRoleIdPrefix, data.Id)
	sysRoleProductCodeNameKey := fmt.Sprintf("%s%v:%v", cacheSysRoleProductCodeNamePrefix, data.ProductCode, data.Name)
	res, err := m.ExecCtx(ctx, func(ctx context.Context, conn sqlx.SqlConn) (sql.Result, error) {
		query := fmt.Sprintf("UPDATE %s SET `name`=?, `remark`=?, `status`=?, `permsLevel`=?, `updateTime`=? WHERE `id`=? AND `updateTime`=?", m.table)
		return conn.ExecCtx(ctx, query, data.Name, data.Remark, data.Status, data.PermsLevel, data.UpdateTime, data.Id, expectedUpdateTime)
	}, sysRoleIdKey, sysRoleProductCodeNameKey)
	if err != nil {
		return err
	}
	affected, _ := res.RowsAffected()
	if affected == 0 {
		return ErrUpdateConflict
	}
	return nil
}

// LockByIdTx 见接口注释。注意：本函数不走缓存层，必须在 TransactCtx / Session 下调用；
// SELECT ... FOR UPDATE 的行锁由 InnoDB 持有到事务结束。
func (m *customSysRoleModel) LockByIdTx(ctx context.Context, session sqlx.Session, id int64) (*SysRole, error) {
	var data SysRole
	query := fmt.Sprintf("SELECT %s FROM %s WHERE `id` = ? LIMIT 1 FOR UPDATE", sysRoleRows, m.table)
	if err := session.QueryRowCtx(ctx, &data, query, id); err != nil {
		return nil, err
	}
	return &data, nil
}

func (m *customSysRoleModel) FindMinPermsLevelByUserIdAndProductCode(ctx context.Context, userId int64, productCode string) (int64, error) {
	var level int64
	query := fmt.Sprintf(
		"SELECT IFNULL(MIN(r.`permsLevel`), -1) FROM %s r INNER JOIN `sys_user_role` ur ON r.`id` = ur.`roleId` WHERE ur.`userId` = ? AND r.`productCode` = ? AND r.`status` = ?",
		m.table,
	)
	if err := m.QueryRowNoCacheCtx(ctx, &level, query, userId, productCode, consts.StatusEnabled); err != nil {
		return 0, err
	}
	if level < 0 {
		return 0, ErrNotFound
	}
	return level, nil
}