From 858dadcdd1caadb5fa8cc13a02eb227098f39c3c Mon Sep 17 00:00:00 2001
From: Bond_009 <bond.009@outlook.com>
Date: Fri, 14 Apr 2023 13:43:56 +0200
Subject: POC sql connection pool

---
 .../Data/BaseSqliteRepository.cs                   | 115 +++++++++++++++------
 1 file changed, 82 insertions(+), 33 deletions(-)

(limited to 'Emby.Server.Implementations/Data/BaseSqliteRepository.cs')

diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
index bc520b86e..859a3c746 100644
--- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
+++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
@@ -4,7 +4,6 @@
 
 using System;
 using System.Collections.Generic;
-using System.Threading;
 using Jellyfin.Extensions;
 using Microsoft.Extensions.Logging;
 using SQLitePCL.pretty;
@@ -27,9 +26,19 @@ namespace Emby.Server.Implementations.Data
         /// <summary>
         /// Gets or sets the path to the DB file.
         /// </summary>
-        /// <value>Path to the DB file.</value>
         protected string DbFilePath { get; set; }
 
+        /// <summary>
+        /// Gets or sets the number of write connections to create.
+        /// </summary>
+        /// <value>Path to the DB file.</value>
+        protected int WriteConnectionsCount { get; set; } = 1;
+
+        /// <summary>
+        /// Gets or sets the number of read connections to create.
+        /// </summary>
+        protected int ReadConnectionsCount { get; set; } = 1;
+
         /// <summary>
         /// Gets the logger.
         /// </summary>
@@ -63,7 +72,7 @@ namespace Emby.Server.Implementations.Data
         /// <summary>
         /// Gets the locking mode. <see href="https://www.sqlite.org/pragma.html#pragma_locking_mode" />.
         /// </summary>
-        protected virtual string LockingMode => "EXCLUSIVE";
+        protected virtual string LockingMode => "NORMAL";
 
         /// <summary>
         /// Gets the journal mode. <see href="https://www.sqlite.org/pragma.html#pragma_journal_mode" />.
@@ -88,7 +97,7 @@ namespace Emby.Server.Implementations.Data
         /// </summary>
         /// <value>The temp store mode.</value>
         /// <see cref="TempStoreMode"/>
-        protected virtual TempStoreMode TempStore => TempStoreMode.Default;
+        protected virtual TempStoreMode TempStore => TempStoreMode.Memory;
 
         /// <summary>
         /// Gets the synchronous mode.
@@ -101,63 +110,115 @@ namespace Emby.Server.Implementations.Data
         /// Gets or sets the write lock.
         /// </summary>
         /// <value>The write lock.</value>
-        protected SemaphoreSlim WriteLock { get; set; } = new SemaphoreSlim(1, 1);
+        protected ConnectionPool WriteConnections { get; set; }
 
         /// <summary>
         /// Gets or sets the write connection.
         /// </summary>
         /// <value>The write connection.</value>
-        protected SQLiteDatabaseConnection WriteConnection { get; set; }
+        protected ConnectionPool ReadConnections { get; set; }
+
+        public virtual void Initialize()
+        {
+            WriteConnections = new ConnectionPool(WriteConnectionsCount, CreateWriteConnection);
+            ReadConnections = new ConnectionPool(ReadConnectionsCount, CreateReadConnection);
+        }
 
         protected ManagedConnection GetConnection(bool readOnly = false)
         {
-            WriteLock.Wait();
-            if (WriteConnection is not null)
+            if (readOnly)
             {
-                return new ManagedConnection(WriteConnection, WriteLock);
+                return ReadConnections.GetConnection();
             }
 
-            WriteConnection = SQLite3.Open(
+            return WriteConnections.GetConnection();
+        }
+
+        protected SQLiteDatabaseConnection CreateWriteConnection()
+        {
+            var writeConnection = SQLite3.Open(
                 DbFilePath,
                 DefaultConnectionFlags | ConnectionFlags.Create | ConnectionFlags.ReadWrite,
                 null);
 
             if (CacheSize.HasValue)
             {
-                WriteConnection.Execute("PRAGMA cache_size=" + CacheSize.Value);
+                writeConnection.Execute("PRAGMA cache_size=" + CacheSize.Value);
             }
 
             if (!string.IsNullOrWhiteSpace(LockingMode))
             {
-                WriteConnection.Execute("PRAGMA locking_mode=" + LockingMode);
+                writeConnection.Execute("PRAGMA locking_mode=" + LockingMode);
             }
 
             if (!string.IsNullOrWhiteSpace(JournalMode))
             {
-                WriteConnection.Execute("PRAGMA journal_mode=" + JournalMode);
+                writeConnection.Execute("PRAGMA journal_mode=" + JournalMode);
             }
 
             if (JournalSizeLimit.HasValue)
             {
-                WriteConnection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value);
+                writeConnection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value);
             }
 
             if (Synchronous.HasValue)
             {
-                WriteConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value);
+                writeConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value);
             }
 
             if (PageSize.HasValue)
             {
-                WriteConnection.Execute("PRAGMA page_size=" + PageSize.Value);
+                writeConnection.Execute("PRAGMA page_size=" + PageSize.Value);
             }
 
-            WriteConnection.Execute("PRAGMA temp_store=" + (int)TempStore);
+            writeConnection.Execute("PRAGMA temp_store=" + (int)TempStore);
 
             // Configuration and pragmas can affect VACUUM so it needs to be last.
-            WriteConnection.Execute("VACUUM");
+            writeConnection.Execute("VACUUM");
 
-            return new ManagedConnection(WriteConnection, WriteLock);
+            return writeConnection;
+        }
+
+        protected SQLiteDatabaseConnection CreateReadConnection()
+        {
+            var writeConnection = SQLite3.Open(
+                DbFilePath,
+                DefaultConnectionFlags | ConnectionFlags.ReadOnly,
+                null);
+
+            if (CacheSize.HasValue)
+            {
+                writeConnection.Execute("PRAGMA cache_size=" + CacheSize.Value);
+            }
+
+            if (!string.IsNullOrWhiteSpace(LockingMode))
+            {
+                writeConnection.Execute("PRAGMA locking_mode=" + LockingMode);
+            }
+
+            if (!string.IsNullOrWhiteSpace(JournalMode))
+            {
+                writeConnection.Execute("PRAGMA journal_mode=" + JournalMode);
+            }
+
+            if (JournalSizeLimit.HasValue)
+            {
+                writeConnection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value);
+            }
+
+            if (Synchronous.HasValue)
+            {
+                writeConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value);
+            }
+
+            if (PageSize.HasValue)
+            {
+                writeConnection.Execute("PRAGMA page_size=" + PageSize.Value);
+            }
+
+            writeConnection.Execute("PRAGMA temp_store=" + (int)TempStore);
+
+            return writeConnection;
         }
 
         public IStatement PrepareStatement(ManagedConnection connection, string sql)
@@ -240,22 +301,10 @@ namespace Emby.Server.Implementations.Data
 
             if (dispose)
             {
-                WriteLock.Wait();
-                try
-                {
-                    WriteConnection?.Dispose();
-                }
-                finally
-                {
-                    WriteLock.Release();
-                }
-
-                WriteLock.Dispose();
+                WriteConnections.Dispose();
+                ReadConnections.Dispose();
             }
 
-            WriteConnection = null;
-            WriteLock = null;
-
             _disposed = true;
         }
     }
-- 
cgit v1.2.3