ABP VNext + SQL Server Temporal Tables：审计与时序数据管理-EW帮帮网

ABP VNext + SQL Server Temporal Tables：审计与时序数据管理 🚀

📚 目录

ABP VNext + SQL Server Temporal Tables：审计与时序数据管理 🚀

📝 一、引言

TL;DR

✨ SQL Server 原生时序表 + EF Core 一行 Fluent API，自动记录实体历史
🔒 零侵入：无需触发器/手写审计表；F12 级调试可见版本化
🕰️ “时间旅行”查询 + 全量历史枚举，支持分页 & 流式返回
🛠️ 支持 SQL Server 2017+ 保留策略、Standard 版行/页压缩、分区滑动窗口清理等高级优化 (Microsoft Learn)

背景与动机

传统审计：分散触发器 & 手写历史表，易漏失、难维护
SQL Server 2016+ 系统版本化表（System-Versioned Temporal Tables）内置行级版本管理
EF Core 6.0+ + ABP VNext，几行配置即可贯通 DB→ORM→API 全链路 (Microsoft for Developers)

🔧 二、环境与依赖

.NET：6.x
ABP：VNext 6.x
EF Core：Microsoft.EntityFrameworkCore.SqlServer ≥ 6.0 (Microsoft for Developers)
SQL Server：
- 基础功能：2016+
- 保留策略 (HISTORY_RETENTION_PERIOD)：2017+ (Microsoft Learn)
- 行/页压缩：2016 SP1+ Standard/Enterprise 均可

NuGet：

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Volo.Abp.EntityFrameworkCore

📊 三、流程图示

🔨 四、启用时序表的数据库准备

4.1 手动 SQL（2016+ 通用）

ALTER TABLE dbo.Orders
ADD
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN,
    SysEndTime   datetime2 GENERATED ALWAYS AS ROW END   HIDDEN,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

ALTER TABLE dbo.Orders
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));

⚠️ 若已有数据或外键、约束，建议先备份或手动迁移旧历史。

4.2 EF Core 迁移自动化（推荐）

在自定义 YourDbContext : AbpDbContext<YourDbContext> 中：

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder); // 确保 ABP & 插件默认配置生效

    builder.Entity<Order>(b =>
    {
        b.ToTable("Orders", tb => tb.IsTemporal(ttb =>
        {
            ttb.HasHistoryTable("OrdersHistory");
            ttb.HasPeriodStart("SysStartTime");
            ttb.HasPeriodEnd("SysEndTime");
        }));
    });
}

dotnet ef migrations add EnableOrderTemporal
dotnet ef database update

— EF Core 将生成并执行等效的 SQL 脚本。

🧩 五、在 ABP 项目中集成

📂 集中化配置：将所有 IsTemporal(...) 逻辑放在 YourDbContext.OnModelCreating
🔗 无 Module 改动：ABP 自带 EF Core 支持，时序表配置自动纳入迁移脚本

⚙️ 六、自动审计与版本查询 API

6.1 时间旅行查询

public async Task<OrderDto> GetSnapshotAsync(
    Guid orderId,
    DateTime asOf,
    CancellationToken cancellationToken)
{
    try
    {
        return await _dbContext.Orders
            .TemporalAsOf(asOf)
            .Where(o => o.Id == orderId)
            .Select(o => new OrderDto {
                Id        = o.Id,
                Status    = o.Status,
                Timestamp = EF.Property<DateTime>(o, "SysStartTime")
            })
            .FirstOrDefaultAsync(cancellationToken)
            .ConfigureAwait(false);
    }
    catch (OperationCanceledException)
    {
        throw new UserFriendlyException("请求已取消");
    }
    catch (InvalidOperationException ex)
    {
        _logger.LogError(ex, "TemporalAsOf 查询异常");
        throw new UserFriendlyException("查询历史快照失败");
    }
}

6.2 全历史枚举（分页 & 流式）

public IAsyncEnumerable<OrderHistoryDto> StreamHistoryAsync(
    Guid orderId,
    CancellationToken cancellationToken)
{
    return _dbContext.Orders
        .TemporalAll()
        .Where(o => o.Id == orderId)
        .OrderBy(o => EF.Property<DateTime>(o, "SysStartTime"))
        .Select(o => new OrderHistoryDto {
            Id           = o.Id,
            Status       = o.Status,
            SysStartTime = EF.Property<DateTime>(o, "SysStartTime"),
            SysEndTime   = EF.Property<DateTime>(o, "SysEndTime")
        })
        .AsAsyncEnumerable();
}

6.3 RESTful Controller 示例

[ApiController]
[Route("api/orders")]
public class OrdersController : AbpController
{
    private readonly IOrderAppService _service;
    public OrdersController(IOrderAppService service) => _service = service;

    [HttpGet("{id}/snapshot")]
    public Task<OrderDto> GetSnapshot(
        Guid id,
        DateTime asOf,
        CancellationToken cancellationToken)
        => _service.GetSnapshotAsync(id, asOf, cancellationToken);

    [HttpGet("{id}/history/stream")]
    public IAsyncEnumerable<OrderHistoryDto> GetHistoryStream(
        Guid id,
        CancellationToken cancellationToken)
        => _service.StreamHistoryAsync(id, cancellationToken);
}

🗄️ 七、性能与存储考量

索引 & 多租户隔离
- 主表：聚集索引包含 Id, TenantId
- 历史表：非聚集索引 (SysStartTime, TenantId)
```
CREATE NONCLUSTERED INDEX IX_OH_SysStart_Tenant
  ON dbo.OrdersHistory (SysStartTime, TenantId);
```

保留策略（2017+）

-- 启用数据库级保留
ALTER DATABASE [YourDb] SET TEMPORAL_HISTORY_RETENTION ON;
-- 表级自动清理
ALTER TABLE dbo.Orders
  SET (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE            = dbo.OrdersHistory,
    HISTORY_RETENTION_PERIOD = 30 DAYS
  ));

手动清理脚本（2016–2019）

ALTER TABLE dbo.Orders SET (SYSTEM_VERSIONING = OFF);
DELETE FROM dbo.OrdersHistory
  WHERE SysEndTime < DATEADD(DAY, -30, SYSUTCDATETIME());
ALTER TABLE dbo.Orders
  SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));

分区滑动窗口

CREATE PARTITION FUNCTION pf_OH (datetime2)
  AS RANGE RIGHT FOR VALUES ('2023-01-01','2024-01-01');
CREATE PARTITION SCHEME ps_OH
  AS PARTITION pf_OH TO ([PRIMARY],[FG_Hist_2023],[FG_Hist_2024]);
ALTER TABLE dbo.OrdersHistory
  SWITCH PARTITION 1 TO dbo.OrdersHistoryArchive PARTITION 1;

存储压缩

ALTER TABLE dbo.OrdersHistory
  REBUILD PARTITION = ALL
  WITH (DATA_COMPRESSION = PAGE);

写入开销
- 每次 DML 都写历史，IO 成本上升；建议仅对关键实体启用

🛠️ 八、CI/CD & 自动化测试

GitHub Actions 示例 (.github/workflows/ci.yml)：

name: CI

on:
  push:
    branches: [ main ]
  pull_request:

jobs:
  build_and_test:
    runs-on: ubuntu-latest
    services:
      sqlserver:
        image: mcr.microsoft.com/mssql/server:2019-latest
        env:
          ACCEPT_EULA: Y
          SA_PASSWORD: Your_strong!Passw0rd
        ports:
          - 1433:1433
    steps:
      - uses: actions/checkout@v3
      - name: Setup .NET
        uses: actions/setup-dotnet@v3
        with:
          dotnet-version: '7.x'
      - name: Restore
        run: dotnet restore
      - name: Build
        run: dotnet build --no-restore --configuration Release
      - name: Apply Migrations
        run: dotnet ef database update --project src/YourProject/YourProject.csproj
      - name: Test
        run: dotnet test --no-build --configuration Release --verbosity normal

🏁 九、简单演示

样例工程

abp new AbpTemporalDemo -t app -u ef
cd AbpTemporalDemo

Docker Compose

services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=Your_strong!Passw0rd
    ports:
      - "1433:1433"

docker-compose up -d

迁移 & 运行

dotnet ef migrations add InitTemporal
dotnet ef database update

验证
- 模拟多次 UPDATE Orders SET Status = …
- 调用 /api/orders/{id}/snapshot?asOf=… & /api/orders/{id}/history/stream

ABP VNext + SQL Server Temporal Tables：审计与时序数据管理