dbapi/EVCB_OCPP.DBAPI/Services/MemDbService.cs

using Dapper;
using EVCB_OCPP.DBAPI.ConnectionFactory;
using EVCB_OCPP.DBAPI.Models.DBContext;
using EVCB_OCPP.DBAPI.Services.ServerMessageServices;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.DependencyInjection;
using Newtonsoft.Json;
using SQLitePCL;
using System.Diagnostics;

namespace EVCB_OCPP.DBAPI.Services
{
    public static class MemDbServiceExtentions
    {
        public const string BackupFileName = "SqlLiteBackup.db";

        public static IServiceCollection AddMemConnectionFactory(this IServiceCollection services)
        {
            services.AddSingleton<ISqliteConnectionConnectionFactory<MemDBContext>>(
            (serviceProvider) =>
            {
                return new SqliteConnectionFactory<MemDBContext>(serviceProvider.GetRequiredService<ILogger<SqliteConnectionFactory<MemDBContext>>>())
                {
                    ConnectionString = "Data Source=InMemory;Mode=Memory;Cache=Shared"
                };
            });
            
            return services;
        }

        public static IServiceCollection AddFileCacheConnectionFactory(this IServiceCollection services)
        {
            services.AddSingleton<ISqliteConnectionConnectionFactory<FileDBContext>>(
            (serviceProvider) =>
            {
                var safePath = GetSafeFolderPath(serviceProvider.GetRequiredService<IConfiguration>());
                var backupFilePath = Path.Combine(safePath, BackupFileName);
                return new SqliteConnectionFactory<FileDBContext>(serviceProvider.GetRequiredService<ILogger<SqliteConnectionFactory<FileDBContext>>>())
                {
                    ConnectionString = string.Format("Data Source= '{0}';", backupFilePath)
                };
            });
            
            return services;
        }

        public static IServiceCollection AddMemDbService(this IServiceCollection services)
        {
            services.AddFileCacheConnectionFactory();
            services.AddMemConnectionFactory();
            services.AddSingleton<MemDbService>();
            services.AddHostedService<MemDbService>( x => x.GetRequiredService<MemDbService>());
            return services;
        }

        internal static string GetSafeFolderPath(IConfiguration configuration)
        {
            var configSafeFolderPath = configuration["SafeFolderPath"];
            return string.IsNullOrEmpty(configSafeFolderPath) ? Directory.GetCurrentDirectory() : configSafeFolderPath;
        }
    }

    public class MemDbService : IHostedService
    {
        public MemDbService(
            IConfiguration configuration,
            ISqliteConnectionConnectionFactory<MemDBContext> memDbConnectionFactory,
            ISqliteConnectionConnectionFactory<FileDBContext> fileDbConnectionFactory,
            ILogger<MemDbService> logger
            )
        {
            this.memDbConnectionFactory = memDbConnectionFactory;
            this.fileDbConnectionFactory = fileDbConnectionFactory;
            this.logger = logger;
        }

        private readonly ISqliteConnectionConnectionFactory<MemDBContext> memDbConnectionFactory;
        private readonly ISqliteConnectionConnectionFactory<FileDBContext> fileDbConnectionFactory;
        private readonly ILogger<MemDbService> logger;
        private SqliteConnection? memConnectionCache = null;

        public async Task StartAsync(CancellationToken cancellationToken)
        {
            Stopwatch stopwatch = Stopwatch.StartNew();

            memConnectionCache = await memDbConnectionFactory.CreateAsync();

            await LoadBackupedData();
            await InitializeDbAsync();

            stopwatch.Stop();
            logger.LogDebug("MemDbService StartAsync cost {time} ms", stopwatch.ElapsedMilliseconds);
        }

        public async Task StopAsync(CancellationToken cancellationToken)
        {
            Stopwatch stopwatch = Stopwatch.StartNew();

            await SaveDbToFileAsync();
            await memConnectionCache!.DisposeAsync();

            stopwatch.Stop();
            logger.LogDebug("MemDbService StopAsync cost {time} ms", stopwatch.ElapsedMilliseconds);
        }

        public async Task<string> GetMemoryUsage()
        {
            var memoryUsed = raw.sqlite3_memory_used();
            return $"Memory used by SQLite (in bytes): {memoryUsed}";

            var cmd0 = """
                PRAGMA page_size
                """;
            var cmd1 = """
                PRAGMA page_count
                """;

            var connection = await memDbConnectionFactory.CreateAsync();
            var page_size = await connection.QueryFirstOrDefaultAsync<int>(cmd0);
            var page_count = await connection.QueryFirstOrDefaultAsync<int>(cmd1);
            return $"{page_count}x{page_size}={page_count* page_size} (bytes):";
        }

        private async Task InitializeDbAsync()
        {
            var connection = await memDbConnectionFactory.CreateAsync();
            await MemDbServerMessageService.IinitMemDbAsync(connection);
        }

        private async Task SaveDbToFileAsync()
        {
            using var memConnection = await memDbConnectionFactory.CreateAsync();
            using var fileConnection = await fileDbConnectionFactory.CreateAsync();
            memConnection.BackupDatabase(fileConnection);
        }

        private async Task LoadBackupedData()
        {
            using var memConnection = await memDbConnectionFactory.CreateAsync();
            using var fileConnection = await fileDbConnectionFactory.CreateAsync();
            fileConnection.BackupDatabase(memConnection);
        }
    }
}