diff options
Diffstat (limited to 'Emby.Server.Implementations/Data/SqliteItemRepository.cs')
| -rw-r--r-- | Emby.Server.Implementations/Data/SqliteItemRepository.cs | 603 |
1 files changed, 278 insertions, 325 deletions
diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index beae7e243..d5c0b7107 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -248,42 +248,6 @@ namespace Emby.Server.Implementations.Data BaseItemKind.AudioBook }; - private static readonly Type[] _knownTypes = - { - typeof(LiveTvProgram), - typeof(LiveTvChannel), - typeof(Series), - typeof(Audio), - typeof(MusicAlbum), - typeof(MusicArtist), - typeof(MusicGenre), - typeof(MusicVideo), - typeof(Movie), - typeof(Playlist), - typeof(AudioBook), - typeof(Trailer), - typeof(BoxSet), - typeof(Episode), - typeof(Season), - typeof(Series), - typeof(Book), - typeof(CollectionFolder), - typeof(Folder), - typeof(Genre), - typeof(Person), - typeof(Photo), - typeof(PhotoAlbum), - typeof(Studio), - typeof(UserRootFolder), - typeof(UserView), - typeof(Video), - typeof(Year), - typeof(Channel), - typeof(AggregateFolder) - }; - - private readonly Dictionary<string, string> _types = GetTypeMapDictionary(); - private static readonly Dictionary<BaseItemKind, string> _baseItemKindNames = new() { { BaseItemKind.AggregateFolder, typeof(AggregateFolder).FullName }, @@ -498,109 +462,110 @@ namespace Emby.Server.Implementations.Data connection.RunQueries(queries); connection.RunInTransaction( - db => - { - var existingColumnNames = GetColumnNames(db, "AncestorIds"); - AddColumn(db, "AncestorIds", "AncestorIdText", "Text", existingColumnNames); - - existingColumnNames = GetColumnNames(db, "TypedBaseItems"); - - AddColumn(db, "TypedBaseItems", "Path", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "StartDate", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "EndDate", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ChannelId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsMovie", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "CommunityRating", "Float", existingColumnNames); - AddColumn(db, "TypedBaseItems", "CustomRating", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IndexNumber", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsLocked", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Name", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "OfficialRating", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "MediaType", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Overview", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ParentIndexNumber", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "PremiereDate", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ProductionYear", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ParentId", "GUID", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Genres", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SortName", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ForcedSortName", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "RunTimeTicks", "BIGINT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "DateCreated", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "DateModified", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsSeries", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "EpisodeTitle", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsRepeat", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "PreferredMetadataLanguage", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "PreferredMetadataCountryCode", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "DateLastRefreshed", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "DateLastSaved", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsInMixedFolder", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "LockedFields", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Studios", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Audio", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ExternalServiceId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Tags", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsFolder", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "InheritedParentalRatingValue", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "UnratedType", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "TopParentId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "TrailerTypes", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "CriticRating", "Float", existingColumnNames); - AddColumn(db, "TypedBaseItems", "CleanName", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "PresentationUniqueKey", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "OriginalTitle", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "PrimaryVersionId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "DateLastMediaAdded", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Album", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsVirtualItem", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SeriesName", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "UserDataKey", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SeasonName", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SeasonId", "GUID", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SeriesId", "GUID", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ExternalSeriesId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Tagline", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ProviderIds", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Images", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ProductionLocations", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ExtraIds", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "TotalBitrate", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ExtraType", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Artists", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "AlbumArtists", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ExternalId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SeriesPresentationUniqueKey", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ShowId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "OwnerId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Width", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Height", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Size", "BIGINT", existingColumnNames); - - existingColumnNames = GetColumnNames(db, "ItemValues"); - AddColumn(db, "ItemValues", "CleanValue", "Text", existingColumnNames); - - existingColumnNames = GetColumnNames(db, ChaptersTableName); - AddColumn(db, ChaptersTableName, "ImageDateModified", "DATETIME", existingColumnNames); - - existingColumnNames = GetColumnNames(db, "MediaStreams"); - AddColumn(db, "MediaStreams", "IsAvc", "BIT", existingColumnNames); - AddColumn(db, "MediaStreams", "TimeBase", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "CodecTimeBase", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "Title", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "NalLengthSize", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "Comment", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "CodecTag", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "PixelFormat", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "BitDepth", "INT", existingColumnNames); - AddColumn(db, "MediaStreams", "RefFrames", "INT", existingColumnNames); - AddColumn(db, "MediaStreams", "KeyFrames", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "IsAnamorphic", "BIT", existingColumnNames); - - AddColumn(db, "MediaStreams", "ColorPrimaries", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "ColorSpace", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "ColorTransfer", "TEXT", existingColumnNames); - }, TransactionMode); + db => + { + var existingColumnNames = GetColumnNames(db, "AncestorIds"); + AddColumn(db, "AncestorIds", "AncestorIdText", "Text", existingColumnNames); + + existingColumnNames = GetColumnNames(db, "TypedBaseItems"); + + AddColumn(db, "TypedBaseItems", "Path", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "StartDate", "DATETIME", existingColumnNames); + AddColumn(db, "TypedBaseItems", "EndDate", "DATETIME", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ChannelId", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "IsMovie", "BIT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "CommunityRating", "Float", existingColumnNames); + AddColumn(db, "TypedBaseItems", "CustomRating", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "IndexNumber", "INT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "IsLocked", "BIT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Name", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "OfficialRating", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "MediaType", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Overview", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ParentIndexNumber", "INT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "PremiereDate", "DATETIME", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ProductionYear", "INT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ParentId", "GUID", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Genres", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "SortName", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ForcedSortName", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "RunTimeTicks", "BIGINT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "DateCreated", "DATETIME", existingColumnNames); + AddColumn(db, "TypedBaseItems", "DateModified", "DATETIME", existingColumnNames); + AddColumn(db, "TypedBaseItems", "IsSeries", "BIT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "EpisodeTitle", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "IsRepeat", "BIT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "PreferredMetadataLanguage", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "PreferredMetadataCountryCode", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "DateLastRefreshed", "DATETIME", existingColumnNames); + AddColumn(db, "TypedBaseItems", "DateLastSaved", "DATETIME", existingColumnNames); + AddColumn(db, "TypedBaseItems", "IsInMixedFolder", "BIT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "LockedFields", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Studios", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Audio", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ExternalServiceId", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Tags", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "IsFolder", "BIT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "InheritedParentalRatingValue", "INT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "UnratedType", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "TopParentId", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "TrailerTypes", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "CriticRating", "Float", existingColumnNames); + AddColumn(db, "TypedBaseItems", "CleanName", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "PresentationUniqueKey", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "OriginalTitle", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "PrimaryVersionId", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "DateLastMediaAdded", "DATETIME", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Album", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "IsVirtualItem", "BIT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "SeriesName", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "UserDataKey", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "SeasonName", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "SeasonId", "GUID", existingColumnNames); + AddColumn(db, "TypedBaseItems", "SeriesId", "GUID", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ExternalSeriesId", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Tagline", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ProviderIds", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Images", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ProductionLocations", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ExtraIds", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "TotalBitrate", "INT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ExtraType", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Artists", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "AlbumArtists", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ExternalId", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "SeriesPresentationUniqueKey", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ShowId", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "OwnerId", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Width", "INT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Height", "INT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Size", "BIGINT", existingColumnNames); + + existingColumnNames = GetColumnNames(db, "ItemValues"); + AddColumn(db, "ItemValues", "CleanValue", "Text", existingColumnNames); + + existingColumnNames = GetColumnNames(db, ChaptersTableName); + AddColumn(db, ChaptersTableName, "ImageDateModified", "DATETIME", existingColumnNames); + + existingColumnNames = GetColumnNames(db, "MediaStreams"); + AddColumn(db, "MediaStreams", "IsAvc", "BIT", existingColumnNames); + AddColumn(db, "MediaStreams", "TimeBase", "TEXT", existingColumnNames); + AddColumn(db, "MediaStreams", "CodecTimeBase", "TEXT", existingColumnNames); + AddColumn(db, "MediaStreams", "Title", "TEXT", existingColumnNames); + AddColumn(db, "MediaStreams", "NalLengthSize", "TEXT", existingColumnNames); + AddColumn(db, "MediaStreams", "Comment", "TEXT", existingColumnNames); + AddColumn(db, "MediaStreams", "CodecTag", "TEXT", existingColumnNames); + AddColumn(db, "MediaStreams", "PixelFormat", "TEXT", existingColumnNames); + AddColumn(db, "MediaStreams", "BitDepth", "INT", existingColumnNames); + AddColumn(db, "MediaStreams", "RefFrames", "INT", existingColumnNames); + AddColumn(db, "MediaStreams", "KeyFrames", "TEXT", existingColumnNames); + AddColumn(db, "MediaStreams", "IsAnamorphic", "BIT", existingColumnNames); + + AddColumn(db, "MediaStreams", "ColorPrimaries", "TEXT", existingColumnNames); + AddColumn(db, "MediaStreams", "ColorSpace", "TEXT", existingColumnNames); + AddColumn(db, "MediaStreams", "ColorTransfer", "TEXT", existingColumnNames); + }, + TransactionMode); connection.RunQueries(postQueries); } @@ -636,16 +601,17 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection()) { connection.RunInTransaction( - db => - { - using (var saveImagesStatement = PrepareStatement(db, "Update TypedBaseItems set Images=@Images where guid=@Id")) + db => { - saveImagesStatement.TryBind("@Id", item.Id.ToByteArray()); - saveImagesStatement.TryBind("@Images", SerializeImages(item.ImageInfos)); + using (var saveImagesStatement = PrepareStatement(db, "Update TypedBaseItems set Images=@Images where guid=@Id")) + { + saveImagesStatement.TryBind("@Id", item.Id.ToByteArray()); + saveImagesStatement.TryBind("@Images", SerializeImages(item.ImageInfos)); - saveImagesStatement.MoveNext(); - } - }, TransactionMode); + saveImagesStatement.MoveNext(); + } + }, + TransactionMode); } } @@ -686,14 +652,15 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection()) { connection.RunInTransaction( - db => - { - SaveItemsInTranscation(db, tuples); - }, TransactionMode); + db => + { + SaveItemsInTransaction(db, tuples); + }, + TransactionMode); } } - private void SaveItemsInTranscation(IDatabaseConnection db, IEnumerable<(BaseItem, List<Guid>, BaseItem, string, List<string>)> tuples) + private void SaveItemsInTransaction(IDatabaseConnection db, IEnumerable<(BaseItem Item, List<Guid> AncestorIds, BaseItem TopParent, string UserDataKey, List<string> InheritedTags)> tuples) { var statements = PrepareAll(db, new string[] { @@ -712,17 +679,17 @@ namespace Emby.Server.Implementations.Data saveItemStatement.Reset(); } - var item = tuple.Item1; - var topParent = tuple.Item3; - var userDataKey = tuple.Item4; + var item = tuple.Item; + var topParent = tuple.TopParent; + var userDataKey = tuple.UserDataKey; SaveItem(item, topParent, userDataKey, saveItemStatement); - var inheritedTags = tuple.Item5; + var inheritedTags = tuple.InheritedTags; if (item.SupportsAncestors) { - UpdateAncestors(item.Id, tuple.Item2, db, deleteAncestorsStatement); + UpdateAncestors(item.Id, tuple.AncestorIds, db, deleteAncestorsStatement); } UpdateItemValues(item.Id, GetItemValuesToSave(item, inheritedTags), db); @@ -2134,13 +2101,14 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection()) { connection.RunInTransaction( - db => - { - // First delete chapters - db.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", idBlob); + db => + { + // First delete chapters + db.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", idBlob); - InsertChapters(idBlob, chapters, db); - }, TransactionMode); + InsertChapters(idBlob, chapters, db); + }, + TransactionMode); } } @@ -2199,7 +2167,7 @@ namespace Emby.Server.Implementations.Data return false; } - var sortingFields = new HashSet<string>(query.OrderBy.Select(i => i.Item1), StringComparer.OrdinalIgnoreCase); + var sortingFields = new HashSet<string>(query.OrderBy.Select(i => i.OrderBy), StringComparer.OrdinalIgnoreCase); return sortingFields.Contains(ItemSortBy.IsFavoriteOrLiked) || sortingFields.Contains(ItemSortBy.IsPlayed) @@ -2944,69 +2912,70 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection(true)) { connection.RunInTransaction( - db => - { - var itemQueryStatement = PrepareStatement(db, itemQuery); - var totalRecordCountQueryStatement = PrepareStatement(db, totalRecordCountQuery); - - if (!isReturningZeroItems) + db => { - using (var statement = itemQueryStatement) + var itemQueryStatement = PrepareStatement(db, itemQuery); + var totalRecordCountQueryStatement = PrepareStatement(db, totalRecordCountQuery); + + if (!isReturningZeroItems) { - if (EnableJoinUserData(query)) + using (var statement = itemQueryStatement) { - statement.TryBind("@UserId", query.User.InternalId); - } + if (EnableJoinUserData(query)) + { + statement.TryBind("@UserId", query.User.InternalId); + } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - var hasEpisodeAttributes = HasEpisodeAttributes(query); - var hasServiceName = HasServiceName(query); - var hasProgramAttributes = HasProgramAttributes(query); - var hasStartDate = HasStartDate(query); - var hasTrailerTypes = HasTrailerTypes(query); - var hasArtistFields = HasArtistFields(query); - var hasSeriesFields = HasSeriesFields(query); + var hasEpisodeAttributes = HasEpisodeAttributes(query); + var hasServiceName = HasServiceName(query); + var hasProgramAttributes = HasProgramAttributes(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); - if (item != null) + foreach (var row in statement.ExecuteQuery()) { - list.Add(item); + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + if (item != null) + { + list.Add(item); + } } } - } - LogQueryTime("GetItems.ItemQuery", itemQuery, now); - } + LogQueryTime("GetItems.ItemQuery", itemQuery, now); + } - now = DateTime.UtcNow; - if (query.EnableTotalRecordCount) - { - using (var statement = totalRecordCountQueryStatement) + now = DateTime.UtcNow; + if (query.EnableTotalRecordCount) { - if (EnableJoinUserData(query)) + using (var statement = totalRecordCountQueryStatement) { - statement.TryBind("@UserId", query.User.InternalId); - } + if (EnableJoinUserData(query)) + { + statement.TryBind("@UserId", query.User.InternalId); + } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); - } + result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); + } - LogQueryTime("GetItems.TotalRecordCount", totalRecordCountQuery, now); - } - }, ReadTransactionMode); + LogQueryTime("GetItems.TotalRecordCount", totalRecordCountQuery, now); + } + }, + ReadTransactionMode); } result.Items = list; @@ -3046,88 +3015,86 @@ namespace Emby.Server.Implementations.Data return " ORDER BY " + string.Join(',', orderBy.Select(i => { - var columnMap = MapOrderByField(i.Item1, query); - - var sortOrder = i.Item2 == SortOrder.Ascending ? "ASC" : "DESC"; - - return columnMap.Item1 + " " + sortOrder; + var columnMap = MapOrderByField(i.OrderBy, query); + var sortOrder = columnMap.SortOrder == SortOrder.Ascending ? "ASC" : "DESC"; + return columnMap.SortBy + " " + sortOrder; })); } - private (string, bool) MapOrderByField(string name, InternalItemsQuery query) + private (string SortBy, SortOrder SortOrder) MapOrderByField(string name, InternalItemsQuery query) { if (string.Equals(name, ItemSortBy.AirTime, StringComparison.OrdinalIgnoreCase)) { // TODO - return ("SortName", false); + return ("SortName", SortOrder.Descending); } else if (string.Equals(name, ItemSortBy.Runtime, StringComparison.OrdinalIgnoreCase)) { - return ("RuntimeTicks", false); + return ("RuntimeTicks", SortOrder.Descending); } else if (string.Equals(name, ItemSortBy.Random, StringComparison.OrdinalIgnoreCase)) { - return ("RANDOM()", false); + return ("RANDOM()", SortOrder.Descending); } else if (string.Equals(name, ItemSortBy.DatePlayed, StringComparison.OrdinalIgnoreCase)) { if (query.GroupBySeriesPresentationUniqueKey) { - return ("MAX(LastPlayedDate)", false); + return ("MAX(LastPlayedDate)", SortOrder.Descending); } - return ("LastPlayedDate", false); + return ("LastPlayedDate", SortOrder.Descending); } else if (string.Equals(name, ItemSortBy.PlayCount, StringComparison.OrdinalIgnoreCase)) { - return ("PlayCount", false); + return ("PlayCount", SortOrder.Descending); } else if (string.Equals(name, ItemSortBy.IsFavoriteOrLiked, StringComparison.OrdinalIgnoreCase)) { - return ("(Select Case When IsFavorite is null Then 0 Else IsFavorite End )", true); + return ("(Select Case When IsFavorite is null Then 0 Else IsFavorite End )", SortOrder.Ascending); } else if (string.Equals(name, ItemSortBy.IsFolder, StringComparison.OrdinalIgnoreCase)) { - return ("IsFolder", true); + return ("IsFolder", SortOrder.Ascending); } else if (string.Equals(name, ItemSortBy.IsPlayed, StringComparison.OrdinalIgnoreCase)) { - return ("played", true); + return ("played", SortOrder.Ascending); } else if (string.Equals(name, ItemSortBy.IsUnplayed, StringComparison.OrdinalIgnoreCase)) { - return ("played", false); + return ("played", SortOrder.Descending); } else if (string.Equals(name, ItemSortBy.DateLastContentAdded, StringComparison.OrdinalIgnoreCase)) { - return ("DateLastMediaAdded", false); + return ("DateLastMediaAdded", SortOrder.Descending); } else if (string.Equals(name, ItemSortBy.Artist, StringComparison.OrdinalIgnoreCase)) { - return ("(select CleanValue from itemvalues where ItemId=Guid and Type=0 LIMIT 1)", false); + return ("(select CleanValue from itemvalues where ItemId=Guid and Type=0 LIMIT 1)", SortOrder.Descending); } else if (string.Equals(name, ItemSortBy.AlbumArtist, StringComparison.OrdinalIgnoreCase)) { - return ("(select CleanValue from itemvalues where ItemId=Guid and Type=1 LIMIT 1)", false); + return ("(select CleanValue from itemvalues where ItemId=Guid and Type=1 LIMIT 1)", SortOrder.Descending); } else if (string.Equals(name, ItemSortBy.OfficialRating, StringComparison.OrdinalIgnoreCase)) { - return ("InheritedParentalRatingValue", false); + return ("InheritedParentalRatingValue", SortOrder.Descending); } else if (string.Equals(name, ItemSortBy.Studio, StringComparison.OrdinalIgnoreCase)) { - return ("(select CleanValue from itemvalues where ItemId=Guid and Type=3 LIMIT 1)", false); + return ("(select CleanValue from itemvalues where ItemId=Guid and Type=3 LIMIT 1)", SortOrder.Descending); } else if (string.Equals(name, ItemSortBy.SeriesDatePlayed, StringComparison.OrdinalIgnoreCase)) { - return ("(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText(query) + " where Played=1 and B.SeriesPresentationUniqueKey=A.PresentationUniqueKey)", false); + return ("(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText(query) + " where Played=1 and B.SeriesPresentationUniqueKey=A.PresentationUniqueKey)", SortOrder.Descending); } else if (string.Equals(name, ItemSortBy.SeriesSortName, StringComparison.OrdinalIgnoreCase)) { - return ("SeriesName", false); + return ("SeriesName", SortOrder.Descending); } - return (name, false); + return (name, SortOrder.Descending); } public List<Guid> GetItemIdsList(InternalItemsQuery query) @@ -3363,51 +3330,52 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection(true)) { connection.RunInTransaction( - db => - { - var statements = PrepareAll(db, statementTexts); - - if (!isReturningZeroItems) + db => { - using (var statement = statements[0]) + var statements = PrepareAll(db, statementTexts); + + if (!isReturningZeroItems) { - if (EnableJoinUserData(query)) + using (var statement = statements[0]) { - statement.TryBind("@UserId", query.User.InternalId); - } + if (EnableJoinUserData(query)) + { + statement.TryBind("@UserId", query.User.InternalId); + } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(row[0].ReadGuidFromBlob()); + foreach (var row in statement.ExecuteQuery()) + { + list.Add(row[0].ReadGuidFromBlob()); + } } } - } - if (query.EnableTotalRecordCount) - { - using (var statement = statements[statements.Length - 1]) + if (query.EnableTotalRecordCount) { - if (EnableJoinUserData(query)) + using (var statement = statements[statements.Length - 1]) { - statement.TryBind("@UserId", query.User.InternalId); - } + if (EnableJoinUserData(query)) + { + statement.TryBind("@UserId", query.User.InternalId); + } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); + result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); + } } - } - }, ReadTransactionMode); + }, + ReadTransactionMode); } LogQueryTime("GetItemIds", commandText, now); @@ -3434,11 +3402,6 @@ namespace Emby.Server.Implementations.Data return true; } - private bool IsValidType(string value) - { - return IsAlphaNumeric(value); - } - private bool IsValidMediaType(string value) { return IsAlphaNumeric(value); @@ -4705,7 +4668,7 @@ namespace Emby.Server.Implementations.Data if (statement == null) { int index = 0; - string excludedTags = string.Join(',', query.ExcludeInheritedTags.Select(t => paramName + index++)); + string excludedTags = string.Join(',', query.ExcludeInheritedTags.Select(_ => paramName + index++)); whereClauses.Add("((select CleanValue from itemvalues where ItemId=Guid and Type=6 and cleanvalue in (" + excludedTags + ")) is null)"); } else @@ -4954,26 +4917,12 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type using (var connection = GetConnection()) { connection.RunInTransaction( - db => - { - connection.ExecuteAll(sql); - }, TransactionMode); - } - } - - private static Dictionary<string, string> GetTypeMapDictionary() - { - var dict = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase); - - foreach (var t in _knownTypes) - { - dict[t.Name] = t.FullName; + db => + { + connection.ExecuteAll(sql); + }, + TransactionMode); } - - dict["Program"] = typeof(LiveTvProgram).FullName; - dict["TvChannel"] = typeof(LiveTvChannel).FullName; - - return dict; } public void DeleteItem(Guid id) @@ -4988,28 +4937,29 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type using (var connection = GetConnection()) { connection.RunInTransaction( - db => - { - var idBlob = id.ToByteArray(); + db => + { + var idBlob = id.ToByteArray(); - // Delete people - ExecuteWithSingleParam(db, "delete from People where ItemId=@Id", idBlob); + // Delete people + ExecuteWithSingleParam(db, "delete from People where ItemId=@Id", idBlob); - // Delete chapters - ExecuteWithSingleParam(db, "delete from " + ChaptersTableName + " where ItemId=@Id", idBlob); + // Delete chapters + ExecuteWithSingleParam(db, "delete from " + ChaptersTableName + " where ItemId=@Id", idBlob); - // Delete media streams - ExecuteWithSingleParam(db, "delete from mediastreams where ItemId=@Id", idBlob); + // Delete media streams + ExecuteWithSingleParam(db, "delete from mediastreams where ItemId=@Id", idBlob); - // Delete ancestors - ExecuteWithSingleParam(db, "delete from AncestorIds where ItemId=@Id", idBlob); + // Delete ancestors + ExecuteWithSingleParam(db, "delete from AncestorIds where ItemId=@Id", idBlob); - // Delete item values - ExecuteWithSingleParam(db, "delete from ItemValues where ItemId=@Id", idBlob); + // Delete item values + ExecuteWithSingleParam(db, "delete from ItemValues where ItemId=@Id", idBlob); - // Delete the item - ExecuteWithSingleParam(db, "delete from TypedBaseItems where guid=@Id", idBlob); - }, TransactionMode); + // Delete the item + ExecuteWithSingleParam(db, "delete from TypedBaseItems where guid=@Id", idBlob); + }, + TransactionMode); } } @@ -5244,32 +5194,32 @@ AND Type = @InternalPersonType)"); } } - public QueryResult<(BaseItem, ItemCounts)> GetAllArtists(InternalItemsQuery query) + public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetAllArtists(InternalItemsQuery query) { return GetItemValues(query, new[] { 0, 1 }, typeof(MusicArtist).FullName); } - public QueryResult<(BaseItem, ItemCounts)> GetArtists(InternalItemsQuery query) + public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetArtists(InternalItemsQuery query) { return GetItemValues(query, new[] { 0 }, typeof(MusicArtist).FullName); } - public QueryResult<(BaseItem, ItemCounts)> GetAlbumArtists(InternalItemsQuery query) + public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetAlbumArtists(InternalItemsQuery query) { return GetItemValues(query, new[] { 1 }, typeof(MusicArtist).FullName); } - public QueryResult<(BaseItem, ItemCounts)> GetStudios(InternalItemsQuery query) + public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetStudios(InternalItemsQuery query) { return GetItemValues(query, new[] { 3 }, typeof(Studio).FullName); } - public QueryResult<(BaseItem, ItemCounts)> GetGenres(InternalItemsQuery query) + public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetGenres(InternalItemsQuery query) { return GetItemValues(query, new[] { 2 }, typeof(Genre).FullName); } - public QueryResult<(BaseItem, ItemCounts)> GetMusicGenres(InternalItemsQuery query) + public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetMusicGenres(InternalItemsQuery query) { return GetItemValues(query, new[] { 2 }, typeof(MusicGenre).FullName); } @@ -5365,7 +5315,7 @@ AND Type = @InternalPersonType)"); return list; } - private QueryResult<(BaseItem, ItemCounts)> GetItemValues(InternalItemsQuery query, int[] itemValueTypes, string returnType) + private QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetItemValues(InternalItemsQuery query, int[] itemValueTypes, string returnType) { if (query == null) { @@ -5690,7 +5640,7 @@ AND Type = @InternalPersonType)"); return counts; } - private List<(int, string)> GetItemValuesToSave(BaseItem item, List<string> inheritedTags) + private List<(int MagicNumber, string Value)> GetItemValuesToSave(BaseItem item, List<string> inheritedTags) { var list = new List<(int, string)>(); @@ -5715,7 +5665,7 @@ AND Type = @InternalPersonType)"); return list; } - private void UpdateItemValues(Guid itemId, List<(int, string)> values, IDatabaseConnection db) + private void UpdateItemValues(Guid itemId, List<(int MagicNumber, string Value)> values, IDatabaseConnection db) { if (itemId.Equals(Guid.Empty)) { @@ -5737,7 +5687,7 @@ AND Type = @InternalPersonType)"); InsertItemValues(guidBlob, values, db); } - private void InsertItemValues(byte[] idBlob, List<(int, string)> values, IDatabaseConnection db) + private void InsertItemValues(byte[] idBlob, List<(int MagicNumber, string Value)> values, IDatabaseConnection db) { const int Limit = 100; var startIndex = 0; @@ -5769,7 +5719,7 @@ AND Type = @InternalPersonType)"); var currentValueInfo = values[i]; - var itemValue = currentValueInfo.Item2; + var itemValue = currentValueInfo.Value; // Don't save if invalid if (string.IsNullOrWhiteSpace(itemValue)) @@ -5777,7 +5727,7 @@ AND Type = @InternalPersonType)"); continue; } - statement.TryBind("@Type" + index, currentValueInfo.Item1); + statement.TryBind("@Type" + index, currentValueInfo.MagicNumber); statement.TryBind("@Value" + index, itemValue); statement.TryBind("@CleanValue" + index, GetCleanValue(itemValue)); } @@ -5808,15 +5758,16 @@ AND Type = @InternalPersonType)"); using (var connection = GetConnection()) { connection.RunInTransaction( - db => - { - var itemIdBlob = itemId.ToByteArray(); + db => + { + var itemIdBlob = itemId.ToByteArray(); - // First delete chapters - db.Execute("delete from People where ItemId=@ItemId", itemIdBlob); + // First delete chapters + db.Execute("delete from People where ItemId=@ItemId", itemIdBlob); - InsertPeople(itemIdBlob, people, db); - }, TransactionMode); + InsertPeople(itemIdBlob, people, db); + }, + TransactionMode); } } @@ -5974,7 +5925,8 @@ AND Type = @InternalPersonType)"); db.Execute("delete from mediastreams where ItemId=@ItemId", itemIdBlob); InsertMediaStreams(itemIdBlob, streams, db); - }, TransactionMode); + }, + TransactionMode); } } @@ -6308,7 +6260,8 @@ AND Type = @InternalPersonType)"); db.Execute("delete from mediaattachments where ItemId=@ItemId", itemIdBlob); InsertMediaAttachments(itemIdBlob, attachments, db, cancellationToken); - }, TransactionMode); + }, + TransactionMode); } } |