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