Files
ExecuteR/tests/Backend.Tests/DatabaseMaintenanceTests.cs
Alphaeus Mote 295481cc70 feat(database): insight, maintenance and periodic backups
Settings › Database on the server side: provider, version, size, free
space and per-table rows/sizes for SQLite, Postgres and SQL Server;
maintenance on demand or on a schedule (SQLite VACUUM + optimize,
Postgres VACUUM (ANALYZE), SQL Server index reorganize + statistics);
and optional periodic backups, off by default: SQLite online copies
(backup API, quick_check, gzip) to DATABASE_BACKUP_PATH and/or the S3
store under executer/backups/, SQL Server COPY_ONLY backups into a
folder on its own host. Postgres backups are refused with the reason.

One task at a time across replicas (claimed with a compare-and-swap in
the settings table, with a heartbeat), retention per destination,
settings saved with If-Match, downloads behind step-up, and every
start, finish, download and delete audited. README documents manual
restore per provider.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-11-06 11:39:32 -05:00

435 lines
24 KiB
C#
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
using System.IO.Compression;
using System.Net;
using System.Net.Http.Json;
using Backend.Data;
using Backend.Maintenance;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Shared.Contracts.Api;
using Shared.Contracts.Serialization;
using Xunit;
namespace Backend.Tests;
/// <summary>
/// Settings › Database on SQLite: insight numbers, VACUUM, backups that open as
/// a database, retention in both destinations, schedules, permissions, step-up
/// on download and names that try to leave the backup folder.
/// </summary>
public class DatabaseMaintenanceTests(BackendTestFixture fixture) : IClassFixture<BackendTestFixture>
{
private sealed record BackupList(List<DatabaseBackupDto> Backups, string? ObjectStoreError);
private DatabaseTaskRunner Runner => fixture.Services.GetRequiredService<DatabaseTaskRunner>();
private string BackupFolder => fixture.Services.GetRequiredService<BackupPaths>().Folder;
private static CancellationToken Ct => TestContext.Current.CancellationToken;
private async Task ResetAsync()
{
await Runner.Current;
await fixture.WithDbAsync(db => db.AppSettings.Where(s => s.Key.StartsWith(DatabaseSettingsStore.Key)).ExecuteDeleteAsync(Ct));
fixture.Services.GetRequiredService<DatabaseSettingsStore>().Invalidate();
if (Directory.Exists(BackupFolder))
{
Directory.Delete(BackupFolder, recursive: true);
}
fixture.BackupStore.Objects.Clear();
fixture.BackupStore.Unavailable = "S3 is not configured.";
fixture.BackupStore.FailUploads = null;
}
private static async Task<T> ReadAsync<T>(HttpResponseMessage response)
{
Assert.True(response.IsSuccessStatusCode, $"{(int)response.StatusCode}: {await response.Content.ReadAsStringAsync()}");
return (await response.Content.ReadFromJsonAsync<T>(JsonDefaults.Options, Ct))!;
}
private static async Task<HttpResponseMessage> SaveAsync(HttpClient client, Func<DatabaseSettingsDto, DatabaseSettingsDto> change)
{
var current = await client.GetAsync(ApiRoutes.DatabaseSettings, Ct);
var settings = await ReadAsync<DatabaseSettingsDto>(current);
using var put = new HttpRequestMessage(HttpMethod.Put, ApiRoutes.DatabaseSettings) { Content = JsonContent.Create(change(settings), options: JsonDefaults.Options) };
put.Headers.TryAddWithoutValidation("If-Match", current.Headers.ETag!.Tag);
return await client.SendAsync(put, Ct);
}
/// <summary>Starts a task, waits for it, and returns the status after.</summary>
private async Task<DatabaseStatusDto> RunAsync(HttpClient client, string route)
{
var started = await client.PostAsync(route, null, Ct);
Assert.Equal(HttpStatusCode.Accepted, started.StatusCode);
await Runner.Current;
return await ReadAsync<DatabaseStatusDto>(await client.GetAsync(ApiRoutes.DatabaseStatus, Ct));
}
private static string Download(DatabaseBackupDto backup) => ApiRoutes.DatabaseBackupDownload
.Replace("{location}", backup.Location, StringComparison.Ordinal).Replace("{name}", backup.Name, StringComparison.Ordinal);
private static string Delete(string location, string name) => ApiRoutes.DatabaseBackup
.Replace("{location}", location, StringComparison.Ordinal).Replace("{name}", name, StringComparison.Ordinal);
/// <summary>Unzips a backup and counts the rows of a table in it, opened as the SQLite database it is.</summary>
private static async Task<long> RowsInBackupAsync(byte[] gzipped, string table)
{
var path = Path.Combine(Path.GetTempPath(), $"backup-check-{Guid.NewGuid():N}.db");
try
{
await using (var input = new GZipStream(new MemoryStream(gzipped), CompressionMode.Decompress))
await using (var output = File.Create(path))
{
await input.CopyToAsync(output, Ct);
}
await using var connection = new SqliteConnection(new SqliteConnectionStringBuilder { DataSource = path, Mode = SqliteOpenMode.ReadOnly, Pooling = false }.ToString());
await connection.OpenAsync(Ct);
await using var command = connection.CreateCommand();
command.CommandText = $"SELECT COUNT(*) FROM \"{table}\"";
return (long)(await command.ExecuteScalarAsync(Ct))!;
}
finally
{
File.Delete(path);
}
}
[Fact]
public async Task InsightReportsSizeFreePagesAndEveryTableBiggestFirst()
{
await ResetAsync();
using var admin = await fixture.SignedInAsync("db-insight-admin");
// Rows to measure, then most of them deleted: their pages go on the free list.
await fixture.WithDbAsync(db => db.Database.ExecuteSqlRawAsync(
"CREATE TABLE IF NOT EXISTS InsightFiller (Id INTEGER PRIMARY KEY, Body TEXT NOT NULL)", Ct));
await fixture.WithDbAsync(db => db.Database.ExecuteSqlRawAsync(
"INSERT INTO InsightFiller (Body) SELECT hex(randomblob(2000)) FROM (WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 500) SELECT x FROM n)", Ct));
await fixture.WithDbAsync(db => db.Database.ExecuteSqlRawAsync("DELETE FROM InsightFiller WHERE Id > 10", Ct));
var insight = await ReadAsync<DatabaseInsightDto>(await admin.GetAsync(ApiRoutes.DatabaseInsight, Ct));
Assert.Equal("sqlite", insight.Provider);
Assert.False(string.IsNullOrWhiteSpace(insight.Version));
Assert.Equal(new FileInfo(fixture.DatabasePath).Length + (File.Exists(fixture.DatabasePath + "-wal") ? new FileInfo(fixture.DatabasePath + "-wal").Length : 0)
+ (File.Exists(fixture.DatabasePath + "-shm") ? new FileInfo(fixture.DatabasePath + "-shm").Length : 0), insight.SizeBytes);
Assert.Contains(insight.Files, f => f.Name == Path.GetFileName(fixture.DatabasePath));
Assert.True(insight.FreeBytes > 1_000_000, $"free {insight.FreeBytes}");
Assert.False(insight.RowsApproximate);
var users = await fixture.WithDbAsync(db => db.Users.LongCountAsync(Ct));
Assert.Equal(users, insight.Tables.Single(t => t.Name == "Users").Rows);
Assert.Equal(10, insight.Tables.Single(t => t.Name == "InsightFiller").Rows);
Assert.DoesNotContain(insight.Tables, t => t.Name.StartsWith("sqlite_", StringComparison.Ordinal));
if (insight.TableSizesUnavailable is null)
{
Assert.All(insight.Tables, t => Assert.NotNull(t.SizeBytes));
Assert.Equal(insight.Tables.OrderByDescending(t => t.SizeBytes).Select(t => t.Name), insight.Tables.Select(t => t.Name));
}
else
{
Assert.All(insight.Tables, t => Assert.Null(t.SizeBytes));
}
await fixture.WithDbAsync(db => db.Database.ExecuteSqlRawAsync("DROP TABLE InsightFiller", Ct));
}
[Fact]
public async Task MaintenanceVacuumsTheFreePagesAwayAndIsRecorded()
{
await ResetAsync();
using var admin = await fixture.SignedInAsync("db-vacuum-admin");
await fixture.WithDbAsync(db => db.Database.ExecuteSqlRawAsync(
"CREATE TABLE VacuumFiller (Id INTEGER PRIMARY KEY, Body TEXT NOT NULL)", Ct));
await fixture.WithDbAsync(db => db.Database.ExecuteSqlRawAsync(
"INSERT INTO VacuumFiller (Body) SELECT hex(randomblob(2000)) FROM (WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 500) SELECT x FROM n)", Ct));
await fixture.WithDbAsync(db => db.Database.ExecuteSqlRawAsync("DROP TABLE VacuumFiller", Ct));
var before = await ReadAsync<DatabaseInsightDto>(await admin.GetAsync(ApiRoutes.DatabaseInsight, Ct));
Assert.True(before.FreeBytes > 1_000_000);
var status = await RunAsync(admin, ApiRoutes.DatabaseMaintenance);
Assert.Null(status.Running);
Assert.Equal(DatabaseOutcomes.Succeeded, status.LastMaintenance?.Outcome);
Assert.Equal("db-vacuum-admin", status.LastMaintenance?.By);
var after = await ReadAsync<DatabaseInsightDto>(await admin.GetAsync(ApiRoutes.DatabaseInsight, Ct));
Assert.Equal(0, after.FreeBytes);
Assert.True(after.SizeBytes < before.SizeBytes - 1_000_000, $"{before.SizeBytes} -> {after.SizeBytes}");
var audited = await fixture.WithDbAsync(db => db.AuditEvents.Where(e => e.Action.StartsWith("database-maintenance-")).Select(e => e.Action).ToListAsync(Ct));
Assert.Contains("database-maintenance-started", audited);
Assert.Contains("database-maintenance-succeeded", audited);
}
[Fact]
public async Task ABackupIsAConsistentDatabaseThatOpensAndReads()
{
await ResetAsync();
using var admin = await fixture.SignedInAsync("db-backup-admin");
var status = await RunAsync(admin, ApiRoutes.DatabaseBackups);
Assert.Equal(DatabaseOutcomes.Succeeded, status.LastBackup?.Outcome);
Assert.Contains("server folder", status.LastBackup!.Detail);
var list = await ReadAsync<BackupList>(await admin.GetAsync(ApiRoutes.DatabaseBackups, Ct));
var backup = Assert.Single(list.Backups);
Assert.Equal(DatabaseBackupLocations.Local, backup.Location);
Assert.Matches(@"^executer-sqlite-\d{8}-\d{6}-\d{3}\.db\.gz$", backup.Name);
Assert.True(File.Exists(Path.Combine(BackupFolder, backup.Name)));
Assert.Empty(Directory.GetFiles(BackupFolder, ".*").Select(Path.GetFileName));
var users = await fixture.WithDbAsync(db => db.Users.LongCountAsync(Ct));
Assert.True(users > 0);
Assert.Equal(users, await RowsInBackupAsync(await File.ReadAllBytesAsync(Path.Combine(BackupFolder, backup.Name), Ct), "Users"));
}
[Fact]
public async Task RetentionKeepsTheNewestInEachDestination()
{
await ResetAsync();
using var admin = await fixture.SignedInAsync("db-retention-admin");
fixture.BackupStore.Unavailable = null;
Assert.Equal(HttpStatusCode.OK, (await SaveAsync(admin, s => s with { BackupKeep = 2, BackupToLocal = true, BackupToObjectStore = true })).StatusCode);
for (var i = 0; i < 3; i++)
{
Assert.Equal(DatabaseOutcomes.Succeeded, (await RunAsync(admin, ApiRoutes.DatabaseBackups)).LastBackup?.Outcome);
}
var list = (await ReadAsync<BackupList>(await admin.GetAsync(ApiRoutes.DatabaseBackups, Ct))).Backups;
var local = list.Where(b => b.Location == DatabaseBackupLocations.Local).Select(b => b.Name).ToList();
var stored = list.Where(b => b.Location == DatabaseBackupLocations.ObjectStore).Select(b => b.Name).ToList();
Assert.Equal(2, local.Count);
Assert.Equal(local, stored);
Assert.Equal(local.OrderDescending(StringComparer.Ordinal), local);
Assert.Equal(2, fixture.BackupStore.Objects.Count);
Assert.All(fixture.BackupStore.Objects.Keys, k => Assert.StartsWith(DatabaseBackups.ObjectPrefix, k));
// Something else's object under the prefix is never touched.
fixture.BackupStore.Objects[DatabaseBackups.ObjectPrefix + "notes.txt"] = ([1], DateTimeOffset.UtcNow);
Assert.Equal(HttpStatusCode.OK, (await SaveAsync(admin, s => s with { BackupKeep = 1 })).StatusCode);
await RunAsync(admin, ApiRoutes.DatabaseBackups);
Assert.Single(Directory.GetFiles(BackupFolder, "executer-sqlite-*"));
Assert.Equal(2, fixture.BackupStore.Objects.Count);
Assert.Contains(DatabaseBackups.ObjectPrefix + "notes.txt", fixture.BackupStore.Objects.Keys);
}
[Fact]
public async Task ObjectStoreOnlyBackupsLeaveNothingLocalAndAFailedUploadFails()
{
await ResetAsync();
using var admin = await fixture.SignedInAsync("db-s3-admin");
fixture.BackupStore.Unavailable = null;
Assert.Equal(HttpStatusCode.OK, (await SaveAsync(admin, s => s with { BackupToLocal = false, BackupToObjectStore = true })).StatusCode);
var status = await RunAsync(admin, ApiRoutes.DatabaseBackups);
Assert.Equal(DatabaseOutcomes.Succeeded, status.LastBackup?.Outcome);
Assert.Empty(Directory.GetFiles(BackupFolder).Select(Path.GetFileName));
var (key, stored) = Assert.Single(fixture.BackupStore.Objects);
Assert.True(await RowsInBackupAsync(stored.Content, "Users") > 0);
// Downloads stream from the store.
var backup = Assert.Single((await ReadAsync<BackupList>(await admin.GetAsync(ApiRoutes.DatabaseBackups, Ct))).Backups);
Assert.Equal(DatabaseBackupLocations.ObjectStore, backup.Location);
await admin.StepUpAsync();
var downloaded = await admin.PostAsync(Download(backup), null, Ct);
Assert.Equal(HttpStatusCode.OK, downloaded.StatusCode);
Assert.Equal(stored.Content, await downloaded.Content.ReadAsByteArrayAsync(Ct));
fixture.BackupStore.FailUploads = "bucket is gone";
status = await RunAsync(admin, ApiRoutes.DatabaseBackups);
Assert.Equal(DatabaseOutcomes.Failed, status.LastBackup?.Outcome);
Assert.Contains("bucket is gone", status.LastBackup!.Detail);
// With the folder chosen too, a failed upload still leaves a backup there.
Assert.Equal(HttpStatusCode.OK, (await SaveAsync(admin, s => s with { BackupToLocal = true })).StatusCode);
status = await RunAsync(admin, ApiRoutes.DatabaseBackups);
Assert.Equal(DatabaseOutcomes.Partial, status.LastBackup?.Outcome);
Assert.Single(Directory.GetFiles(BackupFolder, "executer-sqlite-*"));
Assert.Equal(key, Assert.Single(fixture.BackupStore.Objects).Key);
}
[Fact]
public async Task DownloadNeedsStepUpAndIsAuditedAndDeleteRemoves()
{
await ResetAsync();
using var admin = await fixture.SignedInAsync("db-download-admin");
await RunAsync(admin, ApiRoutes.DatabaseBackups);
var backup = Assert.Single((await ReadAsync<BackupList>(await admin.GetAsync(ApiRoutes.DatabaseBackups, Ct))).Backups);
var refused = await admin.PostAsync(Download(backup), null, Ct);
Assert.Equal(HttpStatusCode.Forbidden, refused.StatusCode);
Assert.Equal("step-up-required", await refused.ProblemAsync());
await admin.StepUpAsync();
var downloaded = await admin.PostAsync(Download(backup), null, Ct);
Assert.Equal(HttpStatusCode.OK, downloaded.StatusCode);
Assert.Equal("application/gzip", downloaded.Content.Headers.ContentType?.MediaType);
Assert.Equal(backup.Name, downloaded.Content.Headers.ContentDisposition?.FileNameStar ?? downloaded.Content.Headers.ContentDisposition?.FileName?.Trim('"'));
Assert.True(await RowsInBackupAsync(await downloaded.Content.ReadAsByteArrayAsync(Ct), "Users") > 0);
var audit = await fixture.WithDbAsync(db => db.AuditEvents.Where(e => e.Action == "database-backup-downloaded").ToListAsync(Ct));
Assert.Contains(audit, e => e.Actor == "db-download-admin" && e.Detail!.Contains(backup.Name, StringComparison.Ordinal));
Assert.Equal(HttpStatusCode.NoContent, (await admin.DeleteAsync(Delete(backup.Location, backup.Name), Ct)).StatusCode);
Assert.False(File.Exists(Path.Combine(BackupFolder, backup.Name)));
Assert.Equal(HttpStatusCode.NotFound, (await admin.DeleteAsync(Delete(backup.Location, backup.Name), Ct)).StatusCode);
Assert.Contains(await fixture.WithDbAsync(db => db.AuditEvents.Select(e => e.Action).ToListAsync(Ct)), a => a == "database-backup-deleted");
}
[Theory]
[InlineData("local", "..%2F..%2Fapp.db")]
[InlineData("local", "..%5Capp.db")]
[InlineData("local", "app.db")]
[InlineData("local", "executer-sqlite-20260101-000000-000.db.gz%2F..%2F..%2Fapp.db")]
[InlineData("local", "..")]
[InlineData("server", "executer-sqlite-20260101-000000-000.db.gz")]
[InlineData("elsewhere", "executer-sqlite-20260101-000000-000.db.gz")]
public async Task NamesOutsideTheBackupFolderAreNotFound(string location, string name)
{
await ResetAsync();
using var admin = await fixture.SignedInAsync("db-traversal-admin");
await admin.StepUpAsync();
// A real file one level up, which a traversal would reach.
Directory.CreateDirectory(BackupFolder);
var outside = Path.Combine(Path.GetDirectoryName(BackupFolder)!, "app.db");
await File.WriteAllTextAsync(outside, "not for download", Ct);
try
{
var download = await admin.PostAsync($"/api/settings/database/backups/{location}/{name}/download", null, Ct);
Assert.True(download.StatusCode is HttpStatusCode.NotFound or HttpStatusCode.BadRequest, $"{download.StatusCode}");
Assert.DoesNotContain("not for download", await download.Content.ReadAsStringAsync(Ct));
var delete = await admin.DeleteAsync($"/api/settings/database/backups/{location}/{name}", Ct);
Assert.True(delete.StatusCode is HttpStatusCode.NotFound or HttpStatusCode.BadRequest or HttpStatusCode.MethodNotAllowed, $"{delete.StatusCode}");
Assert.True(File.Exists(outside));
}
finally
{
File.Delete(outside);
}
}
[Fact]
public async Task EverythingNeedsManageSettings()
{
await ResetAsync();
using var viewer = await fixture.SignedInAsync("db-viewer", "Viewer");
Assert.Equal(HttpStatusCode.Forbidden, (await viewer.GetAsync(ApiRoutes.DatabaseStatus, Ct)).StatusCode);
Assert.Equal(HttpStatusCode.Forbidden, (await viewer.GetAsync(ApiRoutes.DatabaseInsight, Ct)).StatusCode);
Assert.Equal(HttpStatusCode.Forbidden, (await viewer.GetAsync(ApiRoutes.DatabaseSettings, Ct)).StatusCode);
Assert.Equal(HttpStatusCode.Forbidden, (await viewer.GetAsync(ApiRoutes.DatabaseBackups, Ct)).StatusCode);
Assert.Equal(HttpStatusCode.Forbidden, (await viewer.PostAsync(ApiRoutes.DatabaseBackups, null, Ct)).StatusCode);
Assert.Equal(HttpStatusCode.Forbidden, (await viewer.PostAsync(ApiRoutes.DatabaseMaintenance, null, Ct)).StatusCode);
Assert.Equal(HttpStatusCode.Forbidden,
(await viewer.PostAsync(Download(new DatabaseBackupDto("executer-sqlite-20260101-000000-000.db.gz", "local", null, DateTimeOffset.UtcNow, true, true, null)), null, Ct)).StatusCode);
Assert.Equal(HttpStatusCode.Forbidden, (await viewer.DeleteAsync(Delete("local", "executer-sqlite-20260101-000000-000.db.gz"), Ct)).StatusCode);
using var anonymous = fixture.CreateClient();
Assert.Equal(HttpStatusCode.Unauthorized, (await anonymous.GetAsync(ApiRoutes.DatabaseStatus, Ct)).StatusCode);
}
[Fact]
public async Task SettingsSaveWithTheirVersionAndAreValidated()
{
await ResetAsync();
using var admin = await fixture.SignedInAsync("db-settings-admin");
var loaded = await admin.GetAsync(ApiRoutes.DatabaseSettings, Ct);
var defaults = await ReadAsync<DatabaseSettingsDto>(loaded);
Assert.False(defaults.BackupEnabled);
Assert.False(defaults.MaintenanceEnabled);
Assert.Equal("UTC", defaults.BackupTimeZoneId);
var noVersion = await admin.PutAsJsonAsync(ApiRoutes.DatabaseSettings, defaults with { BackupKeep = 3 }, JsonDefaults.Options, Ct);
Assert.Equal(HttpStatusCode.PreconditionRequired, noVersion.StatusCode);
Assert.Equal(HttpStatusCode.OK, (await SaveAsync(admin, s => s with { BackupKeep = 3 })).StatusCode);
using var stale = new HttpRequestMessage(HttpMethod.Put, ApiRoutes.DatabaseSettings) { Content = JsonContent.Create(defaults, options: JsonDefaults.Options) };
stale.Headers.TryAddWithoutValidation("If-Match", loaded.Headers.ETag!.Tag);
var refused = await admin.SendAsync(stale, Ct);
Assert.Equal(HttpStatusCode.PreconditionFailed, refused.StatusCode);
Assert.Equal(HttpStatusCode.BadRequest, (await SaveAsync(admin, s => s with { BackupKeep = 0 })).StatusCode);
Assert.Equal(HttpStatusCode.BadRequest, (await SaveAsync(admin, s => s with { BackupCron = "every day" })).StatusCode);
Assert.Equal(HttpStatusCode.BadRequest, (await SaveAsync(admin, s => s with { MaintenanceTimeZoneId = "Mars/Olympus" })).StatusCode);
Assert.Equal(HttpStatusCode.BadRequest,
(await SaveAsync(admin, s => s with { BackupEnabled = true, BackupToLocal = false, BackupToObjectStore = false })).StatusCode);
var saved = await ReadAsync<DatabaseSettingsDto>(await SaveAsync(admin, s => s with { BackupEnabled = true, BackupCron = "30 4 * * *" }));
Assert.True(saved.BackupEnabled);
Assert.Equal(3, saved.BackupKeep);
var status = await ReadAsync<DatabaseStatusDto>(await admin.GetAsync(ApiRoutes.DatabaseStatus, Ct));
Assert.Equal(new TimeSpan(4, 30, 0), status.NextBackupUtc!.Value.TimeOfDay);
Assert.Null(status.NextMaintenanceUtc);
Assert.Equal(BackupFolder, status.LocalBackupPath);
Assert.Equal("S3 is not configured.", status.ObjectStoreUnavailable);
Assert.Null(status.BackupUnavailable);
var audited = await fixture.WithDbAsync(db => db.AuditEvents.Where(e => e.Action == "database-settings-updated").Select(e => e.Detail).ToListAsync(Ct));
Assert.Contains(audited, d => d!.Contains("backupCron: 0 2 * * * → 30 4 * * *", StringComparison.Ordinal));
}
[Fact]
public async Task OneTaskAtATimeAcrossNodes()
{
await ResetAsync();
using var admin = await fixture.SignedInAsync("db-busy-admin");
var activity = fixture.Services.GetRequiredService<DatabaseActivityStore>();
// Another node is mid-backup and still beating.
await activity.UpdateAsync(s => s.With(
running: new DatabaseActivityDto(DatabaseOperations.Backup, DateTimeOffset.UtcNow, "elsewhere", "Compressing", 40),
owner: "other-node", heartbeat: DateTimeOffset.UtcNow), Ct);
var status = await ReadAsync<DatabaseStatusDto>(await admin.GetAsync(ApiRoutes.DatabaseStatus, Ct));
Assert.Equal("Compressing", status.Running?.Step);
Assert.Equal(HttpStatusCode.Conflict, (await admin.PostAsync(ApiRoutes.DatabaseMaintenance, null, Ct)).StatusCode);
Assert.Equal(HttpStatusCode.Conflict, (await admin.PostAsync(ApiRoutes.DatabaseBackups, null, Ct)).StatusCode);
// That node died: its claim lapses with its heartbeat.
await activity.UpdateAsync(s => s.With(heartbeat: DateTimeOffset.UtcNow - DatabaseActivityState.HeartbeatTimeout - TimeSpan.FromSeconds(1)), Ct);
Assert.Null((await ReadAsync<DatabaseStatusDto>(await admin.GetAsync(ApiRoutes.DatabaseStatus, Ct))).Running);
Assert.Equal(DatabaseOutcomes.Succeeded, (await RunAsync(admin, ApiRoutes.DatabaseMaintenance)).LastMaintenance?.Outcome);
}
[Fact]
public async Task SchedulesRunWhenDueOnceAndNotWhenOff()
{
await ResetAsync();
using var admin = await fixture.SignedInAsync("db-schedule-admin");
var scheduler = fixture.Services.GetRequiredService<DatabaseScheduler>();
var now = DateTimeOffset.UtcNow;
// Off: nothing runs however late it gets.
Assert.Empty(await scheduler.RunPassAsync(now.AddDays(2), Ct));
Assert.Equal(HttpStatusCode.OK, (await SaveAsync(admin, s => s with
{
BackupEnabled = true, BackupCron = "0 * * * *", MaintenanceEnabled = true, MaintenanceCron = "0 0 1 1 *",
})).StatusCode);
// Saving starts the schedules from now: nothing is due yet.
Assert.Empty(await scheduler.RunPassAsync(now, Ct));
// Two hours on, the hourly backup is due once (the missed runs are not replayed); the yearly maintenance is not.
var later = now.AddHours(2);
Assert.Equal([DatabaseOperations.Backup], await scheduler.RunPassAsync(later, Ct));
await Runner.Current;
Assert.Empty(await scheduler.RunPassAsync(later.AddSeconds(1), Ct));
var status = await ReadAsync<DatabaseStatusDto>(await admin.GetAsync(ApiRoutes.DatabaseStatus, Ct));
Assert.Equal(DatabaseOutcomes.Succeeded, status.LastBackup?.Outcome);
Assert.Null(status.LastBackup?.By);
Assert.Single(Directory.GetFiles(BackupFolder, "executer-sqlite-*"));
Assert.Contains(await fixture.WithDbAsync(db => db.AuditEvents.Where(e => e.Action == "database-backup-succeeded").Select(e => e.Actor).ToListAsync(Ct)),
actor => actor == "schedule");
// Turned off, it stays off.
Assert.Equal(HttpStatusCode.OK, (await SaveAsync(admin, s => s with { BackupEnabled = false, MaintenanceEnabled = false })).StatusCode);
Assert.Empty(await scheduler.RunPassAsync(later.AddDays(30), Ct));
}
}