From a4d1c9e6e48f63121cc51abda61ed46d7f6a72cf Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Sun, 1 May 2016 17:48:37 -0400 Subject: update sqlite --- .../Persistence/IDisplayPreferencesRepository.cs | 6 ------ MediaBrowser.Controller/Persistence/IItemRepository.cs | 6 ------ MediaBrowser.Controller/Persistence/IUserDataRepository.cs | 6 ------ 3 files changed, 18 deletions(-) (limited to 'MediaBrowser.Controller/Persistence') diff --git a/MediaBrowser.Controller/Persistence/IDisplayPreferencesRepository.cs b/MediaBrowser.Controller/Persistence/IDisplayPreferencesRepository.cs index 17de730cb..abf96994f 100644 --- a/MediaBrowser.Controller/Persistence/IDisplayPreferencesRepository.cs +++ b/MediaBrowser.Controller/Persistence/IDisplayPreferencesRepository.cs @@ -11,12 +11,6 @@ namespace MediaBrowser.Controller.Persistence /// public interface IDisplayPreferencesRepository : IRepository { - /// - /// Opens the connection to the repository - /// - /// Task. - Task Initialize(); - /// /// Saves display preferences for an item /// diff --git a/MediaBrowser.Controller/Persistence/IItemRepository.cs b/MediaBrowser.Controller/Persistence/IItemRepository.cs index 15df1f649..7bcc36958 100644 --- a/MediaBrowser.Controller/Persistence/IItemRepository.cs +++ b/MediaBrowser.Controller/Persistence/IItemRepository.cs @@ -13,12 +13,6 @@ namespace MediaBrowser.Controller.Persistence /// public interface IItemRepository : IRepository { - /// - /// Opens the connection to the repository - /// - /// Task. - Task Initialize(); - /// /// Saves an item /// diff --git a/MediaBrowser.Controller/Persistence/IUserDataRepository.cs b/MediaBrowser.Controller/Persistence/IUserDataRepository.cs index 2a904be0d..2e165f416 100644 --- a/MediaBrowser.Controller/Persistence/IUserDataRepository.cs +++ b/MediaBrowser.Controller/Persistence/IUserDataRepository.cs @@ -11,12 +11,6 @@ namespace MediaBrowser.Controller.Persistence /// public interface IUserDataRepository : IRepository { - /// - /// Opens the connection to the repository - /// - /// Task. - Task Initialize(); - /// /// Saves the user data. /// -- cgit v1.2.3 From ddb6ea6f0576a6ba9c6050cc0799ac6b0e3c2fa8 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Fri, 3 Jun 2016 20:15:14 -0400 Subject: rework user data --- MediaBrowser.Controller/Entities/Folder.cs | 69 +-------------- MediaBrowser.Controller/Entities/TV/Episode.cs | 8 +- MediaBrowser.Controller/Entities/TV/Season.cs | 49 ++--------- MediaBrowser.Controller/Entities/TV/Series.cs | 45 ++++++---- .../Persistence/IUserDataRepository.cs | 2 + .../Library/UserDataManager.cs | 98 ++++++---------------- .../Persistence/SqliteUserDataRepository.cs | 48 +++++++++++ .../TV/TVSeriesManager.cs | 40 ++++----- 8 files changed, 138 insertions(+), 221 deletions(-) (limited to 'MediaBrowser.Controller/Persistence') diff --git a/MediaBrowser.Controller/Entities/Folder.cs b/MediaBrowser.Controller/Entities/Folder.cs index 2cec15d51..2e4cf3745 100644 --- a/MediaBrowser.Controller/Entities/Folder.cs +++ b/MediaBrowser.Controller/Entities/Folder.cs @@ -164,49 +164,15 @@ namespace MediaBrowser.Controller.Entities item.DateModified = DateTime.UtcNow; } - AddChildInternal(item.Id); - await LibraryManager.CreateItem(item, cancellationToken).ConfigureAwait(false); } - protected void AddChildrenInternal(List children) - { - lock (_childrenSyncLock) - { - var newChildren = ChildIds.ToList(); - newChildren.AddRange(children); - _children = newChildren.ToList(); - } - } - protected void AddChildInternal(Guid child) - { - lock (_childrenSyncLock) - { - var childIds = ChildIds.ToList(); - if (!childIds.Contains(child)) - { - childIds.Add(child); - _children = childIds.ToList(); - } - } - } - - protected void RemoveChildrenInternal(List children) - { - lock (_childrenSyncLock) - { - _children = ChildIds.Except(children).ToList(); - } - } - /// /// Removes the child. /// /// The item. public void RemoveChild(BaseItem item) { - RemoveChildrenInternal(new[] { item.Id }.ToList()); - item.SetParent(null); } @@ -241,33 +207,6 @@ namespace MediaBrowser.Controller.Entities #endregion - /// - /// The children - /// - private IReadOnlyList _children; - /// - /// The _children sync lock - /// - private readonly object _childrenSyncLock = new object(); - /// - /// Gets or sets the actual children. - /// - /// The actual children. - protected virtual IEnumerable ChildIds - { - get - { - lock (_childrenSyncLock) - { - if (_children == null) - { - _children = LoadChildren().ToList(); - } - return _children.ToList(); - } - } - } - /// /// Gets the actual children. /// @@ -277,7 +216,7 @@ namespace MediaBrowser.Controller.Entities { get { - return ChildIds.Select(LibraryManager.GetItemById).Where(i => i != null); + return LoadChildren().Select(LibraryManager.GetItemById).Where(i => i != null); } } @@ -479,8 +418,6 @@ namespace MediaBrowser.Controller.Entities if (actualRemovals.Count > 0) { - RemoveChildrenInternal(actualRemovals.Select(i => i.Id).ToList()); - foreach (var item in actualRemovals) { Logger.Debug("Removed item: " + item.Path); @@ -493,8 +430,6 @@ namespace MediaBrowser.Controller.Entities } await LibraryManager.CreateItems(newItems, cancellationToken).ConfigureAwait(false); - - AddChildrenInternal(newItems.Select(i => i.Id).ToList()); } } @@ -766,7 +701,7 @@ namespace MediaBrowser.Controller.Entities { if (!(this is ICollectionFolder)) { - Logger.Debug("Query requires post-filtering due to LinkedChildren"); + Logger.Debug("Query requires post-filtering due to LinkedChildren. Type: " + GetType().Name); return true; } } diff --git a/MediaBrowser.Controller/Entities/TV/Episode.cs b/MediaBrowser.Controller/Entities/TV/Episode.cs index 7ca09d9b2..2dc459239 100644 --- a/MediaBrowser.Controller/Entities/TV/Episode.cs +++ b/MediaBrowser.Controller/Entities/TV/Episode.cs @@ -108,7 +108,13 @@ namespace MediaBrowser.Controller.Entities.TV var series = Series; if (series != null && ParentIndexNumber.HasValue && IndexNumber.HasValue) { - list.InsertRange(0, series.GetUserDataKeys().Select(i => i + ParentIndexNumber.Value.ToString("000") + IndexNumber.Value.ToString("000"))); + var seriesUserDataKeys = series.GetUserDataKeys(); + var take = seriesUserDataKeys.Count; + if (seriesUserDataKeys.Count > 1) + { + take--; + } + list.InsertRange(0, seriesUserDataKeys.Take(take).Select(i => i + ParentIndexNumber.Value.ToString("000") + IndexNumber.Value.ToString("000"))); } return list; diff --git a/MediaBrowser.Controller/Entities/TV/Season.cs b/MediaBrowser.Controller/Entities/TV/Season.cs index 7fa1b55de..f07d4be13 100644 --- a/MediaBrowser.Controller/Entities/TV/Season.cs +++ b/MediaBrowser.Controller/Entities/TV/Season.cs @@ -196,52 +196,17 @@ namespace MediaBrowser.Controller.Entities.TV { var config = user.Configuration; - return GetEpisodes(user, config.DisplayMissingEpisodes, config.DisplayUnairedEpisodes); + return GetEpisodes(Series, user, config.DisplayMissingEpisodes, config.DisplayUnairedEpisodes); } - public IEnumerable GetEpisodes(User user, bool includeMissingEpisodes, bool includeVirtualUnairedEpisodes) + public IEnumerable GetEpisodes(Series series, User user, bool includeMissingEpisodes, bool includeVirtualUnairedEpisodes) { - var series = Series; - - if (IndexNumber.HasValue && series != null) - { - return series.GetEpisodes(user, this, includeMissingEpisodes, includeVirtualUnairedEpisodes); - } - - var episodes = GetRecursiveChildren(user) - .OfType(); - - if (series != null && series.ContainsEpisodesWithoutSeasonFolders) - { - var seasonNumber = IndexNumber; - var list = episodes.ToList(); - - if (seasonNumber.HasValue) - { - list.AddRange(series.GetRecursiveChildren(user).OfType() - .Where(i => i.ParentIndexNumber.HasValue && i.ParentIndexNumber.Value == seasonNumber.Value)); - } - else - { - list.AddRange(series.GetRecursiveChildren(user).OfType() - .Where(i => !i.ParentIndexNumber.HasValue)); - } - - episodes = list.DistinctBy(i => i.Id); - } - - if (!includeMissingEpisodes) - { - episodes = episodes.Where(i => !i.IsMissingEpisode); - } - if (!includeVirtualUnairedEpisodes) - { - episodes = episodes.Where(i => !i.IsVirtualUnaired); - } + return GetEpisodes(series, user, includeMissingEpisodes, includeVirtualUnairedEpisodes, null); + } - return LibraryManager - .Sort(episodes, user, new[] { ItemSortBy.SortName }, SortOrder.Ascending) - .Cast(); + public IEnumerable GetEpisodes(Series series, User user, bool includeMissingEpisodes, bool includeVirtualUnairedEpisodes, IEnumerable allSeriesEpisodes) + { + return series.GetEpisodes(user, this, includeMissingEpisodes, includeVirtualUnairedEpisodes, allSeriesEpisodes); } public IEnumerable GetEpisodes() diff --git a/MediaBrowser.Controller/Entities/TV/Series.cs b/MediaBrowser.Controller/Entities/TV/Series.cs index 600ea173a..bbb9b3019 100644 --- a/MediaBrowser.Controller/Entities/TV/Series.cs +++ b/MediaBrowser.Controller/Entities/TV/Series.cs @@ -183,24 +183,20 @@ namespace MediaBrowser.Controller.Entities.TV protected override Task> GetItemsInternal(InternalItemsQuery query) { + if (query.User == null) + { + return base.GetItemsInternal(query); + } + var user = query.User; Func filter = i => UserViewBuilder.Filter(i, user, query, UserDataManager, LibraryManager); IEnumerable items; - if (query.User == null) - { - items = query.Recursive - ? GetRecursiveChildren(filter) - : Children.Where(filter); - } - else - { - items = query.Recursive - ? GetSeasons(user).Cast().Concat(GetEpisodes(user)).Where(filter) - : GetSeasons(user).Where(filter); - } + items = query.Recursive + ? GetSeasons(user).Cast().Concat(GetEpisodes(user)).Where(filter) + : GetSeasons(user).Where(filter); var result = PostFilterAndSort(items, query); @@ -260,8 +256,10 @@ namespace MediaBrowser.Controller.Entities.TV public IEnumerable GetEpisodes(User user, bool includeMissing, bool includeVirtualUnaired) { + var allSeriesEpisodes = GetAllEpisodes(user).ToList(); + var allEpisodes = GetSeasons(user, true, true) - .SelectMany(i => i.GetEpisodes(user, includeMissing, includeVirtualUnaired)) + .SelectMany(i => i.GetEpisodes(this, user, includeMissing, includeVirtualUnaired, allSeriesEpisodes)) .Reverse() .ToList(); @@ -350,7 +348,7 @@ namespace MediaBrowser.Controller.Entities.TV return false; } - public IEnumerable GetEpisodes(User user, Season parentSeason, bool includeMissingEpisodes, bool includeVirtualUnairedEpisodes) + private IEnumerable GetAllEpisodes(User user) { IEnumerable episodes; @@ -388,7 +386,24 @@ namespace MediaBrowser.Controller.Entities.TV }).Cast(); } - episodes = FilterEpisodesBySeason(episodes, parentSeason, DisplaySpecialsWithSeasons); + return episodes; + } + + public IEnumerable GetEpisodes(User user, Season parentSeason, bool includeMissingEpisodes, bool includeVirtualUnairedEpisodes) + { + IEnumerable episodes = GetAllEpisodes(user); + + return GetEpisodes(user, parentSeason, includeMissingEpisodes, includeVirtualUnairedEpisodes, episodes); + } + + public IEnumerable GetEpisodes(User user, Season parentSeason, bool includeMissingEpisodes, bool includeVirtualUnairedEpisodes, IEnumerable allSeriesEpisodes) + { + if (allSeriesEpisodes == null) + { + return GetEpisodes(user, parentSeason, includeMissingEpisodes, includeVirtualUnairedEpisodes); + } + + var episodes = FilterEpisodesBySeason(allSeriesEpisodes, parentSeason, DisplaySpecialsWithSeasons); if (!includeMissingEpisodes) { diff --git a/MediaBrowser.Controller/Persistence/IUserDataRepository.cs b/MediaBrowser.Controller/Persistence/IUserDataRepository.cs index 2e165f416..ca4dc9751 100644 --- a/MediaBrowser.Controller/Persistence/IUserDataRepository.cs +++ b/MediaBrowser.Controller/Persistence/IUserDataRepository.cs @@ -29,6 +29,8 @@ namespace MediaBrowser.Controller.Persistence /// Task{UserItemData}. UserItemData GetUserData(Guid userId, string key); + UserItemData GetUserData(Guid userId, List keys); + /// /// Return all user data associated with the given user /// diff --git a/MediaBrowser.Server.Implementations/Library/UserDataManager.cs b/MediaBrowser.Server.Implementations/Library/UserDataManager.cs index afbce87a9..c2606dc4b 100644 --- a/MediaBrowser.Server.Implementations/Library/UserDataManager.cs +++ b/MediaBrowser.Server.Implementations/Library/UserDataManager.cs @@ -23,7 +23,8 @@ namespace MediaBrowser.Server.Implementations.Library { public event EventHandler UserDataSaved; - private readonly Dictionary _userData = new Dictionary(StringComparer.OrdinalIgnoreCase); + private readonly ConcurrentDictionary _userData = + new ConcurrentDictionary(StringComparer.OrdinalIgnoreCase); private readonly ILogger _logger; private readonly IServerConfigurationManager _config; @@ -64,13 +65,6 @@ namespace MediaBrowser.Server.Implementations.Library try { await Repository.SaveUserData(userId, key, userData, cancellationToken).ConfigureAwait(false); - - var newValue = userData; - - lock (_userData) - { - _userData[GetCacheKey(userId, key)] = newValue; - } } catch (Exception ex) { @@ -80,6 +74,9 @@ namespace MediaBrowser.Server.Implementations.Library } } + var cacheKey = GetCacheKey(userId, item.Id); + _userData.AddOrUpdate(cacheKey, userData, (k, v) => userData); + EventHelper.FireEventIfNotNull(UserDataSaved, this, new UserDataSaveEventArgs { Keys = keys, @@ -122,7 +119,7 @@ namespace MediaBrowser.Server.Implementations.Library throw; } - + } /// @@ -140,7 +137,7 @@ namespace MediaBrowser.Server.Implementations.Library return Repository.GetAllUserData(userId); } - public UserItemData GetUserData(Guid userId, List keys) + public UserItemData GetUserData(Guid userId, Guid itemId, List keys) { if (userId == Guid.Empty) { @@ -150,26 +147,23 @@ namespace MediaBrowser.Server.Implementations.Library { throw new ArgumentNullException("keys"); } - - lock (_userData) + if (keys.Count == 0) { - foreach (var key in keys) - { - var cacheKey = GetCacheKey(userId, key); - UserItemData value; - if (_userData.TryGetValue(cacheKey, out value)) - { - return value; - } + throw new ArgumentException("UserData keys cannot be empty."); + } - value = Repository.GetUserData(userId, key); + var cacheKey = GetCacheKey(userId, itemId); - if (value != null) - { - _userData[cacheKey] = value; - return value; - } - } + return _userData.GetOrAdd(cacheKey, k => GetUserDataInternal(userId, keys)); + } + + private UserItemData GetUserDataInternal(Guid userId, List keys) + { + var userData = Repository.GetUserData(userId, keys); + + if (userData != null) + { + return userData; } if (keys.Count > 0) @@ -184,57 +178,13 @@ namespace MediaBrowser.Server.Implementations.Library return null; } - /// - /// Gets the user data. - /// - /// The user id. - /// The key. - /// Task{UserItemData}. - public UserItemData GetUserData(Guid userId, string key) - { - if (userId == Guid.Empty) - { - throw new ArgumentNullException("userId"); - } - if (string.IsNullOrEmpty(key)) - { - throw new ArgumentNullException("key"); - } - - lock (_userData) - { - var cacheKey = GetCacheKey(userId, key); - UserItemData value; - if (_userData.TryGetValue(cacheKey, out value)) - { - return value; - } - - value = Repository.GetUserData(userId, key); - - if (value == null) - { - value = new UserItemData - { - UserId = userId, - Key = key - }; - } - - _userData[cacheKey] = value; - return value; - } - } - /// /// Gets the internal key. /// - /// The user id. - /// The key. /// System.String. - private string GetCacheKey(Guid userId, string key) + private string GetCacheKey(Guid userId, Guid itemId) { - return userId + key; + return userId.ToString("N") + itemId.ToString("N"); } public UserItemData GetUserData(IHasUserData user, IHasUserData item) @@ -249,7 +199,7 @@ namespace MediaBrowser.Server.Implementations.Library public UserItemData GetUserData(Guid userId, IHasUserData item) { - return GetUserData(userId, item.GetUserDataKeys()); + return GetUserData(userId, item.Id, item.GetUserDataKeys()); } public UserItemDataDto GetUserDataDto(IHasUserData item, User user) diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs index 7f3b32e06..bfdb9e0c7 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs @@ -5,7 +5,9 @@ using MediaBrowser.Model.Logging; using System; using System.Collections.Generic; using System.Data; +using System.Globalization; using System.IO; +using System.Text; using System.Threading; using System.Threading.Tasks; @@ -300,6 +302,52 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + public UserItemData GetUserData(Guid userId, List keys) + { + if (userId == Guid.Empty) + { + throw new ArgumentNullException("userId"); + } + if (keys == null) + { + throw new ArgumentNullException("keys"); + } + + using (var cmd = _connection.CreateCommand()) + { + var index = 0; + var excludeIds = new List(); + var builder = new StringBuilder(); + foreach (var key in keys) + { + var paramName = "@Key" + index; + excludeIds.Add("Key =" + paramName); + cmd.Parameters.Add(cmd, paramName, DbType.String).Value = key; + builder.Append(" WHEN Key=" + paramName + " THEN " + index); + index++; + } + + var keyText = string.Join(" OR ", excludeIds.ToArray()); + + cmd.CommandText = "select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@userId AND (" + keyText + ") "; + + cmd.CommandText += " ORDER BY (Case " + builder + " Else " + keys.Count.ToString(CultureInfo.InvariantCulture) + " End )"; + cmd.CommandText += " LIMIT 1"; + + cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) + { + if (reader.Read()) + { + return ReadRow(reader); + } + } + + return null; + } + } + /// /// Return all user-data associated with the given user /// diff --git a/MediaBrowser.Server.Implementations/TV/TVSeriesManager.cs b/MediaBrowser.Server.Implementations/TV/TVSeriesManager.cs index ec91dc1b7..6019d64b4 100644 --- a/MediaBrowser.Server.Implementations/TV/TVSeriesManager.cs +++ b/MediaBrowser.Server.Implementations/TV/TVSeriesManager.cs @@ -111,24 +111,6 @@ namespace MediaBrowser.Server.Implementations.TV .Select(i => GetNextUp(i, currentUser)) // Include if an episode was found, and either the series is not unwatched or the specific series was requested .Where(i => i.Item1 != null && (!i.Item3 || !string.IsNullOrWhiteSpace(request.SeriesId))) - //.OrderByDescending(i => - //{ - // var episode = i.Item1; - - // var seriesUserData = _userDataManager.GetUserData(user, episode.Series); - - // if (seriesUserData.IsFavorite) - // { - // return 2; - // } - - // if (seriesUserData.Likes.HasValue) - // { - // return seriesUserData.Likes.Value ? 1 : -1; - // } - - // return 0; - //}) .OrderByDescending(i => i.Item2) .ThenByDescending(i => i.Item1.PremiereDate ?? DateTime.MinValue) .Select(i => i.Item1); @@ -143,9 +125,8 @@ namespace MediaBrowser.Server.Implementations.TV private Tuple GetNextUp(Series series, User user) { // Get them in display order, then reverse - var allEpisodes = series.GetSeasons(user, true, true) - .Where(i => !i.IndexNumber.HasValue || i.IndexNumber.Value != 0) - .SelectMany(i => i.GetEpisodes(user)) + var allEpisodes = series.GetEpisodes(user, true, true) + .Where(i => !i.ParentIndexNumber.HasValue || i.ParentIndexNumber.Value != 0) .Reverse() .ToList(); @@ -155,6 +136,8 @@ namespace MediaBrowser.Server.Implementations.TV var includeMissing = user.Configuration.DisplayMissingEpisodes; + var unplayedEpisodes = new List(); + // Go back starting with the most recent episodes foreach (var episode in allEpisodes) { @@ -172,6 +155,8 @@ namespace MediaBrowser.Server.Implementations.TV } else { + unplayedEpisodes.Add(episode); + if (!episode.IsVirtualUnaired && (includeMissing || !episode.IsMissingEpisode)) { nextUp = episode; @@ -184,7 +169,18 @@ namespace MediaBrowser.Server.Implementations.TV return new Tuple(nextUp, lastWatchedDate, false); } - var firstEpisode = allEpisodes.LastOrDefault(i => !i.IsVirtualUnaired && (includeMissing || !i.IsMissingEpisode) && !i.IsPlayed(user)); + Episode firstEpisode = null; + // Find the first unplayed episode. Start from the back of the list since they're in reverse order + for (var i = unplayedEpisodes.Count - 1; i >= 0; i--) + { + var unplayedEpisode = unplayedEpisodes[i]; + + if (!unplayedEpisode.IsVirtualUnaired && (includeMissing || !unplayedEpisode.IsMissingEpisode)) + { + firstEpisode = unplayedEpisode; + break; + } + } // Return the first episode return new Tuple(firstEpisode, DateTime.MinValue, true); -- cgit v1.2.3 From da6e94396fb417077010d3a2e5f3282bf06234c3 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Sat, 11 Jun 2016 16:12:01 -0400 Subject: update connection pooling --- .../Chapters/IChapterManager.cs | 2 +- .../Persistence/IItemRepository.cs | 4 +- MediaBrowser.Providers/Chapters/ChapterManager.cs | 2 +- .../Activity/ActivityRepository.cs | 7 +- .../Library/LibraryManager.cs | 10 +- .../LiveTv/LiveTvManager.cs | 2 +- .../Notifications/SqliteNotificationsRepository.cs | 7 +- .../Persistence/BaseSqliteRepository.cs | 12 +- .../SqliteDisplayPreferencesRepository.cs | 7 +- .../Persistence/SqliteExtensions.cs | 13 +- .../SqliteFileOrganizationRepository.cs | 7 +- .../Persistence/SqliteItemRepository.cs | 2855 ++++++++++---------- .../Persistence/SqliteUserDataRepository.cs | 7 +- .../Persistence/SqliteUserRepository.cs | 3 - .../Security/AuthenticationRepository.cs | 7 +- .../Social/SharingRepository.cs | 3 - .../Sync/SyncRepository.cs | 3 - 17 files changed, 1533 insertions(+), 1418 deletions(-) (limited to 'MediaBrowser.Controller/Persistence') diff --git a/MediaBrowser.Controller/Chapters/IChapterManager.cs b/MediaBrowser.Controller/Chapters/IChapterManager.cs index 676ef9c56..27e06fb8d 100644 --- a/MediaBrowser.Controller/Chapters/IChapterManager.cs +++ b/MediaBrowser.Controller/Chapters/IChapterManager.cs @@ -33,7 +33,7 @@ namespace MediaBrowser.Controller.Chapters /// The chapters. /// The cancellation token. /// Task. - Task SaveChapters(string itemId, IEnumerable chapters, CancellationToken cancellationToken); + Task SaveChapters(string itemId, List chapters, CancellationToken cancellationToken); /// /// Searches the specified video. diff --git a/MediaBrowser.Controller/Persistence/IItemRepository.cs b/MediaBrowser.Controller/Persistence/IItemRepository.cs index 7bcc36958..80a6e4042 100644 --- a/MediaBrowser.Controller/Persistence/IItemRepository.cs +++ b/MediaBrowser.Controller/Persistence/IItemRepository.cs @@ -81,7 +81,7 @@ namespace MediaBrowser.Controller.Persistence /// The chapters. /// The cancellation token. /// Task. - Task SaveChapters(Guid id, IEnumerable chapters, CancellationToken cancellationToken); + Task SaveChapters(Guid id, List chapters, CancellationToken cancellationToken); /// /// Gets the media streams. @@ -97,7 +97,7 @@ namespace MediaBrowser.Controller.Persistence /// The streams. /// The cancellation token. /// Task. - Task SaveMediaStreams(Guid id, IEnumerable streams, CancellationToken cancellationToken); + Task SaveMediaStreams(Guid id, List streams, CancellationToken cancellationToken); /// /// Gets the item ids. diff --git a/MediaBrowser.Providers/Chapters/ChapterManager.cs b/MediaBrowser.Providers/Chapters/ChapterManager.cs index 6e2cd77eb..88811c850 100644 --- a/MediaBrowser.Providers/Chapters/ChapterManager.cs +++ b/MediaBrowser.Providers/Chapters/ChapterManager.cs @@ -259,7 +259,7 @@ namespace MediaBrowser.Providers.Chapters return _itemRepo.GetChapters(new Guid(itemId)); } - public Task SaveChapters(string itemId, IEnumerable chapters, CancellationToken cancellationToken) + public Task SaveChapters(string itemId, List chapters, CancellationToken cancellationToken) { return _itemRepo.SaveChapters(new Guid(itemId), chapters, cancellationToken); } diff --git a/MediaBrowser.Server.Implementations/Activity/ActivityRepository.cs b/MediaBrowser.Server.Implementations/Activity/ActivityRepository.cs index d6ae381e9..c992def39 100644 --- a/MediaBrowser.Server.Implementations/Activity/ActivityRepository.cs +++ b/MediaBrowser.Server.Implementations/Activity/ActivityRepository.cs @@ -30,12 +30,7 @@ namespace MediaBrowser.Server.Implementations.Activity 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)", - - //pragmas - "pragma temp_store = memory", - - "pragma shrink_memory" + "create index if not exists idx_ActivityLogEntries on ActivityLogEntries(Id)" }; connection.RunQueries(queries, Logger); diff --git a/MediaBrowser.Server.Implementations/Library/LibraryManager.cs b/MediaBrowser.Server.Implementations/Library/LibraryManager.cs index 503fb1aa7..2483ec93e 100644 --- a/MediaBrowser.Server.Implementations/Library/LibraryManager.cs +++ b/MediaBrowser.Server.Implementations/Library/LibraryManager.cs @@ -1309,7 +1309,15 @@ namespace MediaBrowser.Server.Implementations.Library AddUserToQuery(query, query.User); } - return ItemRepository.GetItems(query); + if (query.EnableTotalRecordCount) + { + return ItemRepository.GetItems(query); + } + + return new QueryResult + { + Items = ItemRepository.GetItemList(query).ToArray() + }; } public List GetItemIds(InternalItemsQuery query) diff --git a/MediaBrowser.Server.Implementations/LiveTv/LiveTvManager.cs b/MediaBrowser.Server.Implementations/LiveTv/LiveTvManager.cs index d7491d2de..171ed824e 100644 --- a/MediaBrowser.Server.Implementations/LiveTv/LiveTvManager.cs +++ b/MediaBrowser.Server.Implementations/LiveTv/LiveTvManager.cs @@ -952,7 +952,7 @@ namespace MediaBrowser.Server.Implementations.LiveTv if (query.Limit.HasValue) { - internalQuery.Limit = Math.Max(query.Limit.Value * 5, 300); + internalQuery.Limit = Math.Max(query.Limit.Value * 5, 200); } if (query.HasAired.HasValue) diff --git a/MediaBrowser.Server.Implementations/Notifications/SqliteNotificationsRepository.cs b/MediaBrowser.Server.Implementations/Notifications/SqliteNotificationsRepository.cs index 6c3bc3050..be8c6d48d 100644 --- a/MediaBrowser.Server.Implementations/Notifications/SqliteNotificationsRepository.cs +++ b/MediaBrowser.Server.Implementations/Notifications/SqliteNotificationsRepository.cs @@ -32,12 +32,7 @@ namespace MediaBrowser.Server.Implementations.Notifications "create table if not exists Notifications (Id GUID NOT NULL, UserId GUID NOT NULL, Date DATETIME NOT NULL, Name TEXT NOT NULL, Description TEXT, Url TEXT, Level TEXT NOT NULL, IsRead BOOLEAN NOT NULL, Category TEXT NOT NULL, RelatedId TEXT, PRIMARY KEY (Id, UserId))", "create index if not exists idx_Notifications1 on Notifications(Id)", - "create index if not exists idx_Notifications2 on Notifications(UserId)", - - //pragmas - "pragma temp_store = memory", - - "pragma shrink_memory" + "create index if not exists idx_Notifications2 on Notifications(UserId)" }; connection.RunQueries(queries, Logger); diff --git a/MediaBrowser.Server.Implementations/Persistence/BaseSqliteRepository.cs b/MediaBrowser.Server.Implementations/Persistence/BaseSqliteRepository.cs index de0c245bd..c6e795229 100644 --- a/MediaBrowser.Server.Implementations/Persistence/BaseSqliteRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/BaseSqliteRepository.cs @@ -20,9 +20,17 @@ namespace MediaBrowser.Server.Implementations.Persistence Logger = logManager.GetLogger(GetType().Name); } - protected Task CreateConnection(bool isReadOnly = false) + protected virtual async Task CreateConnection(bool isReadOnly = false) { - return DbConnector.Connect(DbFilePath, false, true); + var connection = await DbConnector.Connect(DbFilePath, false, true).ConfigureAwait(false); + + connection.RunQueries(new [] + { + "pragma temp_store = memory" + + }, Logger); + + return connection; } private bool _disposed; diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs index 1a6a88ebe..40970dbe4 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs @@ -53,12 +53,7 @@ namespace MediaBrowser.Server.Implementations.Persistence string[] queries = { "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)", - - //pragmas - "pragma temp_store = memory", - - "pragma shrink_memory" + "create unique index if not exists userdisplaypreferencesindex on userdisplaypreferences (id, userId, client)" }; connection.RunQueries(queries, Logger); diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs index cc9e3ebcc..0a76381e1 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs @@ -26,8 +26,6 @@ namespace MediaBrowser.Server.Implementations.Persistence throw new ArgumentNullException("dbPath"); } - logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, dbPath); - var connectionstr = new SQLiteConnectionStringBuilder { PageSize = 4096, @@ -39,7 +37,16 @@ namespace MediaBrowser.Server.Implementations.Persistence ReadOnly = isReadOnly }; - var connection = new SQLiteConnection(connectionstr.ConnectionString); + var connectionString = connectionstr.ConnectionString; + + if (enablePooling) + { + connectionString += ";Max Pool Size=100"; + } + + //logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, connectionString); + + var connection = new SQLiteConnection(connectionString); await connection.OpenAsync().ConfigureAwait(false); diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs index 8a4ba6460..d7e8afdd4 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs @@ -44,12 +44,7 @@ namespace MediaBrowser.Server.Implementations.Persistence 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)", - - //pragmas - "pragma temp_store = memory", - - "pragma shrink_memory" + "create index if not exists idx_FileOrganizerResults on FileOrganizerResults(ResultId)" }; _connection.RunQueries(queries, Logger); diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 9bfdd879d..0eb56a1fc 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -31,8 +31,6 @@ namespace MediaBrowser.Server.Implementations.Persistence /// public class SqliteItemRepository : BaseSqliteRepository, IItemRepository { - private IDbConnection _connection; - private readonly TypeMapper _typeMapper = new TypeMapper(); /// @@ -58,42 +56,8 @@ namespace MediaBrowser.Server.Implementations.Persistence /// private readonly IServerConfigurationManager _config; - /// - /// The _save item command - /// - private IDbCommand _saveItemCommand; - private readonly string _criticReviewsPath; - private IDbCommand _deleteItemCommand; - - private IDbCommand _deletePeopleCommand; - private IDbCommand _savePersonCommand; - - private IDbCommand _deleteChaptersCommand; - private IDbCommand _saveChapterCommand; - - private IDbCommand _deleteStreamsCommand; - private IDbCommand _saveStreamCommand; - - private IDbCommand _deleteAncestorsCommand; - private IDbCommand _saveAncestorCommand; - - private IDbCommand _deleteUserDataKeysCommand; - private IDbCommand _saveUserDataKeysCommand; - - private IDbCommand _deleteItemValuesCommand; - private IDbCommand _saveItemValuesCommand; - - private IDbCommand _deleteProviderIdsCommand; - private IDbCommand _saveProviderIdsCommand; - - private IDbCommand _deleteImagesCommand; - private IDbCommand _saveImagesCommand; - - private IDbCommand _updateInheritedRatingCommand; - private IDbCommand _updateInheritedTagsCommand; - public const int LatestSchemaVersion = 89; /// @@ -115,6 +79,22 @@ namespace MediaBrowser.Server.Implementations.Persistence _jsonSerializer = jsonSerializer; _criticReviewsPath = Path.Combine(_config.ApplicationPaths.DataPath, "critic-reviews"); + DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); + } + + protected override async Task CreateConnection(bool isReadOnly = false) + { + var connection = await DbConnector.Connect(DbFilePath, false, true, 20000).ConfigureAwait(false); + + //AttachUserDataDb(connection); + + //connection.RunQueries(new [] + //{ + // "pragma locking_mode=EXCLUSIVE" + + //}, Logger); + + return connection; } private const string ChaptersTableName = "Chapters2"; @@ -125,14 +105,12 @@ namespace MediaBrowser.Server.Implementations.Persistence /// Task. public async Task Initialize(IDbConnector dbConnector) { - var dbFile = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); - - _connection = await dbConnector.Connect(dbFile, false, false, 6000).ConfigureAwait(false); - - 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))"; + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + 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 = { + string[] queries = { "create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB, ParentId GUID, Path TEXT)", "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)", @@ -165,113 +143,113 @@ namespace MediaBrowser.Server.Implementations.Persistence createMediaStreamsTableCommand, "create index if not exists idx_mediastreams1 on mediastreams(ItemId)", - - //pragmas - "pragma temp_store = memory", - - "pragma shrink_memory" + }; - _connection.RunQueries(queries, Logger); - - _connection.AddColumn(Logger, "AncestorIds", "AncestorIdText", "Text"); - - _connection.AddColumn(Logger, "TypedBaseItems", "Path", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "StartDate", "DATETIME"); - _connection.AddColumn(Logger, "TypedBaseItems", "EndDate", "DATETIME"); - _connection.AddColumn(Logger, "TypedBaseItems", "ChannelId", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsMovie", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsSports", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsKids", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "CommunityRating", "Float"); - _connection.AddColumn(Logger, "TypedBaseItems", "CustomRating", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "IndexNumber", "INT"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsLocked", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "Name", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "OfficialRating", "Text"); - - _connection.AddColumn(Logger, "TypedBaseItems", "MediaType", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "Overview", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "ParentIndexNumber", "INT"); - _connection.AddColumn(Logger, "TypedBaseItems", "PremiereDate", "DATETIME"); - _connection.AddColumn(Logger, "TypedBaseItems", "ProductionYear", "INT"); - _connection.AddColumn(Logger, "TypedBaseItems", "ParentId", "GUID"); - _connection.AddColumn(Logger, "TypedBaseItems", "Genres", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "ParentalRatingValue", "INT"); - _connection.AddColumn(Logger, "TypedBaseItems", "SchemaVersion", "INT"); - _connection.AddColumn(Logger, "TypedBaseItems", "SortName", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "RunTimeTicks", "BIGINT"); - - _connection.AddColumn(Logger, "TypedBaseItems", "OfficialRatingDescription", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "HomePageUrl", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "VoteCount", "INT"); - _connection.AddColumn(Logger, "TypedBaseItems", "DisplayMediaType", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "DateCreated", "DATETIME"); - _connection.AddColumn(Logger, "TypedBaseItems", "DateModified", "DATETIME"); - - _connection.AddColumn(Logger, "TypedBaseItems", "ForcedSortName", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsOffline", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "LocationType", "Text"); - - _connection.AddColumn(Logger, "TypedBaseItems", "IsSeries", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsLive", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsNews", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsPremiere", "BIT"); - - _connection.AddColumn(Logger, "TypedBaseItems", "EpisodeTitle", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsRepeat", "BIT"); - - _connection.AddColumn(Logger, "TypedBaseItems", "PreferredMetadataLanguage", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "PreferredMetadataCountryCode", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsHD", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "ExternalEtag", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "DateLastRefreshed", "DATETIME"); - - _connection.AddColumn(Logger, "TypedBaseItems", "DateLastSaved", "DATETIME"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsInMixedFolder", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "LockedFields", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "Studios", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "Audio", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "ExternalServiceId", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "Tags", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsFolder", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "InheritedParentalRatingValue", "INT"); - _connection.AddColumn(Logger, "TypedBaseItems", "UnratedType", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "TopParentId", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsItemByName", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "SourceType", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "TrailerTypes", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "CriticRating", "Float"); - _connection.AddColumn(Logger, "TypedBaseItems", "CriticRatingSummary", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "DateModifiedDuringLastRefresh", "DATETIME"); - _connection.AddColumn(Logger, "TypedBaseItems", "InheritedTags", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "CleanName", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "PresentationUniqueKey", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "SlugName", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "OriginalTitle", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "PrimaryVersionId", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "DateLastMediaAdded", "DATETIME"); - _connection.AddColumn(Logger, "TypedBaseItems", "Album", "Text"); - _connection.AddColumn(Logger, "TypedBaseItems", "IsVirtualItem", "BIT"); - _connection.AddColumn(Logger, "TypedBaseItems", "SeriesName", "Text"); - - _connection.AddColumn(Logger, "UserDataKeys", "Priority", "INT"); - - string[] postQueries = - { + connection.RunQueries(queries, Logger); + + connection.AddColumn(Logger, "AncestorIds", "AncestorIdText", "Text"); + + connection.AddColumn(Logger, "TypedBaseItems", "Path", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "StartDate", "DATETIME"); + connection.AddColumn(Logger, "TypedBaseItems", "EndDate", "DATETIME"); + connection.AddColumn(Logger, "TypedBaseItems", "ChannelId", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "IsMovie", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "IsSports", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "IsKids", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "CommunityRating", "Float"); + connection.AddColumn(Logger, "TypedBaseItems", "CustomRating", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "IndexNumber", "INT"); + connection.AddColumn(Logger, "TypedBaseItems", "IsLocked", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "Name", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "OfficialRating", "Text"); + + connection.AddColumn(Logger, "TypedBaseItems", "MediaType", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "Overview", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "ParentIndexNumber", "INT"); + connection.AddColumn(Logger, "TypedBaseItems", "PremiereDate", "DATETIME"); + connection.AddColumn(Logger, "TypedBaseItems", "ProductionYear", "INT"); + connection.AddColumn(Logger, "TypedBaseItems", "ParentId", "GUID"); + connection.AddColumn(Logger, "TypedBaseItems", "Genres", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "ParentalRatingValue", "INT"); + connection.AddColumn(Logger, "TypedBaseItems", "SchemaVersion", "INT"); + connection.AddColumn(Logger, "TypedBaseItems", "SortName", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "RunTimeTicks", "BIGINT"); + + connection.AddColumn(Logger, "TypedBaseItems", "OfficialRatingDescription", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "HomePageUrl", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "VoteCount", "INT"); + connection.AddColumn(Logger, "TypedBaseItems", "DisplayMediaType", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "DateCreated", "DATETIME"); + connection.AddColumn(Logger, "TypedBaseItems", "DateModified", "DATETIME"); + + connection.AddColumn(Logger, "TypedBaseItems", "ForcedSortName", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "IsOffline", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "LocationType", "Text"); + + connection.AddColumn(Logger, "TypedBaseItems", "IsSeries", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "IsLive", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "IsNews", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "IsPremiere", "BIT"); + + connection.AddColumn(Logger, "TypedBaseItems", "EpisodeTitle", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "IsRepeat", "BIT"); + + connection.AddColumn(Logger, "TypedBaseItems", "PreferredMetadataLanguage", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "PreferredMetadataCountryCode", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "IsHD", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "ExternalEtag", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "DateLastRefreshed", "DATETIME"); + + connection.AddColumn(Logger, "TypedBaseItems", "DateLastSaved", "DATETIME"); + connection.AddColumn(Logger, "TypedBaseItems", "IsInMixedFolder", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "LockedFields", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "Studios", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "Audio", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "ExternalServiceId", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "Tags", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "IsFolder", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "InheritedParentalRatingValue", "INT"); + connection.AddColumn(Logger, "TypedBaseItems", "UnratedType", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "TopParentId", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "IsItemByName", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "SourceType", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "TrailerTypes", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "CriticRating", "Float"); + connection.AddColumn(Logger, "TypedBaseItems", "CriticRatingSummary", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "DateModifiedDuringLastRefresh", "DATETIME"); + connection.AddColumn(Logger, "TypedBaseItems", "InheritedTags", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "CleanName", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "PresentationUniqueKey", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "SlugName", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "OriginalTitle", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "PrimaryVersionId", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "DateLastMediaAdded", "DATETIME"); + connection.AddColumn(Logger, "TypedBaseItems", "Album", "Text"); + connection.AddColumn(Logger, "TypedBaseItems", "IsVirtualItem", "BIT"); + connection.AddColumn(Logger, "TypedBaseItems", "SeriesName", "Text"); + + connection.AddColumn(Logger, "UserDataKeys", "Priority", "INT"); + + string[] postQueries = + { "create index if not exists idx_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)", "create index if not exists idx_Type on TypedBaseItems(Type)", "create index if not exists idx_TopParentId on TypedBaseItems(TopParentId)", "create index if not exists idx_TypeTopParentId on TypedBaseItems(Type,TopParentId)" - }; + }; - _connection.RunQueries(postQueries, Logger); + connection.RunQueries(postQueries, Logger); - PrepareStatements(); + new MediaStreamColumns(connection, Logger).AddColumns(); - new MediaStreamColumns(_connection, Logger).AddColumns(); + //AttachUserDataDb(connection); + } + } - DataExtensions.Attach(_connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb"); + private void AttachUserDataDb(IDbConnection connection) + { + DataExtensions.Attach(connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb" + Guid.NewGuid().ToString("N")); } private readonly string[] _retriveItemColumns = @@ -373,220 +351,6 @@ namespace MediaBrowser.Server.Implementations.Persistence "CodecTimeBase" }; - /// - /// Prepares the statements. - /// - private void PrepareStatements() - { - var saveColumns = new List - { - "guid", - "type", - "data", - "Path", - "StartDate", - "EndDate", - "ChannelId", - "IsKids", - "IsMovie", - "IsSports", - "IsSeries", - "IsLive", - "IsNews", - "IsPremiere", - "EpisodeTitle", - "IsRepeat", - "CommunityRating", - "CustomRating", - "IndexNumber", - "IsLocked", - "Name", - "OfficialRating", - "MediaType", - "Overview", - "ParentIndexNumber", - "PremiereDate", - "ProductionYear", - "ParentId", - "Genres", - "ParentalRatingValue", - "InheritedParentalRatingValue", - "SchemaVersion", - "SortName", - "RunTimeTicks", - "OfficialRatingDescription", - "HomePageUrl", - "VoteCount", - "DisplayMediaType", - "DateCreated", - "DateModified", - "ForcedSortName", - "IsOffline", - "LocationType", - "PreferredMetadataLanguage", - "PreferredMetadataCountryCode", - "IsHD", - "ExternalEtag", - "DateLastRefreshed", - "DateLastSaved", - "IsInMixedFolder", - "LockedFields", - "Studios", - "Audio", - "ExternalServiceId", - "Tags", - "IsFolder", - "UnratedType", - "TopParentId", - "IsItemByName", - "SourceType", - "TrailerTypes", - "CriticRating", - "CriticRatingSummary", - "DateModifiedDuringLastRefresh", - "InheritedTags", - "CleanName", - "PresentationUniqueKey", - "SlugName", - "OriginalTitle", - "PrimaryVersionId", - "DateLastMediaAdded", - "Album", - "IsVirtualItem", - "SeriesName" - }; - _saveItemCommand = _connection.CreateCommand(); - _saveItemCommand.CommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values ("; - - for (var i = 1; i <= saveColumns.Count; i++) - { - if (i > 1) - { - _saveItemCommand.CommandText += ","; - } - _saveItemCommand.CommandText += "@" + i.ToString(CultureInfo.InvariantCulture); - - _saveItemCommand.Parameters.Add(_saveItemCommand, "@" + i.ToString(CultureInfo.InvariantCulture)); - } - _saveItemCommand.CommandText += ")"; - - _deleteItemCommand = _connection.CreateCommand(); - _deleteItemCommand.CommandText = "delete from TypedBaseItems where guid=@Id"; - _deleteItemCommand.Parameters.Add(_deleteItemCommand, "@Id"); - - // People - _deletePeopleCommand = _connection.CreateCommand(); - _deletePeopleCommand.CommandText = "delete from People where ItemId=@Id"; - _deletePeopleCommand.Parameters.Add(_deletePeopleCommand, "@Id"); - - _savePersonCommand = _connection.CreateCommand(); - _savePersonCommand.CommandText = "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values (@ItemId, @Name, @Role, @PersonType, @SortOrder, @ListOrder)"; - _savePersonCommand.Parameters.Add(_savePersonCommand, "@ItemId"); - _savePersonCommand.Parameters.Add(_savePersonCommand, "@Name"); - _savePersonCommand.Parameters.Add(_savePersonCommand, "@Role"); - _savePersonCommand.Parameters.Add(_savePersonCommand, "@PersonType"); - _savePersonCommand.Parameters.Add(_savePersonCommand, "@SortOrder"); - _savePersonCommand.Parameters.Add(_savePersonCommand, "@ListOrder"); - - // Ancestors - _deleteAncestorsCommand = _connection.CreateCommand(); - _deleteAncestorsCommand.CommandText = "delete from AncestorIds where ItemId=@Id"; - _deleteAncestorsCommand.Parameters.Add(_deleteAncestorsCommand, "@Id"); - - _saveAncestorCommand = _connection.CreateCommand(); - _saveAncestorCommand.CommandText = "insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values (@ItemId, @AncestorId, @AncestorIdText)"; - _saveAncestorCommand.Parameters.Add(_saveAncestorCommand, "@ItemId"); - _saveAncestorCommand.Parameters.Add(_saveAncestorCommand, "@AncestorId"); - _saveAncestorCommand.Parameters.Add(_saveAncestorCommand, "@AncestorIdText"); - - // Chapters - _deleteChaptersCommand = _connection.CreateCommand(); - _deleteChaptersCommand.CommandText = "delete from " + ChaptersTableName + " where ItemId=@ItemId"; - _deleteChaptersCommand.Parameters.Add(_deleteChaptersCommand, "@ItemId"); - - _saveChapterCommand = _connection.CreateCommand(); - _saveChapterCommand.CommandText = "replace into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath) values (@ItemId, @ChapterIndex, @StartPositionTicks, @Name, @ImagePath)"; - - _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ItemId"); - _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ChapterIndex"); - _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@StartPositionTicks"); - _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@Name"); - _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ImagePath"); - - // MediaStreams - _deleteStreamsCommand = _connection.CreateCommand(); - _deleteStreamsCommand.CommandText = "delete from mediastreams where ItemId=@ItemId"; - _deleteStreamsCommand.Parameters.Add(_deleteStreamsCommand, "@ItemId"); - - _saveStreamCommand = _connection.CreateCommand(); - - _saveStreamCommand.CommandText = string.Format("replace into mediastreams ({0}) values ({1})", - string.Join(",", _mediaStreamSaveColumns), - string.Join(",", _mediaStreamSaveColumns.Select(i => "@" + i).ToArray())); - - foreach (var col in _mediaStreamSaveColumns) - { - _saveStreamCommand.Parameters.Add(_saveStreamCommand, "@" + col); - } - - _updateInheritedRatingCommand = _connection.CreateCommand(); - _updateInheritedRatingCommand.CommandText = "Update TypedBaseItems set InheritedParentalRatingValue=@InheritedParentalRatingValue where Guid=@Guid"; - _updateInheritedRatingCommand.Parameters.Add(_updateInheritedRatingCommand, "@Guid"); - _updateInheritedRatingCommand.Parameters.Add(_updateInheritedRatingCommand, "@InheritedParentalRatingValue"); - - _updateInheritedTagsCommand = _connection.CreateCommand(); - _updateInheritedTagsCommand.CommandText = "Update TypedBaseItems set InheritedTags=@InheritedTags where Guid=@Guid"; - _updateInheritedTagsCommand.Parameters.Add(_updateInheritedTagsCommand, "@Guid"); - _updateInheritedTagsCommand.Parameters.Add(_updateInheritedTagsCommand, "@InheritedTags"); - - // user data - _deleteUserDataKeysCommand = _connection.CreateCommand(); - _deleteUserDataKeysCommand.CommandText = "delete from UserDataKeys where ItemId=@Id"; - _deleteUserDataKeysCommand.Parameters.Add(_deleteUserDataKeysCommand, "@Id"); - - _saveUserDataKeysCommand = _connection.CreateCommand(); - _saveUserDataKeysCommand.CommandText = "insert into UserDataKeys (ItemId, UserDataKey, Priority) values (@ItemId, @UserDataKey, @Priority)"; - _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@ItemId"); - _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@UserDataKey"); - _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@Priority"); - - // item values - _deleteItemValuesCommand = _connection.CreateCommand(); - _deleteItemValuesCommand.CommandText = "delete from ItemValues where ItemId=@Id"; - _deleteItemValuesCommand.Parameters.Add(_deleteItemValuesCommand, "@Id"); - - _saveItemValuesCommand = _connection.CreateCommand(); - _saveItemValuesCommand.CommandText = "insert into ItemValues (ItemId, Type, Value) values (@ItemId, @Type, @Value)"; - _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@ItemId"); - _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Type"); - _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Value"); - - // provider ids - _deleteProviderIdsCommand = _connection.CreateCommand(); - _deleteProviderIdsCommand.CommandText = "delete from ProviderIds where ItemId=@Id"; - _deleteProviderIdsCommand.Parameters.Add(_deleteProviderIdsCommand, "@Id"); - - _saveProviderIdsCommand = _connection.CreateCommand(); - _saveProviderIdsCommand.CommandText = "insert into ProviderIds (ItemId, Name, Value) values (@ItemId, @Name, @Value)"; - _saveProviderIdsCommand.Parameters.Add(_saveProviderIdsCommand, "@ItemId"); - _saveProviderIdsCommand.Parameters.Add(_saveProviderIdsCommand, "@Name"); - _saveProviderIdsCommand.Parameters.Add(_saveProviderIdsCommand, "@Value"); - - // images - _deleteImagesCommand = _connection.CreateCommand(); - _deleteImagesCommand.CommandText = "delete from Images where ItemId=@Id"; - _deleteImagesCommand.Parameters.Add(_deleteImagesCommand, "@Id"); - - _saveImagesCommand = _connection.CreateCommand(); - _saveImagesCommand.CommandText = "insert into Images (ItemId, ImageType, Path, DateModified, IsPlaceHolder, SortOrder) values (@ItemId, @ImageType, @Path, @DateModified, @IsPlaceHolder, @SortOrder)"; - _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@ItemId"); - _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@ImageType"); - _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@Path"); - _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@DateModified"); - _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@IsPlaceHolder"); - _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@SortOrder"); - } - /// /// Save a standard item in the repo /// @@ -626,314 +390,408 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); + IDbTransaction transaction = null; - foreach (var item in items) + try { - cancellationToken.ThrowIfCancellationRequested(); - - var index = 0; - - _saveItemCommand.GetParameter(index++).Value = item.Id; - _saveItemCommand.GetParameter(index++).Value = item.GetType().FullName; - _saveItemCommand.GetParameter(index++).Value = _jsonSerializer.SerializeToBytes(item); - - _saveItemCommand.GetParameter(index++).Value = item.Path; - - var hasStartDate = item as IHasStartDate; - if (hasStartDate != null) - { - _saveItemCommand.GetParameter(index++).Value = hasStartDate.StartDate; - } - else - { - _saveItemCommand.GetParameter(index++).Value = null; - } - - _saveItemCommand.GetParameter(index++).Value = item.EndDate; - _saveItemCommand.GetParameter(index++).Value = item.ChannelId; - - var hasProgramAttributes = item as IHasProgramAttributes; - if (hasProgramAttributes != null) - { - _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsKids; - _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsMovie; - _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsSports; - _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsSeries; - _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsLive; - _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsNews; - _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsPremiere; - _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.EpisodeTitle; - _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsRepeat; - } - else - { - _saveItemCommand.GetParameter(index++).Value = null; - _saveItemCommand.GetParameter(index++).Value = null; - _saveItemCommand.GetParameter(index++).Value = null; - _saveItemCommand.GetParameter(index++).Value = null; - _saveItemCommand.GetParameter(index++).Value = null; - _saveItemCommand.GetParameter(index++).Value = null; - _saveItemCommand.GetParameter(index++).Value = null; - _saveItemCommand.GetParameter(index++).Value = null; - _saveItemCommand.GetParameter(index++).Value = null; - } - - _saveItemCommand.GetParameter(index++).Value = item.CommunityRating; - _saveItemCommand.GetParameter(index++).Value = item.CustomRating; - - _saveItemCommand.GetParameter(index++).Value = item.IndexNumber; - _saveItemCommand.GetParameter(index++).Value = item.IsLocked; - - _saveItemCommand.GetParameter(index++).Value = item.Name; - _saveItemCommand.GetParameter(index++).Value = item.OfficialRating; - - _saveItemCommand.GetParameter(index++).Value = item.MediaType; - _saveItemCommand.GetParameter(index++).Value = item.Overview; - _saveItemCommand.GetParameter(index++).Value = item.ParentIndexNumber; - _saveItemCommand.GetParameter(index++).Value = item.PremiereDate; - _saveItemCommand.GetParameter(index++).Value = item.ProductionYear; - - if (item.ParentId == Guid.Empty) - { - _saveItemCommand.GetParameter(index++).Value = null; - } - else - { - _saveItemCommand.GetParameter(index++).Value = item.ParentId; - } - - _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Genres.ToArray()); - _saveItemCommand.GetParameter(index++).Value = item.GetParentalRatingValue() ?? 0; - _saveItemCommand.GetParameter(index++).Value = item.GetInheritedParentalRatingValue() ?? 0; - - _saveItemCommand.GetParameter(index++).Value = LatestSchemaVersion; - _saveItemCommand.GetParameter(index++).Value = item.SortName; - _saveItemCommand.GetParameter(index++).Value = item.RunTimeTicks; - - _saveItemCommand.GetParameter(index++).Value = item.OfficialRatingDescription; - _saveItemCommand.GetParameter(index++).Value = item.HomePageUrl; - _saveItemCommand.GetParameter(index++).Value = item.VoteCount; - _saveItemCommand.GetParameter(index++).Value = item.DisplayMediaType; - _saveItemCommand.GetParameter(index++).Value = item.DateCreated; - _saveItemCommand.GetParameter(index++).Value = item.DateModified; - - _saveItemCommand.GetParameter(index++).Value = item.ForcedSortName; - _saveItemCommand.GetParameter(index++).Value = item.IsOffline; - _saveItemCommand.GetParameter(index++).Value = item.LocationType.ToString(); - - _saveItemCommand.GetParameter(index++).Value = item.PreferredMetadataLanguage; - _saveItemCommand.GetParameter(index++).Value = item.PreferredMetadataCountryCode; - _saveItemCommand.GetParameter(index++).Value = item.IsHD; - _saveItemCommand.GetParameter(index++).Value = item.ExternalEtag; - - if (item.DateLastRefreshed == default(DateTime)) - { - _saveItemCommand.GetParameter(index++).Value = null; - } - else - { - _saveItemCommand.GetParameter(index++).Value = item.DateLastRefreshed; - } - - if (item.DateLastSaved == default(DateTime)) - { - _saveItemCommand.GetParameter(index++).Value = null; - } - else - { - _saveItemCommand.GetParameter(index++).Value = item.DateLastSaved; - } - - _saveItemCommand.GetParameter(index++).Value = item.IsInMixedFolder; - _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.LockedFields.Select(i => i.ToString()).ToArray()); - _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Studios.ToArray()); - - if (item.Audio.HasValue) - { - _saveItemCommand.GetParameter(index++).Value = item.Audio.Value.ToString(); - } - else - { - _saveItemCommand.GetParameter(index++).Value = null; - } - - _saveItemCommand.GetParameter(index++).Value = item.ServiceName; - - if (item.Tags.Count > 0) - { - _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Tags.ToArray()); - } - else - { - _saveItemCommand.GetParameter(index++).Value = null; - } - - _saveItemCommand.GetParameter(index++).Value = item.IsFolder; - - _saveItemCommand.GetParameter(index++).Value = item.GetBlockUnratedType().ToString(); - - var topParent = item.GetTopParent(); - if (topParent != null) - { - //Logger.Debug("Item {0} has top parent {1}", item.Id, topParent.Id); - _saveItemCommand.GetParameter(index++).Value = topParent.Id.ToString("N"); - } - else - { - //Logger.Debug("Item {0} has null top parent", item.Id); - _saveItemCommand.GetParameter(index++).Value = null; - } - - var isByName = false; - var byName = item as IItemByName; - if (byName != null) - { - var dualAccess = item as IHasDualAccess; - isByName = dualAccess == null || dualAccess.IsAccessedByName; - } - _saveItemCommand.GetParameter(index++).Value = isByName; - - _saveItemCommand.GetParameter(index++).Value = item.SourceType.ToString(); - - var trailer = item as Trailer; - if (trailer != null && trailer.TrailerTypes.Count > 0) - { - _saveItemCommand.GetParameter(index++).Value = string.Join("|", trailer.TrailerTypes.Select(i => i.ToString()).ToArray()); - } - else - { - _saveItemCommand.GetParameter(index++).Value = null; - } + transaction = connection.BeginTransaction(); - _saveItemCommand.GetParameter(index++).Value = item.CriticRating; - _saveItemCommand.GetParameter(index++).Value = item.CriticRatingSummary; - - if (!item.DateModifiedDuringLastRefresh.HasValue || item.DateModifiedDuringLastRefresh.Value == default(DateTime)) - { - _saveItemCommand.GetParameter(index++).Value = null; - } - else - { - _saveItemCommand.GetParameter(index++).Value = item.DateModifiedDuringLastRefresh.Value; - } - - var inheritedTags = item.GetInheritedTags(); - if (inheritedTags.Count > 0) - { - _saveItemCommand.GetParameter(index++).Value = string.Join("|", inheritedTags.ToArray()); - } - else - { - _saveItemCommand.GetParameter(index++).Value = null; - } - - if (string.IsNullOrWhiteSpace(item.Name)) - { - _saveItemCommand.GetParameter(index++).Value = null; - } - else + using (var saveItemCommand = connection.CreateCommand()) { - _saveItemCommand.GetParameter(index++).Value = item.Name.RemoveDiacritics(); - } - - _saveItemCommand.GetParameter(index++).Value = item.PresentationUniqueKey; - _saveItemCommand.GetParameter(index++).Value = item.SlugName; - _saveItemCommand.GetParameter(index++).Value = item.OriginalTitle; - - var video = item as Video; - if (video != null) - { - _saveItemCommand.GetParameter(index++).Value = video.PrimaryVersionId; - } - else - { - _saveItemCommand.GetParameter(index++).Value = null; - } - - var folder = item as Folder; - if (folder != null && folder.DateLastMediaAdded.HasValue) - { - _saveItemCommand.GetParameter(index++).Value = folder.DateLastMediaAdded.Value; - } - else - { - _saveItemCommand.GetParameter(index++).Value = null; - } - - _saveItemCommand.GetParameter(index++).Value = item.Album; + var saveColumns = new List + { + "guid", + "type", + "data", + "Path", + "StartDate", + "EndDate", + "ChannelId", + "IsKids", + "IsMovie", + "IsSports", + "IsSeries", + "IsLive", + "IsNews", + "IsPremiere", + "EpisodeTitle", + "IsRepeat", + "CommunityRating", + "CustomRating", + "IndexNumber", + "IsLocked", + "Name", + "OfficialRating", + "MediaType", + "Overview", + "ParentIndexNumber", + "PremiereDate", + "ProductionYear", + "ParentId", + "Genres", + "ParentalRatingValue", + "InheritedParentalRatingValue", + "SchemaVersion", + "SortName", + "RunTimeTicks", + "OfficialRatingDescription", + "HomePageUrl", + "VoteCount", + "DisplayMediaType", + "DateCreated", + "DateModified", + "ForcedSortName", + "IsOffline", + "LocationType", + "PreferredMetadataLanguage", + "PreferredMetadataCountryCode", + "IsHD", + "ExternalEtag", + "DateLastRefreshed", + "DateLastSaved", + "IsInMixedFolder", + "LockedFields", + "Studios", + "Audio", + "ExternalServiceId", + "Tags", + "IsFolder", + "UnratedType", + "TopParentId", + "IsItemByName", + "SourceType", + "TrailerTypes", + "CriticRating", + "CriticRatingSummary", + "DateModifiedDuringLastRefresh", + "InheritedTags", + "CleanName", + "PresentationUniqueKey", + "SlugName", + "OriginalTitle", + "PrimaryVersionId", + "DateLastMediaAdded", + "Album", + "IsVirtualItem", + "SeriesName" + }; + + saveItemCommand.CommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values ("; + + for (var i = 1; i <= saveColumns.Count; i++) + { + if (i > 1) + { + saveItemCommand.CommandText += ","; + } + saveItemCommand.CommandText += "@" + i.ToString(CultureInfo.InvariantCulture); - var season = item as Season; - if (season != null && season.IsVirtualItem.HasValue) - { - _saveItemCommand.GetParameter(index++).Value = season.IsVirtualItem.Value; - } - else - { - _saveItemCommand.GetParameter(index++).Value = null; - } + saveItemCommand.Parameters.Add(saveItemCommand, "@" + i.ToString(CultureInfo.InvariantCulture)); + } + saveItemCommand.CommandText += ")"; - var hasSeries = item as IHasSeries; - if (hasSeries != null) - { - _saveItemCommand.GetParameter(index++).Value = hasSeries.SeriesName; - } - else - { - _saveItemCommand.GetParameter(index++).Value = null; + foreach (var item in items) + { + cancellationToken.ThrowIfCancellationRequested(); + + var index = 0; + + saveItemCommand.GetParameter(index++).Value = item.Id; + saveItemCommand.GetParameter(index++).Value = item.GetType().FullName; + saveItemCommand.GetParameter(index++).Value = _jsonSerializer.SerializeToBytes(item); + + saveItemCommand.GetParameter(index++).Value = item.Path; + + var hasStartDate = item as IHasStartDate; + if (hasStartDate != null) + { + saveItemCommand.GetParameter(index++).Value = hasStartDate.StartDate; + } + else + { + saveItemCommand.GetParameter(index++).Value = null; + } + + saveItemCommand.GetParameter(index++).Value = item.EndDate; + saveItemCommand.GetParameter(index++).Value = item.ChannelId; + + var hasProgramAttributes = item as IHasProgramAttributes; + if (hasProgramAttributes != null) + { + saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsKids; + saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsMovie; + saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsSports; + saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsSeries; + saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsLive; + saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsNews; + saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsPremiere; + saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.EpisodeTitle; + saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsRepeat; + } + else + { + saveItemCommand.GetParameter(index++).Value = null; + saveItemCommand.GetParameter(index++).Value = null; + saveItemCommand.GetParameter(index++).Value = null; + saveItemCommand.GetParameter(index++).Value = null; + saveItemCommand.GetParameter(index++).Value = null; + saveItemCommand.GetParameter(index++).Value = null; + saveItemCommand.GetParameter(index++).Value = null; + saveItemCommand.GetParameter(index++).Value = null; + saveItemCommand.GetParameter(index++).Value = null; + } + + saveItemCommand.GetParameter(index++).Value = item.CommunityRating; + saveItemCommand.GetParameter(index++).Value = item.CustomRating; + + saveItemCommand.GetParameter(index++).Value = item.IndexNumber; + saveItemCommand.GetParameter(index++).Value = item.IsLocked; + + saveItemCommand.GetParameter(index++).Value = item.Name; + saveItemCommand.GetParameter(index++).Value = item.OfficialRating; + + saveItemCommand.GetParameter(index++).Value = item.MediaType; + saveItemCommand.GetParameter(index++).Value = item.Overview; + saveItemCommand.GetParameter(index++).Value = item.ParentIndexNumber; + saveItemCommand.GetParameter(index++).Value = item.PremiereDate; + saveItemCommand.GetParameter(index++).Value = item.ProductionYear; + + if (item.ParentId == Guid.Empty) + { + saveItemCommand.GetParameter(index++).Value = null; + } + else + { + saveItemCommand.GetParameter(index++).Value = item.ParentId; + } + + saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Genres.ToArray()); + saveItemCommand.GetParameter(index++).Value = item.GetParentalRatingValue() ?? 0; + saveItemCommand.GetParameter(index++).Value = item.GetInheritedParentalRatingValue() ?? 0; + + saveItemCommand.GetParameter(index++).Value = LatestSchemaVersion; + saveItemCommand.GetParameter(index++).Value = item.SortName; + saveItemCommand.GetParameter(index++).Value = item.RunTimeTicks; + + saveItemCommand.GetParameter(index++).Value = item.OfficialRatingDescription; + saveItemCommand.GetParameter(index++).Value = item.HomePageUrl; + saveItemCommand.GetParameter(index++).Value = item.VoteCount; + saveItemCommand.GetParameter(index++).Value = item.DisplayMediaType; + saveItemCommand.GetParameter(index++).Value = item.DateCreated; + saveItemCommand.GetParameter(index++).Value = item.DateModified; + + saveItemCommand.GetParameter(index++).Value = item.ForcedSortName; + saveItemCommand.GetParameter(index++).Value = item.IsOffline; + saveItemCommand.GetParameter(index++).Value = item.LocationType.ToString(); + + saveItemCommand.GetParameter(index++).Value = item.PreferredMetadataLanguage; + saveItemCommand.GetParameter(index++).Value = item.PreferredMetadataCountryCode; + saveItemCommand.GetParameter(index++).Value = item.IsHD; + saveItemCommand.GetParameter(index++).Value = item.ExternalEtag; + + if (item.DateLastRefreshed == default(DateTime)) + { + saveItemCommand.GetParameter(index++).Value = null; + } + else + { + saveItemCommand.GetParameter(index++).Value = item.DateLastRefreshed; + } + + if (item.DateLastSaved == default(DateTime)) + { + saveItemCommand.GetParameter(index++).Value = null; + } + else + { + saveItemCommand.GetParameter(index++).Value = item.DateLastSaved; + } + + saveItemCommand.GetParameter(index++).Value = item.IsInMixedFolder; + saveItemCommand.GetParameter(index++).Value = string.Join("|", item.LockedFields.Select(i => i.ToString()).ToArray()); + saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Studios.ToArray()); + + if (item.Audio.HasValue) + { + saveItemCommand.GetParameter(index++).Value = item.Audio.Value.ToString(); + } + else + { + saveItemCommand.GetParameter(index++).Value = null; + } + + saveItemCommand.GetParameter(index++).Value = item.ServiceName; + + if (item.Tags.Count > 0) + { + saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Tags.ToArray()); + } + else + { + saveItemCommand.GetParameter(index++).Value = null; + } + + saveItemCommand.GetParameter(index++).Value = item.IsFolder; + + saveItemCommand.GetParameter(index++).Value = item.GetBlockUnratedType().ToString(); + + var topParent = item.GetTopParent(); + if (topParent != null) + { + //Logger.Debug("Item {0} has top parent {1}", item.Id, topParent.Id); + saveItemCommand.GetParameter(index++).Value = topParent.Id.ToString("N"); + } + else + { + //Logger.Debug("Item {0} has null top parent", item.Id); + saveItemCommand.GetParameter(index++).Value = null; + } + + var isByName = false; + var byName = item as IItemByName; + if (byName != null) + { + var dualAccess = item as IHasDualAccess; + isByName = dualAccess == null || dualAccess.IsAccessedByName; + } + saveItemCommand.GetParameter(index++).Value = isByName; + + saveItemCommand.GetParameter(index++).Value = item.SourceType.ToString(); + + var trailer = item as Trailer; + if (trailer != null && trailer.TrailerTypes.Count > 0) + { + saveItemCommand.GetParameter(index++).Value = string.Join("|", trailer.TrailerTypes.Select(i => i.ToString()).ToArray()); + } + else + { + saveItemCommand.GetParameter(index++).Value = null; + } + + saveItemCommand.GetParameter(index++).Value = item.CriticRating; + saveItemCommand.GetParameter(index++).Value = item.CriticRatingSummary; + + if (!item.DateModifiedDuringLastRefresh.HasValue || item.DateModifiedDuringLastRefresh.Value == default(DateTime)) + { + saveItemCommand.GetParameter(index++).Value = null; + } + else + { + saveItemCommand.GetParameter(index++).Value = item.DateModifiedDuringLastRefresh.Value; + } + + var inheritedTags = item.GetInheritedTags(); + if (inheritedTags.Count > 0) + { + saveItemCommand.GetParameter(index++).Value = string.Join("|", inheritedTags.ToArray()); + } + else + { + saveItemCommand.GetParameter(index++).Value = null; + } + + if (string.IsNullOrWhiteSpace(item.Name)) + { + saveItemCommand.GetParameter(index++).Value = null; + } + else + { + saveItemCommand.GetParameter(index++).Value = item.Name.RemoveDiacritics(); + } + + saveItemCommand.GetParameter(index++).Value = item.PresentationUniqueKey; + saveItemCommand.GetParameter(index++).Value = item.SlugName; + saveItemCommand.GetParameter(index++).Value = item.OriginalTitle; + + var video = item as Video; + if (video != null) + { + saveItemCommand.GetParameter(index++).Value = video.PrimaryVersionId; + } + else + { + saveItemCommand.GetParameter(index++).Value = null; + } + + var folder = item as Folder; + if (folder != null && folder.DateLastMediaAdded.HasValue) + { + saveItemCommand.GetParameter(index++).Value = folder.DateLastMediaAdded.Value; + } + else + { + saveItemCommand.GetParameter(index++).Value = null; + } + + saveItemCommand.GetParameter(index++).Value = item.Album; + + var season = item as Season; + if (season != null && season.IsVirtualItem.HasValue) + { + saveItemCommand.GetParameter(index++).Value = season.IsVirtualItem.Value; + } + else + { + saveItemCommand.GetParameter(index++).Value = null; + } + + var hasSeries = item as IHasSeries; + if (hasSeries != null) + { + saveItemCommand.GetParameter(index++).Value = hasSeries.SeriesName; + } + else + { + saveItemCommand.GetParameter(index++).Value = null; + } + + saveItemCommand.Transaction = transaction; + + saveItemCommand.ExecuteNonQuery(); + + if (item.SupportsAncestors) + { + UpdateAncestors(item.Id, item.GetAncestorIds().Distinct().ToList(), connection, transaction); + } + + UpdateUserDataKeys(item.Id, item.GetUserDataKeys().Distinct(StringComparer.OrdinalIgnoreCase).ToList(), connection, transaction); + UpdateImages(item.Id, item.ImageInfos, connection, transaction); + UpdateProviderIds(item.Id, item.ProviderIds, connection, transaction); + UpdateItemValues(item.Id, GetItemValues(item), connection, transaction); + } } - _saveItemCommand.Transaction = transaction; - - _saveItemCommand.ExecuteNonQuery(); - - if (item.SupportsAncestors) + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) { - UpdateAncestors(item.Id, item.GetAncestorIds().Distinct().ToList(), transaction); + transaction.Rollback(); } - UpdateUserDataKeys(item.Id, item.GetUserDataKeys().Distinct(StringComparer.OrdinalIgnoreCase).ToList(), transaction); - UpdateImages(item.Id, item.ImageInfos, transaction); - UpdateProviderIds(item.Id, item.ProviderIds, transaction); - UpdateItemValues(item.Id, GetItemValues(item), transaction); + throw; } - - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) + catch (Exception e) { - transaction.Rollback(); - } + Logger.ErrorException("Failed to save items:", e); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save items:", e); + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); + throw; } - - throw; - } - finally - { - if (transaction != null) + finally { - transaction.Dispose(); + if (transaction != null) + { + transaction.Dispose(); + } } - - WriteLock.Release(); } } @@ -953,19 +811,22 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid"; - cmd.Parameters.Add(cmd, "@guid", DbType.Guid).Value = id; - - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) + using (var cmd = connection.CreateCommand()) { - if (reader.Read()) + cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid"; + cmd.Parameters.Add(cmd, "@guid", DbType.Guid).Value = id; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) { - return GetItem(reader); + if (reader.Read()) + { + return GetItem(reader); + } } + return null; } - return null; } } @@ -1371,22 +1232,25 @@ namespace MediaBrowser.Server.Implementations.Persistence } var list = new List(); - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"; - cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = id; + cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = id; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) - { - while (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { - list.Add(GetChapter(reader)); + while (reader.Read()) + { + list.Add(GetChapter(reader)); + } } } - } - return list; + return list; + } } /// @@ -1404,21 +1268,24 @@ namespace MediaBrowser.Server.Implementations.Persistence throw new ArgumentNullException("id"); } - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex"; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "select StartPositionTicks,Name,ImagePath 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; + cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = id; + cmd.Parameters.Add(cmd, "@ChapterIndex", DbType.Int32).Value = index; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) - { - if (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) { - return GetChapter(reader); + if (reader.Read()) + { + return GetChapter(reader); + } } + return null; } - return null; } } @@ -1447,6 +1314,21 @@ namespace MediaBrowser.Server.Implementations.Persistence return chapter; } + private void DeleteChapters(IDbConnection connection, IDbTransaction transaction, Guid id) + { + using (var deleteChaptersCommand = connection.CreateCommand()) + { + deleteChaptersCommand.CommandText = "delete from " + ChaptersTableName + " where ItemId=@ItemId"; + deleteChaptersCommand.Parameters.Add(deleteChaptersCommand, "@ItemId"); + + deleteChaptersCommand.GetParameter(0).Value = id; + + deleteChaptersCommand.Transaction = transaction; + + deleteChaptersCommand.ExecuteNonQuery(); + } + } + /// /// Saves the chapters. /// @@ -1461,7 +1343,7 @@ namespace MediaBrowser.Server.Implementations.Persistence /// or /// cancellationToken /// - public async Task SaveChapters(Guid id, IEnumerable chapters, CancellationToken cancellationToken) + public async Task SaveChapters(Guid id, List chapters, CancellationToken cancellationToken) { CheckDisposed(); @@ -1477,90 +1359,89 @@ namespace MediaBrowser.Server.Implementations.Persistence cancellationToken.ThrowIfCancellationRequested(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); - - // First delete chapters - _deleteChaptersCommand.GetParameter(0).Value = id; + IDbTransaction transaction = null; - _deleteChaptersCommand.Transaction = transaction; - - _deleteChaptersCommand.ExecuteNonQuery(); - - var index = 0; - - foreach (var chapter in chapters) + try { - cancellationToken.ThrowIfCancellationRequested(); + transaction = connection.BeginTransaction(); - _saveChapterCommand.GetParameter(0).Value = id; - _saveChapterCommand.GetParameter(1).Value = index; - _saveChapterCommand.GetParameter(2).Value = chapter.StartPositionTicks; - _saveChapterCommand.GetParameter(3).Value = chapter.Name; - _saveChapterCommand.GetParameter(4).Value = chapter.ImagePath; + // First delete chapters + DeleteChapters(connection, transaction, id); - _saveChapterCommand.Transaction = transaction; + var index = 0; - _saveChapterCommand.ExecuteNonQuery(); + if (chapters.Count > 0) + { + using (var saveChapterCommand = connection.CreateCommand()) + { + saveChapterCommand.CommandText = "replace into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath) values (@ItemId, @ChapterIndex, @StartPositionTicks, @Name, @ImagePath)"; + saveChapterCommand.Parameters.Add(saveChapterCommand, "@ItemId"); + saveChapterCommand.Parameters.Add(saveChapterCommand, "@ChapterIndex"); + saveChapterCommand.Parameters.Add(saveChapterCommand, "@StartPositionTicks"); + saveChapterCommand.Parameters.Add(saveChapterCommand, "@Name"); + saveChapterCommand.Parameters.Add(saveChapterCommand, "@ImagePath"); + + if (chapters.Count > 1) + { + saveChapterCommand.Prepare(); + } + + foreach (var chapter in chapters) + { + cancellationToken.ThrowIfCancellationRequested(); + + saveChapterCommand.GetParameter(0).Value = id; + saveChapterCommand.GetParameter(1).Value = index; + saveChapterCommand.GetParameter(2).Value = chapter.StartPositionTicks; + saveChapterCommand.GetParameter(3).Value = chapter.Name; + saveChapterCommand.GetParameter(4).Value = chapter.ImagePath; + + saveChapterCommand.Transaction = transaction; + + saveChapterCommand.ExecuteNonQuery(); + + index++; + } + } + } - index++; + transaction.Commit(); } - - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) + catch (OperationCanceledException) { - transaction.Rollback(); + if (transaction != null) + { + transaction.Rollback(); + } + + throw; } + catch (Exception e) + { + Logger.ErrorException("Failed to save chapters:", e); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save chapters:", e); + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); + throw; } - - throw; - } - finally - { - if (transaction != null) + finally { - transaction.Dispose(); + if (transaction != null) + { + transaction.Dispose(); + } } - - WriteLock.Release(); } } - protected override void CloseConnection() + private bool EnableJoinUserData(InternalItemsQuery query) { - if (_connection != null) - { - if (_connection.IsOpen()) - { - _connection.Close(); - } - - _connection.Dispose(); - _connection = null; - } - } - - private bool EnableJoinUserData(InternalItemsQuery query) - { - if (query.User == null) + if (query.User == null) { return false; } @@ -1628,13 +1509,13 @@ namespace MediaBrowser.Server.Implementations.Persistence if (EnableJoinUserData(query)) { - list.Add("UserDataDb.UserData.UserId"); - list.Add("UserDataDb.UserData.lastPlayedDate"); - list.Add("UserDataDb.UserData.playbackPositionTicks"); - list.Add("UserDataDb.UserData.playcount"); - list.Add("UserDataDb.UserData.isFavorite"); - list.Add("UserDataDb.UserData.played"); - list.Add("UserDataDb.UserData.rating"); + list.Add("UserData.UserId"); + list.Add("UserData.lastPlayedDate"); + list.Add("UserData.playbackPositionTicks"); + list.Add("UserData.playcount"); + list.Add("UserData.isFavorite"); + list.Add("UserData.played"); + list.Add("UserData.rating"); } if (query.SimilarTo != null) @@ -1688,7 +1569,7 @@ namespace MediaBrowser.Server.Implementations.Persistence return string.Empty; } - return " left join UserDataDb.UserData on (select UserDataKey from UserDataKeys where ItemId=Guid order by Priority LIMIT 1)=UserDataDb.UserData.Key"; + return " left join UserData on (select UserDataKey from UserDataKeys where ItemId=Guid order by Priority LIMIT 1)=UserData.Key"; } public IEnumerable GetItemList(InternalItemsQuery query) @@ -1702,53 +1583,61 @@ namespace MediaBrowser.Server.Implementations.Persistence var now = DateTime.UtcNow; - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems"; - cmd.CommandText += GetJoinUserDataText(query); - if (EnableJoinUserData(query)) { - cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + AttachUserDataDb(connection); } - var whereClauses = GetWhereClauses(query, cmd); + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems"; + cmd.CommandText += GetJoinUserDataText(query); - var whereText = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } - cmd.CommandText += whereText; + var whereClauses = GetWhereClauses(query, cmd); - if (EnableGroupByPresentationUniqueKey(query)) - { - cmd.CommandText += " Group by PresentationUniqueKey"; - } + var whereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - cmd.CommandText += GetOrderByText(query); + cmd.CommandText += whereText; - if (query.Limit.HasValue || query.StartIndex.HasValue) - { - var limit = query.Limit ?? int.MaxValue; + if (EnableGroupByPresentationUniqueKey(query)) + { + cmd.CommandText += " Group by PresentationUniqueKey"; + } - cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); + cmd.CommandText += GetOrderByText(query); - if (query.StartIndex.HasValue) + if (query.Limit.HasValue || query.StartIndex.HasValue) { - cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); - } - } + var limit = query.Limit ?? int.MaxValue; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) - { - LogQueryTime("GetItemList", cmd, now); + cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); - while (reader.Read()) + if (query.StartIndex.HasValue) + { + cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + } + } + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { - var item = GetItem(reader); - if (item != null) + LogQueryTime("GetItemList", cmd, now); + + while (reader.Read()) { - yield return item; + var item = GetItem(reader); + if (item != null) + { + yield return item; + } } } } @@ -1792,86 +1681,94 @@ namespace MediaBrowser.Server.Implementations.Persistence var now = DateTime.UtcNow; - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems"; - cmd.CommandText += GetJoinUserDataText(query); - if (EnableJoinUserData(query)) { - cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + AttachUserDataDb(connection); } - var whereClauses = GetWhereClauses(query, cmd); + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems"; + cmd.CommandText += GetJoinUserDataText(query); + + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } - var whereTextWithoutPaging = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + var whereClauses = GetWhereClauses(query, cmd); - var whereText = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + var whereTextWithoutPaging = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - cmd.CommandText += whereText; + var whereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - if (EnableGroupByPresentationUniqueKey(query)) - { - cmd.CommandText += " Group by PresentationUniqueKey"; - } + cmd.CommandText += whereText; + + if (EnableGroupByPresentationUniqueKey(query)) + { + cmd.CommandText += " Group by PresentationUniqueKey"; + } - cmd.CommandText += GetOrderByText(query); + cmd.CommandText += GetOrderByText(query); - if (query.Limit.HasValue || query.StartIndex.HasValue) - { - var limit = query.Limit ?? int.MaxValue; + if (query.Limit.HasValue || query.StartIndex.HasValue) + { + var limit = query.Limit ?? int.MaxValue; + + cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); - cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); + if (query.StartIndex.HasValue) + { + cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + } + } - if (query.StartIndex.HasValue) + if (EnableGroupByPresentationUniqueKey(query)) { - cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + cmd.CommandText += "; select count (distinct PresentationUniqueKey) from TypedBaseItems"; + } + else + { + cmd.CommandText += "; select count (guid) from TypedBaseItems"; } - } - if (EnableGroupByPresentationUniqueKey(query)) - { - cmd.CommandText += "; select count (distinct PresentationUniqueKey) from TypedBaseItems"; - } - else - { - cmd.CommandText += "; select count (guid) from TypedBaseItems"; - } + cmd.CommandText += GetJoinUserDataText(query); + cmd.CommandText += whereTextWithoutPaging; - cmd.CommandText += GetJoinUserDataText(query); - cmd.CommandText += whereTextWithoutPaging; + var list = new List(); + var count = 0; - var list = new List(); - var count = 0; + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) + { + LogQueryTime("GetItems", cmd, now); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) - { - LogQueryTime("GetItems", cmd, now); + while (reader.Read()) + { + var item = GetItem(reader); + if (item != null) + { + list.Add(item); + } + } - while (reader.Read()) - { - var item = GetItem(reader); - if (item != null) + if (reader.NextResult() && reader.Read()) { - list.Add(item); + count = reader.GetInt32(0); } } - if (reader.NextResult() && reader.Read()) + return new QueryResult() { - count = reader.GetInt32(0); - } + Items = list.ToArray(), + TotalRecordCount = count + }; } - - return new QueryResult() - { - Items = list.ToArray(), - TotalRecordCount = count - }; } } @@ -1989,56 +1886,64 @@ namespace MediaBrowser.Server.Implementations.Persistence var now = DateTime.UtcNow; - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems"; - cmd.CommandText += GetJoinUserDataText(query); - if (EnableJoinUserData(query)) { - cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + AttachUserDataDb(connection); } - var whereClauses = GetWhereClauses(query, cmd); + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems"; + cmd.CommandText += GetJoinUserDataText(query); - var whereText = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } - cmd.CommandText += whereText; + var whereClauses = GetWhereClauses(query, cmd); - if (EnableGroupByPresentationUniqueKey(query)) - { - cmd.CommandText += " Group by PresentationUniqueKey"; - } + var whereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - cmd.CommandText += GetOrderByText(query); + cmd.CommandText += whereText; - if (query.Limit.HasValue || query.StartIndex.HasValue) - { - var limit = query.Limit ?? int.MaxValue; + if (EnableGroupByPresentationUniqueKey(query)) + { + cmd.CommandText += " Group by PresentationUniqueKey"; + } - cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); + cmd.CommandText += GetOrderByText(query); - if (query.StartIndex.HasValue) + if (query.Limit.HasValue || query.StartIndex.HasValue) { - cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); - } - } + var limit = query.Limit ?? int.MaxValue; - var list = new List(); + cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) - { - LogQueryTime("GetItemIdsList", cmd, now); + if (query.StartIndex.HasValue) + { + cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + } + } + + var list = new List(); - while (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { - list.Add(reader.GetGuid(0)); + LogQueryTime("GetItemIdsList", cmd, now); + + while (reader.Read()) + { + list.Add(reader.GetGuid(0)); + } } - } - return list; + return list; + } } } @@ -2051,73 +1956,76 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "select guid,path from TypedBaseItems"; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "select guid,path from TypedBaseItems"; - var whereClauses = GetWhereClauses(query, cmd); + var whereClauses = GetWhereClauses(query, cmd); - 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()); - 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()); - cmd.CommandText += whereText; + cmd.CommandText += whereText; - if (EnableGroupByPresentationUniqueKey(query)) - { - cmd.CommandText += " Group by PresentationUniqueKey"; - } + if (EnableGroupByPresentationUniqueKey(query)) + { + cmd.CommandText += " Group by PresentationUniqueKey"; + } - cmd.CommandText += GetOrderByText(query); + cmd.CommandText += GetOrderByText(query); - if (query.Limit.HasValue || query.StartIndex.HasValue) - { - var limit = query.Limit ?? int.MaxValue; + if (query.Limit.HasValue || query.StartIndex.HasValue) + { + var limit = query.Limit ?? int.MaxValue; - cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); + cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); - if (query.StartIndex.HasValue) - { - cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + if (query.StartIndex.HasValue) + { + cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + } } - } - cmd.CommandText += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging; + cmd.CommandText += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging; - var list = new List>(); - var count = 0; + var list = new List>(); + var count = 0; - Logger.Debug(cmd.CommandText); + Logger.Debug(cmd.CommandText); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) - { - while (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { - var id = reader.GetGuid(0); - string path = null; + while (reader.Read()) + { + var id = reader.GetGuid(0); + string path = null; + + if (!reader.IsDBNull(1)) + { + path = reader.GetString(1); + } + list.Add(new Tuple(id, path)); + } - if (!reader.IsDBNull(1)) + if (reader.NextResult() && reader.Read()) { - path = reader.GetString(1); + count = reader.GetInt32(0); } - list.Add(new Tuple(id, path)); } - if (reader.NextResult() && reader.Read()) + return new QueryResult>() { - count = reader.GetInt32(0); - } + Items = list.ToArray(), + TotalRecordCount = count + }; } - - return new QueryResult>() - { - Items = list.ToArray(), - TotalRecordCount = count - }; } } @@ -2132,78 +2040,86 @@ namespace MediaBrowser.Server.Implementations.Persistence var now = DateTime.UtcNow; - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems"; - - var whereClauses = GetWhereClauses(query, cmd); - cmd.CommandText += GetJoinUserDataText(query); - if (EnableJoinUserData(query)) { - cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + AttachUserDataDb(connection); } - var whereText = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); - - cmd.CommandText += whereText; - - if (EnableGroupByPresentationUniqueKey(query)) + using (var cmd = connection.CreateCommand()) { - cmd.CommandText += " Group by PresentationUniqueKey"; - } + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems"; - cmd.CommandText += GetOrderByText(query); + var whereClauses = GetWhereClauses(query, cmd); + cmd.CommandText += GetJoinUserDataText(query); - if (query.Limit.HasValue || query.StartIndex.HasValue) - { - var limit = query.Limit ?? int.MaxValue; + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } + + var whereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); + cmd.CommandText += whereText; - if (query.StartIndex.HasValue) + if (EnableGroupByPresentationUniqueKey(query)) { - cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + cmd.CommandText += " Group by PresentationUniqueKey"; } - } - if (EnableGroupByPresentationUniqueKey(query)) - { - cmd.CommandText += "; select count (distinct PresentationUniqueKey) from TypedBaseItems"; - } - else - { - cmd.CommandText += "; select count (guid) from TypedBaseItems"; - } + cmd.CommandText += GetOrderByText(query); - cmd.CommandText += GetJoinUserDataText(query); - cmd.CommandText += whereText; + if (query.Limit.HasValue || query.StartIndex.HasValue) + { + var limit = query.Limit ?? int.MaxValue; - var list = new List(); - var count = 0; + cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) - { - LogQueryTime("GetItemIds", cmd, now); + if (query.StartIndex.HasValue) + { + cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + } + } - while (reader.Read()) + if (EnableGroupByPresentationUniqueKey(query)) + { + cmd.CommandText += "; select count (distinct PresentationUniqueKey) from TypedBaseItems"; + } + else { - list.Add(reader.GetGuid(0)); + cmd.CommandText += "; select count (guid) from TypedBaseItems"; } - if (reader.NextResult() && reader.Read()) + cmd.CommandText += GetJoinUserDataText(query); + cmd.CommandText += whereText; + + var list = new List(); + var count = 0; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { - count = reader.GetInt32(0); + LogQueryTime("GetItemIds", cmd, now); + + while (reader.Read()) + { + list.Add(reader.GetGuid(0)); + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } } - } - return new QueryResult() - { - Items = list.ToArray(), - TotalRecordCount = count - }; + return new QueryResult() + { + Items = list.ToArray(), + TotalRecordCount = count + }; + } } } @@ -2987,77 +2903,88 @@ namespace MediaBrowser.Server.Implementations.Persistence private async Task UpdateInheritedTags(CancellationToken cancellationToken) { - var newValues = new List>(); - - using (var cmd = _connection.CreateCommand()) + using (var connection = await CreateConnection().ConfigureAwait(false)) { - cmd.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"; + var newValues = new List>(); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + using (var cmd = connection.CreateCommand()) { - while (reader.Read()) + cmd.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 reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { - var id = reader.GetGuid(0); - string value = reader.IsDBNull(2) ? null : reader.GetString(2); + while (reader.Read()) + { + var id = reader.GetGuid(0); + string value = reader.IsDBNull(2) ? null : reader.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; + } - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); + IDbTransaction transaction = null; - IDbTransaction transaction = null; + try + { + transaction = connection.BeginTransaction(); - try - { - transaction = _connection.BeginTransaction(); + using (var updateInheritedTagsCommand = connection.CreateCommand()) + { + updateInheritedTagsCommand.CommandText = "Update TypedBaseItems set InheritedTags=@InheritedTags where Guid=@Guid"; + updateInheritedTagsCommand.Parameters.Add(updateInheritedTagsCommand, "@Guid"); + updateInheritedTagsCommand.Parameters.Add(updateInheritedTagsCommand, "@InheritedTags"); - foreach (var item in newValues) - { - _updateInheritedTagsCommand.GetParameter(0).Value = item.Item1; - _updateInheritedTagsCommand.GetParameter(1).Value = item.Item2; + if (newValues.Count > 1) + { + updateInheritedTagsCommand.Prepare(); + } - _updateInheritedTagsCommand.Transaction = transaction; - _updateInheritedTagsCommand.ExecuteNonQuery(); - } + foreach (var item in newValues) + { + updateInheritedTagsCommand.GetParameter(0).Value = item.Item1; + updateInheritedTagsCommand.GetParameter(1).Value = item.Item2; - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) + updateInheritedTagsCommand.Transaction = transaction; + updateInheritedTagsCommand.ExecuteNonQuery(); + } + } + + transaction.Commit(); + } + catch (OperationCanceledException) { - transaction.Rollback(); + if (transaction != null) + { + transaction.Rollback(); + } + + throw; } + catch (Exception e) + { + Logger.ErrorException("Error running query:", e); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Error running query:", e); + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); + throw; } - - throw; - } - finally - { - if (transaction != null) + finally { - transaction.Dispose(); + if (transaction != null) + { + transaction.Dispose(); + } } - - WriteLock.Release(); } } @@ -3065,75 +2992,82 @@ namespace MediaBrowser.Server.Implementations.Persistence { var newValues = new List>(); - using (var cmd = _connection.CreateCommand()) + using (var connection = await CreateConnection().ConfigureAwait(false)) { - cmd.CommandText = "select Guid,InheritedParentalRatingValue,(select Max(ParentalRatingValue, (select COALESCE(MAX(ParentalRatingValue),0) from TypedBaseItems where guid in (Select AncestorId from AncestorIds where ItemId=Outer.guid)))) as NewInheritedParentalRatingValue from typedbaseitems as Outer where InheritedParentalRatingValue <> NewInheritedParentalRatingValue"; - - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + using (var cmd = connection.CreateCommand()) { - while (reader.Read()) + cmd.CommandText = "select Guid,InheritedParentalRatingValue,(select Max(ParentalRatingValue, (select COALESCE(MAX(ParentalRatingValue),0) from TypedBaseItems where guid in (Select AncestorId from AncestorIds where ItemId=Outer.guid)))) as NewInheritedParentalRatingValue from typedbaseitems as Outer where InheritedParentalRatingValue <> NewInheritedParentalRatingValue"; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { - var id = reader.GetGuid(0); - var newValue = reader.GetInt32(2); + while (reader.Read()) + { + var id = reader.GetGuid(0); + var newValue = reader.GetInt32(2); - newValues.Add(new Tuple(id, newValue)); + newValues.Add(new Tuple(id, newValue)); + } } } - } - Logger.Debug("UpdateInheritedParentalRatings - {0} rows", newValues.Count); - if (newValues.Count == 0) - { - return; - } + Logger.Debug("UpdateInheritedParentalRatings - {0} rows", newValues.Count); + if (newValues.Count == 0) + { + return; + } - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); + using (var updateInheritedRatingCommand = connection.CreateCommand()) + { + updateInheritedRatingCommand.CommandText = "Update TypedBaseItems set InheritedParentalRatingValue=@InheritedParentalRatingValue where Guid=@Guid"; + updateInheritedRatingCommand.Parameters.Add(updateInheritedRatingCommand, "@Guid"); + updateInheritedRatingCommand.Parameters.Add(updateInheritedRatingCommand, "@InheritedParentalRatingValue"); + updateInheritedRatingCommand.Prepare(); - IDbTransaction transaction = null; + IDbTransaction transaction = null; - try - { - transaction = _connection.BeginTransaction(); + try + { + transaction = connection.BeginTransaction(); - foreach (var item in newValues) - { - _updateInheritedRatingCommand.GetParameter(0).Value = item.Item1; - _updateInheritedRatingCommand.GetParameter(1).Value = item.Item2; + foreach (var item in newValues) + { + updateInheritedRatingCommand.GetParameter(0).Value = item.Item1; + updateInheritedRatingCommand.GetParameter(1).Value = item.Item2; - _updateInheritedRatingCommand.Transaction = transaction; - _updateInheritedRatingCommand.ExecuteNonQuery(); - } + updateInheritedRatingCommand.Transaction = transaction; + updateInheritedRatingCommand.ExecuteNonQuery(); + } - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } - throw; - } - catch (Exception e) - { - Logger.ErrorException("Error running query:", e); + throw; + } + catch (Exception e) + { + Logger.ErrorException("Error running query:", e); - if (transaction != null) - { - transaction.Rollback(); - } + if (transaction != null) + { + transaction.Rollback(); + } - throw; - } - finally - { - if (transaction != null) - { - transaction.Dispose(); + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + } } - - WriteLock.Release(); } } @@ -3176,89 +3110,73 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); + IDbTransaction transaction = null; - // Delete people - _deletePeopleCommand.GetParameter(0).Value = id; - _deletePeopleCommand.Transaction = transaction; - _deletePeopleCommand.ExecuteNonQuery(); + try + { + transaction = connection.BeginTransaction(); - // Delete chapters - _deleteChaptersCommand.GetParameter(0).Value = id; - _deleteChaptersCommand.Transaction = transaction; - _deleteChaptersCommand.ExecuteNonQuery(); + // Delete people + DeletePeople(connection, transaction, id); + DeleteChapters(connection, transaction, id); + DeleteMediaStreams(connection, transaction, id); - // Delete media streams - _deleteStreamsCommand.GetParameter(0).Value = id; - _deleteStreamsCommand.Transaction = transaction; - _deleteStreamsCommand.ExecuteNonQuery(); + // Delete ancestors + DeleteAncestors(connection, transaction, id); - // Delete ancestors - _deleteAncestorsCommand.GetParameter(0).Value = id; - _deleteAncestorsCommand.Transaction = transaction; - _deleteAncestorsCommand.ExecuteNonQuery(); + // Delete user data keys + DeleteUserDataKeys(connection, transaction, id); - // Delete user data keys - _deleteUserDataKeysCommand.GetParameter(0).Value = id; - _deleteUserDataKeysCommand.Transaction = transaction; - _deleteUserDataKeysCommand.ExecuteNonQuery(); + // Delete item values + DeleteItemValues(connection, transaction, id); - // Delete item values - _deleteItemValuesCommand.GetParameter(0).Value = id; - _deleteItemValuesCommand.Transaction = transaction; - _deleteItemValuesCommand.ExecuteNonQuery(); + // Delete provider ids + DeleteProviderIds(connection, transaction, id); - // Delete provider ids - _deleteProviderIdsCommand.GetParameter(0).Value = id; - _deleteProviderIdsCommand.Transaction = transaction; - _deleteProviderIdsCommand.ExecuteNonQuery(); + DeleteImages(connection, transaction, id); - // Delete images - _deleteImagesCommand.GetParameter(0).Value = id; - _deleteImagesCommand.Transaction = transaction; - _deleteImagesCommand.ExecuteNonQuery(); + // Delete the item + using (var deleteItemCommand = connection.CreateCommand()) + { + deleteItemCommand.CommandText = "delete from TypedBaseItems where guid=@Id"; + deleteItemCommand.Parameters.Add(deleteItemCommand, "@Id"); - // Delete the item - _deleteItemCommand.GetParameter(0).Value = id; - _deleteItemCommand.Transaction = transaction; - _deleteItemCommand.ExecuteNonQuery(); + deleteItemCommand.GetParameter(0).Value = id; + deleteItemCommand.Transaction = transaction; + deleteItemCommand.ExecuteNonQuery(); + } - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) + transaction.Commit(); + } + catch (OperationCanceledException) { - transaction.Rollback(); + if (transaction != null) + { + transaction.Rollback(); + } + + throw; } + catch (Exception e) + { + Logger.ErrorException("Failed to save children:", e); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save children:", e); + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); + throw; } - - throw; - } - finally - { - if (transaction != null) + finally { - transaction.Dispose(); + if (transaction != null) + { + transaction.Dispose(); + } } - - WriteLock.Release(); } } @@ -3271,30 +3189,33 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "select Distinct Name from People"; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "select Distinct Name from People"; - var whereClauses = GetPeopleWhereClauses(query, cmd); + var whereClauses = GetPeopleWhereClauses(query, cmd); - if (whereClauses.Count > 0) - { - cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray()); - } + if (whereClauses.Count > 0) + { + cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray()); + } - cmd.CommandText += " order by ListOrder"; + cmd.CommandText += " order by ListOrder"; - var list = new List(); + var list = new List(); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) - { - while (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { - list.Add(reader.GetString(0)); + while (reader.Read()) + { + list.Add(reader.GetString(0)); + } } - } - return list; + return list; + } } } @@ -3307,30 +3228,33 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "select ItemId, Name, Role, PersonType, SortOrder from People"; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "select ItemId, Name, Role, PersonType, SortOrder from People"; - var whereClauses = GetPeopleWhereClauses(query, cmd); + var whereClauses = GetPeopleWhereClauses(query, cmd); - if (whereClauses.Count > 0) - { - cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray()); - } + if (whereClauses.Count > 0) + { + cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray()); + } - cmd.CommandText += " order by ListOrder"; + cmd.CommandText += " order by ListOrder"; - var list = new List(); + var list = new List(); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) - { - while (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { - list.Add(GetPerson(reader)); + while (reader.Read()) + { + list.Add(GetPerson(reader)); + } } - } - return list; + return list; + } } } @@ -3384,7 +3308,21 @@ namespace MediaBrowser.Server.Implementations.Persistence return whereClauses; } - private void UpdateAncestors(Guid itemId, List ancestorIds, IDbTransaction transaction) + private void DeleteAncestors(IDbConnection connection, IDbTransaction transaction, Guid id) + { + using (var deleteAncestorsCommand = connection.CreateCommand()) + { + deleteAncestorsCommand.CommandText = "delete from AncestorIds where ItemId=@Id"; + deleteAncestorsCommand.Parameters.Add(deleteAncestorsCommand, "@Id"); + + deleteAncestorsCommand.GetParameter(0).Value = id; + deleteAncestorsCommand.Transaction = transaction; + + deleteAncestorsCommand.ExecuteNonQuery(); + } + } + + private void UpdateAncestors(Guid itemId, List ancestorIds, IDbConnection connection, IDbTransaction transaction) { if (itemId == Guid.Empty) { @@ -3399,20 +3337,33 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); // First delete - _deleteAncestorsCommand.GetParameter(0).Value = itemId; - _deleteAncestorsCommand.Transaction = transaction; + DeleteAncestors(connection, transaction, itemId); - _deleteAncestorsCommand.ExecuteNonQuery(); - - foreach (var ancestorId in ancestorIds) + if (ancestorIds.Count > 0) { - _saveAncestorCommand.GetParameter(0).Value = itemId; - _saveAncestorCommand.GetParameter(1).Value = ancestorId; - _saveAncestorCommand.GetParameter(2).Value = ancestorId.ToString("N"); + using (var saveAncestorCommand = connection.CreateCommand()) + { + saveAncestorCommand.CommandText = "insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values (@ItemId, @AncestorId, @AncestorIdText)"; + saveAncestorCommand.Parameters.Add(saveAncestorCommand, "@ItemId"); + saveAncestorCommand.Parameters.Add(saveAncestorCommand, "@AncestorId"); + saveAncestorCommand.Parameters.Add(saveAncestorCommand, "@AncestorIdText"); + + if (ancestorIds.Count > 1) + { + saveAncestorCommand.Prepare(); + } + + foreach (var ancestorId in ancestorIds) + { + saveAncestorCommand.GetParameter(0).Value = itemId; + saveAncestorCommand.GetParameter(1).Value = ancestorId; + saveAncestorCommand.GetParameter(2).Value = ancestorId.ToString("N"); - _saveAncestorCommand.Transaction = transaction; + saveAncestorCommand.Transaction = transaction; - _saveAncestorCommand.ExecuteNonQuery(); + saveAncestorCommand.ExecuteNonQuery(); + } + } } } @@ -3440,7 +3391,20 @@ namespace MediaBrowser.Server.Implementations.Persistence return list; } - private void UpdateImages(Guid itemId, List images, IDbTransaction transaction) + private void DeleteImages(IDbConnection connection, IDbTransaction transaction, Guid id) + { + using (var deleteImagesCommand = connection.CreateCommand()) + { + deleteImagesCommand.CommandText = "delete from Images where ItemId=@Id"; + deleteImagesCommand.Parameters.Add(deleteImagesCommand, "@Id"); + + deleteImagesCommand.GetParameter(0).Value = id; + deleteImagesCommand.Transaction = transaction; + deleteImagesCommand.ExecuteNonQuery(); + } + } + + private void UpdateImages(Guid itemId, List images, IDbConnection connection, IDbTransaction transaction) { if (itemId == Guid.Empty) { @@ -3455,38 +3419,70 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); // First delete - _deleteImagesCommand.GetParameter(0).Value = itemId; - _deleteImagesCommand.Transaction = transaction; - - _deleteImagesCommand.ExecuteNonQuery(); + DeleteImages(connection, transaction, itemId); - var index = 0; - foreach (var image in images) + if (images.Count > 0) { - _saveImagesCommand.GetParameter(0).Value = itemId; - _saveImagesCommand.GetParameter(1).Value = image.Type; - _saveImagesCommand.GetParameter(2).Value = image.Path; - - if (image.DateModified == default(DateTime)) - { - _saveImagesCommand.GetParameter(3).Value = null; - } - else + using (var saveImagesCommand = connection.CreateCommand()) { - _saveImagesCommand.GetParameter(3).Value = image.DateModified; + var index = 0; + + saveImagesCommand.CommandText = "insert into Images (ItemId, ImageType, Path, DateModified, IsPlaceHolder, SortOrder) values (@ItemId, @ImageType, @Path, @DateModified, @IsPlaceHolder, @SortOrder)"; + saveImagesCommand.Parameters.Add(saveImagesCommand, "@ItemId"); + saveImagesCommand.Parameters.Add(saveImagesCommand, "@ImageType"); + saveImagesCommand.Parameters.Add(saveImagesCommand, "@Path"); + saveImagesCommand.Parameters.Add(saveImagesCommand, "@DateModified"); + saveImagesCommand.Parameters.Add(saveImagesCommand, "@IsPlaceHolder"); + saveImagesCommand.Parameters.Add(saveImagesCommand, "@SortOrder"); + + if (images.Count > 1) + { + saveImagesCommand.Prepare(); + } + + foreach (var image in images) + { + saveImagesCommand.GetParameter(0).Value = itemId; + saveImagesCommand.GetParameter(1).Value = image.Type; + saveImagesCommand.GetParameter(2).Value = image.Path; + + if (image.DateModified == default(DateTime)) + { + saveImagesCommand.GetParameter(3).Value = null; + } + else + { + saveImagesCommand.GetParameter(3).Value = image.DateModified; + } + + saveImagesCommand.GetParameter(4).Value = image.IsPlaceholder; + saveImagesCommand.GetParameter(5).Value = index; + + saveImagesCommand.Transaction = transaction; + + saveImagesCommand.ExecuteNonQuery(); + index++; + } } + } + } - _saveImagesCommand.GetParameter(4).Value = image.IsPlaceholder; - _saveImagesCommand.GetParameter(5).Value = index; + private void DeleteProviderIds(IDbConnection connection, IDbTransaction transaction, Guid itemId) + { + using (var deleteProviderIdsCommand = connection.CreateCommand()) + { + // provider ids + deleteProviderIdsCommand.CommandText = "delete from ProviderIds where ItemId=@Id"; + deleteProviderIdsCommand.Parameters.Add(deleteProviderIdsCommand, "@Id"); - _saveImagesCommand.Transaction = transaction; + deleteProviderIdsCommand.GetParameter(0).Value = itemId; + deleteProviderIdsCommand.Transaction = transaction; - _saveImagesCommand.ExecuteNonQuery(); - index++; + deleteProviderIdsCommand.ExecuteNonQuery(); } } - private void UpdateProviderIds(Guid itemId, Dictionary values, IDbTransaction transaction) + private void UpdateProviderIds(Guid itemId, Dictionary values, IDbConnection connection, IDbTransaction transaction) { if (itemId == Guid.Empty) { @@ -3501,23 +3497,51 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); // First delete - _deleteProviderIdsCommand.GetParameter(0).Value = itemId; - _deleteProviderIdsCommand.Transaction = transaction; + DeleteProviderIds(connection, transaction, itemId); + + if (values.Count > 0) + { + using (var saveProviderIdsCommand = connection.CreateCommand()) + { + saveProviderIdsCommand.CommandText = "insert into ProviderIds (ItemId, Name, Value) values (@ItemId, @Name, @Value)"; + saveProviderIdsCommand.Parameters.Add(saveProviderIdsCommand, "@ItemId"); + saveProviderIdsCommand.Parameters.Add(saveProviderIdsCommand, "@Name"); + saveProviderIdsCommand.Parameters.Add(saveProviderIdsCommand, "@Value"); + + if (values.Count > 1) + { + saveProviderIdsCommand.Prepare(); + } + + foreach (var pair in values) + { + saveProviderIdsCommand.GetParameter(0).Value = itemId; + saveProviderIdsCommand.GetParameter(1).Value = pair.Key; + saveProviderIdsCommand.GetParameter(2).Value = pair.Value; + saveProviderIdsCommand.Transaction = transaction; - _deleteProviderIdsCommand.ExecuteNonQuery(); + saveProviderIdsCommand.ExecuteNonQuery(); + } + } + } + } - foreach (var pair in values) + private void DeleteItemValues(IDbConnection connection, IDbTransaction transaction, Guid itemId) + { + using (var deleteItemValuesCommand = connection.CreateCommand()) { - _saveProviderIdsCommand.GetParameter(0).Value = itemId; - _saveProviderIdsCommand.GetParameter(1).Value = pair.Key; - _saveProviderIdsCommand.GetParameter(2).Value = pair.Value; - _saveProviderIdsCommand.Transaction = transaction; + deleteItemValuesCommand.CommandText = "delete from ItemValues where ItemId=@Id"; + deleteItemValuesCommand.Parameters.Add(deleteItemValuesCommand, "@Id"); + + // First delete + deleteItemValuesCommand.GetParameter(0).Value = itemId; + deleteItemValuesCommand.Transaction = transaction; - _saveProviderIdsCommand.ExecuteNonQuery(); + deleteItemValuesCommand.ExecuteNonQuery(); } } - private void UpdateItemValues(Guid itemId, List> values, IDbTransaction transaction) + private void UpdateItemValues(Guid itemId, List> values, IDbConnection connection, IDbTransaction transaction) { if (itemId == Guid.Empty) { @@ -3532,23 +3556,51 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); // First delete - _deleteItemValuesCommand.GetParameter(0).Value = itemId; - _deleteItemValuesCommand.Transaction = transaction; + DeleteItemValues(connection, transaction, itemId); + + if (values.Count > 0) + { + using (var saveItemValuesCommand = connection.CreateCommand()) + { + saveItemValuesCommand.CommandText = "insert into ItemValues (ItemId, Type, Value) values (@ItemId, @Type, @Value)"; + saveItemValuesCommand.Parameters.Add(saveItemValuesCommand, "@ItemId"); + saveItemValuesCommand.Parameters.Add(saveItemValuesCommand, "@Type"); + saveItemValuesCommand.Parameters.Add(saveItemValuesCommand, "@Value"); + + if (values.Count > 1) + { + saveItemValuesCommand.Prepare(); + } + + foreach (var pair in values) + { + saveItemValuesCommand.GetParameter(0).Value = itemId; + saveItemValuesCommand.GetParameter(1).Value = pair.Item1; + saveItemValuesCommand.GetParameter(2).Value = pair.Item2; + saveItemValuesCommand.Transaction = transaction; - _deleteItemValuesCommand.ExecuteNonQuery(); + saveItemValuesCommand.ExecuteNonQuery(); + } + } + } + } - foreach (var pair in values) + private void DeleteUserDataKeys(IDbConnection connection, IDbTransaction transaction, Guid itemId) + { + using (var deleteUserDataKeysCommand = connection.CreateCommand()) { - _saveItemValuesCommand.GetParameter(0).Value = itemId; - _saveItemValuesCommand.GetParameter(1).Value = pair.Item1; - _saveItemValuesCommand.GetParameter(2).Value = pair.Item2; - _saveItemValuesCommand.Transaction = transaction; + // user data + deleteUserDataKeysCommand.CommandText = "delete from UserDataKeys where ItemId=@Id"; + deleteUserDataKeysCommand.Parameters.Add(deleteUserDataKeysCommand, "@Id"); + + deleteUserDataKeysCommand.GetParameter(0).Value = itemId; + deleteUserDataKeysCommand.Transaction = transaction; - _saveItemValuesCommand.ExecuteNonQuery(); + deleteUserDataKeysCommand.ExecuteNonQuery(); } } - private void UpdateUserDataKeys(Guid itemId, List keys, IDbTransaction transaction) + private void UpdateUserDataKeys(Guid itemId, List keys, IDbConnection connection, IDbTransaction transaction) { if (itemId == Guid.Empty) { @@ -3563,21 +3615,50 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); // First delete - _deleteUserDataKeysCommand.GetParameter(0).Value = itemId; - _deleteUserDataKeysCommand.Transaction = transaction; + DeleteUserDataKeys(connection, transaction, itemId); - _deleteUserDataKeysCommand.ExecuteNonQuery(); var index = 0; - foreach (var key in keys) + if (keys.Count > 0) + { + using (var saveUserDataKeysCommand = connection.CreateCommand()) + { + saveUserDataKeysCommand.CommandText = "insert into UserDataKeys (ItemId, UserDataKey, Priority) values (@ItemId, @UserDataKey, @Priority)"; + saveUserDataKeysCommand.Parameters.Add(saveUserDataKeysCommand, "@ItemId"); + saveUserDataKeysCommand.Parameters.Add(saveUserDataKeysCommand, "@UserDataKey"); + saveUserDataKeysCommand.Parameters.Add(saveUserDataKeysCommand, "@Priority"); + + if (keys.Count > 1) + { + saveUserDataKeysCommand.Prepare(); + } + + foreach (var key in keys) + { + saveUserDataKeysCommand.GetParameter(0).Value = itemId; + saveUserDataKeysCommand.GetParameter(1).Value = key; + saveUserDataKeysCommand.GetParameter(2).Value = index; + index++; + saveUserDataKeysCommand.Transaction = transaction; + + saveUserDataKeysCommand.ExecuteNonQuery(); + } + } + } + } + + private void DeletePeople(IDbConnection connection, IDbTransaction transaction, Guid id) + { + using (var deletePeopleCommand = connection.CreateCommand()) { - _saveUserDataKeysCommand.GetParameter(0).Value = itemId; - _saveUserDataKeysCommand.GetParameter(1).Value = key; - _saveUserDataKeysCommand.GetParameter(2).Value = index; - index++; - _saveUserDataKeysCommand.Transaction = transaction; + deletePeopleCommand.CommandText = "delete from People where ItemId=@Id"; + deletePeopleCommand.Parameters.Add(deletePeopleCommand, "@Id"); - _saveUserDataKeysCommand.ExecuteNonQuery(); + + deletePeopleCommand.GetParameter(0).Value = id; + deletePeopleCommand.Transaction = transaction; + + deletePeopleCommand.ExecuteNonQuery(); } } @@ -3597,69 +3678,85 @@ namespace MediaBrowser.Server.Implementations.Persistence var cancellationToken = CancellationToken.None; - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); - - // First delete - _deletePeopleCommand.GetParameter(0).Value = itemId; - _deletePeopleCommand.Transaction = transaction; + IDbTransaction transaction = null; - _deletePeopleCommand.ExecuteNonQuery(); + try + { + transaction = connection.BeginTransaction(); - var listIndex = 0; + // First delete + DeletePeople(connection, transaction, itemId); - foreach (var person in people) - { - cancellationToken.ThrowIfCancellationRequested(); + var listIndex = 0; - _savePersonCommand.GetParameter(0).Value = itemId; - _savePersonCommand.GetParameter(1).Value = person.Name; - _savePersonCommand.GetParameter(2).Value = person.Role; - _savePersonCommand.GetParameter(3).Value = person.Type; - _savePersonCommand.GetParameter(4).Value = person.SortOrder; - _savePersonCommand.GetParameter(5).Value = listIndex; + if (people.Count > 0) + { + using (var savePersonCommand = connection.CreateCommand()) + { + savePersonCommand.CommandText = "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values (@ItemId, @Name, @Role, @PersonType, @SortOrder, @ListOrder)"; + savePersonCommand.Parameters.Add(savePersonCommand, "@ItemId"); + savePersonCommand.Parameters.Add(savePersonCommand, "@Name"); + savePersonCommand.Parameters.Add(savePersonCommand, "@Role"); + savePersonCommand.Parameters.Add(savePersonCommand, "@PersonType"); + savePersonCommand.Parameters.Add(savePersonCommand, "@SortOrder"); + savePersonCommand.Parameters.Add(savePersonCommand, "@ListOrder"); + + if (people.Count > 1) + { + savePersonCommand.Prepare(); + } + + foreach (var person in people) + { + cancellationToken.ThrowIfCancellationRequested(); + + savePersonCommand.GetParameter(0).Value = itemId; + savePersonCommand.GetParameter(1).Value = person.Name; + savePersonCommand.GetParameter(2).Value = person.Role; + savePersonCommand.GetParameter(3).Value = person.Type; + savePersonCommand.GetParameter(4).Value = person.SortOrder; + savePersonCommand.GetParameter(5).Value = listIndex; + + savePersonCommand.Transaction = transaction; + + savePersonCommand.ExecuteNonQuery(); + listIndex++; + } + } + } - _savePersonCommand.Transaction = transaction; + transaction.Commit(); - _savePersonCommand.ExecuteNonQuery(); - listIndex++; } - - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) + catch (OperationCanceledException) { - transaction.Rollback(); + if (transaction != null) + { + transaction.Rollback(); + } + + throw; } + catch (Exception e) + { + Logger.ErrorException("Failed to save people:", e); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save people:", e); + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); + throw; } - - throw; - } - finally - { - if (transaction != null) + finally { - transaction.Dispose(); + if (transaction != null) + { + transaction.Dispose(); + } } - - WriteLock.Release(); } } @@ -3699,42 +3796,61 @@ namespace MediaBrowser.Server.Implementations.Persistence var list = new List(); - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - var cmdText = "select " + string.Join(",", _mediaStreamSaveColumns) + " from mediastreams where"; + using (var cmd = connection.CreateCommand()) + { + var cmdText = "select " + string.Join(",", _mediaStreamSaveColumns) + " from mediastreams where"; - cmdText += " ItemId=@ItemId"; - cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = query.ItemId; + cmdText += " ItemId=@ItemId"; + cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = query.ItemId; - if (query.Type.HasValue) - { - cmdText += " AND StreamType=@StreamType"; - cmd.Parameters.Add(cmd, "@StreamType", DbType.String).Value = query.Type.Value.ToString(); - } + if (query.Type.HasValue) + { + cmdText += " AND StreamType=@StreamType"; + cmd.Parameters.Add(cmd, "@StreamType", DbType.String).Value = query.Type.Value.ToString(); + } - if (query.Index.HasValue) - { - cmdText += " AND StreamIndex=@StreamIndex"; - cmd.Parameters.Add(cmd, "@StreamIndex", DbType.Int32).Value = query.Index.Value; - } + if (query.Index.HasValue) + { + cmdText += " AND StreamIndex=@StreamIndex"; + cmd.Parameters.Add(cmd, "@StreamIndex", DbType.Int32).Value = query.Index.Value; + } - cmdText += " order by StreamIndex ASC"; + cmdText += " order by StreamIndex ASC"; - cmd.CommandText = cmdText; + cmd.CommandText = cmdText; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) - { - while (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { - list.Add(GetMediaStream(reader)); + while (reader.Read()) + { + list.Add(GetMediaStream(reader)); + } } } + + return list; } + } - return list; + private void DeleteMediaStreams(IDbConnection connection, IDbTransaction transaction, Guid id) + { + using (var deleteStreamsCommand = connection.CreateCommand()) + { + // MediaStreams + deleteStreamsCommand.CommandText = "delete from mediastreams where ItemId=@ItemId"; + deleteStreamsCommand.Parameters.Add(deleteStreamsCommand, "@ItemId"); + + deleteStreamsCommand.GetParameter(0).Value = id; + + deleteStreamsCommand.Transaction = transaction; + + deleteStreamsCommand.ExecuteNonQuery(); + } } - public async Task SaveMediaStreams(Guid id, IEnumerable streams, CancellationToken cancellationToken) + public async Task SaveMediaStreams(Guid id, List streams, CancellationToken cancellationToken) { CheckDisposed(); @@ -3750,100 +3866,115 @@ namespace MediaBrowser.Server.Implementations.Persistence cancellationToken.ThrowIfCancellationRequested(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); - - // First delete chapters - _deleteStreamsCommand.GetParameter(0).Value = id; - - _deleteStreamsCommand.Transaction = transaction; + IDbTransaction transaction = null; - _deleteStreamsCommand.ExecuteNonQuery(); - - foreach (var stream in streams) + try { - cancellationToken.ThrowIfCancellationRequested(); - - var index = 0; - - _saveStreamCommand.GetParameter(index++).Value = id; - _saveStreamCommand.GetParameter(index++).Value = stream.Index; - _saveStreamCommand.GetParameter(index++).Value = stream.Type.ToString(); - _saveStreamCommand.GetParameter(index++).Value = stream.Codec; - _saveStreamCommand.GetParameter(index++).Value = stream.Language; - _saveStreamCommand.GetParameter(index++).Value = stream.ChannelLayout; - _saveStreamCommand.GetParameter(index++).Value = stream.Profile; - _saveStreamCommand.GetParameter(index++).Value = stream.AspectRatio; - _saveStreamCommand.GetParameter(index++).Value = stream.Path; - - _saveStreamCommand.GetParameter(index++).Value = stream.IsInterlaced; - - _saveStreamCommand.GetParameter(index++).Value = stream.BitRate; - _saveStreamCommand.GetParameter(index++).Value = stream.Channels; - _saveStreamCommand.GetParameter(index++).Value = stream.SampleRate; + transaction = connection.BeginTransaction(); - _saveStreamCommand.GetParameter(index++).Value = stream.IsDefault; - _saveStreamCommand.GetParameter(index++).Value = stream.IsForced; - _saveStreamCommand.GetParameter(index++).Value = stream.IsExternal; + // First delete + DeleteMediaStreams(connection, transaction, id); - _saveStreamCommand.GetParameter(index++).Value = stream.Width; - _saveStreamCommand.GetParameter(index++).Value = stream.Height; - _saveStreamCommand.GetParameter(index++).Value = stream.AverageFrameRate; - _saveStreamCommand.GetParameter(index++).Value = stream.RealFrameRate; - _saveStreamCommand.GetParameter(index++).Value = stream.Level; - _saveStreamCommand.GetParameter(index++).Value = stream.PixelFormat; - _saveStreamCommand.GetParameter(index++).Value = stream.BitDepth; - _saveStreamCommand.GetParameter(index++).Value = stream.IsAnamorphic; - _saveStreamCommand.GetParameter(index++).Value = stream.RefFrames; - - _saveStreamCommand.GetParameter(index++).Value = stream.CodecTag; - _saveStreamCommand.GetParameter(index++).Value = stream.Comment; - _saveStreamCommand.GetParameter(index++).Value = stream.NalLengthSize; - _saveStreamCommand.GetParameter(index++).Value = stream.IsAVC; - _saveStreamCommand.GetParameter(index++).Value = stream.Title; - - _saveStreamCommand.GetParameter(index++).Value = stream.TimeBase; - _saveStreamCommand.GetParameter(index++).Value = stream.CodecTimeBase; + if (streams.Count > 0) + { + using (var saveStreamCommand = connection.CreateCommand()) + { + saveStreamCommand.CommandText = string.Format("replace into mediastreams ({0}) values ({1})", + string.Join(",", _mediaStreamSaveColumns), + string.Join(",", _mediaStreamSaveColumns.Select(i => "@" + i).ToArray())); + + foreach (var col in _mediaStreamSaveColumns) + { + saveStreamCommand.Parameters.Add(saveStreamCommand, "@" + col); + } + + if (streams.Count > 1) + { + saveStreamCommand.Prepare(); + } + + foreach (var stream in streams) + { + cancellationToken.ThrowIfCancellationRequested(); + + var index = 0; + + saveStreamCommand.GetParameter(index++).Value = id; + saveStreamCommand.GetParameter(index++).Value = stream.Index; + saveStreamCommand.GetParameter(index++).Value = stream.Type.ToString(); + saveStreamCommand.GetParameter(index++).Value = stream.Codec; + saveStreamCommand.GetParameter(index++).Value = stream.Language; + saveStreamCommand.GetParameter(index++).Value = stream.ChannelLayout; + saveStreamCommand.GetParameter(index++).Value = stream.Profile; + saveStreamCommand.GetParameter(index++).Value = stream.AspectRatio; + saveStreamCommand.GetParameter(index++).Value = stream.Path; + + saveStreamCommand.GetParameter(index++).Value = stream.IsInterlaced; + + saveStreamCommand.GetParameter(index++).Value = stream.BitRate; + saveStreamCommand.GetParameter(index++).Value = stream.Channels; + saveStreamCommand.GetParameter(index++).Value = stream.SampleRate; + + saveStreamCommand.GetParameter(index++).Value = stream.IsDefault; + saveStreamCommand.GetParameter(index++).Value = stream.IsForced; + saveStreamCommand.GetParameter(index++).Value = stream.IsExternal; + + saveStreamCommand.GetParameter(index++).Value = stream.Width; + saveStreamCommand.GetParameter(index++).Value = stream.Height; + saveStreamCommand.GetParameter(index++).Value = stream.AverageFrameRate; + saveStreamCommand.GetParameter(index++).Value = stream.RealFrameRate; + saveStreamCommand.GetParameter(index++).Value = stream.Level; + saveStreamCommand.GetParameter(index++).Value = stream.PixelFormat; + saveStreamCommand.GetParameter(index++).Value = stream.BitDepth; + saveStreamCommand.GetParameter(index++).Value = stream.IsAnamorphic; + saveStreamCommand.GetParameter(index++).Value = stream.RefFrames; + + saveStreamCommand.GetParameter(index++).Value = stream.CodecTag; + saveStreamCommand.GetParameter(index++).Value = stream.Comment; + saveStreamCommand.GetParameter(index++).Value = stream.NalLengthSize; + saveStreamCommand.GetParameter(index++).Value = stream.IsAVC; + saveStreamCommand.GetParameter(index++).Value = stream.Title; + + saveStreamCommand.GetParameter(index++).Value = stream.TimeBase; + saveStreamCommand.GetParameter(index++).Value = stream.CodecTimeBase; + + saveStreamCommand.Transaction = transaction; + saveStreamCommand.ExecuteNonQuery(); + } + } + } - _saveStreamCommand.Transaction = transaction; - _saveStreamCommand.ExecuteNonQuery(); + transaction.Commit(); } - - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) + catch (OperationCanceledException) { - transaction.Rollback(); + if (transaction != null) + { + transaction.Rollback(); + } + + throw; } + catch (Exception e) + { + Logger.ErrorException("Failed to save media streams:", e); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save media streams:", e); + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); + throw; } - - throw; - } - finally - { - if (transaction != null) + finally { - transaction.Dispose(); + if (transaction != null) + { + transaction.Dispose(); + } } - - WriteLock.Release(); } } diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs index 0ce27fa5a..da0f584d5 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs @@ -45,12 +45,7 @@ namespace MediaBrowser.Server.Implementations.Persistence "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 index if not exists idx_userdata on userdata(key)", - "create unique index if not exists userdataindex on userdata (key, userId)", - - //pragmas - "pragma temp_store = memory", - - "pragma shrink_memory" + "create unique index if not exists userdataindex on userdata (key, userId)" }; connection.RunQueries(queries, Logger); diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs index 4c07a6018..25ab60ca5 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs @@ -52,9 +52,6 @@ namespace MediaBrowser.Server.Implementations.Persistence "create index if not exists idx_users on users(guid)", "create table if not exists schema_version (table_name primary key, version)", - //pragmas - "pragma temp_store = memory", - "pragma shrink_memory" }; diff --git a/MediaBrowser.Server.Implementations/Security/AuthenticationRepository.cs b/MediaBrowser.Server.Implementations/Security/AuthenticationRepository.cs index 3013510de..74a552dcc 100644 --- a/MediaBrowser.Server.Implementations/Security/AuthenticationRepository.cs +++ b/MediaBrowser.Server.Implementations/Security/AuthenticationRepository.cs @@ -32,12 +32,7 @@ namespace MediaBrowser.Server.Implementations.Security 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 index if not exists idx_AccessTokens on AccessTokens(Id)", - - //pragmas - "pragma temp_store = memory", - - "pragma shrink_memory" + "create index if not exists idx_AccessTokens on AccessTokens(Id)" }; connection.RunQueries(queries, Logger); diff --git a/MediaBrowser.Server.Implementations/Social/SharingRepository.cs b/MediaBrowser.Server.Implementations/Social/SharingRepository.cs index 8a895037e..c4243c1a7 100644 --- a/MediaBrowser.Server.Implementations/Social/SharingRepository.cs +++ b/MediaBrowser.Server.Implementations/Social/SharingRepository.cs @@ -31,9 +31,6 @@ namespace MediaBrowser.Server.Implementations.Social "create table if not exists Shares (Id GUID, ItemId TEXT, UserId TEXT, ExpirationDate DateTime, PRIMARY KEY (Id))", "create index if not exists idx_Shares on Shares(Id)", - //pragmas - "pragma temp_store = memory", - "pragma shrink_memory" }; diff --git a/MediaBrowser.Server.Implementations/Sync/SyncRepository.cs b/MediaBrowser.Server.Implementations/Sync/SyncRepository.cs index 5b623cf2a..a1ed66a99 100644 --- a/MediaBrowser.Server.Implementations/Sync/SyncRepository.cs +++ b/MediaBrowser.Server.Implementations/Sync/SyncRepository.cs @@ -43,9 +43,6 @@ namespace MediaBrowser.Server.Implementations.Sync "create index if not exists idx_SyncJobItems1 on SyncJobItems(Id)", "create index if not exists idx_SyncJobItems2 on SyncJobItems(TargetId)", - //pragmas - "pragma temp_store = memory", - "pragma shrink_memory" }; -- cgit v1.2.3 From f9847be17c5037671b622b61df9aaa113723f318 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Fri, 17 Jun 2016 09:06:13 -0400 Subject: update item by name queries --- MediaBrowser.Api/StartupWizardService.cs | 2 +- MediaBrowser.Api/UserLibrary/ArtistsService.cs | 40 ++- .../UserLibrary/BaseItemsByNameService.cs | 134 +++++++- MediaBrowser.Api/UserLibrary/GameGenresService.cs | 25 +- MediaBrowser.Api/UserLibrary/GenresService.cs | 61 +--- MediaBrowser.Api/UserLibrary/MusicGenresService.cs | 17 +- MediaBrowser.Api/UserLibrary/StudiosService.cs | 11 +- MediaBrowser.Controller/Entities/Audio/Audio.cs | 2 - .../Entities/Audio/MusicArtist.cs | 10 +- .../Entities/Audio/MusicGenre.cs | 11 +- MediaBrowser.Controller/Entities/BaseItem.cs | 4 + MediaBrowser.Controller/Entities/Folder.cs | 8 +- MediaBrowser.Controller/Entities/GameGenre.cs | 11 +- MediaBrowser.Controller/Entities/Genre.cs | 11 +- MediaBrowser.Controller/Entities/Person.cs | 11 +- MediaBrowser.Controller/Entities/Studio.cs | 11 +- MediaBrowser.Controller/Entities/Video.cs | 3 - MediaBrowser.Controller/Library/ILibraryManager.cs | 8 + .../Persistence/IItemRepository.cs | 8 + .../MediaBrowser.Model.Portable.csproj | 3 - .../MediaBrowser.Model.net35.csproj | 3 - MediaBrowser.Model/Dto/ItemCounts.cs | 1 + MediaBrowser.Model/Entities/MediaUrl.cs | 1 - MediaBrowser.Model/Entities/VideoSize.cs | 8 - MediaBrowser.Model/MediaBrowser.Model.csproj | 1 - .../Movies/GenericMovieDbInfo.cs | 3 +- .../Dto/DtoService.cs | 2 +- .../Library/LibraryManager.cs | 96 +++++- .../Library/Resolvers/BaseVideoResolver.cs | 2 +- .../MediaBrowser.Server.Implementations.csproj | 4 +- .../Persistence/CleanDatabaseScheduledTask.cs | 24 +- .../Persistence/SqliteItemRepository.cs | 348 +++++++++++++++++++-- .../packages.config | 2 +- 33 files changed, 733 insertions(+), 153 deletions(-) delete mode 100644 MediaBrowser.Model/Entities/VideoSize.cs (limited to 'MediaBrowser.Controller/Persistence') diff --git a/MediaBrowser.Api/StartupWizardService.cs b/MediaBrowser.Api/StartupWizardService.cs index f4aee080a..e4949b4d8 100644 --- a/MediaBrowser.Api/StartupWizardService.cs +++ b/MediaBrowser.Api/StartupWizardService.cs @@ -114,7 +114,7 @@ namespace MediaBrowser.Api config.EnableStandaloneMusicKeys = true; config.EnableCaseSensitiveItemIds = true; config.EnableFolderView = true; - config.SchemaVersion = 92; + config.SchemaVersion = 95; } public void Post(UpdateStartupConfiguration request) diff --git a/MediaBrowser.Api/UserLibrary/ArtistsService.cs b/MediaBrowser.Api/UserLibrary/ArtistsService.cs index cde5eade5..df73ef720 100644 --- a/MediaBrowser.Api/UserLibrary/ArtistsService.cs +++ b/MediaBrowser.Api/UserLibrary/ArtistsService.cs @@ -1,4 +1,5 @@ -using MediaBrowser.Controller.Dto; +using System; +using MediaBrowser.Controller.Dto; using MediaBrowser.Controller.Entities; using MediaBrowser.Controller.Entities.Audio; using MediaBrowser.Controller.Library; @@ -8,6 +9,8 @@ using MediaBrowser.Model.Dto; using ServiceStack; using System.Collections.Generic; using System.Linq; +using MediaBrowser.Model.Entities; +using MediaBrowser.Model.Querying; namespace MediaBrowser.Api.UserLibrary { @@ -100,7 +103,12 @@ namespace MediaBrowser.Api.UserLibrary /// System.Object. public object Get(GetArtists request) { - var result = GetResult(request); + if (string.IsNullOrWhiteSpace(request.IncludeItemTypes)) + { + //request.IncludeItemTypes = "Audio,MusicVideo"; + } + + var result = GetResultSlim(request); return ToOptimizedResult(result); } @@ -112,11 +120,26 @@ namespace MediaBrowser.Api.UserLibrary /// System.Object. public object Get(GetAlbumArtists request) { - var result = GetResult(request); + if (string.IsNullOrWhiteSpace(request.IncludeItemTypes)) + { + //request.IncludeItemTypes = "Audio,MusicVideo"; + } + + var result = GetResultSlim(request); return ToOptimizedResult(result); } + protected override QueryResult> GetItems(GetItemsByName request, InternalItemsQuery query) + { + if (request is GetAlbumArtists) + { + return LibraryManager.GetAlbumArtists(query); + } + + return LibraryManager.GetArtists(query); + } + /// /// Gets all items. /// @@ -125,16 +148,7 @@ namespace MediaBrowser.Api.UserLibrary /// IEnumerable{Tuple{System.StringFunc{System.Int32}}}. protected override IEnumerable GetAllItems(GetItemsByName request, IEnumerable items) { - if (request is GetAlbumArtists) - { - return LibraryManager.GetAlbumArtists(items - .Where(i => !i.IsFolder) - .OfType()); - } - - return LibraryManager.GetArtists(items - .Where(i => !i.IsFolder) - .OfType()); + throw new NotImplementedException(); } } } diff --git a/MediaBrowser.Api/UserLibrary/BaseItemsByNameService.cs b/MediaBrowser.Api/UserLibrary/BaseItemsByNameService.cs index 18dec3253..9465d1fdc 100644 --- a/MediaBrowser.Api/UserLibrary/BaseItemsByNameService.cs +++ b/MediaBrowser.Api/UserLibrary/BaseItemsByNameService.cs @@ -8,6 +8,7 @@ using ServiceStack; using System; using System.Collections.Generic; using System.Linq; +using MediaBrowser.Model.Dto; namespace MediaBrowser.Api.UserLibrary { @@ -83,6 +84,137 @@ namespace MediaBrowser.Api.UserLibrary return null; } + protected ItemsResult GetResultSlim(GetItemsByName request) + { + var dtoOptions = GetDtoOptions(request); + + User user = null; + BaseItem parentItem; + + if (!string.IsNullOrWhiteSpace(request.UserId)) + { + user = UserManager.GetUserById(request.UserId); + parentItem = string.IsNullOrEmpty(request.ParentId) ? user.RootFolder : LibraryManager.GetItemById(request.ParentId); + } + else + { + parentItem = string.IsNullOrEmpty(request.ParentId) ? LibraryManager.RootFolder : LibraryManager.GetItemById(request.ParentId); + } + + var excludeItemTypes = request.GetExcludeItemTypes(); + var includeItemTypes = request.GetIncludeItemTypes(); + var mediaTypes = request.GetMediaTypes(); + + var query = new InternalItemsQuery(user) + { + ExcludeItemTypes = excludeItemTypes, + IncludeItemTypes = includeItemTypes, + MediaTypes = mediaTypes, + StartIndex = request.StartIndex, + Limit = request.Limit, + IsFavorite = request.IsFavorite, + NameLessThan = request.NameLessThan, + NameStartsWith = request.NameStartsWith, + NameStartsWithOrGreater = request.NameStartsWithOrGreater, + AlbumArtistStartsWithOrGreater = request.AlbumArtistStartsWithOrGreater, + Tags = request.GetTags(), + OfficialRatings = request.GetOfficialRatings(), + Genres = request.GetGenres(), + GenreIds = request.GetGenreIds(), + Studios = request.GetStudios(), + StudioIds = request.GetStudioIds(), + Person = request.Person, + PersonIds = request.GetPersonIds(), + PersonTypes = request.GetPersonTypes(), + Years = request.GetYears(), + MinCommunityRating = request.MinCommunityRating + }; + + if (!string.IsNullOrWhiteSpace(request.ParentId)) + { + if (parentItem is Folder) + { + query.AncestorIds = new[] { request.ParentId }; + } + else + { + query.ItemIds = new[] { request.ParentId }; + } + } + + foreach (var filter in request.GetFilters()) + { + switch (filter) + { + case ItemFilter.Dislikes: + query.IsLiked = false; + break; + case ItemFilter.IsFavorite: + query.IsFavorite = true; + break; + case ItemFilter.IsFavoriteOrLikes: + query.IsFavoriteOrLiked = true; + break; + case ItemFilter.IsFolder: + query.IsFolder = true; + break; + case ItemFilter.IsNotFolder: + query.IsFolder = false; + break; + case ItemFilter.IsPlayed: + query.IsPlayed = true; + break; + case ItemFilter.IsRecentlyAdded: + break; + case ItemFilter.IsResumable: + query.IsResumable = true; + break; + case ItemFilter.IsUnplayed: + query.IsPlayed = false; + break; + case ItemFilter.Likes: + query.IsLiked = true; + break; + } + } + + var result = GetItems(request, query); + + var dtos = result.Items.Select(i => + { + var dto = DtoService.GetItemByNameDto(i.Item1, dtoOptions, null, user); + + if (!string.IsNullOrWhiteSpace(request.IncludeItemTypes)) + { + SetItemCounts(dto, i.Item2); + } + return dto; + }); + + return new ItemsResult + { + Items = dtos.ToArray(), + TotalRecordCount = result.TotalRecordCount + }; + } + + protected virtual QueryResult> GetItems(GetItemsByName request, InternalItemsQuery query) + { + return new QueryResult>(); + } + + private void SetItemCounts(BaseItemDto dto, ItemCounts counts) + { + dto.ChildCount = counts.ItemCount; + dto.SeriesCount = counts.SeriesCount; + dto.EpisodeCount = counts.EpisodeCount; + dto.MovieCount = counts.MovieCount; + dto.TrailerCount = counts.TrailerCount; + dto.AlbumCount = counts.AlbumCount; + dto.SongCount = counts.SongCount; + dto.GameCount = counts.GameCount; + } + /// /// Gets the specified request. /// @@ -374,7 +506,7 @@ namespace MediaBrowser.Api.UserLibrary /// The include item types. /// The media types. /// IEnumerable{BaseItem}. - protected bool FilterItem(GetItemsByName request, BaseItem f, string[] excludeItemTypes, string[] includeItemTypes, string[] mediaTypes) + private bool FilterItem(GetItemsByName request, BaseItem f, string[] excludeItemTypes, string[] includeItemTypes, string[] mediaTypes) { // Exclude item types if (excludeItemTypes.Length > 0) diff --git a/MediaBrowser.Api/UserLibrary/GameGenresService.cs b/MediaBrowser.Api/UserLibrary/GameGenresService.cs index 58237f80f..ebc9a970d 100644 --- a/MediaBrowser.Api/UserLibrary/GameGenresService.cs +++ b/MediaBrowser.Api/UserLibrary/GameGenresService.cs @@ -9,6 +9,7 @@ using ServiceStack; using System; using System.Collections.Generic; using System.Linq; +using MediaBrowser.Model.Querying; namespace MediaBrowser.Api.UserLibrary { @@ -87,11 +88,16 @@ namespace MediaBrowser.Api.UserLibrary /// System.Object. public object Get(GetGameGenres request) { - var result = GetResult(request); + var result = GetResultSlim(request); return ToOptimizedSerializedResultUsingCache(result); } + protected override QueryResult> GetItems(GetItemsByName request, InternalItemsQuery query) + { + return LibraryManager.GetGameGenres(query); + } + /// /// Gets all items. /// @@ -100,22 +106,7 @@ namespace MediaBrowser.Api.UserLibrary /// IEnumerable{Tuple{System.StringFunc{System.Int32}}}. protected override IEnumerable GetAllItems(GetItemsByName request, IEnumerable items) { - return items - .SelectMany(i => i.Genres) - .DistinctNames() - .Select(name => - { - try - { - return LibraryManager.GetGameGenre(name); - } - catch (Exception ex) - { - Logger.ErrorException("Error getting genre {0}", ex, name); - return null; - } - }) - .Where(i => i != null); + throw new NotImplementedException(); } } } diff --git a/MediaBrowser.Api/UserLibrary/GenresService.cs b/MediaBrowser.Api/UserLibrary/GenresService.cs index d383bd0ad..57c11a1fa 100644 --- a/MediaBrowser.Api/UserLibrary/GenresService.cs +++ b/MediaBrowser.Api/UserLibrary/GenresService.cs @@ -9,6 +9,7 @@ using ServiceStack; using System; using System.Collections.Generic; using System.Linq; +using MediaBrowser.Model.Querying; namespace MediaBrowser.Api.UserLibrary { @@ -92,65 +93,37 @@ namespace MediaBrowser.Api.UserLibrary /// System.Object. public object Get(GetGenres request) { - var result = GetResult(request); + var result = GetResultSlim(request); return ToOptimizedSerializedResultUsingCache(result); } - /// - /// Gets all items. - /// - /// The request. - /// The items. - /// IEnumerable{Tuple{System.StringFunc{System.Int32}}}. - protected override IEnumerable GetAllItems(GetItemsByName request, IEnumerable items) + protected override QueryResult> GetItems(GetItemsByName request, InternalItemsQuery query) { var viewType = GetParentItemViewType(request); if (string.Equals(viewType, CollectionType.Music) || string.Equals(viewType, CollectionType.MusicVideos)) { - return items - .SelectMany(i => i.Genres) - .DistinctNames() - .Select(name => LibraryManager.GetMusicGenre(name)); + return LibraryManager.GetMusicGenres(query); } if (string.Equals(viewType, CollectionType.Games)) { - return items - .SelectMany(i => i.Genres) - .DistinctNames() - .Select(name => - { - try - { - return LibraryManager.GetGameGenre(name); - } - catch (Exception ex) - { - Logger.ErrorException("Error getting genre {0}", ex, name); - return null; - } - }) - .Where(i => i != null); + return LibraryManager.GetGameGenres(query); } - return items - .SelectMany(i => i.Genres) - .DistinctNames() - .Select(name => - { - try - { - return LibraryManager.GetGenre(name); - } - catch (Exception ex) - { - Logger.ErrorException("Error getting genre {0}", ex, name); - return null; - } - }) - .Where(i => i != null); + return LibraryManager.GetGenres(query); + } + + /// + /// Gets all items. + /// + /// The request. + /// The items. + /// IEnumerable{Tuple{System.StringFunc{System.Int32}}}. + protected override IEnumerable GetAllItems(GetItemsByName request, IEnumerable items) + { + throw new NotImplementedException(); } } } diff --git a/MediaBrowser.Api/UserLibrary/MusicGenresService.cs b/MediaBrowser.Api/UserLibrary/MusicGenresService.cs index 12cb62fac..f02136e05 100644 --- a/MediaBrowser.Api/UserLibrary/MusicGenresService.cs +++ b/MediaBrowser.Api/UserLibrary/MusicGenresService.cs @@ -1,4 +1,5 @@ -using MediaBrowser.Controller.Dto; +using System; +using MediaBrowser.Controller.Dto; using MediaBrowser.Controller.Entities; using MediaBrowser.Controller.Entities.Audio; using MediaBrowser.Controller.Library; @@ -8,6 +9,8 @@ using MediaBrowser.Model.Dto; using ServiceStack; using System.Collections.Generic; using System.Linq; +using MediaBrowser.Model.Entities; +using MediaBrowser.Model.Querying; namespace MediaBrowser.Api.UserLibrary { @@ -86,11 +89,16 @@ namespace MediaBrowser.Api.UserLibrary /// System.Object. public object Get(GetMusicGenres request) { - var result = GetResult(request); + var result = GetResultSlim(request); return ToOptimizedSerializedResultUsingCache(result); } + protected override QueryResult> GetItems(GetItemsByName request, InternalItemsQuery query) + { + return LibraryManager.GetMusicGenres(query); + } + /// /// Gets all items. /// @@ -99,10 +107,7 @@ namespace MediaBrowser.Api.UserLibrary /// IEnumerable{Tuple{System.StringFunc{System.Int32}}}. protected override IEnumerable GetAllItems(GetItemsByName request, IEnumerable items) { - return items - .SelectMany(i => i.Genres) - .DistinctNames() - .Select(name => LibraryManager.GetMusicGenre(name)); + throw new NotImplementedException(); } } } diff --git a/MediaBrowser.Api/UserLibrary/StudiosService.cs b/MediaBrowser.Api/UserLibrary/StudiosService.cs index 2cdabf721..9e9c25d78 100644 --- a/MediaBrowser.Api/UserLibrary/StudiosService.cs +++ b/MediaBrowser.Api/UserLibrary/StudiosService.cs @@ -1,4 +1,5 @@ -using MediaBrowser.Controller.Dto; +using System; +using MediaBrowser.Controller.Dto; using MediaBrowser.Controller.Entities; using MediaBrowser.Controller.Library; using MediaBrowser.Controller.Net; @@ -7,6 +8,7 @@ using MediaBrowser.Model.Dto; using ServiceStack; using System.Collections.Generic; using System.Linq; +using MediaBrowser.Model.Querying; namespace MediaBrowser.Api.UserLibrary { @@ -90,11 +92,16 @@ namespace MediaBrowser.Api.UserLibrary /// System.Object. public object Get(GetStudios request) { - var result = GetResult(request); + var result = GetResultSlim(request); return ToOptimizedSerializedResultUsingCache(result); } + protected override QueryResult> GetItems(GetItemsByName request, InternalItemsQuery query) + { + return LibraryManager.GetStudios(query); + } + /// /// Gets all items. /// diff --git a/MediaBrowser.Controller/Entities/Audio/Audio.cs b/MediaBrowser.Controller/Entities/Audio/Audio.cs index 06710b030..b3df34c4d 100644 --- a/MediaBrowser.Controller/Entities/Audio/Audio.cs +++ b/MediaBrowser.Controller/Entities/Audio/Audio.cs @@ -26,8 +26,6 @@ namespace MediaBrowser.Controller.Entities.Audio { public List ChannelMediaSources { get; set; } - public long? Size { get; set; } - public string Container { get; set; } public int? TotalBitrate { get; set; } public ExtraType? ExtraType { get; set; } diff --git a/MediaBrowser.Controller/Entities/Audio/MusicArtist.cs b/MediaBrowser.Controller/Entities/Audio/MusicArtist.cs index 950701687..6790a1bcf 100644 --- a/MediaBrowser.Controller/Entities/Audio/MusicArtist.cs +++ b/MediaBrowser.Controller/Entities/Audio/MusicArtist.cs @@ -9,6 +9,7 @@ using System.Linq; using System.Runtime.Serialization; using System.Threading; using System.Threading.Tasks; +using MediaBrowser.Common.Extensions; namespace MediaBrowser.Controller.Entities.Audio { @@ -165,10 +166,17 @@ namespace MediaBrowser.Controller.Entities.Audio list.Add("Artist-Musicbrainz-" + id); } - list.Add("Artist-" + item.Name); + list.Add("Artist-" + (item.Name ?? string.Empty).RemoveDiacritics()); return list; } + public override string PresentationUniqueKey + { + get + { + return "Artist-" + (Name ?? string.Empty).RemoveDiacritics(); + } + } protected override bool GetBlockUnratedValue(UserPolicy config) { return config.BlockUnratedItems.Contains(UnratedItem.Music); diff --git a/MediaBrowser.Controller/Entities/Audio/MusicGenre.cs b/MediaBrowser.Controller/Entities/Audio/MusicGenre.cs index 77cf0cc49..798bc79fb 100644 --- a/MediaBrowser.Controller/Entities/Audio/MusicGenre.cs +++ b/MediaBrowser.Controller/Entities/Audio/MusicGenre.cs @@ -2,6 +2,7 @@ using System.Collections.Generic; using System.Linq; using System.Runtime.Serialization; +using MediaBrowser.Common.Extensions; namespace MediaBrowser.Controller.Entities.Audio { @@ -14,10 +15,18 @@ namespace MediaBrowser.Controller.Entities.Audio { var list = base.GetUserDataKeys(); - list.Insert(0, "MusicGenre-" + Name); + list.Insert(0, GetType().Name + "-" + (Name ?? string.Empty).RemoveDiacritics()); return list; } + public override string PresentationUniqueKey + { + get + { + return GetUserDataKeys()[0]; + } + } + [IgnoreDataMember] public override bool SupportsAddingToPlaylist { diff --git a/MediaBrowser.Controller/Entities/BaseItem.cs b/MediaBrowser.Controller/Entities/BaseItem.cs index 33fd03e15..ed838ab31 100644 --- a/MediaBrowser.Controller/Entities/BaseItem.cs +++ b/MediaBrowser.Controller/Entities/BaseItem.cs @@ -69,6 +69,10 @@ namespace MediaBrowser.Controller.Entities [IgnoreDataMember] public string PreferredMetadataLanguage { get; set; } + public long? Size { get; set; } + public string Container { get; set; } + public string ShortOverview { get; set; } + public List ImageInfos { get; set; } [IgnoreDataMember] diff --git a/MediaBrowser.Controller/Entities/Folder.cs b/MediaBrowser.Controller/Entities/Folder.cs index e3841099e..91ca1f009 100644 --- a/MediaBrowser.Controller/Entities/Folder.cs +++ b/MediaBrowser.Controller/Entities/Folder.cs @@ -216,7 +216,7 @@ namespace MediaBrowser.Controller.Entities { get { - return LoadChildren().Select(LibraryManager.GetItemById).Where(i => i != null); + return LoadChildren(); } } @@ -270,7 +270,7 @@ namespace MediaBrowser.Controller.Entities /// Loads our children. Validation will occur externally. /// We want this sychronous. /// - protected virtual IEnumerable LoadChildren() + protected virtual IEnumerable LoadChildren() { //just load our children from the repo - the library will be validated and maintained in other processes return GetCachedChildren(); @@ -657,9 +657,9 @@ namespace MediaBrowser.Controller.Entities /// Get our children from the repo - stubbed for now /// /// IEnumerable{BaseItem}. - protected IEnumerable GetCachedChildren() + protected IEnumerable GetCachedChildren() { - return ItemRepository.GetItemIdsList(new InternalItemsQuery + return ItemRepository.GetItemList(new InternalItemsQuery { ParentId = Id, GroupByPresentationUniqueKey = false diff --git a/MediaBrowser.Controller/Entities/GameGenre.cs b/MediaBrowser.Controller/Entities/GameGenre.cs index 7c1e88cb1..45e766c0f 100644 --- a/MediaBrowser.Controller/Entities/GameGenre.cs +++ b/MediaBrowser.Controller/Entities/GameGenre.cs @@ -2,6 +2,7 @@ using System.Collections.Generic; using System.Linq; using System.Runtime.Serialization; +using MediaBrowser.Common.Extensions; namespace MediaBrowser.Controller.Entities { @@ -11,10 +12,18 @@ namespace MediaBrowser.Controller.Entities { var list = base.GetUserDataKeys(); - list.Insert(0, "GameGenre-" + Name); + list.Insert(0, GetType().Name + "-" + (Name ?? string.Empty).RemoveDiacritics()); return list; } + public override string PresentationUniqueKey + { + get + { + return GetUserDataKeys()[0]; + } + } + /// /// Returns the folder containing the item. /// If the item is a folder, it returns the folder itself diff --git a/MediaBrowser.Controller/Entities/Genre.cs b/MediaBrowser.Controller/Entities/Genre.cs index c87d4daaf..cc5aebb2a 100644 --- a/MediaBrowser.Controller/Entities/Genre.cs +++ b/MediaBrowser.Controller/Entities/Genre.cs @@ -3,6 +3,7 @@ using MediaBrowser.Controller.Entities.Audio; using System; using System.Collections.Generic; using System.Linq; +using MediaBrowser.Common.Extensions; namespace MediaBrowser.Controller.Entities { @@ -15,10 +16,18 @@ namespace MediaBrowser.Controller.Entities { var list = base.GetUserDataKeys(); - list.Insert(0, "Genre-" + Name); + list.Insert(0, GetType().Name + "-" + (Name ?? string.Empty).RemoveDiacritics()); return list; } + public override string PresentationUniqueKey + { + get + { + return GetUserDataKeys()[0]; + } + } + /// /// Returns the folder containing the item. /// If the item is a folder, it returns the folder itself diff --git a/MediaBrowser.Controller/Entities/Person.cs b/MediaBrowser.Controller/Entities/Person.cs index 2b099e3d5..8ef0d70bf 100644 --- a/MediaBrowser.Controller/Entities/Person.cs +++ b/MediaBrowser.Controller/Entities/Person.cs @@ -3,6 +3,7 @@ using System; using System.Collections.Generic; using System.Linq; using System.Runtime.Serialization; +using MediaBrowser.Common.Extensions; using MediaBrowser.Model.Entities; namespace MediaBrowser.Controller.Entities @@ -22,10 +23,18 @@ namespace MediaBrowser.Controller.Entities { var list = base.GetUserDataKeys(); - list.Insert(0, "Person-" + Name); + list.Insert(0, GetType().Name + "-" + (Name ?? string.Empty).RemoveDiacritics()); return list; } + public override string PresentationUniqueKey + { + get + { + return GetUserDataKeys()[0]; + } + } + public PersonLookupInfo GetLookupInfo() { return GetItemLookupInfo(); diff --git a/MediaBrowser.Controller/Entities/Studio.cs b/MediaBrowser.Controller/Entities/Studio.cs index e46978af3..762798b55 100644 --- a/MediaBrowser.Controller/Entities/Studio.cs +++ b/MediaBrowser.Controller/Entities/Studio.cs @@ -2,6 +2,7 @@ using System.Collections.Generic; using System.Linq; using System.Runtime.Serialization; +using MediaBrowser.Common.Extensions; namespace MediaBrowser.Controller.Entities { @@ -14,10 +15,18 @@ namespace MediaBrowser.Controller.Entities { var list = base.GetUserDataKeys(); - list.Insert(0, "Studio-" + Name); + list.Insert(0, GetType().Name + "-" + (Name ?? string.Empty).RemoveDiacritics()); return list; } + public override string PresentationUniqueKey + { + get + { + return GetUserDataKeys()[0]; + } + } + /// /// Returns the folder containing the item. /// If the item is a folder, it returns the folder itself diff --git a/MediaBrowser.Controller/Entities/Video.cs b/MediaBrowser.Controller/Entities/Video.cs index 2502033c8..73c893dd6 100644 --- a/MediaBrowser.Controller/Entities/Video.cs +++ b/MediaBrowser.Controller/Entities/Video.cs @@ -58,10 +58,7 @@ namespace MediaBrowser.Controller.Entities } } - public long? Size { get; set; } - public string Container { get; set; } public int? TotalBitrate { get; set; } - public string ShortOverview { get; set; } public ExtraType? ExtraType { get; set; } /// diff --git a/MediaBrowser.Controller/Library/ILibraryManager.cs b/MediaBrowser.Controller/Library/ILibraryManager.cs index 1d0e06482..4e641b005 100644 --- a/MediaBrowser.Controller/Library/ILibraryManager.cs +++ b/MediaBrowser.Controller/Library/ILibraryManager.cs @@ -11,6 +11,7 @@ using System.Collections.Generic; using System.Threading; using System.Threading.Tasks; using CommonIO; +using MediaBrowser.Model.Dto; namespace MediaBrowser.Controller.Library { @@ -567,5 +568,12 @@ namespace MediaBrowser.Controller.Library void RemoveVirtualFolder(string name, bool refreshLibrary); void AddMediaPath(string virtualFolderName, string path); void RemoveMediaPath(string virtualFolderName, string path); + + QueryResult> GetGenres(InternalItemsQuery query); + QueryResult> GetMusicGenres(InternalItemsQuery query); + QueryResult> GetGameGenres(InternalItemsQuery query); + QueryResult> GetStudios(InternalItemsQuery query); + QueryResult> GetArtists(InternalItemsQuery query); + QueryResult> GetAlbumArtists(InternalItemsQuery query); } } \ No newline at end of file diff --git a/MediaBrowser.Controller/Persistence/IItemRepository.cs b/MediaBrowser.Controller/Persistence/IItemRepository.cs index 80a6e4042..2ef8b70f0 100644 --- a/MediaBrowser.Controller/Persistence/IItemRepository.cs +++ b/MediaBrowser.Controller/Persistence/IItemRepository.cs @@ -4,6 +4,7 @@ using System; using System.Collections.Generic; using System.Threading; using System.Threading.Tasks; +using MediaBrowser.Model.Dto; using MediaBrowser.Model.Querying; namespace MediaBrowser.Controller.Persistence @@ -161,6 +162,13 @@ namespace MediaBrowser.Controller.Persistence /// The cancellation token. /// Task. Task UpdateInheritedValues(CancellationToken cancellationToken); + + QueryResult> GetGenres(InternalItemsQuery query); + QueryResult> GetMusicGenres(InternalItemsQuery query); + QueryResult> GetGameGenres(InternalItemsQuery query); + QueryResult> GetStudios(InternalItemsQuery query); + QueryResult> GetArtists(InternalItemsQuery query); + QueryResult> GetAlbumArtists(InternalItemsQuery query); } } diff --git a/MediaBrowser.Model.Portable/MediaBrowser.Model.Portable.csproj b/MediaBrowser.Model.Portable/MediaBrowser.Model.Portable.csproj index f759ffeae..f9d28605e 100644 --- a/MediaBrowser.Model.Portable/MediaBrowser.Model.Portable.csproj +++ b/MediaBrowser.Model.Portable/MediaBrowser.Model.Portable.csproj @@ -599,9 +599,6 @@ Entities\Video3DFormat.cs - - Entities\VideoSize.cs - Entities\VideoType.cs diff --git a/MediaBrowser.Model.net35/MediaBrowser.Model.net35.csproj b/MediaBrowser.Model.net35/MediaBrowser.Model.net35.csproj index 05bbeb50e..edaa0e027 100644 --- a/MediaBrowser.Model.net35/MediaBrowser.Model.net35.csproj +++ b/MediaBrowser.Model.net35/MediaBrowser.Model.net35.csproj @@ -573,9 +573,6 @@ Entities\Video3DFormat.cs - - Entities\VideoSize.cs - Entities\VideoType.cs diff --git a/MediaBrowser.Model/Dto/ItemCounts.cs b/MediaBrowser.Model/Dto/ItemCounts.cs index a3a00c341..07ddfa1ac 100644 --- a/MediaBrowser.Model/Dto/ItemCounts.cs +++ b/MediaBrowser.Model/Dto/ItemCounts.cs @@ -60,5 +60,6 @@ /// /// The book count. public int BookCount { get; set; } + public int ItemCount { get; set; } } } diff --git a/MediaBrowser.Model/Entities/MediaUrl.cs b/MediaBrowser.Model/Entities/MediaUrl.cs index 24e3b1492..2e17bba8a 100644 --- a/MediaBrowser.Model/Entities/MediaUrl.cs +++ b/MediaBrowser.Model/Entities/MediaUrl.cs @@ -5,6 +5,5 @@ namespace MediaBrowser.Model.Entities { public string Url { get; set; } public string Name { get; set; } - public VideoSize? VideoSize { get; set; } } } diff --git a/MediaBrowser.Model/Entities/VideoSize.cs b/MediaBrowser.Model/Entities/VideoSize.cs deleted file mode 100644 index 0100f3b90..000000000 --- a/MediaBrowser.Model/Entities/VideoSize.cs +++ /dev/null @@ -1,8 +0,0 @@ -namespace MediaBrowser.Model.Entities -{ - public enum VideoSize - { - StandardDefinition, - HighDefinition - } -} \ No newline at end of file diff --git a/MediaBrowser.Model/MediaBrowser.Model.csproj b/MediaBrowser.Model/MediaBrowser.Model.csproj index 522b7d38d..7c9f132db 100644 --- a/MediaBrowser.Model/MediaBrowser.Model.csproj +++ b/MediaBrowser.Model/MediaBrowser.Model.csproj @@ -227,7 +227,6 @@ - diff --git a/MediaBrowser.Providers/Movies/GenericMovieDbInfo.cs b/MediaBrowser.Providers/Movies/GenericMovieDbInfo.cs index 3b3065893..a6b6de7e5 100644 --- a/MediaBrowser.Providers/Movies/GenericMovieDbInfo.cs +++ b/MediaBrowser.Providers/Movies/GenericMovieDbInfo.cs @@ -326,8 +326,7 @@ namespace MediaBrowser.Providers.Movies hasTrailers.RemoteTrailers = movieData.trailers.youtube.Select(i => new MediaUrl { Url = string.Format("https://www.youtube.com/watch?v={0}", i.source), - Name = i.name, - VideoSize = string.Equals("hd", i.size, StringComparison.OrdinalIgnoreCase) ? VideoSize.HighDefinition : VideoSize.StandardDefinition + Name = i.name }).ToList(); } diff --git a/MediaBrowser.Server.Implementations/Dto/DtoService.cs b/MediaBrowser.Server.Implementations/Dto/DtoService.cs index 95a235109..bb9fcc924 100644 --- a/MediaBrowser.Server.Implementations/Dto/DtoService.cs +++ b/MediaBrowser.Server.Implementations/Dto/DtoService.cs @@ -418,7 +418,7 @@ namespace MediaBrowser.Server.Implementations.Dto var dto = GetBaseItemDtoInternal(item, options, GetSyncedItemProgressDictionary(syncProgress), user); - if (options.Fields.Contains(ItemFields.ItemCounts)) + if (taggedItems != null && options.Fields.Contains(ItemFields.ItemCounts)) { SetItemByNameInfo(item, dto, taggedItems, user); } diff --git a/MediaBrowser.Server.Implementations/Library/LibraryManager.cs b/MediaBrowser.Server.Implementations/Library/LibraryManager.cs index c0c40aa5b..09dc0400c 100644 --- a/MediaBrowser.Server.Implementations/Library/LibraryManager.cs +++ b/MediaBrowser.Server.Implementations/Library/LibraryManager.cs @@ -33,6 +33,7 @@ using System.Net; using System.Threading; using System.Threading.Tasks; using CommonIO; +using MediaBrowser.Model.Dto; using MediaBrowser.Model.Extensions; using MediaBrowser.Model.Library; using MediaBrowser.Model.Net; @@ -1278,7 +1279,7 @@ namespace MediaBrowser.Server.Implementations.Library private bool EnableCaching { - get { return true; } + get { return false; } } public IEnumerable GetItemList(InternalItemsQuery query) @@ -1326,6 +1327,99 @@ namespace MediaBrowser.Server.Implementations.Library return ItemRepository.GetItemIdsList(query); } + public QueryResult> GetStudios(InternalItemsQuery query) + { + if (query.User != null) + { + AddUserToQuery(query, query.User); + } + + SetTopParentOrAncestorIds(query); + return ItemRepository.GetStudios(query); + } + + public QueryResult> GetGenres(InternalItemsQuery query) + { + if (query.User != null) + { + AddUserToQuery(query, query.User); + } + + SetTopParentOrAncestorIds(query); + return ItemRepository.GetGenres(query); + } + + public QueryResult> GetGameGenres(InternalItemsQuery query) + { + if (query.User != null) + { + AddUserToQuery(query, query.User); + } + + SetTopParentOrAncestorIds(query); + return ItemRepository.GetGameGenres(query); + } + + public QueryResult> GetMusicGenres(InternalItemsQuery query) + { + if (query.User != null) + { + AddUserToQuery(query, query.User); + } + + SetTopParentOrAncestorIds(query); + return ItemRepository.GetMusicGenres(query); + } + + public QueryResult> GetArtists(InternalItemsQuery query) + { + if (query.User != null) + { + AddUserToQuery(query, query.User); + } + + SetTopParentOrAncestorIds(query); + return ItemRepository.GetArtists(query); + } + + private void SetTopParentOrAncestorIds(InternalItemsQuery query) + { + if (query.AncestorIds.Length == 0) + { + return; + } + + var parents = query.AncestorIds.Select(i => GetItemById(new Guid(i))).ToList(); + + if (parents.All(i => + { + if (i is ICollectionFolder || i is UserView) + { + return true; + } + + _logger.Debug("Query requires ancestor query due to type: " + i.GetType().Name); + return false; + + })) + { + // Optimize by querying against top level views + query.TopParentIds = parents.SelectMany(i => GetTopParentsForQuery(i, query.User)).Select(i => i.Id.ToString("N")).ToArray(); + query.AncestorIds = new string[] { }; + } + } + + public QueryResult> GetAlbumArtists(InternalItemsQuery query) + { + if (query.User != null) + { + AddUserToQuery(query, query.User); + } + + SetTopParentOrAncestorIds(query); + return ItemRepository.GetAlbumArtists(query); + } + public IEnumerable GetItemList(InternalItemsQuery query, IEnumerable parentIds) { var parents = parentIds.Select(i => GetItemById(new Guid(i))).Where(i => i != null).ToList(); diff --git a/MediaBrowser.Server.Implementations/Library/Resolvers/BaseVideoResolver.cs b/MediaBrowser.Server.Implementations/Library/Resolvers/BaseVideoResolver.cs index 9edd3f83f..703a33856 100644 --- a/MediaBrowser.Server.Implementations/Library/Resolvers/BaseVideoResolver.cs +++ b/MediaBrowser.Server.Implementations/Library/Resolvers/BaseVideoResolver.cs @@ -126,7 +126,7 @@ namespace MediaBrowser.Server.Implementations.Library.Resolvers } else { - var videoInfo = parser.ResolveFile(args.Path); + var videoInfo = parser.Resolve(args.Path, false, false); if (videoInfo == null) { diff --git a/MediaBrowser.Server.Implementations/MediaBrowser.Server.Implementations.csproj b/MediaBrowser.Server.Implementations/MediaBrowser.Server.Implementations.csproj index 066a8c3ca..c69c788c8 100644 --- a/MediaBrowser.Server.Implementations/MediaBrowser.Server.Implementations.csproj +++ b/MediaBrowser.Server.Implementations/MediaBrowser.Server.Implementations.csproj @@ -56,8 +56,8 @@ ..\packages\Interfaces.IO.1.0.0.5\lib\portable-net45+sl4+wp71+win8+wpa81\Interfaces.IO.dll - - ..\packages\MediaBrowser.Naming.1.0.0.51\lib\portable-net45+sl4+wp71+win8+wpa81\MediaBrowser.Naming.dll + + ..\packages\MediaBrowser.Naming.1.0.0.52\lib\portable-net45+sl4+wp71+win8+wpa81\MediaBrowser.Naming.dll True diff --git a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs index 2a2f9a09d..b11a3e496 100644 --- a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs +++ b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs @@ -15,6 +15,7 @@ using System.Threading.Tasks; using CommonIO; using MediaBrowser.Controller.Channels; using MediaBrowser.Controller.Entities.Audio; +using MediaBrowser.Controller.LiveTv; using MediaBrowser.Controller.Localization; using MediaBrowser.Controller.Net; using MediaBrowser.Server.Implementations.ScheduledTasks; @@ -145,7 +146,8 @@ namespace MediaBrowser.Server.Implementations.Persistence { var itemIds = _libraryManager.GetItemIds(new InternalItemsQuery { - IsCurrentSchema = false + IsCurrentSchema = false, + ExcludeItemTypes = new[] { typeof(LiveTvProgram).Name } }); var numComplete = 0; @@ -236,14 +238,14 @@ namespace MediaBrowser.Server.Implementations.Persistence // These have their own cleanup routines ExcludeItemTypes = new[] { - typeof(Person).Name, - typeof(Genre).Name, - typeof(MusicGenre).Name, - typeof(GameGenre).Name, - typeof(Studio).Name, - typeof(Year).Name, - typeof(Channel).Name, - typeof(AggregateFolder).Name, + typeof(Person).Name, + typeof(Genre).Name, + typeof(MusicGenre).Name, + typeof(GameGenre).Name, + typeof(Studio).Name, + typeof(Year).Name, + typeof(Channel).Name, + typeof(AggregateFolder).Name, typeof(CollectionFolder).Name } }); @@ -313,8 +315,8 @@ namespace MediaBrowser.Server.Implementations.Persistence public IEnumerable GetDefaultTriggers() { - return new ITaskTrigger[] - { + return new ITaskTrigger[] + { new IntervalTrigger{ Interval = TimeSpan.FromHours(24)} }; } diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 437d16974..121074a6e 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -22,6 +22,7 @@ using MediaBrowser.Common.Extensions; using MediaBrowser.Controller.Channels; using MediaBrowser.Controller.Configuration; using MediaBrowser.Controller.Playlists; +using MediaBrowser.Model.Dto; using MediaBrowser.Model.LiveTv; namespace MediaBrowser.Server.Implementations.Persistence @@ -94,7 +95,7 @@ namespace MediaBrowser.Server.Implementations.Persistence private IDbCommand _updateInheritedRatingCommand; private IDbCommand _updateInheritedTagsCommand; - public const int LatestSchemaVersion = 92; + public const int LatestSchemaVersion = 95; /// /// Initializes a new instance of the class. @@ -157,7 +158,7 @@ namespace MediaBrowser.Server.Implementations.Persistence "create table if not exists UserDataKeys (ItemId GUID, UserDataKey TEXT, PRIMARY KEY (ItemId, UserDataKey))", "create index if not exists idx_UserDataKeys1 on UserDataKeys(ItemId)", - "create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT)", + "create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT, CleanValue TEXT)", "create index if not exists idx_ItemValues on ItemValues(ItemId)", "create index if not exists idx_ItemValues2 on ItemValues(ItemId,Type)", @@ -263,6 +264,7 @@ namespace MediaBrowser.Server.Implementations.Persistence _connection.AddColumn(Logger, "TypedBaseItems", "SeriesName", "Text"); _connection.AddColumn(Logger, "UserDataKeys", "Priority", "INT"); + _connection.AddColumn(Logger, "ItemValues", "CleanValue", "Text"); string[] postQueries = { @@ -568,10 +570,11 @@ namespace MediaBrowser.Server.Implementations.Persistence _deleteItemValuesCommand.Parameters.Add(_deleteItemValuesCommand, "@Id"); _saveItemValuesCommand = _connection.CreateCommand(); - _saveItemValuesCommand.CommandText = "insert into ItemValues (ItemId, Type, Value) values (@ItemId, @Type, @Value)"; + _saveItemValuesCommand.CommandText = "insert into ItemValues (ItemId, Type, Value, CleanValue) values (@ItemId, @Type, @Value, @CleanValue)"; _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@ItemId"); _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Type"); _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Value"); + _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@CleanValue"); // provider ids _deleteProviderIdsCommand = _connection.CreateCommand(); @@ -905,7 +908,7 @@ namespace MediaBrowser.Server.Implementations.Persistence UpdateUserDataKeys(item.Id, item.GetUserDataKeys().Distinct(StringComparer.OrdinalIgnoreCase).ToList(), transaction); UpdateImages(item.Id, item.ImageInfos, transaction); UpdateProviderIds(item.Id, item.ProviderIds, transaction); - UpdateItemValues(item.Id, GetItemValues(item), transaction); + UpdateItemValues(item.Id, GetItemValuesToSave(item), transaction); } transaction.Commit(); @@ -2019,7 +2022,7 @@ namespace MediaBrowser.Server.Implementations.Persistence } if (string.Equals(name, ItemSortBy.SeriesDatePlayed, StringComparison.OrdinalIgnoreCase)) { - return new Tuple("(Select MAX(LastPlayedDate) from TypedBaseItems B"+ GetJoinUserDataText(query) + " where B.Guid in (Select ItemId from AncestorIds where AncestorId in (select guid from typedbaseitems c where C.Type = 'MediaBrowser.Controller.Entities.TV.Series' And C.Guid in (Select AncestorId from AncestorIds where ItemId=A.Guid))))", false); + return new Tuple("(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText(query) + " where B.Guid in (Select ItemId from AncestorIds where AncestorId in (select guid from typedbaseitems c where C.Type = 'MediaBrowser.Controller.Entities.TV.Series' And C.Guid in (Select AncestorId from AncestorIds where ItemId=A.Guid))))", false); } return new Tuple(name, false); @@ -2245,7 +2248,7 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - private List GetWhereClauses(InternalItemsQuery query, IDbCommand cmd) + private List GetWhereClauses(InternalItemsQuery query, IDbCommand cmd, string paramSuffix = "") { var whereClauses = new List(); @@ -2321,8 +2324,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var includeTypes = query.IncludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray(); if (includeTypes.Length == 1) { - whereClauses.Add("type=@type"); - cmd.Parameters.Add(cmd, "@type", DbType.String).Value = includeTypes[0]; + whereClauses.Add("type=@type" + paramSuffix); + cmd.Parameters.Add(cmd, "@type" + paramSuffix, DbType.String).Value = includeTypes[0]; } else if (includeTypes.Length > 1) { @@ -2626,8 +2629,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var artist in query.ArtistNames) { - clauses.Add("@ArtistName" + index + " in (select value from itemvalues where ItemId=Guid and Type <= 1)"); - cmd.Parameters.Add(cmd, "@ArtistName" + index, DbType.String).Value = artist; + clauses.Add("@ArtistName" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type <= 1)"); + cmd.Parameters.Add(cmd, "@ArtistName" + index, DbType.String).Value = artist.RemoveDiacritics(); index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2640,8 +2643,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Genres) { - clauses.Add("@Genre" + index + " in (select value from itemvalues where ItemId=Guid and Type=2)"); - cmd.Parameters.Add(cmd, "@Genre" + index, DbType.String).Value = item; + clauses.Add("@Genre" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=2)"); + cmd.Parameters.Add(cmd, "@Genre" + index, DbType.String).Value = item.RemoveDiacritics(); index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2654,8 +2657,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Tags) { - clauses.Add("@Tag" + index + " in (select value from itemvalues where ItemId=Guid and Type=4)"); - cmd.Parameters.Add(cmd, "@Tag" + index, DbType.String).Value = item; + clauses.Add("@Tag" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=4)"); + cmd.Parameters.Add(cmd, "@Tag" + index, DbType.String).Value = item.RemoveDiacritics(); index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2668,8 +2671,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Studios) { - clauses.Add("@Studio" + index + " in (select value from itemvalues where ItemId=Guid and Type=3)"); - cmd.Parameters.Add(cmd, "@Studio" + index, DbType.String).Value = item; + clauses.Add("@Studio" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=3)"); + cmd.Parameters.Add(cmd, "@Studio" + index, DbType.String).Value = item.RemoveDiacritics(); index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2682,8 +2685,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Keywords) { - clauses.Add("@Keyword" + index + " in (select value from itemvalues where ItemId=Guid and Type=5)"); - cmd.Parameters.Add(cmd, "@Keyword" + index, DbType.String).Value = item; + clauses.Add("@Keyword" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=5)"); + cmd.Parameters.Add(cmd, "@Keyword" + index, DbType.String).Value = item.RemoveDiacritics(); index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2812,6 +2815,20 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add("MediaType in (" + val + ")"); } + if (query.ItemIds.Length > 0) + { + var excludeIds = new List(); + + var index = 0; + foreach (var id in query.ItemIds) + { + excludeIds.Add("Guid = @IncludeId" + index); + cmd.Parameters.Add(cmd, "@IncludeId" + index, DbType.Guid).Value = new Guid(id); + index++; + } + + whereClauses.Add(string.Join(" OR ", excludeIds.ToArray())); + } if (query.ExcludeItemIds.Length > 0) { var excludeIds = new List(); @@ -3478,7 +3495,292 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - private List> GetItemValues(BaseItem item) + public QueryResult> GetArtists(InternalItemsQuery query) + { + return GetItemValues(query, 0, typeof(MusicArtist).FullName); + } + + public QueryResult> GetAlbumArtists(InternalItemsQuery query) + { + return GetItemValues(query, 1, typeof(MusicArtist).FullName); + } + + public QueryResult> GetStudios(InternalItemsQuery query) + { + return GetItemValues(query, 3, typeof(Studio).FullName); + } + + public QueryResult> GetGenres(InternalItemsQuery query) + { + return GetItemValues(query, 2, typeof(Genre).FullName); + } + + public QueryResult> GetGameGenres(InternalItemsQuery query) + { + return GetItemValues(query, 2, typeof(GameGenre).FullName); + } + + public QueryResult> GetMusicGenres(InternalItemsQuery query) + { + return GetItemValues(query, 2, typeof(MusicGenre).FullName); + } + + private QueryResult> GetItemValues(InternalItemsQuery query, int itemValueType, string returnType) + { + if (query == null) + { + throw new ArgumentNullException("query"); + } + + if (!query.Limit.HasValue) + { + query.EnableTotalRecordCount = false; + } + + CheckDisposed(); + + var now = DateTime.UtcNow; + + using (var cmd = _connection.CreateCommand()) + { + var itemCountColumns = new List>(); + + var typesToCount = query.IncludeItemTypes.ToList(); + + if (typesToCount.Count == 0) + { + //typesToCount.Add("Item"); + } + + foreach (var type in typesToCount) + { + var itemCountColumnQuery = "Select Count(Value) from ItemValues where ItemValues.CleanValue=CleanName AND Type=@ItemValueType AND ItemId in ("; + itemCountColumnQuery += "select guid" + GetFromText(); + + var typeSubQuery = new InternalItemsQuery(query.User) + { + ExcludeItemTypes = query.ExcludeItemTypes, + MediaTypes = query.MediaTypes, + AncestorIds = query.AncestorIds, + ExcludeItemIds = query.ExcludeItemIds, + ItemIds = query.ItemIds, + TopParentIds = query.TopParentIds, + ParentId = query.ParentId, + IsPlayed = query.IsPlayed + }; + if (string.Equals(type, "Item", StringComparison.OrdinalIgnoreCase)) + { + typeSubQuery.IncludeItemTypes = query.IncludeItemTypes; + } + else + { + typeSubQuery.IncludeItemTypes = new[] { type }; + } + var whereClauses = GetWhereClauses(typeSubQuery, cmd, type); + + var typeWhereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); + + itemCountColumnQuery += typeWhereText; + + itemCountColumnQuery += ")"; + + var columnName = type + "Count"; + + itemCountColumns.Add(new Tuple(columnName, "(" + itemCountColumnQuery + ") as " + columnName)); + } + + var columns = _retriveItemColumns.ToList(); + columns.AddRange(itemCountColumns.Select(i => i.Item2).ToArray()); + + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, columns.ToArray(), cmd)) + GetFromText(); + cmd.CommandText += GetJoinUserDataText(query); + + var innerQuery = new InternalItemsQuery(query.User) + { + ExcludeItemTypes = query.ExcludeItemTypes, + IncludeItemTypes = query.IncludeItemTypes, + MediaTypes = query.MediaTypes, + AncestorIds = query.AncestorIds, + ExcludeItemIds = query.ExcludeItemIds, + ItemIds = query.ItemIds, + TopParentIds = query.TopParentIds, + ParentId = query.ParentId, + IsPlayed = query.IsPlayed + }; + + var innerWhereClauses = GetWhereClauses(innerQuery, cmd); + + var innerWhereText = innerWhereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", innerWhereClauses.ToArray()); + + var whereText = " where Type=@SelectType"; + whereText += " And CleanName In (Select CleanValue from ItemValues where Type=@ItemValueType AND ItemId in (select guid from TypedBaseItems" + innerWhereText + "))"; + cmd.CommandText += whereText; + + var outerQuery = new InternalItemsQuery(query.User) + { + IsFavorite = query.IsFavorite, + IsFavoriteOrLiked = query.IsFavoriteOrLiked, + IsLiked = query.IsLiked, + IsLocked = query.IsLocked, + NameLessThan = query.NameLessThan, + NameStartsWith = query.NameStartsWith, + NameStartsWithOrGreater = query.NameStartsWithOrGreater, + AlbumArtistStartsWithOrGreater = query.AlbumArtistStartsWithOrGreater, + Tags = query.Tags, + OfficialRatings = query.OfficialRatings, + Genres = query.GenreIds, + Years = query.Years + }; + + var outerWhereClauses = GetWhereClauses(outerQuery, cmd); + + var outerWhereText = outerWhereClauses.Count == 0 ? + string.Empty : + " AND " + string.Join(" AND ", outerWhereClauses.ToArray()); + cmd.CommandText += outerWhereText; + + cmd.Parameters.Add(cmd, "@SelectType", DbType.String).Value = returnType; + cmd.Parameters.Add(cmd, "@ItemValueType", DbType.Int32).Value = itemValueType; + + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } + + //cmd.CommandText += GetGroupBy(query); + cmd.CommandText += " group by PresentationUniqueKey"; + + cmd.CommandText += " order by SortName"; + + if (query.Limit.HasValue || query.StartIndex.HasValue) + { + var limit = query.Limit ?? int.MaxValue; + + cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); + + if (query.StartIndex.HasValue) + { + cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + } + } + + cmd.CommandText += ";"; + + var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; + + if (isReturningZeroItems) + { + cmd.CommandText = ""; + } + + if (query.EnableTotalRecordCount) + { + cmd.CommandText += "select count (guid)" + GetFromText(); + + cmd.CommandText += GetJoinUserDataText(query); + cmd.CommandText += whereText; + } + else + { + cmd.CommandText = cmd.CommandText.TrimEnd(';'); + } + + var list = new List>(); + var count = 0; + + var commandBehavior = isReturningZeroItems || !query.EnableTotalRecordCount + ? (CommandBehavior.SequentialAccess | CommandBehavior.SingleResult) + : CommandBehavior.SequentialAccess; + + using (var reader = cmd.ExecuteReader(commandBehavior)) + { + LogQueryTime("GetItemValues", cmd, now); + + if (isReturningZeroItems) + { + if (reader.Read()) + { + count = reader.GetInt32(0); + } + } + else + { + while (reader.Read()) + { + var item = GetItem(reader); + if (item != null) + { + var countStartColumn = columns.Count - typesToCount.Count; + + list.Add(new Tuple(item, GetItemCounts(reader, countStartColumn, typesToCount))); + } + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } + } + } + + if (count == 0) + { + count = list.Count; + } + + return new QueryResult> + { + Items = list.ToArray(), + TotalRecordCount = count + }; + + } + } + + private ItemCounts GetItemCounts(IDataReader reader, int countStartColumn, List typesToCount) + { + var counts = new ItemCounts(); + + for (var i = 0; i < typesToCount.Count; i++) + { + var value = reader.GetInt32(countStartColumn + i); + + var type = typesToCount[i]; + if (string.Equals(type, "Series", StringComparison.OrdinalIgnoreCase)) + { + counts.SeriesCount = value; + } + else if (string.Equals(type, "Episode", StringComparison.OrdinalIgnoreCase)) + { + counts.EpisodeCount = value; + } + else if (string.Equals(type, "Movie", StringComparison.OrdinalIgnoreCase)) + { + counts.MovieCount = value; + } + else if (string.Equals(type, "MusicAlbum", StringComparison.OrdinalIgnoreCase)) + { + counts.AlbumCount = value; + } + else if (string.Equals(type, "Audio", StringComparison.OrdinalIgnoreCase)) + { + counts.SongCount = value; + } + else if (string.Equals(type, "Game", StringComparison.OrdinalIgnoreCase)) + { + counts.GameCount = value; + } + counts.ItemCount += value; + } + + return counts; + } + + private List> GetItemValuesToSave(BaseItem item) { var list = new List>(); @@ -3604,6 +3906,14 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemValuesCommand.GetParameter(0).Value = itemId; _saveItemValuesCommand.GetParameter(1).Value = pair.Item1; _saveItemValuesCommand.GetParameter(2).Value = pair.Item2; + if (pair.Item2 == null) + { + _saveItemValuesCommand.GetParameter(3).Value = null; + } + else + { + _saveItemValuesCommand.GetParameter(3).Value = pair.Item2.RemoveDiacritics(); + } _saveItemValuesCommand.Transaction = transaction; _saveItemValuesCommand.ExecuteNonQuery(); diff --git a/MediaBrowser.Server.Implementations/packages.config b/MediaBrowser.Server.Implementations/packages.config index 42fc51a63..326721ff3 100644 --- a/MediaBrowser.Server.Implementations/packages.config +++ b/MediaBrowser.Server.Implementations/packages.config @@ -4,7 +4,7 @@ - + -- cgit v1.2.3 From a75f24e8e15a0ff89da2047f7535047adc8b9f51 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Mon, 20 Jun 2016 18:07:18 -0400 Subject: add temp file with recording conversion --- .../Persistence/IItemRepository.cs | 2 +- .../Dto/DtoService.cs | 2 +- .../LiveTv/EmbyTV/EmbyTV.cs | 9 ++- .../LiveTv/EmbyTV/EncodedRecorder.cs | 89 ++++++++++++++++++---- .../LiveTv/EmbyTV/TimerManager.cs | 12 ++- .../Persistence/SqliteItemRepository.cs | 22 +++++- 6 files changed, 112 insertions(+), 24 deletions(-) (limited to 'MediaBrowser.Controller/Persistence') diff --git a/MediaBrowser.Controller/Persistence/IItemRepository.cs b/MediaBrowser.Controller/Persistence/IItemRepository.cs index 2ef8b70f0..78138999c 100644 --- a/MediaBrowser.Controller/Persistence/IItemRepository.cs +++ b/MediaBrowser.Controller/Persistence/IItemRepository.cs @@ -154,7 +154,7 @@ namespace MediaBrowser.Controller.Persistence /// /// The query. /// List<BaseItem>. - IEnumerable GetItemList(InternalItemsQuery query); + List GetItemList(InternalItemsQuery query); /// /// Updates the inherited values. diff --git a/MediaBrowser.Server.Implementations/Dto/DtoService.cs b/MediaBrowser.Server.Implementations/Dto/DtoService.cs index a4cf90e5b..f21dd27ff 100644 --- a/MediaBrowser.Server.Implementations/Dto/DtoService.cs +++ b/MediaBrowser.Server.Implementations/Dto/DtoService.cs @@ -1306,7 +1306,7 @@ namespace MediaBrowser.Server.Implementations.Dto ItemIds = new[] { item.Id.ToString("N") } }); - dto.ArtistItems = artistItems.Items + dto.AlbumArtists = artistItems.Items .Select(i => { var artist = i.Item1; diff --git a/MediaBrowser.Server.Implementations/LiveTv/EmbyTV/EmbyTV.cs b/MediaBrowser.Server.Implementations/LiveTv/EmbyTV/EmbyTV.cs index 1b43ff7c1..29c1d1c9a 100644 --- a/MediaBrowser.Server.Implementations/LiveTv/EmbyTV/EmbyTV.cs +++ b/MediaBrowser.Server.Implementations/LiveTv/EmbyTV/EmbyTV.cs @@ -1059,6 +1059,9 @@ namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV if (recordingStatus == RecordingStatus.Completed) { + timer.Status = RecordingStatus.Completed; + _timerProvider.AddOrUpdate(timer); + OnSuccessfulRecording(info.IsSeries, recordPath); _timerProvider.Delete(timer); } @@ -1067,7 +1070,9 @@ namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV const int retryIntervalSeconds = 60; _logger.Info("Retrying recording in {0} seconds.", retryIntervalSeconds); - _timerProvider.StartTimer(timer, TimeSpan.FromSeconds(retryIntervalSeconds)); + timer.Status = RecordingStatus.New; + timer.StartDate = DateTime.UtcNow.AddSeconds(retryIntervalSeconds); + _timerProvider.AddOrUpdate(timer); } else { @@ -1119,7 +1124,7 @@ namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV if (regInfo.IsValid) { - return new EncodedRecorder(_logger, _fileSystem, _mediaEncoder, _config.ApplicationPaths, _jsonSerializer, config); + return new EncodedRecorder(_logger, _fileSystem, _mediaEncoder, _config.ApplicationPaths, _jsonSerializer, config, _httpClient); } } diff --git a/MediaBrowser.Server.Implementations/LiveTv/EmbyTV/EncodedRecorder.cs b/MediaBrowser.Server.Implementations/LiveTv/EmbyTV/EncodedRecorder.cs index 13158d346..6704dc139 100644 --- a/MediaBrowser.Server.Implementations/LiveTv/EmbyTV/EncodedRecorder.cs +++ b/MediaBrowser.Server.Implementations/LiveTv/EmbyTV/EncodedRecorder.cs @@ -8,7 +8,9 @@ using System.Text; using System.Threading; using System.Threading.Tasks; using CommonIO; -using MediaBrowser.Common.Configuration; +using MediaBrowser.Common.IO; +using MediaBrowser.Common.Net; +using MediaBrowser.Controller; using MediaBrowser.Controller.MediaEncoding; using MediaBrowser.Model.Dto; using MediaBrowser.Model.Entities; @@ -22,8 +24,9 @@ namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV { private readonly ILogger _logger; private readonly IFileSystem _fileSystem; + private readonly IHttpClient _httpClient; private readonly IMediaEncoder _mediaEncoder; - private readonly IApplicationPaths _appPaths; + private readonly IServerApplicationPaths _appPaths; private readonly LiveTvOptions _liveTvOptions; private bool _hasExited; private Stream _logFileStream; @@ -32,7 +35,7 @@ namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV private readonly IJsonSerializer _json; private readonly TaskCompletionSource _taskCompletionSource = new TaskCompletionSource(); - public EncodedRecorder(ILogger logger, IFileSystem fileSystem, IMediaEncoder mediaEncoder, IApplicationPaths appPaths, IJsonSerializer json, LiveTvOptions liveTvOptions) + public EncodedRecorder(ILogger logger, IFileSystem fileSystem, IMediaEncoder mediaEncoder, IServerApplicationPaths appPaths, IJsonSerializer json, LiveTvOptions liveTvOptions, IHttpClient httpClient) { _logger = logger; _fileSystem = fileSystem; @@ -40,6 +43,7 @@ namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV _appPaths = appPaths; _json = json; _liveTvOptions = liveTvOptions; + _httpClient = httpClient; } public string GetOutputPath(MediaSourceInfo mediaSource, string targetFile) @@ -49,20 +53,73 @@ namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV public async Task Record(MediaSourceInfo mediaSource, string targetFile, TimeSpan duration, Action onStarted, CancellationToken cancellationToken) { - if (mediaSource.RunTimeTicks.HasValue) + var tempfile = Path.Combine(_appPaths.TranscodingTempPath, Guid.NewGuid().ToString("N") + ".ts"); + + try { - // The media source already has a fixed duration - // But add another stop 1 minute later just in case the recording gets stuck for any reason - var durationToken = new CancellationTokenSource(duration.Add(TimeSpan.FromMinutes(1))); - cancellationToken = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken, durationToken.Token).Token; + await RecordInternal(mediaSource, tempfile, targetFile, duration, onStarted, cancellationToken) + .ConfigureAwait(false); } - else + finally + { + File.Delete(tempfile); + } + } + + public async Task RecordInternal(MediaSourceInfo mediaSource, string tempFile, string targetFile, TimeSpan duration, Action onStarted, CancellationToken cancellationToken) + { + var httpRequestOptions = new HttpRequestOptions() { - // The media source if infinite so we need to handle stopping ourselves - var durationToken = new CancellationTokenSource(duration); - cancellationToken = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken, durationToken.Token).Token; + Url = mediaSource.Path + }; + + httpRequestOptions.BufferContent = false; + + using (var response = await _httpClient.SendAsync(httpRequestOptions, "GET").ConfigureAwait(false)) + { + _logger.Info("Opened recording stream from tuner provider"); + + Directory.CreateDirectory(Path.GetDirectoryName(tempFile)); + + using (var output = _fileSystem.GetFileStream(tempFile, FileMode.Create, FileAccess.Write, FileShare.Read)) + { + //onStarted(); + + _logger.Info("Copying recording stream to file {0}", tempFile); + + var bufferMs = 5000; + + if (mediaSource.RunTimeTicks.HasValue) + { + // The media source already has a fixed duration + // But add another stop 1 minute later just in case the recording gets stuck for any reason + var durationToken = new CancellationTokenSource(duration.Add(TimeSpan.FromMinutes(1))); + cancellationToken = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken, durationToken.Token).Token; + } + else + { + // The media source if infinite so we need to handle stopping ourselves + var durationToken = new CancellationTokenSource(duration.Add(TimeSpan.FromMilliseconds(bufferMs))); + cancellationToken = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken, durationToken.Token).Token; + } + + var tempFileTask = response.Content.CopyToAsync(output, StreamDefaults.DefaultCopyToBufferSize, cancellationToken); + + // Give the temp file a little time to build up + await Task.Delay(bufferMs, cancellationToken).ConfigureAwait(false); + + await RecordFromFile(mediaSource, tempFile, targetFile, onStarted, cancellationToken) + .ConfigureAwait(false); + + await tempFileTask.ConfigureAwait(false); + } } + _logger.Info("Recording completed to file {0}", targetFile); + } + + private async Task RecordFromFile(MediaSourceInfo mediaSource, string inputFile, string targetFile, Action onStarted, CancellationToken cancellationToken) + { _targetPath = targetFile; _fileSystem.CreateDirectory(Path.GetDirectoryName(targetFile)); @@ -79,7 +136,7 @@ namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV RedirectStandardInput = true, FileName = _mediaEncoder.EncoderPath, - Arguments = GetCommandLineArgs(mediaSource, targetFile, duration), + Arguments = GetCommandLineArgs(mediaSource, inputFile, targetFile), WindowStyle = ProcessWindowStyle.Hidden, ErrorDialog = false @@ -119,7 +176,7 @@ namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV await _taskCompletionSource.Task.ConfigureAwait(false); } - private string GetCommandLineArgs(MediaSourceInfo mediaSource, string targetFile, TimeSpan duration) + private string GetCommandLineArgs(MediaSourceInfo mediaSource, string inputTempFile, string targetFile) { string videoArgs; if (EncodeVideo(mediaSource)) @@ -135,14 +192,14 @@ namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV videoArgs = "-codec:v:0 copy"; } - var commandLineArgs = "-fflags +genpts -async 1 -vsync -1 -re -i \"{0}\" -t {4} -sn {2} -map_metadata -1 -threads 0 {3} -y \"{1}\""; + var commandLineArgs = "-fflags +genpts -async 1 -vsync -1 -re -i \"{0}\" -sn {2} -map_metadata -1 -threads 0 {3} -y \"{1}\""; if (mediaSource.ReadAtNativeFramerate) { commandLineArgs = "-re " + commandLineArgs; } - commandLineArgs = string.Format(commandLineArgs, mediaSource.Path, targetFile, videoArgs, GetAudioArgs(mediaSource), _mediaEncoder.GetTimeParameter(duration.Ticks)); + commandLineArgs = string.Format(commandLineArgs, inputTempFile, targetFile, videoArgs, GetAudioArgs(mediaSource)); return commandLineArgs; } diff --git a/MediaBrowser.Server.Implementations/LiveTv/EmbyTV/TimerManager.cs b/MediaBrowser.Server.Implementations/LiveTv/EmbyTV/TimerManager.cs index 5d462f106..bcad80447 100644 --- a/MediaBrowser.Server.Implementations/LiveTv/EmbyTV/TimerManager.cs +++ b/MediaBrowser.Server.Implementations/LiveTv/EmbyTV/TimerManager.cs @@ -10,6 +10,7 @@ using System.Linq; using System.Threading; using CommonIO; using MediaBrowser.Controller.Power; +using MediaBrowser.Model.LiveTv; namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV { @@ -85,6 +86,11 @@ namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV private void AddTimer(TimerInfo item) { + if (item.Status == RecordingStatus.Completed) + { + return; + } + var startDate = RecordingHelper.GetStartTime(item); var now = DateTime.UtcNow; @@ -117,15 +123,15 @@ namespace MediaBrowser.Server.Implementations.LiveTv.EmbyTV } } - public void StartTimer(TimerInfo item, TimeSpan length) + public void StartTimer(TimerInfo item, TimeSpan dueTime) { StopTimer(item); - var timer = new Timer(TimerCallback, item.Id, length, TimeSpan.Zero); + var timer = new Timer(TimerCallback, item.Id, dueTime, TimeSpan.Zero); if (_timers.TryAdd(item.Id, timer)) { - _logger.Info("Creating recording timer for {0}, {1}. Timer will fire in {2} minutes", item.Id, item.Name, length.TotalMinutes.ToString(CultureInfo.InvariantCulture)); + _logger.Info("Creating recording timer for {0}, {1}. Timer will fire in {2} minutes", item.Id, item.Name, dueTime.TotalMinutes.ToString(CultureInfo.InvariantCulture)); } else { diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 006cb2edf..e98bb49d6 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -1742,7 +1742,7 @@ namespace MediaBrowser.Server.Implementations.Persistence return " from TypedBaseItems A"; } - public IEnumerable GetItemList(InternalItemsQuery query) + public List GetItemList(InternalItemsQuery query) { if (query == null) { @@ -1842,6 +1842,16 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); + if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0)) + { + var list = GetItemList(query); + return new QueryResult + { + Items = list.ToArray(), + TotalRecordCount = list.Count + }; + } + var now = DateTime.UtcNow; using (var cmd = _connection.CreateCommand()) @@ -2196,6 +2206,16 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); + if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0)) + { + var list = GetItemIdsList(query); + return new QueryResult + { + Items = list.ToArray(), + TotalRecordCount = list.Count + }; + } + var now = DateTime.UtcNow; using (var cmd = _connection.CreateCommand()) -- cgit v1.2.3