hermes-server/Store/ChatterStore.cs

using System.Collections.Immutable;
using HermesSocketLibrary.db;
using HermesSocketServer.Models;

namespace HermesSocketServer.Store
{
    public class ChatterStore : GroupSaveStore<string, ChatterVoice>
    {
        private readonly string _userId;
        private readonly Database _database;
        private readonly Serilog.ILogger _logger;
        private readonly GroupSaveSqlGenerator<ChatterVoice> _generator;


        public ChatterStore(string userId, Database database, Serilog.ILogger logger) : base(logger)
        {
            _userId = userId;
            _database = database;
            _logger = logger;

            var ctp = new Dictionary<string, string>
            {
                { "chatterId", "ChatterId" },
                { "ttsVoiceId", "VoiceId" },
                { "userId", "UserId" },
            };
            _generator = new GroupSaveSqlGenerator<ChatterVoice>(ctp, _logger);
        }

        public override async Task Load()
        {
            var data = new Dictionary<string, object>() { { "user", _userId } };
            string sql = $"SELECT \"chatterId\", \"ttsVoiceId\" FROM \"TtsChatVoice\" WHERE \"userId\" = @user";
            await _database.Execute(sql, data, (reader) =>
            {
                var chatterId = reader.GetInt64(0);
                _store.Add(chatterId.ToString(), new ChatterVoice()
                {
                    UserId = _userId,
                    ChatterId = chatterId,
                    VoiceId = reader.GetString(1)
                });
            });
            _logger.Information($"Loaded {_store.Count} TTS chatter voices from database.");
        }

        protected override void OnInitialAdd(string key, ChatterVoice value)
        {
        }

        protected override void OnInitialModify(string key, ChatterVoice value)
        {
        }

        protected override void OnInitialRemove(string key)
        {
        }

        public override async Task Save()
        {
            int count = 0;
            string sql = string.Empty;
            ImmutableList<string>? list = null;

            if (_added.Any())
            {
                lock (_lock)
                {
                    list = _added.ToImmutableList();
                    _added.Clear();
                }
                count = list.Count;
                sql = _generator.GeneratePreparedInsertSql("TtsChatVoice", count, ["userId", "chatterId", "ttsVoiceId"]);

                _logger.Debug($"User - Adding {count} rows to database: {sql}");
                var values = list.Select(id => _store[id]).Where(v => v != null);
                await _generator.DoPreparedStatement(_database, sql, values, ["id", "name", "email", "role", "ttsDefaultVoice"]);
            }
            if (_modified.Any())
            {
                lock (_lock)
                {
                    list = _modified.ToImmutableList();
                    _modified.Clear();
                }
                count = list.Count;
                sql = _generator.GeneratePreparedUpdateSql("TtsChatVoice", count, ["userId", "chatterId"], ["ttsVoiceId"]);

                _logger.Debug($"User - Modifying {count} rows in database: {sql}");
                var values = list.Select(id => _store[id]).Where(v => v != null);
                await _generator.DoPreparedStatement(_database, sql, values, ["id", "name", "email", "role", "ttsDefaultVoice"]);
            }
            if (_deleted.Any())
            {
                lock (_lock)
                {
                    list = _deleted.ToImmutableList();
                    _deleted.Clear();
                }
                count = list.Count;
                sql = _generator.GeneratePreparedDeleteSql("TtsChatVoice", count, ["userId", "chatterId"]);

                _logger.Debug($"User - Deleting {count} rows from database: {sql}");
                await _generator.DoPreparedStatementRaw(_database, sql, list, ["id"]);
            }
        }
    }
}