82 lines
3.6 KiB
C#
82 lines
3.6 KiB
C#
#if WITH_SQLITE
|
|
using System.Data.Common;
|
|
using Dapper;
|
|
using Microsoft.Data.Sqlite;
|
|
|
|
namespace Outnumbered.Data;
|
|
|
|
// SQLite backend — the DEV store (Provider="sqlite"). Shares all queries with DapperPlayerRepository; supplies only the
|
|
// connection (WAL + busy_timeout so periodic flush / disconnect saves don't trip over each other) and the schema DDL.
|
|
public sealed class SqliteRepository : DapperPlayerRepository
|
|
{
|
|
private readonly string _connectionString;
|
|
|
|
public SqliteRepository(string dbFilePath, string serverId) : base(serverId) =>
|
|
_connectionString = new SqliteConnectionStringBuilder { DataSource = dbFilePath }.ToString();
|
|
|
|
protected override async Task<DbConnection> OpenConnectionAsync()
|
|
{
|
|
var c = new SqliteConnection(_connectionString);
|
|
await c.OpenAsync();
|
|
await c.ExecuteAsync("PRAGMA journal_mode=WAL; PRAGMA busy_timeout=5000;");
|
|
return c;
|
|
}
|
|
|
|
public override async Task EnsureSchemaAsync()
|
|
{
|
|
await using var c = await OpenConnectionAsync();
|
|
// Permanent progression — GLOBAL across all servers (shared leaderboard).
|
|
await c.ExecuteAsync(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS players(
|
|
steamid INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL DEFAULT '',
|
|
xp INTEGER NOT NULL DEFAULT 0,
|
|
level INTEGER NOT NULL DEFAULT 1,
|
|
prestige INTEGER NOT NULL DEFAULT 0,
|
|
points INTEGER NOT NULL DEFAULT 1,
|
|
primary_weapon TEXT,
|
|
secondary_weapon TEXT,
|
|
best_wave INTEGER,
|
|
gg_best_ms INTEGER,
|
|
last_seen TEXT);
|
|
CREATE TABLE IF NOT EXISTS upgrades(
|
|
steamid INTEGER NOT NULL,
|
|
stat_key TEXT NOT NULL,
|
|
level INTEGER NOT NULL,
|
|
PRIMARY KEY(steamid, stat_key));
|
|
""");
|
|
|
|
// match_state is PER-SERVER (composite key) + ephemeral; a one-time drop migrates off the old single-PK schema.
|
|
bool matchExists = await c.ExecuteScalarAsync<long>(
|
|
"SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='match_state';") > 0;
|
|
bool hasServerId = matchExists && await c.ExecuteScalarAsync<long>(
|
|
"SELECT COUNT(*) FROM pragma_table_info('match_state') WHERE name='server_id';") > 0;
|
|
if (matchExists && !hasServerId) await c.ExecuteAsync("DROP TABLE match_state;");
|
|
await c.ExecuteAsync(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS match_state(
|
|
server_id TEXT NOT NULL DEFAULT 'default',
|
|
steamid INTEGER NOT NULL,
|
|
kills INTEGER NOT NULL DEFAULT 0,
|
|
deaths INTEGER NOT NULL DEFAULT 0,
|
|
streak INTEGER NOT NULL DEFAULT 0,
|
|
headshot_kills INTEGER NOT NULL DEFAULT 0,
|
|
gg_run_started_at INTEGER,
|
|
PRIMARY KEY(server_id, steamid));
|
|
""");
|
|
|
|
await TryAddColumnAsync(c, "players", "primary_weapon", "TEXT");
|
|
await TryAddColumnAsync(c, "players", "secondary_weapon", "TEXT");
|
|
await TryAddColumnAsync(c, "players", "best_wave", "INTEGER");
|
|
await TryAddColumnAsync(c, "players", "gg_best_ms", "INTEGER");
|
|
await TryAddColumnAsync(c, "match_state", "gg_run_started_at", "INTEGER");
|
|
}
|
|
|
|
private static async Task TryAddColumnAsync(DbConnection c, string table, string col, string type)
|
|
{
|
|
try { await c.ExecuteAsync($"ALTER TABLE {table} ADD COLUMN {col} {type};"); }
|
|
catch (SqliteException) { /* column already exists — fine */ }
|
|
}
|
|
}
|
|
#endif
|