diff options
| -rw-r--r-- | Emby.Server.Implementations/Data/SqliteItemRepository.cs | 1805 | ||||
| -rw-r--r-- | Jellyfin.Data/Entities/PeopleKind.cs | 133 | ||||
| -rw-r--r-- | Jellyfin.Server.Implementations/Item/BaseItemManager.cs | 434 | ||||
| -rw-r--r-- | Jellyfin.Server.Implementations/Item/MediaStreamManager.cs | 201 | ||||
| -rw-r--r-- | Jellyfin.Server.Implementations/Item/PeopleManager.cs | 164 |
5 files changed, 796 insertions, 1941 deletions
diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 26255e6aa..a650f9555 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -146,358 +146,6 @@ namespace Emby.Server.Implementations.Data || query.IsLiked.HasValue; } - private bool HasField(InternalItemsQuery query, ItemFields name) - { - switch (name) - { - case ItemFields.Tags: - return query.DtoOptions.ContainsField(name) || HasProgramAttributes(query); - case ItemFields.CustomRating: - case ItemFields.ProductionLocations: - case ItemFields.Settings: - case ItemFields.OriginalTitle: - case ItemFields.Taglines: - case ItemFields.SortName: - case ItemFields.Studios: - case ItemFields.ExtraIds: - case ItemFields.DateCreated: - case ItemFields.Overview: - case ItemFields.Genres: - case ItemFields.DateLastMediaAdded: - case ItemFields.PresentationUniqueKey: - case ItemFields.InheritedParentalRatingValue: - case ItemFields.ExternalSeriesId: - case ItemFields.SeriesPresentationUniqueKey: - case ItemFields.DateLastRefreshed: - case ItemFields.DateLastSaved: - return query.DtoOptions.ContainsField(name); - case ItemFields.ServiceName: - return HasServiceName(query); - default: - return true; - } - } - - private bool HasProgramAttributes(InternalItemsQuery query) - { - if (query.ParentType is not null && _programExcludeParentTypes.Contains(query.ParentType.Value)) - { - return false; - } - - if (query.IncludeItemTypes.Length == 0) - { - return true; - } - - return query.IncludeItemTypes.Any(x => _programTypes.Contains(x)); - } - - private bool HasServiceName(InternalItemsQuery query) - { - if (query.ParentType is not null && _programExcludeParentTypes.Contains(query.ParentType.Value)) - { - return false; - } - - if (query.IncludeItemTypes.Length == 0) - { - return true; - } - - return query.IncludeItemTypes.Any(x => _serviceTypes.Contains(x)); - } - - private bool HasStartDate(InternalItemsQuery query) - { - if (query.ParentType is not null && _programExcludeParentTypes.Contains(query.ParentType.Value)) - { - return false; - } - - if (query.IncludeItemTypes.Length == 0) - { - return true; - } - - return query.IncludeItemTypes.Any(x => _startDateTypes.Contains(x)); - } - - private bool HasEpisodeAttributes(InternalItemsQuery query) - { - if (query.IncludeItemTypes.Length == 0) - { - return true; - } - - return query.IncludeItemTypes.Contains(BaseItemKind.Episode); - } - - private bool HasTrailerTypes(InternalItemsQuery query) - { - if (query.IncludeItemTypes.Length == 0) - { - return true; - } - - return query.IncludeItemTypes.Contains(BaseItemKind.Trailer); - } - - private bool HasArtistFields(InternalItemsQuery query) - { - if (query.ParentType is not null && _artistExcludeParentTypes.Contains(query.ParentType.Value)) - { - return false; - } - - if (query.IncludeItemTypes.Length == 0) - { - return true; - } - - return query.IncludeItemTypes.Any(x => _artistsTypes.Contains(x)); - } - - private bool HasSeriesFields(InternalItemsQuery query) - { - if (query.ParentType == BaseItemKind.PhotoAlbum) - { - return false; - } - - if (query.IncludeItemTypes.Length == 0) - { - return true; - } - - return query.IncludeItemTypes.Any(x => _seriesTypes.Contains(x)); - } - - private void SetFinalColumnsToSelect(InternalItemsQuery query, List<string> columns) - { - foreach (var field in _allItemFields) - { - if (!HasField(query, field)) - { - switch (field) - { - case ItemFields.Settings: - columns.Remove("IsLocked"); - columns.Remove("PreferredMetadataCountryCode"); - columns.Remove("PreferredMetadataLanguage"); - columns.Remove("LockedFields"); - break; - case ItemFields.ServiceName: - columns.Remove("ExternalServiceId"); - break; - case ItemFields.SortName: - columns.Remove("ForcedSortName"); - break; - case ItemFields.Taglines: - columns.Remove("Tagline"); - break; - case ItemFields.Tags: - columns.Remove("Tags"); - break; - case ItemFields.IsHD: - // do nothing - break; - default: - columns.Remove(field.ToString()); - break; - } - } - } - - if (!HasProgramAttributes(query)) - { - columns.Remove("IsMovie"); - columns.Remove("IsSeries"); - columns.Remove("EpisodeTitle"); - columns.Remove("IsRepeat"); - columns.Remove("ShowId"); - } - - if (!HasEpisodeAttributes(query)) - { - columns.Remove("SeasonName"); - columns.Remove("SeasonId"); - } - - if (!HasStartDate(query)) - { - columns.Remove("StartDate"); - } - - if (!HasTrailerTypes(query)) - { - columns.Remove("TrailerTypes"); - } - - if (!HasArtistFields(query)) - { - columns.Remove("AlbumArtists"); - columns.Remove("Artists"); - } - - if (!HasSeriesFields(query)) - { - columns.Remove("SeriesId"); - } - - if (!HasEpisodeAttributes(query)) - { - columns.Remove("SeasonName"); - columns.Remove("SeasonId"); - } - - if (!query.DtoOptions.EnableImages) - { - columns.Remove("Images"); - } - - if (EnableJoinUserData(query)) - { - columns.Add("UserDatas.UserId"); - columns.Add("UserDatas.lastPlayedDate"); - columns.Add("UserDatas.playbackPositionTicks"); - columns.Add("UserDatas.playcount"); - columns.Add("UserDatas.isFavorite"); - columns.Add("UserDatas.played"); - columns.Add("UserDatas.rating"); - } - - if (query.SimilarTo is not null) - { - var item = query.SimilarTo; - - var builder = new StringBuilder(); - builder.Append('('); - - if (item.InheritedParentalRatingValue == 0) - { - builder.Append("((InheritedParentalRatingValue=0) * 10)"); - } - else - { - builder.Append( - @"(SELECT CASE WHEN COALESCE(InheritedParentalRatingValue, 0)=0 - THEN 0 - ELSE 10.0 / (1.0 + ABS(InheritedParentalRatingValue - @InheritedParentalRatingValue)) - END)"); - } - - if (item.ProductionYear.HasValue) - { - builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 10 Then 10 Else 0 End )"); - builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 5 Then 5 Else 0 End )"); - } - - // 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))"); - builder.Append("+ (Select count(1) * 10 from People where ItemId=Guid and Name in (select Name from People 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"); - - columns.Add(builder.ToString()); - - query.ExcludeItemIds = [.. query.ExcludeItemIds, item.Id, .. item.ExtraIds]; - query.ExcludeProviderIds = item.ProviderIds; - } - - if (!string.IsNullOrEmpty(query.SearchTerm)) - { - var builder = new StringBuilder(); - builder.Append('('); - - builder.Append("((CleanName like @SearchTermStartsWith or (OriginalTitle not null and OriginalTitle like @SearchTermStartsWith)) * 10)"); - builder.Append("+ ((CleanName = @SearchTermStartsWith COLLATE NOCASE or (OriginalTitle not null and OriginalTitle = @SearchTermStartsWith COLLATE NOCASE)) * 10)"); - - if (query.SearchTerm.Length > 1) - { - builder.Append("+ ((CleanName like @SearchTermContains or (OriginalTitle not null and OriginalTitle like @SearchTermContains)) * 10)"); - } - - builder.Append(") as SearchScore"); - - columns.Add(builder.ToString()); - } - } - - private void BindSearchParams(InternalItemsQuery query, SqliteCommand statement) - { - var searchTerm = query.SearchTerm; - - if (string.IsNullOrEmpty(searchTerm)) - { - return; - } - - searchTerm = FixUnicodeChars(searchTerm); - searchTerm = GetCleanValue(searchTerm); - - var commandText = statement.CommandText; - if (commandText.Contains("@SearchTermStartsWith", StringComparison.OrdinalIgnoreCase)) - { - statement.TryBind("@SearchTermStartsWith", searchTerm + "%"); - } - - if (commandText.Contains("@SearchTermContains", StringComparison.OrdinalIgnoreCase)) - { - statement.TryBind("@SearchTermContains", "%" + searchTerm + "%"); - } - } - - private void BindSimilarParams(InternalItemsQuery query, SqliteCommand statement) - { - var item = query.SimilarTo; - - if (item is null) - { - return; - } - - var commandText = statement.CommandText; - - if (commandText.Contains("@ItemOfficialRating", StringComparison.OrdinalIgnoreCase)) - { - statement.TryBind("@ItemOfficialRating", item.OfficialRating); - } - - if (commandText.Contains("@ItemProductionYear", StringComparison.OrdinalIgnoreCase)) - { - statement.TryBind("@ItemProductionYear", item.ProductionYear ?? 0); - } - - if (commandText.Contains("@SimilarItemId", StringComparison.OrdinalIgnoreCase)) - { - statement.TryBind("@SimilarItemId", item.Id); - } - - if (commandText.Contains("@InheritedParentalRatingValue", StringComparison.OrdinalIgnoreCase)) - { - statement.TryBind("@InheritedParentalRatingValue", item.InheritedParentalRatingValue); - } - } - private string GetJoinUserDataText(InternalItemsQuery query) { if (!EnableJoinUserData(query)) @@ -508,528 +156,6 @@ namespace Emby.Server.Implementations.Data return " left join UserDatas on UserDataKey=UserDatas.Key And (UserId=@UserId)"; } - private string GetGroupBy(InternalItemsQuery query) - { - var enableGroupByPresentationUniqueKey = EnableGroupByPresentationUniqueKey(query); - if (enableGroupByPresentationUniqueKey && query.GroupBySeriesPresentationUniqueKey) - { - return " Group by PresentationUniqueKey, SeriesPresentationUniqueKey"; - } - - if (enableGroupByPresentationUniqueKey) - { - return " Group by PresentationUniqueKey"; - } - - if (query.GroupBySeriesPresentationUniqueKey) - { - return " Group by SeriesPresentationUniqueKey"; - } - - return string.Empty; - } - - - private string FixUnicodeChars(string buffer) - { - buffer = buffer.Replace('\u2013', '-'); // en dash - buffer = buffer.Replace('\u2014', '-'); // em dash - buffer = buffer.Replace('\u2015', '-'); // horizontal bar - buffer = buffer.Replace('\u2017', '_'); // double low line - buffer = buffer.Replace('\u2018', '\''); // left single quotation mark - buffer = buffer.Replace('\u2019', '\''); // right single quotation mark - buffer = buffer.Replace('\u201a', ','); // single low-9 quotation mark - buffer = buffer.Replace('\u201b', '\''); // single high-reversed-9 quotation mark - buffer = buffer.Replace('\u201c', '\"'); // left double quotation mark - buffer = buffer.Replace('\u201d', '\"'); // right double quotation mark - buffer = buffer.Replace('\u201e', '\"'); // double low-9 quotation mark - buffer = buffer.Replace("\u2026", "...", StringComparison.Ordinal); // horizontal ellipsis - buffer = buffer.Replace('\u2032', '\''); // prime - buffer = buffer.Replace('\u2033', '\"'); // double prime - buffer = buffer.Replace('\u0060', '\''); // grave accent - return buffer.Replace('\u00B4', '\''); // acute accent - } - - private string GetOrderByText(InternalItemsQuery query) - { - var orderBy = query.OrderBy; - bool hasSimilar = query.SimilarTo is not null; - bool hasSearch = !string.IsNullOrEmpty(query.SearchTerm); - - if (hasSimilar || hasSearch) - { - List<(ItemSortBy, SortOrder)> prepend = new List<(ItemSortBy, SortOrder)>(4); - if (hasSearch) - { - prepend.Add((ItemSortBy.SearchScore, SortOrder.Descending)); - prepend.Add((ItemSortBy.SortName, SortOrder.Ascending)); - } - - if (hasSimilar) - { - prepend.Add((ItemSortBy.SimilarityScore, SortOrder.Descending)); - prepend.Add((ItemSortBy.Random, SortOrder.Ascending)); - } - - orderBy = query.OrderBy = [.. prepend, .. orderBy]; - } - else if (orderBy.Count == 0) - { - return string.Empty; - } - - return " ORDER BY " + string.Join(',', orderBy.Select(i => - { - var sortBy = MapOrderByField(i.OrderBy, query); - var sortOrder = i.SortOrder == SortOrder.Ascending ? "ASC" : "DESC"; - return sortBy + " " + sortOrder; - })); - } - - private string MapOrderByField(ItemSortBy sortBy, InternalItemsQuery query) - { - return sortBy switch - { - ItemSortBy.AirTime => "SortName", // TODO - ItemSortBy.Runtime => "RuntimeTicks", - ItemSortBy.Random => "RANDOM()", - ItemSortBy.DatePlayed when query.GroupBySeriesPresentationUniqueKey => "MAX(LastPlayedDate)", - ItemSortBy.DatePlayed => "LastPlayedDate", - ItemSortBy.PlayCount => "PlayCount", - ItemSortBy.IsFavoriteOrLiked => "(Select Case When IsFavorite is null Then 0 Else IsFavorite End )", - ItemSortBy.IsFolder => "IsFolder", - ItemSortBy.IsPlayed => "played", - ItemSortBy.IsUnplayed => "played", - ItemSortBy.DateLastContentAdded => "DateLastMediaAdded", - ItemSortBy.Artist => "(select CleanValue from ItemValues where ItemId=Guid and Type=0 LIMIT 1)", - ItemSortBy.AlbumArtist => "(select CleanValue from ItemValues where ItemId=Guid and Type=1 LIMIT 1)", - ItemSortBy.OfficialRating => "InheritedParentalRatingValue", - ItemSortBy.Studio => "(select CleanValue from ItemValues where ItemId=Guid and Type=3 LIMIT 1)", - ItemSortBy.SeriesDatePlayed => "(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText(query) + " where Played=1 and B.SeriesPresentationUniqueKey=A.PresentationUniqueKey)", - ItemSortBy.SeriesSortName => "SeriesName", - ItemSortBy.AiredEpisodeOrder => "AiredEpisodeOrder", - ItemSortBy.Album => "Album", - ItemSortBy.DateCreated => "DateCreated", - ItemSortBy.PremiereDate => "PremiereDate", - ItemSortBy.StartDate => "StartDate", - ItemSortBy.Name => "Name", - ItemSortBy.CommunityRating => "CommunityRating", - ItemSortBy.ProductionYear => "ProductionYear", - ItemSortBy.CriticRating => "CriticRating", - ItemSortBy.VideoBitRate => "VideoBitRate", - ItemSortBy.ParentIndexNumber => "ParentIndexNumber", - ItemSortBy.IndexNumber => "IndexNumber", - ItemSortBy.SimilarityScore => "SimilarityScore", - ItemSortBy.SearchScore => "SearchScore", - _ => "SortName" - }; - } - - /// <inheritdoc /> - public List<Guid> GetItemIdsList(InternalItemsQuery query) - { - ArgumentNullException.ThrowIfNull(query); - - CheckDisposed(); - - var columns = new List<string> { "guid" }; - SetFinalColumnsToSelect(query, columns); - var commandTextBuilder = new StringBuilder("select ", 256) - .AppendJoin(',', columns) - .Append(FromText) - .Append(GetJoinUserDataText(query)); - - var whereClauses = GetWhereClauses(query, null); - if (whereClauses.Count != 0) - { - commandTextBuilder.Append(" where ") - .AppendJoin(" AND ", whereClauses); - } - - commandTextBuilder.Append(GetGroupBy(query)) - .Append(GetOrderByText(query)); - - if (query.Limit.HasValue || query.StartIndex.HasValue) - { - var offset = query.StartIndex ?? 0; - - if (query.Limit.HasValue || offset > 0) - { - commandTextBuilder.Append(" LIMIT ") - .Append(query.Limit ?? int.MaxValue); - } - - if (offset > 0) - { - commandTextBuilder.Append(" OFFSET ") - .Append(offset); - } - } - - var commandText = commandTextBuilder.ToString(); - var list = new List<Guid>(); - using (new QueryTimeLogger(Logger, commandText)) - using (var connection = GetConnection(true)) - using (var statement = PrepareStatement(connection, commandText)) - { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } - - BindSimilarParams(query, statement); - BindSearchParams(query, statement); - - // Running this again will bind the params - GetWhereClauses(query, statement); - - foreach (var row in statement.ExecuteQuery()) - { - list.Add(row.GetGuid(0)); - } - } - - return list; - } - - private bool IsAlphaNumeric(string str) - { - if (string.IsNullOrWhiteSpace(str)) - { - return false; - } - - for (int i = 0; i < str.Length; i++) - { - if (!char.IsLetter(str[i]) && !char.IsNumber(str[i])) - { - return false; - } - } - - return true; - } - - private bool IsValidPersonType(string value) - { - return IsAlphaNumeric(value); - } - - private bool IsTypeInQuery(BaseItemKind type, InternalItemsQuery query) - { - if (query.ExcludeItemTypes.Contains(type)) - { - return false; - } - - return query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(type); - } - - private string GetCleanValue(string value) - { - if (string.IsNullOrWhiteSpace(value)) - { - return value; - } - - return value.RemoveDiacritics().ToLowerInvariant(); - } - - /// <inheritdoc /> - public void UpdateInheritedValues() - { - const string Statements = """ -delete from ItemValues where type = 6; -insert into ItemValues (ItemId, Type, Value, CleanValue) select ItemId, 6, Value, CleanValue from ItemValues where Type=4; -insert into ItemValues (ItemId, Type, Value, CleanValue) select AncestorIds.itemid, 6, ItemValues.Value, ItemValues.CleanValue -FROM AncestorIds -LEFT JOIN ItemValues ON (AncestorIds.AncestorId = ItemValues.ItemId) -where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type = 4; -"""; - using var connection = GetConnection(); - using var transaction = connection.BeginTransaction(); - connection.Execute(Statements); - transaction.Commit(); - } - - /// <inheritdoc /> - public void DeleteItem(Guid id) - { - if (id.IsEmpty()) - { - throw new ArgumentNullException(nameof(id)); - } - - CheckDisposed(); - - using var connection = GetConnection(); - using var transaction = connection.BeginTransaction(); - // Delete people - ExecuteWithSingleParam(connection, "delete from People where ItemId=@Id", id); - - // Delete chapters - ExecuteWithSingleParam(connection, "delete from " + ChaptersTableName + " where ItemId=@Id", id); - - // Delete media streams - ExecuteWithSingleParam(connection, "delete from mediastreams where ItemId=@Id", id); - - // Delete ancestors - ExecuteWithSingleParam(connection, "delete from AncestorIds where ItemId=@Id", id); - - // Delete item values - ExecuteWithSingleParam(connection, "delete from ItemValues where ItemId=@Id", id); - - // Delete the item - ExecuteWithSingleParam(connection, "delete from TypedBaseItems where guid=@Id", id); - - transaction.Commit(); - } - - private void ExecuteWithSingleParam(ManagedConnection db, string query, Guid value) - { - using (var statement = PrepareStatement(db, query)) - { - statement.TryBind("@Id", value); - - statement.ExecuteNonQuery(); - } - } - - /// <inheritdoc /> - public List<string> GetPeopleNames(InternalPeopleQuery query) - { - ArgumentNullException.ThrowIfNull(query); - - CheckDisposed(); - - var commandText = new StringBuilder("select Distinct p.Name from People p"); - - var whereClauses = GetPeopleWhereClauses(query, null); - - if (whereClauses.Count != 0) - { - commandText.Append(" where ").AppendJoin(" AND ", whereClauses); - } - - commandText.Append(" order by ListOrder"); - - if (query.Limit > 0) - { - commandText.Append(" LIMIT ").Append(query.Limit); - } - - var list = new List<string>(); - using (var connection = GetConnection(true)) - using (var statement = PrepareStatement(connection, commandText.ToString())) - { - // Run this again to bind the params - GetPeopleWhereClauses(query, statement); - - foreach (var row in statement.ExecuteQuery()) - { - list.Add(row.GetString(0)); - } - } - - return list; - } - - /// <inheritdoc /> - public List<PersonInfo> GetPeople(InternalPeopleQuery query) - { - ArgumentNullException.ThrowIfNull(query); - - CheckDisposed(); - - StringBuilder commandText = new StringBuilder("select ItemId, Name, Role, PersonType, SortOrder from People p"); - - var whereClauses = GetPeopleWhereClauses(query, null); - - if (whereClauses.Count != 0) - { - commandText.Append(" where ").AppendJoin(" AND ", whereClauses); - } - - commandText.Append(" order by ListOrder"); - - if (query.Limit > 0) - { - commandText.Append(" LIMIT ").Append(query.Limit); - } - - var list = new List<PersonInfo>(); - using (var connection = GetConnection(true)) - using (var statement = PrepareStatement(connection, commandText.ToString())) - { - // Run this again to bind the params - GetPeopleWhereClauses(query, statement); - - foreach (var row in statement.ExecuteQuery()) - { - list.Add(GetPerson(row)); - } - } - - return list; - } - - private List<string> GetPeopleWhereClauses(InternalPeopleQuery query, SqliteCommand statement) - { - var whereClauses = new List<string>(); - - if (query.User is not null && query.IsFavorite.HasValue) - { - whereClauses.Add(@"p.Name IN ( -SELECT Name FROM TypedBaseItems WHERE UserDataKey IN ( -SELECT key FROM UserDatas WHERE isFavorite=@IsFavorite AND userId=@UserId) -AND Type = @InternalPersonType)"); - statement?.TryBind("@IsFavorite", query.IsFavorite.Value); - statement?.TryBind("@InternalPersonType", typeof(Person).FullName); - statement?.TryBind("@UserId", query.User.InternalId); - } - - if (!query.ItemId.IsEmpty()) - { - whereClauses.Add("ItemId=@ItemId"); - statement?.TryBind("@ItemId", query.ItemId); - } - - if (!query.AppearsInItemId.IsEmpty()) - { - whereClauses.Add("p.Name in (Select Name from People where ItemId=@AppearsInItemId)"); - statement?.TryBind("@AppearsInItemId", query.AppearsInItemId); - } - - var queryPersonTypes = query.PersonTypes.Where(IsValidPersonType).ToList(); - - if (queryPersonTypes.Count == 1) - { - whereClauses.Add("PersonType=@PersonType"); - statement?.TryBind("@PersonType", queryPersonTypes[0]); - } - else if (queryPersonTypes.Count > 1) - { - var val = string.Join(',', queryPersonTypes.Select(i => "'" + i + "'")); - - whereClauses.Add("PersonType in (" + val + ")"); - } - - var queryExcludePersonTypes = query.ExcludePersonTypes.Where(IsValidPersonType).ToList(); - - if (queryExcludePersonTypes.Count == 1) - { - whereClauses.Add("PersonType<>@PersonType"); - statement?.TryBind("@PersonType", queryExcludePersonTypes[0]); - } - else if (queryExcludePersonTypes.Count > 1) - { - var val = string.Join(',', queryExcludePersonTypes.Select(i => "'" + i + "'")); - - whereClauses.Add("PersonType not in (" + val + ")"); - } - - if (query.MaxListOrder.HasValue) - { - whereClauses.Add("ListOrder<=@MaxListOrder"); - statement?.TryBind("@MaxListOrder", query.MaxListOrder.Value); - } - - if (!string.IsNullOrWhiteSpace(query.NameContains)) - { - whereClauses.Add("p.Name like @NameContains"); - statement?.TryBind("@NameContains", "%" + query.NameContains + "%"); - } - - return whereClauses; - } - - private void UpdateAncestors(Guid itemId, List<Guid> ancestorIds, ManagedConnection db, SqliteCommand deleteAncestorsStatement) - { - if (itemId.IsEmpty()) - { - throw new ArgumentNullException(nameof(itemId)); - } - - ArgumentNullException.ThrowIfNull(ancestorIds); - - CheckDisposed(); - - // First delete - deleteAncestorsStatement.TryBind("@ItemId", itemId); - deleteAncestorsStatement.ExecuteNonQuery(); - - if (ancestorIds.Count == 0) - { - return; - } - - var insertText = new StringBuilder("insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values "); - - for (var i = 0; i < ancestorIds.Count; i++) - { - insertText.AppendFormat( - CultureInfo.InvariantCulture, - "(@ItemId, @AncestorId{0}, @AncestorIdText{0}),", - i.ToString(CultureInfo.InvariantCulture)); - } - - // Remove trailing comma - insertText.Length--; - - using (var statement = PrepareStatement(db, insertText.ToString())) - { - statement.TryBind("@ItemId", itemId); - - for (var i = 0; i < ancestorIds.Count; i++) - { - var index = i.ToString(CultureInfo.InvariantCulture); - - var ancestorId = ancestorIds[i]; - - statement.TryBind("@AncestorId" + index, ancestorId); - statement.TryBind("@AncestorIdText" + index, ancestorId.ToString("N", CultureInfo.InvariantCulture)); - } - - statement.ExecuteNonQuery(); - } - } - - /// <inheritdoc /> - public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetAllArtists(InternalItemsQuery query) - { - return GetItemValues(query, new[] { 0, 1 }, typeof(MusicArtist).FullName); - } - - /// <inheritdoc /> - public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetArtists(InternalItemsQuery query) - { - return GetItemValues(query, new[] { 0 }, typeof(MusicArtist).FullName); - } - - /// <inheritdoc /> - public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetAlbumArtists(InternalItemsQuery query) - { - return GetItemValues(query, new[] { 1 }, typeof(MusicArtist).FullName); - } - - /// <inheritdoc /> - public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetStudios(InternalItemsQuery query) - { - return GetItemValues(query, new[] { 3 }, typeof(Studio).FullName); - } - - /// <inheritdoc /> - public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetGenres(InternalItemsQuery query) - { - return GetItemValues(query, new[] { 2 }, typeof(Genre).FullName); - } - - /// <inheritdoc /> - public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetMusicGenres(InternalItemsQuery query) - { - return GetItemValues(query, new[] { 2 }, typeof(MusicGenre).FullName); - } - /// <inheritdoc /> public List<string> GetStudioNames() { @@ -1123,923 +249,7 @@ AND Type = @InternalPersonType)"); return list; } - private QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetItemValues(InternalItemsQuery query, int[] itemValueTypes, string returnType) - { - ArgumentNullException.ThrowIfNull(query); - if (!query.Limit.HasValue) - { - query.EnableTotalRecordCount = false; - } - - CheckDisposed(); - - var typeClause = itemValueTypes.Length == 1 ? - ("Type=" + itemValueTypes[0]) : - ("Type in (" + string.Join(',', itemValueTypes) + ")"); - - InternalItemsQuery typeSubQuery = null; - - string itemCountColumns = null; - - var stringBuilder = new StringBuilder(1024); - var typesToCount = query.IncludeItemTypes; - - if (typesToCount.Length > 0) - { - stringBuilder.Append("(select group_concat(type, '|') from TypedBaseItems B"); - - typeSubQuery = 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 whereClauses = GetWhereClauses(typeSubQuery, null); - - stringBuilder.Append(" where ") - .AppendJoin(" AND ", whereClauses) - .Append(" AND ") - .Append("guid in (select ItemId from ItemValues where ItemValues.CleanValue=A.CleanName AND ") - .Append(typeClause) - .Append(")) as itemTypes"); - - itemCountColumns = stringBuilder.ToString(); - stringBuilder.Clear(); - } - - List<string> columns = _retrieveItemColumns.ToList(); - // Unfortunately we need to add it to columns to ensure the order of the columns in the select - if (!string.IsNullOrEmpty(itemCountColumns)) - { - columns.Add(itemCountColumns); - } - - // do this first before calling GetFinalColumnsToSelect, otherwise ExcludeItemIds will be set by SimilarTo - var innerQuery = new InternalItemsQuery(query.User) - { - ExcludeItemTypes = query.ExcludeItemTypes, - IncludeItemTypes = query.IncludeItemTypes, - MediaTypes = query.MediaTypes, - AncestorIds = query.AncestorIds, - ItemIds = query.ItemIds, - TopParentIds = query.TopParentIds, - ParentId = query.ParentId, - IsAiring = query.IsAiring, - IsMovie = query.IsMovie, - IsSports = query.IsSports, - IsKids = query.IsKids, - IsNews = query.IsNews, - IsSeries = query.IsSeries - }; - - SetFinalColumnsToSelect(query, columns); - - var innerWhereClauses = GetWhereClauses(innerQuery, null); - - stringBuilder.Append(" where Type=@SelectType And CleanName In (Select CleanValue from ItemValues where ") - .Append(typeClause) - .Append(" AND ItemId in (select guid from TypedBaseItems"); - if (innerWhereClauses.Count > 0) - { - stringBuilder.Append(" where ") - .AppendJoin(" AND ", innerWhereClauses); - } - - stringBuilder.Append("))"); - - var outerQuery = new InternalItemsQuery(query.User) - { - IsPlayed = query.IsPlayed, - IsFavorite = query.IsFavorite, - IsFavoriteOrLiked = query.IsFavoriteOrLiked, - IsLiked = query.IsLiked, - IsLocked = query.IsLocked, - NameLessThan = query.NameLessThan, - NameStartsWith = query.NameStartsWith, - NameStartsWithOrGreater = query.NameStartsWithOrGreater, - Tags = query.Tags, - OfficialRatings = query.OfficialRatings, - StudioIds = query.StudioIds, - GenreIds = query.GenreIds, - Genres = query.Genres, - Years = query.Years, - NameContains = query.NameContains, - SearchTerm = query.SearchTerm, - SimilarTo = query.SimilarTo, - ExcludeItemIds = query.ExcludeItemIds - }; - - var outerWhereClauses = GetWhereClauses(outerQuery, null); - if (outerWhereClauses.Count != 0) - { - stringBuilder.Append(" AND ") - .AppendJoin(" AND ", outerWhereClauses); - } - - var whereText = stringBuilder.ToString(); - stringBuilder.Clear(); - - stringBuilder.Append("select ") - .AppendJoin(',', columns) - .Append(FromText) - .Append(GetJoinUserDataText(query)) - .Append(whereText) - .Append(" group by PresentationUniqueKey"); - - if (query.OrderBy.Count != 0 - || query.SimilarTo is not null - || !string.IsNullOrEmpty(query.SearchTerm)) - { - stringBuilder.Append(GetOrderByText(query)); - } - else - { - stringBuilder.Append(" order by SortName"); - } - - if (query.Limit.HasValue || query.StartIndex.HasValue) - { - var offset = query.StartIndex ?? 0; - - if (query.Limit.HasValue || offset > 0) - { - stringBuilder.Append(" LIMIT ") - .Append(query.Limit ?? int.MaxValue); - } - - if (offset > 0) - { - stringBuilder.Append(" OFFSET ") - .Append(offset); - } - } - - var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; - - string commandText = string.Empty; - - if (!isReturningZeroItems) - { - commandText = stringBuilder.ToString(); - } - - string countText = string.Empty; - if (query.EnableTotalRecordCount) - { - stringBuilder.Clear(); - var columnsToSelect = new List<string> { "count (distinct PresentationUniqueKey)" }; - SetFinalColumnsToSelect(query, columnsToSelect); - stringBuilder.Append("select ") - .AppendJoin(',', columnsToSelect) - .Append(FromText) - .Append(GetJoinUserDataText(query)) - .Append(whereText); - - countText = stringBuilder.ToString(); - } - - var list = new List<(BaseItem, ItemCounts)>(); - var result = new QueryResult<(BaseItem, ItemCounts)>(); - using (new QueryTimeLogger(Logger, commandText)) - using (var connection = GetConnection(true)) - using (var transaction = connection.BeginTransaction()) - { - if (!isReturningZeroItems) - { - using (var statement = PrepareStatement(connection, commandText)) - { - statement.TryBind("@SelectType", returnType); - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } - - if (typeSubQuery is not null) - { - GetWhereClauses(typeSubQuery, null); - } - - BindSimilarParams(query, statement); - BindSearchParams(query, statement); - GetWhereClauses(innerQuery, statement); - GetWhereClauses(outerQuery, statement); - - var hasEpisodeAttributes = HasEpisodeAttributes(query); - var hasProgramAttributes = HasProgramAttributes(query); - var hasServiceName = HasServiceName(query); - var hasStartDate = HasStartDate(query); - var hasTrailerTypes = HasTrailerTypes(query); - var hasArtistFields = HasArtistFields(query); - var hasSeriesFields = HasSeriesFields(query); - - foreach (var row in statement.ExecuteQuery()) - { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields, false); - if (item is not null) - { - var countStartColumn = columns.Count - 1; - - list.Add((item, GetItemCounts(row, countStartColumn, typesToCount))); - } - } - } - } - - if (query.EnableTotalRecordCount) - { - using (var statement = PrepareStatement(connection, countText)) - { - statement.TryBind("@SelectType", returnType); - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } - - if (typeSubQuery is not null) - { - GetWhereClauses(typeSubQuery, null); - } - - BindSimilarParams(query, statement); - BindSearchParams(query, statement); - GetWhereClauses(innerQuery, statement); - GetWhereClauses(outerQuery, statement); - - result.TotalRecordCount = statement.SelectScalarInt(); - } - } - - transaction.Commit(); - } - - if (result.TotalRecordCount == 0) - { - result.TotalRecordCount = list.Count; - } - - result.StartIndex = query.StartIndex ?? 0; - result.Items = list; - - return result; - } - - private static ItemCounts GetItemCounts(SqliteDataReader reader, int countStartColumn, BaseItemKind[] typesToCount) - { - var counts = new ItemCounts(); - - if (typesToCount.Length == 0) - { - return counts; - } - - if (!reader.TryGetString(countStartColumn, out var typeString)) - { - return counts; - } - - foreach (var typeName in typeString.AsSpan().Split('|')) - { - if (typeName.Equals(typeof(Series).FullName, StringComparison.OrdinalIgnoreCase)) - { - counts.SeriesCount++; - } - else if (typeName.Equals(typeof(Episode).FullName, StringComparison.OrdinalIgnoreCase)) - { - counts.EpisodeCount++; - } - else if (typeName.Equals(typeof(Movie).FullName, StringComparison.OrdinalIgnoreCase)) - { - counts.MovieCount++; - } - else if (typeName.Equals(typeof(MusicAlbum).FullName, StringComparison.OrdinalIgnoreCase)) - { - counts.AlbumCount++; - } - else if (typeName.Equals(typeof(MusicArtist).FullName, StringComparison.OrdinalIgnoreCase)) - { - counts.ArtistCount++; - } - else if (typeName.Equals(typeof(Audio).FullName, StringComparison.OrdinalIgnoreCase)) - { - counts.SongCount++; - } - else if (typeName.Equals(typeof(Trailer).FullName, StringComparison.OrdinalIgnoreCase)) - { - counts.TrailerCount++; - } - - counts.ItemCount++; - } - - return counts; - } - - private List<(int MagicNumber, string Value)> GetItemValuesToSave(BaseItem item, List<string> inheritedTags) - { - var list = new List<(int, string)>(); - - if (item is IHasArtist hasArtist) - { - list.AddRange(hasArtist.Artists.Select(i => (0, i))); - } - - if (item is IHasAlbumArtist hasAlbumArtist) - { - list.AddRange(hasAlbumArtist.AlbumArtists.Select(i => (1, i))); - } - - list.AddRange(item.Genres.Select(i => (2, i))); - list.AddRange(item.Studios.Select(i => (3, i))); - list.AddRange(item.Tags.Select(i => (4, i))); - - // keywords was 5 - - list.AddRange(inheritedTags.Select(i => (6, i))); - - // Remove all invalid values. - list.RemoveAll(i => string.IsNullOrWhiteSpace(i.Item2)); - - return list; - } - - private void UpdateItemValues(Guid itemId, List<(int MagicNumber, string Value)> values, ManagedConnection db) - { - if (itemId.IsEmpty()) - { - throw new ArgumentNullException(nameof(itemId)); - } - - ArgumentNullException.ThrowIfNull(values); - - CheckDisposed(); - - // First delete - using var command = db.PrepareStatement("delete from ItemValues where ItemId=@Id"); - command.TryBind("@Id", itemId); - command.ExecuteNonQuery(); - - InsertItemValues(itemId, values, db); - } - - private void InsertItemValues(Guid id, List<(int MagicNumber, string Value)> values, ManagedConnection db) - { - const int Limit = 100; - var startIndex = 0; - - const string StartInsertText = "insert into ItemValues (ItemId, Type, Value, CleanValue) values "; - var insertText = new StringBuilder(StartInsertText); - while (startIndex < values.Count) - { - var endIndex = Math.Min(values.Count, startIndex + Limit); - - for (var i = startIndex; i < endIndex; i++) - { - insertText.AppendFormat( - CultureInfo.InvariantCulture, - "(@ItemId, @Type{0}, @Value{0}, @CleanValue{0}),", - i); - } - - // Remove trailing comma - insertText.Length--; - - using (var statement = PrepareStatement(db, insertText.ToString())) - { - statement.TryBind("@ItemId", id); - - for (var i = startIndex; i < endIndex; i++) - { - var index = i.ToString(CultureInfo.InvariantCulture); - - var currentValueInfo = values[i]; - - var itemValue = currentValueInfo.Value; - - statement.TryBind("@Type" + index, currentValueInfo.MagicNumber); - statement.TryBind("@Value" + index, itemValue); - statement.TryBind("@CleanValue" + index, GetCleanValue(itemValue)); - } - - statement.ExecuteNonQuery(); - } - - startIndex += Limit; - insertText.Length = StartInsertText.Length; - } - } - - /// <inheritdoc /> - public void UpdatePeople(Guid itemId, List<PersonInfo> people) - { - if (itemId.IsEmpty()) - { - throw new ArgumentNullException(nameof(itemId)); - } - - CheckDisposed(); - - using var connection = GetConnection(); - using var transaction = connection.BeginTransaction(); - // Delete all existing people first - using var command = connection.CreateCommand(); - command.CommandText = "delete from People where ItemId=@ItemId"; - command.TryBind("@ItemId", itemId); - command.ExecuteNonQuery(); - - if (people is not null) - { - InsertPeople(itemId, people, connection); - } - - transaction.Commit(); - } - - private void InsertPeople(Guid id, List<PersonInfo> people, ManagedConnection db) - { - const int Limit = 100; - var startIndex = 0; - var listIndex = 0; - - const string StartInsertText = "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values "; - var insertText = new StringBuilder(StartInsertText); - while (startIndex < people.Count) - { - var endIndex = Math.Min(people.Count, startIndex + Limit); - for (var i = startIndex; i < endIndex; i++) - { - insertText.AppendFormat( - CultureInfo.InvariantCulture, - "(@ItemId, @Name{0}, @Role{0}, @PersonType{0}, @SortOrder{0}, @ListOrder{0}),", - i.ToString(CultureInfo.InvariantCulture)); - } - - // Remove trailing comma - insertText.Length--; - - using (var statement = PrepareStatement(db, insertText.ToString())) - { - statement.TryBind("@ItemId", id); - - for (var i = startIndex; i < endIndex; i++) - { - var index = i.ToString(CultureInfo.InvariantCulture); - - var person = people[i]; - - statement.TryBind("@Name" + index, person.Name); - statement.TryBind("@Role" + index, person.Role); - statement.TryBind("@PersonType" + index, person.Type.ToString()); - statement.TryBind("@SortOrder" + index, person.SortOrder); - statement.TryBind("@ListOrder" + index, listIndex); - - listIndex++; - } - - statement.ExecuteNonQuery(); - } - - startIndex += Limit; - insertText.Length = StartInsertText.Length; - } - } - - private PersonInfo GetPerson(SqliteDataReader reader) - { - var item = new PersonInfo - { - ItemId = reader.GetGuid(0), - Name = reader.GetString(1) - }; - - if (reader.TryGetString(2, out var role)) - { - item.Role = role; - } - - if (reader.TryGetString(3, out var type) - && Enum.TryParse(type, true, out PersonKind personKind)) - { - item.Type = personKind; - } - - if (reader.TryGetInt32(4, out var sortOrder)) - { - item.SortOrder = sortOrder; - } - - return item; - } - - /// <inheritdoc /> - public List<MediaStream> GetMediaStreams(MediaStreamQuery query) - { - CheckDisposed(); - - ArgumentNullException.ThrowIfNull(query); - - var cmdText = _mediaStreamSaveColumnsSelectQuery; - - if (query.Type.HasValue) - { - cmdText += " AND StreamType=@StreamType"; - } - - if (query.Index.HasValue) - { - cmdText += " AND StreamIndex=@StreamIndex"; - } - - cmdText += " order by StreamIndex ASC"; - - using (var connection = GetConnection(true)) - { - var list = new List<MediaStream>(); - - using (var statement = PrepareStatement(connection, cmdText)) - { - statement.TryBind("@ItemId", query.ItemId); - - if (query.Type.HasValue) - { - statement.TryBind("@StreamType", query.Type.Value.ToString()); - } - - if (query.Index.HasValue) - { - statement.TryBind("@StreamIndex", query.Index.Value); - } - - foreach (var row in statement.ExecuteQuery()) - { - list.Add(GetMediaStream(row)); - } - } - - return list; - } - } - - /// <inheritdoc /> - public void SaveMediaStreams(Guid id, IReadOnlyList<MediaStream> streams, CancellationToken cancellationToken) - { - CheckDisposed(); - - if (id.IsEmpty()) - { - throw new ArgumentNullException(nameof(id)); - } - - ArgumentNullException.ThrowIfNull(streams); - - cancellationToken.ThrowIfCancellationRequested(); - - using var connection = GetConnection(); - using var transaction = connection.BeginTransaction(); - // Delete existing mediastreams - using var command = connection.PrepareStatement("delete from mediastreams where ItemId=@ItemId"); - command.TryBind("@ItemId", id); - command.ExecuteNonQuery(); - - InsertMediaStreams(id, streams, connection); - - transaction.Commit(); - } - - private void InsertMediaStreams(Guid id, IReadOnlyList<MediaStream> streams, ManagedConnection db) - { - const int Limit = 10; - var startIndex = 0; - - var insertText = new StringBuilder(_mediaStreamSaveColumnsInsertQuery); - while (startIndex < streams.Count) - { - var endIndex = Math.Min(streams.Count, startIndex + Limit); - - for (var i = startIndex; i < endIndex; i++) - { - if (i != startIndex) - { - insertText.Append(','); - } - - var index = i.ToString(CultureInfo.InvariantCulture); - insertText.Append("(@ItemId, "); - - foreach (var column in _mediaStreamSaveColumns.Skip(1)) - { - insertText.Append('@').Append(column).Append(index).Append(','); - } - - insertText.Length -= 1; // Remove the last comma - - insertText.Append(')'); - } - - using (var statement = PrepareStatement(db, insertText.ToString())) - { - statement.TryBind("@ItemId", id); - - for (var i = startIndex; i < endIndex; i++) - { - var index = i.ToString(CultureInfo.InvariantCulture); - - var stream = streams[i]; - - statement.TryBind("@StreamIndex" + index, stream.Index); - statement.TryBind("@StreamType" + index, stream.Type.ToString()); - statement.TryBind("@Codec" + index, stream.Codec); - statement.TryBind("@Language" + index, stream.Language); - statement.TryBind("@ChannelLayout" + index, stream.ChannelLayout); - statement.TryBind("@Profile" + index, stream.Profile); - statement.TryBind("@AspectRatio" + index, stream.AspectRatio); - statement.TryBind("@Path" + index, GetPathToSave(stream.Path)); - - statement.TryBind("@IsInterlaced" + index, stream.IsInterlaced); - statement.TryBind("@BitRate" + index, stream.BitRate); - statement.TryBind("@Channels" + index, stream.Channels); - statement.TryBind("@SampleRate" + index, stream.SampleRate); - - statement.TryBind("@IsDefault" + index, stream.IsDefault); - statement.TryBind("@IsForced" + index, stream.IsForced); - statement.TryBind("@IsExternal" + index, stream.IsExternal); - - // Yes these are backwards due to a mistake - statement.TryBind("@Width" + index, stream.Height); - statement.TryBind("@Height" + index, stream.Width); - - statement.TryBind("@AverageFrameRate" + index, stream.AverageFrameRate); - statement.TryBind("@RealFrameRate" + index, stream.RealFrameRate); - statement.TryBind("@Level" + index, stream.Level); - - statement.TryBind("@PixelFormat" + index, stream.PixelFormat); - statement.TryBind("@BitDepth" + index, stream.BitDepth); - statement.TryBind("@IsAnamorphic" + index, stream.IsAnamorphic); - statement.TryBind("@IsExternal" + index, stream.IsExternal); - statement.TryBind("@RefFrames" + index, stream.RefFrames); - - statement.TryBind("@CodecTag" + index, stream.CodecTag); - statement.TryBind("@Comment" + index, stream.Comment); - statement.TryBind("@NalLengthSize" + index, stream.NalLengthSize); - statement.TryBind("@IsAvc" + index, stream.IsAVC); - statement.TryBind("@Title" + index, stream.Title); - - statement.TryBind("@TimeBase" + index, stream.TimeBase); - statement.TryBind("@CodecTimeBase" + index, stream.CodecTimeBase); - - statement.TryBind("@ColorPrimaries" + index, stream.ColorPrimaries); - statement.TryBind("@ColorSpace" + index, stream.ColorSpace); - statement.TryBind("@ColorTransfer" + index, stream.ColorTransfer); - - statement.TryBind("@DvVersionMajor" + index, stream.DvVersionMajor); - statement.TryBind("@DvVersionMinor" + index, stream.DvVersionMinor); - statement.TryBind("@DvProfile" + index, stream.DvProfile); - statement.TryBind("@DvLevel" + index, stream.DvLevel); - statement.TryBind("@RpuPresentFlag" + index, stream.RpuPresentFlag); - statement.TryBind("@ElPresentFlag" + index, stream.ElPresentFlag); - statement.TryBind("@BlPresentFlag" + index, stream.BlPresentFlag); - statement.TryBind("@DvBlSignalCompatibilityId" + index, stream.DvBlSignalCompatibilityId); - - statement.TryBind("@IsHearingImpaired" + index, stream.IsHearingImpaired); - - statement.TryBind("@Rotation" + index, stream.Rotation); - } - - statement.ExecuteNonQuery(); - } - - startIndex += Limit; - insertText.Length = _mediaStreamSaveColumnsInsertQuery.Length; - } - } - - /// <summary> - /// Gets the media stream. - /// </summary> - /// <param name="reader">The reader.</param> - /// <returns>MediaStream.</returns> - private MediaStream GetMediaStream(SqliteDataReader reader) - { - var item = new MediaStream - { - Index = reader.GetInt32(1), - Type = Enum.Parse<MediaStreamType>(reader.GetString(2), true) - }; - - if (reader.TryGetString(3, out var codec)) - { - item.Codec = codec; - } - - if (reader.TryGetString(4, out var language)) - { - item.Language = language; - } - - if (reader.TryGetString(5, out var channelLayout)) - { - item.ChannelLayout = channelLayout; - } - - if (reader.TryGetString(6, out var profile)) - { - item.Profile = profile; - } - - if (reader.TryGetString(7, out var aspectRatio)) - { - item.AspectRatio = aspectRatio; - } - - if (reader.TryGetString(8, out var path)) - { - item.Path = RestorePath(path); - } - - item.IsInterlaced = reader.GetBoolean(9); - - if (reader.TryGetInt32(10, out var bitrate)) - { - item.BitRate = bitrate; - } - - if (reader.TryGetInt32(11, out var channels)) - { - item.Channels = channels; - } - - if (reader.TryGetInt32(12, out var sampleRate)) - { - item.SampleRate = sampleRate; - } - - item.IsDefault = reader.GetBoolean(13); - item.IsForced = reader.GetBoolean(14); - item.IsExternal = reader.GetBoolean(15); - - if (reader.TryGetInt32(16, out var width)) - { - item.Width = width; - } - - if (reader.TryGetInt32(17, out var height)) - { - item.Height = height; - } - - if (reader.TryGetSingle(18, out var averageFrameRate)) - { - item.AverageFrameRate = averageFrameRate; - } - - if (reader.TryGetSingle(19, out var realFrameRate)) - { - item.RealFrameRate = realFrameRate; - } - - if (reader.TryGetSingle(20, out var level)) - { - item.Level = level; - } - - if (reader.TryGetString(21, out var pixelFormat)) - { - item.PixelFormat = pixelFormat; - } - - if (reader.TryGetInt32(22, out var bitDepth)) - { - item.BitDepth = bitDepth; - } - - if (reader.TryGetBoolean(23, out var isAnamorphic)) - { - item.IsAnamorphic = isAnamorphic; - } - - if (reader.TryGetInt32(24, out var refFrames)) - { - item.RefFrames = refFrames; - } - - if (reader.TryGetString(25, out var codecTag)) - { - item.CodecTag = codecTag; - } - - if (reader.TryGetString(26, out var comment)) - { - item.Comment = comment; - } - - if (reader.TryGetString(27, out var nalLengthSize)) - { - item.NalLengthSize = nalLengthSize; - } - - if (reader.TryGetBoolean(28, out var isAVC)) - { - item.IsAVC = isAVC; - } - - if (reader.TryGetString(29, out var title)) - { - item.Title = title; - } - - if (reader.TryGetString(30, out var timeBase)) - { - item.TimeBase = timeBase; - } - - if (reader.TryGetString(31, out var codecTimeBase)) - { - item.CodecTimeBase = codecTimeBase; - } - - if (reader.TryGetString(32, out var colorPrimaries)) - { - item.ColorPrimaries = colorPrimaries; - } - - if (reader.TryGetString(33, out var colorSpace)) - { - item.ColorSpace = colorSpace; - } - - if (reader.TryGetString(34, out var colorTransfer)) - { - item.ColorTransfer = colorTransfer; - } - - if (reader.TryGetInt32(35, out var dvVersionMajor)) - { - item.DvVersionMajor = dvVersionMajor; - } - - if (reader.TryGetInt32(36, out var dvVersionMinor)) - { - item.DvVersionMinor = dvVersionMinor; - } - - if (reader.TryGetInt32(37, out var dvProfile)) - { - item.DvProfile = dvProfile; - } - - if (reader.TryGetInt32(38, out var dvLevel)) - { - item.DvLevel = dvLevel; - } - - if (reader.TryGetInt32(39, out var rpuPresentFlag)) - { - item.RpuPresentFlag = rpuPresentFlag; - } - - if (reader.TryGetInt32(40, out var elPresentFlag)) - { - item.ElPresentFlag = elPresentFlag; - } - - if (reader.TryGetInt32(41, out var blPresentFlag)) - { - item.BlPresentFlag = blPresentFlag; - } - - if (reader.TryGetInt32(42, out var dvBlSignalCompatibilityId)) - { - item.DvBlSignalCompatibilityId = dvBlSignalCompatibilityId; - } - - item.IsHearingImpaired = reader.TryGetBoolean(43, out var result) && result; - - if (reader.TryGetInt32(44, out var rotation)) - { - item.Rotation = rotation; - } - - if (item.Type is MediaStreamType.Audio or MediaStreamType.Subtitle) - { - item.LocalizedDefault = _localization.GetLocalizedString("Default"); - item.LocalizedExternal = _localization.GetLocalizedString("External"); - - if (item.Type is MediaStreamType.Subtitle) - { - item.LocalizedUndefined = _localization.GetLocalizedString("Undefined"); - item.LocalizedForced = _localization.GetLocalizedString("Forced"); - item.LocalizedHearingImpaired = _localization.GetLocalizedString("HearingImpaired"); - } - } - - return item; - } /// <inheritdoc /> public List<MediaAttachment> GetMediaAttachments(MediaAttachmentQuery query) @@ -2205,21 +415,6 @@ AND Type = @InternalPersonType)"); return item; } - private static string BuildMediaAttachmentInsertPrefix() - { - var queryPrefixText = new StringBuilder(); - queryPrefixText.Append("insert into mediaattachments ("); - foreach (var column in _mediaAttachmentSaveColumns) - { - queryPrefixText.Append(column) - .Append(','); - } - - queryPrefixText.Length -= 1; - queryPrefixText.Append(") values "); - return queryPrefixText.ToString(); - } - #nullable enable private readonly struct QueryTimeLogger : IDisposable diff --git a/Jellyfin.Data/Entities/PeopleKind.cs b/Jellyfin.Data/Entities/PeopleKind.cs new file mode 100644 index 000000000..967f7c11f --- /dev/null +++ b/Jellyfin.Data/Entities/PeopleKind.cs @@ -0,0 +1,133 @@ +namespace Jellyfin.Data.Entities; + +/// <summary> +/// The person kind. +/// </summary> +public enum PeopleKind +{ + /// <summary> + /// An unknown person kind. + /// </summary> + Unknown, + + /// <summary> + /// A person whose profession is acting on the stage, in films, or on television. + /// </summary> + Actor, + + /// <summary> + /// A person who supervises the actors and other staff in a film, play, or similar production. + /// </summary> + Director, + + /// <summary> + /// A person who writes music, especially as a professional occupation. + /// </summary> + Composer, + + /// <summary> + /// A writer of a book, article, or document. Can also be used as a generic term for music writer if there is a lack of specificity. + /// </summary> + Writer, + + /// <summary> + /// A well-known actor or other performer who appears in a work in which they do not have a regular role. + /// </summary> + GuestStar, + + /// <summary> + /// A person responsible for the financial and managerial aspects of the making of a film or broadcast or for staging a play, opera, etc. + /// </summary> + Producer, + + /// <summary> + /// A person who directs the performance of an orchestra or choir. + /// </summary> + Conductor, + + /// <summary> + /// A person who writes the words to a song or musical. + /// </summary> + Lyricist, + + /// <summary> + /// A person who adapts a musical composition for performance. + /// </summary> + Arranger, + + /// <summary> + /// An audio engineer who performed a general engineering role. + /// </summary> + Engineer, + + /// <summary> + /// An engineer responsible for using a mixing console to mix a recorded track into a single piece of music suitable for release. + /// </summary> + Mixer, + + /// <summary> + /// A person who remixed a recording by taking one or more other tracks, substantially altering them and mixing them together with other material. + /// </summary> + Remixer, + + /// <summary> + /// A person who created the material. + /// </summary> + Creator, + + /// <summary> + /// A person who was the artist. + /// </summary> + Artist, + + /// <summary> + /// A person who was the album artist. + /// </summary> + AlbumArtist, + + /// <summary> + /// A person who was the author. + /// </summary> + Author, + + /// <summary> + /// A person who was the illustrator. + /// </summary> + Illustrator, + + /// <summary> + /// A person responsible for drawing the art. + /// </summary> + Penciller, + + /// <summary> + /// A person responsible for inking the pencil art. + /// </summary> + Inker, + + /// <summary> + /// A person responsible for applying color to drawings. + /// </summary> + Colorist, + + /// <summary> + /// A person responsible for drawing text and speech bubbles. + /// </summary> + Letterer, + + /// <summary> + /// A person responsible for drawing the cover art. + /// </summary> + CoverArtist, + + /// <summary> + /// A person contributing to a resource by revising or elucidating the content, e.g., adding an introduction, notes, or other critical matter. + /// An editor may also prepare a resource for production, publication, or distribution. + /// </summary> + Editor, + + /// <summary> + /// A person who renders a text from one language into another. + /// </summary> + Translator +} diff --git a/Jellyfin.Server.Implementations/Item/BaseItemManager.cs b/Jellyfin.Server.Implementations/Item/BaseItemManager.cs index f2d6b6261..022f26cd7 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemManager.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemManager.cs @@ -19,10 +19,12 @@ using MediaBrowser.Controller.Entities.TV; using MediaBrowser.Controller.LiveTv; using MediaBrowser.Controller.Persistence; using MediaBrowser.Controller.Playlists; +using MediaBrowser.Model.Dto; using MediaBrowser.Model.Entities; using MediaBrowser.Model.LiveTv; using MediaBrowser.Model.Querying; using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Internal; using Microsoft.EntityFrameworkCore.Query.SqlExpressions; using BaseItemDto = MediaBrowser.Controller.Entities.BaseItem; using BaseItemEntity = Jellyfin.Data.Entities.BaseItem; @@ -145,8 +147,72 @@ public class BaseItemManager : IItemRepository _appHost = appHost; } - private IQueryable<T> Pageinate<T>(IQueryable<T> query, InternalItemsQuery filter) + private QueryResult<(BaseItemDto Item, ItemCounts ItemCounts)> GetItemValues(InternalItemsQuery filter, int[] itemValueTypes, string returnType) { + ArgumentNullException.ThrowIfNull(filter); + + if (!filter.Limit.HasValue) + { + filter.EnableTotalRecordCount = false; + } + + using var context = _dbProvider.CreateDbContext(); + + var innerQuery = new InternalItemsQuery(filter.User) + { + ExcludeItemTypes = filter.ExcludeItemTypes, + IncludeItemTypes = filter.IncludeItemTypes, + MediaTypes = filter.MediaTypes, + AncestorIds = filter.AncestorIds, + ItemIds = filter.ItemIds, + TopParentIds = filter.TopParentIds, + ParentId = filter.ParentId, + IsAiring = filter.IsAiring, + IsMovie = filter.IsMovie, + IsSports = filter.IsSports, + IsKids = filter.IsKids, + IsNews = filter.IsNews, + IsSeries = filter.IsSeries + }; + var query = TranslateQuery(context.BaseItems, context, innerQuery); + + query = query.Where(e => e.Type == returnType && e.ItemValues!.Any(f => e.CleanName == f.CleanValue && itemValueTypes.Contains(f.Type))); + + var outerQuery = new InternalItemsQuery(filter.User) + { + IsPlayed = filter.IsPlayed, + IsFavorite = filter.IsFavorite, + IsFavoriteOrLiked = filter.IsFavoriteOrLiked, + IsLiked = filter.IsLiked, + IsLocked = filter.IsLocked, + NameLessThan = filter.NameLessThan, + NameStartsWith = filter.NameStartsWith, + NameStartsWithOrGreater = filter.NameStartsWithOrGreater, + Tags = filter.Tags, + OfficialRatings = filter.OfficialRatings, + StudioIds = filter.StudioIds, + GenreIds = filter.GenreIds, + Genres = filter.Genres, + Years = filter.Years, + NameContains = filter.NameContains, + SearchTerm = filter.SearchTerm, + SimilarTo = filter.SimilarTo, + ExcludeItemIds = filter.ExcludeItemIds + }; + query = TranslateQuery(query, context, outerQuery) + .OrderBy(e => e.PresentationUniqueKey); + + if (filter.OrderBy.Count != 0 + || filter.SimilarTo is not null + || !string.IsNullOrEmpty(filter.SearchTerm)) + { + query = ApplyOrder(query, filter); + } + else + { + query = query.OrderBy(e => e.SortName); + } + if (filter.Limit.HasValue || filter.StartIndex.HasValue) { var offset = filter.StartIndex ?? 0; @@ -158,131 +224,96 @@ public class BaseItemManager : IItemRepository if (filter.Limit.HasValue) { - query = query.Take(filter.Limit.Value); + query.Take(filter.Limit.Value); } } - return query; - } - - private Expression<Func<BaseItemEntity, object>> MapOrderByField(ItemSortBy sortBy, InternalItemsQuery query) - { -#pragma warning disable CS8603 // Possible null reference return. - return sortBy switch - { - ItemSortBy.AirTime => e => e.SortName, // TODO - ItemSortBy.Runtime => e => e.RunTimeTicks, - ItemSortBy.Random => e => EF.Functions.Random(), - ItemSortBy.DatePlayed => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.LastPlayedDate, - ItemSortBy.PlayCount => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.PlayCount, - ItemSortBy.IsFavoriteOrLiked => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.IsFavorite, - ItemSortBy.IsFolder => e => e.IsFolder, - ItemSortBy.IsPlayed => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.Played, - ItemSortBy.IsUnplayed => e => !e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.Played, - ItemSortBy.DateLastContentAdded => e => e.DateLastMediaAdded, - ItemSortBy.Artist => e => e.ItemValues!.Where(f => f.Type == 0).Select(f => f.CleanValue), - ItemSortBy.AlbumArtist => e => e.ItemValues!.Where(f => f.Type == 1).Select(f => f.CleanValue), - ItemSortBy.Studio => e => e.ItemValues!.Where(f => f.Type == 3).Select(f => f.CleanValue), - ItemSortBy.OfficialRating => e => e.InheritedParentalRatingValue, - // ItemSortBy.SeriesDatePlayed => "(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText(query) + " where Played=1 and B.SeriesPresentationUniqueKey=A.PresentationUniqueKey)", - ItemSortBy.SeriesSortName => e => e.SeriesName, - // ItemSortBy.AiredEpisodeOrder => "AiredEpisodeOrder", - ItemSortBy.Album => e => e.Album, - ItemSortBy.DateCreated => e => e.DateCreated, - ItemSortBy.PremiereDate => e => e.PremiereDate, - ItemSortBy.StartDate => e => e.StartDate, - ItemSortBy.Name => e => e.Name, - ItemSortBy.CommunityRating => e => e.CommunityRating, - ItemSortBy.ProductionYear => e => e.ProductionYear, - ItemSortBy.CriticRating => e => e.CriticRating, - ItemSortBy.VideoBitRate => e => e.TotalBitrate, - ItemSortBy.ParentIndexNumber => e => e.ParentIndexNumber, - ItemSortBy.IndexNumber => e => e.IndexNumber, - _ => e => e.SortName - }; -#pragma warning restore CS8603 // Possible null reference return. - - } - - private IQueryable<BaseItemEntity> MapOrderByField(IQueryable<BaseItemEntity> dbQuery, ItemSortBy sortBy, InternalItemsQuery query) - { - return sortBy switch + var result = new QueryResult<(BaseItem, ItemCounts)>(); + string countText = string.Empty; + if (filter.EnableTotalRecordCount) { - ItemSortBy.AirTime => dbQuery.OrderBy(e => e.SortName), // TODO - ItemSortBy.Runtime => dbQuery.OrderBy(e => e.RunTimeTicks), - ItemSortBy.Random => dbQuery.OrderBy(e => EF.Functions.Random()), - ItemSortBy.DatePlayed => dbQuery.OrderBy(e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.LastPlayedDate), - ItemSortBy.PlayCount => dbQuery.OrderBy(e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.PlayCount), - ItemSortBy.IsFavoriteOrLiked => dbQuery.OrderBy(e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.IsFavorite), - ItemSortBy.IsFolder => dbQuery.OrderBy(e => e.IsFolder), - ItemSortBy.IsPlayed => dbQuery.OrderBy(e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.Played), - ItemSortBy.IsUnplayed => dbQuery.OrderBy(e => !e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.Played), - ItemSortBy.DateLastContentAdded => dbQuery.OrderBy(e => e.DateLastMediaAdded), - ItemSortBy.Artist => dbQuery.OrderBy(e => e.ItemValues!.Where(f => f.Type == 0).Select(f => f.CleanValue)), - ItemSortBy.AlbumArtist => dbQuery.OrderBy(e => e.ItemValues!.Where(f => f.Type == 1).Select(f => f.CleanValue)), - ItemSortBy.Studio => dbQuery.OrderBy(e => e.ItemValues!.Where(f => f.Type == 3).Select(f => f.CleanValue)), - ItemSortBy.OfficialRating => dbQuery.OrderBy(e => e.InheritedParentalRatingValue), - // ItemSortBy.SeriesDatePlayed => "(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText(query) + " where Played=1 and B.SeriesPresentationUniqueKey=A.PresentationUniqueKey)", - ItemSortBy.SeriesSortName => dbQuery.OrderBy(e => e.SeriesName), - // ItemSortBy.AiredEpisodeOrder => "AiredEpisodeOrder", - ItemSortBy.Album => dbQuery.OrderBy(e => e.Album), - ItemSortBy.DateCreated => dbQuery.OrderBy(e => e.DateCreated), - ItemSortBy.PremiereDate => dbQuery.OrderBy(e => e.PremiereDate), - ItemSortBy.StartDate => dbQuery.OrderBy(e => e.StartDate), - ItemSortBy.Name => dbQuery.OrderBy(e => e.Name), - ItemSortBy.CommunityRating => dbQuery.OrderBy(e => e.CommunityRating), - ItemSortBy.ProductionYear => dbQuery.OrderBy(e => e.ProductionYear), - ItemSortBy.CriticRating => dbQuery.OrderBy(e => e.CriticRating), - ItemSortBy.VideoBitRate => dbQuery.OrderBy(e => e.TotalBitrate), - ItemSortBy.ParentIndexNumber => dbQuery.OrderBy(e => e.ParentIndexNumber), - ItemSortBy.IndexNumber => dbQuery.OrderBy(e => e.IndexNumber), - _ => dbQuery.OrderBy(e => e.SortName) - }; - } - - private IQueryable<BaseItemEntity> ApplyOrder(IQueryable<BaseItemEntity> query, InternalItemsQuery filter) - { - var orderBy = filter.OrderBy; - bool hasSearch = !string.IsNullOrEmpty(filter.SearchTerm); + result.TotalRecordCount = query.DistinctBy(e => e.PresentationUniqueKey).Count(); + } - if (hasSearch) + var resultQuery = query.Select(e => new { - List<(ItemSortBy, SortOrder)> prepend = new List<(ItemSortBy, SortOrder)>(4); - if (hasSearch) + item = e, + itemCount = new ItemCounts() { - prepend.Add((ItemSortBy.SortName, SortOrder.Ascending)); + SeriesCount = e.ItemValues!.Count(e => e.Type == (int)BaseItemKind.Series), + EpisodeCount = e.ItemValues!.Count(e => e.Type == (int)BaseItemKind.Episode), + MovieCount = e.ItemValues!.Count(e => e.Type == (int)BaseItemKind.Movie), + AlbumCount = e.ItemValues!.Count(e => e.Type == (int)BaseItemKind.MusicAlbum), + ArtistCount = e.ItemValues!.Count(e => e.Type == 0 || e.Type == 1), + SongCount = e.ItemValues!.Count(e => e.Type == (int)BaseItemKind.MusicAlbum), + TrailerCount = e.ItemValues!.Count(e => e.Type == (int)BaseItemKind.Trailer), } + }); - orderBy = filter.OrderBy = [.. prepend, .. orderBy]; - } - else if (orderBy.Count == 0) + result.StartIndex = filter.StartIndex ?? 0; + result.Items = resultQuery.ToImmutableArray().Select(e => { - return query; - } + return (DeserialiseBaseItem(e.item), e.itemCount); + }).ToImmutableArray(); - foreach (var item in orderBy) - { - var expression = MapOrderByField(item.OrderBy, filter); - if (item.SortOrder == SortOrder.Ascending) - { - query = query.OrderBy(expression); - } - else - { - query = query.OrderByDescending(expression); - } - } + return result; + } - return query; + /// <inheritdoc /> + public void DeleteItem(Guid id) + { + ArgumentNullException.ThrowIfNull(id.IsEmpty() ? null : id); + + using var context = _dbProvider.CreateDbContext(); + using var transaction = context.Database.BeginTransaction(); + context.Peoples.Where(e => e.ItemId.Equals(id)).ExecuteDelete(); + context.Chapters.Where(e => e.ItemId.Equals(id)).ExecuteDelete(); + context.MediaStreamInfos.Where(e => e.ItemId.Equals(id)).ExecuteDelete(); + context.AncestorIds.Where(e => e.ItemId.Equals(id)).ExecuteDelete(); + context.ItemValues.Where(e => e.ItemId.Equals(id)).ExecuteDelete(); + context.BaseItems.Where(e => e.Id.Equals(id)).ExecuteDelete(); + context.SaveChanges(); + transaction.Commit(); + } + + /// <inheritdoc /> + public void UpdateInheritedValues() + { + using var context = _dbProvider.CreateDbContext(); + using var transaction = context.Database.BeginTransaction(); + + context.ItemValues.Where(e => e.Type == 6).ExecuteDelete(); + context.ItemValues.AddRange(context.ItemValues.Where(e => e.Type == 4).Select(e => new Data.Entities.ItemValue() + { + CleanValue = e.CleanValue, + ItemId = e.ItemId, + Type = 6, + Value = e.Value, + Item = null! + })); + + context.ItemValues.AddRange( + context.AncestorIds.Where(e => e.AncestorIdText != null).Join(context.ItemValues.Where(e => e.Value != null && e.Type == 4), e => e.Id, e => e.ItemId, (e, f) => new Data.Entities.ItemValue() + { + CleanValue = f.CleanValue, + ItemId = e.ItemId, + Item = null!, + Type = 6, + Value = f.Value + })); + context.SaveChanges(); + + transaction.Commit(); } + /// <inheritdoc cref="IItemRepository"/> public IReadOnlyList<Guid> GetItemIdsList(InternalItemsQuery filter) { ArgumentNullException.ThrowIfNull(filter); PrepareFilterQuery(filter); using var context = _dbProvider.CreateDbContext(); - var dbQuery = TranslateQuery(context.BaseItems, context, filter) + var dbQuery = TranslateQuery(context.BaseItems.AsNoTracking(), context, filter) .DistinctBy(e => e.Id); var enableGroupByPresentationUniqueKey = EnableGroupByPresentationUniqueKey(filter); @@ -306,56 +337,57 @@ public class BaseItemManager : IItemRepository return Pageinate(dbQuery, filter).Select(e => e.Id).ToImmutableArray(); } - private bool EnableGroupByPresentationUniqueKey(InternalItemsQuery query) + /// <inheritdoc /> + public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetAllArtists(InternalItemsQuery query) { - if (!query.GroupByPresentationUniqueKey) - { - return false; - } + return GetItemValues(query, new[] { 0, 1 }, typeof(MusicArtist).FullName); + } - if (query.GroupBySeriesPresentationUniqueKey) - { - return false; - } + /// <inheritdoc /> + public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetArtists(InternalItemsQuery query) + { + return GetItemValues(query, new[] { 0 }, typeof(MusicArtist).FullName); + } - if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey)) - { - return false; - } + /// <inheritdoc /> + public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetAlbumArtists(InternalItemsQuery query) + { + return GetItemValues(query, new[] { 1 }, typeof(MusicArtist).FullName); + } - if (query.User is null) - { - return false; - } + /// <inheritdoc /> + public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetStudios(InternalItemsQuery query) + { + return GetItemValues(query, new[] { 3 }, typeof(Studio).FullName); + } - if (query.IncludeItemTypes.Length == 0) - { - return true; - } + /// <inheritdoc /> + public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetGenres(InternalItemsQuery query) + { + return GetItemValues(query, new[] { 2 }, typeof(Genre).FullName); + } - return query.IncludeItemTypes.Contains(BaseItemKind.Episode) - || query.IncludeItemTypes.Contains(BaseItemKind.Video) - || query.IncludeItemTypes.Contains(BaseItemKind.Movie) - || query.IncludeItemTypes.Contains(BaseItemKind.MusicVideo) - || query.IncludeItemTypes.Contains(BaseItemKind.Series) - || query.IncludeItemTypes.Contains(BaseItemKind.Season); + /// <inheritdoc /> + public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetMusicGenres(InternalItemsQuery query) + { + return GetItemValues(query, new[] { 2 }, typeof(MusicGenre).FullName); } /// <inheritdoc cref="IItemRepository"/> - public QueryResult<BaseItem> GetItems(InternalItemsQuery query) + public QueryResult<BaseItemDto> GetItems(InternalItemsQuery query) { ArgumentNullException.ThrowIfNull(query); if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0)) { var returnList = GetItemList(query); - return new QueryResult<BaseItem>( + return new QueryResult<BaseItemDto>( query.StartIndex, returnList.Count, returnList); } PrepareFilterQuery(query); - var result = new QueryResult<BaseItem>(); + var result = new QueryResult<BaseItemDto>(); using var context = _dbProvider.CreateDbContext(); var dbQuery = TranslateQuery(context.BaseItems, context, query) @@ -2094,4 +2126,134 @@ public class BaseItemManager : IItemRepository return query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(type); } + + private IQueryable<T> Pageinate<T>(IQueryable<T> query, InternalItemsQuery filter) + { + if (filter.Limit.HasValue || filter.StartIndex.HasValue) + { + var offset = filter.StartIndex ?? 0; + + if (offset > 0) + { + query = query.Skip(offset); + } + + if (filter.Limit.HasValue) + { + query = query.Take(filter.Limit.Value); + } + } + + return query; + } + + private Expression<Func<BaseItemEntity, object>> MapOrderByField(ItemSortBy sortBy, InternalItemsQuery query) + { +#pragma warning disable CS8603 // Possible null reference return. + return sortBy switch + { + ItemSortBy.AirTime => e => e.SortName, // TODO + ItemSortBy.Runtime => e => e.RunTimeTicks, + ItemSortBy.Random => e => EF.Functions.Random(), + ItemSortBy.DatePlayed => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.LastPlayedDate, + ItemSortBy.PlayCount => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.PlayCount, + ItemSortBy.IsFavoriteOrLiked => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.IsFavorite, + ItemSortBy.IsFolder => e => e.IsFolder, + ItemSortBy.IsPlayed => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.Played, + ItemSortBy.IsUnplayed => e => !e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.Played, + ItemSortBy.DateLastContentAdded => e => e.DateLastMediaAdded, + ItemSortBy.Artist => e => e.ItemValues!.Where(f => f.Type == 0).Select(f => f.CleanValue), + ItemSortBy.AlbumArtist => e => e.ItemValues!.Where(f => f.Type == 1).Select(f => f.CleanValue), + ItemSortBy.Studio => e => e.ItemValues!.Where(f => f.Type == 3).Select(f => f.CleanValue), + ItemSortBy.OfficialRating => e => e.InheritedParentalRatingValue, + // ItemSortBy.SeriesDatePlayed => "(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText(query) + " where Played=1 and B.SeriesPresentationUniqueKey=A.PresentationUniqueKey)", + ItemSortBy.SeriesSortName => e => e.SeriesName, + // ItemSortBy.AiredEpisodeOrder => "AiredEpisodeOrder", + ItemSortBy.Album => e => e.Album, + ItemSortBy.DateCreated => e => e.DateCreated, + ItemSortBy.PremiereDate => e => e.PremiereDate, + ItemSortBy.StartDate => e => e.StartDate, + ItemSortBy.Name => e => e.Name, + ItemSortBy.CommunityRating => e => e.CommunityRating, + ItemSortBy.ProductionYear => e => e.ProductionYear, + ItemSortBy.CriticRating => e => e.CriticRating, + ItemSortBy.VideoBitRate => e => e.TotalBitrate, + ItemSortBy.ParentIndexNumber => e => e.ParentIndexNumber, + ItemSortBy.IndexNumber => e => e.IndexNumber, + _ => e => e.SortName + }; +#pragma warning restore CS8603 // Possible null reference return. + + } + + private bool EnableGroupByPresentationUniqueKey(InternalItemsQuery query) + { + if (!query.GroupByPresentationUniqueKey) + { + return false; + } + + if (query.GroupBySeriesPresentationUniqueKey) + { + return false; + } + + if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey)) + { + return false; + } + + if (query.User is null) + { + return false; + } + + if (query.IncludeItemTypes.Length == 0) + { + return true; + } + + return query.IncludeItemTypes.Contains(BaseItemKind.Episode) + || query.IncludeItemTypes.Contains(BaseItemKind.Video) + || query.IncludeItemTypes.Contains(BaseItemKind.Movie) + || query.IncludeItemTypes.Contains(BaseItemKind.MusicVideo) + || query.IncludeItemTypes.Contains(BaseItemKind.Series) + || query.IncludeItemTypes.Contains(BaseItemKind.Season); + } + + private IQueryable<BaseItemEntity> ApplyOrder(IQueryable<BaseItemEntity> query, InternalItemsQuery filter) + { + var orderBy = filter.OrderBy; + bool hasSearch = !string.IsNullOrEmpty(filter.SearchTerm); + + if (hasSearch) + { + List<(ItemSortBy, SortOrder)> prepend = new List<(ItemSortBy, SortOrder)>(4); + if (hasSearch) + { + prepend.Add((ItemSortBy.SortName, SortOrder.Ascending)); + } + + orderBy = filter.OrderBy = [.. prepend, .. orderBy]; + } + else if (orderBy.Count == 0) + { + return query; + } + + foreach (var item in orderBy) + { + var expression = MapOrderByField(item.OrderBy, filter); + if (item.SortOrder == SortOrder.Ascending) + { + query = query.OrderBy(expression); + } + else + { + query = query.OrderByDescending(expression); + } + } + + return query; + } } diff --git a/Jellyfin.Server.Implementations/Item/MediaStreamManager.cs b/Jellyfin.Server.Implementations/Item/MediaStreamManager.cs new file mode 100644 index 000000000..e609cdc1e --- /dev/null +++ b/Jellyfin.Server.Implementations/Item/MediaStreamManager.cs @@ -0,0 +1,201 @@ +using System; +using System.Collections.Generic; +using System.Collections.Immutable; +using System.Linq; +using System.Threading; +using Jellyfin.Data.Entities; +using MediaBrowser.Controller; +using MediaBrowser.Controller.Persistence; +using MediaBrowser.Model.Entities; +using MediaBrowser.Model.Globalization; +using Microsoft.EntityFrameworkCore; + +namespace Jellyfin.Server.Implementations.Item; + +/// <summary> +/// Initializes a new instance of the <see cref="MediaStreamManager"/> class. +/// </summary> +/// <param name="dbProvider"></param> +/// <param name="serverApplicationHost"></param> +/// <param name="localization"></param> +public class MediaStreamManager(IDbContextFactory<JellyfinDbContext> dbProvider, IServerApplicationHost serverApplicationHost, ILocalizationManager localization) +{ + /// <inheritdoc /> + public void SaveMediaStreams(Guid id, IReadOnlyList<MediaStream> streams, CancellationToken cancellationToken) + { + using var context = dbProvider.CreateDbContext(); + using var transaction = context.Database.BeginTransaction(); + + context.MediaStreamInfos.Where(e => e.ItemId.Equals(id)).ExecuteDelete(); + context.MediaStreamInfos.AddRange(streams.Select(f => Map(f, id))); + context.SaveChanges(); + + transaction.Commit(); + } + + /// <inheritdoc /> + public IReadOnlyList<MediaStream> GetMediaStreams(MediaStreamQuery filter) + { + using var context = dbProvider.CreateDbContext(); + return TranslateQuery(context.MediaStreamInfos, filter).ToList().Select(Map).ToImmutableArray(); + } + + private string? GetPathToSave(string? path) + { + if (path is null) + { + return null; + } + + return serverApplicationHost.ReverseVirtualPath(path); + } + + private string? RestorePath(string? path) + { + if (path is null) + { + return null; + } + + return serverApplicationHost.ExpandVirtualPath(path); + } + + private IQueryable<MediaStreamInfo> TranslateQuery(IQueryable<MediaStreamInfo> query, MediaStreamQuery filter) + { + query = query.Where(e => e.ItemId.Equals(filter.ItemId)); + if (filter.Index.HasValue) + { + query = query.Where(e => e.StreamIndex == filter.Index); + } + + if (filter.Type.HasValue) + { + query = query.Where(e => e.StreamType == filter.Type.ToString()); + } + + return query; + } + + private MediaStream Map(MediaStreamInfo entity) + { + var dto = new MediaStream(); + dto.Index = entity.StreamIndex; + if (entity.StreamType != null) + { + dto.Type = Enum.Parse<MediaStreamType>(entity.StreamType); + } + + dto.IsAVC = entity.IsAvc; + dto.Codec = entity.Codec; + dto.Language = entity.Language; + dto.ChannelLayout = entity.ChannelLayout; + dto.Profile = entity.Profile; + dto.AspectRatio = entity.AspectRatio; + dto.Path = RestorePath(entity.Path); + dto.IsInterlaced = entity.IsInterlaced; + dto.BitRate = entity.BitRate; + dto.Channels = entity.Channels; + dto.SampleRate = entity.SampleRate; + dto.IsDefault = entity.IsDefault; + dto.IsForced = entity.IsForced; + dto.IsExternal = entity.IsExternal; + dto.Height = entity.Height; + dto.Width = entity.Width; + dto.AverageFrameRate = entity.AverageFrameRate; + dto.RealFrameRate = entity.RealFrameRate; + dto.Level = entity.Level; + dto.PixelFormat = entity.PixelFormat; + dto.BitDepth = entity.BitDepth; + dto.IsAnamorphic = entity.IsAnamorphic; + dto.RefFrames = entity.RefFrames; + dto.CodecTag = entity.CodecTag; + dto.Comment = entity.Comment; + dto.NalLengthSize = entity.NalLengthSize; + dto.Title = entity.Title; + dto.TimeBase = entity.TimeBase; + dto.CodecTimeBase = entity.CodecTimeBase; + dto.ColorPrimaries = entity.ColorPrimaries; + dto.ColorSpace = entity.ColorSpace; + dto.ColorTransfer = entity.ColorTransfer; + dto.DvVersionMajor = entity.DvVersionMajor; + dto.DvVersionMinor = entity.DvVersionMinor; + dto.DvProfile = entity.DvProfile; + dto.DvLevel = entity.DvLevel; + dto.RpuPresentFlag = entity.RpuPresentFlag; + dto.ElPresentFlag = entity.ElPresentFlag; + dto.BlPresentFlag = entity.BlPresentFlag; + dto.DvBlSignalCompatibilityId = entity.DvBlSignalCompatibilityId; + dto.IsHearingImpaired = entity.IsHearingImpaired; + dto.Rotation = entity.Rotation; + + if (dto.Type is MediaStreamType.Audio or MediaStreamType.Subtitle) + { + dto.LocalizedDefault = localization.GetLocalizedString("Default"); + dto.LocalizedExternal = localization.GetLocalizedString("External"); + + if (dto.Type is MediaStreamType.Subtitle) + { + dto.LocalizedUndefined = localization.GetLocalizedString("Undefined"); + dto.LocalizedForced = localization.GetLocalizedString("Forced"); + dto.LocalizedHearingImpaired = localization.GetLocalizedString("HearingImpaired"); + } + } + + return dto; + } + + private MediaStreamInfo Map(MediaStream dto, Guid itemId) + { + var entity = new MediaStreamInfo + { + Item = null!, + ItemId = itemId, + StreamIndex = dto.Index, + StreamType = dto.Type.ToString(), + IsAvc = dto.IsAVC.GetValueOrDefault(), + + Codec = dto.Codec, + Language = dto.Language, + ChannelLayout = dto.ChannelLayout, + Profile = dto.Profile, + AspectRatio = dto.AspectRatio, + Path = GetPathToSave(dto.Path), + IsInterlaced = dto.IsInterlaced, + BitRate = dto.BitRate.GetValueOrDefault(0), + Channels = dto.Channels.GetValueOrDefault(0), + SampleRate = dto.SampleRate.GetValueOrDefault(0), + IsDefault = dto.IsDefault, + IsForced = dto.IsForced, + IsExternal = dto.IsExternal, + Height = dto.Height.GetValueOrDefault(0), + Width = dto.Width.GetValueOrDefault(0), + AverageFrameRate = dto.AverageFrameRate.GetValueOrDefault(0), + RealFrameRate = dto.RealFrameRate.GetValueOrDefault(0), + Level = (float)dto.Level.GetValueOrDefault(), + PixelFormat = dto.PixelFormat, + BitDepth = dto.BitDepth.GetValueOrDefault(0), + IsAnamorphic = dto.IsAnamorphic.GetValueOrDefault(0), + RefFrames = dto.RefFrames.GetValueOrDefault(0), + CodecTag = dto.CodecTag, + Comment = dto.Comment, + NalLengthSize = dto.NalLengthSize, + Title = dto.Title, + TimeBase = dto.TimeBase, + CodecTimeBase = dto.CodecTimeBase, + ColorPrimaries = dto.ColorPrimaries, + ColorSpace = dto.ColorSpace, + ColorTransfer = dto.ColorTransfer, + DvVersionMajor = dto.DvVersionMajor.GetValueOrDefault(0), + DvVersionMinor = dto.DvVersionMinor.GetValueOrDefault(0), + DvProfile = dto.DvProfile.GetValueOrDefault(0), + DvLevel = dto.DvLevel.GetValueOrDefault(0), + RpuPresentFlag = dto.RpuPresentFlag.GetValueOrDefault(0), + ElPresentFlag = dto.ElPresentFlag.GetValueOrDefault(0), + BlPresentFlag = dto.BlPresentFlag.GetValueOrDefault(0), + DvBlSignalCompatibilityId = dto.DvBlSignalCompatibilityId.GetValueOrDefault(0), + IsHearingImpaired = dto.IsHearingImpaired, + Rotation = dto.Rotation.GetValueOrDefault(0) + }; + return entity; + } +} diff --git a/Jellyfin.Server.Implementations/Item/PeopleManager.cs b/Jellyfin.Server.Implementations/Item/PeopleManager.cs new file mode 100644 index 000000000..0f1760cbd --- /dev/null +++ b/Jellyfin.Server.Implementations/Item/PeopleManager.cs @@ -0,0 +1,164 @@ +using System; +using System.Collections.Generic; +using System.Collections.Immutable; +using System.Linq; +using Jellyfin.Data.Entities; +using Jellyfin.Data.Enums; +using Jellyfin.Extensions; +using MediaBrowser.Controller.Entities; +using Microsoft.EntityFrameworkCore; + +namespace Jellyfin.Server.Implementations.Item; + +public class PeopleManager +{ + private readonly IDbContextFactory<JellyfinDbContext> _dbProvider; + + /// <summary> + /// Initializes a new instance of the <see cref="PeopleManager"/> class. + /// </summary> + /// <param name="dbProvider">The EFCore Context factory.</param> + public PeopleManager(IDbContextFactory<JellyfinDbContext> dbProvider) + { + _dbProvider = dbProvider; + } + + public IReadOnlyList<PersonInfo> GetPeople(InternalPeopleQuery filter) + { + using var context = _dbProvider.CreateDbContext(); + var dbQuery = TranslateQuery(context.Peoples.AsNoTracking(), context, filter); + + dbQuery = dbQuery.OrderBy(e => e.ListOrder); + if (filter.Limit > 0) + { + dbQuery = dbQuery.Take(filter.Limit); + } + + return dbQuery.ToList().Select(Map).ToImmutableArray(); + } + + public IReadOnlyList<string> GetPeopleNames(InternalPeopleQuery filter) + { + using var context = _dbProvider.CreateDbContext(); + var dbQuery = TranslateQuery(context.Peoples.AsNoTracking(), context, filter); + + dbQuery = dbQuery.OrderBy(e => e.ListOrder); + if (filter.Limit > 0) + { + dbQuery = dbQuery.Take(filter.Limit); + } + + return dbQuery.Select(e => e.Name).ToImmutableArray(); + } + + /// <inheritdoc /> + public void UpdatePeople(Guid itemId, IReadOnlyList<PersonInfo> people) + { + using var context = _dbProvider.CreateDbContext(); + using var transaction = context.Database.BeginTransaction(); + + context.Peoples.Where(e => e.ItemId.Equals(itemId)).ExecuteDelete(); + context.Peoples.AddRange(people.Select(Map)); + context.SaveChanges(); + transaction.Commit(); + } + + private PersonInfo Map(People people) + { + var personInfo = new PersonInfo() + { + ItemId = people.ItemId, + Name = people.Name, + Role = people.Role, + SortOrder = people.SortOrder, + }; + if (Enum.TryParse<PersonKind>(people.PersonType, out var kind)) + { + personInfo.Type = kind; + } + + return personInfo; + } + + private People Map(PersonInfo people) + { + var personInfo = new People() + { + ItemId = people.ItemId, + Name = people.Name, + Role = people.Role, + SortOrder = people.SortOrder, + PersonType = people.Type.ToString() + }; + + return personInfo; + } + + private IQueryable<People> TranslateQuery(IQueryable<People> query, JellyfinDbContext context, InternalPeopleQuery filter) + { + if (filter.User is not null && filter.IsFavorite.HasValue) + { + query = query.Where(e => e.PersonType == typeof(Person).FullName) + .Where(e => context.BaseItems.Where(d => context.UserData.Where(e => e.IsFavorite == filter.IsFavorite && e.UserId.Equals(filter.User.Id)).Any(f => f.Key == d.UserDataKey)) + .Select(f => f.Name).Contains(e.Name)); + } + + if (!filter.ItemId.IsEmpty()) + { + query = query.Where(e => e.ItemId.Equals(filter.ItemId)); + } + + if (!filter.AppearsInItemId.IsEmpty()) + { + query = query.Where(e => context.Peoples.Where(f => f.ItemId.Equals(filter.AppearsInItemId)).Select(e => e.Name).Contains(e.Name)); + } + + var queryPersonTypes = filter.PersonTypes.Where(IsValidPersonType).ToList(); + if (queryPersonTypes.Count > 0) + { + query = query.Where(e => queryPersonTypes.Contains(e.PersonType)); + } + + var queryExcludePersonTypes = filter.ExcludePersonTypes.Where(IsValidPersonType).ToList(); + + if (queryExcludePersonTypes.Count > 0) + { + query = query.Where(e => !queryPersonTypes.Contains(e.PersonType)); + } + + if (filter.MaxListOrder.HasValue) + { + query = query.Where(e => e.ListOrder <= filter.MaxListOrder.Value); + } + + if (!string.IsNullOrWhiteSpace(filter.NameContains)) + { + query = query.Where(e => e.Name.Contains(filter.NameContains)); + } + + return query; + } + + private bool IsAlphaNumeric(string str) + { + if (string.IsNullOrWhiteSpace(str)) + { + return false; + } + + for (int i = 0; i < str.Length; i++) + { + if (!char.IsLetter(str[i]) && !char.IsNumber(str[i])) + { + return false; + } + } + + return true; + } + + private bool IsValidPersonType(string value) + { + return IsAlphaNumeric(value); + } +} |