使用SQLite实现CacheHelper的示例代码

# 1. 用于操作 sqlite 数据库
dotnet add package microsoft.data.sqlite

# 2. 用于高效的对象序列化/反序列化
dotnet add package messagepack

using microsoft.data.sqlite;
using system;
using system.io;
using system.threading.tasks;
using system.collections.concurrent;
using pei_repspark_admin_webapi.entities.constants;
using messagepack.resolvers;
using messagepack; // 请确保您的常量命名空间正确

namespace you_project.utils
{
    public static class cachehelper
    {
        private static readonly string _dbpath;
        private static readonly string _connectionstring;
        private static readonly messagepackserializeroptions _serializeroptions = contractlessstandardresolver.options;
        private static readonly int default_limit = cacheconstants.filter_limit;

        // 用于线程安全的锁管理器，确保每个 key 都有一个独立的锁对象
        private static readonly concurrentdictionary<string, object> _locks = new concurrentdictionary<string, object>();

        /// <summary>
        /// 静态构造函数，在类第一次被访问时自动运行一次，用于初始化数据库。
        /// </summary>
        static cachehelper()
        {
            // 将数据库文件放在应用程序的根目录下，确保路径一致性
            _dbpath = path.combine(appcontext.basedirectory, "cache.db");
            _connectionstring = $"data source={_dbpath}";
            initializedatabase();
        }

        /// <summary>
        /// 初始化数据库：如果数据库文件或表不存在，则创建它们。
        /// </summary>
        private static void initializedatabase()
        {
            try
            {
                using var connection = new sqliteconnection(_connectionstring);
                connection.open();

                // 创建一个可重用的 command 对象
                var command = connection.createcommand();

                // 1. 执行 pragma 指令以优化并发性能
                command.commandtext = "pragma journal_mode=wal;";
                command.executenonquery();

                // 2. 重用同一个 command 对象，执行 create table 指令
                command.commandtext = @"
                create table if not exists cachestore (
                    key text not null primary key,
                    value blob not null,
                    insertiontimeutc integer not null,
                    expirationtimeutc integer not null,
                    slidingexpirationminutes integer not null
                );";
                command.executenonquery();
            }
            catch (exception ex)
            {
                // 如果数据库初始化失败，这是个严重问题，需要记录下来
                // 在生产环境中，应使用专业的日志库（如 serilog, nlog）
                ($"[cache critical] database initialization failed. error: {ex.message}").logerr();
                // 抛出异常，因为如果数据库无法初始化，整个缓存服务都无法工作
                throw;
            }
        }

        #region public cache modification methods (add, clean)

        public static void addcachewithabsolute(string key, object value, int minute)
        {
            set(key, value, timespan.fromminutes(minute), issliding: false);
        }

        public static void addcachewithrelative(string key, object value, int minute)
        {
            set(key, value, timespan.fromminutes(minute), issliding: true);
        }

        public static void addcache(string key, object value)
        {
            // 对于永久缓存，我们设置一个极大的过期时间
            var longlivedtimespan = timespan.fromdays(30); // 30 days
            set(key, value, longlivedtimespan, issliding: false);
        }

        public static void cleancache(string key)
        {
            try
            {
                using var connection = new sqliteconnection(_connectionstring);
                connection.open();

                var command = connection.createcommand();
                command.commandtext = "delete from cachestore where key = $key;";
                command.parameters.addwithvalue("$key", key);
                command.executenonquery();
            }
            catch (exception ex)
            {
                ($"[cache error] failed to clean cache for key '{key}'. error: {ex.message}").logerr();
            }
        }

        #endregion

        #region public cache retrieval methods (get, getorquery)

        public static object? getcache(string key)
        {
            var (found, value) = trygetcache<object>(key);
            return found ? value : null;
        }

        public static t getcacheorquery<t>(string key, func<t> myfunc)
        {
            return getcacheorquery<t>(key, default_limit, myfunc);
        }

        public static t getcacheorquery<t>(string key, int minute, func<t> myfunc)
        {
            return getorset(key, minute, () => myfunc());
        }

        public static r getcacheorquery<p, r>(string key, func<p, r> myfunc, p param1)
        {
            return getcacheorquery<p, r>(key, default_limit, myfunc, param1);
        }

        public static r getcacheorquery<p, r>(string key, int minter, func<p, r> myfunc, p param1)
        {
            return getorset(key, minter, () => myfunc(param1));
        }

        // --- 为了简洁，这里省略了剩余的 getcacheorquery 重载 ---
        // --- 您可以按照下面的 `getorset` 模式轻松地实现它们 ---
        // 示例：
        public static r getcacheorquery<p1, p2, r>(string key, int minter, func<p1, p2, r> myfunc, p1 param1, p2 param2)
        {
            return getorset(key, minter, () => myfunc(param1, param2));
        }

        public static r getcacheorquery<p1, p2, r>(string key, func<p1, p2, r> myfunc, p1 param1, p2 param2)
        {
            return getcacheorquery(key, default_limit, myfunc, param1, param2);
        }

        // ... 请为其他所有重载方法应用相同的模式 ...

        #endregion

        #region core logic (private methods)

        /// <summary>
        /// 核心的 "get-or-set" 方法，实现了双重检查锁定以确保线程安全。
        /// </summary>
        private static t getorset<t>(string key, int minute, func<t> queryfunc)
        {
            // 第一次检查（在锁之外），这是为了在缓存命中的情况下获得最高性能
            var (found, value) = trygetcache<t>(key);
            if (found)
            {
                console.writeline($"get [{key}] cache, survival time is [{minute}] minutes");
                return value!;
            }

            // 获取或为当前 key 创建一个唯一的锁对象
            var lockobject = _locks.getoradd(key, k => new object());

            // 进入锁代码块，确保同一时间只有一个线程能为这个 key 生成缓存
            lock (lockobject)
            {
                // 第二次检查（在锁之内），防止在等待锁的过程中，其他线程已经生成了缓存
                (found, value) = trygetcache<t>(key);
                if (found)
                {
                    return value!;
                }

                // 执行昂贵的数据查询操作
                var result = queryfunc();

                // 将查询结果存入缓存
                if (result != null)
                {
                    set(key, result, timespan.fromminutes(minute), issliding: false);
                }
                console.writeline($"added [{key}] cache, survival time is [{minute}] minutes");
                return result;
            }
        }

        /// <summary>
        /// 统一的缓存写入方法。
        /// </summary>
        private static void set(string key, object value, timespan expiration, bool issliding)
        {
            if (value == null) return;

            try
            {
                var serializedvalue = messagepackserializer.serialize(value, _serializeroptions);
                var now = datetime.utcnow;

                using var connection = new sqliteconnection(_connectionstring);
                connection.open();

                var command = connection.createcommand();
                command.commandtext = @"
                    insert or replace into cachestore (key, value, insertiontimeutc, expirationtimeutc, slidingexpirationminutes)
                    values ($key, $value, $insertion, $expiration, $sliding);";

                command.parameters.addwithvalue("$key", key);
                command.parameters.addwithvalue("$value", serializedvalue);
                command.parameters.addwithvalue("$insertion", now.ticks);
                command.parameters.addwithvalue("$expiration", now.add(expiration).ticks);
                command.parameters.addwithvalue("$sliding", issliding ? expiration.totalminutes : 0);

                command.executenonquery();
            }
            catch (exception ex)
            {
                ($"[cache error] failed to set cache for key '{key}'. error: {ex.message}").logerr();
                // 吞掉异常，保证主程序继续运行
            }
        }

        /// <summary>
        /// 尝试从缓存中获取数据，并处理滑动过期的更新逻辑。
        /// </summary>
        private static (bool found, t? value) trygetcache<t>(string key)
        {
            try
            {
                using var connection = new sqliteconnection(_connectionstring);
                connection.open();

                var command = connection.createcommand();
                command.commandtext = @"
                    select value, slidingexpirationminutes from cachestore
                    where key = $key and expirationtimeutc > $now;";
                command.parameters.addwithvalue("$key", key);
                command.parameters.addwithvalue("$now", datetime.utcnow.ticks);

                using var reader = command.executereader();
                if (reader.read())
                {
                    var blob = reader.getfieldvalue<byte[]>(0);
                    var slidingminutes = reader.getint64(1);

                    // 如果是滑动过期项，则更新其过期时间
                    if (slidingminutes > 0)
                    {
                        try
                        {
                            var updatecmd = connection.createcommand();
                            updatecmd.commandtext = "update cachestore set expirationtimeutc = $newexpiration where key = $key;";
                            updatecmd.parameters.addwithvalue("$key", key);
                            updatecmd.parameters.addwithvalue("$newexpiration", datetime.utcnow.addminutes(slidingminutes).ticks);
                            updatecmd.executenonquery();
                        }
                        catch (exception updateex)
                        {
                            // 滑动过期更新失败不是致命错误，只记录警告
                            ($"[cache warning] failed to update sliding expiration for key '{key}'. error: {updateex.message}").logerr();
                        }
                    }

                    var deserializedvalue = messagepackserializer.deserialize<t>(blob, _serializeroptions);
                    return (true, deserializedvalue);
                }
            }
            catch (exception ex)
            {
                ($"[cache error] failed to get cache for key '{key}'. error: {ex.message}").logerr();
            }

            // 如果发生任何错误或未找到，都返回“未命中”
            return (false, default);
        }

        #endregion
    }
}