From e1f562e16ff585b440a43029efe9db314b4de965 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Wed, 1 Jun 2016 01:50:00 -0400 Subject: calculate similarity at database level --- .../Persistence/SqliteItemRepository.cs | 80 +++++++++++++++------- 1 file changed, 57 insertions(+), 23 deletions(-) (limited to 'MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs') diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 3149352a90..131158dd2d 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -15,6 +15,7 @@ using System.Globalization; using System.IO; using System.Linq; using System.Runtime.Serialization; +using System.Text; using System.Threading; using System.Threading.Tasks; using MediaBrowser.Common.Extensions; @@ -258,6 +259,8 @@ namespace MediaBrowser.Server.Implementations.Persistence new MediaStreamColumns(_connection, Logger).AddColumns(); DataExtensions.Attach(_connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb"); + + dbConnector.BindSimilarityScoreFunction(_connection); } private readonly string[] _retriveItemColumns = @@ -1575,7 +1578,7 @@ namespace MediaBrowser.Server.Implementations.Persistence return false; } - private string[] GetFinalColumnsToSelect(InternalItemsQuery query, string[] startColumns) + private string[] GetFinalColumnsToSelect(InternalItemsQuery query, string[] startColumns, IDbCommand cmd) { var list = startColumns.ToList(); @@ -1590,6 +1593,45 @@ namespace MediaBrowser.Server.Implementations.Persistence list.Add("UserDataDb.UserData.rating"); } + if (query.SimilarTo != null) + { + var item = query.SimilarTo; + + var builder = new StringBuilder(); + builder.Append("GetSimilarityScore("); + + builder.Append("@ItemOfficialRating,"); + builder.Append("OfficialRating,"); + + builder.Append("@ItemProductionYear,"); + builder.Append("ProductionYear,"); + + builder.Append("@ItemGenres,"); + builder.Append("Genres,"); + + builder.Append("@ItemTags,"); + builder.Append("Tags,"); + + builder.Append("@ItemKeywords,"); + builder.Append("(select group_concat((Select Value from ItemValues where ItemId=Guid and Type=5), '|')),"); + + builder.Append("@ItemStudios,"); + builder.Append("Studios"); + builder.Append(") as SimilarityScore"); + + list.Add(builder.ToString()); + cmd.Parameters.Add(cmd, "@ItemOfficialRating", DbType.String).Value = item.OfficialRating; + cmd.Parameters.Add(cmd, "@ItemProductionYear", DbType.Int32).Value = item.ProductionYear ?? -1; + cmd.Parameters.Add(cmd, "@ItemGenres", DbType.String).Value = string.Join("|", item.Genres.ToArray()); + cmd.Parameters.Add(cmd, "@ItemTags", DbType.String).Value = string.Join("|", item.Tags.ToArray()); + cmd.Parameters.Add(cmd, "@ItemKeywords", DbType.String).Value = string.Join("|", item.Keywords.ToArray()); + cmd.Parameters.Add(cmd, "@ItemStudios", DbType.String).Value = string.Join("|", item.Studios.ToArray()); + + var excludeIds = query.ExcludeItemIds.ToList(); + excludeIds.Add(item.Id.ToString("N")); + query.ExcludeItemIds = excludeIds.ToArray(); + } + return list.ToArray(); } @@ -1616,7 +1658,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns)) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems"; cmd.CommandText += GetJoinUserDataText(query); if (EnableJoinUserData(query)) @@ -1706,7 +1748,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns)) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems"; cmd.CommandText += GetJoinUserDataText(query); if (EnableJoinUserData(query)) @@ -1789,6 +1831,15 @@ namespace MediaBrowser.Server.Implementations.Persistence private string GetOrderByText(InternalItemsQuery query) { + if (query.SimilarTo != null) + { + if (query.SortBy == null || query.SortBy.Length == 0) + { + query.SortBy = new[] { "SimilarityScore", "Random" }; + query.SortOrder = SortOrder.Descending; + } + } + if (query.SortBy == null || query.SortBy.Length == 0) { return string.Empty; @@ -1879,7 +1930,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" })) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems"; cmd.CommandText += GetJoinUserDataText(query); if (EnableJoinUserData(query)) @@ -2022,7 +2073,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" })) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems"; var whereClauses = GetWhereClauses(query, cmd); cmd.CommandText += GetJoinUserDataText(query); @@ -2148,24 +2199,7 @@ namespace MediaBrowser.Server.Implementations.Persistence } else { - if (query.IsMovie.Value) - { - var typeClauses = new List(); - var typeIndex = 0; - foreach (var type in alternateTypes) - { - var paramName = "@AlternateType" + typeIndex.ToString(CultureInfo.InvariantCulture); - typeClauses.Add("Type=" + paramName); - cmd.Parameters.Add(cmd, paramName, DbType.String).Value = type; - typeIndex++; - } - - whereClauses.Add("(IsMovie=@IsMovie OR " + string.Join(" OR ", typeClauses.ToArray()) + ")"); - } - else - { - whereClauses.Add("(IsMovie is null OR IsMovie=@IsMovie)"); - } + whereClauses.Add("(IsMovie is null OR IsMovie=@IsMovie)"); } cmd.Parameters.Add(cmd, "@IsMovie", DbType.Boolean).Value = query.IsMovie; } -- cgit v1.2.3 From 669af870148a92db4a851868781e2e3af4111a26 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Wed, 1 Jun 2016 02:01:43 -0400 Subject: support querying more fields --- MediaBrowser.Controller/Entities/Folder.cs | 16 ---------------- .../Persistence/SqliteItemRepository.cs | 22 ++++++++++++++++++++++ 2 files changed, 22 insertions(+), 16 deletions(-) (limited to 'MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs') diff --git a/MediaBrowser.Controller/Entities/Folder.cs b/MediaBrowser.Controller/Entities/Folder.cs index cbbd0ad686..0d307c35bc 100644 --- a/MediaBrowser.Controller/Entities/Folder.cs +++ b/MediaBrowser.Controller/Entities/Folder.cs @@ -793,11 +793,6 @@ namespace MediaBrowser.Controller.Entities Logger.Debug("Query requires post-filtering due to ItemSortBy.Metascore"); return true; } - if (query.SortBy.Contains(ItemSortBy.OfficialRating, StringComparer.OrdinalIgnoreCase)) - { - Logger.Debug("Query requires post-filtering due to ItemSortBy.OfficialRating"); - return true; - } if (query.SortBy.Contains(ItemSortBy.Players, StringComparer.OrdinalIgnoreCase)) { Logger.Debug("Query requires post-filtering due to ItemSortBy.Players"); @@ -813,11 +808,6 @@ namespace MediaBrowser.Controller.Entities Logger.Debug("Query requires post-filtering due to ItemSortBy.SeriesSortName"); return true; } - if (query.SortBy.Contains(ItemSortBy.Studio, StringComparer.OrdinalIgnoreCase)) - { - Logger.Debug("Query requires post-filtering due to ItemSortBy.Studio"); - return true; - } if (query.SortBy.Contains(ItemSortBy.VideoBitRate, StringComparer.OrdinalIgnoreCase)) { Logger.Debug("Query requires post-filtering due to ItemSortBy.VideoBitRate"); @@ -962,12 +952,6 @@ namespace MediaBrowser.Controller.Entities return true; } - if (query.OfficialRatings.Length > 0) - { - Logger.Debug("Query requires post-filtering due to OfficialRatings"); - return true; - } - if (query.IsMissing.HasValue) { Logger.Debug("Query requires post-filtering due to IsMissing"); diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 131158dd2d..9b815bc0b2 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -1913,6 +1913,14 @@ namespace MediaBrowser.Server.Implementations.Persistence { return new Tuple("(select value from itemvalues where ItemId=Guid and Type=1 LIMIT 1)", false); } + if (string.Equals(name, ItemSortBy.OfficialRating, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple("ParentalRatingValue", false); + } + if (string.Equals(name, ItemSortBy.Studio, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple("(select value from itemvalues where ItemId=Guid and Type=3 LIMIT 1)", false); + } return new Tuple(name, false); } @@ -2573,6 +2581,20 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add(clause); } + if (query.OfficialRatings.Length > 0) + { + var clauses = new List(); + var index = 0; + foreach (var item in query.OfficialRatings) + { + clauses.Add("OfficialRating=@OfficialRating" + index); + cmd.Parameters.Add(cmd, "@OfficialRating" + index, DbType.String).Value = item; + index++; + } + var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); + } + if (query.MinParentalRating.HasValue) { whereClauses.Add("InheritedParentalRatingValue<=@MinParentalRating"); -- cgit v1.2.3 From 900cf09e0329af915b36b203b4a1e03477507e04 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Wed, 1 Jun 2016 02:20:21 -0400 Subject: use similar query for intros --- .../Intros/DefaultIntroProvider.cs | 182 +-------------------- .../Persistence/SqliteItemRepository.cs | 14 +- 2 files changed, 21 insertions(+), 175 deletions(-) (limited to 'MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs') diff --git a/MediaBrowser.Server.Implementations/Intros/DefaultIntroProvider.cs b/MediaBrowser.Server.Implementations/Intros/DefaultIntroProvider.cs index df128a90b0..7c7a535cd7 100644 --- a/MediaBrowser.Server.Implementations/Intros/DefaultIntroProvider.cs +++ b/MediaBrowser.Server.Implementations/Intros/DefaultIntroProvider.cs @@ -63,16 +63,8 @@ namespace MediaBrowser.Server.Implementations.Intros ? null : _localization.GetRatingLevel(item.OfficialRating); - var random = new Random(Environment.TickCount + Guid.NewGuid().GetHashCode()); - var candidates = new List(); - var itemPeople = _libraryManager.GetPeople(item); - var allPeople = _libraryManager.GetPeople(new InternalPeopleQuery - { - AppearsInItemId = item.Id - }); - var trailerTypes = new List(); if (config.EnableIntrosFromMoviesInLibrary) @@ -105,26 +97,25 @@ namespace MediaBrowser.Server.Implementations.Intros var trailerResult = _libraryManager.GetItemList(new InternalItemsQuery { IncludeItemTypes = new[] { typeof(Trailer).Name }, - TrailerTypes = trailerTypes.ToArray() + TrailerTypes = trailerTypes.ToArray(), + SimilarTo = item, + IsPlayed = config.EnableIntrosForWatchedContent ? (bool?) null : false, + MaxParentalRating = config.EnableIntrosParentalControl ? ratingLevel : null, + Limit = config.TrailerLimit }); candidates.AddRange(trailerResult.Select(i => new ItemWithTrailer { Item = i, Type = i.SourceType == SourceType.Channel ? ItemWithTrailerType.ChannelTrailer : ItemWithTrailerType.ItemWithTrailer, - User = user, - WatchingItem = item, - WatchingItemPeople = itemPeople, - AllPeople = allPeople, - Random = random, LibraryManager = _libraryManager })); } - return GetResult(item, candidates, config, ratingLevel); + return GetResult(item, candidates, config); } - private IEnumerable GetResult(BaseItem item, IEnumerable candidates, CinemaModeConfiguration config, int? ratingLevel) + private IEnumerable GetResult(BaseItem item, IEnumerable candidates, CinemaModeConfiguration config) { var customIntros = !string.IsNullOrWhiteSpace(config.CustomIntroPath) ? GetCustomIntros(config) : @@ -134,48 +125,12 @@ namespace MediaBrowser.Server.Implementations.Intros GetMediaInfoIntros(config, item) : new List(); - var trailerLimit = config.TrailerLimit; - // Avoid implicitly captured closure - return candidates.Where(i => - { - if (config.EnableIntrosParentalControl && !FilterByParentalRating(ratingLevel, i.Item)) - { - return false; - } - - if (!config.EnableIntrosForWatchedContent && i.IsPlayed) - { - return false; - } - return !IsDuplicate(item, i.Item); - }) - .OrderByDescending(i => i.Score) - .ThenBy(i => Guid.NewGuid()) - .ThenByDescending(i => i.IsPlayed ? 0 : 1) - .Select(i => i.IntroInfo) - .Take(trailerLimit) + return candidates.Select(i => i.IntroInfo) .Concat(customIntros.Take(1)) .Concat(mediaInfoIntros); } - private bool IsDuplicate(BaseItem playingContent, BaseItem test) - { - var id = playingContent.GetProviderId(MetadataProviders.Imdb); - if (!string.IsNullOrWhiteSpace(id) && string.Equals(id, test.GetProviderId(MetadataProviders.Imdb), StringComparison.OrdinalIgnoreCase)) - { - return true; - } - - id = playingContent.GetProviderId(MetadataProviders.Tmdb); - if (!string.IsNullOrWhiteSpace(id) && string.Equals(id, test.GetProviderId(MetadataProviders.Tmdb), StringComparison.OrdinalIgnoreCase)) - { - return true; - } - - return false; - } - private CinemaModeConfiguration GetOptions() { return _serverConfig.GetConfiguration("cinemamode"); @@ -346,96 +301,6 @@ namespace MediaBrowser.Server.Implementations.Intros return list.Distinct(StringComparer.OrdinalIgnoreCase); } - private bool FilterByParentalRating(int? ratingLevel, BaseItem item) - { - // Only content rated same or lower - if (ratingLevel.HasValue) - { - var level = string.IsNullOrWhiteSpace(item.OfficialRating) - ? (int?)null - : _localization.GetRatingLevel(item.OfficialRating); - - return level.HasValue && level.Value <= ratingLevel.Value; - } - - return true; - } - - internal static int GetSimiliarityScore(BaseItem item1, List item1People, List allPeople, BaseItem item2, Random random, ILibraryManager libraryManager) - { - var points = 0; - - if (!string.IsNullOrEmpty(item1.OfficialRating) && string.Equals(item1.OfficialRating, item2.OfficialRating, StringComparison.OrdinalIgnoreCase)) - { - points += 10; - } - - // Find common genres - points += item1.Genres.Where(i => item2.Genres.Contains(i, StringComparer.OrdinalIgnoreCase)).Sum(i => 10); - - // Find common tags - points += GetTags(item1).Where(i => GetTags(item2).Contains(i, StringComparer.OrdinalIgnoreCase)).Sum(i => 10); - - // Find common keywords - points += GetKeywords(item1).Where(i => GetKeywords(item2).Contains(i, StringComparer.OrdinalIgnoreCase)).Sum(i => 10); - - // Find common studios - points += item1.Studios.Where(i => item2.Studios.Contains(i, StringComparer.OrdinalIgnoreCase)).Sum(i => 5); - - var item2PeopleNames = allPeople.Where(i => i.ItemId == item2.Id) - .Select(i => i.Name) - .Where(i => !string.IsNullOrWhiteSpace(i)) - .DistinctNames() - .ToDictionary(i => i, StringComparer.OrdinalIgnoreCase); - - points += item1People.Where(i => item2PeopleNames.ContainsKey(i.Name)).Sum(i => - { - if (string.Equals(i.Type, PersonType.Director, StringComparison.OrdinalIgnoreCase) || string.Equals(i.Role, PersonType.Director, StringComparison.OrdinalIgnoreCase)) - { - return 5; - } - if (string.Equals(i.Type, PersonType.Actor, StringComparison.OrdinalIgnoreCase) || string.Equals(i.Role, PersonType.Actor, StringComparison.OrdinalIgnoreCase)) - { - return 3; - } - if (string.Equals(i.Type, PersonType.Composer, StringComparison.OrdinalIgnoreCase) || string.Equals(i.Role, PersonType.Composer, StringComparison.OrdinalIgnoreCase)) - { - return 3; - } - if (string.Equals(i.Type, PersonType.GuestStar, StringComparison.OrdinalIgnoreCase) || string.Equals(i.Role, PersonType.GuestStar, StringComparison.OrdinalIgnoreCase)) - { - return 3; - } - if (string.Equals(i.Type, PersonType.Writer, StringComparison.OrdinalIgnoreCase) || string.Equals(i.Role, PersonType.Writer, StringComparison.OrdinalIgnoreCase)) - { - return 2; - } - - return 1; - }); - - // Add some randomization so that you're not always seeing the same ones for a given movie - points += random.Next(0, 50); - - return points; - } - - private static IEnumerable GetTags(BaseItem item) - { - var hasTags = item as IHasTags; - if (hasTags != null) - { - return hasTags.Tags; - } - - return new List(); - } - - private static IEnumerable GetKeywords(BaseItem item) - { - return item.Keywords; - } - public IEnumerable GetAllIntroFiles() { return GetCustomIntroFiles(GetOptions(), true, true); @@ -455,39 +320,8 @@ namespace MediaBrowser.Server.Implementations.Intros { internal BaseItem Item; internal ItemWithTrailerType Type; - internal User User; - internal BaseItem WatchingItem; - internal List WatchingItemPeople; - internal List AllPeople; - internal Random Random; internal ILibraryManager LibraryManager; - private bool? _isPlayed; - public bool IsPlayed - { - get - { - if (!_isPlayed.HasValue) - { - _isPlayed = Item.IsPlayed(User); - } - return _isPlayed.Value; - } - } - - private int? _score; - public int Score - { - get - { - if (!_score.HasValue) - { - _score = GetSimiliarityScore(WatchingItem, WatchingItemPeople, AllPeople, Item, Random, LibraryManager); - } - return _score.Value; - } - } - public IntroInfo IntroInfo { get diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 9b815bc0b2..c00c4bf300 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -1526,6 +1526,11 @@ namespace MediaBrowser.Server.Implementations.Persistence return false; } + if (query.SimilarTo != null) + { + return true; + } + if (query.SortBy != null && query.SortBy.Length > 0) { if (query.SortBy.Contains(ItemSortBy.IsFavoriteOrLiked, StringComparer.OrdinalIgnoreCase)) @@ -1835,7 +1840,14 @@ namespace MediaBrowser.Server.Implementations.Persistence { if (query.SortBy == null || query.SortBy.Length == 0) { - query.SortBy = new[] { "SimilarityScore", "Random" }; + if (query.User != null) + { + query.SortBy = new[] { "SimilarityScore", "IsUnplayed", "Random" }; + } + else + { + query.SortBy = new[] { "SimilarityScore", "Random" }; + } query.SortOrder = SortOrder.Descending; } } -- cgit v1.2.3