AIPlatform/aipagnt/SqliteDbAccessor.cs

using AIPlatform.Protocol.Entities;
using AIPlatform.Protocol.Model;
using SQLite.Net;
using SQLite.Net.Interop;
using SQLite.Net.Platform.Win32;
using System;
using System.Collections.Generic;
using System.IO;

namespace aipagnt
{
    class TrainGroupCache : TrainGroupModel
    {
    }

    class TestGroupCache : TrainGroupCache
    {
    }

    class TrainLabeledFileCache : EntityBase
    {
        public bool IsVideo { get; set; }

        public byte[] FileData { get; set; }

        public string LabelContent { get; set; }

        public int Index { get; set; }

        public int GroupIndex { get; set; }

        /// <summary>
        /// 模态名称
        /// </summary>
        public string ModalTitle { get; set; }
    }

    class TestLabeledFileCache : TrainLabeledFileCache
    {
    }

    class SqliteDbAccessor
    {
        private static SqliteDbAccessor _instance;
        private readonly object _dbLock = new object();

        /// <summary>
        /// The sqlite connection.
        /// </summary>
        private SQLiteConnection _connection;

        private string _dbPath;
        private ISQLitePlatform _platform;
        private Dictionary<int, TrainGroupCache> _trainGroupCaches = new Dictionary<int, TrainGroupCache>();
        private Dictionary<int, TestGroupCache> _testGroupCaches = new Dictionary<int, TestGroupCache>();
        private Dictionary<long, TrainLabeledFileCache> _trainFileCaches = new Dictionary<long, TrainLabeledFileCache>();
        private Dictionary<long, TestLabeledFileCache> _testFileCaches = new Dictionary<long, TestLabeledFileCache>();
        private const int BatchSize = 1000;

        public static SqliteDbAccessor Instance => _instance ?? (_instance = new SqliteDbAccessor());

        /// <summary>
        /// Initialize db
        /// </summary>
        public void Initialize()
        {
            _platform = new SQLitePlatformWin32();
            ConnectDatabase();
        }

        private void ConnectDatabase()
        {
            var cacheFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory + "ImagesCache");
            if (!Directory.Exists(cacheFolder))
            {
                Directory.CreateDirectory(cacheFolder);
            }

            var dbFileName = "aipagent.imagescache.db";
            _dbPath = Path.Combine(cacheFolder, dbFileName);
            if (File.Exists(_dbPath))
            {
                File.SetAttributes(_dbPath, FileAttributes.Normal);
                File.Delete(_dbPath);
            }
            _connection = new SQLiteConnection(_platform, _dbPath);
            CreateTableGroup<TrainGroupCache>();
            CreateTableGroup<TestGroupCache>();
            CreateTable<TrainLabeledFileCache>();
            CreateTable<TestLabeledFileCache>();
        }

        #region Group

        /// <summary>
        /// Create table
        /// </summary>
        /// <typeparam name="T">Table type.</typeparam>
        private void CreateTableGroup<T>() where T : TrainGroupCache
        {
            lock (_dbLock)
            {
                _connection.CreateTable<T>();
                _connection.CreateIndex<T>(x => x.Index);
            }
        }

        public T GetGroup<T>(int index) where T : TrainGroupCache
        {
            lock (_dbLock)
            {
                return _connection.Find<T>(x => x.Index == index);
            }
        }

        /// <summary>
        /// Create or update a exam record cache
        /// </summary>
        /// <param name="model"></param>
        public void InsertGroup<T>(T model) where T : TrainGroupCache
        {
            lock (_dbLock)
            {
                if (model is TestGroupCache testGroupCache)
                {
                    if (_testGroupCaches.ContainsKey(model.Index))
                    {
                        _connection.InsertAll(_testGroupCaches.Values);
                        _testGroupCaches.Clear();
                    }
                    else
                    {
                        _testGroupCaches.Add(testGroupCache.Index, testGroupCache);
                    }
                }
                else
                {
                    if (_trainGroupCaches.ContainsKey(model.Index))
                    {
                        _connection.InsertAll(_trainGroupCaches.Values);
                        _trainGroupCaches.Clear();
                    }
                    else
                    {
                        _trainGroupCaches.Add(model.Index, model);
                    }
                }

                if (_trainGroupCaches.Count >= BatchSize)
                {
                    _connection.InsertAll(_trainGroupCaches.Values);
                    _trainGroupCaches.Clear();
                }

                if (_testGroupCaches.Count >= BatchSize)
                {
                    _connection.InsertAll(_testGroupCaches.Values);
                    _testGroupCaches.Clear();
                }
            }
        }

        #endregion

        #region File

        /// <summary>
        /// Create table
        /// </summary>
        /// <typeparam name="T">Table type.</typeparam>
        private void CreateTable<T>() where T : TrainLabeledFileCache
        {
            lock (_dbLock)
            {
                _connection.CreateTable<T>();
                _connection.CreateIndex<T>(x => x.Id);
            }
        }

        public T Get<T>(long id) where T : TrainLabeledFileCache
        {
            lock (_dbLock)
            {
                return _connection.Find<T>(x => x.Id == id);
            }
        }

        /// <summary>
        /// Create or update a exam record cache
        /// </summary>
        /// <param name="model"></param>
        public void Insert<T>(T model) where T : TrainLabeledFileCache
        {
            lock (_dbLock)
            {
                if (model is TestLabeledFileCache testLabeledImageCache)
                {
                    if (_testFileCaches.ContainsKey(model.Id))
                    {
                        _connection.InsertAll(_testFileCaches.Values);
                        _testFileCaches.Clear();
                    }
                    else
                    {
                        _testFileCaches.Add(testLabeledImageCache.Id, testLabeledImageCache);
                    }
                }
                else
                {
                    if (_trainFileCaches.ContainsKey(model.Id))
                    {
                        _connection.InsertAll(_trainFileCaches.Values);
                        _trainFileCaches.Clear();
                    }
                    else
                    {
                        _trainFileCaches.Add(model.Id, model);
                    }
                }

                if (_trainFileCaches.Count >= BatchSize)
                {
                    _connection.InsertAll(_trainFileCaches.Values);
                    _trainFileCaches.Clear();
                }

                if (_testFileCaches.Count >= BatchSize)
                {
                    _connection.InsertAll(_testFileCaches.Values);
                    _testFileCaches.Clear();
                }
            }
        }

        #endregion

        /// <summary>
        /// Clear current database
        /// </summary>
        public void Reset()
        {
            lock (_dbLock)
            {
                _trainGroupCaches.Clear();
                _testGroupCaches.Clear();
                _testFileCaches.Clear();
                _trainFileCaches.Clear();
                if (_connection != null)
                {
                    _connection.Close();
                    _connection.Dispose();
                }

                ConnectDatabase();
            }
        }
    }
}