Merge branch 'master' into segment-deletion

1 files changed, 96 insertions, 46 deletions

diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
index 450688491..d05534ee7 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,10 +26,20 @@ 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>
         /// <value>The logger.</value>
@@ -61,10 +70,22 @@ namespace Emby.Server.Implementations.Data
         protected virtual int? CacheSize => null;
 
         /// <summary>
+        /// Gets the locking mode. <see href="https://www.sqlite.org/pragma.html#pragma_locking_mode" />.
+        /// </summary>
+        protected virtual string LockingMode => "NORMAL";
+
+        /// <summary>
         /// Gets the journal mode. <see href="https://www.sqlite.org/pragma.html#pragma_journal_mode" />.
         /// </summary>
         /// <value>The journal mode.</value>
-        protected virtual string JournalMode => "TRUNCATE";
+        protected virtual string JournalMode => "WAL";
+
+        /// <summary>
+        /// Gets the journal size limit. <see href="https://www.sqlite.org/pragma.html#pragma_journal_size_limit" />.
+        /// The default (-1) is overriden to prevent unconstrained WAL size, as reported by users.
+        /// </summary>
+        /// <value>The journal size limit.</value>
+        protected virtual int? JournalSizeLimit => 134_217_728; // 128MiB
 
         /// <summary>
         /// Gets the page size.
@@ -77,86 +98,127 @@ 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.
         /// </summary>
         /// <value>The synchronous mode or null.</value>
         /// <see cref="SynchronousMode"/>
-        protected virtual SynchronousMode? Synchronous => null;
+        protected virtual SynchronousMode? Synchronous => SynchronousMode.Normal;
 
         /// <summary>
         /// 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; }
 
-        protected ManagedConnection GetConnection(bool readOnly = false)
+        public virtual void Initialize()
         {
-            WriteLock.Wait();
-            if (WriteConnection != null)
+            WriteConnections = new ConnectionPool(WriteConnectionsCount, CreateWriteConnection);
+            ReadConnections = new ConnectionPool(ReadConnectionsCount, CreateReadConnection);
+
+            // Configuration and pragmas can affect VACUUM so it needs to be last.
+            using (var connection = GetConnection())
             {
-                return new ManagedConnection(WriteConnection, WriteLock);
+                connection.Execute("VACUUM");
             }
+        }
+
+        protected ManagedConnection GetConnection(bool readOnly = false)
+            => readOnly ? ReadConnections.GetConnection() : WriteConnections.GetConnection();
 
-            WriteConnection = SQLite3.Open(
+        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);
             }
 
             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);
             }
 
             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);
-
-            // Configuration and pragmas can affect VACUUM so it needs to be last.
-            WriteConnection.Execute("VACUUM");
+            writeConnection.Execute("PRAGMA temp_store=" + (int)TempStore);
 
-            return new ManagedConnection(WriteConnection, WriteLock);
+            return writeConnection;
         }
 
-        public IStatement PrepareStatement(ManagedConnection connection, string sql)
-            => connection.PrepareStatement(sql);
+        protected SQLiteDatabaseConnection CreateReadConnection()
+        {
+            var connection = SQLite3.Open(
+                DbFilePath,
+                DefaultConnectionFlags | ConnectionFlags.ReadOnly,
+                null);
 
-        public IStatement PrepareStatement(IDatabaseConnection connection, string sql)
-            => connection.PrepareStatement(sql);
+            if (CacheSize.HasValue)
+            {
+                connection.Execute("PRAGMA cache_size=" + CacheSize.Value);
+            }
 
-        public IStatement[] PrepareAll(IDatabaseConnection connection, IReadOnlyList<string> sql)
-        {
-            int len = sql.Count;
-            IStatement[] statements = new IStatement[len];
-            for (int i = 0; i < len; i++)
+            if (!string.IsNullOrWhiteSpace(LockingMode))
+            {
+                connection.Execute("PRAGMA locking_mode=" + LockingMode);
+            }
+
+            if (!string.IsNullOrWhiteSpace(JournalMode))
+            {
+                connection.Execute("PRAGMA journal_mode=" + JournalMode);
+            }
+
+            if (JournalSizeLimit.HasValue)
+            {
+                connection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value);
+            }
+
+            if (Synchronous.HasValue)
             {
-                statements[i] = connection.PrepareStatement(sql[i]);
+                connection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value);
             }
 
-            return statements;
+            connection.Execute("PRAGMA temp_store=" + (int)TempStore);
+
+            return connection;
         }
 
+        public IStatement PrepareStatement(ManagedConnection connection, string sql)
+            => connection.PrepareStatement(sql);
+
+        public IStatement PrepareStatement(IDatabaseConnection connection, string sql)
+            => connection.PrepareStatement(sql);
+
         protected bool TableExists(ManagedConnection connection, string name)
         {
             return connection.RunInTransaction(
@@ -231,22 +293,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;
         }
     }