test/Assets/RankDemo/SQLite4Unity3d/RankService.cs

using System;
using Mono.Data.Sqlite;
using UnityEngine;
using System.Collections.Generic;
using System.IO;

namespace LocalRank
{
    public class RankService
    {
        private const string DatabaseName = "rank.db";
        private static string DbFullPath;
        static RankService()
        {
#if UNITY_EDITOR
            DbFullPath = Path.Combine(Application.streamingAssetsPath, DatabaseName);
#else
            var filepath = Path.Combine(Application.persistentDataPath, DatabaseName);
            if (!File.Exists(filepath))
            {
                Debug.Log("Database not in Persistent path, copying from StreamingAssets...");

#if UNITY_ANDROID
                var sourcePath = "jar:file://" + Application.dataPath + "!/assets/" + DatabaseName;
                var www = new WWW(sourcePath);
                while (!www.isDone) { }
                File.WriteAllBytes(filepath, www.bytes);

#elif UNITY_IOS
                var sourcePath = Path.Combine(Application.dataPath + "/Raw", DatabaseName);
                File.Copy(sourcePath, filepath);

#elif UNITY_STANDALONE_OSX
                var sourcePath = Path.Combine(Application.dataPath + "/Resources/Data/StreamingAssets", DatabaseName);
                File.Copy(sourcePath, filepath);

#else
                var sourcePath = Path.Combine(Application.streamingAssetsPath, DatabaseName);
                File.Copy(sourcePath, filepath);
#endif

                Debug.Log("Database copied to: " + filepath);
            }

            DbFullPath = filepath;
#endif
        }
        //private static string DbPath => $"URI=file:{Application.persistentDataPath}/rank.db";
        private static string DbPath => $"URI=file:{DbFullPath}";
        // 检查表是否存在
        public static bool IsTableExists(int gameId)
        {
            using var conn = new SqliteConnection(DbPath);
            conn.Open();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = $@"
                SELECT COUNT(*) 
                FROM sqlite_master 
                WHERE type = 'table' AND name = 'rank_game_{gameId}';
            ";
            return Convert.ToInt32(cmd.ExecuteScalar()) > 0;
        }

        // 创建表格（如果表不存在）
        public static void CreateTableIfNotExists(int gameId)
        {
            if (IsTableExists(gameId)) return; // 如果表已存在，直接返回

            using var conn = new SqliteConnection(DbPath);
            conn.Open();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = $@"
                CREATE TABLE IF NOT EXISTS rank_game_{gameId} (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    userId INTEGER NOT NULL,
                    nickname TEXT,
                    avatarUrl TEXT,
                    score INTEGER,
                    update_time DATETIME DEFAULT CURRENT_TIMESTAMP
                );
            ";
            cmd.ExecuteNonQuery();
        }

        // 清空表格
        public static void ClearTable(int gameId)
        {
            using var conn = new SqliteConnection(DbPath);
            conn.Open();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = $"DELETE FROM rank_game_{gameId};";
            cmd.ExecuteNonQuery();
        }

        // 上传分数（如果是新用户，插入；如果是高分，更新）
        public static void UploadScore(int gameId, int userId, string nickname, string avatarUrl, int score)
        {
            CreateTableIfNotExists(gameId);

            using var conn = new SqliteConnection(DbPath);
            conn.Open();

            using var cmd = conn.CreateCommand();

            // 查询旧分数
            cmd.CommandText = $"SELECT score FROM rank_game_{gameId} WHERE userId = @userId;";
            cmd.Parameters.AddWithValue("@userId", userId);
            var reader = cmd.ExecuteReader();
            int? oldScore = null;
            if (reader.Read()) oldScore = reader.GetInt32(0);
            reader.Close();

            if (oldScore == null)
            {
                // 新用户插入
                cmd.CommandText = $@"INSERT INTO rank_game_{gameId} (userId, nickname, avatarUrl, score) 
                                 VALUES (@userId, @nickname, @avatarUrl, @score);";
            }
            else if (score > oldScore)
            {
                // 更新高分
                cmd.CommandText = $@"UPDATE rank_game_{gameId} 
                                 SET score = @score, update_time = CURRENT_TIMESTAMP 
                                 WHERE userId = @userId;";
            }
            else
            {
                return; // 分数没提升
            }

            cmd.Parameters.AddWithValue("@nickname", nickname);
            cmd.Parameters.AddWithValue("@avatarUrl", avatarUrl);
            cmd.Parameters.AddWithValue("@score", score);
            cmd.ExecuteNonQuery();
        }


        public class RankEntry
        {
            public string Nickname;
            public string AvatarUrl;
            public int Score;
            public int Rank;
            public bool IsSelf;
        }

        public static (List<RankEntry> resultList, int userRank, int totalCount) GetRankList(int gameId, int userId)
        {
            var result = new List<RankEntry>();

            using var conn = new SqliteConnection(DbPath);
            conn.Open();

            using var cmd = conn.CreateCommand();
            cmd.CommandText = $"SELECT userId, nickname, avatarUrl, score, update_time FROM rank_game_{gameId} ORDER BY score DESC, update_time DESC;";
            using var reader = cmd.ExecuteReader();

            var allRanks = new List<RankEntry>();
            int currentRank = 1;
            int userIndex = -1;

            while (reader.Read())
            {
                var entry = new RankEntry
                {
                    Nickname = reader.GetString(1),
                    AvatarUrl = reader.GetString(2),
                    Score = reader.GetInt32(3),
                    Rank = currentRank++,
                    IsSelf = reader.GetInt32(0) == userId
                };

                if (entry.IsSelf)
                    userIndex = allRanks.Count;

                allRanks.Add(entry);
            }

            reader.Close();

            // 加入前三名
            for (int i = 0; i < Math.Min(3, allRanks.Count); i++)
            {
                result.Add(allRanks[i]);
            }

            // 加入中间的自己 + 周围 7 条数据
            if (userIndex == -1)
            {
                // 用户不存在
                result.Add(new RankEntry
                {
                    Rank = -1,
                    IsSelf = true,
                    Nickname = "None",
                    AvatarUrl = "",
                    Score = 0
                });
            }
            else
            {
                // 起始从第4条开始找
                int centerStart = Math.Max(3, userIndex - 3);
                int centerEnd = Math.Min(allRanks.Count, centerStart + 7);

                for (int i = centerStart; i < centerEnd; i++)
                {
                    result.Add(allRanks[i]);
                }
            }

            return (result, userIndex == -1 ? -1 : allRanks[userIndex].Rank, allRanks.Count);
        }

        /// <summary>
        /// 用设备唯一标识（如 SystemInfo.deviceUniqueIdentifier）做基础，然后加时间戳或随机数避免重复
        /// </summary>
        /// <returns></returns>
        public static int GenerateLocalUserId()
        {
            return Math.Abs($"{SystemInfo.deviceUniqueIdentifier}_{DateTime.UtcNow.Ticks}".GetHashCode());
        }
    }


}