From 4b7093e923b457f34f717f286ebb08514cfb9067 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Sun, 20 Mar 2016 02:46:51 -0400 Subject: get recursive items from db --- .../Persistence/SqliteItemRepository.cs | 104 ++++++++++++++++++--- 1 file changed, 93 insertions(+), 11 deletions(-) (limited to 'MediaBrowser.Server.Implementations/Persistence') diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 50662c90f..12e444017 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -1583,11 +1583,15 @@ namespace MediaBrowser.Server.Implementations.Persistence private string MapOrderByField(string name) { - if (string.Equals(name, "airtime", StringComparison.OrdinalIgnoreCase)) + if (string.Equals(name, ItemSortBy.AirTime, StringComparison.OrdinalIgnoreCase)) { // TODO return "SortName"; } + if (string.Equals(name, ItemSortBy.Runtime, StringComparison.OrdinalIgnoreCase)) + { + return "RuntimeTicks"; + } return name; } @@ -1783,6 +1787,16 @@ namespace MediaBrowser.Server.Implementations.Persistence } cmd.Parameters.Add(cmd, "@SchemaVersion", DbType.Int32).Value = LatestSchemaVersion; } + if (query.IsHD.HasValue) + { + whereClauses.Add("IsHD=@IsHD"); + cmd.Parameters.Add(cmd, "@IsHD", DbType.Boolean).Value = query.IsHD; + } + if (query.IsLocked.HasValue) + { + whereClauses.Add("IsLocked=@IsLocked"); + cmd.Parameters.Add(cmd, "@IsLocked", DbType.Boolean).Value = query.IsLocked; + } if (query.IsOffline.HasValue) { whereClauses.Add("IsOffline=@IsOffline"); @@ -1861,6 +1875,30 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.Parameters.Add(cmd, "@Path", DbType.String).Value = query.Path; } + if (query.MinCommunityRating.HasValue) + { + whereClauses.Add("CommunityRating>=@MinCommunityRating"); + cmd.Parameters.Add(cmd, "@MinCommunityRating", DbType.Double).Value = query.MinCommunityRating.Value; + } + + if (query.MinIndexNumber.HasValue) + { + whereClauses.Add("IndexNumber>=@MinIndexNumber"); + cmd.Parameters.Add(cmd, "@MinIndexNumber", DbType.Int32).Value = query.MinIndexNumber.Value; + } + + //if (query.MinPlayers.HasValue) + //{ + // whereClauses.Add("Players>=@MinPlayers"); + // cmd.Parameters.Add(cmd, "@MinPlayers", DbType.Int32).Value = query.MinPlayers.Value; + //} + + //if (query.MaxPlayers.HasValue) + //{ + // whereClauses.Add("Players<=@MaxPlayers"); + // cmd.Parameters.Add(cmd, "@MaxPlayers", DbType.Int32).Value = query.MaxPlayers.Value; + //} + if (query.MinEndDate.HasValue) { whereClauses.Add("EndDate>=@MinEndDate"); @@ -1879,16 +1917,21 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.Parameters.Add(cmd, "@MinStartDate", DbType.Date).Value = query.MinStartDate.Value; } + if (query.MaxStartDate.HasValue) + { + whereClauses.Add("StartDate<=@MaxStartDate"); + cmd.Parameters.Add(cmd, "@MaxStartDate", DbType.Date).Value = query.MaxStartDate.Value; + } + if (query.MinPremiereDate.HasValue) { whereClauses.Add("PremiereDate>=@MinPremiereDate"); cmd.Parameters.Add(cmd, "@MinPremiereDate", DbType.Date).Value = query.MinPremiereDate.Value; } - - if (query.MaxStartDate.HasValue) + if (query.MaxPremiereDate.HasValue) { - whereClauses.Add("StartDate<=@MaxStartDate"); - cmd.Parameters.Add(cmd, "@MaxStartDate", DbType.Date).Value = query.MaxStartDate.Value; + whereClauses.Add("PremiereDate<=@MaxPremiereDate"); + cmd.Parameters.Add(cmd, "@MaxPremiereDate", DbType.Date).Value = query.MaxPremiereDate.Value; } if (query.SourceTypes.Length == 1) @@ -1972,16 +2015,44 @@ namespace MediaBrowser.Server.Implementations.Persistence if (query.Genres.Length > 0) { - var genres = new List(); + var clauses = new List(); + var index = 0; + foreach (var item in query.Genres) + { + clauses.Add("Genres like @Genres" + index); + cmd.Parameters.Add(cmd, "@Genres" + index, DbType.String).Value = "%" + item + "%"; + index++; + } + var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); + } + + if (query.Tags.Length > 0) + { + var clauses = new List(); + var index = 0; + foreach (var item in query.Tags) + { + clauses.Add("Tags like @Tags" + index); + cmd.Parameters.Add(cmd, "@Tags" + index, DbType.String).Value = "%" + item + "%"; + index++; + } + var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); + } + + if (query.Studios.Length > 0) + { + var clauses = new List(); var index = 0; - foreach (var genre in query.Genres) + foreach (var item in query.Studios) { - genres.Add("Genres like @Genres" + index); - cmd.Parameters.Add(cmd, "@Genres" + index, DbType.String).Value = "%" + genre + "%"; + clauses.Add("Studios like @Studios" + index); + cmd.Parameters.Add(cmd, "@Studios" + index, DbType.String).Value = "%" + item + "%"; index++; } - var genreCaluse = "(" + string.Join(" OR ", genres.ToArray()) + ")"; - whereClauses.Add(genreCaluse); + var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); } if (query.MaxParentalRating.HasValue) @@ -2020,6 +2091,17 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add("LocationType not in (" + val + ")"); } + if (query.MediaTypes.Length == 1) + { + whereClauses.Add("MediaType=@MediaTypes"); + cmd.Parameters.Add(cmd, "@MediaTypes", DbType.String).Value = query.MediaTypes[0].ToString(); + } + if (query.MediaTypes.Length > 1) + { + var val = string.Join(",", query.MediaTypes.Select(i => "'" + i + "'").ToArray()); + + whereClauses.Add("MediaType in (" + val + ")"); + } var enableItemsByName = query.IncludeItemsByName ?? query.IncludeItemTypes.Length > 0; -- cgit v1.2.3 From fc6a270fb71c1c5db34562759d043531d242775d Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Sun, 20 Mar 2016 13:52:36 -0400 Subject: update sorting --- MediaBrowser.Controller/Entities/Folder.cs | 5 ----- .../Persistence/SqliteItemRepository.cs | 4 ++++ 2 files changed, 4 insertions(+), 5 deletions(-) (limited to 'MediaBrowser.Server.Implementations/Persistence') diff --git a/MediaBrowser.Controller/Entities/Folder.cs b/MediaBrowser.Controller/Entities/Folder.cs index 0cf102d93..3d4e90671 100644 --- a/MediaBrowser.Controller/Entities/Folder.cs +++ b/MediaBrowser.Controller/Entities/Folder.cs @@ -916,11 +916,6 @@ namespace MediaBrowser.Controller.Entities Logger.Debug("Query requires post-filtering due to ItemSortBy.Players"); return true; } - if (query.SortBy.Contains(ItemSortBy.Random, StringComparer.OrdinalIgnoreCase)) - { - Logger.Debug("Query requires post-filtering due to ItemSortBy.Random"); - return true; - } if (query.SortBy.Contains(ItemSortBy.Revenue, StringComparer.OrdinalIgnoreCase)) { Logger.Debug("Query requires post-filtering due to ItemSortBy.Revenue"); diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 12e444017..1eaff1a6a 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -1592,6 +1592,10 @@ namespace MediaBrowser.Server.Implementations.Persistence { return "RuntimeTicks"; } + if (string.Equals(name, ItemSortBy.Random, StringComparison.OrdinalIgnoreCase)) + { + return "RANDOM()"; + } return name; } -- cgit v1.2.3 From 6807b2dd71d8a7ece0c1809449fffa78d4d62d3a Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Sun, 20 Mar 2016 14:39:20 -0400 Subject: update playlist querying --- MediaBrowser.Controller/Entities/Folder.cs | 7 +++++-- .../Persistence/SqliteItemRepository.cs | 5 +++++ 2 files changed, 10 insertions(+), 2 deletions(-) (limited to 'MediaBrowser.Server.Implementations/Persistence') diff --git a/MediaBrowser.Controller/Entities/Folder.cs b/MediaBrowser.Controller/Entities/Folder.cs index 3d4e90671..efb784153 100644 --- a/MediaBrowser.Controller/Entities/Folder.cs +++ b/MediaBrowser.Controller/Entities/Folder.cs @@ -830,8 +830,11 @@ namespace MediaBrowser.Controller.Entities { if (LinkedChildren.Count > 0) { - Logger.Debug("Query requires post-filtering due to LinkedChildren"); - return true; + if (!(this is ICollectionFolder)) + { + Logger.Debug("Query requires post-filtering due to LinkedChildren"); + return true; + } } if (query.SortBy != null && query.SortBy.Length > 0) diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 1eaff1a6a..491f2c116 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -19,6 +19,7 @@ using System.Runtime.Serialization; using System.Threading; using System.Threading.Tasks; using MediaBrowser.Controller.Channels; +using MediaBrowser.Controller.Playlists; using MediaBrowser.Model.LiveTv; namespace MediaBrowser.Server.Implementations.Persistence @@ -729,10 +730,12 @@ namespace MediaBrowser.Server.Implementations.Persistence var topParent = item.GetTopParent(); if (topParent != null) { + //Logger.Debug("Item {0} has top parent {1}", item.Id, topParent.Id); _saveItemCommand.GetParameter(index++).Value = topParent.Id.ToString("N"); } else { + //Logger.Debug("Item {0} has null top parent", item.Id); _saveItemCommand.GetParameter(index++).Value = null; } @@ -2199,6 +2202,8 @@ namespace MediaBrowser.Server.Implementations.Persistence typeof(MusicGenre), typeof(MusicVideo), typeof(Movie), + typeof(Playlist), + typeof(AudioPodcast), typeof(Trailer), typeof(BoxSet), typeof(Episode), -- cgit v1.2.3