一、数据库文件更新原理
1、通过设置首选项 Preferences,在客户端以KV的形式,存储数据库文件版本
// 设置数据库版本
Preferences.Set("DBVersion",1);
// 读取数据库版本,失败返回0
Preferences.Get("DBVersion",0);
2、通过数据库版本号比对,执行相应更新逻辑
3、KV说明
键 | 值 | 说明 |
---|---|---|
DBVersion | 0 | 未初始化 |
1 | 版本1 | |
2 | 版本2 | |
n | 版本n |
二、添加数据库资源文件
1、添加资源文件
项目,MauiApp2.Library属性,资源,常规,创建或打开程序集资源,添加资源,添加现有文件,poetrydb.sqlite
2、嵌入的资源
选中资源文件,按F4打开属性,生成操作,改为 嵌入的资源
3、修改资源名称
<ItemGroup>
<EmbeddedResource Include="Resources\poetrydb.sqlite" >
<LogicalName>poetrydb.sqlite</LogicalName>
</EmbeddedResource>
</ItemGroup>
三、添加Services
1、创建接口
IPoetryStorage.cs
using MauiApp2.Library.Models;
using System.Linq.Expressions;
namespace MauiApp2.Library.Services;
public interface IPoetryStorage
{
/// <summary>
/// 判断数据库是否已初始化
/// </summary>
bool IsInitialized { get; }
/// <summary>
/// 初始化或更新数据库版本
/// </summary>
/// <returns></returns>
Task InitializeAsync();
/// <summary>
/// 获取一条记录
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
Task<Poetry> GetPoetryAsync(int id);
/// <summary>
/// 获取一些记录
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
Task<IEnumerable<Poetry>> GetPoetriesAsync(Expression<Func<Poetry, bool>> where, int skip, int take);
}
IPreferenceStorage.cs
namespace MauiApp2.Library.Services;
/// <summary>
/// 键值对存储接口
/// </summary>
public interface IPreferenceStorage
{
/// <summary>
/// 设置版本号
/// </summary>
/// <param name="key"></param>
/// <param name="value"></param>
void Set(string key, int value);
/// <summary>
/// 获取版本号
/// </summary>
/// <param name="key"></param>
/// <param name="defaultValue"></param>
/// <returns></returns>
int Get(string key, int defaultValue);
}
2、实现接口
PoetryStorage.cs
using System.Linq.Expressions;
using MauiApp2.Library.Models;
using SQLite;
namespace MauiApp2.Library.Services;
public class PoetryStorage : IPoetryStorage
{
/// <summary>
/// 数据库文件存储位置
/// </summary>
public const string DbName = "poetrydb.sqlite";
public static readonly string PoetryDbPath =
Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), DbName);
/// <summary>
/// 创建数据库连接
/// </summary>
private SQLiteAsyncConnection _connection;
private SQLiteAsyncConnection Connection => _connection ??= new SQLiteAsyncConnection(PoetryDbPath);
/// <summary>
/// 键值对存储接口实例
/// </summary>
private readonly IPreferenceStorage _preferenceStorage;
/// <summary>
/// 通过构造函数注入依赖
/// </summary>
/// <param name="preferenceStorage"></param>
public PoetryStorage(IPreferenceStorage preferenceStorage)
{
_preferenceStorage = preferenceStorage;
}
/// <summary>
/// 判断用户数据库版本
/// </summary>
public bool IsInitialized => _preferenceStorage.Get(PoetryStorageConstant.DbVersionKey, 0) == PoetryStorageConstant.Version;
/// <summary>
/// 初始化或更新数据库版本
/// </summary>
/// <returns></returns>
/// <exception cref="NotImplementedException"></exception>
public async Task InitializeAsync()
{
// 打开写入的文件
await using var dbFileStream = new FileStream(PoetryDbPath, FileMode.OpenOrCreate);
// 打开读取的文件
await using var dbAssetStream = typeof(PoetryStorage).Assembly.GetManifestResourceStream(DbName);
// 复制并写入文件
await dbAssetStream.CopyToAsync(dbFileStream);
// 关闭读取的文件
// 关闭写入的文件
// 写入新的版本号
_preferenceStorage.Set(PoetryStorageConstant.DbVersionKey, PoetryStorageConstant.Version);
}
/// <summary>
/// 获取一条记录
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Task<Poetry> GetPoetryAsync(int id) =>
Connection.Table<Poetry>().FirstOrDefaultAsync(p => p.Id == id);
/// <summary>
/// 获取一些记录
/// </summary>
/// <param name="where"></param>
/// <param name="skip"></param>
/// <param name="take"></param>
/// <returns></returns>
public async Task<IEnumerable<Poetry>> GetPoetriesAsync(Expression<Func<Poetry, bool>> where, int skip, int take) =>
await Connection.Table<Poetry>().Where(where).Skip(skip).Take(take).ToListAsync();
/// <summary>
/// 关闭数据库连接
/// </summary>
/// <returns></returns>
public async Task CloseAsync() => await Connection.CloseAsync();
}
/// <summary>
/// 通过nameof定义常量
/// </summary>
public static class PoetryStorageConstant
{
public const string DbVersionKey = nameof(PoetryStorageConstant) + "." + nameof(DbVersionKey);
// 当前数据库版本
public const int Version = 1;
}
四、单元测试
1、创建xUnit测试项目
2、打开测试资源管理器
试运行,正常
3、添加目标项目依赖
4、NuGet添加Moq [Mock]
5、按目录结构添加测试类
PoetryStorageTest.cs
using MauiApp2.Library.Models;
using MauiApp2.Library.Services;
using MauiApp2.UnitTest.Helpers;
using Moq;
using System.Linq.Expressions;
namespace MauiApp2.UnitTest.Services;
public class PoetryStorageTest : IDisposable
{
/// <summary>
/// 单元测试前自动执行
/// </summary>
public PoetryStorageTest() => PoetryStorageHelper.RemoveDatabaseFile();
/// <summary>
/// 单元测试后自动执行
/// </summary>
public void Dispose() => PoetryStorageHelper.RemoveDatabaseFile();
/// <summary>
/// 数据库未初始化测试
/// </summary>
[Fact]
public void IsInitialized_NotInitialized()
{
var preferenceStorageMock = new Mock<IPreferenceStorage>();
preferenceStorageMock.Setup(p => p.Get(PoetryStorageConstant.DbVersionKey, 0))
.Returns(0);
var mockPreferenceStorage = preferenceStorageMock.Object;
var poetryStorage = new PoetryStorage(mockPreferenceStorage);
Assert.False(poetryStorage.IsInitialized);
}
/// <summary>
/// 数据库已初始化测试
/// </summary>
[Fact]
public void IsInitialized_Initialized()
{
var preferenceStorageMock = new Mock<IPreferenceStorage>();
preferenceStorageMock.Setup(p => p.Get(PoetryStorageConstant.DbVersionKey, 0))
.Returns(PoetryStorageConstant.Version);
var mockPreferenceStorage = preferenceStorageMock.Object;
var poetryStorage = new PoetryStorage(mockPreferenceStorage);
Assert.True(poetryStorage.IsInitialized);
}
/// <summary>
/// 数据库初始化或更新测试
/// </summary>
/// <returns></returns>
[Fact]
public async Task InitializeAsync_Default()
{
var preferenceStorageMock = new Mock<IPreferenceStorage>();
var mockPreferenceStorage = preferenceStorageMock.Object;
var poetryStorage = new PoetryStorage(mockPreferenceStorage);
Assert.False(File.Exists(PoetryStorage.PoetryDbPath));
await poetryStorage.InitializeAsync();
Assert.True(File.Exists(PoetryStorage.PoetryDbPath));
// 验证代码是否被调用一次
preferenceStorageMock.Verify(p => p.Set(PoetryStorageConstant.DbVersionKey, PoetryStorageConstant.Version), Times.Once);
}
/// <summary>
/// 获取一条记录测试
/// </summary>
/// <returns></returns>
[Fact]
public async Task GetPoetryAsync_Default()
{
var poetryStorage = await PoetryStorageHelper.GetInitializedPoetryStorage();
var poetry = await poetryStorage.GetPoetryAsync(10001);
Assert.Equal("临江仙 · 夜归临皋", poetry.Name);
await poetryStorage.CloseAsync();
}
/// <summary>
/// 获取一些记录记录
/// </summary>
/// <returns></returns>
[Fact]
public async Task GetPoetriesAsync_Default()
{
// p => p.Id == id
// p => true
var poetryStorage = await PoetryStorageHelper.GetInitializedPoetryStorage();
var poetries = await poetryStorage.GetPoetriesAsync(Expression.Lambda<Func<Poetry, bool>>(Expression.Constant(true), Expression.Parameter(typeof(Poetry), "p")), skip: 0, take: 30);
Assert.Equal(30, poetries.Count());
await poetryStorage.CloseAsync();
}
}
五、实现更新逻辑
1、添加项目引用
2、添加实现类
PreferenceStorage.cs
using MauiApp2.Library.Services;
namespace MauiApp2.Services;
internal class PreferenceStorage : IPreferenceStorage
{
public void Set(string key, int value) => Preferences.Set(key, value);
public int Get(string key, int defaultValue) => Preferences.Get(key, defaultValue);
}
3、单元测试
PoetryStorageTest.cs
using MauiApp2.Library.Services;
using Moq;
namespace MauiApp2.UnitTest.Services;
public class PoetryStorageTest
{
/// <summary>
/// 数据库未初始化测试
/// </summary>
[Fact]
public void IsInitialized_NotInitialized()
{
var preferenceStorageMock = new Mock<IPreferenceStorage>();
preferenceStorageMock.Setup(p => p.Get(PoetryStorageConstant.DbVersionKey, 0))
.Returns(0);
var mockPreferenceStorage = preferenceStorageMock.Object;
var poetryStorage = new PoetryStorage(mockPreferenceStorage);
Assert.False(poetryStorage.IsInitialized);
}
/// <summary>
/// 数据库已初始化测试
/// </summary>
[Fact]
public void IsInitialized_Initialized()
{
var preferenceStorageMock = new Mock<IPreferenceStorage>();
preferenceStorageMock.Setup(p => p.Get(PoetryStorageConstant.DbVersionKey, 0))
.Returns(PoetryStorageConstant.Version);
var mockPreferenceStorage = preferenceStorageMock.Object;
var poetryStorage = new PoetryStorage(mockPreferenceStorage);
Assert.True(poetryStorage.IsInitialized);
}
/// <summary>
/// 数据库初始化或更新测试
/// </summary>
/// <returns></returns>
[Fact]
public async Task InitializeAsync_Default()
{
var mockPreferenceStorage = new Mock<IPreferenceStorage>().Object;
var poetryStorage = new PoetryStorage(mockPreferenceStorage);
Assert.False(File.Exists(PoetryStorage.PoetryDbPath));
await poetryStorage.InitializeAsync();
Assert.True(File.Exists(PoetryStorage.PoetryDbPath));
}
}
六、清理单元测试文件
1、创建Helper类
PoetryStorageHelper.cs
using MauiApp2.Library.Services;
using Moq;
namespace MauiApp2.UnitTest.Helpers;
public class PoetryStorageHelper
{
/// <summary>
/// 删除数据库文件
/// </summary>
public static void RemoveDatabaseFile() => File.Delete(PoetryStorage.PoetryDbPath);
/// <summary>
/// 初始化数据库
/// </summary>
/// <returns></returns>
public static async Task<PoetryStorage> GetInitializedPoetryStorage()
{
var preferenceStorageMock = new Mock<IPreferenceStorage>();
preferenceStorageMock.Setup(p => p.Get(PoetryStorageConstant.DbVersionKey, -1)).Returns(-1);
var mockPreferenceStorage = preferenceStorageMock.Object;
var poetryStorage = new PoetryStorage(mockPreferenceStorage);
await poetryStorage.InitializeAsync();
return poetryStorage;
}
}
2、自动执行清理
PoetryStorageTest.cs
using MauiApp2.Library.Models;
using MauiApp2.Library.Services;
using MauiApp2.UnitTest.Helpers;
using Moq;
using System.Linq.Expressions;
namespace MauiApp2.UnitTest.Services;
public class PoetryStorageTest : IDisposable
{
/// <summary>
/// 单元测试前自动执行
/// </summary>
public PoetryStorageTest() => PoetryStorageHelper.RemoveDatabaseFile();
/// <summary>
/// 单元测试后自动执行
/// </summary>
public void Dispose() => PoetryStorageHelper.RemoveDatabaseFile();
/// <summary>
/// 数据库未初始化测试
/// </summary>
[Fact]
public void IsInitialized_NotInitialized()
{
var preferenceStorageMock = new Mock<IPreferenceStorage>();
preferenceStorageMock.Setup(p => p.Get(PoetryStorageConstant.DbVersionKey, 0))
.Returns(0);
var mockPreferenceStorage = preferenceStorageMock.Object;
var poetryStorage = new PoetryStorage(mockPreferenceStorage);
Assert.False(poetryStorage.IsInitialized);
}
/// <summary>
/// 数据库已初始化测试
/// </summary>
[Fact]
public void IsInitialized_Initialized()
{
var preferenceStorageMock = new Mock<IPreferenceStorage>();
preferenceStorageMock.Setup(p => p.Get(PoetryStorageConstant.DbVersionKey, 0))
.Returns(PoetryStorageConstant.Version);
var mockPreferenceStorage = preferenceStorageMock.Object;
var poetryStorage = new PoetryStorage(mockPreferenceStorage);
Assert.True(poetryStorage.IsInitialized);
}
/// <summary>
/// 数据库初始化或更新测试
/// </summary>
/// <returns></returns>
[Fact]
public async Task InitializeAsync_Default()
{
var preferenceStorageMock = new Mock<IPreferenceStorage>();
var mockPreferenceStorage = preferenceStorageMock.Object;
var poetryStorage = new PoetryStorage(mockPreferenceStorage);
Assert.False(File.Exists(PoetryStorage.PoetryDbPath));
await poetryStorage.InitializeAsync();
Assert.True(File.Exists(PoetryStorage.PoetryDbPath));
// 验证代码是否被调用一次
preferenceStorageMock.Verify(p => p.Set(PoetryStorageConstant.DbVersionKey, PoetryStorageConstant.Version), Times.Once);
}
/// <summary>
/// 获取一条记录测试
/// </summary>
/// <returns></returns>
[Fact]
public async Task GetPoetryAsync_Default()
{
var poetryStorage = await PoetryStorageHelper.GetInitializedPoetryStorage();
var poetry = await poetryStorage.GetPoetryAsync(10001);
Assert.Equal("临江仙 · 夜归临皋", poetry.Name);
await poetryStorage.CloseAsync();
}
/// <summary>
/// 获取一些记录记录
/// </summary>
/// <returns></returns>
[Fact]
public async Task GetPoetriesAsync_Default()
{
// p => p.Id == id
// p => true
var poetryStorage = await PoetryStorageHelper.GetInitializedPoetryStorage();
var poetries = await poetryStorage.GetPoetriesAsync(Expression.Lambda<Func<Poetry, bool>>(Expression.Constant(true), Expression.Parameter(typeof(Poetry), "p")), skip: 0, take: 30);
Assert.Equal(30, poetries.Count());
await poetryStorage.CloseAsync();
}
}