349 lines
14 KiB
C#
349 lines
14 KiB
C#
using Microsoft.Data.Sqlite;
|
|
|
|
namespace ytLive.Services;
|
|
|
|
public partial class LayoutStore : IDisposable
|
|
{
|
|
private static readonly string[] SnapshotTables =
|
|
{
|
|
"Scene", "Source", "Webcam", "WebcamSceneConfig", "Socials", "SocialEntry", "Music",
|
|
};
|
|
|
|
private const string DefaultsInitializedKey = "DefaultsInitialized";
|
|
private const string CurrentBuildIdKey = "CurrentBuildId";
|
|
|
|
private void EnsureLayoutSnapshotSchema()
|
|
{
|
|
using (var cmd = _connection.CreateCommand())
|
|
{
|
|
cmd.CommandText = """
|
|
CREATE TABLE IF NOT EXISTS LayoutSnapshotState (
|
|
Key TEXT PRIMARY KEY,
|
|
Value TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS DefaultLayoutSetting (
|
|
Key TEXT PRIMARY KEY,
|
|
Value TEXT NOT NULL
|
|
);
|
|
""";
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
foreach (var table in SnapshotTables)
|
|
{
|
|
var snapshotTable = "Default" + table;
|
|
using (var create = _connection.CreateCommand())
|
|
{
|
|
create.CommandText = $"CREATE TABLE IF NOT EXISTS {snapshotTable} AS SELECT * FROM {table} WHERE 0;";
|
|
create.ExecuteNonQuery();
|
|
}
|
|
|
|
var snapshotColumns = ReadColumnNames(snapshotTable);
|
|
foreach (var (name, type, defaultValue) in ReadColumnDefinitions(table))
|
|
{
|
|
if (snapshotColumns.Contains(name, StringComparer.OrdinalIgnoreCase)) continue;
|
|
var definition = string.IsNullOrWhiteSpace(type) ? "BLOB" : type;
|
|
if (defaultValue != null) definition += " DEFAULT " + defaultValue;
|
|
using var add = _connection.CreateCommand();
|
|
add.CommandText = $"ALTER TABLE {snapshotTable} ADD COLUMN {QuoteIdentifier(name)} {definition};";
|
|
add.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>Captures the installed layout once as the immutable branded default. Existing databases
|
|
/// are snapshotted before MainViewModel loads them; a fresh database calls
|
|
/// <see cref="CaptureDefaultsIfMissing"/> after its factory scenes have been seeded.</summary>
|
|
public void PrepareLayoutSnapshots(string buildId)
|
|
{
|
|
ArgumentException.ThrowIfNullOrWhiteSpace(buildId);
|
|
var defaultsInitialized = GetSnapshotState(DefaultsInitializedKey) == "1";
|
|
if (!defaultsInitialized && HasStoredScenes())
|
|
{
|
|
CaptureDefaults();
|
|
defaultsInitialized = true;
|
|
}
|
|
|
|
var currentBuildId = GetSnapshotState(CurrentBuildIdKey);
|
|
if (defaultsInitialized && !string.Equals(currentBuildId, buildId, StringComparison.Ordinal))
|
|
RestoreDefaults(buildId);
|
|
else if (!defaultsInitialized && string.IsNullOrWhiteSpace(currentBuildId))
|
|
SetSnapshotState(CurrentBuildIdKey, buildId);
|
|
}
|
|
|
|
/// <summary>Seeds the default snapshot after the first-run factory layout has been persisted.</summary>
|
|
public void CaptureDefaultsIfMissing()
|
|
{
|
|
if (GetSnapshotState(DefaultsInitializedKey) != "1")
|
|
CaptureDefaults();
|
|
}
|
|
|
|
/// <summary>Restores one existing scene by its current ID. Unknown scenes have no branded default
|
|
/// and are left untouched.</summary>
|
|
public bool RestoreDefaultScene(string sceneId)
|
|
{
|
|
ArgumentException.ThrowIfNullOrWhiteSpace(sceneId);
|
|
|
|
string? defaultSceneId;
|
|
using (var find = _connection.CreateCommand())
|
|
{
|
|
find.CommandText = """
|
|
SELECT Id FROM DefaultScene
|
|
WHERE Name = (SELECT Name FROM Scene WHERE Id = $sceneId) COLLATE NOCASE
|
|
LIMIT 1;
|
|
""";
|
|
find.Parameters.AddWithValue("$sceneId", sceneId);
|
|
defaultSceneId = find.ExecuteScalar() as string;
|
|
}
|
|
if (defaultSceneId == null) return false;
|
|
|
|
var columnsByTable = SnapshotTables.ToDictionary(table => table, ReadColumnNames);
|
|
var webcamId = GetCurrentWebcamId();
|
|
using var tx = _connection.BeginTransaction();
|
|
|
|
using (var clearBackgroundSettings = _connection.CreateCommand())
|
|
{
|
|
clearBackgroundSettings.Transaction = tx;
|
|
clearBackgroundSettings.CommandText = """
|
|
DELETE FROM Settings
|
|
WHERE Key IN (
|
|
SELECT 'BackgroundPath_' || Id FROM Source WHERE SceneId = $sceneId
|
|
UNION SELECT 'BackgroundUseDefault_' || Id FROM Source WHERE SceneId = $sceneId
|
|
UNION SELECT 'BackgroundPath_' || $sceneId
|
|
UNION SELECT 'BackgroundUseDefault_' || $sceneId
|
|
);
|
|
""";
|
|
clearBackgroundSettings.Parameters.AddWithValue("$sceneId", sceneId);
|
|
clearBackgroundSettings.ExecuteNonQuery();
|
|
}
|
|
|
|
foreach (var table in new[] { "WebcamSceneConfig", "Source" })
|
|
{
|
|
using var delete = _connection.CreateCommand();
|
|
delete.Transaction = tx;
|
|
delete.CommandText = $"DELETE FROM {table} WHERE SceneId = $sceneId;";
|
|
delete.Parameters.AddWithValue("$sceneId", sceneId);
|
|
delete.ExecuteNonQuery();
|
|
}
|
|
|
|
var sceneColumns = columnsByTable["Scene"]
|
|
.Where(column => column != "Id" && column != "SortOrder").ToArray();
|
|
var assignments = string.Join(", ", sceneColumns.Select(column =>
|
|
$"{QuoteIdentifier(column)} = (SELECT {QuoteIdentifier(column)} FROM DefaultScene WHERE Id = $defaultSceneId)"));
|
|
using (var updateScene = _connection.CreateCommand())
|
|
{
|
|
updateScene.Transaction = tx;
|
|
updateScene.CommandText = $"UPDATE Scene SET {assignments} WHERE Id = $sceneId;";
|
|
updateScene.Parameters.AddWithValue("$sceneId", sceneId);
|
|
updateScene.Parameters.AddWithValue("$defaultSceneId", defaultSceneId);
|
|
updateScene.ExecuteNonQuery();
|
|
}
|
|
|
|
CopyDefaultSceneRows("Source", sceneId, defaultSceneId, null, tx, columnsByTable["Source"]);
|
|
if (webcamId != null)
|
|
CopyDefaultSceneRows("WebcamSceneConfig", sceneId, defaultSceneId, webcamId, tx,
|
|
columnsByTable["WebcamSceneConfig"]);
|
|
|
|
using (var copyBackgroundSettings = _connection.CreateCommand())
|
|
{
|
|
copyBackgroundSettings.Transaction = tx;
|
|
copyBackgroundSettings.CommandText = """
|
|
INSERT INTO Settings (Key, Value)
|
|
SELECT Key, Value FROM DefaultLayoutSetting
|
|
WHERE Key IN (
|
|
SELECT 'BackgroundPath_' || Id FROM DefaultSource WHERE SceneId = $defaultSceneId
|
|
UNION SELECT 'BackgroundUseDefault_' || Id FROM DefaultSource WHERE SceneId = $defaultSceneId
|
|
UNION SELECT 'BackgroundPath_' || $defaultSceneId
|
|
UNION SELECT 'BackgroundUseDefault_' || $defaultSceneId
|
|
);
|
|
""";
|
|
copyBackgroundSettings.Parameters.AddWithValue("$defaultSceneId", defaultSceneId);
|
|
copyBackgroundSettings.ExecuteNonQuery();
|
|
}
|
|
|
|
tx.Commit();
|
|
return true;
|
|
}
|
|
|
|
/// <summary>Restores the full factory snapshot without changing the active build stamp.</summary>
|
|
public bool RestoreAllDefaults()
|
|
{
|
|
if (GetSnapshotState(DefaultsInitializedKey) != "1") return false;
|
|
var buildId = GetSnapshotState(CurrentBuildIdKey);
|
|
if (string.IsNullOrWhiteSpace(buildId)) return false;
|
|
RestoreDefaults(buildId);
|
|
return true;
|
|
}
|
|
|
|
private string? GetCurrentWebcamId()
|
|
{
|
|
using var cmd = _connection.CreateCommand();
|
|
cmd.CommandText = "SELECT Id FROM Webcam LIMIT 1;";
|
|
return cmd.ExecuteScalar() as string;
|
|
}
|
|
|
|
private void CopyDefaultSceneRows(string table, string sceneId, string defaultSceneId,
|
|
string? webcamId, SqliteTransaction tx, IReadOnlyList<string> columns)
|
|
{
|
|
var columnList = string.Join(", ", columns.Select(QuoteIdentifier));
|
|
var values = string.Join(", ", columns.Select(column => column switch
|
|
{
|
|
"SceneId" => "$sceneId",
|
|
"WebcamId" when webcamId != null => "$webcamId",
|
|
_ => QuoteIdentifier(column),
|
|
}));
|
|
|
|
using var copy = _connection.CreateCommand();
|
|
copy.Transaction = tx;
|
|
copy.CommandText = $"INSERT INTO {table} ({columnList}) SELECT {values} FROM Default{table} WHERE SceneId = $defaultSceneId;";
|
|
copy.Parameters.AddWithValue("$sceneId", sceneId);
|
|
copy.Parameters.AddWithValue("$defaultSceneId", defaultSceneId);
|
|
if (webcamId != null) copy.Parameters.AddWithValue("$webcamId", webcamId);
|
|
copy.ExecuteNonQuery();
|
|
}
|
|
|
|
private void CaptureDefaults()
|
|
{
|
|
var columnsByTable = SnapshotTables.ToDictionary(table => table, ReadColumnNames);
|
|
using var tx = _connection.BeginTransaction();
|
|
foreach (var table in SnapshotTables.Reverse())
|
|
{
|
|
using var delete = _connection.CreateCommand();
|
|
delete.Transaction = tx;
|
|
delete.CommandText = $"DELETE FROM Default{table};";
|
|
delete.ExecuteNonQuery();
|
|
}
|
|
|
|
foreach (var table in SnapshotTables)
|
|
{
|
|
var columns = string.Join(", ", columnsByTable[table].Select(QuoteIdentifier));
|
|
using var copy = _connection.CreateCommand();
|
|
copy.Transaction = tx;
|
|
copy.CommandText = $"INSERT INTO Default{table} ({columns}) SELECT {columns} FROM {table};";
|
|
copy.ExecuteNonQuery();
|
|
}
|
|
|
|
using (var clearSettings = _connection.CreateCommand())
|
|
{
|
|
clearSettings.Transaction = tx;
|
|
clearSettings.CommandText = "DELETE FROM DefaultLayoutSetting;";
|
|
clearSettings.ExecuteNonQuery();
|
|
}
|
|
using (var copySettings = _connection.CreateCommand())
|
|
{
|
|
copySettings.Transaction = tx;
|
|
copySettings.CommandText = """
|
|
INSERT INTO DefaultLayoutSetting (Key, Value)
|
|
SELECT Key, Value FROM Settings
|
|
WHERE Key LIKE 'BackgroundPath_%' OR Key LIKE 'BackgroundUseDefault_%';
|
|
""";
|
|
copySettings.ExecuteNonQuery();
|
|
}
|
|
|
|
using (var state = _connection.CreateCommand())
|
|
{
|
|
state.Transaction = tx;
|
|
state.CommandText = """
|
|
INSERT INTO LayoutSnapshotState (Key, Value) VALUES ($key, '1')
|
|
ON CONFLICT(Key) DO UPDATE SET Value = excluded.Value;
|
|
""";
|
|
state.Parameters.AddWithValue("$key", DefaultsInitializedKey);
|
|
state.ExecuteNonQuery();
|
|
}
|
|
tx.Commit();
|
|
}
|
|
|
|
private void RestoreDefaults(string buildId)
|
|
{
|
|
var columnsByTable = SnapshotTables.ToDictionary(table => table, ReadColumnNames);
|
|
var currentWebcamId = GetCurrentWebcamId();
|
|
using var tx = _connection.BeginTransaction();
|
|
|
|
foreach (var table in SnapshotTables.Reverse())
|
|
{
|
|
if (table == "Webcam") continue;
|
|
using var delete = _connection.CreateCommand();
|
|
delete.Transaction = tx;
|
|
delete.CommandText = $"DELETE FROM {table};";
|
|
delete.ExecuteNonQuery();
|
|
}
|
|
|
|
foreach (var table in SnapshotTables)
|
|
{
|
|
if (table == "Webcam" || (table == "WebcamSceneConfig" && currentWebcamId == null)) continue;
|
|
var columns = string.Join(", ", columnsByTable[table].Select(QuoteIdentifier));
|
|
var values = string.Join(", ", columnsByTable[table].Select(column =>
|
|
column == "WebcamId" && table == "WebcamSceneConfig"
|
|
? "$webcamId"
|
|
: QuoteIdentifier(column)));
|
|
using var copy = _connection.CreateCommand();
|
|
copy.Transaction = tx;
|
|
copy.CommandText = $"INSERT INTO {table} ({columns}) SELECT {values} FROM Default{table};";
|
|
if (table == "WebcamSceneConfig") copy.Parameters.AddWithValue("$webcamId", currentWebcamId!);
|
|
copy.ExecuteNonQuery();
|
|
}
|
|
|
|
using (var clearSettings = _connection.CreateCommand())
|
|
{
|
|
clearSettings.Transaction = tx;
|
|
clearSettings.CommandText = "DELETE FROM Settings WHERE Key LIKE 'BackgroundPath_%' OR Key LIKE 'BackgroundUseDefault_%';";
|
|
clearSettings.ExecuteNonQuery();
|
|
}
|
|
using (var copySettings = _connection.CreateCommand())
|
|
{
|
|
copySettings.Transaction = tx;
|
|
copySettings.CommandText = "INSERT INTO Settings (Key, Value) SELECT Key, Value FROM DefaultLayoutSetting;";
|
|
copySettings.ExecuteNonQuery();
|
|
}
|
|
|
|
SetSnapshotState(CurrentBuildIdKey, buildId, tx);
|
|
tx.Commit();
|
|
}
|
|
|
|
private bool HasStoredScenes()
|
|
{
|
|
using var cmd = _connection.CreateCommand();
|
|
cmd.CommandText = "SELECT EXISTS(SELECT 1 FROM Scene LIMIT 1);";
|
|
return Convert.ToInt32(cmd.ExecuteScalar()) != 0;
|
|
}
|
|
|
|
private string? GetSnapshotState(string key)
|
|
{
|
|
using var cmd = _connection.CreateCommand();
|
|
cmd.CommandText = "SELECT Value FROM LayoutSnapshotState WHERE Key = $key;";
|
|
cmd.Parameters.AddWithValue("$key", key);
|
|
return cmd.ExecuteScalar() as string;
|
|
}
|
|
|
|
private void SetSnapshotState(string key, string value, SqliteTransaction? transaction = null)
|
|
{
|
|
using var cmd = _connection.CreateCommand();
|
|
cmd.Transaction = transaction;
|
|
cmd.CommandText = """
|
|
INSERT INTO LayoutSnapshotState (Key, Value) VALUES ($key, $value)
|
|
ON CONFLICT(Key) DO UPDATE SET Value = excluded.Value;
|
|
""";
|
|
cmd.Parameters.AddWithValue("$key", key);
|
|
cmd.Parameters.AddWithValue("$value", value);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
private List<string> ReadColumnNames(string table)
|
|
=> ReadColumnDefinitions(table).Select(column => column.Name).ToList();
|
|
|
|
private List<(string Name, string Type, string? DefaultValue)> ReadColumnDefinitions(string table)
|
|
{
|
|
var columns = new List<(string, string, string?)>();
|
|
using var cmd = _connection.CreateCommand();
|
|
cmd.CommandText = $"PRAGMA table_info({table});";
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
columns.Add((reader.GetString(1), reader.IsDBNull(2) ? string.Empty : reader.GetString(2),
|
|
reader.IsDBNull(4) ? null : reader.GetString(4)));
|
|
return columns;
|
|
}
|
|
|
|
private static string QuoteIdentifier(string identifier)
|
|
=> "\"" + identifier.Replace("\"", "\"\"") + "\"";
|
|
}
|