diff options
Diffstat (limited to 'Emby.Server.Implementations/Data/SqliteItemRepository.cs')
| -rw-r--r-- | Emby.Server.Implementations/Data/SqliteItemRepository.cs | 157 |
1 files changed, 88 insertions, 69 deletions
diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 1c2aeda70..7e01bd4b6 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -1007,7 +1007,7 @@ namespace Emby.Server.Implementations.Data return; } - var parts = value.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + var parts = value.Split('|', StringSplitOptions.RemoveEmptyEntries); foreach (var part in parts) { @@ -1057,7 +1057,7 @@ namespace Emby.Server.Implementations.Data return; } - var parts = value.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + var parts = value.Split('|' , StringSplitOptions.RemoveEmptyEntries); var list = new List<ItemImageInfo>(); foreach (var part in parts) { @@ -1096,7 +1096,7 @@ namespace Emby.Server.Implementations.Data public ItemImageInfo ItemImageInfoFromValueString(string value) { - var parts = value.Split(new[] { '*' }, StringSplitOptions.None); + var parts = value.Split('*', StringSplitOptions.None); if (parts.Length < 3) { @@ -1532,7 +1532,7 @@ namespace Emby.Server.Implementations.Data { if (!reader.IsDBNull(index)) { - item.Genres = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + item.Genres = reader.GetString(index).Split('|', StringSplitOptions.RemoveEmptyEntries); } index++; @@ -1593,7 +1593,7 @@ namespace Emby.Server.Implementations.Data { IEnumerable<MetadataField> GetLockedFields(string s) { - foreach (var i in s.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries)) + foreach (var i in s.Split('|', StringSplitOptions.RemoveEmptyEntries)) { if (Enum.TryParse(i, true, out MetadataField parsedValue)) { @@ -1612,7 +1612,7 @@ namespace Emby.Server.Implementations.Data { if (!reader.IsDBNull(index)) { - item.Studios = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + item.Studios = reader.GetString(index).Split('|', StringSplitOptions.RemoveEmptyEntries); } index++; @@ -1622,7 +1622,7 @@ namespace Emby.Server.Implementations.Data { if (!reader.IsDBNull(index)) { - item.Tags = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + item.Tags = reader.GetString(index).Split('|', StringSplitOptions.RemoveEmptyEntries); } index++; @@ -1636,7 +1636,7 @@ namespace Emby.Server.Implementations.Data { IEnumerable<TrailerType> GetTrailerTypes(string s) { - foreach (var i in s.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries)) + foreach (var i in s.Split('|', StringSplitOptions.RemoveEmptyEntries)) { if (Enum.TryParse(i, true, out TrailerType parsedValue)) { @@ -1811,7 +1811,7 @@ namespace Emby.Server.Implementations.Data { if (!reader.IsDBNull(index)) { - item.ProductionLocations = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries).ToArray(); + item.ProductionLocations = reader.GetString(index).Split('|', StringSplitOptions.RemoveEmptyEntries).ToArray(); } index++; @@ -1848,14 +1848,14 @@ namespace Emby.Server.Implementations.Data { if (item is IHasArtist hasArtists && !reader.IsDBNull(index)) { - hasArtists.Artists = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + hasArtists.Artists = reader.GetString(index).Split('|', StringSplitOptions.RemoveEmptyEntries); } index++; if (item is IHasAlbumArtist hasAlbumArtists && !reader.IsDBNull(index)) { - hasAlbumArtists.AlbumArtists = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + hasAlbumArtists.AlbumArtists = reader.GetString(index).Split('|', StringSplitOptions.RemoveEmptyEntries); } index++; @@ -2403,11 +2403,11 @@ namespace Emby.Server.Implementations.Data if (string.IsNullOrEmpty(item.OfficialRating)) { - builder.Append("((OfficialRating is null) * 10)"); + builder.Append("(OfficialRating is null * 10)"); } else { - builder.Append("((OfficialRating=@ItemOfficialRating) * 10)"); + builder.Append("(OfficialRating=@ItemOfficialRating * 10)"); } if (item.ProductionYear.HasValue) @@ -2416,8 +2416,26 @@ namespace Emby.Server.Implementations.Data builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 5 Then 5 Else 0 End )"); } - //// genres, tags - builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId)) * 10)"); + // genres, tags, studios, person, year? + builder.Append("+ (Select count(1) * 10 from ItemValues where ItemId=Guid and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId))"); + + if (item is MusicArtist) + { + // Match albums where the artist is AlbumArtist against other albums. + // It is assumed that similar albums => similar artists. + builder.Append( + @"+ (WITH artistValues AS ( + SELECT DISTINCT albumValues.CleanValue + FROM ItemValues albumValues + INNER JOIN ItemValues artistAlbums ON albumValues.ItemId = artistAlbums.ItemId + INNER JOIN TypedBaseItems artistItem ON artistAlbums.CleanValue = artistItem.CleanName AND artistAlbums.TYPE = 1 AND artistItem.Guid = @SimilarItemId + ), similarArtist AS ( + SELECT albumValues.ItemId + FROM ItemValues albumValues + INNER JOIN ItemValues artistAlbums ON albumValues.ItemId = artistAlbums.ItemId + INNER JOIN TypedBaseItems artistItem ON artistAlbums.CleanValue = artistItem.CleanName AND artistAlbums.TYPE = 1 AND artistItem.Guid = A.Guid + ) SELECT COUNT(DISTINCT(CleanValue)) * 10 FROM ItemValues WHERE ItemId IN (SELECT ItemId FROM similarArtist) AND CleanValue IN (SELECT CleanValue FROM artistValues))"); + } builder.Append(") as SimilarityScore"); @@ -3593,12 +3611,12 @@ namespace Emby.Server.Implementations.Data whereClauses.Add($"type in ({inClause})"); } - if (query.ChannelIds.Length == 1) + if (query.ChannelIds.Count == 1) { whereClauses.Add("ChannelId=@ChannelId"); statement?.TryBind("@ChannelId", query.ChannelIds[0].ToString("N", CultureInfo.InvariantCulture)); } - else if (query.ChannelIds.Length > 1) + else if (query.ChannelIds.Count > 1) { var inClause = string.Join(",", query.ChannelIds.Select(i => "'" + i.ToString("N", CultureInfo.InvariantCulture) + "'")); whereClauses.Add($"ChannelId in ({inClause})"); @@ -3914,7 +3932,7 @@ namespace Emby.Server.Implementations.Data if (query.IsPlayed.HasValue) { // We should probably figure this out for all folders, but for right now, this is the only place where we need it - if (query.IncludeItemTypes.Length == 1 && string.Equals(query.IncludeItemTypes[0], typeof(Series).Name, StringComparison.OrdinalIgnoreCase)) + if (query.IncludeItemTypes.Length == 1 && string.Equals(query.IncludeItemTypes[0], nameof(Series), StringComparison.OrdinalIgnoreCase)) { if (query.IsPlayed.Value) { @@ -4058,7 +4076,7 @@ namespace Emby.Server.Implementations.Data whereClauses.Add(clause); } - if (query.GenreIds.Length > 0) + if (query.GenreIds.Count > 0) { var clauses = new List<string>(); var index = 0; @@ -4079,7 +4097,7 @@ namespace Emby.Server.Implementations.Data whereClauses.Add(clause); } - if (query.Genres.Length > 0) + if (query.Genres.Count > 0) { var clauses = new List<string>(); var index = 0; @@ -4755,29 +4773,29 @@ namespace Emby.Server.Implementations.Data { var list = new List<string>(); - if (IsTypeInQuery(typeof(Person).Name, query)) + if (IsTypeInQuery(nameof(Person), query)) { - list.Add(typeof(Person).Name); + list.Add(nameof(Person)); } - if (IsTypeInQuery(typeof(Genre).Name, query)) + if (IsTypeInQuery(nameof(Genre), query)) { - list.Add(typeof(Genre).Name); + list.Add(nameof(Genre)); } - if (IsTypeInQuery(typeof(MusicGenre).Name, query)) + if (IsTypeInQuery(nameof(MusicGenre), query)) { - list.Add(typeof(MusicGenre).Name); + list.Add(nameof(MusicGenre)); } - if (IsTypeInQuery(typeof(MusicArtist).Name, query)) + if (IsTypeInQuery(nameof(MusicArtist), query)) { - list.Add(typeof(MusicArtist).Name); + list.Add(nameof(MusicArtist)); } - if (IsTypeInQuery(typeof(Studio).Name, query)) + if (IsTypeInQuery(nameof(Studio), query)) { - list.Add(typeof(Studio).Name); + list.Add(nameof(Studio)); } return list; @@ -4832,12 +4850,12 @@ namespace Emby.Server.Implementations.Data var types = new[] { - typeof(Episode).Name, - typeof(Video).Name, - typeof(Movie).Name, - typeof(MusicVideo).Name, - typeof(Series).Name, - typeof(Season).Name + nameof(Episode), + nameof(Video), + nameof(Movie), + nameof(MusicVideo), + nameof(Series), + nameof(Season) }; if (types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase))) @@ -5002,26 +5020,33 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type CheckDisposed(); - var commandText = "select Distinct Name from People"; + var commandText = new StringBuilder("select Distinct p.Name from People p"); + + if (query.User != null && query.IsFavorite.HasValue) + { + commandText.Append(" LEFT JOIN TypedBaseItems tbi ON tbi.Name=p.Name AND tbi.Type='"); + commandText.Append(typeof(Person).FullName); + commandText.Append("' LEFT JOIN UserDatas ON tbi.UserDataKey=key AND userId=@UserId"); + } var whereClauses = GetPeopleWhereClauses(query, null); if (whereClauses.Count != 0) { - commandText += " where " + string.Join(" AND ", whereClauses); + commandText.Append(" where ").Append(string.Join(" AND ", whereClauses)); } - commandText += " order by ListOrder"; + commandText.Append(" order by ListOrder"); if (query.Limit > 0) { - commandText += " LIMIT " + query.Limit; + commandText.Append(" LIMIT ").Append(query.Limit); } using (var connection = GetConnection(true)) { var list = new List<string>(); - using (var statement = PrepareStatement(connection, commandText)) + using (var statement = PrepareStatement(connection, commandText.ToString())) { // Run this again to bind the params GetPeopleWhereClauses(query, statement); @@ -5045,7 +5070,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type CheckDisposed(); - var commandText = "select ItemId, Name, Role, PersonType, SortOrder from People"; + var commandText = "select ItemId, Name, Role, PersonType, SortOrder from People p"; var whereClauses = GetPeopleWhereClauses(query, null); @@ -5087,19 +5112,13 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type if (!query.ItemId.Equals(Guid.Empty)) { whereClauses.Add("ItemId=@ItemId"); - if (statement != null) - { - statement.TryBind("@ItemId", query.ItemId.ToByteArray()); - } + statement?.TryBind("@ItemId", query.ItemId.ToByteArray()); } if (!query.AppearsInItemId.Equals(Guid.Empty)) { - whereClauses.Add("Name in (Select Name from People where ItemId=@AppearsInItemId)"); - if (statement != null) - { - statement.TryBind("@AppearsInItemId", query.AppearsInItemId.ToByteArray()); - } + whereClauses.Add("p.Name in (Select Name from People where ItemId=@AppearsInItemId)"); + statement?.TryBind("@AppearsInItemId", query.AppearsInItemId.ToByteArray()); } var queryPersonTypes = query.PersonTypes.Where(IsValidPersonType).ToList(); @@ -5107,10 +5126,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type if (queryPersonTypes.Count == 1) { whereClauses.Add("PersonType=@PersonType"); - if (statement != null) - { - statement.TryBind("@PersonType", queryPersonTypes[0]); - } + statement?.TryBind("@PersonType", queryPersonTypes[0]); } else if (queryPersonTypes.Count > 1) { @@ -5124,10 +5140,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type if (queryExcludePersonTypes.Count == 1) { whereClauses.Add("PersonType<>@PersonType"); - if (statement != null) - { - statement.TryBind("@PersonType", queryExcludePersonTypes[0]); - } + statement?.TryBind("@PersonType", queryExcludePersonTypes[0]); } else if (queryExcludePersonTypes.Count > 1) { @@ -5139,19 +5152,24 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type if (query.MaxListOrder.HasValue) { whereClauses.Add("ListOrder<=@MaxListOrder"); - if (statement != null) - { - statement.TryBind("@MaxListOrder", query.MaxListOrder.Value); - } + statement?.TryBind("@MaxListOrder", query.MaxListOrder.Value); } if (!string.IsNullOrWhiteSpace(query.NameContains)) { - whereClauses.Add("Name like @NameContains"); - if (statement != null) - { - statement.TryBind("@NameContains", "%" + query.NameContains + "%"); - } + whereClauses.Add("p.Name like @NameContains"); + statement?.TryBind("@NameContains", "%" + query.NameContains + "%"); + } + + if (query.IsFavorite.HasValue) + { + whereClauses.Add("isFavorite=@IsFavorite"); + statement?.TryBind("@IsFavorite", query.IsFavorite.Value); + } + + if (query.User != null) + { + statement?.TryBind("@UserId", query.User.InternalId); } return whereClauses; @@ -5420,6 +5438,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type NameStartsWithOrGreater = query.NameStartsWithOrGreater, Tags = query.Tags, OfficialRatings = query.OfficialRatings, + StudioIds = query.StudioIds, GenreIds = query.GenreIds, Genres = query.Genres, Years = query.Years, @@ -5592,7 +5611,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type return counts; } - var allTypes = typeString.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries) + var allTypes = typeString.Split('|', StringSplitOptions.RemoveEmptyEntries) .ToLookup(x => x); foreach (var type in allTypes) |