870 lines
28 KiB
Markdown
870 lines
28 KiB
Markdown
|
#unity/代码缓存
|
|||
|
|
|
|||
|
|
配合[[队列操作]]管理好，异步并发的启动顺序
|
|||
|
|
|
|||
|
|
## SQLiteManager
|
|||
|
|
|
|||
|
|
``` cs
|
|||
|
|
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.");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
|
|||
|
|
## 数据结构参考：
|
|||
|
|
|
|||
|
|
``` cs
|
|||
|
|
/// <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; }
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
## 调用参考：
|
|||
|
|
``` cs
|
|||
|
|
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("数据更新完成");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
```
|
|||
|
|
|