using System.Net;
using System.Net.Http.Json;
using Backend.Maintenance;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Shared.Contracts.Api;
using Shared.Contracts.Serialization;
using Xunit;
namespace Backend.Tests;
///
/// The server on a real Postgres (PG* variables; skipped without): migrations and seeding, Settings › Database insight and
/// VACUUM (ANALYZE), and pg_dump backups (when pg_dump is on PATH): a custom-format archive that pg_restore reads back into
/// another database, both destinations, and retention that leaves another provider's backups alone.
///
public sealed class PostgresDatabaseTests(PostgresBackendFixture fixture) : IClassFixture
{
private sealed record BackupList(List Backups, string? ObjectStoreError);
private static CancellationToken Ct => TestContext.Current.CancellationToken;
private DatabaseTaskRunner Runner => fixture.Services.GetRequiredService();
private string BackupFolder => fixture.Services.GetRequiredService().Folder;
private PostgresTestDatabase Database
{
get
{
if (fixture.Database is null)
{
Assert.Skip(PostgresTestDatabase.SkipReason);
}
return fixture.Database!;
}
}
private PostgresTools Tools => fixture.Services.GetRequiredService();
private static async Task ReadAsync(HttpResponseMessage response)
{
Assert.True(response.IsSuccessStatusCode, $"{(int)response.StatusCode}: {await response.Content.ReadAsStringAsync()}");
return (await response.Content.ReadFromJsonAsync(JsonDefaults.Options, Ct))!;
}
private static async Task SaveAsync(HttpClient client, Func change)
{
var current = await client.GetAsync(ApiRoutes.DatabaseSettings, Ct);
var settings = await ReadAsync(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);
}
private async Task RunAsync(HttpClient client, string route)
{
var started = await client.PostAsync(route, null, Ct);
Assert.True(started.StatusCode == HttpStatusCode.Accepted, await started.Content.ReadAsStringAsync(Ct));
await Runner.Current;
return await ReadAsync(await client.GetAsync(ApiRoutes.DatabaseStatus, Ct));
}
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().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;
}
[Fact]
public async Task TheServerRunsOnPostgresWithInsightAndMaintenance()
{
_ = Database;
await ResetAsync();
using var admin = await fixture.SignedInAsync("pg-insight-admin");
var status = await ReadAsync(await admin.GetAsync(ApiRoutes.DatabaseStatus, Ct));
Assert.Equal("postgres", status.Provider);
Assert.Equal(Tools.Unavailable, status.BackupUnavailable);
Assert.Equal("S3 is not configured.", status.ObjectStoreUnavailable);
var insight = await ReadAsync(await admin.GetAsync(ApiRoutes.DatabaseInsight, Ct));
Assert.Equal("postgres", insight.Provider);
Assert.False(string.IsNullOrWhiteSpace(insight.Version));
Assert.True(insight.SizeBytes > 0);
Assert.True(insight.RowsApproximate);
Assert.Contains(insight.Tables, t => t.Name == "Users");
Assert.Contains(insight.Tables, t => t.Name == "__EFMigrationsHistory");
status = await RunAsync(admin, ApiRoutes.DatabaseMaintenance);
Assert.Equal(DatabaseOutcomes.Succeeded, status.LastMaintenance?.Outcome);
Assert.Contains("VACUUM", status.MaintenanceSummary);
// VACUUM (ANALYZE) refreshed the statistics the estimates come from.
insight = await ReadAsync(await admin.GetAsync(ApiRoutes.DatabaseInsight, Ct));
Assert.True(insight.Tables.Single(t => t.Name == "Users").Rows > 0);
Assert.NotNull(insight.DeadRows);
}
[Fact]
public async Task ABackupIsACustomFormatDumpThatRestoresIntoAnotherDatabase()
{
var database = Database;
if (Tools.PgDump is null)
{
Assert.Skip("pg_dump is not on PATH.");
}
await ResetAsync();
using var admin = await fixture.SignedInAsync("pg-backup-admin");
fixture.BackupStore.Unavailable = null;
Assert.Equal(HttpStatusCode.OK, (await SaveAsync(admin, s => s with { BackupToLocal = true, BackupToObjectStore = true })).StatusCode);
var status = await RunAsync(admin, ApiRoutes.DatabaseBackups);
Assert.True(status.LastBackup?.Outcome == DatabaseOutcomes.Succeeded, status.LastBackup?.Detail);
Assert.Contains("to the server folder and the S3 store", status.LastBackup!.Detail);
var file = Assert.Single(Directory.GetFiles(BackupFolder));
Assert.Matches(@"executer-postgres-\d{8}-\d{6}-\d{3}\.dump$", file);
Assert.Equal("PGDMP"u8.ToArray(), File.ReadAllBytes(file)[..5]);
var (key, stored) = Assert.Single(fixture.BackupStore.Objects);
Assert.Equal(DatabaseBackups.ObjectPrefix + Path.GetFileName(file), key);
Assert.Equal(await File.ReadAllBytesAsync(file, Ct), stored.Content);
var listed = (await ReadAsync(await admin.GetAsync(ApiRoutes.DatabaseBackups, Ct))).Backups;
Assert.Equal(2, listed.Count);
Assert.All(listed, b => Assert.Equal(Path.GetFileName(file), b.Name));
await admin.StepUpAsync();
var local = listed.Single(b => b.Location == DatabaseBackupLocations.Local);
var downloaded = await admin.PostAsync(ApiRoutes.DatabaseBackupDownload
.Replace("{location}", local.Location, StringComparison.Ordinal).Replace("{name}", local.Name, StringComparison.Ordinal), null, Ct);
Assert.Equal(HttpStatusCode.OK, downloaded.StatusCode);
Assert.Equal("application/octet-stream", downloaded.Content.Headers.ContentType?.MediaType);
// pg_restore puts it into an empty database: the same rows come back.
using var restored = database.Sibling("executer_restore_tests");
await PostgresTools.RunAsync(Tools.PgRestore!, ["--no-owner", "--exit-on-error", $"--dbname={restored.Name}", file],
PostgresTools.ConnectionEnvironment(restored.ConnectionString), null, Ct);
var users = await fixture.WithDbAsync(db => db.Users.LongCountAsync(Ct));
Assert.Equal(users, restored.Scalar("SELECT count(*) FROM \"Users\""));
Assert.Equal(database.Scalar("SELECT count(*) FROM \"__EFMigrationsHistory\""), restored.Scalar("SELECT count(*) FROM \"__EFMigrationsHistory\""));
}
[Fact]
public async Task RetentionKeepsThisProvidersNewestAndLeavesOtherBackupsAlone()
{
_ = Database;
if (Tools.PgDump is null)
{
Assert.Skip("pg_dump is not on PATH.");
}
await ResetAsync();
using var admin = await fixture.SignedInAsync("pg-retention-admin");
Assert.Equal(HttpStatusCode.OK, (await SaveAsync(admin, s => s with { BackupKeep = 2, BackupToLocal = true })).StatusCode);
// A SQLite backup from before the move to Postgres.
Directory.CreateDirectory(BackupFolder);
var before = Path.Combine(BackupFolder, "executer-sqlite-20260101-000000-000.db.gz");
await File.WriteAllBytesAsync(before, [0x1f, 0x8b], Ct);
for (var i = 0; i < 3; i++)
{
Assert.Equal(DatabaseOutcomes.Succeeded, (await RunAsync(admin, ApiRoutes.DatabaseBackups)).LastBackup?.Outcome);
await Task.Delay(5, Ct);
}
Assert.Equal(2, Directory.GetFiles(BackupFolder, "executer-postgres-*.dump").Length);
Assert.True(File.Exists(before));
var listed = (await ReadAsync(await admin.GetAsync(ApiRoutes.DatabaseBackups, Ct))).Backups;
Assert.Equal(3, listed.Count);
Assert.Equal("executer-sqlite-20260101-000000-000.db.gz", listed[^1].Name);
}
[Fact]
public async Task BackupsNeedADestinationAndAreRefusedWithoutPgDump()
{
_ = Database;
await ResetAsync();
using var admin = await fixture.SignedInAsync("pg-refusal-admin");
Assert.Equal(HttpStatusCode.BadRequest,
(await SaveAsync(admin, s => s with { BackupEnabled = true, BackupToLocal = false, BackupToObjectStore = false })).StatusCode);
// Without pg_dump the server says so, as it did before the client tools were bundled.
var missing = new PostgresTools(new ConfigurationBuilder()
.AddInMemoryCollection(new Dictionary { ["PG_DUMP_PATH"] = Path.Combine(Path.GetTempPath(), "no-such-pg_dump") })
.Build());
Assert.Null(missing.PgDump);
Assert.Contains("PG_DUMP_PATH", DatabaseBackups.Unavailable(Backend.Data.DatabaseProvider.Postgres, missing));
Assert.Null(DatabaseBackups.Unavailable(Backend.Data.DatabaseProvider.Sqlite, missing));
Assert.Contains("PG_DUMP_PATH", DatabaseSettingsStore.Validate(
DatabaseSettingsStore.Defaults with { BackupEnabled = true }, Backend.Data.DatabaseProvider.Postgres, missing.Unavailable)["backupEnabled"][0]);
}
[Fact]
public void TheToolsGetTheServersConnectionAndNothingElse()
{
var environment = PostgresTools.ConnectionEnvironment("Host=db;Port=6543;Database=executer;Username=executer;Password=p;SSL Mode=Require;Timeout=7");
Assert.Equal("db", environment["PGHOST"]);
Assert.Equal("6543", environment["PGPORT"]);
Assert.Equal("executer", environment["PGDATABASE"]);
Assert.Equal("executer", environment["PGUSER"]);
Assert.Equal("p", environment["PGPASSWORD"]);
Assert.Equal("require", environment["PGSSLMODE"]);
Assert.Equal("7", environment["PGCONNECT_TIMEOUT"]);
Assert.Equal("prefer", PostgresTools.ConnectionEnvironment("Host=postgres;Username=u")["PGSSLMODE"]);
Assert.Equal("u", PostgresTools.ConnectionEnvironment("Host=postgres;Username=u")["PGDATABASE"]);
}
}