SimonCropp / Delta

суббота, 30 ноября 2024 г. в 00:00:03

An approach to implementing a 304 Not Modified leveraging SqlServer change tracking

Delta

Delta is an approach to implementing a 304 Not Modified leveraging SqlServer change tracking

The approach uses a last updated timestamp from the database to generate an ETag. All dynamic requests then have that ETag checked/applied.

This approach works well when the frequency of updates is relatively low. In this scenario, the majority of requests will leverage the result in a 304 Not Modified being returned and the browser loading the content its cache.

Effectively consumers will always receive the most current data, while the load on the server remains low.

See Milestones for release notes.

Assumptions

Frequency of updates to data is relatively low compared to reads
Using either SQL Server Change Tracking and/or SQL Server Row Versioning

304 Not Modified Flow

graph TD
    Request
    CalculateEtag[Calculate current ETag<br/>based on timestamp<br/>from web assembly and SQL]
    IfNoneMatch{Has<br/>If-None-Match<br/>header?}
    EtagMatch{Current<br/>Etag matches<br/>If-None-Match?}
    AddETag[Add current ETag<br/>to Response headers]
    304[Respond with<br/>304 Not-Modified]
    Request --> CalculateEtag
    CalculateEtag --> IfNoneMatch
    IfNoneMatch -->|Yes| EtagMatch
    IfNoneMatch -->|No| AddETag
    EtagMatch -->|No| AddETag
    EtagMatch -->|Yes| 304

ETag calculation logic

The ETag is calculated from a combination several parts

AssemblyWriteTime

The last write time of the web entry point assembly

var webAssemblyLocation = Assembly.GetEntryAssembly()!.Location;
AssemblyWriteTime = File.GetLastWriteTime(webAssemblyLocation).Ticks.ToString();

^{snippet source | anchor}

SQL timestamp

A combination of change_tracking_current_version (if tracking is enabled) and @@DBTS (row version timestamp)

declare @changeTracking bigint = change_tracking_current_version();
declare @timeStamp bigint = convert(bigint, @@dbts);

if (@changeTracking is null)
  select cast(@timeStamp as varchar)
else
  select cast(@timeStamp as varchar) + '-' + cast(@changeTracking as varchar)

^{snippet source | anchor}

Suffix

An optional string suffix that is dynamically calculated at runtime based on the current HttpContext.

var app = builder.Build();
app.UseDelta(suffix: httpContext => "MySuffix");

^{snippet source | anchor}

Combining the above

internal static string BuildEtag(string timeStamp, string? suffix)
{
    if (suffix == null)
    {
        return $"\"{AssemblyWriteTime}-{timeStamp}\"";
    }

    return $"\"{AssemblyWriteTime}-{timeStamp}-{suffix}\"";
}

^{snippet source | anchor}

NuGet

Delta is shipped as two nugets:

Delta: Delivers functionality using SqlConnection and SqlTransaction.
Delta.EF: Delivers functionality using SQL Server EF Database Provider.

Only one of the above should be used.

Usage

DB Schema

Ensure SQL Server Change Tracking and/or SQL Server Row Versioning is enabled for all relevant tables.

Example SQL schema:

-- Tables

CREATE TABLE [dbo].[Companies](
	[Id] [uniqueidentifier] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
	[Content] [nvarchar](max) NULL,
 CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[Employees](
	[Id] [uniqueidentifier] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
	[CompanyId] [uniqueidentifier] NOT NULL,
	[Content] [nvarchar](max) NULL,
	[Age] [int] NOT NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Employees_CompanyId] ON [dbo].[Employees]
(
	[CompanyId] ASC
) ON [PRIMARY]

^{snippet source | anchor}

Add to WebApplicationBuilder

var builder = WebApplication.CreateBuilder();
builder.Services.AddScoped(_ => new SqlConnection(connectionString));
var app = builder.Build();
app.UseDelta();

^{snippet source | anchor}

Add to a Route Group

To add to a specific Route Group:

app.MapGroup("/group")
    .UseDelta()
    .MapGet("/", () => "Hello Group!");

^{snippet source | anchor}

ShouldExecute

Optionally control what requests Delta is executed on.

var app = builder.Build();
app.UseDelta(
    shouldExecute: httpContext =>
    {
        var path = httpContext.Request.Path.ToString();
        return path.Contains("match");
    });

^{snippet source | anchor}

Custom Connection discovery

By default, Delta uses HttpContext.RequestServices to discover the SqlConnection and SqlTransaction:

static Connection DiscoverConnection(HttpContext httpContext)
{
    var provider = httpContext.RequestServices;
    var connection = provider.GetRequiredService<SqlConnection>();
    var transaction = provider.GetService<SqlTransaction>();
    return new(connection, transaction);
}

^{snippet source | anchor}

To use custom connection discovery:

var application = webApplicationBuilder.Build();
application.UseDelta(
    getConnection: httpContext => httpContext.RequestServices.GetRequiredService<SqlConnection>());

^{snippet source | anchor}

To use custom connection and transaction discovery:

var webApplication = webApplicationBuilder.Build();
webApplication.UseDelta(
    getConnection: httpContext =>
    {
        var provider = httpContext.RequestServices;
        var sqlConnection = provider.GetRequiredService<SqlConnection>();
        var sqlTransaction = provider.GetService<SqlTransaction>();
        return new(sqlConnection, sqlTransaction);
    });

^{snippet source | anchor}

EF Usage

DbContext using RowVersion

Enable row versioning in Entity Framework

public class SampleDbContext(DbContextOptions options) :
    DbContext(options)
{
    public DbSet<Employee> Employees { get; set; } = null!;
    public DbSet<Company> Companies { get; set; } = null!;

    protected override void OnModelCreating(ModelBuilder builder)
    {
        var company = builder.Entity<Company>();
        company.HasKey(_ => _.Id);
        company
            .HasMany(_ => _.Employees)
            .WithOne(_ => _.Company)
            .IsRequired();
        company
            .Property(_ => _.RowVersion)
            .IsRowVersion()
            .HasConversion<byte[]>();

        var employee = builder.Entity<Employee>();
        employee.HasKey(_ => _.Id);
        employee
            .Property(_ => _.RowVersion)
            .IsRowVersion()
            .HasConversion<byte[]>();
    }
}

^{snippet source | anchor}

Add to WebApplicationBuilder

var builder = WebApplication.CreateBuilder();
builder.Services.AddSqlServer<SampleDbContext>(database.ConnectionString);
var app = builder.Build();
app.UseDelta<SampleDbContext>();

^{snippet source | anchor}

Add to a Route Group

To add to a specific Route Group:

app.MapGroup("/group")
    .UseDelta<SampleDbContext>()
    .MapGet("/", () => "Hello Group!");

^{snippet source | anchor}

ShouldExecute

Optionally control what requests Delta is executed on.

var app = builder.Build();
app.UseDelta<SampleDbContext>(
    shouldExecute: httpContext =>
    {
        var path = httpContext.Request.Path.ToString();
        return path.Contains("match");
    });

^{snippet source | anchor}

UseResponseDiagnostics

Response diagnostics is an opt-in feature that includes extra log information in the response headers.

Enable by setting UseResponseDiagnostics to true at startup:

DeltaExtensions.UseResponseDiagnostics = true;

^{snippet source | anchor}

Response diagnostics headers are prefixed with Delta-.

Example Response header when the Request has not If-None-Match header.

Helpers

Utility methods for working with databases using the Delta conventions.

GetLastTimeStamp

For a `SqlConnection`:

var timeStamp = await sqlConnection.GetLastTimeStamp();

^{snippet source | anchor}

For a `DbContext`:

var timeStamp = await dbContext.GetLastTimeStamp();

^{snippet source | anchor}

GetDatabasesWithTracking

Get a list of all databases with change tracking enabled.

var trackedDatabases = await sqlConnection.GetTrackedDatabases();
foreach (var db in trackedDatabases)
{
    Trace.WriteLine(db);
}

^{snippet source | anchor}

Uses the following SQL:

select d.name
from sys.databases as d inner join
  sys.change_tracking_databases as t on
  t.database_id = d.database_id

^{snippet source | anchor}

GetTrackedTables

Get a list of all tracked tables in database.

var trackedTables = await sqlConnection.GetTrackedTables();
foreach (var db in trackedTables)
{
    Trace.WriteLine(db);
}

^{snippet source | anchor}

Uses the following SQL:

select t.Name
from sys.tables as t left join
  sys.change_tracking_tables as c on t.[object_id] = c.[object_id]
where c.[object_id] is not null

^{snippet source | anchor}

IsTrackingEnabled

Determine if change tracking is enabled for a database.

var isTrackingEnabled = await sqlConnection.IsTrackingEnabled();

^{snippet source | anchor}

Uses the following SQL:

select count(d.name)
from sys.databases as d inner join
  sys.change_tracking_databases as t on
  t.database_id = d.database_id
where d.name = '{database}'

^{snippet source | anchor}

EnableTracking

Enable change tracking for a database.

await sqlConnection.EnableTracking();

^{snippet source | anchor}

Uses the following SQL:

alter database {database}
set change_tracking = on
(
  change_retention = {retentionDays} days,
  auto_cleanup = on
)

^{snippet source | anchor}

DisableTracking

Disable change tracking for a database and all tables within that database.

await sqlConnection.DisableTracking();

^{snippet source | anchor}

Uses the following SQL:

For disabling tracking on a database:

alter database [{database}] set change_tracking = off;

^{snippet source | anchor}

For disabling tracking on tables:

alter table [{table}] disable change_tracking;

^{snippet source | anchor}

SetTrackedTables

Enables change tracking for all tables listed, and disables change tracking for all tables not listed.

await sqlConnection.SetTrackedTables(["Companies"]);

^{snippet source | anchor}

Uses the following SQL:

For enabling tracking on a database:

alter database {database}
set change_tracking = on
(
  change_retention = {retentionDays} days,
  auto_cleanup = on
)

^{snippet source | anchor}

For enabling tracking on tables:

alter table [{table}] enable change_tracking

^{snippet source | anchor}

For disabling tracking on tables:

alter table [{table}] disable change_tracking;

^{snippet source | anchor}

Programmatic client usage

Delta is primarily designed to support web browsers as a client. All web browsers have the necessary 304 and caching functionally required.

In the scenario where web apis (that support using 304) are being consumed using .net as a client, consider using one of the below extensions to cache responses.

Icon

Estuary designed by Daan from The Noun Project.

SimonCropp / Delta

Delta

Assumptions

304 Not Modified Flow

ETag calculation logic

AssemblyWriteTime

SQL timestamp

Suffix

Combining the above

NuGet

Usage

DB Schema

Add to WebApplicationBuilder

Add to a Route Group

ShouldExecute

Custom Connection discovery

EF Usage

DbContext using RowVersion

Add to WebApplicationBuilder

Add to a Route Group

ShouldExecute

UseResponseDiagnostics

Helpers

GetLastTimeStamp

For a SqlConnection:

For a DbContext:

GetDatabasesWithTracking

GetTrackedTables

IsTrackingEnabled

EnableTracking

DisableTracking

For disabling tracking on a database:

For disabling tracking on tables:

SetTrackedTables

For enabling tracking on a database:

For enabling tracking on tables:

For disabling tracking on tables:

Programmatic client usage

Icon

For a `SqlConnection`:

For a `DbContext`: