Files
Alby96andClaude Opus 5.5 53ca01b7c3 feat(core): platform layer, portable database, archives and access (refs #7 #8 #9 #10 #11)
Phase 1 foundations in the Anubis library:
- Platform: startup configuration from environment variables (Docker) or
  config.toml (desktop, in-house TOML subset), data folder layout, secret key
  custody (ANUBIS_SECRET_KEY, DPAPI on Windows, restricted file elsewhere),
  installation identity and database fingerprint.
- Db: ADO.NET with Npgsql and Microsoft.Data.Sqlite, money as NUMERIC(38,18)
  or canonical text rounded to 18 decimals, UTC moments, UUIDv7 ids; numbered
  SQL migrations with type tokens and a version table; engine-independent
  export/import with count and accounting-total checks; backups keeping 14.
- Core: injectable clock, channel event bus, scheduler with fixed, interval
  and market-relative times, settings with change log, AES-GCM secrets, log
  redaction, Italian formats.
- Access: PBKDF2-SHA512 hashes, RFC 6238 TOTP, hashed session tokens,
  sign-in attempt limit.

SQLitePCLRaw (Apache-2.0) arrives with Microsoft.Data.Sqlite and is declared
in DependencyPolicyTests and API-NOTES.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-11-10 11:09:42 +01:00

250 lines
13 KiB
C#

using Anubis.Core;
using Anubis.Db;
using Anubis.Platform;
namespace Anubis.Tests;
/// <summary>
/// The portable database (issue #8): every test runs on the engine of this run (ANUBIS_TEST_DB), so the same
/// battery passes on SQLite and on PostgreSQL.
/// </summary>
[TestClass]
public sealed class DatabaseTests
{
private static readonly ManualClock _clock = new(new DateTimeOffset(2026, 10, 9, 6, 21, 0, TimeSpan.Zero));
internal static async Task<TestDb> MigratedAsync()
{
var test = await TestDb.CreateAsync().ConfigureAwait(false);
await Migrations.MigrateAsync(test.Db, _clock, null, CancellationToken.None).ConfigureAwait(false);
return test;
}
[TestMethod]
[TestCategory(Category.Integration)]
public async Task MigrationsCreateTheSchemaAndRunOnce()
{
await using var test = await TestDb.CreateAsync().ConfigureAwait(false);
Assert.IsTrue((await Migrations.StatusAsync(test.Db, CancellationToken.None).ConfigureAwait(false)).IsEmpty);
Assert.AreEqual(Migrations.Latest, await Migrations.MigrateAsync(test.Db, _clock, null, CancellationToken.None).ConfigureAwait(false));
Assert.AreEqual(0, await Migrations.MigrateAsync(test.Db, _clock, null, CancellationToken.None).ConfigureAwait(false));
var status = await Migrations.StatusAsync(test.Db, CancellationToken.None).ConfigureAwait(false);
Assert.AreEqual(Migrations.Latest, status.Current);
}
[TestMethod]
[TestCategory(Category.Integration)]
public async Task ColumnsInTheDatabaseMatchTheSchemaReadFromMigrations()
{
await using var test = await MigratedAsync().ConfigureAwait(false);
foreach (var table in Schema.Tables)
{
var actual = await test.Db.ReadAsync(s => s.QueryAsync(
s.Engine == DbEngine.Sqlite
? $"SELECT name, type, \"notnull\" AS required FROM pragma_table_info('{table.Name}')"
: $"SELECT column_name AS name, data_type AS type, CASE WHEN is_nullable = 'NO' THEN 1 ELSE 0 END AS required FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = '{table.Name}'",
r => (Name: r.Text("name"), Type: r.Text("type").ToUpperInvariant(), Required: r.Number("required") != 0))).ConfigureAwait(false);
CollectionAssert.AreEquivalent(table.Columns.Select(c => c.Name).ToList(), actual.Select(a => a.Name).ToList(), table.Name);
foreach (var column in table.Columns)
{
var found = actual.Single(a => a.Name == column.Name);
if (column.Kind == ColumnKind.Money)
{
Assert.IsTrue(found.Type is "TEXT" or "NUMERIC", $"{table.Name}.{column.Name} è {found.Type}: il denaro non è mai in virgola mobile");
}
if (!column.Nullable)
{
Assert.IsTrue(found.Required || column.Name == "id", $"{table.Name}.{column.Name} dovrebbe essere obbligatoria");
}
}
}
}
[TestMethod]
[TestCategory(Category.Integration)]
public async Task DecimalsDatesAndIdsRoundTripExactly()
{
await using var test = await MigratedAsync().ConfigureAwait(false);
await test.Db.WriteAsync(s => s.ExecuteAsync("INSERT INTO accounts (id, name, broker, environment, base_currency, created_at) VALUES (@id, 'Prova', 'paper', 'simulation', 'EUR', @t)",
("id", Guid.CreateVersion7(_clock.UtcNow)), ("t", _clock.UtcNow))).ConfigureAwait(false);
var account = await test.Db.ReadAsync(s => s.FirstOrDefaultAsync("SELECT id FROM accounts", r => r.Id("id"))).ConfigureAwait(false);
decimal[] values =
[
0m, 1m, -1m, 0.000000000000000001m, -0.000000000000000001m, 1299.84m, 0.1m + 0.2m,
99_999_999_999_999_999_999m, -99_999_999_999_999_999_999m, 1234567890.123456789012345678m,
12345678901.12345678901234567m, 7.000000000000000000m, 79228162514.264337593543950335m,
];
var moments = new[]
{
new DateTimeOffset(2026, 10, 9, 8, 21, 0, TimeSpan.FromHours(2)),
new DateTimeOffset(1970, 1, 1, 0, 0, 0, TimeSpan.Zero),
new DateTimeOffset(2099, 12, 31, 23, 59, 59, TimeSpan.Zero).AddTicks(1234567),
};
var rows = new List<(Guid Id, decimal Value, DateTimeOffset At)>();
for (var i = 0; i < values.Length; i++)
{
rows.Add((Guid.CreateVersion7(_clock.UtcNow.AddMilliseconds(i)), values[i], moments[i % moments.Length]));
}
await test.Db.WriteAsync(async s =>
{
foreach (var (id, value, at) in rows)
{
await s.ExecuteAsync("INSERT INTO cash_ledger (id, account_id, currency, amount, kind, occurred_at, created_at) VALUES (@id, @a, 'EUR', @v, 'test', @at, @t)",
("id", id), ("a", account), ("v", value), ("at", at), ("t", _clock.UtcNow)).ConfigureAwait(false);
}
}).ConfigureAwait(false);
var read = await test.Db.ReadAsync(s => s.QueryAsync("SELECT id, amount, occurred_at FROM cash_ledger ORDER BY id",
r => (Id: r.Id("id"), Value: r.Money("amount"), At: r.Moment("occurred_at")))).ConfigureAwait(false);
Assert.HasCount(rows.Count, read);
for (var i = 0; i < rows.Count; i++)
{
Assert.AreEqual(rows[i].Id, read[i].Id);
Assert.AreEqual(DbValues.Money(rows[i].Value), read[i].Value, $"valore {rows[i].Value}");
Assert.AreEqual(DbValues.Moment(rows[i].At), read[i].At);
Assert.AreEqual(TimeSpan.Zero, read[i].At.Offset, "si rilegge sempre in UTC");
}
// More than 18 decimals: rounded half away from zero, the same on both engines.
Assert.AreEqual(0.000000000000000002m, DbValues.Money(0.0000000000000000015m));
Assert.AreEqual(1.123456789012345679m, DbValues.Money(1.1234567890123456789m));
}
[TestMethod]
[TestCategory(Category.Unit)]
public void ValuesOutsideNumeric38And18AndMomentsWithoutZoneAreRefused()
{
Assert.Throws<OverflowException>(() => DbValues.Money(100_000_000_000_000_000_000m));
Assert.Throws<OverflowException>(() => DbValues.Money(decimal.MaxValue));
Assert.Throws<ArgumentException>(() => DbValues.ToProvider(new DateTime(2026, 10, 9), DbEngine.Sqlite));
Assert.Throws<FormatException>(() => DbValues.ParseMoment("2026-10-09T08:21:00"));
Assert.AreEqual("1.5", DbValues.MoneyText(1.500m));
Assert.AreEqual("0", DbValues.MoneyText(0.000m));
}
[TestMethod]
[TestCategory(Category.Unit)]
public void MoneyRoundTripsForAnyValue() =>
Property.ForAll(g => g.Decimal(-1_000_000m, 1_000_000m, 12), value =>
{
var text = DbValues.MoneyText(value);
Assert.AreEqual(DbValues.Money(value), DbValues.ParseMoney(text));
});
[TestMethod]
[TestCategory(Category.Integration)]
public async Task SameExecutionIsRecordedOnce()
{
await using var test = await MigratedAsync().ConfigureAwait(false);
var (account, instrument) = await SeedAccountAsync(test.Db).ConfigureAwait(false);
async Task InsertFill() => await test.Db.WriteAsync(s => s.ExecuteAsync(
"INSERT INTO fills (id, account_id, instrument_id, execution_id, side, quantity, price, fee, fee_currency, executed_at, created_at) VALUES (@id, @a, @i, 'EX-1', 'buy', 1, 100, 0, 'EUR', @t, @t)",
("id", Guid.NewGuid()), ("a", account), ("i", instrument), ("t", _clock.UtcNow))).ConfigureAwait(false);
await InsertFill().ConfigureAwait(false);
await Assert.ThrowsAsync<System.Data.Common.DbException>(InsertFill).ConfigureAwait(false);
}
[TestMethod]
[TestCategory(Category.Integration)]
public async Task FailedWritesLeaveNothingBehind()
{
await using var test = await MigratedAsync().ConfigureAwait(false);
await Assert.ThrowsAsync<InvalidOperationException>(() => test.Db.WriteAsync(async s =>
{
await s.ExecuteAsync("INSERT INTO accounts (id, name, broker, environment, base_currency, created_at) VALUES (@id, 'X', 'paper', 'simulation', 'EUR', @t)",
("id", Guid.NewGuid()), ("t", _clock.UtcNow)).ConfigureAwait(false);
throw new InvalidOperationException("stop");
})).ConfigureAwait(false);
Assert.AreEqual(0L, await test.Db.ReadAsync(s => s.CountAsync("SELECT COUNT(*) FROM accounts")).ConfigureAwait(false));
}
[TestMethod]
[TestCategory(Category.Integration)]
public async Task SqliteUsesWalFullSyncAndForeignKeys()
{
if (TestDatabase.Engine != "sqlite")
{
Assert.Inconclusive("Solo su SQLite.");
}
await using var test = await MigratedAsync().ConfigureAwait(false);
var pragmas = await test.Db.ReadAsync(async s => (
Journal: await s.FirstOrDefaultAsync("PRAGMA journal_mode", r => r.Text("journal_mode")).ConfigureAwait(false),
Sync: await s.CountAsync("PRAGMA synchronous").ConfigureAwait(false),
Keys: await s.CountAsync("PRAGMA foreign_keys").ConfigureAwait(false),
Busy: await s.CountAsync("PRAGMA busy_timeout").ConfigureAwait(false))).ConfigureAwait(false);
Assert.AreEqual("wal", pragmas.Journal);
Assert.AreEqual(2L, pragmas.Sync, "synchronous=FULL");
Assert.AreEqual(1L, pragmas.Keys);
Assert.AreEqual(5000L, pragmas.Busy);
}
[TestMethod]
[TestCategory(Category.Unit)]
public void SqliteDoesNotCreateAMissingFileWhenAskedNotTo()
{
var folder = TempFolder.Create("nocreate");
try
{
var path = Path.Combine(folder, "db", "anubis.sqlite3");
var db = Database.Open(new DbSettings { Engine = DbEngine.Sqlite, SqlitePath = path }, allowCreate: false);
Assert.ThrowsAsync<Microsoft.Data.Sqlite.SqliteException>(() => db.PingAsync(CancellationToken.None)).GetAwaiter().GetResult();
Assert.IsFalse(File.Exists(path));
}
finally
{
TempFolder.Delete(folder);
}
}
[TestMethod]
[TestCategory(Category.Unit)]
public void MigrationsUseOnlyPortableSql()
{
string[] forbidden = ["SERIAL", "AUTOINCREMENT", "JSONB", "MATERIALIZED", "PARTITION", "LISTEN", "NOTIFY", "REAL", "FLOAT", "DOUBLE", "pg_advisory", "RETURNING"];
foreach (var migration in Migrations.All)
{
var sql = System.Text.RegularExpressions.Regex.Replace(migration.Sql, @"--[^\n]*|\{[a-z]+\}", "");
foreach (var word in forbidden)
{
Assert.IsFalse(System.Text.RegularExpressions.Regex.IsMatch(sql, $@"\b{word}\b", System.Text.RegularExpressions.RegexOptions.IgnoreCase),
$"{migration.Version:D4}: '{word}' non è portabile");
}
}
Assert.IsTrue(Schema.Tables.All(t => t.Columns.Any(c => c.Name == "id" && c.Kind == ColumnKind.Uuid)), "ogni tabella ha una chiave UUID");
Assert.IsTrue(Schema.Tables.All(t => t.Columns.Any(c => c.Name == "created_at" && c.Kind == ColumnKind.Moment)), "ogni tabella ha created_at");
}
[TestMethod]
[TestCategory(Category.Unit)]
public void AccountingTablesAreNeverUpdatedOrDeletedInCode()
{
var pattern = new System.Text.RegularExpressions.Regex(@"\b(UPDATE\s+(fills|lots|cash_ledger)\b|DELETE\s+FROM\s+(fills|lots|cash_ledger)\b)", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
var offenders = Repo.Files()
.Where(f => f.Extension is ".cs" or ".sql" && Repo.Relative(f).StartsWith("src/", StringComparison.Ordinal))
.Where(f => pattern.IsMatch(File.ReadAllText(f.FullName)))
.Select(Repo.Relative)
.ToList();
Assert.IsEmpty(offenders, "Le tabelle contabili si correggono con righe di storno: " + string.Join(", ", offenders));
}
internal static async Task<(Guid Account, Guid Instrument)> SeedAccountAsync(Database db)
{
var account = Guid.CreateVersion7(_clock.UtcNow);
var instrument = Guid.CreateVersion7(_clock.UtcNow.AddMilliseconds(1));
await db.WriteAsync(async s =>
{
await s.ExecuteAsync("INSERT INTO accounts (id, name, broker, environment, base_currency, created_at) VALUES (@id, 'OKX Europe', 'okx', 'simulation', 'EUR', @t)",
("id", account), ("t", _clock.UtcNow)).ConfigureAwait(false);
await s.ExecuteAsync("INSERT INTO instruments (id, venue, symbol, name, asset_class, quote_currency, created_at) VALUES (@id, 'okx', 'BTC/EUR', 'Bitcoin', 'crypto', 'EUR', @t)",
("id", instrument), ("t", _clock.UtcNow)).ConfigureAwait(false);
}).ConfigureAwait(false);
return (account, instrument);
}
}