update db connections

7 files changed, 157 insertions, 150 deletions

diff --git a/Emby.Common.Implementations/ScheduledTasks/ScheduledTaskWorker.cs b/Emby.Common.Implementations/ScheduledTasks/ScheduledTaskWorker.cs
index f288c5c0f..cbc7c7c2d 100644
--- a/Emby.Common.Implementations/ScheduledTasks/ScheduledTaskWorker.cs
+++ b/Emby.Common.Implementations/ScheduledTasks/ScheduledTaskWorker.cs
@@ -387,6 +387,7 @@ namespace Emby.Common.Implementations.ScheduledTasks
             finally
             {
                 _currentTask = null;
+                GC.Collect();
             }
         }
 
diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
index b29309ef2..206422176 100644
--- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
+++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
@@ -1,5 +1,6 @@
 using System;
 using System.Collections.Generic;
+using System.Globalization;
 using System.Threading;
 using System.Threading.Tasks;
 using MediaBrowser.Model.Logging;
@@ -43,6 +44,7 @@ namespace Emby.Server.Implementations.Data
             //CheckOk(rc);
 
             rc = raw.sqlite3_config(raw.SQLITE_CONFIG_MULTITHREAD, 1);
+            //rc = raw.sqlite3_config(raw.SQLITE_CONFIG_SINGLETHREAD, 1);
             //rc = raw.sqlite3_config(raw.SQLITE_CONFIG_SERIALIZED, 1);
             //CheckOk(rc);
 
@@ -54,9 +56,20 @@ namespace Emby.Server.Implementations.Data
         private static bool _versionLogged;
 
         private string _defaultWal;
+        protected ManagedConnection _connection;
 
-        protected SQLiteDatabaseConnection CreateConnection(bool isReadOnly = false)
+        protected virtual bool EnableSingleConnection
         {
+            get { return true; }
+        }
+
+        protected ManagedConnection CreateConnection(bool isReadOnly = false)
+        {
+            if (_connection != null)
+            {
+                return _connection;
+            }
+
             lock (WriteLock)
             {
                 if (!_versionLogged)
@@ -82,7 +95,15 @@ namespace Emby.Server.Implementations.Data
                     connectionFlags |= ConnectionFlags.ReadWrite;
                 }
 
-                connectionFlags |= ConnectionFlags.SharedCached;
+                if (EnableSingleConnection)
+                {
+                    connectionFlags |= ConnectionFlags.PrivateCache;
+                }
+                else
+                {
+                    connectionFlags |= ConnectionFlags.SharedCached;
+                }
+
                 connectionFlags |= ConnectionFlags.NoMutex;
 
                 var db = SQLite3.Open(DbFilePath, connectionFlags, null);
@@ -95,11 +116,16 @@ namespace Emby.Server.Implementations.Data
                 }
 
                 var queries = new List<string>
-            {
-                //"PRAGMA cache size=-10000"
-                //"PRAGMA read_uncommitted = true",
-                "PRAGMA synchronous=Normal"
-            };
+                {
+                    //"PRAGMA cache size=-10000"
+                    //"PRAGMA read_uncommitted = true",
+                    "PRAGMA synchronous=Normal"
+                };
+
+                if (CacheSize.HasValue)
+                {
+                    queries.Add("PRAGMA cache_size=-" + CacheSize.Value.ToString(CultureInfo.InvariantCulture));
+                }
 
                 if (EnableTempStoreMemory)
                 {
@@ -135,10 +161,22 @@ namespace Emby.Server.Implementations.Data
                     db.Execute(query);
                 }
 
-                return db;
+                _connection = new ManagedConnection(db, false);
+
+                return _connection;
             }
         }
 
+        public IStatement PrepareStatement(ManagedConnection connection, string sql)
+        {
+            return connection.PrepareStatement(sql);
+        }
+
+        public IStatement PrepareStatementSafe(ManagedConnection connection, string sql)
+        {
+            return connection.PrepareStatement(sql);
+        }
+
         public IStatement PrepareStatement(IDatabaseConnection connection, string sql)
         {
             return connection.PrepareStatement(sql);
@@ -159,7 +197,7 @@ namespace Emby.Server.Implementations.Data
             return sql.Select(connection.PrepareStatement).ToList();
         }
 
-        protected void RunDefaultInitialization(IDatabaseConnection db)
+        protected void RunDefaultInitialization(ManagedConnection db)
         {
             var queries = new List<string>
             {
@@ -246,6 +284,12 @@ namespace Emby.Server.Implementations.Data
                     {
                         using (WriteLock.Write())
                         {
+                            if (_connection != null)
+                            {
+                                _connection.Close();
+                                _connection = null;
+                            }
+
                             CloseConnection();
                         }
                     }
@@ -340,7 +384,7 @@ namespace Emby.Server.Implementations.Data
             //{
             //    return new DummyToken();
             //}
-            return new ReadLockToken(obj);
+            return new WriteLockToken(obj);
         }
         public static IDisposable Write(this ReaderWriterLockSlim obj)
         {
diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs
index 5b2549087..d6ad0ba8a 100644
--- a/Emby.Server.Implementations/Data/SqliteExtensions.cs
+++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs
@@ -129,7 +129,7 @@ namespace Emby.Server.Implementations.Data
             }
         }
 
-        public static void Attach(IDatabaseConnection db, string path, string alias)
+        public static void Attach(ManagedConnection db, string path, string alias)
         {
             var commandText = string.Format("attach @path as {0};", alias);
 
diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
index f93819322..5f2a314f9 100644
--- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs
+++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
@@ -105,13 +105,7 @@ namespace Emby.Server.Implementations.Data
         {
             get
             {
-                var cacheSize = _config.Configuration.SqliteCacheSize;
-                if (cacheSize <= 0)
-                {
-                    cacheSize = Math.Min(Environment.ProcessorCount * 50000, 100000);
-                }
-
-                return 0 - cacheSize;
+                return 20000;
             }
         }
 
@@ -376,9 +370,9 @@ namespace Emby.Server.Implementations.Data
                 //await Vacuum(_connection).ConfigureAwait(false);
             }
 
-            userDataRepo.Initialize(WriteLock);
+            userDataRepo.Initialize(WriteLock, _connection);
 
-            _shrinkMemoryTimer = _timerFactory.Create(OnShrinkMemoryTimerCallback, null, TimeSpan.FromMinutes(1), TimeSpan.FromMinutes(30));
+            _shrinkMemoryTimer = _timerFactory.Create(OnShrinkMemoryTimerCallback, null, TimeSpan.FromMinutes(1), TimeSpan.FromMinutes(15));
         }
 
         private void OnShrinkMemoryTimerCallback(object state)
@@ -1258,21 +1252,17 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    return connection.RunInTransaction(db =>
+                    using (var statement = PrepareStatementSafe(connection, "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid"))
                     {
-                        using (var statement = PrepareStatementSafe(db, "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid"))
-                        {
-                            statement.TryBind("@guid", id);
+                        statement.TryBind("@guid", id);
 
-                            foreach (var row in statement.ExecuteQuery())
-                            {
-                                return GetItem(row);
-                            }
+                        foreach (var row in statement.ExecuteQuery())
+                        {
+                            return GetItem(row);
                         }
+                    }
 
-                        return null;
-
-                    }, ReadTransactionMode);
+                    return null;
                 }
             }
         }
@@ -2091,23 +2081,19 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    return connection.RunInTransaction(db =>
+                    var list = new List<ChapterInfo>();
+
+                    using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"))
                     {
-                        var list = new List<ChapterInfo>();
+                        statement.TryBind("@ItemId", id);
 
-                        using (var statement = PrepareStatementSafe(db, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"))
+                        foreach (var row in statement.ExecuteQuery())
                         {
-                            statement.TryBind("@ItemId", id);
-
-                            foreach (var row in statement.ExecuteQuery())
-                            {
-                                list.Add(GetChapter(row));
-                            }
+                            list.Add(GetChapter(row));
                         }
+                    }
 
-                        return list;
-
-                    }, ReadTransactionMode);
+                    return list;
                 }
             }
         }
@@ -2486,26 +2472,22 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    return connection.RunInTransaction(db =>
+                    using (var statement = PrepareStatementSafe(connection, commandText))
                     {
-                        using (var statement = PrepareStatementSafe(db, commandText))
+                        if (EnableJoinUserData(query))
                         {
-                            if (EnableJoinUserData(query))
-                            {
-                                statement.TryBind("@UserId", query.User.Id);
-                            }
-
-                            BindSimilarParams(query, statement);
+                            statement.TryBind("@UserId", query.User.Id);
+                        }
 
-                            // Running this again will bind the params
-                            GetWhereClauses(query, statement);
+                        BindSimilarParams(query, statement);
 
-                            var count = statement.ExecuteQuery().SelectScalarInt().First();
-                            LogQueryTime("GetCount", commandText, now);
-                            return count;
-                        }
+                        // Running this again will bind the params
+                        GetWhereClauses(query, statement);
 
-                    }, ReadTransactionMode);
+                        var count = statement.ExecuteQuery().SelectScalarInt().First();
+                        LogQueryTime("GetCount", commandText, now);
+                        return count;
+                    }
                 }
 
             }
@@ -2983,33 +2965,29 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    return connection.RunInTransaction(db =>
-                    {
-                        var list = new List<Guid>();
+                    var list = new List<Guid>();
 
-                        using (var statement = PrepareStatementSafe(db, commandText))
+                    using (var statement = PrepareStatementSafe(connection, commandText))
+                    {
+                        if (EnableJoinUserData(query))
                         {
-                            if (EnableJoinUserData(query))
-                            {
-                                statement.TryBind("@UserId", query.User.Id);
-                            }
+                            statement.TryBind("@UserId", query.User.Id);
+                        }
 
-                            BindSimilarParams(query, statement);
+                        BindSimilarParams(query, statement);
 
-                            // Running this again will bind the params
-                            GetWhereClauses(query, statement);
+                        // Running this again will bind the params
+                        GetWhereClauses(query, statement);
 
-                            foreach (var row in statement.ExecuteQuery())
-                            {
-                                list.Add(row[0].ReadGuid());
-                            }
+                        foreach (var row in statement.ExecuteQuery())
+                        {
+                            list.Add(row[0].ReadGuid());
                         }
+                    }
 
-                        LogQueryTime("GetItemList", commandText, now);
-
-                        return list;
+                    LogQueryTime("GetItemList", commandText, now);
 
-                    }, ReadTransactionMode);
+                    return list;
                 }
             }
         }
@@ -4678,21 +4656,18 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    return connection.RunInTransaction(db =>
+                    var list = new List<string>();
+                    using (var statement = PrepareStatementSafe(connection, commandText))
                     {
-                        var list = new List<string>();
-                        using (var statement = PrepareStatementSafe(db, commandText))
-                        {
-                            // Run this again to bind the params
-                            GetPeopleWhereClauses(query, statement);
+                        // Run this again to bind the params
+                        GetPeopleWhereClauses(query, statement);
 
-                            foreach (var row in statement.ExecuteQuery())
-                            {
-                                list.Add(row.GetString(0));
-                            }
+                        foreach (var row in statement.ExecuteQuery())
+                        {
+                            list.Add(row.GetString(0));
                         }
-                        return list;
-                    }, ReadTransactionMode);
+                    }
+                    return list;
                 }
             }
         }
@@ -4721,23 +4696,20 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    return connection.RunInTransaction(db =>
+                    var list = new List<PersonInfo>();
+
+                    using (var statement = PrepareStatementSafe(connection, commandText))
                     {
-                        var list = new List<PersonInfo>();
+                        // Run this again to bind the params
+                        GetPeopleWhereClauses(query, statement);
 
-                        using (var statement = PrepareStatementSafe(db, commandText))
+                        foreach (var row in statement.ExecuteQuery())
                         {
-                            // Run this again to bind the params
-                            GetPeopleWhereClauses(query, statement);
-
-                            foreach (var row in statement.ExecuteQuery())
-                            {
-                                list.Add(GetPerson(row));
-                            }
+                            list.Add(GetPerson(row));
                         }
+                    }
 
-                        return list;
-                    }, ReadTransactionMode);
+                    return list;
                 }
             }
         }
@@ -4939,25 +4911,22 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    return connection.RunInTransaction(db =>
-                    {
-                        var list = new List<string>();
+                    var list = new List<string>();
 
-                        using (var statement = PrepareStatementSafe(db, commandText))
+                    using (var statement = PrepareStatementSafe(connection, commandText))
+                    {
+                        foreach (var row in statement.ExecuteQuery())
                         {
-                            foreach (var row in statement.ExecuteQuery())
+                            if (!row.IsDBNull(0))
                             {
-                                if (!row.IsDBNull(0))
-                                {
-                                    list.Add(row.GetString(0));
-                                }
+                                list.Add(row.GetString(0));
                             }
                         }
+                    }
 
-                        LogQueryTime("GetItemValueNames", commandText, now);
+                    LogQueryTime("GetItemValueNames", commandText, now);
 
-                        return list;
-                    }, ReadTransactionMode);
+                    return list;
                 }
             }
         }
@@ -5434,33 +5403,29 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    return connection.RunInTransaction(db =>
+                    var list = new List<MediaStream>();
+
+                    using (var statement = PrepareStatementSafe(connection, cmdText))
                     {
-                        var list = new List<MediaStream>();
+                        statement.TryBind("@ItemId", query.ItemId.ToGuidParamValue());
 
-                        using (var statement = PrepareStatementSafe(db, cmdText))
+                        if (query.Type.HasValue)
                         {
-                            statement.TryBind("@ItemId", query.ItemId.ToGuidParamValue());
-
-                            if (query.Type.HasValue)
-                            {
-                                statement.TryBind("@StreamType", query.Type.Value.ToString());
-                            }
-
-                            if (query.Index.HasValue)
-                            {
-                                statement.TryBind("@StreamIndex", query.Index.Value);
-                            }
+                            statement.TryBind("@StreamType", query.Type.Value.ToString());
+                        }
 
-                            foreach (var row in statement.ExecuteQuery())
-                            {
-                                list.Add(GetMediaStream(row));
-                            }
+                        if (query.Index.HasValue)
+                        {
+                            statement.TryBind("@StreamIndex", query.Index.Value);
                         }
 
-                        return list;
+                        foreach (var row in statement.ExecuteQuery())
+                        {
+                            list.Add(GetMediaStream(row));
+                        }
+                    }
 
-                    }, ReadTransactionMode);
+                    return list;
                 }
             }
         }
diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs
index 7767ae892..2e39b038a 100644
--- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs
+++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs
@@ -42,8 +42,10 @@ namespace Emby.Server.Implementations.Data
         /// Opens the connection to the database
         /// </summary>
         /// <returns>Task.</returns>
-        public void Initialize(ReaderWriterLockSlim writeLock)
+        public void Initialize(ReaderWriterLockSlim writeLock, ManagedConnection managedConnection)
         {
+            _connection = managedConnection;
+
             WriteLock.Dispose();
             WriteLock = writeLock;
 
@@ -90,7 +92,7 @@ namespace Emby.Server.Implementations.Data
             }
         }
 
-        private void ImportUserDataIfNeeded(IDatabaseConnection connection)
+        private void ImportUserDataIfNeeded(ManagedConnection connection)
         {
             if (!_fileSystem.FileExists(_importFile))
             {
@@ -117,7 +119,7 @@ namespace Emby.Server.Implementations.Data
             }, TransactionMode);
         }
 
-        private void ImportUserData(IDatabaseConnection connection, string file)
+        private void ImportUserData(ManagedConnection connection, string file)
         {
             SqliteExtensions.Attach(connection, file, "UserDataBackup");
 
@@ -300,22 +302,18 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    return connection.RunInTransaction(db =>
+                    using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where key =@Key and userId=@UserId"))
                     {
-                        using (var statement = db.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where key =@Key and userId=@UserId"))
-                        {
-                            statement.TryBind("@UserId", userId.ToGuidParamValue());
-                            statement.TryBind("@Key", key);
+                        statement.TryBind("@UserId", userId.ToGuidParamValue());
+                        statement.TryBind("@Key", key);
 
-                            foreach (var row in statement.ExecuteQuery())
-                            {
-                                return ReadRow(row);
-                            }
+                        foreach (var row in statement.ExecuteQuery())
+                        {
+                            return ReadRow(row);
                         }
+                    }
 
-                        return null;
-
-                    }, ReadTransactionMode);
+                    return null;
                 }
             }
         }
diff --git a/Emby.Server.Implementations/Emby.Server.Implementations.csproj b/Emby.Server.Implementations/Emby.Server.Implementations.csproj
index e478b9d81..92e83f05b 100644
--- a/Emby.Server.Implementations/Emby.Server.Implementations.csproj
+++ b/Emby.Server.Implementations/Emby.Server.Implementations.csproj
@@ -51,6 +51,7 @@
     <Compile Include="Connect\ConnectManager.cs" />
     <Compile Include="Connect\Responses.cs" />
     <Compile Include="Connect\Validator.cs" />
+    <Compile Include="Data\ManagedConnection.cs" />
     <Compile Include="Data\SqliteDisplayPreferencesRepository.cs" />
     <Compile Include="Data\SqliteFileOrganizationRepository.cs" />
     <Compile Include="Data\SqliteItemRepository.cs" />
diff --git a/MediaBrowser.Model/Configuration/ServerConfiguration.cs b/MediaBrowser.Model/Configuration/ServerConfiguration.cs
index 35677813d..f5c4bba50 100644
--- a/MediaBrowser.Model/Configuration/ServerConfiguration.cs
+++ b/MediaBrowser.Model/Configuration/ServerConfiguration.cs
@@ -191,7 +191,6 @@ namespace MediaBrowser.Model.Configuration
         public int SharingExpirationDays { get; set; }
 
         public int SchemaVersion { get; set; }
-        public int SqliteCacheSize { get; set; }
 
         public bool EnableAnonymousUsageReporting { get; set; }
         public bool EnableStandaloneMusicKeys { get; set; }
@@ -214,7 +213,6 @@ namespace MediaBrowser.Model.Configuration
         {
             LocalNetworkAddresses = new string[] { };
             CodecsUsed = new string[] { };
-            SqliteCacheSize = 0;
             ImageExtractionTimeoutMs = 0;
 
             EnableLocalizedGuids = true;