74399e7fc6
Settings › Database backs up Postgres: a pg_dump custom-format archive (executer-postgres-<stamp>.dump), checked with pg_restore --list, to the server folder and/or the S3 store with the same retention as SQLite (pruning only this provider's backups). pg_dump comes from PG_DUMP_PATH or PATH; without it the console says so, as before. The backend image installs postgresql-client-18 from apt.postgresql.org and sets PG_DUMP_PATH. migrate-database copies a SQLite database into the configured Postgres one through EF (both at the same migration), in foreign-key order, in one transaction, keeping keys, identity values (sequences moved past them), concurrency stamps and values equal to a column default, and checks every table's row count on both sides before committing. --overwrite empties a target a server already started on; a non-empty -wal is refused without --force. Tests run the server on a throwaway Postgres database (insight, VACUUM (ANALYZE), backups restored with pg_restore, retention) and the copy end to end; both skip without PG* variables. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
103 lines
3.6 KiB
C#
103 lines
3.6 KiB
C#
using Npgsql;
|
|
|
|
namespace Backend.Tests;
|
|
|
|
/// <summary>
|
|
/// A throwaway database on the PostgreSQL server the PG* variables name (PGHOST, PGPORT, PGUSER, PGPASSWORD), created for a
|
|
/// test and dropped after it. <see cref="TryCreate"/> is null when no server is configured; the tests then skip.
|
|
/// </summary>
|
|
public sealed class PostgresTestDatabase : IDisposable
|
|
{
|
|
private readonly string admin;
|
|
|
|
private PostgresTestDatabase(string admin, string name)
|
|
{
|
|
this.admin = admin;
|
|
Name = name;
|
|
ConnectionString = new NpgsqlConnectionStringBuilder(admin) { Database = name, Pooling = false }.ConnectionString;
|
|
Execute(admin, $"CREATE DATABASE \"{name}\"");
|
|
}
|
|
|
|
public const string SkipReason = "No PostgreSQL to test against (PGHOST, PGUSER, PGPASSWORD).";
|
|
|
|
public string Name { get; }
|
|
|
|
/// <summary>The database's connection string, password included.</summary>
|
|
public string ConnectionString { get; }
|
|
|
|
/// <summary>The server's connection, for the server's own database.</summary>
|
|
public static string? AdminConnectionString()
|
|
{
|
|
var host = Environment.GetEnvironmentVariable("PGHOST");
|
|
var user = Environment.GetEnvironmentVariable("PGUSER");
|
|
var password = Environment.GetEnvironmentVariable("PGPASSWORD");
|
|
if (string.IsNullOrEmpty(host) || string.IsNullOrEmpty(user) || password is null)
|
|
{
|
|
return null;
|
|
}
|
|
|
|
return new NpgsqlConnectionStringBuilder
|
|
{
|
|
Host = host,
|
|
Port = int.TryParse(Environment.GetEnvironmentVariable("PGPORT"), out var port) ? port : 5432,
|
|
Username = user,
|
|
Password = password,
|
|
Database = "postgres",
|
|
Pooling = false,
|
|
}.ConnectionString;
|
|
}
|
|
|
|
public static PostgresTestDatabase? TryCreate(string prefix)
|
|
=> AdminConnectionString() is { } admin ? new PostgresTestDatabase(admin, $"{prefix}_{Guid.NewGuid():N}"[..40]) : null;
|
|
|
|
/// <summary>Another throwaway database on the same server (a restore target).</summary>
|
|
public PostgresTestDatabase Sibling(string prefix) => new(admin, $"{prefix}_{Guid.NewGuid():N}"[..40]);
|
|
|
|
public long Scalar(string sql)
|
|
{
|
|
using var connection = new NpgsqlConnection(ConnectionString);
|
|
connection.Open();
|
|
using var command = new NpgsqlCommand(sql, connection);
|
|
return Convert.ToInt64(command.ExecuteScalar(), System.Globalization.CultureInfo.InvariantCulture);
|
|
}
|
|
|
|
private static void Execute(string connectionString, string sql)
|
|
{
|
|
using var connection = new NpgsqlConnection(connectionString);
|
|
connection.Open();
|
|
using var command = new NpgsqlCommand(sql, connection);
|
|
command.ExecuteNonQuery();
|
|
}
|
|
|
|
public void Dispose()
|
|
{
|
|
NpgsqlConnection.ClearAllPools();
|
|
Execute(admin, $"DROP DATABASE IF EXISTS \"{Name}\" WITH (FORCE)");
|
|
}
|
|
}
|
|
|
|
/// <summary>The backend on a throwaway Postgres database (SQLite when no server is configured: the tests skip then).</summary>
|
|
public sealed class PostgresBackendFixture : BackendTestFixture
|
|
{
|
|
public PostgresBackendFixture()
|
|
{
|
|
Database = PostgresTestDatabase.TryCreate("executer_backend_tests");
|
|
if (Database is not null)
|
|
{
|
|
Settings["DATABASE_PROVIDER"] = "postgres";
|
|
Settings["DATABASE_CONNECTION_STRING"] = Database.ConnectionString;
|
|
}
|
|
}
|
|
|
|
public PostgresTestDatabase? Database { get; }
|
|
|
|
protected override void Dispose(bool disposing)
|
|
{
|
|
base.Dispose(disposing);
|
|
if (disposing)
|
|
{
|
|
Database?.Dispose();
|
|
}
|
|
}
|
|
}
|