Temporal Tables — Zamansal Tablolar

"Bu müşterinin adresi 3 ay önce neydi?" gibi sorulara doğrudan cevap verebilirsiniz.

Yapılandırma

// Entity
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public string Email { get; set; } = null!;
    public string Address { get; set; } = null!;

    // EF Core bunları otomatik ekler (opsiyonel — görmek istersen tanımla)
    public DateTime PeriodStart { get; set; }
    public DateTime PeriodEnd { get; set; }
}

// Configuration
public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.ToTable("Customers", t => t.IsTemporal(temporal =>
        {
            temporal.HasPeriodStart("PeriodStart");
            temporal.HasPeriodEnd("PeriodEnd");
            temporal.UseHistoryTable("CustomersHistory", "audit");
        }));
    }
}

SQL Çıktısı

CREATE TABLE [dbo].[Customers] (
    [Id]          INT            IDENTITY(1,1) NOT NULL,
    [Name]        NVARCHAR(MAX)  NOT NULL,
    [Email]       NVARCHAR(MAX)  NOT NULL,
    [Address]     NVARCHAR(MAX)  NOT NULL,
    [PeriodStart] DATETIME2      GENERATED ALWAYS AS ROW START NOT NULL,
    [PeriodEnd]   DATETIME2      GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([PeriodStart], [PeriodEnd]),
    CONSTRAINT [PK_Customers] PRIMARY KEY ([Id])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [audit].[CustomersHistory]));

-- PostgreSQL'de SQL Server'ın SYSTEM_VERSIONING özelliği YOKTUR.
-- Alternatif: Trigger-based history table

CREATE TABLE customers (
    id       INTEGER GENERATED ALWAYS AS IDENTITY,
    name     TEXT NOT NULL,
    email    TEXT NOT NULL,
    address  TEXT NOT NULL,
    CONSTRAINT pk_customers PRIMARY KEY (id)
);

CREATE TABLE customers_history (
    history_id   SERIAL PRIMARY KEY,
    customer_id  INTEGER NOT NULL,
    name         TEXT,
    email        TEXT,
    address      TEXT,
    valid_from   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    valid_to     TIMESTAMPTZ NOT NULL DEFAULT 'infinity',
    operation    TEXT NOT NULL  -- 'INSERT','UPDATE','DELETE'
);

-- Trigger fonksiyonu ile otomatik history tracking:
CREATE OR REPLACE FUNCTION track_customer_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        UPDATE customers_history SET valid_to = NOW()
        WHERE customer_id = OLD.id AND valid_to = 'infinity';
        INSERT INTO customers_history (customer_id, name, email, address, operation)
        VALUES (NEW.id, NEW.name, NEW.email, NEW.address, 'UPDATE');
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO customers_history (customer_id, name, email, address, operation)
        VALUES (NEW.id, NEW.name, NEW.email, NEW.address, 'INSERT');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_customer_history
    AFTER INSERT OR UPDATE OR DELETE ON customers
    FOR EACH ROW EXECUTE FUNCTION track_customer_changes();

Zaman Yolculuğu Sorguları

// Belirli bir andaki hali
var customerInPast = await context.Customers
    .TemporalAsOf(new DateTime(2024, 1, 15, 0, 0, 0, DateTimeKind.Utc))
    .FirstOrDefaultAsync(c => c.Id == 42);

// Tüm değişiklik geçmişi
var history = await context.Customers
    .TemporalAll()
    .Where(c => c.Id == 42)
    .OrderBy(c => EF.Property<DateTime>(c, "PeriodStart"))
    .Select(c => new
    {
        c.Name,
        c.Address,
        ValidFrom = EF.Property<DateTime>(c, "PeriodStart"),
        ValidTo = EF.Property<DateTime>(c, "PeriodEnd")
    })
    .ToListAsync();

// Belirli tarih aralığındaki versiyonlar
var changes = await context.Customers
    .TemporalBetween(startDate, endDate)
    .Where(c => c.Id == 42)
    .ToListAsync();

// İki tarih arasında aktif olan kayıtlar
var overlapping = await context.Customers
    .TemporalFromTo(startDate, endDate)
    .ToListAsync();

Oluşan SQL (TemporalAll)

SELECT [c].[Id], [c].[Name], [c].[Address], [c].[PeriodStart], [c].[PeriodEnd]
FROM [dbo].[Customers] FOR SYSTEM_TIME ALL AS [c]
WHERE [c].[Id] = 42
ORDER BY [c].[PeriodStart]

SELECT c.id, c.name, c.address, c.valid_from, c.valid_to
FROM customers_history AS c
WHERE c.id = 42
ORDER BY c.valid_from;

Örnek Geçmiş Verisi

PostgreSQL'de Temporal Tables:

• PostgreSQL'de SQL Server'ın SYSTEM_VERSIONING özelliği yoktur
• IsTemporal() API'si PostgreSQL'de çalışmaz — bu tamamen SQL Server'a özgü
• Alternatif çözümler:

// PostgreSQL Alternatif 1: Trigger-based history table
// History tablosu ayrı entity olarak tanımlanır:
public class CustomerHistory
{
    public int HistoryId { get; set; }
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public DateTime ValidFrom { get; set; }
    public DateTime ValidTo { get; set; }
    public string Operation { get; set; }  // INSERT, UPDATE, DELETE
}

-- PostgreSQL: Trigger ile otomatik history
CREATE TABLE customers_history (
    history_id   SERIAL PRIMARY KEY,
    customer_id  INT NOT NULL,
    name         TEXT,
    address      TEXT,
    valid_from   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    valid_to     TIMESTAMPTZ NOT NULL DEFAULT 'infinity',
    operation    TEXT NOT NULL  -- 'INSERT','UPDATE','DELETE'
);

-- Trigger fonksiyonu:
CREATE OR REPLACE FUNCTION track_customer_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        -- Eski kaydı kapat
        UPDATE customers_history
        SET valid_to = NOW()
        WHERE customer_id = OLD.id AND valid_to = 'infinity';
        -- Yeni kayıt ekle
        INSERT INTO customers_history (customer_id, name, address, operation)
        VALUES (NEW.id, NEW.name, NEW.address, 'UPDATE');
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO customers_history (customer_id, name, address, operation)
        VALUES (NEW.id, NEW.name, NEW.address, 'INSERT');
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE customers_history
        SET valid_to = NOW()
        WHERE customer_id = OLD.id AND valid_to = 'infinity';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_customer_history
    AFTER INSERT OR UPDATE OR DELETE ON customers
    FOR EACH ROW EXECUTE FUNCTION track_customer_changes();

-- Sorgulama (SQL Server'ın FOR SYSTEM_TIME ALL karşılığı):
SELECT * FROM customers_history
WHERE customer_id = 42
ORDER BY valid_from;

PostgreSQL alternatifleri özet:

Yöntem	Avantaj	Dezavantaj
Trigger + history table	Tam kontrol, özelleştirilebilir	Manuel kurulum
pgAudit extension	DML/DDL seviyesinde log	Sütun bazlı geçmiş yok
EF Interceptor (Bölüm 30)	Provider bağımsız	Uygulama seviyesinde
temporal_tables extension	SQL Server benzeri syntax	Community, resmi değil