28 KiB
28 KiB
#unity/代码缓存
配合队列操作管理好，异步并发的启动顺序
SQLiteManager
using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using UnityEngine;
using System.IO;
using System.Linq.Expressions;
using System.Threading;
using CGBU_SDK;
namespace SQLite4Unity3d
{
public class TaskQueue
{
private ConcurrentQueue<Func<Task>> _tasks = new ConcurrentQueue<Func<Task>>();
private SemaphoreSlim _semaphore = new SemaphoreSlim(1, 1);
// 用于无返回值的任务
public Task EnqueueTask(Func<Task> task)
{
var tcs = new TaskCompletionSource<object>();
_tasks.Enqueue(async () =>
{
try
{
await task();
tcs.SetResult(null);
}
catch (Exception ex)
{
tcs.SetException(ex);
}
});
TryExecuteNextTask();
return tcs.Task;
}
// 用于有返回值的任务
public Task<T> EnqueueTask<T>(Func<Task<T>> task)
{
var tcs = new TaskCompletionSource<T>();
_tasks.Enqueue(async () =>
{
try
{
T result = await task();
tcs.SetResult(result);
}
catch (Exception ex)
{
tcs.SetException(ex);
}
});
TryExecuteNextTask();
return tcs.Task;
}
private async void TryExecuteNextTask()
{
if (_semaphore.CurrentCount == 0)
return;
if (_tasks.TryDequeue(out var nextTask))
{
await _semaphore.WaitAsync();
try
{
await nextTask();
}
finally
{
_semaphore.Release();
TryExecuteNextTask();
}
}
}
}
public class SQLiteManager
{
private SQLiteConnection _db; //数据库连接
/// <summary>
/// 数据库 名称 ，数据库 多个表的类型
/// </summary>
/// <param name="dbName"></param>
/// <param name="tableTypes"></param>
public SQLiteManager(string dbName, params Type[] tableTypes)
{
CreateSQLiteDataBase($"{dbName}.db");
// 确保所有传入的表类型都存在
foreach (var type in tableTypes)
{
EnsureTableExists(type);
}
}
/// <summary>
/// 插入 特定表 单条数据
/// </summary>
/// <param name="data"></param>
/// <typeparam name="T"></typeparam>
public async Task InsertDataAsync<T>(T data) where T : new()
{
await Task.Run(() =>
{
_db.RunInTransaction(() =>
{
try
{
_db.Insert(data);
}
catch (Exception ex)
{
Debug.LogError($"Error inserting {typeof(T).Name}: {ex.Message}");
throw;
}
});
});
}
/// <summary>
/// 批量插入 特定表 数据
/// </summary>
/// <param name="dataList"></param>
/// <typeparam name="T"></typeparam>
public async Task InsertBatchDataAsync<T>(IEnumerable<T> dataList) where T : new()
{
await Task.Run(() =>
{
_db.RunInTransaction(() =>
{
try
{
_db.InsertAll(dataList);
}
catch (Exception ex)
{
Debug.LogError($"Error inserting batch data for {typeof(T).Name}: {ex.Message}");
throw;
}
});
});
}
/// <summary>
/// 获取 特定表中 所有数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public async Task<List<object>> GetAllDataAsync<T>() where T : new()
{
return await Task.Run(() =>
{
lock (_db) // 确保线程安全
{
try
{
// 获取数据并转换为 object 类型
return _db.Table<T>().ToList().Cast<object>().ToList();
}
catch (Exception ex)
{
Debug.LogError($"Error fetching data from {typeof(T).Name}: {ex.Message}");
return new List<object>(); // 返回空列表以避免错误
}
}
});
}
/// <summary>
/// 删除 特定表中 所有数据 （保留表结构）
/// </summary>
/// <typeparam name="T"></typeparam>
public async Task DeleteAllDataAsync<T>() where T : new()
{
await Task.Run(() =>
{
lock (_db) // 线程安全
{
_db.RunInTransaction(() =>
{
try
{
string tableName = typeof(T).Name;
_db.Execute($"DELETE FROM {tableName}");
}
catch (Exception ex)
{
Debug.LogError($"Error deleting all records from {typeof(T).Name}: {ex.Message}");
throw;
}
});
}
});
}
/// <summary>
/// 获得表里 最新的数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public async Task<T> GetMostRecentDataAsync<T>() where T : new()
{
return await Task.Run(() =>
{
lock (_db) // Ensures thread safety
{
try
{
// Query to get the most recent row based on the 'id' field.
string query = $"SELECT * FROM {typeof(T).Name} ORDER BY id DESC LIMIT 1";
var result = _db.Query<T>(query).FirstOrDefault();
return result;
}
catch (Exception ex)
{
Debug.LogError($"Error fetching the most recent data from {typeof(T).Name}: {ex.Message}");
return default(T); // Return default object if error occurs
}
}
});
}
/// <summary>
/// 根据uuid获取数据库中 特定表数据
/// </summary>
/// <param name="uuid"></param>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public async Task<T> GetDataByIdAsync<T>(string uuid) where T : new()
{
return await Task.Run(() =>
{
try
{
return _db.Table<T>().FirstOrDefault(e => (string)e.GetType().GetProperty("uuid").GetValue(e) == uuid);
}
catch (Exception ex)
{
Debug.LogError($"Error retrieving data from {typeof(T).Name}: {ex.Message}");
return default; // 返回 null（适用于引用类型）
}
});
}
public async Task UpdateDataByUuidAsync<T>(string uuid, T newData) where T : new()
{
await Task.Run(() =>
{
try
{
// Check if the entry exists
var existingData = _db.Table<T>().FirstOrDefault(e => (string)e.GetType().GetProperty("uuid").GetValue(e) == uuid);
if (existingData != null)
{
// **Update the fields of existing data with new data**
foreach (var prop in typeof(T).GetProperties())
{
// Skipping the uuid property to avoid overwriting it
if (prop.Name != "uuid")
{
var newValue = prop.GetValue(newData);
prop.SetValue(existingData, newValue);
}
}
// **Update the record in the database**
_db.Update(existingData); // Updates the record
}
else
{
Debug.LogWarning($"No record found with uuid: {uuid}");
}
}
catch (Exception ex)
{
Debug.LogError($"Error updating data in {typeof(T).Name}: {ex.Message}");
}
});
}
/// <summary>
/// 传参uuids列表，不在uuids列表中的数据库数据，移除
/// </summary>
/// <param name="uuids"></param>
/// <param name="columnName"></param>
/// <typeparam name="T"></typeparam>
public async Task DeleteExceptUuidsAsync<T>(List<string> uuids, string columnName = "uuid") where T : new()
{
if (uuids == null || uuids.Count == 0)
{
Debug.LogWarning("DeleteExceptUuidsAsync: UUID list is empty. No data will be deleted.");
return;
}
await Task.Run(() =>
{
lock (_db) // 线程安全
{
_db.RunInTransaction(() =>
{
try
{
// 生成SQL：删除所有不在 UUID 列表中的数据
string placeholders = string.Join(",", uuids.Select(_ => "?"));
string sql = $"DELETE FROM {typeof(T).Name} WHERE {columnName} NOT IN ({placeholders})";
_db.Execute(sql, uuids.ToArray());
}
catch (Exception ex)
{
Debug.LogError($"Error deleting records not in the UUID list from {typeof(T).Name}: {ex.Message}");
throw;
}
});
}
});
}
/// <summary>
/// 批量更新 多个表 数据
/// </summary>
/// <param name="tableDataMap"></param>
/// <param name="columnName"></param>
/// <exception cref="ArgumentException"></exception>
public async Task UpsertDataAsync(Dictionary<Type, List<object>> tableDataMap, string columnName = "uuid")
{
if (tableDataMap == null || tableDataMap.Count == 0)
{
Debug.LogWarning("UpsertDataAsync: No data provided for any table. No operation performed.");
return;
}
await Task.Run(() =>
{
lock (_db) // 线程安全
{
_db.RunInTransaction(() =>
{
try
{
foreach (var entry in tableDataMap)
{
Type tableType = entry.Key;
List<object> dataList = entry.Value;
if (dataList == null || dataList.Count == 0)
{
Debug.LogWarning($"UpsertDataAsync: No data provided for {tableType.Name}. Skipping...");
try
{
// Clear the table by executing a DELETE statement
string clearTable = $"DELETE FROM {tableType.Name}";
_db.Execute(clearTable);
}
catch (Exception ex)
{
Debug.LogError($"Error clearing data in {tableType.Name}: {ex.Message}");
}
continue;
}
var propertyInfo = tableType.GetProperty(columnName);
if (propertyInfo == null)
{
throw new ArgumentException($"Column '{columnName}' not found in {tableType.Name}.");
}
// **1️⃣ 获取数据库已有的 UUIDs**
string query = $"SELECT {columnName} FROM {tableType.Name}";
var tableMapping = _db.GetMapping(tableType);
var existingUuids = new HashSet<string>(
_db.Query(tableMapping, query)
.Select(d => propertyInfo.GetValue(d)?.ToString())
.Where(uuid => !string.IsNullOrEmpty(uuid))
);
// **2️⃣ 删除数据库中不在 `incomingUuids` 里的数据**
var incomingUuids = new HashSet<string>(
dataList.Select(d => propertyInfo.GetValue(d)?.ToString())
.Where(uuid => !string.IsNullOrEmpty(uuid))
);
if (incomingUuids.Count == 0)
{
Debug.LogWarning($"UpsertDataAsync: No valid UUIDs found in the data list for {tableType.Name}.");
continue;
}
string placeholders = string.Join(",", incomingUuids.Select(_ => "?"));
string deleteSql = $"DELETE FROM {tableType.Name} WHERE {columnName} NOT IN ({placeholders})";
_db.Execute(deleteSql, incomingUuids.ToArray());
// **3️⃣ 计算需要更新和插入的数据**
var toUpdateList = dataList.Where(d =>
{
var uuid = propertyInfo.GetValue(d)?.ToString();
return !string.IsNullOrEmpty(uuid) && existingUuids.Contains(uuid);
}).ToList();
var toInsertList = dataList.Where(d =>
{
var uuid = propertyInfo.GetValue(d)?.ToString();
return !string.IsNullOrEmpty(uuid) && !existingUuids.Contains(uuid);
}).ToList();
// **4️⃣ 更新已有数据**
foreach (var item in toUpdateList)
{
_db.Update(item);
}
// **5️⃣ 插入新数据**
if (toInsertList.Count > 0)
{
_db.InsertAll(toInsertList);
}
}
}
catch (Exception ex)
{
Debug.LogError($"Error in UpsertDataAsync: {ex.Message}");
throw;
}
});
}
});
}
public void UpsertData(Dictionary<Type, List<object>> tableDataMap, string columnName = "uuid")
{
if (tableDataMap == null || tableDataMap.Count == 0)
{
Debug.LogWarning("UpsertData: No data provided for any table. No operation performed.");
return;
}
lock (_db) // 线程安全
{
_db.RunInTransaction(() =>
{
try
{
foreach (var entry in tableDataMap)
{
Type tableType = entry.Key;
List<object> dataList = entry.Value;
if (dataList == null || dataList.Count == 0)
{
Debug.LogWarning($"UpsertData: No data provided for {tableType.Name}. Skipping...");
try
{
// Clear the table by executing a DELETE statement
string clearTable = $"DELETE FROM {tableType.Name}";
_db.Execute(clearTable);
}
catch (Exception ex)
{
Debug.LogError($"Error clearing data in {tableType.Name}: {ex.Message}");
}
continue;
}
var propertyInfo = tableType.GetProperty(columnName);
if (propertyInfo == null)
{
throw new ArgumentException($"Column '{columnName}' not found in {tableType.Name}.");
}
// 获取数据库已有的 UUIDs
string query = $"SELECT {columnName} FROM {tableType.Name}";
var tableMapping = _db.GetMapping(tableType);
var existingUuids = new HashSet<string>(
_db.Query(tableMapping, query)
.Select(d => propertyInfo.GetValue(d)?.ToString())
.Where(uuid => !string.IsNullOrEmpty(uuid))
);
// 删除数据库中不在 `incomingUuids` 里的数据
var incomingUuids = new HashSet<string>(
dataList.Select(d => propertyInfo.GetValue(d)?.ToString())
.Where(uuid => !string.IsNullOrEmpty(uuid))
);
if (incomingUuids.Count == 0)
{
Debug.LogWarning($"UpsertData: No valid UUIDs found in the data list for {tableType.Name}.");
continue;
}
string placeholders = string.Join(",", incomingUuids.Select(_ => "?"));
string deleteSql = $"DELETE FROM {tableType.Name} WHERE {columnName} NOT IN ({placeholders})";
_db.Execute(deleteSql, incomingUuids.ToArray());
// 计算需要更新和插入的数据
var toUpdateList = dataList.Where(d =>
{
var uuid = propertyInfo.GetValue(d)?.ToString();
return !string.IsNullOrEmpty(uuid) && existingUuids.Contains(uuid);
}).ToList();
var toInsertList = dataList.Where(d =>
{
var uuid = propertyInfo.GetValue(d)?.ToString();
return !string.IsNullOrEmpty(uuid) && !existingUuids.Contains(uuid);
}).ToList();
// 更新已有数据
foreach (var item in toUpdateList)
{
_db.Update(item);
}
// 插入新数据
if (toInsertList.Count > 0)
{
_db.InsertAll(toInsertList);
}
}
}
catch (Exception ex)
{
Debug.LogError($"Error in UpsertData: {ex.Message}");
throw;
}
});
}
}
/// <summary>
/// 清楚替换，单个表结构
/// </summary>
/// <param name="newDataList"></param>
/// <typeparam name="T"></typeparam>
public async Task BulkReplaceDataAsync<T>(List<T> newDataList) where T : new()
{
if (newDataList == null || newDataList.Count == 0)
{
Debug.LogWarning($"BulkReplaceDataAsync: No data provided for table {typeof(T).Name}. Operation aborted.");
return;
}
await Task.Run(() =>
{
lock (_db) // 确保线程安全
{
_db.RunInTransaction(() =>
{
try
{
_db.DeleteAll<T>(); // 清空表数据
_db.InsertAll(newDataList); // 批量插入新的数据
Debug.Log($"BulkReplaceDataAsync: Successfully replaced data in {typeof(T).Name}.");
}
catch (Exception ex)
{
Debug.LogError($"BulkReplaceDataAsync: Error processing table {typeof(T).Name}: {ex.Message}");
}
});
}
});
}
public async Task BulkReplaceMultipleTablesAsync(params (Type TableType, IList Data)[] tablesData)
{
if (tablesData == null || tablesData.Length == 0)
{
Debug.LogWarning("BulkReplaceMultipleTablesAsync: No data provided, no operation performed.");
return;
}
await Task.Run(() =>
{
lock (_db) // 确保数据库线程安全
{
_db.RunInTransaction(() =>
{
try
{
foreach (var (tableType, data) in tablesData)
{
if (data == null || data.Count == 0) continue;
// 清空表数据
string tableName = tableType.Name;
_db.Execute($"DELETE FROM {tableName}");
// 批量插入数据
_db.InsertAll(data);
Debug.Log($"BulkReplaceMultipleTablesAsync: Replaced data for {tableName}");
}
}
catch (Exception ex)
{
Debug.LogError($"BulkReplaceMultipleTablesAsync: Error processing tables: {ex.Message}");
throw;
}
});
}
});
}
/// <summary>
/// 获取 | 创建 SQLite数据库文件
/// </summary>
/// <param name="DatabaseName"></param>
private void CreateSQLiteDataBase(string DatabaseName)
{
#if UNITY_EDITOR
var dbPath = string.Format(@"Assets/StreamingAssets/{0}", DatabaseName);
#else
// check if file exists in Application.persistentDataPath
var filepath = string.Format("{0}/{1}", Application.persistentDataPath, DatabaseName);
if (!File.Exists(filepath))
{
Debug.Log("Database not in Persistent path");
// if it doesn't ->
// open StreamingAssets directory and load the db ->
#if UNITY_ANDROID
var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + DatabaseName); // this is the path to your StreamingAssets in android
while (!loadDb.isDone) { } // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check
// then save to Application.persistentDataPath
File.WriteAllBytes(filepath, loadDb.bytes);
#elif UNITY_IOS
var loadDb = Application.dataPath + "/Raw/" + DatabaseName; // this is the path to your StreamingAssets in iOS
// then save to Application.persistentDataPath
File.Copy(loadDb, filepath);
#elif UNITY_WP8
var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName; // this is the path to your StreamingAssets in iOS
// then save to Application.persistentDataPath
File.Copy(loadDb, filepath);
#elif UNITY_WINRT
var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName; // this is the path to your StreamingAssets in iOS
// then save to Application.persistentDataPath
File.Copy(loadDb, filepath);
#elif UNITY_STANDALONE_OSX
var loadDb = Application.dataPath + "/Resources/Data/StreamingAssets/" + DatabaseName; // this is the path to your StreamingAssets in iOS
// then save to Application.persistentDataPath
File.Copy(loadDb, filepath);
#else
var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName; // this is the path to your StreamingAssets in iOS
// then save to Application.persistentDataPath
File.Copy(loadDb, filepath);
#endif
Debug.Log("Database written");
}
var dbPath = filepath;
#endif
_db = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
_db.Execute("PRAGMA synchronous=NORMAL;");
// string sqliteVersion = _db.ExecuteScalar<string>("SELECT sqlite_version();");
// Debug.Log($"SQLite 版本: {sqliteVersion}");
// _db.Execute("PRAGMA journal_mode=WAL;"); // 启用 WAL 模式
// string mode = _db.ExecuteScalar<string>("PRAGMA journal_mode=WAL;");
// Debug.Log($"WAL 模式启用: {mode}");
//
// if (mode != "wal")
// {
// Debug.LogError("WAL 模式未成功启用，请检查数据库权限或文件占用情况！");
// }
// _db.DropTable<SQLiteSessionEventData> ();
// _db.CreateTable<SQLiteSessionEventData> ();
// Debug.Log("数据库文件 所在路径：" + dbPath);
}
/// <summary>
/// 判断数据库中 表是否存在
/// </summary>
/// <typeparam name="T"></typeparam>
public void EnsureTableExists(Type tableType)
{
string tableName = tableType.Name;
// 查询 sqlite_master 来检查表是否存在
var tableExists = _db.ExecuteScalar<int>($"SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='{tableName}';");
if (tableExists == 0) // 如果表不存在
{
_db.CreateTable(tableType); // 创建表
Debug.Log($"Table {tableName} created.");
}
else
{
Debug.Log($"Table {tableName} already exists, skipping creation.");
}
}
}
}
数据结构参考：
/// <summary>
/// 开始&结束会话
/// </summary>
[Serializable]
public class SessionEventData
{
public string uuid;
public string event_name;
public long event_timestamp;
public string public_properties;
public Dictionary<string, object> event_properties;
public int snum; //发送计数器
}
/// <summary>
/// 普通会话
/// </summary>
[Serializable]
public class EventData
{
public string uuid;
public string event_name;
public long event_timestamp;
public string public_properties;
public Dictionary<string, object> event_properties;
public int snum; //发送计数器
}
/// <summary>
/// 事件类型
/// </summary>
public enum SessionType
{
start_end_session, //开始/结束会话
default_session //普通会话
}
/// <summary>
/// 开始&结束会话 SQLite存储的消息结构
/// </summary>
public class SQLiteSessionEventData
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string uuid{ get; set; }
public string event_name { get; set; }
public long event_timestamp { get; set; }
public string public_properties { get; set; }
public string event_properties { get; set; }
public int snum { get; set; } // 发送计数器
}
/// <summary>
/// 普通会话 SQLite存储的消息结构
/// </summary>
public class SQLiteEventData
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string uuid{ get; set; }
public string event_name { get; set; }
public long event_timestamp { get; set; }
public string public_properties { get; set; }
public string event_properties { get; set; }
public int snum { get; set; } // 发送计数器
}
/// <summary>
/// 临时ID SQLite存储结构
/// </summary>
public class SQLiteTempIdData
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string TempId { get; set; }
public string uuid { get; set; }
public string RealId { get; set; }
}
调用参考：
public async void UpdateSQLiteCache()
{
if (TimeTickUtil.GetEventTimesTamp() - LastChangeDataTime >= ChangeDataTime && isInitData)
{
LastChangeDataTime = TimeTickUtil.GetEventTimesTamp();
List<object> SQLiteSessionEventDataList = new List<object>(); //数据库中 SessionEventData 表数据 (开始&结束会话)
List<object> SQLiteEventDataList = new List<object>(); //数据库中 EventData 表数据 (普通事件)
foreach (KeyValuePair<string,object> kvp in SessionDic[SessionType.start_end_session])
{
SessionEventData sessionEventData = kvp.Value as SessionEventData;
string sessionEventJosn = JsonConvert.SerializeObject(sessionEventData.event_properties);
var insertSessionData = new SQLiteSessionEventData
{
uuid = sessionEventData.uuid,
event_name = sessionEventData.event_name,
event_timestamp = sessionEventData.event_timestamp,
public_properties = sessionEventData.public_properties,
event_properties = sessionEventJosn,
snum = sessionEventData.snum
};
SQLiteSessionEventDataList.Add(insertSessionData);
}
foreach (KeyValuePair<string,object> kvp in SessionDic[SessionType.default_session])
{
EventData eventData = kvp.Value as EventData;
string sessionEventJosn = JsonConvert.SerializeObject(eventData.event_properties);
var insertSessionData = new SQLiteEventData
{
uuid = eventData.uuid,
event_name = eventData.event_name,
event_timestamp = eventData.event_timestamp,
public_properties = eventData.public_properties,
event_properties = sessionEventJosn,
snum = eventData.snum
};
SQLiteEventDataList.Add(insertSessionData);
}
await taskQueue.EnqueueTask(() => SQLiteManager.UpsertDataAsync(new Dictionary<Type, List<object>>
{
{ typeof(SQLiteSessionEventData), SQLiteSessionEventDataList },
{ typeof(SQLiteEventData), SQLiteEventDataList },
}));
Debug.Log("数据更新完成");
}
}