Whisper/Database.cs

using MySqlConnector;

namespace Whisper;

// Whisper's wh_* tables in CS2-SimpleAdmin's MySQL database, over its connection string. Every
// method opens its own connection from the pool and runs off the game thread. Writes come only
// from the Writer, one at a time, in order.
public sealed class Database(string connectionString)
{
    private const string MigrationPrefix = "Whisper.Migrations.";

    public sealed record Block(ulong SteamId, string Name, DateTime At);

    private async Task<MySqlConnection> OpenAsync(CancellationToken token = default)
    {
        var connection = new MySqlConnection(connectionString);
        await connection.OpenAsync(token);
        return connection;
    }

    private static MySqlCommand Command(MySqlConnection connection, string sql, params (string Name, object? Value)[] parameters)
    {
        var command = new MySqlCommand(sql, connection);
        foreach (var (name, value) in parameters)
            command.Parameters.AddWithValue(name, value ?? DBNull.Value);
        return command;
    }

    // Applies the embedded Migrations/*.sql in name order, each once, the way SimpleAdmin's
    // Migration class does with sa_migrations. Returns the versions applied.
    public async Task<List<string>> MigrateAsync(CancellationToken token)
    {
        await using var connection = await OpenAsync(token);
        await using (var create = Command(connection, """
            CREATE TABLE IF NOT EXISTS `wh_migrations` (
                `id` int NOT NULL AUTO_INCREMENT,
                `version` varchar(128) NOT NULL,
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
            """))
            await create.ExecuteNonQueryAsync(token);

        string last;
        await using (var query = Command(connection, "SELECT version FROM wh_migrations ORDER BY id DESC LIMIT 1"))
            last = (await query.ExecuteScalarAsync(token))?.ToString() ?? "";

        var assembly = typeof(Database).Assembly;
        var applied = new List<string>();
        foreach (var resource in assembly.GetManifestResourceNames()
                     .Where(n => n.StartsWith(MigrationPrefix) && n.EndsWith(".sql"))
                     .Order(StringComparer.Ordinal))
        {
            var version = resource[MigrationPrefix.Length..^".sql".Length];
            if (string.CompareOrdinal(version, last) <= 0)
                continue;

            using var reader = new StreamReader(assembly.GetManifestResourceStream(resource)!);
            await using (var migrate = Command(connection, await reader.ReadToEndAsync(token)))
                await migrate.ExecuteNonQueryAsync(token);
            await using (var record = Command(connection, "INSERT INTO wh_migrations (version) VALUES (@version)", ("@version", version)))
                await record.ExecuteNonQueryAsync(token);
            applied.Add(version);
        }
        return applied;
    }

    public async Task ApplyAsync(Op op, CancellationToken token)
    {
        await using var connection = await OpenAsync(token);
        switch (op)
        {
            case MessageRecord o:
                var r = o.Recipient;
                await Exec(connection, token, """
                    INSERT IGNORE INTO wh_messages
                        (message_key, sent_at, server_id, map, sender_steamid64, sender_name, sender_team, sender_alive,
                         recipient_steamid64, recipient_name, recipient_team, recipient_alive, target, message, result)
                    VALUES (@key, @at, @server, @map, @sid, @sname, @steam, @salive, @rid, @rname, @rteam, @ralive, @target, @message, @result)
                    """, ("@key", o.Key), ("@at", o.At), ("@server", o.ServerId), ("@map", Clip(o.Map, 64)),
                    ("@sid", o.Sender.SteamId), ("@sname", Clip(o.Sender.Name, 128)), ("@steam", o.Sender.Team), ("@salive", o.Sender.Alive),
                    ("@rid", r?.SteamId), ("@rname", Clip(r?.Name, 128)), ("@rteam", r?.Team), ("@ralive", r?.Alive),
                    ("@target", Clip(o.Target, 128)), ("@message", Clip(o.Message, 512)), ("@result", o.Result));
                break;

            case BlockAdd o:
                await Exec(connection, token, """
                    INSERT INTO wh_blocks (blocker_steamid64, blocked_steamid64, blocked_name, created_at)
                    VALUES (@blocker, @blocked, @name, @at)
                    ON DUPLICATE KEY UPDATE blocked_name = IF(VALUES(blocked_name) = '', blocked_name, VALUES(blocked_name))
                    """, ("@blocker", o.Blocker), ("@blocked", o.Blocked), ("@name", Clip(o.BlockedName, 128)), ("@at", o.At));
                break;

            case BlockRemove o:
                await Exec(connection, token, "DELETE FROM wh_blocks WHERE blocker_steamid64 = @blocker AND blocked_steamid64 = @blocked",
                    ("@blocker", o.Blocker), ("@blocked", o.Blocked));
                break;

            default:
                throw new ArgumentException($"Unknown op {op.GetType().Name}");
        }
    }

    // One player's blocks, oldest first: the order /blocklist numbers them in.
    public async Task<List<Block>> BlocksAsync(ulong blocker)
    {
        await using var connection = await OpenAsync();
        await using var query = Command(connection, """
            SELECT blocked_steamid64, blocked_name, created_at FROM wh_blocks
            WHERE blocker_steamid64 = @blocker ORDER BY created_at, blocked_steamid64
            """, ("@blocker", blocker));
        await using var reader = await query.ExecuteReaderAsync();
        var blocks = new List<Block>();
        while (await reader.ReadAsync())
            blocks.Add(new Block(reader.GetUInt64(0), reader.GetString(1), DateTime.SpecifyKind(reader.GetDateTime(2), DateTimeKind.Utc)));
        return blocks;
    }

    private static async Task Exec(MySqlConnection connection, CancellationToken token, string sql, params (string Name, object? Value)[] parameters)
    {
        await using var command = Command(connection, sql, parameters);
        await command.ExecuteNonQueryAsync(token);
    }

    private static string? Clip(string? value, int length) =>
        value == null || value.Length <= length ? value : value[..length];
}