From 8629d509e4cb8d803fe1195c2861b98868854e51 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Wed, 6 Jul 2016 01:13:26 -0400 Subject: speed up db upgrade --- .../Persistence/CleanDatabaseScheduledTask.cs | 91 ++++++++++++++-------- 1 file changed, 58 insertions(+), 33 deletions(-) (limited to 'MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs') diff --git a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs index b11a3e496..4235f6073 100644 --- a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs +++ b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs @@ -142,52 +142,77 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - private async Task UpdateToLatestSchema(CancellationToken cancellationToken, IProgress progress) + private Task UpdateToLatestSchema(CancellationToken cancellationToken, IProgress progress) { - var itemIds = _libraryManager.GetItemIds(new InternalItemsQuery + return UpdateToLatestSchema(0, 0, null, cancellationToken, progress); + } + + private async Task UpdateToLatestSchema(int queryStartIndex, int progressStartIndex, int? totalRecordCount, CancellationToken cancellationToken, IProgress progress) + { + IEnumerable items; + int numItemsToSave; + var pageSize = 2000; + + if (totalRecordCount.HasValue) { - IsCurrentSchema = false, - ExcludeItemTypes = new[] { typeof(LiveTvProgram).Name } - }); + var list = _libraryManager.GetItemList(new InternalItemsQuery + { + IsCurrentSchema = false, + ExcludeItemTypes = new[] { typeof(LiveTvProgram).Name }, + StartIndex = queryStartIndex, + Limit = pageSize - var numComplete = 0; - var numItems = itemIds.Count; + }).ToList(); + + items = list; + numItemsToSave = list.Count; + } + else + { + var itemsResult = _libraryManager.GetItemsResult(new InternalItemsQuery + { + IsCurrentSchema = false, + ExcludeItemTypes = new[] { typeof(LiveTvProgram).Name }, + StartIndex = queryStartIndex, + Limit = pageSize + }); + + totalRecordCount = itemsResult.TotalRecordCount; + items = itemsResult.Items; + numItemsToSave = itemsResult.Items.Length; + } + + var numItems = totalRecordCount.Value; _logger.Debug("Upgrading schema for {0} items", numItems); - foreach (var itemId in itemIds) + if (numItemsToSave > 0) { - cancellationToken.ThrowIfCancellationRequested(); - - if (itemId != Guid.Empty) + try { - // Somehow some invalid data got into the db. It probably predates the boundary checking - var item = _libraryManager.GetItemById(itemId); - - if (item != null) - { - try - { - await _itemRepo.SaveItem(item, cancellationToken).ConfigureAwait(false); - } - catch (OperationCanceledException) - { - throw; - } - catch (Exception ex) - { - _logger.ErrorException("Error saving item", ex); - } - } + await _itemRepo.SaveItems(items, cancellationToken).ConfigureAwait(false); + } + catch (OperationCanceledException) + { + throw; + } + catch (Exception ex) + { + _logger.ErrorException("Error saving item", ex); } - numComplete++; - double percent = numComplete; + progressStartIndex += pageSize; + double percent = progressStartIndex; percent /= numItems; progress.Report(percent * 100); - } - progress.Report(100); + var newStartIndex = queryStartIndex + (pageSize - numItemsToSave); + await UpdateToLatestSchema(newStartIndex, progressStartIndex, totalRecordCount, cancellationToken, progress).ConfigureAwait(false); + } + else + { + progress.Report(100); + } } private async Task CleanDeadItems(CancellationToken cancellationToken, IProgress progress) -- cgit v1.2.3 From 3c6797678bd11f00182da6e2cb3dbb3cbfee628f Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Wed, 6 Jul 2016 13:44:44 -0400 Subject: store chapter image date modified --- MediaBrowser.Controller/Entities/BaseItem.cs | 2 +- MediaBrowser.Model/Entities/ChapterInfo.cs | 4 +++- MediaBrowser.Server.Implementations/Dto/DtoService.cs | 2 +- .../MediaEncoder/EncodingManager.cs | 2 ++ .../Persistence/CleanDatabaseScheduledTask.cs | 2 +- .../Persistence/SqliteItemRepository.cs | 13 +++++++++++-- 6 files changed, 19 insertions(+), 6 deletions(-) (limited to 'MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs') diff --git a/MediaBrowser.Controller/Entities/BaseItem.cs b/MediaBrowser.Controller/Entities/BaseItem.cs index 9d1a45689..0860cb61c 100644 --- a/MediaBrowser.Controller/Entities/BaseItem.cs +++ b/MediaBrowser.Controller/Entities/BaseItem.cs @@ -1878,7 +1878,7 @@ namespace MediaBrowser.Controller.Entities return new ItemImageInfo { Path = path, - DateModified = FileSystem.GetLastWriteTimeUtc(path), + DateModified = chapter.ImageDateModified, Type = imageType }; } diff --git a/MediaBrowser.Model/Entities/ChapterInfo.cs b/MediaBrowser.Model/Entities/ChapterInfo.cs index 9da7a9caa..7e5700965 100644 --- a/MediaBrowser.Model/Entities/ChapterInfo.cs +++ b/MediaBrowser.Model/Entities/ChapterInfo.cs @@ -1,4 +1,5 @@ - +using System; + namespace MediaBrowser.Model.Entities { /// @@ -23,5 +24,6 @@ namespace MediaBrowser.Model.Entities /// /// The image path. public string ImagePath { get; set; } + public DateTime ImageDateModified { get; set; } } } diff --git a/MediaBrowser.Server.Implementations/Dto/DtoService.cs b/MediaBrowser.Server.Implementations/Dto/DtoService.cs index 8bd1e5e62..3e009d210 100644 --- a/MediaBrowser.Server.Implementations/Dto/DtoService.cs +++ b/MediaBrowser.Server.Implementations/Dto/DtoService.cs @@ -863,7 +863,7 @@ namespace MediaBrowser.Server.Implementations.Dto { Path = chapterInfo.ImagePath, Type = ImageType.Chapter, - DateModified = _fileSystem.GetLastWriteTimeUtc(chapterInfo.ImagePath) + DateModified = chapterInfo.ImageDateModified }); } diff --git a/MediaBrowser.Server.Implementations/MediaEncoder/EncodingManager.cs b/MediaBrowser.Server.Implementations/MediaEncoder/EncodingManager.cs index b1b2072c4..7f709d084 100644 --- a/MediaBrowser.Server.Implementations/MediaEncoder/EncodingManager.cs +++ b/MediaBrowser.Server.Implementations/MediaEncoder/EncodingManager.cs @@ -152,6 +152,7 @@ namespace MediaBrowser.Server.Implementations.MediaEncoder } chapter.ImagePath = path; + chapter.ImageDateModified = _fileSystem.GetLastWriteTimeUtc(path); changesMade = true; } catch (Exception ex) @@ -170,6 +171,7 @@ namespace MediaBrowser.Server.Implementations.MediaEncoder else if (!string.Equals(path, chapter.ImagePath, StringComparison.OrdinalIgnoreCase)) { chapter.ImagePath = path; + chapter.ImageDateModified = _fileSystem.GetLastWriteTimeUtc(path); changesMade = true; } } diff --git a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs index 4235f6073..c321e5f01 100644 --- a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs +++ b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs @@ -151,7 +151,7 @@ namespace MediaBrowser.Server.Implementations.Persistence { IEnumerable items; int numItemsToSave; - var pageSize = 2000; + var pageSize = 1000; if (totalRecordCount.HasValue) { diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 31388661b..93f58b399 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -278,6 +278,8 @@ namespace MediaBrowser.Server.Implementations.Persistence _connection.AddColumn(Logger, "UserDataKeys", "Priority", "INT"); _connection.AddColumn(Logger, "ItemValues", "CleanValue", "Text"); + _connection.AddColumn(Logger, ChaptersTableName, "ImageDateModified", "DATETIME"); + string[] postQueries = { @@ -591,6 +593,7 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@StartPositionTicks"); _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@Name"); _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ImagePath"); + _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ImageDateModified"); // MediaStreams _deleteStreamsCommand = _connection.CreateCommand(); @@ -1497,7 +1500,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"; + cmd.CommandText = "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"; cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = id; @@ -1530,7 +1533,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex"; + cmd.CommandText = "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex"; cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = id; cmd.Parameters.Add(cmd, "@ChapterIndex", DbType.Int32).Value = index; @@ -1568,6 +1571,11 @@ namespace MediaBrowser.Server.Implementations.Persistence chapter.ImagePath = reader.GetString(2); } + if (!reader.IsDBNull(3)) + { + chapter.ImageDateModified = reader.GetDateTime(3).ToUniversalTime(); + } + return chapter; } @@ -1627,6 +1635,7 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveChapterCommand.GetParameter(2).Value = chapter.StartPositionTicks; _saveChapterCommand.GetParameter(3).Value = chapter.Name; _saveChapterCommand.GetParameter(4).Value = chapter.ImagePath; + _saveChapterCommand.GetParameter(5).Value = chapter.ImageDateModified; _saveChapterCommand.Transaction = transaction; -- cgit v1.2.3 From f618650fbd4d97846322b677dcbaecc8dc41c405 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Thu, 7 Jul 2016 11:55:39 -0400 Subject: revert clean task changes --- .../Persistence/CleanDatabaseScheduledTask.cs | 91 ++++++++-------------- 1 file changed, 33 insertions(+), 58 deletions(-) (limited to 'MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs') diff --git a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs index c321e5f01..b11a3e496 100644 --- a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs +++ b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs @@ -142,77 +142,52 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - private Task UpdateToLatestSchema(CancellationToken cancellationToken, IProgress progress) + private async Task UpdateToLatestSchema(CancellationToken cancellationToken, IProgress progress) { - return UpdateToLatestSchema(0, 0, null, cancellationToken, progress); - } - - private async Task UpdateToLatestSchema(int queryStartIndex, int progressStartIndex, int? totalRecordCount, CancellationToken cancellationToken, IProgress progress) - { - IEnumerable items; - int numItemsToSave; - var pageSize = 1000; - - if (totalRecordCount.HasValue) - { - var list = _libraryManager.GetItemList(new InternalItemsQuery - { - IsCurrentSchema = false, - ExcludeItemTypes = new[] { typeof(LiveTvProgram).Name }, - StartIndex = queryStartIndex, - Limit = pageSize - - }).ToList(); - - items = list; - numItemsToSave = list.Count; - } - else + var itemIds = _libraryManager.GetItemIds(new InternalItemsQuery { - var itemsResult = _libraryManager.GetItemsResult(new InternalItemsQuery - { - IsCurrentSchema = false, - ExcludeItemTypes = new[] { typeof(LiveTvProgram).Name }, - StartIndex = queryStartIndex, - Limit = pageSize - }); - - totalRecordCount = itemsResult.TotalRecordCount; - items = itemsResult.Items; - numItemsToSave = itemsResult.Items.Length; - } + IsCurrentSchema = false, + ExcludeItemTypes = new[] { typeof(LiveTvProgram).Name } + }); - var numItems = totalRecordCount.Value; + var numComplete = 0; + var numItems = itemIds.Count; _logger.Debug("Upgrading schema for {0} items", numItems); - if (numItemsToSave > 0) + foreach (var itemId in itemIds) { - try - { - await _itemRepo.SaveItems(items, cancellationToken).ConfigureAwait(false); - } - catch (OperationCanceledException) - { - throw; - } - catch (Exception ex) + cancellationToken.ThrowIfCancellationRequested(); + + if (itemId != Guid.Empty) { - _logger.ErrorException("Error saving item", ex); + // Somehow some invalid data got into the db. It probably predates the boundary checking + var item = _libraryManager.GetItemById(itemId); + + if (item != null) + { + try + { + await _itemRepo.SaveItem(item, cancellationToken).ConfigureAwait(false); + } + catch (OperationCanceledException) + { + throw; + } + catch (Exception ex) + { + _logger.ErrorException("Error saving item", ex); + } + } } - progressStartIndex += pageSize; - double percent = progressStartIndex; + numComplete++; + double percent = numComplete; percent /= numItems; progress.Report(percent * 100); - - var newStartIndex = queryStartIndex + (pageSize - numItemsToSave); - await UpdateToLatestSchema(newStartIndex, progressStartIndex, totalRecordCount, cancellationToken, progress).ConfigureAwait(false); - } - else - { - progress.Report(100); } + + progress.Report(100); } private async Task CleanDeadItems(CancellationToken cancellationToken, IProgress progress) -- cgit v1.2.3 From f952ac0f1f84f43f68cd28173b7c3ff9369a2040 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Thu, 7 Jul 2016 23:22:02 -0400 Subject: fix season ids --- MediaBrowser.Controller/Entities/Book.cs | 1 + MediaBrowser.Controller/Entities/TV/Episode.cs | 1 + MediaBrowser.Controller/Entities/TV/Season.cs | 1 + .../Dto/DtoService.cs | 26 +++++------- .../Persistence/CleanDatabaseScheduledTask.cs | 49 ++++++++++++++++------ 5 files changed, 51 insertions(+), 27 deletions(-) (limited to 'MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs') diff --git a/MediaBrowser.Controller/Entities/Book.cs b/MediaBrowser.Controller/Entities/Book.cs index 34368d61f..9d35c86d2 100644 --- a/MediaBrowser.Controller/Entities/Book.cs +++ b/MediaBrowser.Controller/Entities/Book.cs @@ -20,6 +20,7 @@ namespace MediaBrowser.Controller.Entities [IgnoreDataMember] public string SeriesName { get; set; } + [IgnoreDataMember] public Guid? SeriesId { get; set; } public string FindSeriesName() diff --git a/MediaBrowser.Controller/Entities/TV/Episode.cs b/MediaBrowser.Controller/Entities/TV/Episode.cs index b13c291d1..78c35ad48 100644 --- a/MediaBrowser.Controller/Entities/TV/Episode.cs +++ b/MediaBrowser.Controller/Entities/TV/Episode.cs @@ -249,6 +249,7 @@ namespace MediaBrowser.Controller.Entities.TV [IgnoreDataMember] public Guid? SeasonId { get; set; } + [IgnoreDataMember] public Guid? SeriesId { get; set; } public Guid? FindSeriesId() diff --git a/MediaBrowser.Controller/Entities/TV/Season.cs b/MediaBrowser.Controller/Entities/TV/Season.cs index 218d0fef8..865cadeb9 100644 --- a/MediaBrowser.Controller/Entities/TV/Season.cs +++ b/MediaBrowser.Controller/Entities/TV/Season.cs @@ -237,6 +237,7 @@ namespace MediaBrowser.Controller.Entities.TV [IgnoreDataMember] public string SeriesName { get; set; } + [IgnoreDataMember] public Guid? SeriesId { get; set; } public string FindSeriesName() diff --git a/MediaBrowser.Server.Implementations/Dto/DtoService.cs b/MediaBrowser.Server.Implementations/Dto/DtoService.cs index 3e009d210..de6c23cac 100644 --- a/MediaBrowser.Server.Implementations/Dto/DtoService.cs +++ b/MediaBrowser.Server.Implementations/Dto/DtoService.cs @@ -671,17 +671,6 @@ namespace MediaBrowser.Server.Implementations.Dto .ToList(); } - private IEnumerable GetCacheTags(BaseItem item, ImageType type, int limit) - { - return item.GetImages(type) - // Convert to a list now in case GetImageCacheTag is slow - .ToList() - .Select(p => GetImageCacheTag(item, p)) - .Where(i => i != null) - .Take(limit) - .ToList(); - } - private string GetImageCacheTag(BaseItem item, ImageType type) { try @@ -1458,9 +1447,16 @@ namespace MediaBrowser.Server.Implementations.Dto while (((!dto.HasLogo && logoLimit > 0) || (!dto.HasArtImage && artLimit > 0) || (!dto.HasThumb && thumbLimit > 0) || parent is Series) && (parent = parent ?? (isFirst ? item.GetParent() ?? owner : parent)) != null) { + if (parent == null) + { + break; + } + + var allImages = parent.ImageInfos; + if (logoLimit > 0 && !dto.HasLogo && dto.ParentLogoItemId == null) { - var image = parent.GetImageInfo(ImageType.Logo, 0); + var image = allImages.FirstOrDefault(i => i.Type == ImageType.Logo); if (image != null) { @@ -1470,7 +1466,7 @@ namespace MediaBrowser.Server.Implementations.Dto } if (artLimit > 0 && !dto.HasArtImage && dto.ParentArtItemId == null) { - var image = parent.GetImageInfo(ImageType.Art, 0); + var image = allImages.FirstOrDefault(i => i.Type == ImageType.Art); if (image != null) { @@ -1480,7 +1476,7 @@ namespace MediaBrowser.Server.Implementations.Dto } if (thumbLimit > 0 && !dto.HasThumb && (dto.ParentThumbItemId == null || parent is Series)) { - var image = parent.GetImageInfo(ImageType.Thumb, 0); + var image = allImages.FirstOrDefault(i => i.Type == ImageType.Thumb); if (image != null) { @@ -1490,7 +1486,7 @@ namespace MediaBrowser.Server.Implementations.Dto } if (backdropLimit > 0 && !dto.HasBackdrop) { - var images = parent.GetImages(ImageType.Backdrop).Take(backdropLimit).ToList(); + var images = allImages.Where(i => i.Type == ImageType.Backdrop).Take(backdropLimit).ToList(); if (images.Count > 0) { diff --git a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs index b11a3e496..bf2afb5ac 100644 --- a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs +++ b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs @@ -155,6 +155,8 @@ namespace MediaBrowser.Server.Implementations.Persistence _logger.Debug("Upgrading schema for {0} items", numItems); + var list = new List(); + foreach (var itemId in itemIds) { cancellationToken.ThrowIfCancellationRequested(); @@ -166,19 +168,26 @@ namespace MediaBrowser.Server.Implementations.Persistence if (item != null) { - try - { - await _itemRepo.SaveItem(item, cancellationToken).ConfigureAwait(false); - } - catch (OperationCanceledException) - { - throw; - } - catch (Exception ex) - { - _logger.ErrorException("Error saving item", ex); - } + list.Add(item); + } + } + + if (list.Count >= 1000) + { + try + { + await _itemRepo.SaveItems(list, cancellationToken).ConfigureAwait(false); } + catch (OperationCanceledException) + { + throw; + } + catch (Exception ex) + { + _logger.ErrorException("Error saving item", ex); + } + + list.Clear(); } numComplete++; @@ -187,6 +196,22 @@ namespace MediaBrowser.Server.Implementations.Persistence progress.Report(percent * 100); } + if (list.Count > 0) + { + try + { + await _itemRepo.SaveItems(list, cancellationToken).ConfigureAwait(false); + } + catch (OperationCanceledException) + { + throw; + } + catch (Exception ex) + { + _logger.ErrorException("Error saving item", ex); + } + } + progress.Report(100); } -- cgit v1.2.3