Merge pull request #9044 from Bond-009/db

2 files changed, 26 insertions, 37 deletions

diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
index ff9aa4c2a..1d61667f8 100644
--- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
+++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
@@ -61,10 +61,21 @@ 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 => "EXCLUSIVE";
+
+        /// <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" />.
+        /// </summary>
+        /// <value>The journal size limit.</value>
+        protected virtual int? JournalSizeLimit => 0;
 
         /// <summary>
         /// Gets the page size.
@@ -84,7 +95,7 @@ namespace Emby.Server.Implementations.Data
         /// </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.
@@ -116,11 +127,21 @@ namespace Emby.Server.Implementations.Data
                 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=" + (int)JournalSizeLimit.Value);
+            }
+
             if (Synchronous.HasValue)
             {
                 WriteConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value);
diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
index 763ff77f1..bc703fe90 100644
--- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs
+++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
@@ -359,8 +359,6 @@ namespace Emby.Server.Implementations.Data
 
             string[] queries =
             {
-                "PRAGMA locking_mode=EXCLUSIVE",
-
                 "create table if not exists TypedBaseItems (guid GUID primary key NOT NULL, type TEXT NOT NULL, data BLOB NULL, ParentId GUID NULL, Path TEXT NULL)",
 
                 "create table if not exists AncestorIds (ItemId GUID NOT NULL, AncestorId GUID NOT NULL, AncestorIdText TEXT NOT NULL, PRIMARY KEY (ItemId, AncestorId))",
@@ -385,39 +383,6 @@ namespace Emby.Server.Implementations.Data
 
             string[] postQueries =
             {
-                // obsolete
-                "drop index if exists idx_TypedBaseItems",
-                "drop index if exists idx_mediastreams",
-                "drop index if exists idx_mediastreams1",
-                "drop index if exists idx_" + ChaptersTableName,
-                "drop index if exists idx_UserDataKeys1",
-                "drop index if exists idx_UserDataKeys2",
-                "drop index if exists idx_TypeTopParentId3",
-                "drop index if exists idx_TypeTopParentId2",
-                "drop index if exists idx_TypeTopParentId4",
-                "drop index if exists idx_Type",
-                "drop index if exists idx_TypeTopParentId",
-                "drop index if exists idx_GuidType",
-                "drop index if exists idx_TopParentId",
-                "drop index if exists idx_TypeTopParentId6",
-                "drop index if exists idx_ItemValues2",
-                "drop index if exists Idx_ProviderIds",
-                "drop index if exists idx_ItemValues3",
-                "drop index if exists idx_ItemValues4",
-                "drop index if exists idx_ItemValues5",
-                "drop index if exists idx_UserDataKeys3",
-                "drop table if exists UserDataKeys",
-                "drop table if exists ProviderIds",
-                "drop index if exists Idx_ProviderIds1",
-                "drop table if exists Images",
-                "drop index if exists idx_Images",
-                "drop index if exists idx_TypeSeriesPresentationUniqueKey",
-                "drop index if exists idx_SeriesPresentationUniqueKey",
-                "drop index if exists idx_TypeSeriesPresentationUniqueKey2",
-                "drop index if exists idx_AncestorIds3",
-                "drop index if exists idx_AncestorIds4",
-                "drop index if exists idx_AncestorIds2",
-
                 "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)",
                 "create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)",
 
@@ -458,6 +423,9 @@ namespace Emby.Server.Implementations.Data
 
                 // Used to update inherited tags
                 "create index if not exists idx_ItemValues8 on ItemValues(Type, ItemId, Value)",
+
+                "CREATE INDEX IF NOT EXISTS idx_TypedBaseItemsUserDataKeyType ON TypedBaseItems(UserDataKey, Type)",
+                "CREATE INDEX IF NOT EXISTS idx_PeopleNameListOrder ON People(Name, ListOrder)"
             };
 
             using (var connection = GetConnection())