From c6f1bd93fc3c6aab16d5ba2c0ceeddb9303d029d Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Fri, 18 Nov 2016 15:43:19 -0500 Subject: rework organizer repository --- .../Data/SqliteFileOrganizationRepository.cs | 256 +++++++++++++++++++++ 1 file changed, 256 insertions(+) create mode 100644 Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs (limited to 'Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs') diff --git a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs new file mode 100644 index 000000000..96edc5d0d --- /dev/null +++ b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs @@ -0,0 +1,256 @@ +using System; +using System.Collections.Generic; +using System.Globalization; +using System.IO; +using System.Linq; +using System.Threading; +using System.Threading.Tasks; +using MediaBrowser.Controller; +using MediaBrowser.Controller.Persistence; +using MediaBrowser.Model.FileOrganization; +using MediaBrowser.Model.Logging; +using MediaBrowser.Model.Querying; +using SQLitePCL.pretty; + +namespace Emby.Server.Implementations.Data +{ + public class SqliteFileOrganizationRepository : BaseSqliteRepository, IFileOrganizationRepository, IDisposable + { + private readonly CultureInfo _usCulture = new CultureInfo("en-US"); + + public SqliteFileOrganizationRepository(ILogger logger, IServerApplicationPaths appPaths) : base(logger) + { + DbFilePath = Path.Combine(appPaths.DataPath, "fileorganization.db"); + } + + /// + /// Opens the connection to the database + /// + /// Task. + public void Initialize() + { + using (var connection = CreateConnection()) + { + string[] queries = { + + "create table if not exists FileOrganizerResults (ResultId GUID PRIMARY KEY, OriginalPath TEXT, TargetPath TEXT, FileLength INT, OrganizationDate datetime, Status TEXT, OrganizationType TEXT, StatusMessage TEXT, ExtractedName TEXT, ExtractedYear int null, ExtractedSeasonNumber int null, ExtractedEpisodeNumber int null, ExtractedEndingEpisodeNumber, DuplicatePaths TEXT int null)", + "create index if not exists idx_FileOrganizerResults on FileOrganizerResults(ResultId)" + }; + + connection.RunQueries(queries); + } + } + + public async Task SaveResult(FileOrganizationResult result, CancellationToken cancellationToken) + { + if (result == null) + { + throw new ArgumentNullException("result"); + } + + cancellationToken.ThrowIfCancellationRequested(); + + using (var connection = CreateConnection()) + { + connection.RunInTransaction(db => + { + var paramList = new List(); + var commandText = "replace into FileOrganizerResults (ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; + + paramList.Add(result.Id.ToGuidParamValue()); + paramList.Add(result.OriginalPath); + paramList.Add(result.TargetPath); + paramList.Add(result.FileSize); + paramList.Add(result.Date.ToDateTimeParamValue()); + paramList.Add(result.Status.ToString()); + paramList.Add(result.Type.ToString()); + paramList.Add(result.StatusMessage); + paramList.Add(result.ExtractedName); + paramList.Add(result.ExtractedSeasonNumber); + paramList.Add(result.ExtractedEpisodeNumber); + paramList.Add(result.ExtractedEndingEpisodeNumber); + paramList.Add(string.Join("|", result.DuplicatePaths.ToArray())); + + + db.Execute(commandText, paramList.ToArray()); + }); + } + } + + public async Task Delete(string id) + { + if (string.IsNullOrEmpty(id)) + { + throw new ArgumentNullException("id"); + } + + using (var connection = CreateConnection()) + { + connection.RunInTransaction(db => + { + var paramList = new List(); + var commandText = "delete from FileOrganizerResults where ResultId = ?"; + + paramList.Add(id.ToGuidParamValue()); + + db.Execute(commandText, paramList.ToArray()); + }); + } + } + + public async Task DeleteAll() + { + using (var connection = CreateConnection()) + { + connection.RunInTransaction(db => + { + var commandText = "delete from FileOrganizerResults"; + + db.Execute(commandText); + }); + } + } + + public QueryResult GetResults(FileOrganizationResultQuery query) + { + if (query == null) + { + throw new ArgumentNullException("query"); + } + + using (var connection = CreateConnection(true)) + { + var commandText = "SELECT ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults"; + + if (query.StartIndex.HasValue && query.StartIndex.Value > 0) + { + commandText += string.Format(" WHERE ResultId NOT IN (SELECT ResultId FROM FileOrganizerResults ORDER BY OrganizationDate desc LIMIT {0})", + query.StartIndex.Value.ToString(_usCulture)); + } + + commandText += " ORDER BY OrganizationDate desc"; + + if (query.Limit.HasValue) + { + commandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); + } + + var list = new List(); + var count = connection.Query("select count (ResultId) from FileOrganizerResults").SelectScalarInt().First(); + + foreach (var row in connection.Query(commandText)) + { + list.Add(GetResult(row)); + } + + return new QueryResult() + { + Items = list.ToArray(), + TotalRecordCount = count + }; + } + } + + public FileOrganizationResult GetResult(string id) + { + if (string.IsNullOrEmpty(id)) + { + throw new ArgumentNullException("id"); + } + + using (var connection = CreateConnection(true)) + { + var paramList = new List(); + + paramList.Add(id.ToGuidParamValue()); + + foreach (var row in connection.Query("select ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults where ResultId=?", paramList.ToArray())) + { + return GetResult(row); + } + + return null; + } + } + + public FileOrganizationResult GetResult(IReadOnlyList reader) + { + var index = 0; + + var result = new FileOrganizationResult + { + Id = reader[0].ReadGuid().ToString("N") + }; + + index++; + if (reader[index].SQLiteType != SQLiteType.Null) + { + result.OriginalPath = reader[index].ToString(); + } + + index++; + if (reader[index].SQLiteType != SQLiteType.Null) + { + result.TargetPath = reader[index].ToString(); + } + + index++; + result.FileSize = reader[index].ToInt64(); + + index++; + result.Date = reader[index].ReadDateTime(); + + index++; + result.Status = (FileSortingStatus)Enum.Parse(typeof(FileSortingStatus), reader[index].ToString(), true); + + index++; + result.Type = (FileOrganizerType)Enum.Parse(typeof(FileOrganizerType), reader[index].ToString(), true); + + index++; + if (reader[index].SQLiteType != SQLiteType.Null) + { + result.StatusMessage = reader[index].ToString(); + } + + result.OriginalFileName = Path.GetFileName(result.OriginalPath); + + index++; + if (reader[index].SQLiteType != SQLiteType.Null) + { + result.ExtractedName = reader[index].ToString(); + } + + index++; + if (reader[index].SQLiteType != SQLiteType.Null) + { + result.ExtractedYear = reader[index].ToInt(); + } + + index++; + if (reader[index].SQLiteType != SQLiteType.Null) + { + result.ExtractedSeasonNumber = reader[index].ToInt(); + } + + index++; + if (reader[index].SQLiteType != SQLiteType.Null) + { + result.ExtractedEpisodeNumber = reader[index].ToInt(); + } + + index++; + if (reader[index].SQLiteType != SQLiteType.Null) + { + result.ExtractedEndingEpisodeNumber = reader[index].ToInt(); + } + + index++; + if (reader[index].SQLiteType != SQLiteType.Null) + { + result.DuplicatePaths = reader[index].ToString().Split('|').Where(i => !string.IsNullOrEmpty(i)).ToList(); + } + + return result; + } + } +} -- cgit v1.2.3 From 1dc080df8ba5b9af9245788634d56cb155afd2ba Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Sun, 20 Nov 2016 22:52:58 -0500 Subject: update connections --- .../Activity/ActivityRepository.cs | 16 +- .../Data/BaseSqliteRepository.cs | 58 +- .../Data/SqliteDisplayPreferencesRepository.cs | 26 +- .../Data/SqliteExtensions.cs | 10 +- .../Data/SqliteFileOrganizationRepository.cs | 8 + .../Data/SqliteItemRepository.cs | 799 +++++++++++---------- .../Data/SqliteUserDataRepository.cs | 99 +-- .../Data/SqliteUserRepository.cs | 20 +- .../Notifications/SqliteNotificationsRepository.cs | 25 +- .../Security/AuthenticationRepository.cs | 55 +- .../Social/SharingRepository.cs | 16 +- Emby.Server.Implementations/Sync/SyncRepository.cs | 76 +- 12 files changed, 672 insertions(+), 536 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs') diff --git a/Emby.Server.Implementations/Activity/ActivityRepository.cs b/Emby.Server.Implementations/Activity/ActivityRepository.cs index d730e420a..8a7573d66 100644 --- a/Emby.Server.Implementations/Activity/ActivityRepository.cs +++ b/Emby.Server.Implementations/Activity/ActivityRepository.cs @@ -27,6 +27,14 @@ namespace Emby.Server.Implementations.Activity { using (var connection = CreateConnection()) { + connection.ExecuteAll(string.Join(";", new[] + { + "pragma default_temp_store = memory", + "pragma default_synchronous=Normal", + "pragma temp_store = memory", + "pragma synchronous=Normal", + })); + string[] queries = { "create table if not exists ActivityLogEntries (Id GUID PRIMARY KEY, Name TEXT, Overview TEXT, ShortOverview TEXT, Type TEXT, ItemId TEXT, UserId TEXT, DateCreated DATETIME, LogSeverity TEXT)", @@ -51,9 +59,9 @@ namespace Emby.Server.Implementations.Activity throw new ArgumentNullException("entry"); } - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(db => { @@ -79,9 +87,9 @@ namespace Emby.Server.Implementations.Activity public QueryResult GetActivityLogEntries(DateTime? minDate, int? startIndex, int? limit) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { var commandText = BaseActivitySelectText; var whereClauses = new List(); diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index c506411d4..382c7f245 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -30,11 +30,6 @@ namespace Emby.Server.Implementations.Data get { return false; } } - protected virtual bool EnableConnectionPooling - { - get { return true; } - } - static BaseSqliteRepository() { SQLite3.EnableSharedCache = false; @@ -45,7 +40,7 @@ namespace Emby.Server.Implementations.Data private static bool _versionLogged; - protected virtual SQLiteDatabaseConnection CreateConnection(bool isReadOnly = false) + protected SQLiteDatabaseConnection CreateConnection(bool isReadOnly = false, Action onConnect = null) { if (!_versionLogged) { @@ -56,7 +51,7 @@ namespace Emby.Server.Implementations.Data ConnectionFlags connectionFlags; - //isReadOnly = false; + isReadOnly = false; if (isReadOnly) { @@ -70,46 +65,40 @@ namespace Emby.Server.Implementations.Data connectionFlags |= ConnectionFlags.ReadWrite; } - if (EnableConnectionPooling) - { - connectionFlags |= ConnectionFlags.SharedCached; - } - else - { - connectionFlags |= ConnectionFlags.PrivateCache; - } - + connectionFlags |= ConnectionFlags.SharedCached; connectionFlags |= ConnectionFlags.NoMutex; var db = SQLite3.Open(DbFilePath, connectionFlags, null); var queries = new List { - "pragma default_temp_store = memory", + "pragma temp_store = memory", "PRAGMA page_size=4096", "PRAGMA journal_mode=WAL", - "PRAGMA temp_store=memory", - "PRAGMA synchronous=Normal", + "pragma synchronous=Normal", //"PRAGMA cache size=-10000" }; - var cacheSize = CacheSize; - if (cacheSize.HasValue) - { + //var cacheSize = CacheSize; + //if (cacheSize.HasValue) + //{ - } + //} - if (EnableExclusiveMode) - { - queries.Add("PRAGMA locking_mode=EXCLUSIVE"); - } + ////foreach (var query in queries) + ////{ + //// db.Execute(query); + ////} - //foreach (var query in queries) - //{ - // db.Execute(query); - //} + using (WriteLock.Write()) + { + db.ExecuteAll(string.Join(";", queries.ToArray())); - db.ExecuteAll(string.Join(";", queries.ToArray())); + if (onConnect != null) + { + onConnect(db); + } + } return db; } @@ -122,11 +111,6 @@ namespace Emby.Server.Implementations.Data } } - protected virtual bool EnableExclusiveMode - { - get { return false; } - } - internal static void CheckOk(int rc) { string msg = ""; diff --git a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs index 184caa4d4..ab927ce86 100644 --- a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs @@ -54,9 +54,17 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection()) { + connection.ExecuteAll(string.Join(";", new[] + { + "pragma default_temp_store = memory", + "pragma default_synchronous=Normal", + "pragma temp_store = memory", + "pragma synchronous=Normal", + })); + string[] queries = { - "create table if not exists userdisplaypreferences (id GUID, userId GUID, client text, data BLOB)", + "create table if not exists userdisplaypreferences (id GUID, userId GUID, client text, data BLOB)", "create unique index if not exists userdisplaypreferencesindex on userdisplaypreferences (id, userId, client)" }; @@ -86,9 +94,9 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(db => { @@ -130,9 +138,9 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(db => { @@ -162,9 +170,9 @@ namespace Emby.Server.Implementations.Data var guidId = displayPreferencesId.GetMD5(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where id = @id and userId=@userId and client=@client")) { @@ -196,9 +204,9 @@ namespace Emby.Server.Implementations.Data { var list = new List(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where userId=@userId")) { diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index e4e91626d..5b2549087 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -131,11 +131,13 @@ namespace Emby.Server.Implementations.Data public static void Attach(IDatabaseConnection db, string path, string alias) { - var commandText = string.Format("attach ? as {0};", alias); - var paramList = new List(); - paramList.Add(path); + var commandText = string.Format("attach @path as {0};", alias); - db.Execute(commandText, paramList.ToArray()); + using (var statement = db.PrepareStatement(commandText)) + { + statement.TryBind("@path", path); + statement.MoveNext(); + } } public static bool IsDBNull(this IReadOnlyList result, int index) diff --git a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs index 96edc5d0d..a71682329 100644 --- a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs @@ -31,6 +31,14 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection()) { + connection.ExecuteAll(string.Join(";", new[] + { + "pragma default_temp_store = memory", + "pragma default_synchronous=Normal", + "pragma temp_store = memory", + "pragma synchronous=Normal", + })); + string[] queries = { "create table if not exists FileOrganizerResults (ResultId GUID PRIMARY KEY, OriginalPath TEXT, TargetPath TEXT, FileLength INT, OrganizationDate datetime, Status TEXT, OrganizationType TEXT, StatusMessage TEXT, ExtractedName TEXT, ExtractedYear int null, ExtractedSeasonNumber int null, ExtractedEpisodeNumber int null, ExtractedEndingEpisodeNumber, DuplicatePaths TEXT int null)", diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index c720a8d89..754af9640 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -127,10 +127,19 @@ namespace Emby.Server.Implementations.Data { _connection = CreateConnection(false); + _connection.ExecuteAll(string.Join(";", new[] + { + "pragma default_temp_store = memory", + "pragma default_synchronous=Normal", + "pragma temp_store = memory", + "pragma synchronous=Normal", + })); + var createMediaStreamsTableCommand = "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, TimeBase TEXT NULL, CodecTimeBase TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))"; string[] queries = { + "PRAGMA locking_mode=NORMAL", "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)", @@ -344,25 +353,26 @@ namespace Emby.Server.Implementations.Data _connection.RunQueries(postQueries); - SqliteExtensions.Attach(_connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb"); - userDataRepo.Initialize(_connection, WriteLock); + //SqliteExtensions.Attach(_connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb"); + userDataRepo.Initialize(WriteLock); //await Vacuum(_connection).ConfigureAwait(false); } - protected override bool EnableConnectionPooling - { - get - { - return false; - } - } - protected override bool EnableExclusiveMode + private SQLiteDatabaseConnection CreateConnection(bool readOnly, bool attachUserdata) { - get + Action onConnect = null; + + if (attachUserdata) { - return true; + onConnect = + c => SqliteExtensions.Attach(c, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), + "UserDataDb"); } + + var conn = CreateConnection(readOnly, onConnect); + + return conn; } private readonly string[] _retriveItemColumns = @@ -635,12 +645,15 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - _connection.RunInTransaction(db => + using (WriteLock.Write()) { - SaveItemsInTranscation(db, items); - }); + connection.RunInTransaction(db => + { + SaveItemsInTranscation(db, items); + }); + } } } @@ -1170,15 +1183,18 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - using (WriteLock.Write()) + using (var connection = CreateConnection(true)) { - using (var statement = _connection.PrepareStatement("select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid")) + using (WriteLock.Read()) { - statement.TryBind("@guid", id); - - foreach (var row in statement.ExecuteQuery()) + using (var statement = connection.PrepareStatement("select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid")) { - return GetItem(row); + statement.TryBind("@guid", id); + + foreach (var row in statement.ExecuteQuery()) + { + return GetItem(row); + } } } } @@ -1976,15 +1992,18 @@ namespace Emby.Server.Implementations.Data var list = new List(); - using (WriteLock.Write()) + using (var connection = CreateConnection(true)) { - using (var statement = _connection.PrepareStatement("select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc")) + using (WriteLock.Read()) { - statement.TryBind("@ItemId", id); - - foreach (var row in statement.ExecuteQuery()) + using (var statement = connection.PrepareStatement("select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc")) { - list.Add(GetChapter(row)); + statement.TryBind("@ItemId", id); + + foreach (var row in statement.ExecuteQuery()) + { + list.Add(GetChapter(row)); + } } } } @@ -2007,16 +2026,19 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException("id"); } - using (WriteLock.Write()) + using (var connection = CreateConnection(true)) { - using (var statement = _connection.PrepareStatement("select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex")) + using (WriteLock.Read()) { - statement.TryBind("@ItemId", id); - statement.TryBind("@ChapterIndex", index); - - foreach (var row in statement.ExecuteQuery()) + using (var statement = connection.PrepareStatement("select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex")) { - return GetChapter(row); + statement.TryBind("@ItemId", id); + statement.TryBind("@ChapterIndex", index); + + foreach (var row in statement.ExecuteQuery()) + { + return GetChapter(row); + } } } } @@ -2085,35 +2107,38 @@ namespace Emby.Server.Implementations.Data var index = 0; - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - _connection.RunInTransaction(db => + using (WriteLock.Write()) { - // First delete chapters - _connection.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", id.ToGuidParamValue()); - - using (var saveChapterStatement = db.PrepareStatement("replace into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath, ImageDateModified) values (@ItemId, @ChapterIndex, @StartPositionTicks, @Name, @ImagePath, @ImageDateModified)")) + connection.RunInTransaction(db => { - foreach (var chapter in chapters) + // First delete chapters + db.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", id.ToGuidParamValue()); + + using (var saveChapterStatement = db.PrepareStatement("replace into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath, ImageDateModified) values (@ItemId, @ChapterIndex, @StartPositionTicks, @Name, @ImagePath, @ImageDateModified)")) { - if (index > 0) + foreach (var chapter in chapters) { - saveChapterStatement.Reset(); - } + if (index > 0) + { + saveChapterStatement.Reset(); + } - saveChapterStatement.TryBind("@ItemId", id.ToGuidParamValue()); - saveChapterStatement.TryBind("@ChapterIndex", index); - saveChapterStatement.TryBind("@StartPositionTicks", chapter.StartPositionTicks); - saveChapterStatement.TryBind("@Name", chapter.Name); - saveChapterStatement.TryBind("@ImagePath", chapter.ImagePath); - saveChapterStatement.TryBind("@ImageDateModified", chapter.ImageDateModified); + saveChapterStatement.TryBind("@ItemId", id.ToGuidParamValue()); + saveChapterStatement.TryBind("@ChapterIndex", index); + saveChapterStatement.TryBind("@StartPositionTicks", chapter.StartPositionTicks); + saveChapterStatement.TryBind("@Name", chapter.Name); + saveChapterStatement.TryBind("@ImagePath", chapter.ImagePath); + saveChapterStatement.TryBind("@ImageDateModified", chapter.ImageDateModified); - saveChapterStatement.MoveNext(); + saveChapterStatement.MoveNext(); - index++; + index++; + } } - } - }); + }); + } } } @@ -2383,31 +2408,34 @@ namespace Emby.Server.Implementations.Data } } - using (WriteLock.Write()) + using (var connection = CreateConnection(true, EnableJoinUserData(query))) { - using (var statement = _connection.PrepareStatement(commandText)) + using (WriteLock.Read()) { - if (EnableJoinUserData(query)) + using (var statement = connection.PrepareStatement(commandText)) { - statement.TryBind("@UserId", query.User.Id); - } + if (EnableJoinUserData(query)) + { + 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()) - { - var item = GetItem(row, query); - if (item != null) + foreach (var row in statement.ExecuteQuery()) { - list.Add(item); + var item = GetItem(row, query); + if (item != null) + { + list.Add(item); + } } } - } - LogQueryTime("GetItemList", commandText, now); + LogQueryTime("GetItemList", commandText, now); + } } // Hack for right now since we currently don't support filtering out these duplicates within a query @@ -2548,72 +2576,75 @@ namespace Emby.Server.Implementations.Data } } - using (WriteLock.Write()) + using (var connection = CreateConnection(true, EnableJoinUserData(query))) { - var totalRecordCount = 0; - var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; - - if (!isReturningZeroItems) + using (WriteLock.Read()) { - using (var statement = _connection.PrepareStatement(commandText)) + var totalRecordCount = 0; + var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; + + if (!isReturningZeroItems) { - if (EnableJoinUserData(query)) + using (var statement = connection.PrepareStatement(commandText)) { - statement.TryBind("@UserId", query.User.Id); - } + if (EnableJoinUserData(query)) + { + 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()) - { - var item = GetItem(row, query); - if (item != null) + foreach (var row in statement.ExecuteQuery()) { - list.Add(item); + var item = GetItem(row, query); + if (item != null) + { + list.Add(item); + } } } } - } - commandText = string.Empty; + commandText = string.Empty; - if (EnableGroupByPresentationUniqueKey(query)) - { - commandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); - } - else - { - commandText += " select count (guid)" + GetFromText(); - } + if (EnableGroupByPresentationUniqueKey(query)) + { + commandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); + } + else + { + commandText += " select count (guid)" + GetFromText(); + } - commandText += GetJoinUserDataText(query); - commandText += whereTextWithoutPaging; + commandText += GetJoinUserDataText(query); + commandText += whereTextWithoutPaging; - using (var statement = _connection.PrepareStatement(commandText)) - { - if (EnableJoinUserData(query)) + using (var statement = connection.PrepareStatement(commandText)) { - statement.TryBind("@UserId", query.User.Id); - } + if (EnableJoinUserData(query)) + { + 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); - totalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); - } + totalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); + } - LogQueryTime("GetItems", commandText, now); + LogQueryTime("GetItems", commandText, now); - return new QueryResult() - { - Items = list.ToArray(), - TotalRecordCount = totalRecordCount - }; + return new QueryResult() + { + Items = list.ToArray(), + TotalRecordCount = totalRecordCount + }; + } } } @@ -2774,29 +2805,32 @@ namespace Emby.Server.Implementations.Data var list = new List(); - using (WriteLock.Write()) + using (var connection = CreateConnection(true, EnableJoinUserData(query))) { - using (var statement = _connection.PrepareStatement(commandText)) + using (WriteLock.Read()) { - if (EnableJoinUserData(query)) + using (var statement = connection.PrepareStatement(commandText)) { - statement.TryBind("@UserId", query.User.Id); - } + if (EnableJoinUserData(query)) + { + 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); + LogQueryTime("GetItemList", commandText, now); - return list; + return list; + } } } @@ -2842,34 +2876,37 @@ namespace Emby.Server.Implementations.Data var list = new List>(); - using (WriteLock.Write()) + using (var connection = CreateConnection(true, EnableJoinUserData(query))) { - using (var statement = _connection.PrepareStatement(commandText)) + using (WriteLock.Read()) { - if (EnableJoinUserData(query)) + using (var statement = connection.PrepareStatement(commandText)) { - statement.TryBind("@UserId", query.User.Id); - } - - // Running this again will bind the params - GetWhereClauses(query, statement); + if (EnableJoinUserData(query)) + { + statement.TryBind("@UserId", query.User.Id); + } - foreach (var row in statement.ExecuteQuery()) - { - var id = row.GetGuid(0); - string path = null; + // Running this again will bind the params + GetWhereClauses(query, statement); - if (!row.IsDBNull(1)) + foreach (var row in statement.ExecuteQuery()) { - path = row.GetString(1); + var id = row.GetGuid(0); + string path = null; + + if (!row.IsDBNull(1)) + { + path = row.GetString(1); + } + list.Add(new Tuple(id, path)); } - list.Add(new Tuple(id, path)); } - } - LogQueryTime("GetItemIdsWithPath", commandText, now); + LogQueryTime("GetItemIdsWithPath", commandText, now); - return list; + return list; + } } } @@ -2928,64 +2965,67 @@ namespace Emby.Server.Implementations.Data var list = new List(); - using (WriteLock.Write()) + using (var connection = CreateConnection(true, EnableJoinUserData(query))) { - var totalRecordCount = 0; - - using (var statement = _connection.PrepareStatement(commandText)) + using (WriteLock.Read()) { - if (EnableJoinUserData(query)) + var totalRecordCount = 0; + + using (var statement = connection.PrepareStatement(commandText)) { - statement.TryBind("@UserId", query.User.Id); - } + if (EnableJoinUserData(query)) + { + 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()); + } } - } - commandText = string.Empty; + commandText = string.Empty; - if (EnableGroupByPresentationUniqueKey(query)) - { - commandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); - } - else - { - commandText += " select count (guid)" + GetFromText(); - } + if (EnableGroupByPresentationUniqueKey(query)) + { + commandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); + } + else + { + commandText += " select count (guid)" + GetFromText(); + } - commandText += GetJoinUserDataText(query); - commandText += whereTextWithoutPaging; + commandText += GetJoinUserDataText(query); + commandText += whereTextWithoutPaging; - using (var statement = _connection.PrepareStatement(commandText)) - { - if (EnableJoinUserData(query)) + using (var statement = connection.PrepareStatement(commandText)) { - statement.TryBind("@UserId", query.User.Id); - } + if (EnableJoinUserData(query)) + { + 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); - totalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); - } + totalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); + } - LogQueryTime("GetItemIds", commandText, now); + LogQueryTime("GetItemIds", commandText, now); - return new QueryResult() - { - Items = list.ToArray(), - TotalRecordCount = totalRecordCount - }; + return new QueryResult() + { + Items = list.ToArray(), + TotalRecordCount = totalRecordCount + }; + } } } @@ -4289,33 +4329,36 @@ namespace Emby.Server.Implementations.Data var commandText = "select Guid,InheritedTags,(select group_concat(Tags, '|') from TypedBaseItems where (guid=outer.guid) OR (guid in (Select AncestorId from AncestorIds where ItemId=Outer.guid))) as NewInheritedTags from typedbaseitems as Outer where NewInheritedTags <> InheritedTags"; - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - foreach (var row in _connection.Query(commandText)) + using (WriteLock.Write()) { - var id = row.GetGuid(0); - string value = row.IsDBNull(2) ? null : row.GetString(2); + foreach (var row in connection.Query(commandText)) + { + var id = row.GetGuid(0); + string value = row.IsDBNull(2) ? null : row.GetString(2); - newValues.Add(new Tuple(id, value)); - } + newValues.Add(new Tuple(id, value)); + } - Logger.Debug("UpdateInheritedTags - {0} rows", newValues.Count); - if (newValues.Count == 0) - { - return; - } + Logger.Debug("UpdateInheritedTags - {0} rows", newValues.Count); + if (newValues.Count == 0) + { + return; + } - // write lock here - using (var statement = _connection.PrepareStatement("Update TypedBaseItems set InheritedTags=@InheritedTags where Guid=@Guid")) - { - foreach (var item in newValues) + // write lock here + using (var statement = connection.PrepareStatement("Update TypedBaseItems set InheritedTags=@InheritedTags where Guid=@Guid")) { - var paramList = new List(); + foreach (var item in newValues) + { + var paramList = new List(); - paramList.Add(item.Item1); - paramList.Add(item.Item2); + paramList.Add(item.Item1); + paramList.Add(item.Item2); - statement.Execute(paramList.ToArray()); + statement.Execute(paramList.ToArray()); + } } } } @@ -4360,28 +4403,31 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - _connection.RunInTransaction(db => + using (WriteLock.Write()) { - // Delete people - ExecuteWithSingleParam(db, "delete from People where ItemId=@Id", id.ToGuidParamValue()); + connection.RunInTransaction(db => + { + // Delete people + ExecuteWithSingleParam(db, "delete from People where ItemId=@Id", id.ToGuidParamValue()); - // Delete chapters - ExecuteWithSingleParam(db, "delete from " + ChaptersTableName + " where ItemId=@Id", id.ToGuidParamValue()); + // Delete chapters + ExecuteWithSingleParam(db, "delete from " + ChaptersTableName + " where ItemId=@Id", id.ToGuidParamValue()); - // Delete media streams - ExecuteWithSingleParam(db, "delete from mediastreams where ItemId=@Id", id.ToGuidParamValue()); + // Delete media streams + ExecuteWithSingleParam(db, "delete from mediastreams where ItemId=@Id", id.ToGuidParamValue()); - // Delete ancestors - ExecuteWithSingleParam(db, "delete from AncestorIds where ItemId=@Id", id.ToGuidParamValue()); + // Delete ancestors + ExecuteWithSingleParam(db, "delete from AncestorIds where ItemId=@Id", id.ToGuidParamValue()); - // Delete item values - ExecuteWithSingleParam(db, "delete from ItemValues where ItemId=@Id", id.ToGuidParamValue()); + // Delete item values + ExecuteWithSingleParam(db, "delete from ItemValues where ItemId=@Id", id.ToGuidParamValue()); - // Delete the item - ExecuteWithSingleParam(db, "delete from TypedBaseItems where guid=@Id", id.ToGuidParamValue()); - }); + // Delete the item + ExecuteWithSingleParam(db, "delete from TypedBaseItems where guid=@Id", id.ToGuidParamValue()); + }); + } } } @@ -4417,19 +4463,22 @@ namespace Emby.Server.Implementations.Data var list = new List(); - using (WriteLock.Write()) + using (var connection = CreateConnection(true)) { - using (var statement = _connection.PrepareStatement(commandText)) + using (WriteLock.Read()) { - // Run this again to bind the params - GetPeopleWhereClauses(query, statement); - - foreach (var row in statement.ExecuteQuery()) + using (var statement = connection.PrepareStatement(commandText)) { - list.Add(row.GetString(0)); + // Run this again to bind the params + GetPeopleWhereClauses(query, statement); + + foreach (var row in statement.ExecuteQuery()) + { + list.Add(row.GetString(0)); + } } + return list; } - return list; } } @@ -4455,16 +4504,19 @@ namespace Emby.Server.Implementations.Data var list = new List(); - using (WriteLock.Write()) + using (var connection = CreateConnection(true)) { - using (var statement = _connection.PrepareStatement(commandText)) + using (WriteLock.Read()) { - // Run this again to bind the params - GetPeopleWhereClauses(query, statement); - - foreach (var row in statement.ExecuteQuery()) + using (var statement = connection.PrepareStatement(commandText)) { - list.Add(GetPerson(row)); + // Run this again to bind the params + GetPeopleWhereClauses(query, statement); + + foreach (var row in statement.ExecuteQuery()) + { + list.Add(GetPerson(row)); + } } } } @@ -4667,13 +4719,16 @@ namespace Emby.Server.Implementations.Data commandText += " Group By CleanValue"; - using (WriteLock.Write()) + using (var connection = CreateConnection(true)) { - foreach (var row in _connection.Query(commandText)) + using (WriteLock.Write()) { - if (!row.IsDBNull(0)) + foreach (var row in connection.Query(commandText)) { - list.Add(row.GetString(0)); + if (!row.IsDBNull(0)) + { + list.Add(row.GetString(0)); + } } } } @@ -4825,67 +4880,70 @@ namespace Emby.Server.Implementations.Data var list = new List>(); var count = 0; - using (WriteLock.Write()) + using (var connection = CreateConnection(true, EnableJoinUserData(query))) { - if (!isReturningZeroItems) + using (WriteLock.Read()) { - using (var statement = _connection.PrepareStatement(commandText)) + if (!isReturningZeroItems) { - statement.TryBind("@SelectType", returnType); - if (EnableJoinUserData(query)) + using (var statement = connection.PrepareStatement(commandText)) { - statement.TryBind("@UserId", query.User.Id); - } + statement.TryBind("@SelectType", returnType); + if (EnableJoinUserData(query)) + { + statement.TryBind("@UserId", query.User.Id); + } - if (typeSubQuery != null) - { - GetWhereClauses(typeSubQuery, null, "itemTypes"); - } - BindSimilarParams(query, statement); - GetWhereClauses(innerQuery, statement); - GetWhereClauses(outerQuery, statement); + if (typeSubQuery != null) + { + GetWhereClauses(typeSubQuery, null, "itemTypes"); + } + BindSimilarParams(query, statement); + GetWhereClauses(innerQuery, statement); + GetWhereClauses(outerQuery, statement); - foreach (var row in statement.ExecuteQuery()) - { - var item = GetItem(row); - if (item != null) + foreach (var row in statement.ExecuteQuery()) { - var countStartColumn = columns.Count - 1; + var item = GetItem(row); + if (item != null) + { + var countStartColumn = columns.Count - 1; - list.Add(new Tuple(item, GetItemCounts(row, countStartColumn, typesToCount))); + list.Add(new Tuple(item, GetItemCounts(row, countStartColumn, typesToCount))); + } } - } - LogQueryTime("GetItemValues", commandText, now); + LogQueryTime("GetItemValues", commandText, now); + } } - } - if (query.EnableTotalRecordCount) - { - commandText = "select count (distinct PresentationUniqueKey)" + GetFromText(); + if (query.EnableTotalRecordCount) + { + commandText = "select count (distinct PresentationUniqueKey)" + GetFromText(); - commandText += GetJoinUserDataText(query); - commandText += whereText; + commandText += GetJoinUserDataText(query); + commandText += whereText; - using (var statement = _connection.PrepareStatement(commandText)) - { - statement.TryBind("@SelectType", returnType); - if (EnableJoinUserData(query)) + using (var statement = connection.PrepareStatement(commandText)) { - statement.TryBind("@UserId", query.User.Id); - } + statement.TryBind("@SelectType", returnType); + if (EnableJoinUserData(query)) + { + statement.TryBind("@UserId", query.User.Id); + } - if (typeSubQuery != null) - { - GetWhereClauses(typeSubQuery, null, "itemTypes"); - } - BindSimilarParams(query, statement); - GetWhereClauses(innerQuery, statement); - GetWhereClauses(outerQuery, statement); + if (typeSubQuery != null) + { + GetWhereClauses(typeSubQuery, null, "itemTypes"); + } + BindSimilarParams(query, statement); + GetWhereClauses(innerQuery, statement); + GetWhereClauses(outerQuery, statement); - count = statement.ExecuteQuery().SelectScalarInt().First(); + count = statement.ExecuteQuery().SelectScalarInt().First(); - LogQueryTime("GetItemValues", commandText, now); + LogQueryTime("GetItemValues", commandText, now); + } } } } @@ -5043,33 +5101,35 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - // First delete - // "delete from People where ItemId=?" - _connection.Execute("delete from People where ItemId=?", itemId.ToGuidParamValue()); + using (WriteLock.Write()) + { // First delete + // "delete from People where ItemId=?" + connection.Execute("delete from People where ItemId=?", itemId.ToGuidParamValue()); - var listIndex = 0; + var listIndex = 0; - using (var statement = _connection.PrepareStatement( - "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values (@ItemId, @Name, @Role, @PersonType, @SortOrder, @ListOrder)")) - { - foreach (var person in people) + using (var statement = connection.PrepareStatement( + "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values (@ItemId, @Name, @Role, @PersonType, @SortOrder, @ListOrder)")) { - if (listIndex > 0) + foreach (var person in people) { - statement.Reset(); - } + if (listIndex > 0) + { + statement.Reset(); + } - statement.TryBind("@ItemId", itemId.ToGuidParamValue()); - statement.TryBind("@Name", person.Name); - statement.TryBind("@Role", person.Role); - statement.TryBind("@PersonType", person.Type); - statement.TryBind("@SortOrder", person.SortOrder); - statement.TryBind("@ListOrder", listIndex); + statement.TryBind("@ItemId", itemId.ToGuidParamValue()); + statement.TryBind("@Name", person.Name); + statement.TryBind("@Role", person.Role); + statement.TryBind("@PersonType", person.Type); + statement.TryBind("@SortOrder", person.SortOrder); + statement.TryBind("@ListOrder", listIndex); - statement.MoveNext(); - listIndex++; + statement.MoveNext(); + listIndex++; + } } } } @@ -5127,25 +5187,28 @@ namespace Emby.Server.Implementations.Data cmdText += " order by StreamIndex ASC"; - using (WriteLock.Write()) + using (var connection = CreateConnection(true)) { - using (var statement = _connection.PrepareStatement(cmdText)) + using (WriteLock.Read()) { - statement.TryBind("@ItemId", query.ItemId.ToGuidParamValue()); - - if (query.Type.HasValue) + using (var statement = connection.PrepareStatement(cmdText)) { - statement.TryBind("@StreamType", query.Type.Value.ToString()); - } + statement.TryBind("@ItemId", query.ItemId.ToGuidParamValue()); - if (query.Index.HasValue) - { - statement.TryBind("@StreamIndex", query.Index.Value); - } + if (query.Type.HasValue) + { + 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); + } + + foreach (var row in statement.ExecuteQuery()) + { + list.Add(GetMediaStream(row)); + } } } } @@ -5169,58 +5232,60 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - // First delete chapters - _connection.Execute("delete from mediastreams where ItemId=@ItemId", id.ToGuidParamValue()); + using (WriteLock.Write()) + { // First delete chapters + connection.Execute("delete from mediastreams where ItemId=@ItemId", id.ToGuidParamValue()); - using (var statement = _connection.PrepareStatement(string.Format("replace into mediastreams ({0}) values ({1})", - string.Join(",", _mediaStreamSaveColumns), - string.Join(",", _mediaStreamSaveColumns.Select(i => "@" + i).ToArray())))) - { - foreach (var stream in streams) + using (var statement = connection.PrepareStatement(string.Format("replace into mediastreams ({0}) values ({1})", + string.Join(",", _mediaStreamSaveColumns), + string.Join(",", _mediaStreamSaveColumns.Select(i => "@" + i).ToArray())))) { - var paramList = new List(); - - paramList.Add(id.ToGuidParamValue()); - paramList.Add(stream.Index); - paramList.Add(stream.Type.ToString()); - paramList.Add(stream.Codec); - paramList.Add(stream.Language); - paramList.Add(stream.ChannelLayout); - paramList.Add(stream.Profile); - paramList.Add(stream.AspectRatio); - paramList.Add(stream.Path); - - paramList.Add(stream.IsInterlaced); - paramList.Add(stream.BitRate); - paramList.Add(stream.Channels); - paramList.Add(stream.SampleRate); - - paramList.Add(stream.IsDefault); - paramList.Add(stream.IsForced); - paramList.Add(stream.IsExternal); - - paramList.Add(stream.Width); - paramList.Add(stream.Height); - paramList.Add(stream.AverageFrameRate); - paramList.Add(stream.RealFrameRate); - paramList.Add(stream.Level); - paramList.Add(stream.PixelFormat); - paramList.Add(stream.BitDepth); - paramList.Add(stream.IsExternal); - paramList.Add(stream.RefFrames); - - paramList.Add(stream.CodecTag); - paramList.Add(stream.Comment); - paramList.Add(stream.NalLengthSize); - paramList.Add(stream.IsAVC); - paramList.Add(stream.Title); - - paramList.Add(stream.TimeBase); - paramList.Add(stream.CodecTimeBase); - - statement.Execute(paramList.ToArray()); + foreach (var stream in streams) + { + var paramList = new List(); + + paramList.Add(id.ToGuidParamValue()); + paramList.Add(stream.Index); + paramList.Add(stream.Type.ToString()); + paramList.Add(stream.Codec); + paramList.Add(stream.Language); + paramList.Add(stream.ChannelLayout); + paramList.Add(stream.Profile); + paramList.Add(stream.AspectRatio); + paramList.Add(stream.Path); + + paramList.Add(stream.IsInterlaced); + paramList.Add(stream.BitRate); + paramList.Add(stream.Channels); + paramList.Add(stream.SampleRate); + + paramList.Add(stream.IsDefault); + paramList.Add(stream.IsForced); + paramList.Add(stream.IsExternal); + + paramList.Add(stream.Width); + paramList.Add(stream.Height); + paramList.Add(stream.AverageFrameRate); + paramList.Add(stream.RealFrameRate); + paramList.Add(stream.Level); + paramList.Add(stream.PixelFormat); + paramList.Add(stream.BitDepth); + paramList.Add(stream.IsExternal); + paramList.Add(stream.RefFrames); + + paramList.Add(stream.CodecTag); + paramList.Add(stream.Comment); + paramList.Add(stream.NalLengthSize); + paramList.Add(stream.IsAVC); + paramList.Add(stream.Title); + + paramList.Add(stream.TimeBase); + paramList.Add(stream.CodecTimeBase); + + statement.Execute(paramList.ToArray()); + } } } } diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index a66536179..0b4768c14 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -14,19 +14,12 @@ namespace Emby.Server.Implementations.Data { public class SqliteUserDataRepository : BaseSqliteRepository, IUserDataRepository { - private SQLiteDatabaseConnection _connection; - public SqliteUserDataRepository(ILogger logger, IApplicationPaths appPaths) : base(logger) { DbFilePath = Path.Combine(appPaths.DataPath, "userdata_v2.db"); } - protected override bool EnableConnectionPooling - { - get { return false; } - } - /// /// Gets the name of the repository /// @@ -43,13 +36,24 @@ namespace Emby.Server.Implementations.Data /// Opens the connection to the database /// /// Task. - public void Initialize(SQLiteDatabaseConnection connection, ReaderWriterLockSlim writeLock) + public void Initialize(ReaderWriterLockSlim writeLock) { WriteLock.Dispose(); WriteLock = writeLock; - _connection = connection; - string[] queries = { + using (var connection = CreateConnection()) + { + connection.ExecuteAll(string.Join(";", new[] + { + "pragma default_temp_store = memory", + "pragma default_synchronous=Normal", + "pragma temp_store = memory", + "pragma synchronous=Normal", + })); + + string[] queries = { + + "PRAGMA locking_mode=NORMAL", "create table if not exists UserDataDb.userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null)", @@ -69,15 +73,16 @@ namespace Emby.Server.Implementations.Data "pragma shrink_memory" }; - _connection.RunQueries(queries); + connection.RunQueries(queries); - connection.RunInTransaction(db => - { - var existingColumnNames = GetColumnNames(db, "userdata"); + connection.RunInTransaction(db => + { + var existingColumnNames = GetColumnNames(db, "userdata"); - AddColumn(db, "userdata", "AudioStreamIndex", "int", existingColumnNames); - AddColumn(db, "userdata", "SubtitleStreamIndex", "int", existingColumnNames); - }); + AddColumn(db, "userdata", "AudioStreamIndex", "int", existingColumnNames); + AddColumn(db, "userdata", "SubtitleStreamIndex", "int", existingColumnNames); + }); + } } /// @@ -139,18 +144,21 @@ namespace Emby.Server.Implementations.Data { cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - _connection.RunInTransaction(db => + using (WriteLock.Write()) { - SaveUserData(db, userId, key, userData); - }); + connection.RunInTransaction(db => + { + SaveUserData(db, userId, key, userData); + }); + } } } private void SaveUserData(IDatabaseConnection db, Guid userId, string key, UserItemData userData) { - using (var statement = _connection.PrepareStatement("replace into userdata (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)")) + using (var statement = db.PrepareStatement("replace into userdata (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)")) { statement.TryBind("@UserId", userId.ToGuidParamValue()); statement.TryBind("@Key", key); @@ -207,15 +215,18 @@ namespace Emby.Server.Implementations.Data { cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - _connection.RunInTransaction(db => + using (WriteLock.Write()) { - foreach (var userItemData in userDataList) + connection.RunInTransaction(db => { - SaveUserData(db, userId, userItemData.Key, userItemData); - } - }); + foreach (var userItemData in userDataList) + { + SaveUserData(db, userId, userItemData.Key, userItemData); + } + }); + } } } @@ -241,16 +252,19 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException("key"); } - using (WriteLock.Write()) + using (var connection = CreateConnection(true)) { - 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 (WriteLock.Read()) { - statement.TryBind("@UserId", userId.ToGuidParamValue()); - statement.TryBind("@Key", key); - - foreach (var row in statement.ExecuteQuery()) + using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where key =@Key and userId=@UserId")) { - return ReadRow(row); + statement.TryBind("@UserId", userId.ToGuidParamValue()); + statement.TryBind("@Key", key); + + foreach (var row in statement.ExecuteQuery()) + { + return ReadRow(row); + } } } } @@ -291,15 +305,18 @@ namespace Emby.Server.Implementations.Data var list = new List(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var statement = _connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@UserId")) + using (WriteLock.Read()) { - statement.TryBind("@UserId", userId.ToGuidParamValue()); - - foreach (var row in statement.ExecuteQuery()) + using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@UserId")) { - list.Add(ReadRow(row)); + statement.TryBind("@UserId", userId.ToGuidParamValue()); + + foreach (var row in statement.ExecuteQuery()) + { + list.Add(ReadRow(row)); + } } } } diff --git a/Emby.Server.Implementations/Data/SqliteUserRepository.cs b/Emby.Server.Implementations/Data/SqliteUserRepository.cs index 5e148d95a..0a2f9e7cd 100644 --- a/Emby.Server.Implementations/Data/SqliteUserRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserRepository.cs @@ -50,6 +50,14 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection()) { + connection.ExecuteAll(string.Join(";", new[] + { + "pragma default_temp_store = memory", + "pragma default_synchronous=Normal", + "pragma temp_store = memory", + "pragma synchronous=Normal", + })); + string[] queries = { "create table if not exists users (guid GUID primary key, data BLOB)", @@ -83,9 +91,9 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(db => { @@ -108,9 +116,9 @@ namespace Emby.Server.Implementations.Data { var list = new List(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { foreach (var row in connection.Query("select guid,data from users")) { @@ -146,9 +154,9 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(db => { diff --git a/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs b/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs index 5a3b64dbb..5fc691527 100644 --- a/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs +++ b/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs @@ -29,7 +29,16 @@ namespace Emby.Server.Implementations.Notifications { using (var connection = CreateConnection()) { + connection.ExecuteAll(string.Join(";", new[] + { + "pragma default_temp_store = memory", + "pragma default_synchronous=Normal", + "pragma temp_store = memory", + "pragma synchronous=Normal", + })); + string[] queries = { + "create table if not exists Notifications (Id GUID NOT NULL, UserId GUID NOT NULL, Date DATETIME NOT NULL, Name TEXT NOT NULL, Description TEXT NULL, Url TEXT NULL, Level TEXT NOT NULL, IsRead BOOLEAN NOT NULL, Category TEXT NOT NULL, RelatedId TEXT NULL, PRIMARY KEY (Id, UserId))", "create index if not exists idx_Notifications1 on Notifications(Id)", "create index if not exists idx_Notifications2 on Notifications(UserId)" @@ -103,9 +112,9 @@ namespace Emby.Server.Implementations.Notifications { var result = new NotificationsSummary(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { using (var statement = connection.PrepareStatement("select Level from Notifications where UserId=@UserId and IsRead=@IsRead")) { @@ -220,9 +229,9 @@ namespace Emby.Server.Implementations.Notifications cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(conn => { @@ -283,9 +292,9 @@ namespace Emby.Server.Implementations.Notifications { cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(conn => { @@ -305,9 +314,9 @@ namespace Emby.Server.Implementations.Notifications { cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(conn => { diff --git a/Emby.Server.Implementations/Security/AuthenticationRepository.cs b/Emby.Server.Implementations/Security/AuthenticationRepository.cs index a9141f153..0a598e9a7 100644 --- a/Emby.Server.Implementations/Security/AuthenticationRepository.cs +++ b/Emby.Server.Implementations/Security/AuthenticationRepository.cs @@ -30,9 +30,17 @@ namespace Emby.Server.Implementations.Security { using (var connection = CreateConnection()) { + connection.ExecuteAll(string.Join(";", new[] + { + "pragma default_temp_store = memory", + "pragma default_synchronous=Normal", + "pragma temp_store = memory", + "pragma synchronous=Normal", + })); + string[] queries = { - "create table if not exists AccessTokens (Id GUID PRIMARY KEY, AccessToken TEXT NOT NULL, DeviceId TEXT, AppName TEXT, AppVersion TEXT, DeviceName TEXT, UserId TEXT, IsActive BIT, DateCreated DATETIME NOT NULL, DateRevoked DATETIME)", + "create table if not exists AccessTokens (Id GUID PRIMARY KEY, AccessToken TEXT NOT NULL, DeviceId TEXT, AppName TEXT, AppVersion TEXT, DeviceName TEXT, UserId TEXT, IsActive BIT, DateCreated DATETIME NOT NULL, DateRevoked DATETIME)", "create index if not exists idx_AccessTokens on AccessTokens(Id)" }; @@ -63,9 +71,9 @@ namespace Emby.Server.Implementations.Security cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(db => { @@ -200,28 +208,31 @@ namespace Emby.Server.Implementations.Security var list = new List(); - using (var statement = connection.PrepareStatement(commandText)) + using (WriteLock.Read()) { - BindAuthenticationQueryParams(query, statement); - - foreach (var row in statement.ExecuteQuery()) - { - list.Add(Get(row)); - } - - using (var totalCountStatement = connection.PrepareStatement("select count (Id) from AccessTokens" + whereTextWithoutPaging)) + using (var statement = connection.PrepareStatement(commandText)) { - BindAuthenticationQueryParams(query, totalCountStatement); + BindAuthenticationQueryParams(query, statement); - var count = totalCountStatement.ExecuteQuery() - .SelectScalarInt() - .First(); + foreach (var row in statement.ExecuteQuery()) + { + list.Add(Get(row)); + } - return new QueryResult() + using (var totalCountStatement = connection.PrepareStatement("select count (Id) from AccessTokens" + whereTextWithoutPaging)) { - Items = list.ToArray(), - TotalRecordCount = count - }; + BindAuthenticationQueryParams(query, totalCountStatement); + + var count = totalCountStatement.ExecuteQuery() + .SelectScalarInt() + .First(); + + return new QueryResult() + { + Items = list.ToArray(), + TotalRecordCount = count + }; + } } } } @@ -234,9 +245,9 @@ namespace Emby.Server.Implementations.Security throw new ArgumentNullException("id"); } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { var commandText = BaseSelectText + " where Id=@Id"; diff --git a/Emby.Server.Implementations/Social/SharingRepository.cs b/Emby.Server.Implementations/Social/SharingRepository.cs index 9065ee108..e0ee62780 100644 --- a/Emby.Server.Implementations/Social/SharingRepository.cs +++ b/Emby.Server.Implementations/Social/SharingRepository.cs @@ -27,6 +27,14 @@ namespace Emby.Server.Implementations.Social { using (var connection = CreateConnection()) { + connection.ExecuteAll(string.Join(";", new[] + { + "pragma default_temp_store = memory", + "pragma default_synchronous=Normal", + "pragma temp_store = memory", + "pragma synchronous=Normal", + })); + string[] queries = { "create table if not exists Shares (Id GUID, ItemId TEXT, UserId TEXT, ExpirationDate DateTime, PRIMARY KEY (Id))", @@ -50,9 +58,9 @@ namespace Emby.Server.Implementations.Social throw new ArgumentNullException("info.Id"); } - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(db => { @@ -75,9 +83,9 @@ namespace Emby.Server.Implementations.Social throw new ArgumentNullException("id"); } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { var commandText = "select Id, ItemId, UserId, ExpirationDate from Shares where id = ?"; diff --git a/Emby.Server.Implementations/Sync/SyncRepository.cs b/Emby.Server.Implementations/Sync/SyncRepository.cs index 6fb918f15..36558142b 100644 --- a/Emby.Server.Implementations/Sync/SyncRepository.cs +++ b/Emby.Server.Implementations/Sync/SyncRepository.cs @@ -43,6 +43,14 @@ namespace Emby.Server.Implementations.Sync { using (var connection = CreateConnection()) { + connection.ExecuteAll(string.Join(";", new[] + { + "pragma default_temp_store = memory", + "pragma default_synchronous=Normal", + "pragma temp_store = memory", + "pragma synchronous=Normal", + })); + string[] queries = { "create table if not exists SyncJobs (Id GUID PRIMARY KEY, TargetId TEXT NOT NULL, Name TEXT NOT NULL, Profile TEXT, Quality TEXT, Bitrate INT, Status TEXT NOT NULL, Progress FLOAT, UserId TEXT NOT NULL, ItemIds TEXT NOT NULL, Category TEXT, ParentId TEXT, UnwatchedOnly BIT, ItemLimit INT, SyncNewContent BIT, DateCreated DateTime, DateLastModified DateTime, ItemCount int)", @@ -95,9 +103,9 @@ namespace Emby.Server.Implementations.Sync throw new ArgumentNullException("id"); } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { var commandText = BaseJobSelectText + " where Id=?"; var paramList = new List(); @@ -206,9 +214,9 @@ namespace Emby.Server.Implementations.Sync CheckDisposed(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { string commandText; var paramList = new List(); @@ -259,9 +267,9 @@ namespace Emby.Server.Implementations.Sync CheckDisposed(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(conn => { @@ -281,9 +289,9 @@ namespace Emby.Server.Implementations.Sync CheckDisposed(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { var commandText = BaseJobSelectText; var paramList = new List(); @@ -379,11 +387,11 @@ namespace Emby.Server.Implementations.Sync CheckDisposed(); - using (WriteLock.Read()) - { - var guid = new Guid(id); + var guid = new Guid(id); - using (var connection = CreateConnection(true)) + using (var connection = CreateConnection(true)) + { + using (WriteLock.Read()) { var commandText = BaseJobItemSelectText + " where Id=?"; var paramList = new List(); @@ -407,9 +415,9 @@ namespace Emby.Server.Implementations.Sync throw new ArgumentNullException("query"); } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { var commandText = baseSelectText; var paramList = new List(); @@ -487,30 +495,30 @@ namespace Emby.Server.Implementations.Sync var now = DateTime.UtcNow; - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) - { - var commandText = "select ItemId,Status,Progress from SyncJobItems"; - var whereClauses = new List(); + var commandText = "select ItemId,Status,Progress from SyncJobItems"; + var whereClauses = new List(); - if (!string.IsNullOrWhiteSpace(query.TargetId)) - { - whereClauses.Add("TargetId=@TargetId"); - } + if (!string.IsNullOrWhiteSpace(query.TargetId)) + { + whereClauses.Add("TargetId=@TargetId"); + } - if (query.Statuses.Length > 0) - { - var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray()); + if (query.Statuses.Length > 0) + { + var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray()); - whereClauses.Add(string.Format("Status in ({0})", statuses)); - } + whereClauses.Add(string.Format("Status in ({0})", statuses)); + } - if (whereClauses.Count > 0) - { - commandText += " where " + string.Join(" AND ", whereClauses.ToArray()); - } + if (whereClauses.Count > 0) + { + commandText += " where " + string.Join(" AND ", whereClauses.ToArray()); + } + using (WriteLock.Read()) + { using (var statement = connection.PrepareStatement(commandText)) { if (!string.IsNullOrWhiteSpace(query.TargetId)) @@ -664,9 +672,9 @@ namespace Emby.Server.Implementations.Sync CheckDisposed(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { string commandText; -- cgit v1.2.3 From f275d7f3d2f40f5e4cbe2f97df6dbd9be8ec37fe Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Mon, 21 Nov 2016 03:54:53 -0500 Subject: reduce library queries --- .../EnvironmentInfo/EnvironmentInfo.cs | 5 ++ Emby.Server.Core/ApplicationHost.cs | 2 +- .../Activity/ActivityRepository.cs | 5 +- .../Data/BaseSqliteRepository.cs | 37 +++++++-- .../Data/SqliteDisplayPreferencesRepository.cs | 5 +- .../Data/SqliteFileOrganizationRepository.cs | 5 +- .../Data/SqliteItemRepository.cs | 96 +++++++++++----------- .../Data/SqliteUserDataRepository.cs | 5 +- .../Data/SqliteUserRepository.cs | 5 +- Emby.Server.Implementations/Dto/DtoService.cs | 16 +++- .../Library/LibraryManager.cs | 31 +++---- .../Library/UserViewManager.cs | 30 ++++--- .../LiveTv/LiveTvManager.cs | 2 +- .../Notifications/SqliteNotificationsRepository.cs | 11 ++- .../Security/AuthenticationRepository.cs | 5 +- .../Social/SharingRepository.cs | 5 +- Emby.Server.Implementations/Sync/SyncRepository.cs | 5 +- Emby.Server.Implementations/TV/TVSeriesManager.cs | 4 +- MediaBrowser.Api/Library/LibraryService.cs | 2 +- MediaBrowser.Api/Movies/MoviesService.cs | 17 +--- MediaBrowser.Controller/Entities/Audio/Audio.cs | 6 ++ MediaBrowser.Controller/Entities/BaseItem.cs | 22 ++++- MediaBrowser.Controller/Entities/Folder.cs | 39 +++++---- .../Entities/InternalItemsQuery.cs | 1 - MediaBrowser.Controller/Entities/TV/Episode.cs | 6 ++ MediaBrowser.Controller/Entities/TV/Season.cs | 6 ++ MediaBrowser.Controller/Entities/TV/Series.cs | 1 - .../Entities/UserViewBuilder.cs | 10 +-- MediaBrowser.Controller/Library/ILibraryManager.cs | 5 +- MediaBrowser.Controller/TV/ITVSeriesManager.cs | 2 +- MediaBrowser.Model/System/IEnvironmentInfo.cs | 1 + MediaBrowser.Providers/Manager/MetadataService.cs | 14 ++-- 32 files changed, 230 insertions(+), 176 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs') diff --git a/Emby.Common.Implementations/EnvironmentInfo/EnvironmentInfo.cs b/Emby.Common.Implementations/EnvironmentInfo/EnvironmentInfo.cs index c040e3931..ebd596dc4 100644 --- a/Emby.Common.Implementations/EnvironmentInfo/EnvironmentInfo.cs +++ b/Emby.Common.Implementations/EnvironmentInfo/EnvironmentInfo.cs @@ -105,5 +105,10 @@ namespace Emby.Common.Implementations.EnvironmentInfo { return null; } + + public string StackTrace + { + get { return Environment.StackTrace; } + } } } diff --git a/Emby.Server.Core/ApplicationHost.cs b/Emby.Server.Core/ApplicationHost.cs index 55a851033..d48138080 100644 --- a/Emby.Server.Core/ApplicationHost.cs +++ b/Emby.Server.Core/ApplicationHost.cs @@ -551,7 +551,7 @@ namespace Emby.Server.Core DisplayPreferencesRepository = displayPreferencesRepo; RegisterSingleInstance(DisplayPreferencesRepository); - var itemRepo = new SqliteItemRepository(ServerConfigurationManager, JsonSerializer, LogManager.GetLogger("SqliteItemRepository"), MemoryStreamFactory, assemblyInfo, FileSystemManager); + var itemRepo = new SqliteItemRepository(ServerConfigurationManager, JsonSerializer, LogManager.GetLogger("SqliteItemRepository"), MemoryStreamFactory, assemblyInfo, FileSystemManager, EnvironmentInfo); ItemRepository = itemRepo; RegisterSingleInstance(ItemRepository); diff --git a/Emby.Server.Implementations/Activity/ActivityRepository.cs b/Emby.Server.Implementations/Activity/ActivityRepository.cs index 8a7573d66..eef9792de 100644 --- a/Emby.Server.Implementations/Activity/ActivityRepository.cs +++ b/Emby.Server.Implementations/Activity/ActivityRepository.cs @@ -29,10 +29,9 @@ namespace Emby.Server.Implementations.Activity { connection.ExecuteAll(string.Join(";", new[] { + "PRAGMA page_size=4096", "pragma default_temp_store = memory", - "pragma default_synchronous=Normal", - "pragma temp_store = memory", - "pragma synchronous=Normal", + "pragma temp_store = memory" })); string[] queries = { diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index 382c7f245..d4226ec25 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -40,6 +40,8 @@ namespace Emby.Server.Implementations.Data private static bool _versionLogged; + private string _defaultWal; + protected SQLiteDatabaseConnection CreateConnection(bool isReadOnly = false, Action onConnect = null) { if (!_versionLogged) @@ -51,6 +53,15 @@ namespace Emby.Server.Implementations.Data ConnectionFlags connectionFlags; + if (isReadOnly) + { + //Logger.Info("Opening read connection"); + } + else + { + //Logger.Info("Opening write connection"); + } + isReadOnly = false; if (isReadOnly) @@ -70,12 +81,16 @@ namespace Emby.Server.Implementations.Data var db = SQLite3.Open(DbFilePath, connectionFlags, null); + if (string.IsNullOrWhiteSpace(_defaultWal)) + { + _defaultWal = db.Query("PRAGMA journal_mode").SelectScalarString().First(); + } + var queries = new List { + "PRAGMA default_temp_store=memory", "pragma temp_store = memory", - "PRAGMA page_size=4096", - "PRAGMA journal_mode=WAL", - "pragma synchronous=Normal", + "PRAGMA journal_mode=WAL" //"PRAGMA cache size=-10000" }; @@ -90,13 +105,19 @@ namespace Emby.Server.Implementations.Data //// db.Execute(query); ////} - using (WriteLock.Write()) - { - db.ExecuteAll(string.Join(";", queries.ToArray())); + //Logger.Info("synchronous: " + db.Query("PRAGMA synchronous").SelectScalarString().First()); + //Logger.Info("temp_store: " + db.Query("PRAGMA temp_store").SelectScalarString().First()); - if (onConnect != null) + //if (!string.Equals(_defaultWal, "wal", StringComparison.OrdinalIgnoreCase) || onConnect != null) + { + using (WriteLock.Write()) { - onConnect(db); + db.ExecuteAll(string.Join(";", queries.ToArray())); + + if (onConnect != null) + { + onConnect(db); + } } } diff --git a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs index ab927ce86..17afbcfa9 100644 --- a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs @@ -56,10 +56,9 @@ namespace Emby.Server.Implementations.Data { connection.ExecuteAll(string.Join(";", new[] { + "PRAGMA page_size=4096", "pragma default_temp_store = memory", - "pragma default_synchronous=Normal", - "pragma temp_store = memory", - "pragma synchronous=Normal", + "pragma temp_store = memory" })); string[] queries = { diff --git a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs index a71682329..efc0ee2ed 100644 --- a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs @@ -33,10 +33,9 @@ namespace Emby.Server.Implementations.Data { connection.ExecuteAll(string.Join(";", new[] { + "PRAGMA page_size=4096", "pragma default_temp_store = memory", - "pragma default_synchronous=Normal", - "pragma temp_store = memory", - "pragma synchronous=Normal", + "pragma temp_store = memory" })); string[] queries = { diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 754af9640..bc1eca06a 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -29,6 +29,7 @@ using MediaBrowser.Server.Implementations.Devices; using MediaBrowser.Server.Implementations.Playlists; using MediaBrowser.Model.Reflection; using SQLitePCL.pretty; +using MediaBrowser.Model.System; namespace Emby.Server.Implementations.Data { @@ -66,14 +67,14 @@ namespace Emby.Server.Implementations.Data private readonly string _criticReviewsPath; - public const int LatestSchemaVersion = 109; private readonly IMemoryStreamFactory _memoryStreamProvider; private readonly IFileSystem _fileSystem; + private readonly IEnvironmentInfo _environmentInfo; /// /// Initializes a new instance of the class. /// - public SqliteItemRepository(IServerConfigurationManager config, IJsonSerializer jsonSerializer, ILogger logger, IMemoryStreamFactory memoryStreamProvider, IAssemblyInfo assemblyInfo, IFileSystem fileSystem) + public SqliteItemRepository(IServerConfigurationManager config, IJsonSerializer jsonSerializer, ILogger logger, IMemoryStreamFactory memoryStreamProvider, IAssemblyInfo assemblyInfo, IFileSystem fileSystem, IEnvironmentInfo environmentInfo) : base(logger) { if (config == null) @@ -89,6 +90,7 @@ namespace Emby.Server.Implementations.Data _jsonSerializer = jsonSerializer; _memoryStreamProvider = memoryStreamProvider; _fileSystem = fileSystem; + _environmentInfo = environmentInfo; _typeMapper = new TypeMapper(assemblyInfo); _criticReviewsPath = Path.Combine(_config.ApplicationPaths.DataPath, "critic-reviews"); @@ -129,10 +131,9 @@ namespace Emby.Server.Implementations.Data _connection.ExecuteAll(string.Join(";", new[] { - "pragma default_temp_store = memory", - "pragma default_synchronous=Normal", - "pragma temp_store = memory", - "pragma synchronous=Normal", + "PRAGMA page_size=4096", + "PRAGMA default_temp_store=memory", + "PRAGMA temp_store=memory" })); var createMediaStreamsTableCommand @@ -193,7 +194,6 @@ namespace Emby.Server.Implementations.Data AddColumn(db, "TypedBaseItems", "ProductionYear", "INT", existingColumnNames); AddColumn(db, "TypedBaseItems", "ParentId", "GUID", existingColumnNames); AddColumn(db, "TypedBaseItems", "Genres", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SchemaVersion", "INT", existingColumnNames); AddColumn(db, "TypedBaseItems", "SortName", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "RunTimeTicks", "BIGINT", existingColumnNames); @@ -205,7 +205,6 @@ namespace Emby.Server.Implementations.Data AddColumn(db, "TypedBaseItems", "DateModified", "DATETIME", existingColumnNames); AddColumn(db, "TypedBaseItems", "ForcedSortName", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsOffline", "BIT", existingColumnNames); AddColumn(db, "TypedBaseItems", "LocationType", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "IsSeries", "BIT", existingColumnNames); @@ -381,7 +380,6 @@ namespace Emby.Server.Implementations.Data "data", "StartDate", "EndDate", - "IsOffline", "ChannelId", "IsMovie", "IsSports", @@ -529,7 +527,6 @@ namespace Emby.Server.Implementations.Data "ParentId", "Genres", "InheritedParentalRatingValue", - "SchemaVersion", "SortName", "RunTimeTicks", "OfficialRatingDescription", @@ -539,7 +536,6 @@ namespace Emby.Server.Implementations.Data "DateCreated", "DateModified", "ForcedSortName", - "IsOffline", "LocationType", "PreferredMetadataLanguage", "PreferredMetadataCountryCode", @@ -790,7 +786,6 @@ namespace Emby.Server.Implementations.Data } saveItemStatement.TryBind("@InheritedParentalRatingValue", item.GetInheritedParentalRatingValue() ?? 0); - saveItemStatement.TryBind("@SchemaVersion", LatestSchemaVersion); saveItemStatement.TryBind("@SortName", item.SortName); saveItemStatement.TryBind("@RunTimeTicks", item.RunTimeTicks); @@ -803,7 +798,6 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@DateModified", item.DateModified); saveItemStatement.TryBind("@ForcedSortName", item.ForcedSortName); - saveItemStatement.TryBind("@IsOffline", item.IsOffline); saveItemStatement.TryBind("@LocationType", item.LocationType.ToString()); saveItemStatement.TryBind("@PreferredMetadataLanguage", item.PreferredMetadataLanguage); @@ -1182,7 +1176,7 @@ namespace Emby.Server.Implementations.Data } CheckDisposed(); - + //Logger.Info("Retrieving item {0}", id.ToString("N")); using (var connection = CreateConnection(true)) { using (WriteLock.Read()) @@ -1369,64 +1363,72 @@ namespace Emby.Server.Implementations.Data if (!reader.IsDBNull(4)) { - item.IsOffline = reader.GetBoolean(4); + item.ChannelId = reader.GetString(4); } - if (!reader.IsDBNull(5)) - { - item.ChannelId = reader.GetString(5); - } + var index = 5; var hasProgramAttributes = item as IHasProgramAttributes; if (hasProgramAttributes != null) { - if (!reader.IsDBNull(6)) + if (!reader.IsDBNull(index)) { - hasProgramAttributes.IsMovie = reader.GetBoolean(6); + hasProgramAttributes.IsMovie = reader.GetBoolean(index); } + index++; - if (!reader.IsDBNull(7)) + if (!reader.IsDBNull(index)) { - hasProgramAttributes.IsSports = reader.GetBoolean(7); + hasProgramAttributes.IsSports = reader.GetBoolean(index); } + index++; - if (!reader.IsDBNull(8)) + if (!reader.IsDBNull(index)) { - hasProgramAttributes.IsKids = reader.GetBoolean(8); + hasProgramAttributes.IsKids = reader.GetBoolean(index); } + index++; - if (!reader.IsDBNull(9)) + if (!reader.IsDBNull(index)) { - hasProgramAttributes.IsSeries = reader.GetBoolean(9); + hasProgramAttributes.IsSeries = reader.GetBoolean(index); } + index++; - if (!reader.IsDBNull(10)) + if (!reader.IsDBNull(index)) { - hasProgramAttributes.IsLive = reader.GetBoolean(10); + hasProgramAttributes.IsLive = reader.GetBoolean(index); } + index++; - if (!reader.IsDBNull(11)) + if (!reader.IsDBNull(index)) { - hasProgramAttributes.IsNews = reader.GetBoolean(11); + hasProgramAttributes.IsNews = reader.GetBoolean(index); } + index++; - if (!reader.IsDBNull(12)) + if (!reader.IsDBNull(index)) { - hasProgramAttributes.IsPremiere = reader.GetBoolean(12); + hasProgramAttributes.IsPremiere = reader.GetBoolean(index); } + index++; - if (!reader.IsDBNull(13)) + if (!reader.IsDBNull(index)) { - hasProgramAttributes.EpisodeTitle = reader.GetString(13); + hasProgramAttributes.EpisodeTitle = reader.GetString(index); } + index++; - if (!reader.IsDBNull(14)) + if (!reader.IsDBNull(index)) { - hasProgramAttributes.IsRepeat = reader.GetBoolean(14); + hasProgramAttributes.IsRepeat = reader.GetBoolean(index); } + index++; + } + else + { + index += 9; } - - var index = 15; if (!reader.IsDBNull(index)) { @@ -2368,6 +2370,8 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); + //Logger.Info("GetItemList: " + _environmentInfo.StackTrace); + var now = DateTime.UtcNow; var list = new List(); @@ -2533,6 +2537,7 @@ namespace Emby.Server.Implementations.Data TotalRecordCount = returnList.Count }; } + //Logger.Info("GetItems: " + _environmentInfo.StackTrace); var now = DateTime.UtcNow; @@ -2770,6 +2775,7 @@ namespace Emby.Server.Implementations.Data } CheckDisposed(); + //Logger.Info("GetItemIdsList: " + _environmentInfo.StackTrace); var now = DateTime.UtcNow; @@ -2928,6 +2934,7 @@ namespace Emby.Server.Implementations.Data TotalRecordCount = returnList.Count }; } + //Logger.Info("GetItemIds: " + _environmentInfo.StackTrace); var now = DateTime.UtcNow; @@ -3053,14 +3060,6 @@ namespace Emby.Server.Implementations.Data statement.TryBind("@IsLocked", query.IsLocked); } } - if (query.IsOffline.HasValue) - { - whereClauses.Add("IsOffline=@IsOffline"); - if (statement != null) - { - statement.TryBind("@IsOffline", query.IsOffline); - } - } var exclusiveProgramAttribtues = !(query.IsMovie ?? true) || !(query.IsSports ?? true) || @@ -4721,7 +4720,7 @@ namespace Emby.Server.Implementations.Data using (var connection = CreateConnection(true)) { - using (WriteLock.Write()) + using (WriteLock.Read()) { foreach (var row in connection.Query(commandText)) { @@ -4750,6 +4749,7 @@ namespace Emby.Server.Implementations.Data } CheckDisposed(); + //Logger.Info("GetItemValues: " + _environmentInfo.StackTrace); var now = DateTime.UtcNow; diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index 0b4768c14..9f7cce13b 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -45,10 +45,9 @@ namespace Emby.Server.Implementations.Data { connection.ExecuteAll(string.Join(";", new[] { + "PRAGMA page_size=4096", "pragma default_temp_store = memory", - "pragma default_synchronous=Normal", - "pragma temp_store = memory", - "pragma synchronous=Normal", + "pragma temp_store = memory" })); string[] queries = { diff --git a/Emby.Server.Implementations/Data/SqliteUserRepository.cs b/Emby.Server.Implementations/Data/SqliteUserRepository.cs index 0a2f9e7cd..f902d981f 100644 --- a/Emby.Server.Implementations/Data/SqliteUserRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserRepository.cs @@ -52,10 +52,9 @@ namespace Emby.Server.Implementations.Data { connection.ExecuteAll(string.Join(";", new[] { + "PRAGMA page_size=4096", "pragma default_temp_store = memory", - "pragma default_synchronous=Normal", - "pragma temp_store = memory", - "pragma synchronous=Normal", + "pragma temp_store = memory" })); string[] queries = { diff --git a/Emby.Server.Implementations/Dto/DtoService.cs b/Emby.Server.Implementations/Dto/DtoService.cs index 85549439b..438fc55d7 100644 --- a/Emby.Server.Implementations/Dto/DtoService.cs +++ b/Emby.Server.Implementations/Dto/DtoService.cs @@ -482,7 +482,7 @@ namespace Emby.Server.Implementations.Dto { if (dtoOptions.EnableUserData) { - dto.UserData = _userDataRepository.GetUserDataDto(item, user).Result; + dto.UserData = await _userDataRepository.GetUserDataDto(item, user).ConfigureAwait(false); } } @@ -1450,11 +1450,19 @@ namespace Emby.Server.Implementations.Dto private void AddInheritedImages(BaseItemDto dto, BaseItem item, DtoOptions options, BaseItem owner) { + if (!item.SupportsInheritedParentImages) + { + return; + } + var logoLimit = options.GetImageLimit(ImageType.Logo); var artLimit = options.GetImageLimit(ImageType.Art); var thumbLimit = options.GetImageLimit(ImageType.Thumb); var backdropLimit = options.GetImageLimit(ImageType.Backdrop); + // For now. Emby apps are not using this + artLimit = 0; + if (logoLimit == 0 && artLimit == 0 && thumbLimit == 0 && backdropLimit == 0) { return; @@ -1515,6 +1523,12 @@ namespace Emby.Server.Implementations.Dto } isFirst = false; + + if (!parent.SupportsInheritedParentImages) + { + break; + } + parent = parent.GetParent(); } } diff --git a/Emby.Server.Implementations/Library/LibraryManager.cs b/Emby.Server.Implementations/Library/LibraryManager.cs index 13c6485e5..283d193dd 100644 --- a/Emby.Server.Implementations/Library/LibraryManager.cs +++ b/Emby.Server.Implementations/Library/LibraryManager.cs @@ -339,11 +339,6 @@ namespace Emby.Server.Implementations.Library { throw new ArgumentNullException("item"); } - RegisterItem(item.Id, item); - } - - private void RegisterItem(Guid id, BaseItem item) - { if (item is IItemByName) { if (!(item is MusicArtist)) @@ -354,13 +349,13 @@ namespace Emby.Server.Implementations.Library if (item.IsFolder) { - if (!(item is ICollectionFolder) && !(item is UserView) && !(item is Channel) && !(item is AggregateFolder)) - { - if (item.SourceType != SourceType.Library) - { - return; - } - } + //if (!(item is ICollectionFolder) && !(item is UserView) && !(item is Channel) && !(item is AggregateFolder)) + //{ + // if (item.SourceType != SourceType.Library) + // { + // return; + // } + //} } else { @@ -370,7 +365,7 @@ namespace Emby.Server.Implementations.Library } } - LibraryItemsCache.AddOrUpdate(id, item, delegate { return item; }); + LibraryItemsCache.AddOrUpdate(item.Id, item, delegate { return item; }); } public async Task DeleteItem(BaseItem item, DeleteOptions options) @@ -822,7 +817,7 @@ namespace Emby.Server.Implementations.Library return _userRootFolder; } - + public BaseItem FindByPath(string path, bool? isFolder) { // If this returns multiple items it could be tricky figuring out which one is correct. @@ -836,7 +831,7 @@ namespace Emby.Server.Implementations.Library SortOrder = SortOrder.Descending, Limit = 1 }; - + return GetItemList(query) .FirstOrDefault(); } @@ -1273,10 +1268,8 @@ namespace Emby.Server.Implementations.Library return ItemRepository.GetItemList(query); } - public IEnumerable GetItemList(InternalItemsQuery query, IEnumerable parentIds) + public IEnumerable GetItemList(InternalItemsQuery query, List parents) { - var parents = parentIds.Select(i => GetItemById(new Guid(i))).Where(i => i != null).ToList(); - SetTopParentIdsOrAncestors(query, parents); if (query.AncestorIds.Length == 0 && query.TopParentIds.Length == 0) @@ -1536,7 +1529,7 @@ namespace Emby.Server.Implementations.Library } // Handle grouping - if (user != null && !string.IsNullOrWhiteSpace(view.ViewType) && UserView.IsEligibleForGrouping(view.ViewType)) + if (user != null && !string.IsNullOrWhiteSpace(view.ViewType) && UserView.IsEligibleForGrouping(view.ViewType) && user.Configuration.GroupedFolders.Length > 0) { return user.RootFolder .GetChildren(user, true) diff --git a/Emby.Server.Implementations/Library/UserViewManager.cs b/Emby.Server.Implementations/Library/UserViewManager.cs index b93f565a3..f7cc8bb73 100644 --- a/Emby.Server.Implementations/Library/UserViewManager.cs +++ b/Emby.Server.Implementations/Library/UserViewManager.cs @@ -245,20 +245,26 @@ namespace Emby.Server.Implementations.Library var includeItemTypes = request.IncludeItemTypes; var limit = request.Limit ?? 10; - var parentIds = string.IsNullOrEmpty(parentId) - ? new string[] { } - : new[] { parentId }; + var parents = new List(); - if (parentIds.Length == 0) + if (!string.IsNullOrWhiteSpace(parentId)) { - parentIds = user.RootFolder.GetChildren(user, true) - .OfType() - .Select(i => i.Id.ToString("N")) - .Where(i => !user.Configuration.LatestItemsExcludes.Contains(i)) - .ToArray(); + var parent = _libraryManager.GetItemById(parentId) as Folder; + if (parent != null) + { + parents.Add(parent); + } + } + + if (parents.Count == 0) + { + parents = user.RootFolder.GetChildren(user, true) + .Where(i => i is Folder) + .Where(i => !user.Configuration.LatestItemsExcludes.Contains(i.Id.ToString("N"))) + .ToList(); } - if (parentIds.Length == 0) + if (parents.Count == 0) { return new List(); } @@ -283,10 +289,10 @@ namespace Emby.Server.Implementations.Library ExcludeItemTypes = excludeItemTypes, ExcludeLocationTypes = new[] { LocationType.Virtual }, Limit = limit * 5, - SourceTypes = parentIds.Length == 0 ? new[] { SourceType.Library } : new SourceType[] { }, + SourceTypes = parents.Count == 0 ? new[] { SourceType.Library } : new SourceType[] { }, IsPlayed = request.IsPlayed - }, parentIds); + }, parents); } } } diff --git a/Emby.Server.Implementations/LiveTv/LiveTvManager.cs b/Emby.Server.Implementations/LiveTv/LiveTvManager.cs index 3a6f23fe9..faf9687f4 100644 --- a/Emby.Server.Implementations/LiveTv/LiveTvManager.cs +++ b/Emby.Server.Implementations/LiveTv/LiveTvManager.cs @@ -491,7 +491,7 @@ namespace Emby.Server.Implementations.LiveTv var id = _tvDtoService.GetInternalChannelId(serviceName, channelInfo.Id); - var item = _itemRepo.RetrieveItem(id) as LiveTvChannel; + var item = _libraryManager.GetItemById(id) as LiveTvChannel; if (item == null) { diff --git a/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs b/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs index 5fc691527..66ef5d5d1 100644 --- a/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs +++ b/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs @@ -31,10 +31,9 @@ namespace Emby.Server.Implementations.Notifications { connection.ExecuteAll(string.Join(";", new[] { + "PRAGMA page_size=4096", "pragma default_temp_store = memory", - "pragma default_synchronous=Normal", - "pragma temp_store = memory", - "pragma synchronous=Normal", + "pragma temp_store = memory" })); string[] queries = { @@ -57,9 +56,9 @@ namespace Emby.Server.Implementations.Notifications { var result = new NotificationResult(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + //using (WriteLock.Read()) { var clauses = new List(); var paramList = new List(); @@ -114,7 +113,7 @@ namespace Emby.Server.Implementations.Notifications using (var connection = CreateConnection(true)) { - using (WriteLock.Read()) + //using (WriteLock.Read()) { using (var statement = connection.PrepareStatement("select Level from Notifications where UserId=@UserId and IsRead=@IsRead")) { diff --git a/Emby.Server.Implementations/Security/AuthenticationRepository.cs b/Emby.Server.Implementations/Security/AuthenticationRepository.cs index 0a598e9a7..dad05718c 100644 --- a/Emby.Server.Implementations/Security/AuthenticationRepository.cs +++ b/Emby.Server.Implementations/Security/AuthenticationRepository.cs @@ -32,10 +32,9 @@ namespace Emby.Server.Implementations.Security { connection.ExecuteAll(string.Join(";", new[] { + "PRAGMA page_size=4096", "pragma default_temp_store = memory", - "pragma default_synchronous=Normal", - "pragma temp_store = memory", - "pragma synchronous=Normal", + "pragma temp_store = memory" })); string[] queries = { diff --git a/Emby.Server.Implementations/Social/SharingRepository.cs b/Emby.Server.Implementations/Social/SharingRepository.cs index e0ee62780..6dab54bc6 100644 --- a/Emby.Server.Implementations/Social/SharingRepository.cs +++ b/Emby.Server.Implementations/Social/SharingRepository.cs @@ -29,10 +29,9 @@ namespace Emby.Server.Implementations.Social { connection.ExecuteAll(string.Join(";", new[] { + "PRAGMA page_size=4096", "pragma default_temp_store = memory", - "pragma default_synchronous=Normal", - "pragma temp_store = memory", - "pragma synchronous=Normal", + "pragma temp_store = memory" })); string[] queries = { diff --git a/Emby.Server.Implementations/Sync/SyncRepository.cs b/Emby.Server.Implementations/Sync/SyncRepository.cs index 36558142b..308c3bd00 100644 --- a/Emby.Server.Implementations/Sync/SyncRepository.cs +++ b/Emby.Server.Implementations/Sync/SyncRepository.cs @@ -45,10 +45,9 @@ namespace Emby.Server.Implementations.Sync { connection.ExecuteAll(string.Join(";", new[] { + "PRAGMA page_size=4096", "pragma default_temp_store = memory", - "pragma default_synchronous=Normal", - "pragma temp_store = memory", - "pragma synchronous=Normal", + "pragma temp_store = memory" })); string[] queries = { diff --git a/Emby.Server.Implementations/TV/TVSeriesManager.cs b/Emby.Server.Implementations/TV/TVSeriesManager.cs index a47aaa305..fd576e081 100644 --- a/Emby.Server.Implementations/TV/TVSeriesManager.cs +++ b/Emby.Server.Implementations/TV/TVSeriesManager.cs @@ -72,7 +72,7 @@ namespace Emby.Server.Implementations.TV return GetResult(episodes, null, request); } - public QueryResult GetNextUp(NextUpQuery request, IEnumerable parentsFolders) + public QueryResult GetNextUp(NextUpQuery request, List parentsFolders) { var user = _userManager.GetUserById(request.UserId); @@ -106,7 +106,7 @@ namespace Emby.Server.Implementations.TV PresentationUniqueKey = presentationUniqueKey, Limit = limit - }, parentsFolders.Select(i => i.Id.ToString("N"))).Cast(); + }, parentsFolders.Cast().ToList()).Cast(); // Avoid implicitly captured closure var episodes = GetNextUpEpisodes(request, user, items); diff --git a/MediaBrowser.Api/Library/LibraryService.cs b/MediaBrowser.Api/Library/LibraryService.cs index 36a58cc20..695718a25 100644 --- a/MediaBrowser.Api/Library/LibraryService.cs +++ b/MediaBrowser.Api/Library/LibraryService.cs @@ -369,7 +369,7 @@ namespace MediaBrowser.Api.Library if (item is Movie || (program != null && program.IsMovie) || item is Trailer) { - return new MoviesService(_userManager, _userDataManager, _libraryManager, _itemRepo, _dtoService, _config, _authContext) + return new MoviesService(_userManager, _libraryManager, _dtoService, _config, _authContext) { Request = Request, diff --git a/MediaBrowser.Api/Movies/MoviesService.cs b/MediaBrowser.Api/Movies/MoviesService.cs index 1b2fa4fff..b5c6f52fc 100644 --- a/MediaBrowser.Api/Movies/MoviesService.cs +++ b/MediaBrowser.Api/Movies/MoviesService.cs @@ -78,16 +78,8 @@ namespace MediaBrowser.Api.Movies /// private readonly IUserManager _userManager; - /// - /// The _user data repository - /// - private readonly IUserDataManager _userDataRepository; - /// - /// The _library manager - /// private readonly ILibraryManager _libraryManager; - private readonly IItemRepository _itemRepo; private readonly IDtoService _dtoService; private readonly IServerConfigurationManager _config; private readonly IAuthorizationContext _authContext; @@ -95,17 +87,10 @@ namespace MediaBrowser.Api.Movies /// /// Initializes a new instance of the class. /// - /// The user manager. - /// The user data repository. - /// The library manager. - /// The item repo. - /// The dto service. - public MoviesService(IUserManager userManager, IUserDataManager userDataRepository, ILibraryManager libraryManager, IItemRepository itemRepo, IDtoService dtoService, IServerConfigurationManager config, IAuthorizationContext authContext) + public MoviesService(IUserManager userManager, ILibraryManager libraryManager, IDtoService dtoService, IServerConfigurationManager config, IAuthorizationContext authContext) { _userManager = userManager; - _userDataRepository = userDataRepository; _libraryManager = libraryManager; - _itemRepo = itemRepo; _dtoService = dtoService; _config = config; _authContext = authContext; diff --git a/MediaBrowser.Controller/Entities/Audio/Audio.cs b/MediaBrowser.Controller/Entities/Audio/Audio.cs index 539cc5f22..e4f638cb6 100644 --- a/MediaBrowser.Controller/Entities/Audio/Audio.cs +++ b/MediaBrowser.Controller/Entities/Audio/Audio.cs @@ -61,6 +61,12 @@ namespace MediaBrowser.Controller.Entities.Audio get { return true; } } + [IgnoreDataMember] + public override bool SupportsInheritedParentImages + { + get { return true; } + } + [IgnoreDataMember] protected override bool SupportsOwnedItems { diff --git a/MediaBrowser.Controller/Entities/BaseItem.cs b/MediaBrowser.Controller/Entities/BaseItem.cs index cd61d2cce..b7ea7a92d 100644 --- a/MediaBrowser.Controller/Entities/BaseItem.cs +++ b/MediaBrowser.Controller/Entities/BaseItem.cs @@ -1569,6 +1569,12 @@ namespace MediaBrowser.Controller.Entities return IsVisibleStandaloneInternal(user, true); } + [IgnoreDataMember] + public virtual bool SupportsInheritedParentImages + { + get { return false; } + } + protected bool IsVisibleStandaloneInternal(User user, bool checkFolders) { if (!IsVisible(user)) @@ -2329,17 +2335,25 @@ namespace MediaBrowser.Controller.Entities { get { - if (GetParent() is AggregateFolder || this is BasePluginFolder || this is Channel) + if (this is BasePluginFolder || this is Channel) { return true; } var view = this as UserView; - if (view != null && string.Equals(view.ViewType, CollectionType.LiveTv, StringComparison.OrdinalIgnoreCase)) + if (view != null) { - return true; + if (string.Equals(view.ViewType, CollectionType.LiveTv, StringComparison.OrdinalIgnoreCase)) + { + return true; + } + if (string.Equals(view.ViewType, CollectionType.Channels, StringComparison.OrdinalIgnoreCase)) + { + return true; + } } - if (view != null && string.Equals(view.ViewType, CollectionType.Channels, StringComparison.OrdinalIgnoreCase)) + + if (GetParent() is AggregateFolder) { return true; } diff --git a/MediaBrowser.Controller/Entities/Folder.cs b/MediaBrowser.Controller/Entities/Folder.cs index 3df77673f..d4ddab7b2 100644 --- a/MediaBrowser.Controller/Entities/Folder.cs +++ b/MediaBrowser.Controller/Entities/Folder.cs @@ -1383,6 +1383,15 @@ namespace MediaBrowser.Controller.Entities { return false; } + var iItemByName = this as IItemByName; + if (iItemByName != null) + { + var hasDualAccess = this as IHasDualAccess; + if (hasDualAccess == null || hasDualAccess.IsAccessedByName) + { + return false; + } + } return true; } @@ -1395,17 +1404,6 @@ namespace MediaBrowser.Controller.Entities return; } - var unplayedQueryResult = await GetItems(new InternalItemsQuery(user) - { - Recursive = true, - IsFolder = false, - IsVirtualItem = false, - EnableTotalRecordCount = true, - Limit = 0, - IsPlayed = false - - }).ConfigureAwait(false); - var allItemsQueryResult = await GetItems(new InternalItemsQuery(user) { Recursive = true, @@ -1415,17 +1413,28 @@ namespace MediaBrowser.Controller.Entities Limit = 0 }).ConfigureAwait(false); + var recursiveItemCount = allItemsQueryResult.TotalRecordCount; if (itemDto != null) { itemDto.RecursiveItemCount = allItemsQueryResult.TotalRecordCount; } - var recursiveItemCount = allItemsQueryResult.TotalRecordCount; - double unplayedCount = unplayedQueryResult.TotalRecordCount; - - if (recursiveItemCount > 0) + if (recursiveItemCount > 0 && SupportsPlayedStatus) { + var unplayedQueryResult = recursiveItemCount > 0 ? await GetItems(new InternalItemsQuery(user) + { + Recursive = true, + IsFolder = false, + IsVirtualItem = false, + EnableTotalRecordCount = true, + Limit = 0, + IsPlayed = false + + }).ConfigureAwait(false) : new QueryResult(); + + double unplayedCount = unplayedQueryResult.TotalRecordCount; + var unplayedPercentage = (unplayedCount / recursiveItemCount) * 100; dto.PlayedPercentage = 100 - unplayedPercentage; dto.Played = dto.PlayedPercentage.Value >= 100; diff --git a/MediaBrowser.Controller/Entities/InternalItemsQuery.cs b/MediaBrowser.Controller/Entities/InternalItemsQuery.cs index 94baacf13..be6e95ddd 100644 --- a/MediaBrowser.Controller/Entities/InternalItemsQuery.cs +++ b/MediaBrowser.Controller/Entities/InternalItemsQuery.cs @@ -124,7 +124,6 @@ namespace MediaBrowser.Controller.Entities public int? MaxParentalRating { get; set; } public bool? HasDeadParentId { get; set; } - public bool? IsOffline { get; set; } public bool? IsVirtualItem { get; set; } public Guid? ParentId { get; set; } diff --git a/MediaBrowser.Controller/Entities/TV/Episode.cs b/MediaBrowser.Controller/Entities/TV/Episode.cs index 99abf5611..29a63f317 100644 --- a/MediaBrowser.Controller/Entities/TV/Episode.cs +++ b/MediaBrowser.Controller/Entities/TV/Episode.cs @@ -71,6 +71,12 @@ namespace MediaBrowser.Controller.Entities.TV { return IsStacked || MediaSourceCount > 1; } + } + + [IgnoreDataMember] + public override bool SupportsInheritedParentImages + { + get { return true; } } [IgnoreDataMember] diff --git a/MediaBrowser.Controller/Entities/TV/Season.cs b/MediaBrowser.Controller/Entities/TV/Season.cs index d8a9a16c1..2663a9dd5 100644 --- a/MediaBrowser.Controller/Entities/TV/Season.cs +++ b/MediaBrowser.Controller/Entities/TV/Season.cs @@ -39,6 +39,12 @@ namespace MediaBrowser.Controller.Entities.TV } } + [IgnoreDataMember] + public override bool SupportsInheritedParentImages + { + get { return true; } + } + [IgnoreDataMember] public override Guid? DisplayParentId { diff --git a/MediaBrowser.Controller/Entities/TV/Series.cs b/MediaBrowser.Controller/Entities/TV/Series.cs index a997d3476..69a3a67c8 100644 --- a/MediaBrowser.Controller/Entities/TV/Series.cs +++ b/MediaBrowser.Controller/Entities/TV/Series.cs @@ -135,7 +135,6 @@ namespace MediaBrowser.Controller.Entities.TV { AncestorWithPresentationUniqueKey = GetUniqueSeriesKey(this), IncludeItemTypes = new[] { typeof(Season).Name }, - SortBy = new[] { ItemSortBy.SortName }, IsVirtualItem = false, Limit = 0 }); diff --git a/MediaBrowser.Controller/Entities/UserViewBuilder.cs b/MediaBrowser.Controller/Entities/UserViewBuilder.cs index 3820de83d..d5781d21e 100644 --- a/MediaBrowser.Controller/Entities/UserViewBuilder.cs +++ b/MediaBrowser.Controller/Entities/UserViewBuilder.cs @@ -1812,7 +1812,7 @@ namespace MediaBrowser.Controller.Entities .Where(i => user.IsFolderGrouped(i.Id) && UserView.IsEligibleForGrouping(i)); } - private IEnumerable GetMediaFolders(User user, IEnumerable viewTypes) + private List GetMediaFolders(User user, IEnumerable viewTypes) { if (user == null) { @@ -1822,7 +1822,7 @@ namespace MediaBrowser.Controller.Entities var folder = i as ICollectionFolder; return folder != null && viewTypes.Contains(folder.CollectionType ?? string.Empty, StringComparer.OrdinalIgnoreCase); - }); + }).ToList(); } return GetMediaFolders(user) .Where(i => @@ -1830,17 +1830,17 @@ namespace MediaBrowser.Controller.Entities var folder = i as ICollectionFolder; return folder != null && viewTypes.Contains(folder.CollectionType ?? string.Empty, StringComparer.OrdinalIgnoreCase); - }); + }).ToList(); } - private IEnumerable GetMediaFolders(Folder parent, User user, IEnumerable viewTypes) + private List GetMediaFolders(Folder parent, User user, IEnumerable viewTypes) { if (parent == null || parent is UserView) { return GetMediaFolders(user, viewTypes); } - return new[] { parent }; + return new List { parent }; } private IEnumerable GetRecursiveChildren(Folder parent, User user, IEnumerable viewTypes) diff --git a/MediaBrowser.Controller/Library/ILibraryManager.cs b/MediaBrowser.Controller/Library/ILibraryManager.cs index acdd9a042..955230b8a 100644 --- a/MediaBrowser.Controller/Library/ILibraryManager.cs +++ b/MediaBrowser.Controller/Library/ILibraryManager.cs @@ -538,10 +538,7 @@ namespace MediaBrowser.Controller.Library /// /// Gets the items. /// - /// The query. - /// The parent ids. - /// List<BaseItem>. - IEnumerable GetItemList(InternalItemsQuery query, IEnumerable parentIds); + IEnumerable GetItemList(InternalItemsQuery query, List parents); /// /// Gets the items result. diff --git a/MediaBrowser.Controller/TV/ITVSeriesManager.cs b/MediaBrowser.Controller/TV/ITVSeriesManager.cs index 3d6e87474..771fa602a 100644 --- a/MediaBrowser.Controller/TV/ITVSeriesManager.cs +++ b/MediaBrowser.Controller/TV/ITVSeriesManager.cs @@ -19,6 +19,6 @@ namespace MediaBrowser.Controller.TV /// The request. /// The parents folders. /// QueryResult<BaseItem>. - QueryResult GetNextUp(NextUpQuery request, IEnumerable parentsFolders); + QueryResult GetNextUp(NextUpQuery request, List parentsFolders); } } diff --git a/MediaBrowser.Model/System/IEnvironmentInfo.cs b/MediaBrowser.Model/System/IEnvironmentInfo.cs index 7e7d81e30..f9795a568 100644 --- a/MediaBrowser.Model/System/IEnvironmentInfo.cs +++ b/MediaBrowser.Model/System/IEnvironmentInfo.cs @@ -14,6 +14,7 @@ namespace MediaBrowser.Model.System Architecture SystemArchitecture { get; } string GetEnvironmentVariable(string name); string GetUserId(); + string StackTrace { get; } } public enum OperatingSystem diff --git a/MediaBrowser.Providers/Manager/MetadataService.cs b/MediaBrowser.Providers/Manager/MetadataService.cs index 3046fc395..0491e800f 100644 --- a/MediaBrowser.Providers/Manager/MetadataService.cs +++ b/MediaBrowser.Providers/Manager/MetadataService.cs @@ -536,7 +536,7 @@ namespace MediaBrowser.Providers.Manager refreshResult.UpdateType = refreshResult.UpdateType | ItemUpdateType.MetadataImport; // Only one local provider allowed per item - if (item.IsLocked || IsFullLocalMetadata(localItem.Item)) + if (item.IsLocked || localItem.Item.IsLocked || IsFullLocalMetadata(localItem.Item)) { hasLocalMetadata = true; } @@ -573,14 +573,16 @@ namespace MediaBrowser.Providers.Manager { if (refreshResult.UpdateType > ItemUpdateType.None) { - // If no local metadata, take data from item itself - if (!hasLocalMetadata) + if (hasLocalMetadata) + { + MergeData(temp, metadata, item.LockedFields, true, true); + } + else { // TODO: If the new metadata from above has some blank data, this can cause old data to get filled into those empty fields - MergeData(metadata, temp, new List(), false, true); + MergeData(metadata, temp, new List(), false, false); + MergeData(temp, metadata, item.LockedFields, true, false); } - - MergeData(temp, metadata, item.LockedFields, true, true); } } -- cgit v1.2.3 From 62f84acd2640de815992436ceb392b199af656de Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Mon, 21 Nov 2016 13:49:07 -0500 Subject: fix auto-organize --- .../Data/SqliteFileOrganizationRepository.cs | 161 ++++++++++++--------- .../Data/SqliteUserDataRepository.cs | 4 +- .../Entities/Audio/MusicGenre.cs | 9 ++ MediaBrowser.Controller/Entities/GameGenre.cs | 9 ++ MediaBrowser.Controller/Entities/Genre.cs | 9 ++ MediaBrowser.Controller/Entities/Studio.cs | 9 ++ MediaBrowser.Controller/Entities/Year.cs | 9 ++ 7 files changed, 141 insertions(+), 69 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs') diff --git a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs index efc0ee2ed..2d1aabf5f 100644 --- a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs @@ -59,28 +59,34 @@ namespace Emby.Server.Implementations.Data using (var connection = CreateConnection()) { - connection.RunInTransaction(db => + using (WriteLock.Write()) { - var paramList = new List(); - var commandText = "replace into FileOrganizerResults (ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; - - paramList.Add(result.Id.ToGuidParamValue()); - paramList.Add(result.OriginalPath); - paramList.Add(result.TargetPath); - paramList.Add(result.FileSize); - paramList.Add(result.Date.ToDateTimeParamValue()); - paramList.Add(result.Status.ToString()); - paramList.Add(result.Type.ToString()); - paramList.Add(result.StatusMessage); - paramList.Add(result.ExtractedName); - paramList.Add(result.ExtractedSeasonNumber); - paramList.Add(result.ExtractedEpisodeNumber); - paramList.Add(result.ExtractedEndingEpisodeNumber); - paramList.Add(string.Join("|", result.DuplicatePaths.ToArray())); - - - db.Execute(commandText, paramList.ToArray()); - }); + connection.RunInTransaction(db => + { + var commandText = "replace into FileOrganizerResults (ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths) values (@ResultId, @OriginalPath, @TargetPath, @FileLength, @OrganizationDate, @Status, @OrganizationType, @StatusMessage, @ExtractedName, @ExtractedYear, @ExtractedSeasonNumber, @ExtractedEpisodeNumber, @ExtractedEndingEpisodeNumber, @DuplicatePaths)"; + + using (var statement = db.PrepareStatement(commandText)) + { + statement.TryBind("@ResultId", result.Id.ToGuidParamValue()); + statement.TryBind("@OriginalPath", result.OriginalPath); + + statement.TryBind("@TargetPath", result.TargetPath); + statement.TryBind("@FileLength", result.FileSize); + statement.TryBind("@OrganizationDate", result.Date.ToDateTimeParamValue()); + statement.TryBind("@Status", result.Status.ToString()); + statement.TryBind("@OrganizationType", result.Type.ToString()); + statement.TryBind("@StatusMessage", result.StatusMessage); + statement.TryBind("@ExtractedName", result.ExtractedName); + statement.TryBind("@ExtractedYear", result.ExtractedYear); + statement.TryBind("@ExtractedSeasonNumber", result.ExtractedSeasonNumber); + statement.TryBind("@ExtractedEpisodeNumber", result.ExtractedEpisodeNumber); + statement.TryBind("@ExtractedEndingEpisodeNumber", result.ExtractedEndingEpisodeNumber); + statement.TryBind("@DuplicatePaths", string.Join("|", result.DuplicatePaths.ToArray())); + + statement.MoveNext(); + } + }); + } } } @@ -93,15 +99,17 @@ namespace Emby.Server.Implementations.Data using (var connection = CreateConnection()) { - connection.RunInTransaction(db => + using (WriteLock.Write()) { - var paramList = new List(); - var commandText = "delete from FileOrganizerResults where ResultId = ?"; - - paramList.Add(id.ToGuidParamValue()); - - db.Execute(commandText, paramList.ToArray()); - }); + connection.RunInTransaction(db => + { + using (var statement = db.PrepareStatement("delete from FileOrganizerResults where ResultId = @ResultId")) + { + statement.TryBind("@ResultId", id.ToGuidParamValue()); + statement.MoveNext(); + } + }); + } } } @@ -109,12 +117,15 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection()) { - connection.RunInTransaction(db => + using (WriteLock.Write()) { - var commandText = "delete from FileOrganizerResults"; + connection.RunInTransaction(db => + { + var commandText = "delete from FileOrganizerResults"; - db.Execute(commandText); - }); + db.Execute(commandText); + }); + } } } @@ -127,34 +138,45 @@ namespace Emby.Server.Implementations.Data using (var connection = CreateConnection(true)) { - var commandText = "SELECT ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults"; - - if (query.StartIndex.HasValue && query.StartIndex.Value > 0) - { - commandText += string.Format(" WHERE ResultId NOT IN (SELECT ResultId FROM FileOrganizerResults ORDER BY OrganizationDate desc LIMIT {0})", - query.StartIndex.Value.ToString(_usCulture)); - } - - commandText += " ORDER BY OrganizationDate desc"; - - if (query.Limit.HasValue) + using (WriteLock.Read()) { - commandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); + var commandText = "SELECT ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults"; + + if (query.StartIndex.HasValue && query.StartIndex.Value > 0) + { + commandText += string.Format(" WHERE ResultId NOT IN (SELECT ResultId FROM FileOrganizerResults ORDER BY OrganizationDate desc LIMIT {0})", + query.StartIndex.Value.ToString(_usCulture)); + } + + commandText += " ORDER BY OrganizationDate desc"; + + if (query.Limit.HasValue) + { + commandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); + } + + var list = new List(); + + using (var statement = connection.PrepareStatement(commandText)) + { + foreach (var row in statement.ExecuteQuery()) + { + list.Add(GetResult(row)); + } + } + + int count; + using (var statement = connection.PrepareStatement("select count (ResultId) from FileOrganizerResults")) + { + count = statement.ExecuteQuery().SelectScalarInt().First(); + } + + return new QueryResult() + { + Items = list.ToArray(), + TotalRecordCount = count + }; } - - var list = new List(); - var count = connection.Query("select count (ResultId) from FileOrganizerResults").SelectScalarInt().First(); - - foreach (var row in connection.Query(commandText)) - { - list.Add(GetResult(row)); - } - - return new QueryResult() - { - Items = list.ToArray(), - TotalRecordCount = count - }; } } @@ -167,16 +189,21 @@ namespace Emby.Server.Implementations.Data using (var connection = CreateConnection(true)) { - var paramList = new List(); - - paramList.Add(id.ToGuidParamValue()); - - foreach (var row in connection.Query("select ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults where ResultId=?", paramList.ToArray())) + using (WriteLock.Read()) { - return GetResult(row); + using (var statement = connection.PrepareStatement("select ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults where ResultId=@ResultId")) + { + statement.TryBind("@ResultId", id.ToGuidParamValue()); + statement.MoveNext(); + + foreach (var row in statement.ExecuteQuery()) + { + return GetResult(row); + } + } + + return null; } - - return null; } } diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index 9f7cce13b..feeb212f1 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -159,8 +159,8 @@ namespace Emby.Server.Implementations.Data { using (var statement = db.PrepareStatement("replace into userdata (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)")) { - statement.TryBind("@UserId", userId.ToGuidParamValue()); - statement.TryBind("@Key", key); + statement.TryBind("@userId", userId.ToGuidParamValue()); + statement.TryBind("@key", key); if (userData.Rating.HasValue) { diff --git a/MediaBrowser.Controller/Entities/Audio/MusicGenre.cs b/MediaBrowser.Controller/Entities/Audio/MusicGenre.cs index c930f2563..d75b31f6a 100644 --- a/MediaBrowser.Controller/Entities/Audio/MusicGenre.cs +++ b/MediaBrowser.Controller/Entities/Audio/MusicGenre.cs @@ -31,6 +31,15 @@ namespace MediaBrowser.Controller.Entities.Audio get { return true; } } + [IgnoreDataMember] + public override bool SupportsAncestors + { + get + { + return false; + } + } + /// /// Returns the folder containing the item. /// If the item is a folder, it returns the folder itself diff --git a/MediaBrowser.Controller/Entities/GameGenre.cs b/MediaBrowser.Controller/Entities/GameGenre.cs index d62d3c8fd..22a8675c5 100644 --- a/MediaBrowser.Controller/Entities/GameGenre.cs +++ b/MediaBrowser.Controller/Entities/GameGenre.cs @@ -37,6 +37,15 @@ namespace MediaBrowser.Controller.Entities } } + [IgnoreDataMember] + public override bool SupportsAncestors + { + get + { + return false; + } + } + /// /// Gets a value indicating whether this instance is owned item. /// diff --git a/MediaBrowser.Controller/Entities/Genre.cs b/MediaBrowser.Controller/Entities/Genre.cs index 731391bc4..da4ee352f 100644 --- a/MediaBrowser.Controller/Entities/Genre.cs +++ b/MediaBrowser.Controller/Entities/Genre.cs @@ -40,6 +40,15 @@ namespace MediaBrowser.Controller.Entities } } + [IgnoreDataMember] + public override bool SupportsAncestors + { + get + { + return false; + } + } + public override bool IsSaveLocalMetadataEnabled() { return true; diff --git a/MediaBrowser.Controller/Entities/Studio.cs b/MediaBrowser.Controller/Entities/Studio.cs index 508eb1514..ec623eeda 100644 --- a/MediaBrowser.Controller/Entities/Studio.cs +++ b/MediaBrowser.Controller/Entities/Studio.cs @@ -39,6 +39,15 @@ namespace MediaBrowser.Controller.Entities } } + [IgnoreDataMember] + public override bool SupportsAncestors + { + get + { + return false; + } + } + public override bool CanDelete() { return false; diff --git a/MediaBrowser.Controller/Entities/Year.cs b/MediaBrowser.Controller/Entities/Year.cs index ab904cbd8..75fb69435 100644 --- a/MediaBrowser.Controller/Entities/Year.cs +++ b/MediaBrowser.Controller/Entities/Year.cs @@ -33,6 +33,15 @@ namespace MediaBrowser.Controller.Entities } } + [IgnoreDataMember] + public override bool SupportsAncestors + { + get + { + return false; + } + } + public override bool CanDelete() { return false; -- cgit v1.2.3 From 8bc4d49c8967f850d0b76ee8896bbc8ce3e50424 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Wed, 23 Nov 2016 01:54:09 -0500 Subject: fix scanning of new libraries --- .../Data/SqliteFileOrganizationRepository.cs | 1 - .../Data/SqliteItemRepository.cs | 41 ++++++++++-------- .../LiveTv/TunerHosts/MulticastStream.cs | 4 +- .../LiveTv/TunerHosts/QueueStream.cs | 10 ++++- MediaBrowser.Providers/Manager/MetadataService.cs | 5 +++ .../TV/EpisodeMetadataService.cs | 49 +++++++++++----------- MediaBrowser.Providers/TV/SeasonMetadataService.cs | 14 +++++-- 7 files changed, 75 insertions(+), 49 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs') diff --git a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs index 2d1aabf5f..23bab883e 100644 --- a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs @@ -194,7 +194,6 @@ namespace Emby.Server.Implementations.Data using (var statement = connection.PrepareStatement("select ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults where ResultId=@ResultId")) { statement.TryBind("@ResultId", id.ToGuidParamValue()); - statement.MoveNext(); foreach (var row in statement.ExecuteQuery()) { diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 78d78a9df..f017a21dc 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -638,25 +638,30 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); + var tuples = new List>>(); + foreach (var item in items) + { + var ancestorIds = item.SupportsAncestors ? + item.GetAncestorIds().Distinct().ToList() : + null; + + tuples.Add(new Tuple>(item, ancestorIds)); + } + using (var connection = CreateConnection()) { using (WriteLock.Write()) { connection.RunInTransaction(db => { - SaveItemsInTranscation(db, items); + SaveItemsInTranscation(db, tuples); }); } } } - private void SaveItemsInTranscation(IDatabaseConnection db, List items) + private void SaveItemsInTranscation(IDatabaseConnection db, List>> tuples) { - if (items == null) - { - throw new ArgumentNullException("items"); - } - var requiresReset = false; using (var saveItemStatement = db.PrepareStatement(GetSaveItemCommandText())) @@ -665,19 +670,21 @@ namespace Emby.Server.Implementations.Data { using (var updateAncestorsStatement = db.PrepareStatement("insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values (@ItemId, @AncestorId, @AncestorIdText)")) { - foreach (var item in items) + foreach (var tuple in tuples) { if (requiresReset) { saveItemStatement.Reset(); } + var item = tuple.Item1; + SaveItem(item, saveItemStatement); //Logger.Debug(_saveItemCommand.CommandText); if (item.SupportsAncestors) { - UpdateAncestors(item.Id, item.GetAncestorIds().Distinct().ToList(), db, deleteAncestorsStatement, updateAncestorsStatement); + UpdateAncestors(item.Id, tuple.Item2, db, deleteAncestorsStatement, updateAncestorsStatement); } UpdateItemValues(item.Id, GetItemValuesToSave(item), db); @@ -802,7 +809,7 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@IsHD", item.IsHD); saveItemStatement.TryBind("@ExternalEtag", item.ExternalEtag); - if (item.DateLastRefreshed == default(DateTime)) + if (item.DateLastRefreshed != default(DateTime)) { saveItemStatement.TryBind("@DateLastRefreshed", item.DateLastRefreshed); } @@ -811,9 +818,9 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBindNull("@DateLastRefreshed"); } - if (item.DateLastSaved == default(DateTime)) + if (item.DateLastSaved != default(DateTime)) { - saveItemStatement.TryBind("@DateLastSaved", item.DateLastRefreshed); + saveItemStatement.TryBind("@DateLastSaved", item.DateLastSaved); } else { @@ -948,7 +955,7 @@ namespace Emby.Server.Implementations.Data var hasSeries = item as IHasSeries; if (hasSeries != null) { - saveItemStatement.TryBind("@SeriesName", hasSeries.FindSeriesName()); + saveItemStatement.TryBind("@SeriesName", hasSeries.SeriesName); } else { @@ -960,8 +967,8 @@ namespace Emby.Server.Implementations.Data var episode = item as Episode; if (episode != null) { - saveItemStatement.TryBind("@SeasonName", episode.FindSeasonName()); - saveItemStatement.TryBind("@SeasonId", episode.FindSeasonId()); + saveItemStatement.TryBind("@SeasonName", episode.SeasonName); + saveItemStatement.TryBind("@SeasonId", episode.SeasonId); } else { @@ -971,8 +978,8 @@ namespace Emby.Server.Implementations.Data if (hasSeries != null) { - saveItemStatement.TryBind("@SeriesId", hasSeries.FindSeriesId()); - saveItemStatement.TryBind("@SeriesSortName", hasSeries.FindSeriesSortName()); + saveItemStatement.TryBind("@SeriesId", hasSeries.SeriesId); + saveItemStatement.TryBind("@SeriesSortName", hasSeries.SeriesSortName); } else { diff --git a/Emby.Server.Implementations/LiveTv/TunerHosts/MulticastStream.cs b/Emby.Server.Implementations/LiveTv/TunerHosts/MulticastStream.cs index 360a2cee7..7b88be19c 100644 --- a/Emby.Server.Implementations/LiveTv/TunerHosts/MulticastStream.cs +++ b/Emby.Server.Implementations/LiveTv/TunerHosts/MulticastStream.cs @@ -25,10 +25,10 @@ namespace Emby.Server.Implementations.LiveTv.TunerHosts { _cancellationToken = cancellationToken; + byte[] buffer = new byte[BufferSize]; + while (!cancellationToken.IsCancellationRequested) { - byte[] buffer = new byte[BufferSize]; - var bytesRead = await source.ReadAsync(buffer, 0, buffer.Length, cancellationToken).ConfigureAwait(false); if (bytesRead > 0) diff --git a/Emby.Server.Implementations/LiveTv/TunerHosts/QueueStream.cs b/Emby.Server.Implementations/LiveTv/TunerHosts/QueueStream.cs index 7605641b2..bd6f31906 100644 --- a/Emby.Server.Implementations/LiveTv/TunerHosts/QueueStream.cs +++ b/Emby.Server.Implementations/LiveTv/TunerHosts/QueueStream.cs @@ -19,6 +19,7 @@ namespace Emby.Server.Implementations.LiveTv.TunerHosts public Action OnFinished { get; set; } private readonly ILogger _logger; + private bool _isActive; public QueueStream(Stream outputStream, ILogger logger) { @@ -29,7 +30,10 @@ namespace Emby.Server.Implementations.LiveTv.TunerHosts public void Queue(byte[] bytes) { - _queue.Enqueue(bytes); + if (_isActive) + { + _queue.Enqueue(bytes); + } } public void Start(CancellationToken cancellationToken) @@ -57,6 +61,8 @@ namespace Emby.Server.Implementations.LiveTv.TunerHosts { while (!cancellationToken.IsCancellationRequested) { + _isActive = true; + var bytes = Dequeue(); if (bytes != null) { @@ -83,6 +89,8 @@ namespace Emby.Server.Implementations.LiveTv.TunerHosts } finally { + _isActive = false; + if (OnFinished != null) { OnFinished(this); diff --git a/MediaBrowser.Providers/Manager/MetadataService.cs b/MediaBrowser.Providers/Manager/MetadataService.cs index 0491e800f..9f8dca434 100644 --- a/MediaBrowser.Providers/Manager/MetadataService.cs +++ b/MediaBrowser.Providers/Manager/MetadataService.cs @@ -418,6 +418,11 @@ namespace MediaBrowser.Providers.Manager // If any remote providers changed, run them all so that priorities can be honored if (i is IRemoteMetadataProvider) { + if (options.MetadataRefreshMode == MetadataRefreshMode.ValidationOnly) + { + return false; + } + return anyRemoteProvidersChanged; } diff --git a/MediaBrowser.Providers/TV/EpisodeMetadataService.cs b/MediaBrowser.Providers/TV/EpisodeMetadataService.cs index 3de79f779..2e4271c0f 100644 --- a/MediaBrowser.Providers/TV/EpisodeMetadataService.cs +++ b/MediaBrowser.Providers/TV/EpisodeMetadataService.cs @@ -20,40 +20,39 @@ namespace MediaBrowser.Providers.TV { var updateType = await base.BeforeSave(item, isFullRefresh, currentUpdateType).ConfigureAwait(false); - if (updateType <= ItemUpdateType.None) + var seriesName = item.FindSeriesName(); + if (!string.Equals(item.SeriesName, seriesName, StringComparison.Ordinal)) { - if (!string.Equals(item.SeriesName, item.FindSeriesName(), StringComparison.Ordinal)) - { - updateType |= ItemUpdateType.MetadataImport; - } + item.SeriesName = seriesName; + updateType |= ItemUpdateType.MetadataImport; } - if (updateType <= ItemUpdateType.None) + + var seriesSortName = item.FindSeriesSortName(); + if (!string.Equals(item.SeriesSortName, seriesSortName, StringComparison.Ordinal)) { - if (!string.Equals(item.SeriesSortName, item.FindSeriesSortName(), StringComparison.Ordinal)) - { - updateType |= ItemUpdateType.MetadataImport; - } + item.SeriesSortName = seriesSortName; + updateType |= ItemUpdateType.MetadataImport; } - if (updateType <= ItemUpdateType.None) + + var seasonName = item.FindSeasonName(); + if (!string.Equals(item.SeasonName, seasonName, StringComparison.Ordinal)) { - if (!string.Equals(item.SeasonName, item.FindSeasonName(), StringComparison.Ordinal)) - { - updateType |= ItemUpdateType.MetadataImport; - } + item.SeasonName = seasonName; + updateType |= ItemUpdateType.MetadataImport; } - if (updateType <= ItemUpdateType.None) + + var seriesId = item.FindSeriesId(); + if (item.SeriesId != seriesId) { - if (item.SeriesId != item.FindSeriesId()) - { - updateType |= ItemUpdateType.MetadataImport; - } + item.SeriesId = seriesId; + updateType |= ItemUpdateType.MetadataImport; } - if (updateType <= ItemUpdateType.None) + + var seasonId = item.FindSeasonId(); + if (item.SeasonId != seasonId) { - if (item.SeasonId != item.FindSeasonId()) - { - updateType |= ItemUpdateType.MetadataImport; - } + item.SeasonId = seasonId; + updateType |= ItemUpdateType.MetadataImport; } return updateType; diff --git a/MediaBrowser.Providers/TV/SeasonMetadataService.cs b/MediaBrowser.Providers/TV/SeasonMetadataService.cs index b973620f1..81a0c0b4a 100644 --- a/MediaBrowser.Providers/TV/SeasonMetadataService.cs +++ b/MediaBrowser.Providers/TV/SeasonMetadataService.cs @@ -37,16 +37,24 @@ namespace MediaBrowser.Providers.TV updateType |= SaveIsVirtualItem(item, episodes); } - if (!string.Equals(item.SeriesName, item.FindSeriesName(), StringComparison.Ordinal)) + var seriesName = item.FindSeriesName(); + if (!string.Equals(item.SeriesName, seriesName, StringComparison.Ordinal)) { + item.SeriesName = seriesName; updateType |= ItemUpdateType.MetadataImport; } - if (!string.Equals(item.SeriesSortName, item.FindSeriesSortName(), StringComparison.Ordinal)) + + var seriesSortName = item.FindSeriesSortName(); + if (!string.Equals(item.SeriesSortName, seriesSortName, StringComparison.Ordinal)) { + item.SeriesSortName = seriesSortName; updateType |= ItemUpdateType.MetadataImport; } - if (item.SeriesId != item.FindSeriesId()) + + var seriesId = item.FindSeriesId(); + if (item.SeriesId != seriesId) { + item.SeriesId = seriesId; updateType |= ItemUpdateType.MetadataImport; } -- cgit v1.2.3 From 1c52e4f51b1a9732264203fb0d987cf9752ef45b Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Mon, 28 Nov 2016 14:26:48 -0500 Subject: update transaction modes --- .../Activity/ActivityRepository.cs | 2 +- .../Data/BaseSqliteRepository.cs | 5 +++++ .../Data/SqliteDisplayPreferencesRepository.cs | 4 ++-- .../Data/SqliteFileOrganizationRepository.cs | 6 +++--- .../Data/SqliteItemRepository.cs | 8 ++++---- .../Data/SqliteUserDataRepository.cs | 10 +++++----- .../Data/SqliteUserRepository.cs | 4 ++-- .../Notifications/SqliteNotificationsRepository.cs | 6 +++--- .../Security/AuthenticationRepository.cs | 18 ++++++++++-------- .../Social/SharingRepository.cs | 2 +- Emby.Server.Implementations/Sync/SyncRepository.cs | 8 ++++---- 11 files changed, 40 insertions(+), 33 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs') diff --git a/Emby.Server.Implementations/Activity/ActivityRepository.cs b/Emby.Server.Implementations/Activity/ActivityRepository.cs index ba81992ac..f7419de80 100644 --- a/Emby.Server.Implementations/Activity/ActivityRepository.cs +++ b/Emby.Server.Implementations/Activity/ActivityRepository.cs @@ -79,7 +79,7 @@ namespace Emby.Server.Implementations.Activity statement.MoveNext(); } - }); + }, TransactionMode); } } } diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index 308b8356f..af8de0e84 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -30,6 +30,11 @@ namespace Emby.Server.Implementations.Data get { return false; } } + protected TransactionMode TransactionMode + { + get { return TransactionMode.Immediate; } + } + static BaseSqliteRepository() { SQLite3.EnableSharedCache = false; diff --git a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs index 1bd64b21d..743186db2 100644 --- a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs @@ -100,7 +100,7 @@ namespace Emby.Server.Implementations.Data connection.RunInTransaction(db => { SaveDisplayPreferences(displayPreferences, userId, client, db); - }); + }, TransactionMode); } } } @@ -147,7 +147,7 @@ namespace Emby.Server.Implementations.Data { SaveDisplayPreferences(displayPreference, userId, displayPreference.Client, db); } - }); + }, TransactionMode); } } } diff --git a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs index 23bab883e..448d38c8c 100644 --- a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs @@ -85,7 +85,7 @@ namespace Emby.Server.Implementations.Data statement.MoveNext(); } - }); + }, TransactionMode); } } } @@ -108,7 +108,7 @@ namespace Emby.Server.Implementations.Data statement.TryBind("@ResultId", id.ToGuidParamValue()); statement.MoveNext(); } - }); + }, TransactionMode); } } } @@ -124,7 +124,7 @@ namespace Emby.Server.Implementations.Data var commandText = "delete from FileOrganizerResults"; db.Execute(commandText); - }); + }, TransactionMode); } } } diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 29aacc059..54820ab53 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -316,7 +316,7 @@ namespace Emby.Server.Implementations.Data AddColumn(db, "MediaStreams", "RefFrames", "INT", existingColumnNames); AddColumn(db, "MediaStreams", "KeyFrames", "TEXT", existingColumnNames); AddColumn(db, "MediaStreams", "IsAnamorphic", "BIT", existingColumnNames); - }); + }, TransactionMode); string[] postQueries = @@ -697,7 +697,7 @@ namespace Emby.Server.Implementations.Data connection.RunInTransaction(db => { SaveItemsInTranscation(db, tuples); - }); + }, TransactionMode); } } } @@ -2211,7 +2211,7 @@ namespace Emby.Server.Implementations.Data index++; } } - }); + }, TransactionMode); } } } @@ -4531,7 +4531,7 @@ namespace Emby.Server.Implementations.Data // Delete the item ExecuteWithSingleParam(db, "delete from TypedBaseItems where guid=@Id", id.ToGuidParamValue()); - }); + }, TransactionMode); } } } diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index b01f215e0..f7184540a 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -78,7 +78,7 @@ namespace Emby.Server.Implementations.Data AddColumn(db, "userdata", "AudioStreamIndex", "int", existingColumnNames); AddColumn(db, "userdata", "SubtitleStreamIndex", "int", existingColumnNames); - }); + }, TransactionMode); ImportUserDataIfNeeded(connection); } @@ -116,7 +116,7 @@ namespace Emby.Server.Implementations.Data statement.TryBind("@IsUserDataImported", true); statement.MoveNext(); } - }); + }, TransactionMode); } private void ImportUserData(IDatabaseConnection connection, string file) @@ -128,7 +128,7 @@ namespace Emby.Server.Implementations.Data connection.RunInTransaction(db => { db.Execute("REPLACE INTO userdata(" + columns + ") SELECT " + columns + " FROM UserDataBackup.userdata;"); - }); + }, TransactionMode); } /// @@ -197,7 +197,7 @@ namespace Emby.Server.Implementations.Data connection.RunInTransaction(db => { SaveUserData(db, userId, key, userData); - }); + }, TransactionMode); } } } @@ -271,7 +271,7 @@ namespace Emby.Server.Implementations.Data { SaveUserData(db, userId, userItemData.Key, userItemData); } - }); + }, TransactionMode); } } } diff --git a/Emby.Server.Implementations/Data/SqliteUserRepository.cs b/Emby.Server.Implementations/Data/SqliteUserRepository.cs index f902d981f..5fb27d081 100644 --- a/Emby.Server.Implementations/Data/SqliteUserRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserRepository.cs @@ -102,7 +102,7 @@ namespace Emby.Server.Implementations.Data statement.TryBind("@data", serialized); statement.MoveNext(); } - }); + }, TransactionMode); } } } @@ -164,7 +164,7 @@ namespace Emby.Server.Implementations.Data statement.TryBind("@id", user.Id.ToGuidParamValue()); statement.MoveNext(); } - }); + }, TransactionMode); } } } diff --git a/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs b/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs index 66ef5d5d1..5f0d4d29a 100644 --- a/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs +++ b/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs @@ -249,7 +249,7 @@ namespace Emby.Server.Implementations.Notifications statement.MoveNext(); } - }); + }, TransactionMode); } } } @@ -304,7 +304,7 @@ namespace Emby.Server.Implementations.Notifications statement.MoveNext(); } - }); + }, TransactionMode); } } } @@ -334,7 +334,7 @@ namespace Emby.Server.Implementations.Notifications } } - }); + }, TransactionMode); } } } diff --git a/Emby.Server.Implementations/Security/AuthenticationRepository.cs b/Emby.Server.Implementations/Security/AuthenticationRepository.cs index 2632b9666..e498253c3 100644 --- a/Emby.Server.Implementations/Security/AuthenticationRepository.cs +++ b/Emby.Server.Implementations/Security/AuthenticationRepository.cs @@ -50,7 +50,8 @@ namespace Emby.Server.Implementations.Security var existingColumnNames = GetColumnNames(db, "AccessTokens"); AddColumn(db, "AccessTokens", "AppVersion", "TEXT", existingColumnNames); - }); + + }, TransactionMode); } } @@ -70,9 +71,9 @@ namespace Emby.Server.Implementations.Security cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(db => { @@ -100,7 +101,8 @@ namespace Emby.Server.Implementations.Security statement.MoveNext(); } - }); + + }, TransactionMode); } } } @@ -137,9 +139,9 @@ namespace Emby.Server.Implementations.Security throw new ArgumentNullException("query"); } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { var commandText = BaseSelectText; @@ -244,9 +246,9 @@ namespace Emby.Server.Implementations.Security throw new ArgumentNullException("id"); } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { var commandText = BaseSelectText + " where Id=@Id"; diff --git a/Emby.Server.Implementations/Social/SharingRepository.cs b/Emby.Server.Implementations/Social/SharingRepository.cs index 6dab54bc6..71f80a575 100644 --- a/Emby.Server.Implementations/Social/SharingRepository.cs +++ b/Emby.Server.Implementations/Social/SharingRepository.cs @@ -70,7 +70,7 @@ namespace Emby.Server.Implementations.Social info.ItemId, info.UserId, info.ExpirationDate.ToDateTimeParamValue()); - }); + }, TransactionMode); } } } diff --git a/Emby.Server.Implementations/Sync/SyncRepository.cs b/Emby.Server.Implementations/Sync/SyncRepository.cs index b2d9fbcc9..caf98e666 100644 --- a/Emby.Server.Implementations/Sync/SyncRepository.cs +++ b/Emby.Server.Implementations/Sync/SyncRepository.cs @@ -79,7 +79,7 @@ namespace Emby.Server.Implementations.Sync existingColumnNames = GetColumnNames(db, "SyncJobItems"); AddColumn(db, "SyncJobItems", "ItemDateModifiedTicks", "BIGINT", existingColumnNames); - }); + }, TransactionMode); } } @@ -268,7 +268,7 @@ namespace Emby.Server.Implementations.Sync connection.RunInTransaction(conn => { conn.Execute(commandText, paramList.ToArray()); - }); + }, TransactionMode); } } } @@ -290,7 +290,7 @@ namespace Emby.Server.Implementations.Sync { conn.Execute("delete from SyncJobs where Id=?", id.ToGuidParamValue()); conn.Execute("delete from SyncJobItems where JobId=?", id); - }); + }, TransactionMode); } } } @@ -743,7 +743,7 @@ namespace Emby.Server.Implementations.Sync connection.RunInTransaction(conn => { conn.Execute(commandText, paramList.ToArray()); - }); + }, TransactionMode); } } } -- cgit v1.2.3 From 61e195c096dfc2c5713bbbcbc0be73ddff99cb55 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Tue, 29 Nov 2016 14:12:37 -0500 Subject: update repositories --- .../Activity/ActivityRepository.cs | 20 ++-- .../Data/BaseSqliteRepository.cs | 42 +++++--- .../Data/SqliteDisplayPreferencesRepository.cs | 7 +- .../Data/SqliteFileOrganizationRepository.cs | 7 +- .../Data/SqliteItemRepository.cs | 11 +- .../Data/SqliteUserDataRepository.cs | 2 - .../Data/SqliteUserRepository.cs | 7 +- .../Notifications/SqliteNotificationsRepository.cs | 11 +- .../Security/AuthenticationRepository.cs | 119 ++++++++++----------- .../Social/SharingRepository.cs | 7 +- Emby.Server.Implementations/Sync/SyncRepository.cs | 7 +- 11 files changed, 101 insertions(+), 139 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs') diff --git a/Emby.Server.Implementations/Activity/ActivityRepository.cs b/Emby.Server.Implementations/Activity/ActivityRepository.cs index f7419de80..9513c224f 100644 --- a/Emby.Server.Implementations/Activity/ActivityRepository.cs +++ b/Emby.Server.Implementations/Activity/ActivityRepository.cs @@ -27,17 +27,11 @@ namespace Emby.Server.Implementations.Activity { using (var connection = CreateConnection()) { - connection.ExecuteAll(string.Join(";", new[] - { - "PRAGMA page_size=4096", - "pragma default_temp_store = memory", - "pragma temp_store = memory" - })); + RunDefaultInitialization(connection); string[] queries = { - - "create table if not exists ActivityLogEntries (Id GUID PRIMARY KEY, Name TEXT, Overview TEXT, ShortOverview TEXT, Type TEXT, ItemId TEXT, UserId TEXT, DateCreated DATETIME, LogSeverity TEXT)", - "create index if not exists idx_ActivityLogEntries on ActivityLogEntries(Id)" + "create table if not exists ActivityLogEntries (Id GUID PRIMARY KEY, Name TEXT, Overview TEXT, ShortOverview TEXT, Type TEXT, ItemId TEXT, UserId TEXT, DateCreated DATETIME, LogSeverity TEXT)", + "create index if not exists idx_ActivityLogEntries on ActivityLogEntries(Id)" }; connection.RunQueries(queries); @@ -58,9 +52,9 @@ namespace Emby.Server.Implementations.Activity throw new ArgumentNullException("entry"); } - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunInTransaction(db => { @@ -86,9 +80,9 @@ namespace Emby.Server.Implementations.Activity public QueryResult GetActivityLogEntries(DateTime? minDate, int? startIndex, int? limit) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { var commandText = BaseActivitySelectText; var whereClauses = new List(); diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index af8de0e84..e066d02d3 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -67,20 +67,8 @@ namespace Emby.Server.Implementations.Data //Logger.Info("Opening write connection"); } - isReadOnly = false; - - if (isReadOnly) - { - connectionFlags = ConnectionFlags.ReadOnly; - //connectionFlags = ConnectionFlags.Create; - //connectionFlags |= ConnectionFlags.ReadWrite; - } - else - { - connectionFlags = ConnectionFlags.Create; - connectionFlags |= ConnectionFlags.ReadWrite; - } - + connectionFlags = ConnectionFlags.Create; + connectionFlags |= ConnectionFlags.ReadWrite; connectionFlags |= ConnectionFlags.SharedCached; connectionFlags |= ConnectionFlags.NoMutex; @@ -89,6 +77,8 @@ namespace Emby.Server.Implementations.Data if (string.IsNullOrWhiteSpace(_defaultWal)) { _defaultWal = db.Query("PRAGMA journal_mode").SelectScalarString().First(); + + Logger.Info("Default journal_mode for {0} is {1}", DbFilePath, _defaultWal); } var queries = new List @@ -115,7 +105,7 @@ namespace Emby.Server.Implementations.Data //Logger.Info("synchronous: " + db.Query("PRAGMA synchronous").SelectScalarString().First()); //Logger.Info("temp_store: " + db.Query("PRAGMA temp_store").SelectScalarString().First()); - if (!string.Equals(_defaultWal, "wal", StringComparison.OrdinalIgnoreCase)) + /*if (!string.Equals(_defaultWal, "wal", StringComparison.OrdinalIgnoreCase)) { queries.Add("PRAGMA journal_mode=WAL"); @@ -124,7 +114,7 @@ namespace Emby.Server.Implementations.Data db.ExecuteAll(string.Join(";", queries.ToArray())); } } - else if (queries.Count > 0) + else*/ if (queries.Count > 0) { db.ExecuteAll(string.Join(";", queries.ToArray())); } @@ -132,6 +122,26 @@ namespace Emby.Server.Implementations.Data return db; } + protected void RunDefaultInitialization(IDatabaseConnection db) + { + var queries = new List + { + "PRAGMA journal_mode=WAL", + "PRAGMA page_size=4096", + }; + + if (EnableTempStoreMemory) + { + queries.AddRange(new List + { + "pragma default_temp_store = memory", + "pragma temp_store = memory" + }); + } + + db.ExecuteAll(string.Join(";", queries.ToArray())); + } + protected virtual bool EnableTempStoreMemory { get diff --git a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs index 743186db2..0197efb51 100644 --- a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs @@ -54,12 +54,7 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection()) { - connection.ExecuteAll(string.Join(";", new[] - { - "PRAGMA page_size=4096", - "pragma default_temp_store = memory", - "pragma temp_store = memory" - })); + RunDefaultInitialization(connection); string[] queries = { diff --git a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs index 448d38c8c..603437120 100644 --- a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs @@ -31,12 +31,7 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection()) { - connection.ExecuteAll(string.Join(";", new[] - { - "PRAGMA page_size=4096", - "pragma default_temp_store = memory", - "pragma temp_store = memory" - })); + RunDefaultInitialization(connection); string[] queries = { diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 54820ab53..9c096916f 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -157,12 +157,7 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection()) { - connection.ExecuteAll(string.Join(";", new[] - { - "PRAGMA page_size=4096", - "PRAGMA default_temp_store=memory", - "PRAGMA temp_store=memory" - })); + RunDefaultInitialization(connection); var createMediaStreamsTableCommand = "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, TimeBase TEXT NULL, CodecTimeBase TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))"; @@ -396,9 +391,9 @@ namespace Emby.Server.Implementations.Data { try { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { connection.RunQueries(new string[] { diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index f7184540a..b65e5d1b3 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -51,8 +51,6 @@ namespace Emby.Server.Implementations.Data { string[] queries = { - "pragma temp_store = memory", - "create table if not exists userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null)", "create table if not exists DataSettings (IsUserDataImported bit)", diff --git a/Emby.Server.Implementations/Data/SqliteUserRepository.cs b/Emby.Server.Implementations/Data/SqliteUserRepository.cs index 5fb27d081..99d7563c7 100644 --- a/Emby.Server.Implementations/Data/SqliteUserRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserRepository.cs @@ -50,12 +50,7 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection()) { - connection.ExecuteAll(string.Join(";", new[] - { - "PRAGMA page_size=4096", - "pragma default_temp_store = memory", - "pragma temp_store = memory" - })); + RunDefaultInitialization(connection); string[] queries = { diff --git a/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs b/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs index 5f0d4d29a..767ba5504 100644 --- a/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs +++ b/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs @@ -29,12 +29,7 @@ namespace Emby.Server.Implementations.Notifications { using (var connection = CreateConnection()) { - connection.ExecuteAll(string.Join(";", new[] - { - "PRAGMA page_size=4096", - "pragma default_temp_store = memory", - "pragma temp_store = memory" - })); + RunDefaultInitialization(connection); string[] queries = { @@ -58,7 +53,7 @@ namespace Emby.Server.Implementations.Notifications using (var connection = CreateConnection(true)) { - //using (WriteLock.Read()) + using (WriteLock.Read()) { var clauses = new List(); var paramList = new List(); @@ -113,7 +108,7 @@ namespace Emby.Server.Implementations.Notifications using (var connection = CreateConnection(true)) { - //using (WriteLock.Read()) + using (WriteLock.Read()) { using (var statement = connection.PrepareStatement("select Level from Notifications where UserId=@UserId and IsRead=@IsRead")) { diff --git a/Emby.Server.Implementations/Security/AuthenticationRepository.cs b/Emby.Server.Implementations/Security/AuthenticationRepository.cs index e498253c3..dbda4a460 100644 --- a/Emby.Server.Implementations/Security/AuthenticationRepository.cs +++ b/Emby.Server.Implementations/Security/AuthenticationRepository.cs @@ -30,12 +30,7 @@ namespace Emby.Server.Implementations.Security { using (var connection = CreateConnection()) { - connection.ExecuteAll(string.Join(";", new[] - { - "PRAGMA page_size=4096", - "pragma default_temp_store = memory", - "pragma temp_store = memory" - })); + RunDefaultInitialization(connection); string[] queries = { @@ -139,78 +134,78 @@ namespace Emby.Server.Implementations.Security throw new ArgumentNullException("query"); } - using (var connection = CreateConnection(true)) - { - using (WriteLock.Read()) - { - var commandText = BaseSelectText; + var commandText = BaseSelectText; - var whereClauses = new List(); + var whereClauses = new List(); - var startIndex = query.StartIndex ?? 0; + var startIndex = query.StartIndex ?? 0; - if (!string.IsNullOrWhiteSpace(query.AccessToken)) - { - whereClauses.Add("AccessToken=@AccessToken"); - } + if (!string.IsNullOrWhiteSpace(query.AccessToken)) + { + whereClauses.Add("AccessToken=@AccessToken"); + } - if (!string.IsNullOrWhiteSpace(query.UserId)) - { - whereClauses.Add("UserId=@UserId"); - } + if (!string.IsNullOrWhiteSpace(query.UserId)) + { + whereClauses.Add("UserId=@UserId"); + } - if (!string.IsNullOrWhiteSpace(query.DeviceId)) - { - whereClauses.Add("DeviceId=@DeviceId"); - } + if (!string.IsNullOrWhiteSpace(query.DeviceId)) + { + whereClauses.Add("DeviceId=@DeviceId"); + } - if (query.IsActive.HasValue) - { - whereClauses.Add("IsActive=@IsActive"); - } + if (query.IsActive.HasValue) + { + whereClauses.Add("IsActive=@IsActive"); + } - if (query.HasUser.HasValue) - { - if (query.HasUser.Value) - { - whereClauses.Add("UserId not null"); - } - else - { - whereClauses.Add("UserId is null"); - } - } + if (query.HasUser.HasValue) + { + if (query.HasUser.Value) + { + whereClauses.Add("UserId not null"); + } + else + { + whereClauses.Add("UserId is null"); + } + } - var whereTextWithoutPaging = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + var whereTextWithoutPaging = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - if (startIndex > 0) - { - var pagingWhereText = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + if (startIndex > 0) + { + var pagingWhereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM AccessTokens {0} ORDER BY DateCreated LIMIT {1})", - pagingWhereText, - startIndex.ToString(_usCulture))); - } + whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM AccessTokens {0} ORDER BY DateCreated LIMIT {1})", + pagingWhereText, + startIndex.ToString(_usCulture))); + } - var whereText = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + var whereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - commandText += whereText; + commandText += whereText; - commandText += " ORDER BY DateCreated"; + commandText += " ORDER BY DateCreated"; - if (query.Limit.HasValue) - { - commandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); - } + if (query.Limit.HasValue) + { + commandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); + } - var list = new List(); + var list = new List(); + using (var connection = CreateConnection(true)) + { + using (WriteLock.Read()) + { using (var statement = connection.PrepareStatement(commandText)) { BindAuthenticationQueryParams(query, statement); diff --git a/Emby.Server.Implementations/Social/SharingRepository.cs b/Emby.Server.Implementations/Social/SharingRepository.cs index 71f80a575..12d846e81 100644 --- a/Emby.Server.Implementations/Social/SharingRepository.cs +++ b/Emby.Server.Implementations/Social/SharingRepository.cs @@ -27,12 +27,7 @@ namespace Emby.Server.Implementations.Social { using (var connection = CreateConnection()) { - connection.ExecuteAll(string.Join(";", new[] - { - "PRAGMA page_size=4096", - "pragma default_temp_store = memory", - "pragma temp_store = memory" - })); + RunDefaultInitialization(connection); string[] queries = { diff --git a/Emby.Server.Implementations/Sync/SyncRepository.cs b/Emby.Server.Implementations/Sync/SyncRepository.cs index caf98e666..037507872 100644 --- a/Emby.Server.Implementations/Sync/SyncRepository.cs +++ b/Emby.Server.Implementations/Sync/SyncRepository.cs @@ -43,12 +43,7 @@ namespace Emby.Server.Implementations.Sync { using (var connection = CreateConnection()) { - connection.ExecuteAll(string.Join(";", new[] - { - "PRAGMA page_size=4096", - "pragma default_temp_store = memory", - "pragma temp_store = memory" - })); + RunDefaultInitialization(connection); string[] queries = { -- cgit v1.2.3 From a9a808a9c407a14af9e041cff20e7fe6af3e5061 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Sun, 11 Dec 2016 00:12:00 -0500 Subject: fix db locking errors --- .../Activity/ActivityRepository.cs | 8 +- .../Data/BaseSqliteRepository.cs | 114 ++++++++++-- .../Data/SqliteDisplayPreferencesRepository.cs | 16 +- .../Data/SqliteFileOrganizationRepository.cs | 20 +-- .../Data/SqliteItemRepository.cs | 197 +++++++++++---------- .../Data/SqliteUserDataRepository.cs | 16 +- .../Data/SqliteUserRepository.cs | 12 +- .../Notifications/SqliteNotificationsRepository.cs | 36 ++-- .../Security/AuthenticationRepository.cs | 12 +- .../Social/SharingRepository.cs | 8 +- Emby.Server.Implementations/Sync/SyncRepository.cs | 74 ++++---- MediaBrowser.Controller/Entities/TV/Season.cs | 6 +- 12 files changed, 306 insertions(+), 213 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs') diff --git a/Emby.Server.Implementations/Activity/ActivityRepository.cs b/Emby.Server.Implementations/Activity/ActivityRepository.cs index 9513c224f..17aef7268 100644 --- a/Emby.Server.Implementations/Activity/ActivityRepository.cs +++ b/Emby.Server.Implementations/Activity/ActivityRepository.cs @@ -52,9 +52,9 @@ namespace Emby.Server.Implementations.Activity throw new ArgumentNullException("entry"); } - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -80,9 +80,9 @@ namespace Emby.Server.Implementations.Activity public QueryResult GetActivityLogEntries(DateTime? minDate, int? startIndex, int? limit) { - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { var commandText = BaseActivitySelectText; var whereClauses = new List(); diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index f132c9765..bef2ce149 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -20,27 +20,34 @@ namespace Emby.Server.Implementations.Data { Logger = logger; - WriteLock = AllowLockRecursion ? - new ReaderWriterLockSlim(LockRecursionPolicy.SupportsRecursion) : - new ReaderWriterLockSlim(LockRecursionPolicy.NoRecursion); + WriteLock = new ReaderWriterLockSlim(LockRecursionPolicy.NoRecursion); } - protected virtual bool AllowLockRecursion + protected TransactionMode TransactionMode { - get { return false; } + get { return TransactionMode.Immediate; } } - protected TransactionMode TransactionMode + protected TransactionMode ReadTransactionMode { - get { return TransactionMode.Immediate; } + get { return TransactionMode.Deferred; } } + internal static int ThreadSafeMode { get; set; } + static BaseSqliteRepository() { SQLite3.EnableSharedCache = false; int rc = raw.sqlite3_config(raw.SQLITE_CONFIG_MEMSTATUS, 0); //CheckOk(rc); + + rc = raw.sqlite3_config(raw.SQLITE_CONFIG_MULTITHREAD, 1); + //CheckOk(rc); + + rc = raw.sqlite3_enable_shared_cache(1); + + ThreadSafeMode = raw.sqlite3_threadsafe(); } private static bool _versionLogged; @@ -61,16 +68,19 @@ namespace Emby.Server.Implementations.Data if (isReadOnly) { //Logger.Info("Opening read connection"); + //connectionFlags = ConnectionFlags.ReadOnly; + connectionFlags = ConnectionFlags.Create; + connectionFlags |= ConnectionFlags.ReadWrite; } else { //Logger.Info("Opening write connection"); + connectionFlags = ConnectionFlags.Create; + connectionFlags |= ConnectionFlags.ReadWrite; } - connectionFlags = ConnectionFlags.Create; - connectionFlags |= ConnectionFlags.ReadWrite; - connectionFlags |= ConnectionFlags.SharedCached; - //connectionFlags |= ConnectionFlags.NoMutex; + //connectionFlags |= ConnectionFlags.SharedCached; + connectionFlags |= ConnectionFlags.NoMutex; var db = SQLite3.Open(DbFilePath, connectionFlags, null); @@ -114,7 +124,8 @@ namespace Emby.Server.Implementations.Data db.ExecuteAll(string.Join(";", queries.ToArray())); } } - else*/ if (queries.Count > 0) + else*/ + if (queries.Count > 0) { db.ExecuteAll(string.Join(";", queries.ToArray())); } @@ -122,6 +133,26 @@ namespace Emby.Server.Implementations.Data return db; } + public IStatement PrepareStatement(IDatabaseConnection connection, string sql) + { + return connection.PrepareStatement(sql); + } + + public IStatement PrepareStatementSafe(IDatabaseConnection connection, string sql) + { + return connection.PrepareStatement(sql); + } + + public List PrepareAll(IDatabaseConnection connection, string sql) + { + return connection.PrepareAll(sql).ToList(); + } + + public List PrepareAllSafe(IDatabaseConnection connection, string sql) + { + return connection.PrepareAll(sql).ToList(); + } + protected void RunDefaultInitialization(IDatabaseConnection db) { var queries = new List @@ -288,12 +319,69 @@ namespace Emby.Server.Implementations.Data } } + public class DummyToken : IDisposable + { + public void Dispose() + { + } + } + public static IDisposable Read(this ReaderWriterLockSlim obj) { - return new ReadLockToken(obj); + //if (BaseSqliteRepository.ThreadSafeMode > 0) + //{ + // return new DummyToken(); + //} + return new WriteLockToken(obj); } public static IDisposable Write(this ReaderWriterLockSlim obj) { + //if (BaseSqliteRepository.ThreadSafeMode > 0) + //{ + // return new DummyToken(); + //} + return new WriteLockToken(obj); + } + } + + public static class SemaphpreSlimExtensions + { + private sealed class WriteLockToken : IDisposable + { + private SemaphoreSlim _sync; + public WriteLockToken(SemaphoreSlim sync) + { + _sync = sync; + var task = sync.WaitAsync(); + Task.WaitAll(task); + } + public void Dispose() + { + if (_sync != null) + { + _sync.Release(); + _sync = null; + } + } + } + + public class DummyToken : IDisposable + { + public void Dispose() + { + } + } + + public static IDisposable Read(this SemaphoreSlim obj) + { + return Write(obj); + } + public static IDisposable Write(this SemaphoreSlim obj) + { + //if (BaseSqliteRepository.ThreadSafeMode > 0) + //{ + // return new DummyToken(); + //} return new WriteLockToken(obj); } } diff --git a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs index 0197efb51..f3d84315e 100644 --- a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs @@ -88,9 +88,9 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -132,9 +132,9 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -164,9 +164,9 @@ namespace Emby.Server.Implementations.Data var guidId = displayPreferencesId.GetMD5(); - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where id = @id and userId=@userId and client=@client")) { @@ -198,9 +198,9 @@ namespace Emby.Server.Implementations.Data { var list = new List(); - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where userId=@userId")) { diff --git a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs index 603437120..9fbe8669d 100644 --- a/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteFileOrganizationRepository.cs @@ -52,9 +52,9 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -92,9 +92,9 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException("id"); } - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -110,9 +110,9 @@ namespace Emby.Server.Implementations.Data public async Task DeleteAll() { - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -131,9 +131,9 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException("query"); } - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { var commandText = "SELECT ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults"; @@ -182,9 +182,9 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException("id"); } - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { using (var statement = connection.PrepareStatement("select ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults where ResultId=@ResultId")) { diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 14723c0a7..1d9cec87e 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -99,14 +99,6 @@ namespace Emby.Server.Implementations.Data DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); } - protected override bool AllowLockRecursion - { - get - { - return true; - } - } - private const string ChaptersTableName = "Chapters2"; protected override int? CacheSize @@ -387,7 +379,7 @@ namespace Emby.Server.Implementations.Data userDataRepo.Initialize(WriteLock); - _backgroundConnection = CreateConnection(true); + //_backgroundConnection = CreateConnection(true); _shrinkMemoryTimer = _timerFactory.Create(OnShrinkMemoryTimerCallback, null, TimeSpan.FromMinutes(1), TimeSpan.FromMinutes(30)); } @@ -396,9 +388,9 @@ namespace Emby.Server.Implementations.Data { try { - using (var connection = CreateConnection()) + lock (WriteLock) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunQueries(new string[] { @@ -682,19 +674,23 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - var tuples = new List>>(); + var tuples = new List, BaseItem, string>>(); foreach (var item in items) { var ancestorIds = item.SupportsAncestors ? item.GetAncestorIds().Distinct().ToList() : null; - tuples.Add(new Tuple>(item, ancestorIds)); + var topParent = item.GetTopParent(); + + var userdataKey = item.GetUserDataKeys().FirstOrDefault(); + + tuples.Add(new Tuple, BaseItem, string>(item, ancestorIds, topParent, userdataKey)); } - using (var connection = CreateConnection()) + lock (WriteLock) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -704,11 +700,11 @@ namespace Emby.Server.Implementations.Data } } - private void SaveItemsInTranscation(IDatabaseConnection db, List>> tuples) + private void SaveItemsInTranscation(IDatabaseConnection db, List, BaseItem, string>> tuples) { var requiresReset = false; - var statements = db.PrepareAll(string.Join(";", new string[] + var statements = PrepareAll(db, string.Join(";", new string[] { GetSaveItemCommandText(), "delete from AncestorIds where ItemId=@ItemId", @@ -729,8 +725,10 @@ namespace Emby.Server.Implementations.Data } var item = tuple.Item1; + var topParent = tuple.Item3; + var userDataKey = tuple.Item4; - SaveItem(item, saveItemStatement); + SaveItem(item, topParent, userDataKey, saveItemStatement); //Logger.Debug(_saveItemCommand.CommandText); if (item.SupportsAncestors) @@ -747,7 +745,7 @@ namespace Emby.Server.Implementations.Data } } - private void SaveItem(BaseItem item, IStatement saveItemStatement) + private void SaveItem(BaseItem item, BaseItem topParent, string userDataKey, IStatement saveItemStatement) { saveItemStatement.TryBind("@guid", item.Id); saveItemStatement.TryBind("@type", item.GetType().FullName); @@ -840,7 +838,7 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBindNull("@Genres"); } - saveItemStatement.TryBind("@InheritedParentalRatingValue", item.GetInheritedParentalRatingValue() ?? 0); + saveItemStatement.TryBind("@InheritedParentalRatingValue", item.InheritedParentalRatingValue); saveItemStatement.TryBind("@SortName", item.SortName); saveItemStatement.TryBind("@RunTimeTicks", item.RunTimeTicks); @@ -922,7 +920,6 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@UnratedType", item.GetBlockUnratedType().ToString()); - var topParent = item.GetTopParent(); if (topParent != null) { //Logger.Debug("Item {0} has top parent {1}", item.Id, topParent.Id); @@ -957,7 +954,7 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@CriticRating", item.CriticRating); saveItemStatement.TryBind("@CriticRatingSummary", item.CriticRatingSummary); - var inheritedTags = item.GetInheritedTags(); + var inheritedTags = item.InheritedTags; if (inheritedTags.Count > 0) { saveItemStatement.TryBind("@InheritedTags", string.Join("|", inheritedTags.ToArray())); @@ -976,7 +973,7 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@CleanName", GetCleanValue(item.Name)); } - saveItemStatement.TryBind("@PresentationUniqueKey", item.GetPresentationUniqueKey()); + saveItemStatement.TryBind("@PresentationUniqueKey", item.PresentationUniqueKey); saveItemStatement.TryBind("@SlugName", item.SlugName); saveItemStatement.TryBind("@OriginalTitle", item.OriginalTitle); @@ -1013,7 +1010,14 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBindNull("@SeriesName"); } - saveItemStatement.TryBind("@UserDataKey", item.GetUserDataKeys().FirstOrDefault()); + if (string.IsNullOrWhiteSpace(userDataKey)) + { + saveItemStatement.TryBindNull("@UserDataKey"); + } + else + { + saveItemStatement.TryBind("@UserDataKey", userDataKey); + } var episode = item as Episode; if (episode != null) @@ -1253,11 +1257,11 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); //Logger.Info("Retrieving item {0}", id.ToString("N")); - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var statement = connection.PrepareStatement("select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid")) + using (var statement = PrepareStatementSafe(connection, "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid")) { statement.TryBind("@guid", id); @@ -2079,11 +2083,11 @@ namespace Emby.Server.Implementations.Data var list = new List(); - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var statement = connection.PrepareStatement("select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc")) + using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc")) { statement.TryBind("@ItemId", id); @@ -2113,11 +2117,11 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException("id"); } - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var statement = connection.PrepareStatement("select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex")) + using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex")) { statement.TryBind("@ItemId", id); statement.TryBind("@ChapterIndex", index); @@ -2194,16 +2198,16 @@ namespace Emby.Server.Implementations.Data var index = 0; - using (var connection = CreateConnection()) + lock (WriteLock) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { // First delete chapters db.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", id.ToGuidParamValue()); - using (var saveChapterStatement = db.PrepareStatement("replace into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath, ImageDateModified) values (@ItemId, @ChapterIndex, @StartPositionTicks, @Name, @ImagePath, @ImageDateModified)")) + using (var saveChapterStatement = PrepareStatement(db, "replace into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath, ImageDateModified) values (@ItemId, @ChapterIndex, @StartPositionTicks, @Name, @ImagePath, @ImageDateModified)")) { foreach (var chapter in chapters) { @@ -2488,11 +2492,11 @@ namespace Emby.Server.Implementations.Data } } - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var statement = connection.PrepareStatement(commandText)) + using (var statement = PrepareStatementSafe(connection, commandText)) { if (EnableJoinUserData(query)) { @@ -2513,9 +2517,9 @@ namespace Emby.Server.Implementations.Data } } } - - LogQueryTime("GetItemList", commandText, now); } + + LogQueryTime("GetItemList", commandText, now); } // Hack for right now since we currently don't support filtering out these duplicates within a query @@ -2683,11 +2687,11 @@ namespace Emby.Server.Implementations.Data statementTexts.Add(commandText); } - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - var statements = connection.PrepareAll(string.Join(";", statementTexts.ToArray())) + var statements = PrepareAllSafe(connection, string.Join(";", statementTexts.ToArray())) .ToList(); if (!isReturningZeroItems) @@ -2902,11 +2906,11 @@ namespace Emby.Server.Implementations.Data var list = new List(); - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var statement = connection.PrepareStatement(commandText)) + using (var statement = PrepareStatementSafe(connection, commandText)) { if (EnableJoinUserData(query)) { @@ -2923,11 +2927,11 @@ namespace Emby.Server.Implementations.Data list.Add(row[0].ReadGuid()); } } + } - LogQueryTime("GetItemList", commandText, now); + LogQueryTime("GetItemList", commandText, now); - return list; - } + return list; } } @@ -2973,11 +2977,11 @@ namespace Emby.Server.Implementations.Data var list = new List>(); - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var statement = connection.PrepareStatement(commandText)) + using (var statement = PrepareStatementSafe(connection, commandText)) { if (EnableJoinUserData(query)) { @@ -2999,11 +3003,11 @@ namespace Emby.Server.Implementations.Data list.Add(new Tuple(id, path)); } } + } - LogQueryTime("GetItemIdsWithPath", commandText, now); + LogQueryTime("GetItemIdsWithPath", commandText, now); - return list; - } + return list; } } @@ -3087,13 +3091,13 @@ namespace Emby.Server.Implementations.Data statementTexts.Add(commandText); } - using (var connection = CreateConnection(true)) + lock (WriteLock) { - var statements = connection.PrepareAll(string.Join(";", statementTexts.ToArray())) - .ToList(); - - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { + var statements = PrepareAllSafe(connection, string.Join(";", statementTexts.ToArray())) + .ToList(); + var totalRecordCount = 0; if (!isReturningZeroItems) @@ -4457,9 +4461,9 @@ namespace Emby.Server.Implementations.Data var commandText = "select Guid,InheritedTags,(select group_concat(Tags, '|') from TypedBaseItems where (guid=outer.guid) OR (guid in (Select AncestorId from AncestorIds where ItemId=Outer.guid))) as NewInheritedTags from typedbaseitems as Outer where NewInheritedTags <> InheritedTags"; - using (var connection = CreateConnection()) + lock (WriteLock) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { foreach (var row in connection.Query(commandText)) { @@ -4476,7 +4480,7 @@ namespace Emby.Server.Implementations.Data } // write lock here - using (var statement = connection.PrepareStatement("Update TypedBaseItems set InheritedTags=@InheritedTags where Guid=@Guid")) + using (var statement = PrepareStatement(connection, "Update TypedBaseItems set InheritedTags=@InheritedTags where Guid=@Guid")) { foreach (var item in newValues) { @@ -4531,9 +4535,9 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - using (var connection = CreateConnection()) + lock (WriteLock) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -4561,7 +4565,7 @@ namespace Emby.Server.Implementations.Data private void ExecuteWithSingleParam(IDatabaseConnection db, string query, byte[] value) { - using (var statement = db.PrepareStatement(query)) + using (var statement = PrepareStatement(db, query)) { statement.TryBind("@Id", value); @@ -4591,11 +4595,11 @@ namespace Emby.Server.Implementations.Data var list = new List(); - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var statement = connection.PrepareStatement(commandText)) + using (var statement = PrepareStatementSafe(connection, commandText)) { // Run this again to bind the params GetPeopleWhereClauses(query, statement); @@ -4605,8 +4609,8 @@ namespace Emby.Server.Implementations.Data list.Add(row.GetString(0)); } } - return list; } + return list; } } @@ -4632,11 +4636,11 @@ namespace Emby.Server.Implementations.Data var list = new List(); - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var statement = connection.PrepareStatement(commandText)) + using (var statement = PrepareStatementSafe(connection, commandText)) { // Run this again to bind the params GetPeopleWhereClauses(query, statement); @@ -4847,15 +4851,18 @@ namespace Emby.Server.Implementations.Data commandText += " Group By CleanValue"; - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - foreach (var row in connection.Query(commandText)) + using (var statement = PrepareStatementSafe(connection, commandText)) { - if (!row.IsDBNull(0)) + foreach (var row in statement.ExecuteQuery()) { - list.Add(row.GetString(0)); + if (!row.IsDBNull(0)) + { + list.Add(row.GetString(0)); + } } } } @@ -5023,11 +5030,11 @@ namespace Emby.Server.Implementations.Data statementTexts.Add(countText); } - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - var statements = connection.PrepareAll(string.Join(";", statementTexts.ToArray())).ToList(); + var statements = PrepareAllSafe(connection, string.Join(";", statementTexts.ToArray())).ToList(); if (!isReturningZeroItems) { @@ -5208,7 +5215,7 @@ namespace Emby.Server.Implementations.Data // First delete db.Execute("delete from ItemValues where ItemId=@Id", itemId.ToGuidParamValue()); - using (var statement = db.PrepareStatement("insert into ItemValues (ItemId, Type, Value, CleanValue) values (@ItemId, @Type, @Value, @CleanValue)")) + using (var statement = PrepareStatement(db, "insert into ItemValues (ItemId, Type, Value, CleanValue) values (@ItemId, @Type, @Value, @CleanValue)")) { foreach (var pair in values) { @@ -5246,16 +5253,17 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - using (var connection = CreateConnection()) + lock (WriteLock) { - using (WriteLock.Write()) - { // First delete + using (var connection = CreateConnection()) + { + // First delete // "delete from People where ItemId=?" connection.Execute("delete from People where ItemId=?", itemId.ToGuidParamValue()); var listIndex = 0; - using (var statement = connection.PrepareStatement( + using (var statement = PrepareStatement(connection, "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values (@ItemId, @Name, @Role, @PersonType, @SortOrder, @ListOrder)")) { foreach (var person in people) @@ -5332,11 +5340,11 @@ namespace Emby.Server.Implementations.Data cmdText += " order by StreamIndex ASC"; - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var statement = connection.PrepareStatement(cmdText)) + using (var statement = PrepareStatementSafe(connection, cmdText)) { statement.TryBind("@ItemId", query.ItemId.ToGuidParamValue()); @@ -5377,13 +5385,14 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (var connection = CreateConnection()) + lock (WriteLock) { - using (WriteLock.Write()) - { // First delete chapters + using (var connection = CreateConnection()) + { + // First delete chapters connection.Execute("delete from mediastreams where ItemId=@ItemId", id.ToGuidParamValue()); - using (var statement = connection.PrepareStatement(string.Format("replace into mediastreams ({0}) values ({1})", + using (var statement = PrepareStatement(connection, string.Format("replace into mediastreams ({0}) values ({1})", string.Join(",", _mediaStreamSaveColumns), string.Join(",", _mediaStreamSaveColumns.Select(i => "@" + i).ToArray())))) { diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index b65e5d1b3..8acbd0bb8 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -188,9 +188,9 @@ namespace Emby.Server.Implementations.Data { cancellationToken.ThrowIfCancellationRequested(); - using (var connection = CreateConnection()) + lock (WriteLock) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -259,9 +259,9 @@ namespace Emby.Server.Implementations.Data { cancellationToken.ThrowIfCancellationRequested(); - using (var connection = CreateConnection()) + lock (WriteLock) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -296,9 +296,9 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException("key"); } - using (var connection = CreateConnection(true)) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where key =@Key and userId=@UserId")) { @@ -349,9 +349,9 @@ namespace Emby.Server.Implementations.Data var list = new List(); - using (var connection = CreateConnection()) + lock (WriteLock) { - using (WriteLock.Read()) + using (var connection = CreateConnection()) { using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@UserId")) { diff --git a/Emby.Server.Implementations/Data/SqliteUserRepository.cs b/Emby.Server.Implementations/Data/SqliteUserRepository.cs index 99d7563c7..b2b917e5e 100644 --- a/Emby.Server.Implementations/Data/SqliteUserRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserRepository.cs @@ -85,9 +85,9 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -110,9 +110,9 @@ namespace Emby.Server.Implementations.Data { var list = new List(); - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { foreach (var row in connection.Query("select guid,data from users")) { @@ -148,9 +148,9 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { diff --git a/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs b/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs index aae41da19..43e19da65 100644 --- a/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs +++ b/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs @@ -51,24 +51,24 @@ namespace Emby.Server.Implementations.Notifications { var result = new NotificationResult(); - using (var connection = CreateConnection(true)) - { - using (WriteLock.Read()) - { - var clauses = new List(); - var paramList = new List(); + var clauses = new List(); + var paramList = new List(); - if (query.IsRead.HasValue) - { - clauses.Add("IsRead=?"); - paramList.Add(query.IsRead.Value); - } + if (query.IsRead.HasValue) + { + clauses.Add("IsRead=?"); + paramList.Add(query.IsRead.Value); + } - clauses.Add("UserId=?"); - paramList.Add(query.UserId.ToGuidParamValue()); + clauses.Add("UserId=?"); + paramList.Add(query.UserId.ToGuidParamValue()); - var whereClause = " where " + string.Join(" And ", clauses.ToArray()); + var whereClause = " where " + string.Join(" And ", clauses.ToArray()); + using (var connection = CreateConnection(true)) + { + lock (WriteLock) + { result.TotalRecordCount = connection.Query("select count(Id) from Notifications" + whereClause, paramList.ToArray()).SelectScalarInt().First(); var commandText = string.Format("select Id,UserId,Date,Name,Description,Url,Level,IsRead,Category,RelatedId from Notifications{0} order by IsRead asc, Date desc", whereClause); @@ -108,7 +108,7 @@ namespace Emby.Server.Implementations.Notifications using (var connection = CreateConnection(true)) { - using (WriteLock.Read()) + lock (WriteLock) { using (var statement = connection.PrepareStatement("select Level from Notifications where UserId=@UserId and IsRead=@IsRead")) { @@ -225,7 +225,7 @@ namespace Emby.Server.Implementations.Notifications using (var connection = CreateConnection()) { - using (WriteLock.Write()) + lock (WriteLock) { connection.RunInTransaction(conn => { @@ -288,7 +288,7 @@ namespace Emby.Server.Implementations.Notifications using (var connection = CreateConnection()) { - using (WriteLock.Write()) + lock (WriteLock) { connection.RunInTransaction(conn => { @@ -310,7 +310,7 @@ namespace Emby.Server.Implementations.Notifications using (var connection = CreateConnection()) { - using (WriteLock.Write()) + lock (WriteLock) { connection.RunInTransaction(conn => { diff --git a/Emby.Server.Implementations/Security/AuthenticationRepository.cs b/Emby.Server.Implementations/Security/AuthenticationRepository.cs index dbda4a460..dbca4931b 100644 --- a/Emby.Server.Implementations/Security/AuthenticationRepository.cs +++ b/Emby.Server.Implementations/Security/AuthenticationRepository.cs @@ -66,9 +66,9 @@ namespace Emby.Server.Implementations.Security cancellationToken.ThrowIfCancellationRequested(); - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -202,9 +202,9 @@ namespace Emby.Server.Implementations.Security var list = new List(); - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { using (var statement = connection.PrepareStatement(commandText)) { @@ -241,9 +241,9 @@ namespace Emby.Server.Implementations.Security throw new ArgumentNullException("id"); } - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { var commandText = BaseSelectText + " where Id=@Id"; diff --git a/Emby.Server.Implementations/Social/SharingRepository.cs b/Emby.Server.Implementations/Social/SharingRepository.cs index 12d846e81..e8230947e 100644 --- a/Emby.Server.Implementations/Social/SharingRepository.cs +++ b/Emby.Server.Implementations/Social/SharingRepository.cs @@ -52,9 +52,9 @@ namespace Emby.Server.Implementations.Social throw new ArgumentNullException("info.Id"); } - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(db => { @@ -77,9 +77,9 @@ namespace Emby.Server.Implementations.Social throw new ArgumentNullException("id"); } - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { var commandText = "select Id, ItemId, UserId, ExpirationDate from Shares where id = ?"; diff --git a/Emby.Server.Implementations/Sync/SyncRepository.cs b/Emby.Server.Implementations/Sync/SyncRepository.cs index 037507872..885f8e64a 100644 --- a/Emby.Server.Implementations/Sync/SyncRepository.cs +++ b/Emby.Server.Implementations/Sync/SyncRepository.cs @@ -105,9 +105,9 @@ namespace Emby.Server.Implementations.Sync throw new ArgumentNullException("id"); } - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { var commandText = BaseJobSelectText + " where Id=?"; var paramList = new List(); @@ -216,9 +216,9 @@ namespace Emby.Server.Implementations.Sync CheckDisposed(); - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { string commandText; var paramList = new List(); @@ -277,9 +277,9 @@ namespace Emby.Server.Implementations.Sync CheckDisposed(); - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { connection.RunInTransaction(conn => { @@ -299,9 +299,9 @@ namespace Emby.Server.Implementations.Sync CheckDisposed(); - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { var commandText = BaseJobSelectText; var paramList = new List(); @@ -399,9 +399,9 @@ namespace Emby.Server.Implementations.Sync var guid = new Guid(id); - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { var commandText = BaseJobItemSelectText + " where Id=?"; var paramList = new List(); @@ -425,9 +425,9 @@ namespace Emby.Server.Implementations.Sync throw new ArgumentNullException("query"); } - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { var commandText = baseSelectText; var paramList = new List(); @@ -505,41 +505,41 @@ namespace Emby.Server.Implementations.Sync var now = DateTime.UtcNow; - using (var connection = CreateConnection(true)) + using (WriteLock.Read()) { - var commandText = "select ItemId,Status,Progress from SyncJobItems"; - var whereClauses = new List(); - - if (!string.IsNullOrWhiteSpace(query.TargetId)) + using (var connection = CreateConnection(true)) { - whereClauses.Add("TargetId=@TargetId"); - } + var commandText = "select ItemId,Status,Progress from SyncJobItems"; + var whereClauses = new List(); - if (query.Statuses.Length > 0) - { - var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray()); + if (!string.IsNullOrWhiteSpace(query.TargetId)) + { + whereClauses.Add("TargetId=@TargetId"); + } - whereClauses.Add(string.Format("Status in ({0})", statuses)); - } + if (query.Statuses.Length > 0) + { + var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray()); - if (whereClauses.Count > 0) - { - commandText += " where " + string.Join(" AND ", whereClauses.ToArray()); - } + whereClauses.Add(string.Format("Status in ({0})", statuses)); + } + + if (whereClauses.Count > 0) + { + commandText += " where " + string.Join(" AND ", whereClauses.ToArray()); + } - var statementTexts = new List + var statementTexts = new List { commandText }; - commandText = commandText - .Replace("select ItemId,Status,Progress from SyncJobItems", "select ItemIds,Status,Progress from SyncJobs") - .Replace("'Synced'", "'Completed','CompletedWithError'"); + commandText = commandText + .Replace("select ItemId,Status,Progress from SyncJobItems", "select ItemIds,Status,Progress from SyncJobs") + .Replace("'Synced'", "'Completed','CompletedWithError'"); - statementTexts.Add(commandText); + statementTexts.Add(commandText); - using (WriteLock.Read()) - { var statements = connection.PrepareAll(string.Join(";", statementTexts.ToArray())) .ToList(); @@ -692,9 +692,9 @@ namespace Emby.Server.Implementations.Sync CheckDisposed(); - using (var connection = CreateConnection()) + using (WriteLock.Write()) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { string commandText; diff --git a/MediaBrowser.Controller/Entities/TV/Season.cs b/MediaBrowser.Controller/Entities/TV/Season.cs index e0cc496a1..f2a6586e2 100644 --- a/MediaBrowser.Controller/Entities/TV/Season.cs +++ b/MediaBrowser.Controller/Entities/TV/Season.cs @@ -48,11 +48,7 @@ namespace MediaBrowser.Controller.Entities.TV [IgnoreDataMember] public override Guid? DisplayParentId { - get - { - var series = Series; - return series == null ? ParentId : series.Id; - } + get { return SeriesId; } } [IgnoreDataMember] -- cgit v1.2.3