91 lines
3.7 KiB
C#
91 lines
3.7 KiB
C#
using Microsoft.Data.Sqlite;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Reflection;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace Elwig.Helpers {
|
|
public static class AppDbUpdater {
|
|
|
|
// Don't forget to update value in Tests/fetch-resources.bat!
|
|
public static readonly int RequiredSchemaVersion = 33;
|
|
|
|
private static int VersionOffset = 0;
|
|
|
|
public static async Task<Version> CheckDb() {
|
|
using var cnx = AppDbContext.Connect();
|
|
|
|
var applId = (long?)await AppDbContext.ExecuteScalar(cnx, "PRAGMA application_id") ?? 0;
|
|
if (applId != 0x454C5747) throw new Exception($"Invalid application_id in database (0x{applId:X08})");
|
|
|
|
var schemaVers = (long?)await AppDbContext.ExecuteScalar(cnx, "PRAGMA schema_version") ?? 0;
|
|
VersionOffset = (int)(schemaVers % 100);
|
|
if (VersionOffset != 0) {
|
|
// schema was modified manually/externally
|
|
// TODO issue warning
|
|
}
|
|
await UpdateDbSchema(cnx, (int)(schemaVers / 100), RequiredSchemaVersion);
|
|
|
|
var userVers = (long?)await AppDbContext.ExecuteScalar(cnx, "PRAGMA user_version") ?? 0;
|
|
var v = new Version((int)(userVers >> 24), (int)((userVers >> 16) & 0xFF), (int)((userVers >> 8) & 0xFF), (int)(userVers & 0xFF));
|
|
|
|
if (App.Version > v) {
|
|
long vers = (App.Version.Major << 24) | (App.Version.Minor << 16) | (App.Version.Build << 8) | App.Version.Revision;
|
|
await AppDbContext.ExecuteBatch(cnx, $"PRAGMA user_version = {vers}");
|
|
}
|
|
|
|
return v;
|
|
}
|
|
|
|
private static async Task UpdateDbSchema(SqliteConnection cnx, int fromVersion, int toVersion) {
|
|
if (fromVersion == toVersion) {
|
|
return;
|
|
} else if (fromVersion > toVersion) {
|
|
throw new Exception("schema_version of database is too new");
|
|
} else if (fromVersion <= 0) {
|
|
throw new Exception("schema_version of database is invalid");
|
|
}
|
|
|
|
var asm = Assembly.GetExecutingAssembly();
|
|
(int From, int To, string Name)[] scripts = asm.GetManifestResourceNames()
|
|
.Where(n => n.StartsWith("Elwig.Resources.Sql."))
|
|
.Select(n => {
|
|
var p = n.Split(".")[^2].Split("-");
|
|
return (int.Parse(p[0]), int.Parse(p[1]), n);
|
|
})
|
|
.OrderBy(s => s.Item1).ThenBy(s => s.Item2)
|
|
.ToArray();
|
|
|
|
List<string> toExecute = [];
|
|
var vers = fromVersion;
|
|
while (vers < toVersion) {
|
|
var (_, to, name) = scripts.Where(s => s.From == vers).Last();
|
|
toExecute.Add(name);
|
|
vers = to;
|
|
}
|
|
if (toExecute.Count == 0)
|
|
return;
|
|
|
|
await AppDbContext.ExecuteBatch(cnx, """
|
|
PRAGMA locking_mode = EXCLUSIVE;
|
|
BEGIN EXCLUSIVE;
|
|
""");
|
|
foreach (var script in toExecute) {
|
|
await AppDbContext.ExecuteEmbeddedScript(cnx, asm, script);
|
|
}
|
|
var violations = await AppDbContext.ForeignKeyCheck(cnx);
|
|
if (violations.Length > 0) {
|
|
throw new Exception($"Foreign key violations ({violations.Length}):\n" + string.Join("\n", violations
|
|
.Select(v => $"{v.Table} - {v.RowId} - {v.Parent} - {v.FkId}")));
|
|
}
|
|
|
|
await AppDbContext.ExecuteBatch(cnx, $"""
|
|
COMMIT;
|
|
VACUUM;
|
|
PRAGMA schema_version = {toVersion * 100 + VersionOffset};
|
|
""");
|
|
}
|
|
}
|
|
}
|