SQL Server/Azure SQL časové tabulky

Dočasné tabulky SQL Serveru automaticky sledují všechna data uložená v tabulce i po aktualizaci nebo odstranění těchto dat. Toho dosáhnete tak, že vytvoříte paralelní tabulku historie, do které se ukládají historická data s časovým razítkem při každé změně hlavní tabulky. To umožňuje dotazování historických dat, například pro účely auditu, nebo jejich obnovení, například po náhodné změně nebo odstranění.

EF Core podporuje:

• Vytvoření dočasných tabulek pomocí migrací EF Core
• Transformace existujících tabulek do dočasných tabulek znovu pomocí migrací
• Dotazování historických dat
• Obnovení dat z nějakého bodu v minulosti

Konfigurace časové tabulky

Tvůrce modelů lze použít ke konfiguraci tabulky jako temporální. Například:

modelBuilder
    .Entity<Employee>()
    .ToTable("Employees", b => b.IsTemporal());

Při použití EF Core k vytvoření databáze se nová tabulka nakonfiguruje jako dočasná tabulka s výchozími nastaveními SQL Serveru pro časové razítka a tabulku historie. Například si představte typ entity Employee:

public class Employee
{
    public Guid EmployeeId { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public string Department { get; set; }
    public string Address { get; set; }
    public decimal AnnualSalary { get; set; }
}

Vytvořená dočasná tabulka vypadá takto:

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'CREATE TABLE [Employees] (
    [EmployeeId] uniqueidentifier NOT NULL,
    [Name] nvarchar(100) NULL,
    [Position] nvarchar(100) NULL,
    [Department] nvarchar(100) NULL,
    [Address] nvarchar(1024) NULL,
    [AnnualSalary] decimal(10,2) NOT NULL,
    [PeriodEnd] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    [PeriodStart] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY ([EmployeeId]),
    PERIOD FOR SYSTEM_TIME([PeriodStart], [PeriodEnd])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].[EmployeeHistory]))');

Všimněte si, že SQL Server vytvoří dva skryté datetime2 sloupce volaný PeriodEnd a PeriodStart. Tyto "sloupce období" představují časový rozsah, během kterého existovala data v řádku. Ve výchozím nastavení jsou tyto sloupce mapovány na stínové vlastnosti v modelu EF Core, což umožňuje jejich použití v dotazech, jak je znázorněno později. Počínaje EF Core 11 je možné sloupce období mapovat také na vlastnosti CLR ve vašem typu entity.

Všimněte si také, že se automaticky vytvoří přidružená EmployeeHistory tabulka historie. Názvy sloupců období a tabulky historie je možné změnit s další konfigurací tvůrce modelů. Například:

modelBuilder
    .Entity<Employee>()
    .ToTable(
        "Employees",
        b => b.IsTemporal(
            b =>
            {
                b.HasPeriodStart("ValidFrom");
                b.HasPeriodEnd("ValidTo");
                b.UseHistoryTable("EmployeeHistoricalData");
            }));

To se projeví v tabulce vytvořené SQL Serverem:

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'CREATE TABLE [Employees] (
    [EmployeeId] uniqueidentifier NOT NULL,
    [Name] nvarchar(100) NULL,
    [Position] nvarchar(100) NULL,
    [Department] nvarchar(100) NULL,
    [Address] nvarchar(1024) NULL,
    [AnnualSalary] decimal(10,2) NOT NULL,
    [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY ([EmployeeId]),
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].[EmployeeHistoricalData]))');

Použití dočasných tabulek

Ve většině případů se dočasné tabulky používají stejně jako všechny ostatní tabulky. To znamená, že sloupce období a historická data jsou transparentně zpracovávány SQL Serverem tak, aby je aplikace mohl ignorovat. Nové entity je například možné uložit do databáze běžným způsobem:

context.AddRange(
    new Employee
    {
        Name = "Pinky Pie",
        Address = "Sugarcube Corner, Ponyville, Equestria",
        Department = "DevDiv",
        Position = "Party Organizer",
        AnnualSalary = 100.0m
    },
    new Employee
    {
        Name = "Rainbow Dash",
        Address = "Cloudominium, Ponyville, Equestria",
        Department = "DevDiv",
        Position = "Ponyville weather patrol",
        AnnualSalary = 900.0m
    },
    new Employee
    {
        Name = "Fluttershy",
        Address = "Everfree Forest, Equestria",
        Department = "DevDiv",
        Position = "Animal caretaker",
        AnnualSalary = 30.0m
    });

await context.SaveChangesAsync();

Tato data se pak dají dotazovat, aktualizovat a odstraňovat běžným způsobem. Například:

var employee = await context.Employees.SingleAsync(e => e.Name == "Rainbow Dash");
context.Remove(employee);
await context.SaveChangesAsync();

Po normálním sledovacím dotazu je také možné získat přístup ke hodnotám ze sloupců období aktuálních dat ze sledovaných entit. Pokud jsou sloupce období mapovány na vlastnosti CLR, můžete k nim přistupovat přímo z entity; v opačném případě je použijte EF.Property pro přístup jako stínové vlastnosti. Například:

var employees = await context.Employees.ToListAsync();
foreach (var employee in employees)
{
    var employeeEntry = context.Entry(employee);
    var validFrom = employeeEntry.Property<DateTime>("ValidFrom").CurrentValue;
    var validTo = employeeEntry.Property<DateTime>("ValidTo").CurrentValue;

    Console.WriteLine($"  Employee {employee.Name} valid from {validFrom} to {validTo}");
}

Toto se tiskne:

Starting data:
  Employee Pinky Pie valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM
  Employee Rainbow Dash valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM
  Employee Fluttershy valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM

Všimněte si, že ValidTo sloupec (ve výchozím nastavení volaný PeriodEnd) obsahuje maximální datetime2 hodnotu. To platí vždy pro aktuální řádky v tabulce. Sloupce ValidFrom (ve výchozím nastavení volané PeriodStart) obsahují čas UTC, kdy byl řádek vložen.

Dotazování historických dat

EF Core podporuje dotazy, které zahrnují historická data prostřednictvím několika specializovaných operátorů dotazů:

• TemporalAsOf: Vrátí řádky, které byly aktivní (aktuální) v daném čase UTC. Toto je jeden řádek z aktuální tabulky nebo tabulky historie pro daný primární klíč.
• TemporalAll: Vrátí všechny řádky v historických datech. Obvykle se jedná o mnoho řádků z tabulky historie nebo aktuální tabulky pro daný primární klíč.
• TemporalFromTo: Vrátí všechny řádky, které byly aktivní mezi dvěma danými časy UTC. Může to být mnoho řádků z tabulky historie nebo aktuální tabulky pro daný primární klíč.
• TemporalBetween: Stejné jako TemporalFromTo, ale zahrnuje řádky, které se staly aktivními na horní hranici.
• TemporalContainedIn: Vrátí všechny řádky, které začaly být aktivní a skončily aktivní mezi dvěma danými časy UTC. Může to být mnoho řádků z tabulky historie nebo aktuální tabulky pro daný primární klíč.

Například po provedení některých aktualizací a odstranění dat můžeme spustit dotaz pomocí TemporalAll zobrazení historických dat:

var history = await context
    .Employees
    .TemporalAll()
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => EF.Property<DateTime>(e, "ValidFrom"))
    .Select(
        e => new
        {
            Employee = e,
            ValidFrom = EF.Property<DateTime>(e, "ValidFrom"),
            ValidTo = EF.Property<DateTime>(e, "ValidTo")
        })
    .ToListAsync();

foreach (var pointInTime in history)
{
    Console.WriteLine(
        $"  Employee {pointInTime.Employee.Name} was '{pointInTime.Employee.Position}' from {pointInTime.ValidFrom} to {pointInTime.ValidTo}");
}

Všimněte si, jak lze EF.Property metodu použít pro přístup k hodnotám z periodických sloupců. Používá se v OrderBy klauzuli k seřazení dat a pak v projekci zahrnout tyto hodnoty do vrácených dat. Pokud jsou sloupce období mapovány na vlastnosti CLR, můžete je odkazovat přímo v dotazu namísto použití EF.Property.

Tento dotaz vrátí následující data:

Historical data for Rainbow Dash:
  Employee Rainbow Dash was 'Ponyville weather patrol' from 8/26/2021 4:38:58 PM to 8/26/2021 4:40:29 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM
  Employee Rainbow Dash was 'Wonderbolt' from 8/26/2021 4:43:29 PM to 8/26/2021 4:44:59 PM

Všimněte si, že poslední vrácený řádek přestal být aktivní v čase 26. 8. 2021 v 16:44:59 odpoledne. Důvodem je, že řádek pro Rainbow Dash byl v té době odstraněn z hlavní tabulky. Později se podíváme, jak se tato data dají obnovit.

Podobné dotazy lze psát pomocí TemporalFromTo, TemporalBetweennebo TemporalContainedIn. Například:

var history = await context
    .Employees
    .TemporalBetween(timeStamp2, timeStamp3)
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => EF.Property<DateTime>(e, "ValidFrom"))
    .Select(
        e => new
        {
            Employee = e,
            ValidFrom = EF.Property<DateTime>(e, "ValidFrom"),
            ValidTo = EF.Property<DateTime>(e, "ValidTo")
        })
    .ToListAsync();

Tento dotaz vrátí následující řádky:

Historical data for Rainbow Dash between 8/26/2021 4:41:14 PM and 8/26/2021 4:42:44 PM:
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM

Obnovení historických dat

Jak už bylo zmíněno výše, Rainbow Dash byla z Employees tabulky odstraněna. To byla jasně chyba, takže se vrátíme k určitému bodu v čase a obnovíme chybějící řádek z této doby.

var employee = await context
    .Employees
    .TemporalAsOf(timeStamp2)
    .SingleAsync(e => e.Name == "Rainbow Dash");

context.Add(employee);
await context.SaveChangesAsync();

Tento dotaz vrátí jeden řádek pro Rainbow Dash, protože byl v daném čase UTC. Dotazy používající časové operátory ve výchozím nastavení nejsou sledovány, takže zde vrácená entita není sledována. To dává smysl, protože v hlavní tabulce aktuálně neexistuje. Pokud chcete entitu znovu vložit do hlavní tabulky, jednoduše ji označíme jako Added a pak zavoláme SaveChanges.

Po opětovném vložení řádku Rainbow Dash se dotazem na historická data zobrazí, že se řádek obnovil tak, jak byl v daném čase UTC:

Historical data for Rainbow Dash:
  Employee Rainbow Dash was 'Ponyville weather patrol' from 8/26/2021 4:38:58 PM to 8/26/2021 4:40:29 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM
  Employee Rainbow Dash was 'Wonderbolt' from 8/26/2021 4:43:29 PM to 8/26/2021 4:44:59 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:44:59 PM to 12/31/9999 11:59:59 PM

Mapování časových sloupců na vlastnosti CLR

Ve výchozím nastavení se sloupce období v dočasné tabulce mapují na vlastnosti shadow v modelu EF Core, což znamená, že ve vašem typu entity .NET nemusí existovat. Počínaje EF Core 11 můžete místo toho namapovat časové sloupce na běžné vlastnosti CLR pro typ entity, což vám umožní přímý přístup k jejich hodnotám.

Uděláte to tak, že přidáte DateTime vlastnosti počátečního a koncového období do typu entity:

public class Employee
{
    public Guid EmployeeId { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public string Department { get; set; }
    public string Address { get; set; }
    public decimal AnnualSalary { get; set; }
    public DateTime PeriodStart { get; set; }
    public DateTime PeriodEnd { get; set; }
}

Potom nakonfigurujte dočasnou tabulku tak, aby používala tyto vlastnosti prostřednictvím výrazu lambda:

modelBuilder
    .Entity<Employee>()
    .ToTable(
        "Employees",
        b => b.IsTemporal(
            b =>
            {
                b.HasPeriodStart(e => e.PeriodStart);
                b.HasPeriodEnd(e => e.PeriodEnd);
            }));

Pokud jsou sloupce období mapovány na vlastnosti CLR, můžete k jejich hodnotám přistupovat přímo v entitě, místo použití EF.Property.

var history = context
    .Employees
    .TemporalAll()
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => e.PeriodStart)
    .Select(
        e => new
        {
            Employee = e,
            e.PeriodStart,
            e.PeriodEnd
        })
    .ToList();