From 2086ac7dd2f64f286ea3c88a53cc0860f28454f8 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sun, 18 Jan 2026 14:59:57 +0100 Subject: Don't use raw SQL --- .../DescendantQueryHelper.cs | 161 +++++++++++++++++++++ .../IDescendantQueryProvider.cs | 30 ---- .../IJellyfinDatabaseProvider.cs | 6 - .../SqliteDatabaseProvider.cs | 3 - .../SqliteDescendantQueryProvider.cs | 129 ----------------- 5 files changed, 161 insertions(+), 168 deletions(-) create mode 100644 src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs delete mode 100644 src/Jellyfin.Database/Jellyfin.Database.Implementations/IDescendantQueryProvider.cs delete mode 100644 src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDescendantQueryProvider.cs (limited to 'src') diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs new file mode 100644 index 0000000000..e6fa6ca458 --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs @@ -0,0 +1,161 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using Jellyfin.Database.Implementations.Entities; +using Jellyfin.Database.Implementations.MatchCriteria; + +namespace Jellyfin.Database.Implementations; + +/// +/// Provides methods for querying item hierarchies using iterative traversal. +/// Uses AncestorIds and LinkedChildren tables for parent-child traversal. +/// +public static class DescendantQueryHelper +{ + /// + /// Gets a queryable of all descendant IDs for a parent item. + /// Traverses AncestorIds and LinkedChildren to find all descendants. + /// + /// Database context. + /// Parent item ID. + /// Queryable of descendant item IDs. + public static IQueryable GetAllDescendantIds(JellyfinDbContext context, Guid parentId) + { + ArgumentNullException.ThrowIfNull(context); + + var descendants = TraverseHierarchyDown(context, [parentId]); + + descendants.Remove(parentId); + + return descendants.AsQueryable(); + } + + /// + /// Gets a queryable of all folder IDs that have any descendant matching the specified criteria. + /// Can be used in LINQ .Contains() expressions. + /// + /// Database context. + /// The matching criteria to apply. + /// Queryable of folder IDs. + public static IQueryable GetFolderIdsMatching(JellyfinDbContext context, FolderMatchCriteria criteria) + { + ArgumentNullException.ThrowIfNull(context); + ArgumentNullException.ThrowIfNull(criteria); + var matchingItemIds = criteria switch + { + HasSubtitles => context.MediaStreamInfos + .Where(ms => ms.StreamType == MediaStreamTypeEntity.Subtitle) + .Select(ms => ms.ItemId) + .Distinct() + .ToHashSet(), + HasChapterImages => context.Chapters + .Where(c => c.ImagePath != null) + .Select(c => c.ItemId) + .Distinct() + .ToHashSet(), + HasMediaStreamType m => GetMatchingMediaStreamItemIds(context, m), + _ => throw new ArgumentOutOfRangeException(nameof(criteria), $"Unknown criteria type: {criteria.GetType().Name}") + }; + + var ancestors = TraverseHierarchyUp(context, matchingItemIds); + + return ancestors.AsQueryable(); + } + + private static HashSet GetMatchingMediaStreamItemIds(JellyfinDbContext context, HasMediaStreamType criteria) + { + var query = context.MediaStreamInfos + .Where(ms => ms.StreamType == criteria.StreamType && ms.Language == criteria.Language); + + if (criteria.IsExternal.HasValue) + { + var isExternal = criteria.IsExternal.Value; + query = query.Where(ms => ms.IsExternal == isExternal); + } + + return query.Select(ms => ms.ItemId).Distinct().ToHashSet(); + } + + /// + /// Traverses DOWN the hierarchy from parent folders to find all descendants. + /// + private static HashSet TraverseHierarchyDown(JellyfinDbContext context, ICollection startIds) + { + var visited = new HashSet(startIds); + var folderStack = new HashSet(startIds); + + while (folderStack.Count != 0) + { + var currentFolders = folderStack.ToArray(); + folderStack.Clear(); + + var directChildren = context.AncestorIds + .WhereOneOrMany(currentFolders, e => e.ParentItemId) + .Select(e => e.ItemId) + .ToArray(); + + var linkedChildren = context.LinkedChildren + .WhereOneOrMany(currentFolders, e => e.ParentId) + .Select(e => e.ChildId) + .ToArray(); + + var allChildren = directChildren.Concat(linkedChildren).Distinct().ToArray(); + + if (allChildren.Length == 0) + { + break; + } + + var childFolders = context.BaseItems + .WhereOneOrMany(allChildren, e => e.Id) + .Where(e => e.IsFolder) + .Select(e => e.Id) + .ToHashSet(); + + foreach (var childId in allChildren) + { + if (visited.Add(childId) && childFolders.Contains(childId)) + { + folderStack.Add(childId); + } + } + } + + return visited; + } + + /// + /// Traverses UP the hierarchy from items to find all ancestor folders. + /// + private static HashSet TraverseHierarchyUp(JellyfinDbContext context, ICollection startIds) + { + var ancestors = new HashSet(); + var itemStack = new HashSet(startIds); + + while (itemStack.Count != 0) + { + var currentItems = itemStack.ToArray(); + itemStack.Clear(); + + var ancestorParents = context.AncestorIds + .WhereOneOrMany(currentItems, e => e.ItemId) + .Select(e => e.ParentItemId) + .ToArray(); + + var linkedParents = context.LinkedChildren + .WhereOneOrMany(currentItems, e => e.ChildId) + .Select(e => e.ParentId) + .ToArray(); + + foreach (var parentId in ancestorParents.Concat(linkedParents)) + { + if (ancestors.Add(parentId)) + { + itemStack.Add(parentId); + } + } + } + + return ancestors; + } +} diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IDescendantQueryProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IDescendantQueryProvider.cs deleted file mode 100644 index 9e3d510b9c..0000000000 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IDescendantQueryProvider.cs +++ /dev/null @@ -1,30 +0,0 @@ -using System; -using System.Linq; -using Jellyfin.Database.Implementations.MatchCriteria; - -namespace Jellyfin.Database.Implementations; - -/// -/// Provider interface for descendant queries using recursive CTEs. -/// Each database provider implements this with provider-specific SQL. -/// -public interface IDescendantQueryProvider -{ - /// - /// Gets a queryable of all descendant IDs for a parent item. - /// Uses recursive CTE to traverse AncestorIds and LinkedChildren infinitely. - /// - /// Database context. - /// Parent item ID. - /// Queryable of descendant item IDs. - IQueryable GetAllDescendantIds(JellyfinDbContext context, Guid parentId); - - /// - /// Gets a queryable of all folder IDs that have any descendant matching the specified criteria. - /// Uses recursive CTE for infinite depth traversal. Can be used in LINQ .Contains() expressions. - /// - /// Database context. - /// The matching criteria to apply. - /// Queryable of folder IDs. - IQueryable GetFolderIdsMatching(JellyfinDbContext context, FolderMatchCriteria criteria); -} diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs index f52a68c684..27dbeaba6a 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs @@ -17,12 +17,6 @@ public interface IJellyfinDatabaseProvider /// IDbContextFactory? DbContextFactory { get; set; } - /// - /// Gets the descendant query provider for this database type. - /// Used for recursive CTE queries to find all descendants of an item. - /// - IDescendantQueryProvider DescendantQueryProvider { get; } - /// /// Initialises jellyfins EFCore database access. /// diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs index 94c470e6cb..da63df8e29 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs @@ -39,9 +39,6 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider /// public IDbContextFactory? DbContextFactory { get; set; } - /// - public IDescendantQueryProvider DescendantQueryProvider { get; } = new SqliteDescendantQueryProvider(); - /// public void Initialise(DbContextOptionsBuilder options, DatabaseConfigurationOptions databaseConfiguration) { diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDescendantQueryProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDescendantQueryProvider.cs deleted file mode 100644 index 756f750bf9..0000000000 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDescendantQueryProvider.cs +++ /dev/null @@ -1,129 +0,0 @@ -using System; -using System.Linq; -using Jellyfin.Database.Implementations; -using Jellyfin.Database.Implementations.Entities; -using Jellyfin.Database.Implementations.MatchCriteria; -using Microsoft.EntityFrameworkCore; - -namespace Jellyfin.Database.Providers.Sqlite; - -/// -/// SQLite implementation of descendant queries using optimized ancestor lookups. -/// Uses AncestorIds and LinkedChildren tables for efficient parent-child traversal. -/// -public class SqliteDescendantQueryProvider : IDescendantQueryProvider -{ - /// - /// Recursive CTE fragment that traverses UP the tree from matching items to find all ancestor folders. - /// Expects a preceding CTE named "MatchingItems" with an ItemId column. - /// - private const string AllAncestorsCte = """ - AllAncestors AS ( - SELECT a.ParentItemId AS AncestorId - FROM AncestorIds a - WHERE a.ItemId IN (SELECT ItemId FROM MatchingItems) - UNION - SELECT lc.ParentId AS AncestorId - FROM LinkedChildren lc - WHERE lc.ChildId IN (SELECT ItemId FROM MatchingItems) - UNION - SELECT a.ParentItemId AS AncestorId - FROM AllAncestors aa - INNER JOIN AncestorIds a ON a.ItemId = aa.AncestorId - UNION - SELECT lc.ParentId AS AncestorId - FROM AllAncestors aa - INNER JOIN LinkedChildren lc ON lc.ChildId = aa.AncestorId - ) - SELECT DISTINCT AncestorId AS Value FROM AllAncestors - """; - - /// - public IQueryable GetAllDescendantIds(JellyfinDbContext context, Guid parentId) - { - ArgumentNullException.ThrowIfNull(context); - - var sql = """ - WITH RECURSIVE AllDescendants AS ( - SELECT ItemId FROM AncestorIds WHERE ParentItemId = {0} - UNION - SELECT ChildId AS ItemId FROM LinkedChildren WHERE ParentId = {0} - UNION ALL - SELECT a.ItemId - FROM AllDescendants d - INNER JOIN BaseItems b ON b.Id = d.ItemId AND b.IsFolder = 1 - INNER JOIN AncestorIds a ON a.ParentItemId = d.ItemId - UNION ALL - SELECT lc.ChildId AS ItemId - FROM AllDescendants d - INNER JOIN BaseItems b ON b.Id = d.ItemId AND b.IsFolder = 1 - INNER JOIN LinkedChildren lc ON lc.ParentId = d.ItemId - ) - SELECT DISTINCT ItemId AS Value FROM AllDescendants - """; - - return context.Database.SqlQueryRaw(sql, parentId); - } - - /// - public IQueryable GetFolderIdsMatching(JellyfinDbContext context, FolderMatchCriteria criteria) - { - ArgumentNullException.ThrowIfNull(context); - ArgumentNullException.ThrowIfNull(criteria); - - return criteria switch - { - HasSubtitles => GetFolderIdsWithSubtitles(context), - HasChapterImages => GetFolderIdsWithChapterImages(context), - HasMediaStreamType m => GetFolderIdsWithMediaStream(context, m.StreamType, m.Language, m.IsExternal), - _ => throw new ArgumentOutOfRangeException(nameof(criteria), $"Unknown criteria type: {criteria.GetType().Name}") - }; - } - - private IQueryable GetFolderIdsWithSubtitles(JellyfinDbContext context) - { - var sql = $""" - WITH RECURSIVE MatchingItems AS ( - SELECT DISTINCT ms.ItemId FROM MediaStreamInfos ms WHERE ms.StreamType = 2 - ), - {AllAncestorsCte} - """; - - return context.Database.SqlQueryRaw(sql); - } - - private IQueryable GetFolderIdsWithChapterImages(JellyfinDbContext context) - { - var sql = $""" - WITH RECURSIVE MatchingItems AS ( - SELECT DISTINCT c.ItemId FROM Chapters c WHERE c.ImagePath IS NOT NULL - ), - {AllAncestorsCte} - """; - - return context.Database.SqlQueryRaw(sql); - } - - private IQueryable GetFolderIdsWithMediaStream(JellyfinDbContext context, MediaStreamTypeEntity streamType, string language, bool? isExternal) - { - ArgumentNullException.ThrowIfNull(language); - - var streamTypeInt = (int)streamType; - var externalCondition = isExternal switch - { - true => " AND ms.IsExternal = 1", - false => " AND ms.IsExternal = 0", - null => string.Empty - }; - - var sql = $$""" - WITH RECURSIVE MatchingItems AS ( - SELECT DISTINCT ms.ItemId FROM MediaStreamInfos ms - WHERE ms.StreamType = {0} AND ms.Language = {1}{{externalCondition}} - ), - {{AllAncestorsCte}} - """; - - return context.Database.SqlQueryRaw(sql, streamTypeInt, language); - } -} -- cgit v1.2.3