diff options
Diffstat (limited to 'Emby.Server.Implementations/Data/SqliteItemRepository.cs')
| -rw-r--r-- | Emby.Server.Implementations/Data/SqliteItemRepository.cs | 3130 |
1 files changed, 1421 insertions, 1709 deletions
diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 9b147b5d7..77cf4089b 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -3,18 +3,19 @@ #pragma warning disable CS1591 using System; -using System.Buffers.Text; using System.Collections.Generic; +using System.Diagnostics; using System.Globalization; using System.IO; using System.Linq; +using System.Runtime.CompilerServices; using System.Text; using System.Text.Json; using System.Threading; using Emby.Server.Implementations.Playlists; using Jellyfin.Data.Enums; -using MediaBrowser.Common.Extensions; -using MediaBrowser.Common.Json; +using Jellyfin.Extensions; +using Jellyfin.Extensions.Json; using MediaBrowser.Controller; using MediaBrowser.Controller.Channels; using MediaBrowser.Controller.Configuration; @@ -24,7 +25,6 @@ using MediaBrowser.Controller.Entities.Audio; using MediaBrowser.Controller.Entities.Movies; using MediaBrowser.Controller.Entities.TV; using MediaBrowser.Controller.Extensions; -using MediaBrowser.Controller.Library; using MediaBrowser.Controller.LiveTv; using MediaBrowser.Controller.Persistence; using MediaBrowser.Controller.Playlists; @@ -33,8 +33,9 @@ using MediaBrowser.Model.Entities; using MediaBrowser.Model.Globalization; using MediaBrowser.Model.LiveTv; using MediaBrowser.Model.Querying; +using Microsoft.Data.Sqlite; +using Microsoft.Extensions.Configuration; using Microsoft.Extensions.Logging; -using SQLitePCL.pretty; namespace Emby.Server.Implementations.Data { @@ -46,6 +47,11 @@ namespace Emby.Server.Implementations.Data private const string FromText = " from TypedBaseItems A"; private const string ChaptersTableName = "Chapters2"; + private const string SaveItemCommandText = + @"replace into TypedBaseItems + (guid,type,data,Path,StartDate,EndDate,ChannelId,IsMovie,IsSeries,EpisodeTitle,IsRepeat,CommunityRating,CustomRating,IndexNumber,IsLocked,Name,OfficialRating,MediaType,Overview,ParentIndexNumber,PremiereDate,ProductionYear,ParentId,Genres,InheritedParentalRatingValue,SortName,ForcedSortName,RunTimeTicks,Size,DateCreated,DateModified,PreferredMetadataLanguage,PreferredMetadataCountryCode,Width,Height,DateLastRefreshed,DateLastSaved,IsInMixedFolder,LockedFields,Studios,Audio,ExternalServiceId,Tags,IsFolder,UnratedType,TopParentId,TrailerTypes,CriticRating,CleanName,PresentationUniqueKey,OriginalTitle,PrimaryVersionId,DateLastMediaAdded,Album,LUFS,IsVirtualItem,SeriesName,UserDataKey,SeasonName,SeasonId,SeriesId,ExternalSeriesId,Tagline,ProviderIds,Images,ProductionLocations,ExtraIds,TotalBitrate,ExtraType,Artists,AlbumArtists,ExternalId,SeriesPresentationUniqueKey,ShowId,OwnerId) + values (@guid,@type,@data,@Path,@StartDate,@EndDate,@ChannelId,@IsMovie,@IsSeries,@EpisodeTitle,@IsRepeat,@CommunityRating,@CustomRating,@IndexNumber,@IsLocked,@Name,@OfficialRating,@MediaType,@Overview,@ParentIndexNumber,@PremiereDate,@ProductionYear,@ParentId,@Genres,@InheritedParentalRatingValue,@SortName,@ForcedSortName,@RunTimeTicks,@Size,@DateCreated,@DateModified,@PreferredMetadataLanguage,@PreferredMetadataCountryCode,@Width,@Height,@DateLastRefreshed,@DateLastSaved,@IsInMixedFolder,@LockedFields,@Studios,@Audio,@ExternalServiceId,@Tags,@IsFolder,@UnratedType,@TopParentId,@TrailerTypes,@CriticRating,@CleanName,@PresentationUniqueKey,@OriginalTitle,@PrimaryVersionId,@DateLastMediaAdded,@Album,@LUFS,@IsVirtualItem,@SeriesName,@UserDataKey,@SeasonName,@SeasonId,@SeriesId,@ExternalSeriesId,@Tagline,@ProviderIds,@Images,@ProductionLocations,@ExtraIds,@TotalBitrate,@ExtraType,@Artists,@AlbumArtists,@ExternalId,@SeriesPresentationUniqueKey,@ShowId,@OwnerId)"; + private readonly IServerConfigurationManager _config; private readonly IServerApplicationHost _appHost; private readonly ILocalizationManager _localization; @@ -55,286 +61,9 @@ namespace Emby.Server.Implementations.Data private readonly TypeMapper _typeMapper; private readonly JsonSerializerOptions _jsonOptions; - static SqliteItemRepository() - { - var queryPrefixText = new StringBuilder(); - queryPrefixText.Append("insert into mediaattachments ("); - foreach (var column in _mediaAttachmentSaveColumns) - { - queryPrefixText.Append(column) - .Append(','); - } - - queryPrefixText.Length -= 1; - queryPrefixText.Append(") values "); - _mediaAttachmentInsertPrefix = queryPrefixText.ToString(); - } - - /// <summary> - /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. - /// </summary> - public SqliteItemRepository( - IServerConfigurationManager config, - IServerApplicationHost appHost, - ILogger<SqliteItemRepository> logger, - ILocalizationManager localization, - IImageProcessor imageProcessor) - : base(logger) - { - if (config == null) - { - throw new ArgumentNullException(nameof(config)); - } - - _config = config; - _appHost = appHost; - _localization = localization; - _imageProcessor = imageProcessor; - - _typeMapper = new TypeMapper(); - _jsonOptions = JsonDefaults.Options; - - DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); - } - - /// <inheritdoc /> - public string Name => "SQLite"; - - /// <inheritdoc /> - protected override int? CacheSize => 20000; - - /// <inheritdoc /> - protected override TempStoreMode TempStore => TempStoreMode.Memory; - - /// <summary> - /// Opens the connection to the database. - /// </summary> - public void Initialize(SqliteUserDataRepository userDataRepo, IUserManager userManager) - { - const string CreateMediaStreamsTableCommand - = "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, TimeBase TEXT NULL, CodecTimeBase TEXT NULL, ColorPrimaries TEXT NULL, ColorSpace TEXT NULL, ColorTransfer TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))"; - const string CreateMediaAttachmentsTableCommand - = "create table if not exists mediaattachments (ItemId GUID, AttachmentIndex INT, Codec TEXT, CodecTag TEXT NULL, Comment TEXT NULL, Filename TEXT NULL, MIMEType TEXT NULL, PRIMARY KEY (ItemId, AttachmentIndex))"; - - string[] queries = - { - "PRAGMA locking_mode=EXCLUSIVE", - - "create table if not exists TypedBaseItems (guid GUID primary key NOT NULL, type TEXT NOT NULL, data BLOB NULL, ParentId GUID NULL, Path TEXT NULL)", - - "create table if not exists AncestorIds (ItemId GUID NOT NULL, AncestorId GUID NOT NULL, AncestorIdText TEXT NOT NULL, PRIMARY KEY (ItemId, AncestorId))", - "create index if not exists idx_AncestorIds1 on AncestorIds(AncestorId)", - "create index if not exists idx_AncestorIds5 on AncestorIds(AncestorIdText,ItemId)", - - "create table if not exists ItemValues (ItemId GUID NOT NULL, Type INT NOT NULL, Value TEXT NOT NULL, CleanValue TEXT NOT NULL)", - - "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)", - - "drop index if exists idxPeopleItemId", - "create index if not exists idxPeopleItemId1 on People(ItemId,ListOrder)", - "create index if not exists idxPeopleName on People(Name)", - - "create table if not exists " + ChaptersTableName + " (ItemId GUID, ChapterIndex INT NOT NULL, StartPositionTicks BIGINT NOT NULL, Name TEXT, ImagePath TEXT, PRIMARY KEY (ItemId, ChapterIndex))", - - CreateMediaStreamsTableCommand, - CreateMediaAttachmentsTableCommand, - - "pragma shrink_memory" - }; + private readonly ItemFields[] _allItemFields = Enum.GetValues<ItemFields>(); - string[] postQueries = - { - // obsolete - "drop index if exists idx_TypedBaseItems", - "drop index if exists idx_mediastreams", - "drop index if exists idx_mediastreams1", - "drop index if exists idx_"+ChaptersTableName, - "drop index if exists idx_UserDataKeys1", - "drop index if exists idx_UserDataKeys2", - "drop index if exists idx_TypeTopParentId3", - "drop index if exists idx_TypeTopParentId2", - "drop index if exists idx_TypeTopParentId4", - "drop index if exists idx_Type", - "drop index if exists idx_TypeTopParentId", - "drop index if exists idx_GuidType", - "drop index if exists idx_TopParentId", - "drop index if exists idx_TypeTopParentId6", - "drop index if exists idx_ItemValues2", - "drop index if exists Idx_ProviderIds", - "drop index if exists idx_ItemValues3", - "drop index if exists idx_ItemValues4", - "drop index if exists idx_ItemValues5", - "drop index if exists idx_UserDataKeys3", - "drop table if exists UserDataKeys", - "drop table if exists ProviderIds", - "drop index if exists Idx_ProviderIds1", - "drop table if exists Images", - "drop index if exists idx_Images", - "drop index if exists idx_TypeSeriesPresentationUniqueKey", - "drop index if exists idx_SeriesPresentationUniqueKey", - "drop index if exists idx_TypeSeriesPresentationUniqueKey2", - "drop index if exists idx_AncestorIds3", - "drop index if exists idx_AncestorIds4", - "drop index if exists idx_AncestorIds2", - - "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)", - "create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)", - - "create index if not exists idx_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)", - "create index if not exists idx_GuidTypeIsFolderIsVirtualItem on TypedBaseItems(Guid,Type,IsFolder,IsVirtualItem)", - "create index if not exists idx_CleanNameType on TypedBaseItems(CleanName,Type)", - - // covering index - "create index if not exists idx_TopParentIdGuid on TypedBaseItems(TopParentId,Guid)", - - // series - "create index if not exists idx_TypeSeriesPresentationUniqueKey1 on TypedBaseItems(Type,SeriesPresentationUniqueKey,PresentationUniqueKey,SortName)", - - // series counts - // seriesdateplayed sort order - "create index if not exists idx_TypeSeriesPresentationUniqueKey3 on TypedBaseItems(SeriesPresentationUniqueKey,Type,IsFolder,IsVirtualItem)", - - // live tv programs - "create index if not exists idx_TypeTopParentIdStartDate on TypedBaseItems(Type,TopParentId,StartDate)", - - // covering index for getitemvalues - "create index if not exists idx_TypeTopParentIdGuid on TypedBaseItems(Type,TopParentId,Guid)", - - // used by movie suggestions - "create index if not exists idx_TypeTopParentIdGroup on TypedBaseItems(Type,TopParentId,PresentationUniqueKey)", - "create index if not exists idx_TypeTopParentId5 on TypedBaseItems(TopParentId,IsVirtualItem)", - - // latest items - "create index if not exists idx_TypeTopParentId9 on TypedBaseItems(TopParentId,Type,IsVirtualItem,PresentationUniqueKey,DateCreated)", - "create index if not exists idx_TypeTopParentId8 on TypedBaseItems(TopParentId,IsFolder,IsVirtualItem,PresentationUniqueKey,DateCreated)", - - // resume - "create index if not exists idx_TypeTopParentId7 on TypedBaseItems(TopParentId,MediaType,IsVirtualItem,PresentationUniqueKey)", - - // items by name - "create index if not exists idx_ItemValues6 on ItemValues(ItemId,Type,CleanValue)", - "create index if not exists idx_ItemValues7 on ItemValues(Type,CleanValue,ItemId)", - - // Used to update inherited tags - "create index if not exists idx_ItemValues8 on ItemValues(Type, ItemId, Value)", - }; - - using (var connection = GetConnection()) - { - 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); - - connection.RunQueries(postQueries); - } - - userDataRepo.Initialize(userManager, WriteLock, WriteConnection); - } - - private static readonly string[] _retriveItemColumns = + private static readonly string[] _retrieveItemColumns = { "type", "data", @@ -381,6 +110,7 @@ namespace Emby.Server.Implementations.Data "PrimaryVersionId", "DateLastMediaAdded", "Album", + "LUFS", "CriticRating", "IsVirtualItem", "SeriesName", @@ -405,7 +135,7 @@ namespace Emby.Server.Implementations.Data "OwnerId" }; - private static readonly string _retriveItemColumnsSelectQuery = $"select {string.Join(',', _retriveItemColumns)} from TypedBaseItems where guid = @guid"; + private static readonly string _retrieveItemColumnsSelectQuery = $"select {string.Join(',', _retrieveItemColumns)} from TypedBaseItems where guid = @guid"; private static readonly string[] _mediaStreamSaveColumns = { @@ -443,7 +173,16 @@ namespace Emby.Server.Implementations.Data "CodecTimeBase", "ColorPrimaries", "ColorSpace", - "ColorTransfer" + "ColorTransfer", + "DvVersionMajor", + "DvVersionMinor", + "DvProfile", + "DvLevel", + "RpuPresentFlag", + "ElPresentFlag", + "BlPresentFlag", + "DvBlSignalCompatibilityId", + "IsHearingImpaired" }; private static readonly string _mediaStreamSaveColumnsInsertQuery = @@ -468,76 +207,395 @@ namespace Emby.Server.Implementations.Data private static readonly string _mediaAttachmentInsertPrefix; - private const string SaveItemCommandText = - @"replace into TypedBaseItems - (guid,type,data,Path,StartDate,EndDate,ChannelId,IsMovie,IsSeries,EpisodeTitle,IsRepeat,CommunityRating,CustomRating,IndexNumber,IsLocked,Name,OfficialRating,MediaType,Overview,ParentIndexNumber,PremiereDate,ProductionYear,ParentId,Genres,InheritedParentalRatingValue,SortName,ForcedSortName,RunTimeTicks,Size,DateCreated,DateModified,PreferredMetadataLanguage,PreferredMetadataCountryCode,Width,Height,DateLastRefreshed,DateLastSaved,IsInMixedFolder,LockedFields,Studios,Audio,ExternalServiceId,Tags,IsFolder,UnratedType,TopParentId,TrailerTypes,CriticRating,CleanName,PresentationUniqueKey,OriginalTitle,PrimaryVersionId,DateLastMediaAdded,Album,IsVirtualItem,SeriesName,UserDataKey,SeasonName,SeasonId,SeriesId,ExternalSeriesId,Tagline,ProviderIds,Images,ProductionLocations,ExtraIds,TotalBitrate,ExtraType,Artists,AlbumArtists,ExternalId,SeriesPresentationUniqueKey,ShowId,OwnerId) - values (@guid,@type,@data,@Path,@StartDate,@EndDate,@ChannelId,@IsMovie,@IsSeries,@EpisodeTitle,@IsRepeat,@CommunityRating,@CustomRating,@IndexNumber,@IsLocked,@Name,@OfficialRating,@MediaType,@Overview,@ParentIndexNumber,@PremiereDate,@ProductionYear,@ParentId,@Genres,@InheritedParentalRatingValue,@SortName,@ForcedSortName,@RunTimeTicks,@Size,@DateCreated,@DateModified,@PreferredMetadataLanguage,@PreferredMetadataCountryCode,@Width,@Height,@DateLastRefreshed,@DateLastSaved,@IsInMixedFolder,@LockedFields,@Studios,@Audio,@ExternalServiceId,@Tags,@IsFolder,@UnratedType,@TopParentId,@TrailerTypes,@CriticRating,@CleanName,@PresentationUniqueKey,@OriginalTitle,@PrimaryVersionId,@DateLastMediaAdded,@Album,@IsVirtualItem,@SeriesName,@UserDataKey,@SeasonName,@SeasonId,@SeriesId,@ExternalSeriesId,@Tagline,@ProviderIds,@Images,@ProductionLocations,@ExtraIds,@TotalBitrate,@ExtraType,@Artists,@AlbumArtists,@ExternalId,@SeriesPresentationUniqueKey,@ShowId,@OwnerId)"; + private static readonly BaseItemKind[] _programTypes = new[] + { + BaseItemKind.Program, + BaseItemKind.TvChannel, + BaseItemKind.LiveTvProgram, + BaseItemKind.LiveTvChannel + }; - /// <summary> - /// Save a standard item in the repo. - /// </summary> - /// <param name="item">The item.</param> - /// <param name="cancellationToken">The cancellation token.</param> - /// <exception cref="ArgumentNullException">item</exception> - public void SaveItem(BaseItem item, CancellationToken cancellationToken) + private static readonly BaseItemKind[] _programExcludeParentTypes = new[] { - if (item == null) + BaseItemKind.Series, + BaseItemKind.Season, + BaseItemKind.MusicAlbum, + BaseItemKind.MusicArtist, + BaseItemKind.PhotoAlbum + }; + + private static readonly BaseItemKind[] _serviceTypes = new[] + { + BaseItemKind.TvChannel, + BaseItemKind.LiveTvChannel + }; + + private static readonly BaseItemKind[] _startDateTypes = new[] + { + BaseItemKind.Program, + BaseItemKind.LiveTvProgram + }; + + private static readonly BaseItemKind[] _seriesTypes = new[] + { + BaseItemKind.Book, + BaseItemKind.AudioBook, + BaseItemKind.Episode, + BaseItemKind.Season + }; + + private static readonly BaseItemKind[] _artistExcludeParentTypes = new[] + { + BaseItemKind.Series, + BaseItemKind.Season, + BaseItemKind.PhotoAlbum + }; + + private static readonly BaseItemKind[] _artistsTypes = new[] + { + BaseItemKind.Audio, + BaseItemKind.MusicAlbum, + BaseItemKind.MusicVideo, + BaseItemKind.AudioBook + }; + + private static readonly Dictionary<BaseItemKind, string> _baseItemKindNames = new() + { + { BaseItemKind.AggregateFolder, typeof(AggregateFolder).FullName }, + { BaseItemKind.Audio, typeof(Audio).FullName }, + { BaseItemKind.AudioBook, typeof(AudioBook).FullName }, + { BaseItemKind.BasePluginFolder, typeof(BasePluginFolder).FullName }, + { BaseItemKind.Book, typeof(Book).FullName }, + { BaseItemKind.BoxSet, typeof(BoxSet).FullName }, + { BaseItemKind.Channel, typeof(Channel).FullName }, + { BaseItemKind.CollectionFolder, typeof(CollectionFolder).FullName }, + { BaseItemKind.Episode, typeof(Episode).FullName }, + { BaseItemKind.Folder, typeof(Folder).FullName }, + { BaseItemKind.Genre, typeof(Genre).FullName }, + { BaseItemKind.Movie, typeof(Movie).FullName }, + { BaseItemKind.LiveTvChannel, typeof(LiveTvChannel).FullName }, + { BaseItemKind.LiveTvProgram, typeof(LiveTvProgram).FullName }, + { BaseItemKind.MusicAlbum, typeof(MusicAlbum).FullName }, + { BaseItemKind.MusicArtist, typeof(MusicArtist).FullName }, + { BaseItemKind.MusicGenre, typeof(MusicGenre).FullName }, + { BaseItemKind.MusicVideo, typeof(MusicVideo).FullName }, + { BaseItemKind.Person, typeof(Person).FullName }, + { BaseItemKind.Photo, typeof(Photo).FullName }, + { BaseItemKind.PhotoAlbum, typeof(PhotoAlbum).FullName }, + { BaseItemKind.Playlist, typeof(Playlist).FullName }, + { BaseItemKind.PlaylistsFolder, typeof(PlaylistsFolder).FullName }, + { BaseItemKind.Season, typeof(Season).FullName }, + { BaseItemKind.Series, typeof(Series).FullName }, + { BaseItemKind.Studio, typeof(Studio).FullName }, + { BaseItemKind.Trailer, typeof(Trailer).FullName }, + { BaseItemKind.TvChannel, typeof(LiveTvChannel).FullName }, + { BaseItemKind.TvProgram, typeof(LiveTvProgram).FullName }, + { BaseItemKind.UserRootFolder, typeof(UserRootFolder).FullName }, + { BaseItemKind.UserView, typeof(UserView).FullName }, + { BaseItemKind.Video, typeof(Video).FullName }, + { BaseItemKind.Year, typeof(Year).FullName } + }; + + static SqliteItemRepository() + { + var queryPrefixText = new StringBuilder(); + queryPrefixText.Append("insert into mediaattachments ("); + foreach (var column in _mediaAttachmentSaveColumns) { - throw new ArgumentNullException(nameof(item)); + queryPrefixText.Append(column) + .Append(','); } - SaveItems(new[] { item }, cancellationToken); + queryPrefixText.Length -= 1; + queryPrefixText.Append(") values "); + _mediaAttachmentInsertPrefix = queryPrefixText.ToString(); } - public void SaveImages(BaseItem item) + /// <summary> + /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. + /// </summary> + /// <param name="config">Instance of the <see cref="IServerConfigurationManager"/> interface.</param> + /// <param name="appHost">Instance of the <see cref="IServerApplicationHost"/> interface.</param> + /// <param name="logger">Instance of the <see cref="ILogger{SqliteItemRepository}"/> interface.</param> + /// <param name="localization">Instance of the <see cref="ILocalizationManager"/> interface.</param> + /// <param name="imageProcessor">Instance of the <see cref="IImageProcessor"/> interface.</param> + /// <param name="configuration">Instance of the <see cref="IConfiguration"/> interface.</param> + /// <exception cref="ArgumentNullException">config is null.</exception> + public SqliteItemRepository( + IServerConfigurationManager config, + IServerApplicationHost appHost, + ILogger<SqliteItemRepository> logger, + ILocalizationManager localization, + IImageProcessor imageProcessor, + IConfiguration configuration) + : base(logger) { - if (item == null) + _config = config; + _appHost = appHost; + _localization = localization; + _imageProcessor = imageProcessor; + + _typeMapper = new TypeMapper(); + _jsonOptions = JsonDefaults.Options; + + DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); + + CacheSize = configuration.GetSqliteCacheSize(); + ReadConnectionsCount = Environment.ProcessorCount * 2; + } + + /// <inheritdoc /> + protected override int? CacheSize { get; } + + /// <inheritdoc /> + protected override TempStoreMode TempStore => TempStoreMode.Memory; + + /// <summary> + /// Opens the connection to the database. + /// </summary> + public override void Initialize() + { + base.Initialize(); + + const string CreateMediaStreamsTableCommand + = "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, TimeBase TEXT NULL, CodecTimeBase TEXT NULL, ColorPrimaries TEXT NULL, ColorSpace TEXT NULL, ColorTransfer TEXT NULL, DvVersionMajor INT NULL, DvVersionMinor INT NULL, DvProfile INT NULL, DvLevel INT NULL, RpuPresentFlag INT NULL, ElPresentFlag INT NULL, BlPresentFlag INT NULL, DvBlSignalCompatibilityId INT NULL, IsHearingImpaired BIT NULL, PRIMARY KEY (ItemId, StreamIndex))"; + + const string CreateMediaAttachmentsTableCommand + = "create table if not exists mediaattachments (ItemId GUID, AttachmentIndex INT, Codec TEXT, CodecTag TEXT NULL, Comment TEXT NULL, Filename TEXT NULL, MIMEType TEXT NULL, PRIMARY KEY (ItemId, AttachmentIndex))"; + + string[] queries = { - throw new ArgumentNullException(nameof(item)); - } + "create table if not exists TypedBaseItems (guid GUID primary key NOT NULL, type TEXT NOT NULL, data BLOB NULL, ParentId GUID NULL, Path TEXT NULL)", - CheckDisposed(); + "create table if not exists AncestorIds (ItemId GUID NOT NULL, AncestorId GUID NOT NULL, AncestorIdText TEXT NOT NULL, PRIMARY KEY (ItemId, AncestorId))", + "create index if not exists idx_AncestorIds1 on AncestorIds(AncestorId)", + "create index if not exists idx_AncestorIds5 on AncestorIds(AncestorIdText,ItemId)", - using (var connection = GetConnection()) + "create table if not exists ItemValues (ItemId GUID NOT NULL, Type INT NOT NULL, Value TEXT NOT NULL, CleanValue TEXT NOT NULL)", + + "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)", + + "drop index if exists idxPeopleItemId", + "create index if not exists idxPeopleItemId1 on People(ItemId,ListOrder)", + "create index if not exists idxPeopleName on People(Name)", + + "create table if not exists " + ChaptersTableName + " (ItemId GUID, ChapterIndex INT NOT NULL, StartPositionTicks BIGINT NOT NULL, Name TEXT, ImagePath TEXT, PRIMARY KEY (ItemId, ChapterIndex))", + + CreateMediaStreamsTableCommand, + CreateMediaAttachmentsTableCommand, + + "pragma shrink_memory" + }; + + string[] postQueries = { - connection.RunInTransaction( - db => - { - using (var saveImagesStatement = base.PrepareStatement(db, "Update TypedBaseItems set Images=@Images where guid=@Id")) - { - saveImagesStatement.TryBind("@Id", item.Id.ToByteArray()); - saveImagesStatement.TryBind("@Images", SerializeImages(item.ImageInfos)); + "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)", + "create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)", - saveImagesStatement.MoveNext(); - } - }, TransactionMode); + "create index if not exists idx_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)", + "create index if not exists idx_GuidTypeIsFolderIsVirtualItem on TypedBaseItems(Guid,Type,IsFolder,IsVirtualItem)", + "create index if not exists idx_CleanNameType on TypedBaseItems(CleanName,Type)", + + // covering index + "create index if not exists idx_TopParentIdGuid on TypedBaseItems(TopParentId,Guid)", + + // series + "create index if not exists idx_TypeSeriesPresentationUniqueKey1 on TypedBaseItems(Type,SeriesPresentationUniqueKey,PresentationUniqueKey,SortName)", + + // series counts + // seriesdateplayed sort order + "create index if not exists idx_TypeSeriesPresentationUniqueKey3 on TypedBaseItems(SeriesPresentationUniqueKey,Type,IsFolder,IsVirtualItem)", + + // live tv programs + "create index if not exists idx_TypeTopParentIdStartDate on TypedBaseItems(Type,TopParentId,StartDate)", + + // covering index for getitemvalues + "create index if not exists idx_TypeTopParentIdGuid on TypedBaseItems(Type,TopParentId,Guid)", + + // used by movie suggestions + "create index if not exists idx_TypeTopParentIdGroup on TypedBaseItems(Type,TopParentId,PresentationUniqueKey)", + "create index if not exists idx_TypeTopParentId5 on TypedBaseItems(TopParentId,IsVirtualItem)", + + // latest items + "create index if not exists idx_TypeTopParentId9 on TypedBaseItems(TopParentId,Type,IsVirtualItem,PresentationUniqueKey,DateCreated)", + "create index if not exists idx_TypeTopParentId8 on TypedBaseItems(TopParentId,IsFolder,IsVirtualItem,PresentationUniqueKey,DateCreated)", + + // resume + "create index if not exists idx_TypeTopParentId7 on TypedBaseItems(TopParentId,MediaType,IsVirtualItem,PresentationUniqueKey)", + + // items by name + "create index if not exists idx_ItemValues6 on ItemValues(ItemId,Type,CleanValue)", + "create index if not exists idx_ItemValues7 on ItemValues(Type,CleanValue,ItemId)", + + // Used to update inherited tags + "create index if not exists idx_ItemValues8 on ItemValues(Type, ItemId, Value)", + + "CREATE INDEX IF NOT EXISTS idx_TypedBaseItemsUserDataKeyType ON TypedBaseItems(UserDataKey, Type)", + "CREATE INDEX IF NOT EXISTS idx_PeopleNameListOrder ON People(Name, ListOrder)" + }; + + using (var connection = GetConnection()) + using (var transaction = connection.BeginTransaction()) + { + connection.Execute(string.Join(';', queries)); + + var existingColumnNames = GetColumnNames(connection, "AncestorIds"); + AddColumn(connection, "AncestorIds", "AncestorIdText", "Text", existingColumnNames); + + existingColumnNames = GetColumnNames(connection, "TypedBaseItems"); + + AddColumn(connection, "TypedBaseItems", "Path", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "StartDate", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "EndDate", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ChannelId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsMovie", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "CommunityRating", "Float", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "CustomRating", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IndexNumber", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsLocked", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Name", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "OfficialRating", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "MediaType", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Overview", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ParentIndexNumber", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PremiereDate", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ProductionYear", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ParentId", "GUID", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Genres", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SortName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ForcedSortName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "RunTimeTicks", "BIGINT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateCreated", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateModified", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsSeries", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "EpisodeTitle", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsRepeat", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PreferredMetadataLanguage", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PreferredMetadataCountryCode", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateLastRefreshed", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateLastSaved", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsInMixedFolder", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "LockedFields", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Studios", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Audio", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExternalServiceId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Tags", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsFolder", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "InheritedParentalRatingValue", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "UnratedType", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "TopParentId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "TrailerTypes", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "CriticRating", "Float", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "CleanName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PresentationUniqueKey", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "OriginalTitle", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PrimaryVersionId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateLastMediaAdded", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Album", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "LUFS", "Float", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsVirtualItem", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeriesName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "UserDataKey", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeasonName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeasonId", "GUID", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeriesId", "GUID", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExternalSeriesId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Tagline", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ProviderIds", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Images", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ProductionLocations", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExtraIds", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "TotalBitrate", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExtraType", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Artists", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "AlbumArtists", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExternalId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeriesPresentationUniqueKey", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ShowId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "OwnerId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Width", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Height", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Size", "BIGINT", existingColumnNames); + + existingColumnNames = GetColumnNames(connection, "ItemValues"); + AddColumn(connection, "ItemValues", "CleanValue", "Text", existingColumnNames); + + existingColumnNames = GetColumnNames(connection, ChaptersTableName); + AddColumn(connection, ChaptersTableName, "ImageDateModified", "DATETIME", existingColumnNames); + + existingColumnNames = GetColumnNames(connection, "MediaStreams"); + AddColumn(connection, "MediaStreams", "IsAvc", "BIT", existingColumnNames); + AddColumn(connection, "MediaStreams", "TimeBase", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "CodecTimeBase", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "Title", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "NalLengthSize", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "Comment", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "CodecTag", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "PixelFormat", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "BitDepth", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "RefFrames", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "KeyFrames", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "IsAnamorphic", "BIT", existingColumnNames); + + AddColumn(connection, "MediaStreams", "ColorPrimaries", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "ColorSpace", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "ColorTransfer", "TEXT", existingColumnNames); + + AddColumn(connection, "MediaStreams", "DvVersionMajor", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "DvVersionMinor", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "DvProfile", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "DvLevel", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "RpuPresentFlag", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "ElPresentFlag", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "BlPresentFlag", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "DvBlSignalCompatibilityId", "INT", existingColumnNames); + + AddColumn(connection, "MediaStreams", "IsHearingImpaired", "BIT", existingColumnNames); + + connection.Execute(string.Join(';', postQueries)); + + transaction.Commit(); } } + public void SaveImages(BaseItem item) + { + ArgumentNullException.ThrowIfNull(item); + + CheckDisposed(); + + var images = SerializeImages(item.ImageInfos); + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + using var saveImagesStatement = PrepareStatement(connection, "Update TypedBaseItems set Images=@Images where guid=@Id"); + saveImagesStatement.TryBind("@Id", item.Id); + saveImagesStatement.TryBind("@Images", images); + + saveImagesStatement.ExecuteNonQuery(); + transaction.Commit(); + } + /// <summary> /// Saves the items. /// </summary> /// <param name="items">The items.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <exception cref="ArgumentNullException"> - /// items - /// or - /// cancellationToken + /// <paramref name="items"/> or <paramref name="cancellationToken"/> is <c>null</c>. /// </exception> - public void SaveItems(IEnumerable<BaseItem> items, CancellationToken cancellationToken) + public void SaveItems(IReadOnlyList<BaseItem> items, CancellationToken cancellationToken) { - if (items == null) - { - throw new ArgumentNullException(nameof(items)); - } + ArgumentNullException.ThrowIfNull(items); cancellationToken.ThrowIfCancellationRequested(); CheckDisposed(); - var tuples = new List<(BaseItem, List<Guid>, BaseItem, string, List<string>)>(); - foreach (var item in items) + var itemsLen = items.Count; + var tuples = new ValueTuple<BaseItem, List<Guid>, BaseItem, string, List<string>>[itemsLen]; + for (int i = 0; i < itemsLen; i++) { + var item = items[i]; var ancestorIds = item.SupportsAncestors ? item.GetAncestorIds().Distinct().ToList() : null; @@ -547,49 +605,40 @@ namespace Emby.Server.Implementations.Data var userdataKey = item.GetUserDataKeys().FirstOrDefault(); var inheritedTags = item.GetInheritedTags(); - tuples.Add((item, ancestorIds, topParent, userdataKey, inheritedTags)); + tuples[i] = (item, ancestorIds, topParent, userdataKey, inheritedTags); } - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - SaveItemsInTranscation(db, tuples); - }, TransactionMode); - } + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + SaveItemsInTransaction(connection, tuples); + transaction.Commit(); } - private void SaveItemsInTranscation(IDatabaseConnection db, IEnumerable<(BaseItem, List<Guid>, BaseItem, string, List<string>)> tuples) + private void SaveItemsInTransaction(SqliteConnection db, IEnumerable<(BaseItem Item, List<Guid> AncestorIds, BaseItem TopParent, string UserDataKey, List<string> InheritedTags)> tuples) { - var statements = PrepareAll(db, new string[] - { - SaveItemCommandText, - "delete from AncestorIds where ItemId=@ItemId" - }); - - using (var saveItemStatement = statements[0]) - using (var deleteAncestorsStatement = statements[1]) + using (var saveItemStatement = PrepareStatement(db, SaveItemCommandText)) + using (var deleteAncestorsStatement = PrepareStatement(db, "delete from AncestorIds where ItemId=@ItemId")) { var requiresReset = false; foreach (var tuple in tuples) { if (requiresReset) { - saveItemStatement.Reset(); + saveItemStatement.Parameters.Clear(); + deleteAncestorsStatement.Parameters.Clear(); } - 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); @@ -601,7 +650,7 @@ namespace Emby.Server.Implementations.Data private string GetPathToSave(string path) { - if (path == null) + if (path is null) { return null; } @@ -614,7 +663,7 @@ namespace Emby.Server.Implementations.Data return _appHost.ExpandVirtualPath(path); } - private void SaveItem(BaseItem item, BaseItem topParent, string userDataKey, IStatement saveItemStatement) + private void SaveItem(BaseItem item, BaseItem topParent, string userDataKey, SqliteCommand saveItemStatement) { Type type = item.GetType(); @@ -623,7 +672,7 @@ namespace Emby.Server.Implementations.Data if (TypeRequiresDeserialization(type)) { - saveItemStatement.TryBind("@data", JsonSerializer.SerializeToUtf8Bytes(item, type, _jsonOptions)); + saveItemStatement.TryBind("@data", JsonSerializer.SerializeToUtf8Bytes(item, type, _jsonOptions), true); } else { @@ -650,7 +699,7 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBindNull("@EndDate"); } - saveItemStatement.TryBind("@ChannelId", item.ChannelId.Equals(Guid.Empty) ? null : item.ChannelId.ToString("N", CultureInfo.InvariantCulture)); + saveItemStatement.TryBind("@ChannelId", item.ChannelId.Equals(default) ? null : item.ChannelId.ToString("N", CultureInfo.InvariantCulture)); if (item is IHasProgramAttributes hasProgramAttributes) { @@ -680,7 +729,7 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@ProductionYear", item.ProductionYear); var parentId = item.ParentId; - if (parentId.Equals(Guid.Empty)) + if (parentId.Equals(default)) { saveItemStatement.TryBindNull("@ParentId"); } @@ -800,7 +849,7 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@UnratedType", item.GetBlockUnratedType().ToString()); - if (topParent == null) + if (topParent is null) { saveItemStatement.TryBindNull("@TopParentId"); } @@ -851,6 +900,7 @@ namespace Emby.Server.Implementations.Data } saveItemStatement.TryBind("@Album", item.Album); + saveItemStatement.TryBind("@LUFS", item.LUFS); saveItemStatement.TryBind("@IsVirtualItem", item.IsVirtualItem); if (item is IHasSeries hasSeriesName) @@ -875,7 +925,7 @@ namespace Emby.Server.Implementations.Data { saveItemStatement.TryBind("@SeasonName", episode.SeasonName); - var nullableSeasonId = episode.SeasonId == Guid.Empty ? (Guid?)null : episode.SeasonId; + var nullableSeasonId = episode.SeasonId.Equals(default) ? (Guid?)null : episode.SeasonId; saveItemStatement.TryBind("@SeasonId", nullableSeasonId); } @@ -887,7 +937,7 @@ namespace Emby.Server.Implementations.Data if (item is IHasSeries hasSeries) { - var nullableSeriesId = hasSeries.SeriesId.Equals(Guid.Empty) ? (Guid?)null : hasSeries.SeriesId; + var nullableSeriesId = hasSeries.SeriesId.Equals(default) ? (Guid?)null : hasSeries.SeriesId; saveItemStatement.TryBind("@SeriesId", nullableSeriesId); saveItemStatement.TryBind("@SeriesPresentationUniqueKey", hasSeries.SeriesPresentationUniqueKey); @@ -960,7 +1010,7 @@ namespace Emby.Server.Implementations.Data } Guid ownerId = item.OwnerId; - if (ownerId == Guid.Empty) + if (ownerId.Equals(default)) { saveItemStatement.TryBindNull("@OwnerId"); } @@ -969,7 +1019,7 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@OwnerId", ownerId); } - saveItemStatement.MoveNext(); + saveItemStatement.ExecuteNonQuery(); } internal static string SerializeProviderIds(Dictionary<string, string> providerIds) @@ -1056,7 +1106,7 @@ namespace Emby.Server.Implementations.Data { var image = ItemImageInfoFromValueString(part); - if (image != null) + if (image is not null) { result[position++] = image; } @@ -1098,13 +1148,15 @@ namespace Emby.Server.Implementations.Data bldr.Append(Delimiter) // Replace delimiters with other characters. // This can be removed when we migrate to a proper DB. - .Append(hash.Replace('*', '/').Replace('|', '\\')); + .Append(hash.Replace(Delimiter, '/').Replace('|', '\\')); } } internal ItemImageInfo ItemImageInfoFromValueString(ReadOnlySpan<char> value) { - var nextSegment = value.IndexOf('*'); + const char Delimiter = '*'; + + var nextSegment = value.IndexOf(Delimiter); if (nextSegment == -1) { return null; @@ -1112,7 +1164,7 @@ namespace Emby.Server.Implementations.Data ReadOnlySpan<char> path = value[..nextSegment]; value = value[(nextSegment + 1)..]; - nextSegment = value.IndexOf('*'); + nextSegment = value.IndexOf(Delimiter); if (nextSegment == -1) { return null; @@ -1120,7 +1172,7 @@ namespace Emby.Server.Implementations.Data ReadOnlySpan<char> dateModified = value[..nextSegment]; value = value[(nextSegment + 1)..]; - nextSegment = value.IndexOf('*'); + nextSegment = value.IndexOf(Delimiter); if (nextSegment == -1) { nextSegment = value.Length; @@ -1133,21 +1185,31 @@ namespace Emby.Server.Implementations.Data Path = RestorePath(path.ToString()) }; - if (long.TryParse(dateModified, NumberStyles.Any, CultureInfo.InvariantCulture, out var ticks)) + if (long.TryParse(dateModified, CultureInfo.InvariantCulture, out var ticks) + && ticks >= DateTime.MinValue.Ticks + && ticks <= DateTime.MaxValue.Ticks) { image.DateModified = new DateTime(ticks, DateTimeKind.Utc); } + else + { + return null; + } - if (Enum.TryParse(imageType.ToString(), true, out ImageType type)) + if (Enum.TryParse(imageType, true, out ImageType type)) { image.Type = type; } + else + { + return null; + } // Optional parameters: width*height*blurhash if (nextSegment + 1 < value.Length - 1) { value = value[(nextSegment + 1)..]; - nextSegment = value.IndexOf('*'); + nextSegment = value.IndexOf(Delimiter); if (nextSegment == -1 || nextSegment == value.Length) { return image; @@ -1156,7 +1218,7 @@ namespace Emby.Server.Implementations.Data ReadOnlySpan<char> widthSpan = value[..nextSegment]; value = value[(nextSegment + 1)..]; - nextSegment = value.IndexOf('*'); + nextSegment = value.IndexOf(Delimiter); if (nextSegment == -1) { nextSegment = value.Length; @@ -1182,7 +1244,7 @@ namespace Emby.Server.Implementations.Data var c = value[i]; blurHashSpan[i] = c switch { - '/' => '*', + '/' => Delimiter, '\\' => '|', _ => c }; @@ -1200,27 +1262,25 @@ namespace Emby.Server.Implementations.Data /// </summary> /// <param name="id">The id.</param> /// <returns>BaseItem.</returns> - /// <exception cref="ArgumentNullException">id</exception> - /// <exception cref="ArgumentException"></exception> + /// <exception cref="ArgumentNullException"><paramref name="id"/> is <c>null</c>.</exception> + /// <exception cref="ArgumentException"><paramr name="id"/> is <seealso cref="Guid.Empty"/>.</exception> public BaseItem RetrieveItem(Guid id) { - if (id == Guid.Empty) + if (id.Equals(default)) { throw new ArgumentException("Guid can't be empty", nameof(id)); } CheckDisposed(); - using (var connection = GetConnection(true)) + using (var connection = GetConnection()) + using (var statement = PrepareStatement(connection, _retrieveItemColumnsSelectQuery)) { - using (var statement = PrepareStatement(connection, _retriveItemColumnsSelectQuery)) - { - statement.TryBind("@guid", id); + statement.TryBind("@guid", id); - foreach (var row in statement.ExecuteQuery()) - { - return GetItem(row, new InternalItemsQuery()); - } + foreach (var row in statement.ExecuteQuery()) + { + return GetItem(row, new InternalItemsQuery()); } } @@ -1231,88 +1291,41 @@ namespace Emby.Server.Implementations.Data { if (_config.Configuration.SkipDeserializationForBasicTypes) { - if (type == typeof(Channel)) - { - return false; - } - else if (type == typeof(UserRootFolder)) + if (type == typeof(Channel) + || type == typeof(UserRootFolder)) { return false; } } - if (type == typeof(Season)) - { - return false; - } - else if (type == typeof(MusicArtist)) - { - return false; - } - else if (type == typeof(Person)) - { - return false; - } - else if (type == typeof(MusicGenre)) - { - return false; - } - else if (type == typeof(Genre)) - { - return false; - } - else if (type == typeof(Studio)) - { - return false; - } - else if (type == typeof(PlaylistsFolder)) - { - return false; - } - else if (type == typeof(PhotoAlbum)) - { - return false; - } - else if (type == typeof(Year)) - { - return false; - } - else if (type == typeof(Book)) - { - return false; - } - else if (type == typeof(LiveTvProgram)) - { - return false; - } - else if (type == typeof(AudioBook)) - { - return false; - } - else if (type == typeof(Audio)) - { - return false; - } - else if (type == typeof(MusicAlbum)) - { - return false; - } - - return true; + return type != typeof(Season) + && type != typeof(MusicArtist) + && type != typeof(Person) + && type != typeof(MusicGenre) + && type != typeof(Genre) + && type != typeof(Studio) + && type != typeof(PlaylistsFolder) + && type != typeof(PhotoAlbum) + && type != typeof(Year) + && type != typeof(Book) + && type != typeof(LiveTvProgram) + && type != typeof(AudioBook) + && type != typeof(Audio) + && type != typeof(MusicAlbum); } - private BaseItem GetItem(IReadOnlyList<ResultSetValue> reader, InternalItemsQuery query) + private BaseItem GetItem(SqliteDataReader reader, InternalItemsQuery query) { return GetItem(reader, query, HasProgramAttributes(query), HasEpisodeAttributes(query), HasServiceName(query), HasStartDate(query), HasTrailerTypes(query), HasArtistFields(query), HasSeriesFields(query)); } - private BaseItem GetItem(IReadOnlyList<ResultSetValue> reader, InternalItemsQuery query, bool enableProgramAttributes, bool hasEpisodeAttributes, bool hasServiceName, bool queryHasStartDate, bool hasTrailerTypes, bool hasArtistFields, bool hasSeriesFields) + private BaseItem GetItem(SqliteDataReader reader, InternalItemsQuery query, bool enableProgramAttributes, bool hasEpisodeAttributes, bool hasServiceName, bool queryHasStartDate, bool hasTrailerTypes, bool hasArtistFields, bool hasSeriesFields) { var typeString = reader.GetString(0); var type = _typeMapper.GetType(typeString); - if (type == null) + if (type is null) { return null; } @@ -1323,7 +1336,7 @@ namespace Emby.Server.Implementations.Data { try { - item = JsonSerializer.Deserialize(reader[1].ToBlob(), type, _jsonOptions) as BaseItem; + item = JsonSerializer.Deserialize(reader.GetStream(1), type, _jsonOptions) as BaseItem; } catch (JsonException ex) { @@ -1331,7 +1344,7 @@ namespace Emby.Server.Implementations.Data } } - if (item == null) + if (item is null) { try { @@ -1342,7 +1355,7 @@ namespace Emby.Server.Implementations.Data } } - if (item == null) + if (item is null) { return null; } @@ -1364,17 +1377,9 @@ namespace Emby.Server.Implementations.Data item.EndDate = endDate; } - var channelId = reader[index]; - if (!channelId.IsDbNull()) + if (reader.TryGetGuid(index, out var guid)) { - if (!Utf8Parser.TryParse(channelId.ToBlob(), out Guid value, out _, standardFormat: 'N')) - { - var str = reader.GetString(index); - Logger.LogWarning("{ChannelId} isn't in the expected format", str); - value = new Guid(str); - } - - item.ChannelId = value; + item.ChannelId = guid; } index++; @@ -1555,7 +1560,6 @@ namespace Emby.Server.Implementations.Data if (reader.TryGetString(index++, out var audioString)) { - // TODO Span overload coming in the future https://github.com/dotnet/runtime/issues/1916 if (Enum.TryParse(audioString, true, out ProgramAudio audio)) { item.Audio = audio; @@ -1594,18 +1598,16 @@ namespace Emby.Server.Implementations.Data { if (reader.TryGetString(index++, out var lockedFields)) { - IEnumerable<MetadataField> GetLockedFields(string s) + List<MetadataField> fields = null; + foreach (var i in lockedFields.AsSpan().Split('|')) { - foreach (var i in s.Split('|', StringSplitOptions.RemoveEmptyEntries)) + if (Enum.TryParse(i, true, out MetadataField parsedValue)) { - if (Enum.TryParse(i, true, out MetadataField parsedValue)) - { - yield return parsedValue; - } + (fields ??= new List<MetadataField>()).Add(parsedValue); } } - item.LockedFields = GetLockedFields(lockedFields).ToArray(); + item.LockedFields = fields?.ToArray() ?? Array.Empty<MetadataField>(); } } @@ -1631,18 +1633,16 @@ namespace Emby.Server.Implementations.Data { if (reader.TryGetString(index, out var trailerTypes)) { - IEnumerable<TrailerType> GetTrailerTypes(string s) + List<TrailerType> types = null; + foreach (var i in trailerTypes.AsSpan().Split('|')) { - foreach (var i in s.Split('|', StringSplitOptions.RemoveEmptyEntries)) + if (Enum.TryParse(i, true, out TrailerType parsedValue)) { - if (Enum.TryParse(i, true, out TrailerType parsedValue)) - { - yield return parsedValue; - } + (types ??= new List<TrailerType>()).Add(parsedValue); } } - trailer.TrailerTypes = GetTrailerTypes(trailerTypes).ToArray(); + trailer.TrailerTypes = types?.ToArray() ?? Array.Empty<TrailerType>(); } } @@ -1682,6 +1682,11 @@ namespace Emby.Server.Implementations.Data item.Album = album; } + if (reader.TryGetSingle(index++, out var lUFS)) + { + item.LUFS = lUFS; + } + if (reader.TryGetSingle(index++, out var criticRating)) { item.CriticRating = criticRating; @@ -1728,7 +1733,7 @@ namespace Emby.Server.Implementations.Data var hasSeries = item as IHasSeries; if (hasSeriesFields) { - if (hasSeries != null) + if (hasSeries is not null) { if (reader.TryGetGuid(index, out var seriesId)) { @@ -1841,7 +1846,7 @@ namespace Emby.Server.Implementations.Data if (HasField(query, ItemFields.SeriesPresentationUniqueKey)) { - if (hasSeries != null) + if (hasSeries is not null) { if (reader.TryGetString(index, out var seriesPresentationUniqueKey)) { @@ -1884,56 +1889,40 @@ namespace Emby.Server.Implementations.Data return result; } - /// <summary> - /// Gets chapters for an item. - /// </summary> - /// <param name="item">The item.</param> - /// <returns>IEnumerable{ChapterInfo}.</returns> - /// <exception cref="ArgumentNullException">id</exception> + /// <inheritdoc /> public List<ChapterInfo> GetChapters(BaseItem item) { CheckDisposed(); - using (var connection = GetConnection(true)) + var chapters = new List<ChapterInfo>(); + using (var connection = GetConnection()) + using (var statement = PrepareStatement(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc")) { - var chapters = new List<ChapterInfo>(); + statement.TryBind("@ItemId", item.Id); - using (var statement = PrepareStatement(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc")) + foreach (var row in statement.ExecuteQuery()) { - statement.TryBind("@ItemId", item.Id); - - foreach (var row in statement.ExecuteQuery()) - { - chapters.Add(GetChapter(row, item)); - } + chapters.Add(GetChapter(row, item)); } - - return chapters; } + + return chapters; } - /// <summary> - /// Gets a single chapter for an item. - /// </summary> - /// <param name="item">The item.</param> - /// <param name="index">The index.</param> - /// <returns>ChapterInfo.</returns> - /// <exception cref="ArgumentNullException">id</exception> + /// <inheritdoc /> public ChapterInfo GetChapter(BaseItem item, int index) { CheckDisposed(); - using (var connection = GetConnection(true)) + using (var connection = GetConnection()) + using (var statement = PrepareStatement(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex")) { - using (var statement = PrepareStatement(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex")) - { - statement.TryBind("@ItemId", item.Id); - statement.TryBind("@ChapterIndex", index); + statement.TryBind("@ItemId", item.Id); + statement.TryBind("@ChapterIndex", index); - foreach (var row in statement.ExecuteQuery()) - { - return GetChapter(row, item); - } + foreach (var row in statement.ExecuteQuery()) + { + return GetChapter(row, item); } } @@ -1946,7 +1935,7 @@ namespace Emby.Server.Implementations.Data /// <param name="reader">The reader.</param> /// <param name="item">The item.</param> /// <returns>ChapterInfo.</returns> - private ChapterInfo GetChapter(IReadOnlyList<ResultSetValue> reader, BaseItem item) + private ChapterInfo GetChapter(SqliteDataReader reader, BaseItem item) { var chapter = new ChapterInfo { @@ -1961,18 +1950,7 @@ namespace Emby.Server.Implementations.Data if (reader.TryGetString(2, out var imagePath)) { chapter.ImagePath = imagePath; - - if (!string.IsNullOrEmpty(chapter.ImagePath)) - { - try - { - chapter.ImageTag = _imageProcessor.GetImageCacheTag(item, chapter); - } - catch (Exception ex) - { - Logger.LogError(ex, "Failed to create image cache tag."); - } - } + chapter.ImageTag = _imageProcessor.GetImageCacheTag(item, chapter); } if (reader.TryReadDateTime(3, out var imageDateModified)) @@ -1986,36 +1964,31 @@ namespace Emby.Server.Implementations.Data /// <summary> /// Saves the chapters. /// </summary> + /// <param name="id">The item id.</param> + /// <param name="chapters">The chapters.</param> public void SaveChapters(Guid id, IReadOnlyList<ChapterInfo> chapters) { CheckDisposed(); - if (id.Equals(Guid.Empty)) + if (id.Equals(default)) { throw new ArgumentNullException(nameof(id)); } - if (chapters == null) - { - throw new ArgumentNullException(nameof(chapters)); - } + ArgumentNullException.ThrowIfNull(chapters); - var idBlob = id.ToByteArray(); - - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - // First delete chapters - db.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", idBlob); + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + // First delete chapters + using var command = connection.PrepareStatement($"delete from {ChaptersTableName} where ItemId=@ItemId"); + command.TryBind("@ItemId", id); + command.ExecuteNonQuery(); - InsertChapters(idBlob, chapters, db); - }, TransactionMode); - } + InsertChapters(id, chapters, connection); + transaction.Commit(); } - private void InsertChapters(byte[] idBlob, IReadOnlyList<ChapterInfo> chapters, IDatabaseConnection db) + private void InsertChapters(Guid idBlob, IReadOnlyList<ChapterInfo> chapters, SqliteConnection db) { var startIndex = 0; var limit = 100; @@ -2030,10 +2003,10 @@ namespace Emby.Server.Implementations.Data for (var i = startIndex; i < endIndex; i++) { - insertText.AppendFormat("(@ItemId, @ChapterIndex{0}, @StartPositionTicks{0}, @Name{0}, @ImagePath{0}, @ImageDateModified{0}),", i.ToString(CultureInfo.InvariantCulture)); + insertText.AppendFormat(CultureInfo.InvariantCulture, "(@ItemId, @ChapterIndex{0}, @StartPositionTicks{0}, @Name{0}, @ImagePath{0}, @ImageDateModified{0}),", i.ToString(CultureInfo.InvariantCulture)); } - insertText.Length -= 1; // Remove last , + insertText.Length -= 1; // Remove trailing comma using (var statement = PrepareStatement(db, insertText.ToString())) { @@ -2054,8 +2027,7 @@ namespace Emby.Server.Implementations.Data chapterIndex++; } - statement.Reset(); - statement.MoveNext(); + statement.ExecuteNonQuery(); } startIndex += limit; @@ -2065,12 +2037,12 @@ namespace Emby.Server.Implementations.Data private static bool EnableJoinUserData(InternalItemsQuery query) { - if (query.User == null) + if (query.User is null) { 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) @@ -2085,8 +2057,6 @@ namespace Emby.Server.Implementations.Data || query.IsLiked.HasValue; } - private readonly ItemFields[] _allFields = Enum.GetValues<ItemFields>(); - private bool HasField(InternalItemsQuery query, ItemFields name) { switch (name) @@ -2119,26 +2089,9 @@ namespace Emby.Server.Implementations.Data } } - private static readonly HashSet<string> _programExcludeParentTypes = new HashSet<string>(StringComparer.OrdinalIgnoreCase) - { - "Series", - "Season", - "MusicAlbum", - "MusicArtist", - "PhotoAlbum" - }; - - private static readonly HashSet<string> _programTypes = new HashSet<string>(StringComparer.OrdinalIgnoreCase) - { - "Program", - "TvChannel", - "LiveTvProgram", - "LiveTvTvChannel" - }; - private bool HasProgramAttributes(InternalItemsQuery query) { - if (_programExcludeParentTypes.Contains(query.ParentType)) + if (query.ParentType is not null && _programExcludeParentTypes.Contains(query.ParentType.Value)) { return false; } @@ -2151,15 +2104,9 @@ namespace Emby.Server.Implementations.Data return query.IncludeItemTypes.Any(x => _programTypes.Contains(x)); } - private static readonly HashSet<string> _serviceTypes = new HashSet<string>(StringComparer.OrdinalIgnoreCase) - { - "TvChannel", - "LiveTvTvChannel" - }; - private bool HasServiceName(InternalItemsQuery query) { - if (_programExcludeParentTypes.Contains(query.ParentType)) + if (query.ParentType is not null && _programExcludeParentTypes.Contains(query.ParentType.Value)) { return false; } @@ -2172,15 +2119,9 @@ namespace Emby.Server.Implementations.Data return query.IncludeItemTypes.Any(x => _serviceTypes.Contains(x)); } - private static readonly HashSet<string> _startDateTypes = new HashSet<string>(StringComparer.OrdinalIgnoreCase) - { - "Program", - "LiveTvProgram" - }; - private bool HasStartDate(InternalItemsQuery query) { - if (_programExcludeParentTypes.Contains(query.ParentType)) + if (query.ParentType is not null && _programExcludeParentTypes.Contains(query.ParentType.Value)) { return false; } @@ -2200,7 +2141,7 @@ namespace Emby.Server.Implementations.Data return true; } - return query.IncludeItemTypes.Contains("Episode", StringComparer.OrdinalIgnoreCase); + return query.IncludeItemTypes.Contains(BaseItemKind.Episode); } private bool HasTrailerTypes(InternalItemsQuery query) @@ -2210,28 +2151,12 @@ namespace Emby.Server.Implementations.Data return true; } - return query.IncludeItemTypes.Contains("Trailer", StringComparer.OrdinalIgnoreCase); + return query.IncludeItemTypes.Contains(BaseItemKind.Trailer); } - private static readonly HashSet<string> _artistExcludeParentTypes = new HashSet<string>(StringComparer.OrdinalIgnoreCase) - { - "Series", - "Season", - "PhotoAlbum" - }; - - private static readonly HashSet<string> _artistsTypes = new HashSet<string>(StringComparer.OrdinalIgnoreCase) - { - "Audio", - "MusicAlbum", - "MusicVideo", - "AudioBook", - "AudioPodcast" - }; - private bool HasArtistFields(InternalItemsQuery query) { - if (_artistExcludeParentTypes.Contains(query.ParentType)) + if (query.ParentType is not null && _artistExcludeParentTypes.Contains(query.ParentType.Value)) { return false; } @@ -2244,17 +2169,9 @@ namespace Emby.Server.Implementations.Data return query.IncludeItemTypes.Any(x => _artistsTypes.Contains(x)); } - private static readonly HashSet<string> _seriesTypes = new HashSet<string>(StringComparer.OrdinalIgnoreCase) - { - "Book", - "AudioBook", - "Episode", - "Season" - }; - private bool HasSeriesFields(InternalItemsQuery query) { - if (string.Equals(query.ParentType, "PhotoAlbum", StringComparison.OrdinalIgnoreCase)) + if (query.ParentType == BaseItemKind.PhotoAlbum) { return false; } @@ -2269,7 +2186,7 @@ namespace Emby.Server.Implementations.Data private void SetFinalColumnsToSelect(InternalItemsQuery query, List<string> columns) { - foreach (var field in _allFields) + foreach (var field in _allItemFields) { if (!HasField(query, field)) { @@ -2361,20 +2278,24 @@ namespace Emby.Server.Implementations.Data columns.Add("UserDatas.rating"); } - if (query.SimilarTo != null) + if (query.SimilarTo is not null) { var item = query.SimilarTo; var builder = new StringBuilder(); builder.Append('('); - if (string.IsNullOrEmpty(item.OfficialRating)) + if (item.InheritedParentalRatingValue == 0) { - builder.Append("(OfficialRating is null * 10)"); + builder.Append("((InheritedParentalRatingValue=0) * 10)"); } else { - builder.Append("(OfficialRating=@ItemOfficialRating * 10)"); + builder.Append( + @"(SELECT CASE WHEN COALESCE(InheritedParentalRatingValue, 0)=0 + THEN 0 + ELSE 10.0 / (1.0 + ABS(InheritedParentalRatingValue - @InheritedParentalRatingValue)) + END)"); } if (item.ProductionYear.HasValue) @@ -2384,7 +2305,8 @@ namespace Emby.Server.Implementations.Data } // 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 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) { @@ -2425,10 +2347,14 @@ namespace Emby.Server.Implementations.Data 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("+ (SELECT COUNT(1) * 1 from ItemValues where ItemId=Guid and CleanValue like @SearchTermContains)"); + builder.Append("+ (SELECT COUNT(1) * 2 from ItemValues where ItemId=Guid and CleanValue like @SearchTermStartsWith)"); + builder.Append("+ (SELECT COUNT(1) * 10 from ItemValues where ItemId=Guid and CleanValue like @SearchTermEquals)"); } builder.Append(") as SearchScore"); @@ -2437,7 +2363,7 @@ namespace Emby.Server.Implementations.Data } } - private void BindSearchParams(InternalItemsQuery query, IStatement statement) + private void BindSearchParams(InternalItemsQuery query, SqliteCommand statement) { var searchTerm = query.SearchTerm; @@ -2449,43 +2375,53 @@ namespace Emby.Server.Implementations.Data searchTerm = FixUnicodeChars(searchTerm); searchTerm = GetCleanValue(searchTerm); - var commandText = statement.SQL; - if (commandText.IndexOf("@SearchTermStartsWith", StringComparison.OrdinalIgnoreCase) != -1) + var commandText = statement.CommandText; + if (commandText.Contains("@SearchTermStartsWith", StringComparison.OrdinalIgnoreCase)) { statement.TryBind("@SearchTermStartsWith", searchTerm + "%"); } - if (commandText.IndexOf("@SearchTermContains", StringComparison.OrdinalIgnoreCase) != -1) + if (commandText.Contains("@SearchTermContains", StringComparison.OrdinalIgnoreCase)) { statement.TryBind("@SearchTermContains", "%" + searchTerm + "%"); } + + if (commandText.Contains("@SearchTermEquals", StringComparison.OrdinalIgnoreCase)) + { + statement.TryBind("@SearchTermEquals", searchTerm); + } } - private void BindSimilarParams(InternalItemsQuery query, IStatement statement) + private void BindSimilarParams(InternalItemsQuery query, SqliteCommand statement) { var item = query.SimilarTo; - if (item == null) + if (item is null) { return; } - var commandText = statement.SQL; + var commandText = statement.CommandText; - if (commandText.IndexOf("@ItemOfficialRating", StringComparison.OrdinalIgnoreCase) != -1) + if (commandText.Contains("@ItemOfficialRating", StringComparison.OrdinalIgnoreCase)) { statement.TryBind("@ItemOfficialRating", item.OfficialRating); } - if (commandText.IndexOf("@ItemProductionYear", StringComparison.OrdinalIgnoreCase) != -1) + if (commandText.Contains("@ItemProductionYear", StringComparison.OrdinalIgnoreCase)) { statement.TryBind("@ItemProductionYear", item.ProductionYear ?? 0); } - if (commandText.IndexOf("@SimilarItemId", StringComparison.OrdinalIgnoreCase) != -1) + 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) @@ -2521,15 +2457,10 @@ namespace Emby.Server.Implementations.Data public int GetCount(InternalItemsQuery query) { - if (query == null) - { - throw new ArgumentNullException(nameof(query)); - } + ArgumentNullException.ThrowIfNull(query); CheckDisposed(); - var now = DateTime.UtcNow; - // Hack for right now since we currently don't support filtering out these duplicates within a query if (query.Limit.HasValue && query.EnableGroupByMetadataKey) { @@ -2551,48 +2482,39 @@ namespace Emby.Server.Implementations.Data } var commandText = commandTextBuilder.ToString(); - int count; - using (var connection = GetConnection(true)) + + using (new QueryTimeLogger(Logger, commandText)) + using (var connection = GetConnection()) + using (var statement = PrepareStatement(connection, commandText)) { - using (var statement = PrepareStatement(connection, commandText)) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + 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); - count = statement.ExecuteQuery().SelectScalarInt().First(); - } + return statement.SelectScalarInt(); } - - LogQueryTime("GetCount", commandText, now); - return count; } public List<BaseItem> GetItemList(InternalItemsQuery query) { - if (query == null) - { - throw new ArgumentNullException(nameof(query)); - } + ArgumentNullException.ThrowIfNull(query); CheckDisposed(); - var now = DateTime.UtcNow; - // Hack for right now since we currently don't support filtering out these duplicates within a query if (query.Limit.HasValue && query.EnableGroupByMetadataKey) { query.Limit = query.Limit.Value + 4; } - var columns = _retriveItemColumns.ToList(); + var columns = _retrieveItemColumns.ToList(); SetFinalColumnsToSelect(query, columns); var commandTextBuilder = new StringBuilder("select ", 1024) .AppendJoin(',', columns) @@ -2629,61 +2551,58 @@ namespace Emby.Server.Implementations.Data var commandText = commandTextBuilder.ToString(); var items = new List<BaseItem>(); - using (var connection = GetConnection(true)) + using (new QueryTimeLogger(Logger, commandText)) + using (var connection = GetConnection()) + using (var statement = PrepareStatement(connection, commandText)) { - using (var statement = PrepareStatement(connection, commandText)) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + 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()) + foreach (var row in statement.ExecuteQuery()) + { + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + if (item is not null) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); - if (item != null) - { - items.Add(item); - } + items.Add(item); } } + } - // Hack for right now since we currently don't support filtering out these duplicates within a query - if (query.EnableGroupByMetadataKey) + // Hack for right now since we currently don't support filtering out these duplicates within a query + if (query.EnableGroupByMetadataKey) + { + var limit = query.Limit ?? int.MaxValue; + limit -= 4; + var newList = new List<BaseItem>(); + + foreach (var item in items) { - var limit = query.Limit ?? int.MaxValue; - limit -= 4; - var newList = new List<BaseItem>(); + AddItem(newList, item); - foreach (var item in items) + if (newList.Count >= limit) { - AddItem(newList, item); - - if (newList.Count >= limit) - { - break; - } + break; } - - items = newList; } - } - LogQueryTime("GetItemList", commandText, now); + items = newList; + } return items; } @@ -2716,12 +2635,12 @@ namespace Emby.Server.Implementations.Data foreach (var providerId in newItem.ProviderIds) { - if (providerId.Key == MetadataProvider.TmdbCollection.ToString()) + if (string.Equals(providerId.Key, nameof(MetadataProvider.TmdbCollection), StringComparison.Ordinal)) { continue; } - if (item.GetProviderId(providerId.Key) == providerId.Value) + if (string.Equals(item.GetProviderId(providerId.Key), providerId.Value, StringComparison.Ordinal)) { if (newItem.SourceType == SourceType.Library) { @@ -2736,54 +2655,28 @@ namespace Emby.Server.Implementations.Data items.Add(newItem); } - private void LogQueryTime(string methodName, string commandText, DateTime startDate) - { - var elapsed = (DateTime.UtcNow - startDate).TotalMilliseconds; - -#if DEBUG - const int SlowThreshold = 100; -#else - const int SlowThreshold = 10; -#endif - - if (elapsed >= SlowThreshold) - { - Logger.LogDebug( - "{Method} query time (slow): {ElapsedMs}ms. Query: {Query}", - methodName, - elapsed, - commandText); - } - } - public QueryResult<BaseItem> GetItems(InternalItemsQuery query) { - if (query == null) - { - throw new ArgumentNullException(nameof(query)); - } + ArgumentNullException.ThrowIfNull(query); CheckDisposed(); if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0)) { var returnList = GetItemList(query); - return new QueryResult<BaseItem> - { - Items = returnList, - TotalRecordCount = returnList.Count - }; + return new QueryResult<BaseItem>( + query.StartIndex, + returnList.Count, + returnList); } - var now = DateTime.UtcNow; - // Hack for right now since we currently don't support filtering out these duplicates within a query if (query.Limit.HasValue && query.EnableGroupByMetadataKey) { query.Limit = query.Limit.Value + 4; } - var columns = _retriveItemColumns.ToList(); + var columns = _retrieveItemColumns.ToList(); SetFinalColumnsToSelect(query, columns); var commandTextBuilder = new StringBuilder("select ", 512) .AppendJoin(',', columns) @@ -2867,74 +2760,66 @@ namespace Emby.Server.Implementations.Data var list = new List<BaseItem>(); var result = new QueryResult<BaseItem>(); - using (var connection = GetConnection(true)) + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + if (!isReturningZeroItems) { - connection.RunInTransaction( - db => + using (new QueryTimeLogger(Logger, itemQuery, "GetItems.ItemQuery")) + using (var statement = PrepareStatement(connection, itemQuery)) { - var itemQueryStatement = PrepareStatement(db, itemQuery); - var totalRecordCountQueryStatement = PrepareStatement(db, totalRecordCountQuery); - - if (!isReturningZeroItems) + if (EnableJoinUserData(query)) { - using (var statement = itemQueryStatement) - { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + 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) - { - list.Add(item); - } - } + foreach (var row in statement.ExecuteQuery()) + { + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + if (item is not null) + { + list.Add(item); } - - LogQueryTime("GetItems.ItemQuery", itemQuery, now); } + } + } - now = DateTime.UtcNow; - if (query.EnableTotalRecordCount) + if (query.EnableTotalRecordCount) + { + using (new QueryTimeLogger(Logger, totalRecordCountQuery, "GetItems.TotalRecordCount")) + using (var statement = PrepareStatement(connection, totalRecordCountQuery)) + { + if (EnableJoinUserData(query)) { - using (var statement = totalRecordCountQueryStatement) - { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } - - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + statement.TryBind("@UserId", query.User.InternalId); + } - // Running this again will bind the params - GetWhereClauses(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); - result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); - } + // Running this again will bind the params + GetWhereClauses(query, statement); - LogQueryTime("GetItems.TotalRecordCount", totalRecordCountQuery, now); - } - }, ReadTransactionMode); + result.TotalRecordCount = statement.SelectScalarInt(); + } } + transaction.Commit(); + + result.StartIndex = query.StartIndex ?? 0; result.Items = list; return result; } @@ -2942,7 +2827,7 @@ namespace Emby.Server.Implementations.Data private string GetOrderByText(InternalItemsQuery query) { var orderBy = query.OrderBy; - bool hasSimilar = query.SimilarTo != null; + bool hasSimilar = query.SimilarTo is not null; bool hasSearch = !string.IsNullOrEmpty(query.SearchTerm); if (hasSimilar || hasSearch) @@ -2972,268 +2857,196 @@ 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 sortBy = MapOrderByField(i.OrderBy, query); + var sortOrder = i.SortOrder == SortOrder.Ascending ? "ASC" : "DESC"; + return sortBy + " " + sortOrder; })); } - private (string, bool) MapOrderByField(string name, InternalItemsQuery query) + private string MapOrderByField(string name, InternalItemsQuery query) { if (string.Equals(name, ItemSortBy.AirTime, StringComparison.OrdinalIgnoreCase)) { // TODO - return ("SortName", false); + return "SortName"; } - else if (string.Equals(name, ItemSortBy.Runtime, StringComparison.OrdinalIgnoreCase)) + + if (string.Equals(name, ItemSortBy.Runtime, StringComparison.OrdinalIgnoreCase)) { - return ("RuntimeTicks", false); + return "RuntimeTicks"; } - else if (string.Equals(name, ItemSortBy.Random, StringComparison.OrdinalIgnoreCase)) + + if (string.Equals(name, ItemSortBy.Random, StringComparison.OrdinalIgnoreCase)) { - return ("RANDOM()", false); + return "RANDOM()"; } - else if (string.Equals(name, ItemSortBy.DatePlayed, StringComparison.OrdinalIgnoreCase)) + + if (string.Equals(name, ItemSortBy.DatePlayed, StringComparison.OrdinalIgnoreCase)) { if (query.GroupBySeriesPresentationUniqueKey) { - return ("MAX(LastPlayedDate)", false); + return "MAX(LastPlayedDate)"; } - return ("LastPlayedDate", false); + return "LastPlayedDate"; } - else if (string.Equals(name, ItemSortBy.PlayCount, StringComparison.OrdinalIgnoreCase)) + + if (string.Equals(name, ItemSortBy.PlayCount, StringComparison.OrdinalIgnoreCase)) { - return ("PlayCount", false); + return ItemSortBy.PlayCount; } - else if (string.Equals(name, ItemSortBy.IsFavoriteOrLiked, StringComparison.OrdinalIgnoreCase)) + + 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 )"; } - else if (string.Equals(name, ItemSortBy.IsFolder, StringComparison.OrdinalIgnoreCase)) + + if (string.Equals(name, ItemSortBy.IsFolder, StringComparison.OrdinalIgnoreCase)) { - return ("IsFolder", true); + return ItemSortBy.IsFolder; } - else if (string.Equals(name, ItemSortBy.IsPlayed, StringComparison.OrdinalIgnoreCase)) + + if (string.Equals(name, ItemSortBy.IsPlayed, StringComparison.OrdinalIgnoreCase)) { - return ("played", true); + return "played"; } - else if (string.Equals(name, ItemSortBy.IsUnplayed, StringComparison.OrdinalIgnoreCase)) + + if (string.Equals(name, ItemSortBy.IsUnplayed, StringComparison.OrdinalIgnoreCase)) { - return ("played", false); + return "played"; } - else if (string.Equals(name, ItemSortBy.DateLastContentAdded, StringComparison.OrdinalIgnoreCase)) + + if (string.Equals(name, ItemSortBy.DateLastContentAdded, StringComparison.OrdinalIgnoreCase)) { - return ("DateLastMediaAdded", false); + return "DateLastMediaAdded"; } - else if (string.Equals(name, ItemSortBy.Artist, StringComparison.OrdinalIgnoreCase)) + + 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)"; } - else if (string.Equals(name, ItemSortBy.AlbumArtist, StringComparison.OrdinalIgnoreCase)) + + 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)"; } - else if (string.Equals(name, ItemSortBy.OfficialRating, StringComparison.OrdinalIgnoreCase)) + + if (string.Equals(name, ItemSortBy.OfficialRating, StringComparison.OrdinalIgnoreCase)) { - return ("InheritedParentalRatingValue", false); + return "InheritedParentalRatingValue"; } - else if (string.Equals(name, ItemSortBy.Studio, StringComparison.OrdinalIgnoreCase)) + + 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)"; } - else if (string.Equals(name, ItemSortBy.SeriesDatePlayed, StringComparison.OrdinalIgnoreCase)) + + 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)"; } - else if (string.Equals(name, ItemSortBy.SeriesSortName, StringComparison.OrdinalIgnoreCase)) + + if (string.Equals(name, ItemSortBy.SeriesSortName, StringComparison.OrdinalIgnoreCase)) { - return ("SeriesName", false); + return "SeriesName"; } - return (name, false); - } - - public List<Guid> GetItemIdsList(InternalItemsQuery query) - { - if (query == null) + if (string.Equals(name, ItemSortBy.AiredEpisodeOrder, StringComparison.OrdinalIgnoreCase)) { - throw new ArgumentNullException(nameof(query)); + return ItemSortBy.AiredEpisodeOrder; } - CheckDisposed(); - - var now = DateTime.UtcNow; - - 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) + if (string.Equals(name, ItemSortBy.Album, StringComparison.OrdinalIgnoreCase)) { - commandTextBuilder.Append(" where ") - .AppendJoin(" AND ", whereClauses); + return ItemSortBy.Album; } - commandTextBuilder.Append(GetGroupBy(query)) - .Append(GetOrderByText(query)); - - if (query.Limit.HasValue || query.StartIndex.HasValue) + if (string.Equals(name, ItemSortBy.DateCreated, StringComparison.OrdinalIgnoreCase)) { - 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); - } + return ItemSortBy.DateCreated; } - var commandText = commandTextBuilder.ToString(); - var list = new List<Guid>(); - using (var connection = GetConnection(true)) + if (string.Equals(name, ItemSortBy.PremiereDate, StringComparison.OrdinalIgnoreCase)) { - 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[0].ReadGuidFromBlob()); - } - } + return ItemSortBy.PremiereDate; } - LogQueryTime("GetItemList", commandText, now); - return list; - } - - public List<Tuple<Guid, string>> GetItemIdsWithPath(InternalItemsQuery query) - { - if (query == null) + if (string.Equals(name, ItemSortBy.StartDate, StringComparison.OrdinalIgnoreCase)) { - throw new ArgumentNullException(nameof(query)); + return ItemSortBy.StartDate; } - CheckDisposed(); - - var now = DateTime.UtcNow; - - var columns = new List<string> { "guid", "path" }; - SetFinalColumnsToSelect(query, columns); - var commandText = "select " + string.Join(',', columns) + FromText; - - var whereClauses = GetWhereClauses(query, null); - if (whereClauses.Count != 0) + if (string.Equals(name, ItemSortBy.Name, StringComparison.OrdinalIgnoreCase)) { - commandText += " where " + string.Join(" AND ", whereClauses); + return ItemSortBy.Name; } - commandText += GetGroupBy(query) - + GetOrderByText(query); - - if (query.Limit.HasValue || query.StartIndex.HasValue) + if (string.Equals(name, ItemSortBy.CommunityRating, StringComparison.OrdinalIgnoreCase)) { - var offset = query.StartIndex ?? 0; - - if (query.Limit.HasValue || offset > 0) - { - commandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); - } + return ItemSortBy.CommunityRating; + } - if (offset > 0) - { - commandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); - } + if (string.Equals(name, ItemSortBy.ProductionYear, StringComparison.OrdinalIgnoreCase)) + { + return ItemSortBy.ProductionYear; } - var list = new List<Tuple<Guid, string>>(); - using (var connection = GetConnection(true)) + if (string.Equals(name, ItemSortBy.CriticRating, StringComparison.OrdinalIgnoreCase)) { - using (var statement = PrepareStatement(connection, commandText)) - { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + return ItemSortBy.CriticRating; + } - // Running this again will bind the params - GetWhereClauses(query, statement); + if (string.Equals(name, ItemSortBy.VideoBitRate, StringComparison.OrdinalIgnoreCase)) + { + return ItemSortBy.VideoBitRate; + } - foreach (var row in statement.ExecuteQuery()) - { - var id = row.GetGuid(0); + if (string.Equals(name, ItemSortBy.ParentIndexNumber, StringComparison.OrdinalIgnoreCase)) + { + return ItemSortBy.ParentIndexNumber; + } - row.TryGetString(1, out var path); + if (string.Equals(name, ItemSortBy.IndexNumber, StringComparison.OrdinalIgnoreCase)) + { + return ItemSortBy.IndexNumber; + } - list.Add(new Tuple<Guid, string>(id, path)); - } - } + if (string.Equals(name, ItemSortBy.SimilarityScore, StringComparison.OrdinalIgnoreCase)) + { + return ItemSortBy.SimilarityScore; } - LogQueryTime("GetItemIdsWithPath", commandText, now); + if (string.Equals(name, ItemSortBy.SearchScore, StringComparison.OrdinalIgnoreCase)) + { + return ItemSortBy.SearchScore; + } - return list; + // Unknown SortBy, just sort by the SortName. + return ItemSortBy.SortName; } - public QueryResult<Guid> GetItemIds(InternalItemsQuery query) + public List<Guid> GetItemIdsList(InternalItemsQuery query) { - if (query == null) - { - throw new ArgumentNullException(nameof(query)); - } + ArgumentNullException.ThrowIfNull(query); CheckDisposed(); - if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0)) - { - var returnList = GetItemIdsList(query); - return new QueryResult<Guid> - { - Items = returnList, - TotalRecordCount = returnList.Count - }; - } - - var now = DateTime.UtcNow; - var columns = new List<string> { "guid" }; SetFinalColumnsToSelect(query, columns); - var commandText = "select " - + string.Join(',', columns) - + FromText - + GetJoinUserDataText(query); + 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); + } - var whereText = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses); - - commandText += whereText - + GetGroupBy(query) - + GetOrderByText(query); + commandTextBuilder.Append(GetGroupBy(query)) + .Append(GetOrderByText(query)); if (query.Limit.HasValue || query.StartIndex.HasValue) { @@ -3241,105 +3054,41 @@ namespace Emby.Server.Implementations.Data if (query.Limit.HasValue || offset > 0) { - commandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + commandTextBuilder.Append(" LIMIT ") + .Append(query.Limit ?? int.MaxValue); } if (offset > 0) { - commandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); + commandTextBuilder.Append(" OFFSET ") + .Append(offset); } } - var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; - - var statementTexts = new List<string>(); - if (!isReturningZeroItems) - { - statementTexts.Add(commandText); - } - - if (query.EnableTotalRecordCount) + var commandText = commandTextBuilder.ToString(); + var list = new List<Guid>(); + using (new QueryTimeLogger(Logger, commandText)) + using (var connection = GetConnection()) + using (var statement = PrepareStatement(connection, commandText)) { - commandText = string.Empty; - - List<string> columnsToSelect; - if (EnableGroupByPresentationUniqueKey(query)) - { - columnsToSelect = new List<string> { "count (distinct PresentationUniqueKey)" }; - } - else if (query.GroupBySeriesPresentationUniqueKey) + if (EnableJoinUserData(query)) { - columnsToSelect = new List<string> { "count (distinct SeriesPresentationUniqueKey)" }; - } - else - { - columnsToSelect = new List<string> { "count (guid)" }; + statement.TryBind("@UserId", query.User.InternalId); } - SetFinalColumnsToSelect(query, columnsToSelect); - commandText += " select " + string.Join(',', columnsToSelect) + FromText; + BindSimilarParams(query, statement); + BindSearchParams(query, statement); - commandText += GetJoinUserDataText(query) - + whereText; - statementTexts.Add(commandText); - } + // Running this again will bind the params + GetWhereClauses(query, statement); - var list = new List<Guid>(); - var result = new QueryResult<Guid>(); - using (var connection = GetConnection(true)) - { - connection.RunInTransaction( - db => + foreach (var row in statement.ExecuteQuery()) { - var statements = PrepareAll(db, statementTexts); - - if (!isReturningZeroItems) - { - using (var statement = statements[0]) - { - 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[0].ReadGuidFromBlob()); - } - } - } - - if (query.EnableTotalRecordCount) - { - using (var statement = statements[statements.Length - 1]) - { - 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); - - result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); - } - } - }, ReadTransactionMode); + list.Add(row.GetGuid(0)); + } } - LogQueryTime("GetItemIds", commandText, now); - - result.Items = list; - return result; + return list; } private bool IsAlphaNumeric(string str) @@ -3360,11 +3109,6 @@ namespace Emby.Server.Implementations.Data return true; } - private bool IsValidType(string value) - { - return IsAlphaNumeric(value); - } - private bool IsValidMediaType(string value) { return IsAlphaNumeric(value); @@ -3375,7 +3119,8 @@ namespace Emby.Server.Implementations.Data return IsAlphaNumeric(value); } - private List<string> GetWhereClauses(InternalItemsQuery query, IStatement statement) +#nullable enable + private List<string> GetWhereClauses(InternalItemsQuery query, SqliteCommand? statement) { if (query.IsResumable ?? false) { @@ -3449,8 +3194,8 @@ namespace Emby.Server.Implementations.Data if (query.IsMovie == true) { if (query.IncludeItemTypes.Length == 0 - || query.IncludeItemTypes.Contains(nameof(Movie)) - || query.IncludeItemTypes.Contains(nameof(Trailer))) + || query.IncludeItemTypes.Contains(BaseItemKind.Movie) + || query.IncludeItemTypes.Contains(BaseItemKind.Trailer)) { whereClauses.Add("(IsMovie is null OR IsMovie=@IsMovie)"); } @@ -3509,7 +3254,7 @@ namespace Emby.Server.Implementations.Data } } - if (query.SimilarTo != null && query.MinSimilarityScore > 0) + if (query.SimilarTo is not null && query.MinSimilarityScore > 0) { whereClauses.Add("SimilarityScore > " + (query.MinSimilarityScore - 1).ToString(CultureInfo.InvariantCulture)); } @@ -3525,31 +3270,81 @@ namespace Emby.Server.Implementations.Data statement?.TryBind("@IsFolder", query.IsFolder); } - var includeTypes = query.IncludeItemTypes.Select(MapIncludeItemTypes).Where(x => x != null).ToArray(); + var includeTypes = query.IncludeItemTypes; // Only specify excluded types if no included types are specified - if (includeTypes.Length == 0) + if (query.IncludeItemTypes.Length == 0) { - var excludeTypes = query.ExcludeItemTypes.Select(MapIncludeItemTypes).Where(x => x != null).ToArray(); + var excludeTypes = query.ExcludeItemTypes; if (excludeTypes.Length == 1) { - whereClauses.Add("type<>@type"); - statement?.TryBind("@type", excludeTypes[0]); + if (_baseItemKindNames.TryGetValue(excludeTypes[0], out var excludeTypeName)) + { + whereClauses.Add("type<>@type"); + statement?.TryBind("@type", excludeTypeName); + } + else + { + Logger.LogWarning("Undefined BaseItemKind to Type mapping: {BaseItemKind}", excludeTypes[0]); + } } else if (excludeTypes.Length > 1) { - var inClause = string.Join(',', excludeTypes.Select(i => "'" + i + "'")); - whereClauses.Add($"type not in ({inClause})"); + var whereBuilder = new StringBuilder("type not in ("); + foreach (var excludeType in excludeTypes) + { + if (_baseItemKindNames.TryGetValue(excludeType, out var baseItemKindName)) + { + whereBuilder + .Append('\'') + .Append(baseItemKindName) + .Append("',"); + } + else + { + Logger.LogWarning("Undefined BaseItemKind to Type mapping: {BaseItemKind}", excludeType); + } + } + + // Remove trailing comma. + whereBuilder.Length--; + whereBuilder.Append(')'); + whereClauses.Add(whereBuilder.ToString()); } } else if (includeTypes.Length == 1) { - whereClauses.Add("type=@type"); - statement?.TryBind("@type", includeTypes[0]); + if (_baseItemKindNames.TryGetValue(includeTypes[0], out var includeTypeName)) + { + whereClauses.Add("type=@type"); + statement?.TryBind("@type", includeTypeName); + } + else + { + Logger.LogWarning("Undefined BaseItemKind to Type mapping: {BaseItemKind}", includeTypes[0]); + } } else if (includeTypes.Length > 1) { - var inClause = string.Join(',', includeTypes.Select(i => "'" + i + "'")); - whereClauses.Add($"type in ({inClause})"); + var whereBuilder = new StringBuilder("type in ("); + foreach (var includeType in includeTypes) + { + if (_baseItemKindNames.TryGetValue(includeType, out var baseItemKindName)) + { + whereBuilder + .Append('\'') + .Append(baseItemKindName) + .Append("',"); + } + else + { + Logger.LogWarning("Undefined BaseItemKind to Type mapping: {BaseItemKind}", includeType); + } + } + + // Remove trailing comma. + whereBuilder.Length--; + whereBuilder.Append(')'); + whereClauses.Add(whereBuilder.ToString()); } if (query.ChannelIds.Count == 1) @@ -3563,7 +3358,7 @@ namespace Emby.Server.Implementations.Data whereClauses.Add($"ChannelId in ({inClause})"); } - if (!query.ParentId.Equals(Guid.Empty)) + if (!query.ParentId.Equals(default)) { whereClauses.Add("ParentId=@ParentId"); statement?.TryBind("@ParentId", query.ParentId); @@ -3593,6 +3388,13 @@ namespace Emby.Server.Implementations.Data statement?.TryBind("@MinIndexNumber", query.MinIndexNumber.Value); } + if (query.MinParentAndIndexNumber.HasValue) + { + whereClauses.Add("((ParentIndexNumber=@MinParentAndIndexNumberParent and IndexNumber>=@MinParentAndIndexNumberIndex) or ParentIndexNumber>@MinParentAndIndexNumberParent)"); + statement?.TryBind("@MinParentAndIndexNumberParent", query.MinParentAndIndexNumber.Value.ParentIndexNumber); + statement?.TryBind("@MinParentAndIndexNumberIndex", query.MinParentAndIndexNumber.Value.IndexNumber); + } + if (query.MinDateCreated.HasValue) { whereClauses.Add("DateCreated>=@MinDateCreated"); @@ -3698,7 +3500,6 @@ namespace Emby.Server.Implementations.Data statement?.TryBind(paramName, "%" + trailerTypes[i] + "%"); } - // Remove last " OR " clauseBuilder.Length -= Or.Length; clauseBuilder.Append(')'); @@ -3739,14 +3540,12 @@ namespace Emby.Server.Implementations.Data .Append(paramName) .Append("))) OR "); - if (statement != null) + if (statement is not null) { - query.PersonIds[i].TryWriteBytes(idBytes); - statement.TryBind(paramName, idBytes); + statement.TryBind(paramName, query.PersonIds[i]); } } - // Remove last " OR " clauseBuilder.Length -= Or.Length; clauseBuilder.Append(')'); @@ -3790,10 +3589,9 @@ namespace Emby.Server.Implementations.Data if (!string.IsNullOrWhiteSpace(nameContains)) { whereClauses.Add("(CleanName like @NameContains or OriginalTitle like @NameContains)"); - if (statement != null) + if (statement is not null) { nameContains = FixUnicodeChars(nameContains); - statement.TryBind("@NameContains", "%" + GetCleanValue(nameContains) + "%"); } } @@ -3873,7 +3671,7 @@ namespace Emby.Server.Implementations.Data if (query.IsPlayed.HasValue) { // We should probably figure this out for all folders, but for right now, this is the only place where we need it - if (query.IncludeItemTypes.Length == 1 && string.Equals(query.IncludeItemTypes[0], nameof(Series), StringComparison.OrdinalIgnoreCase)) + if (query.IncludeItemTypes.Length == 1 && query.IncludeItemTypes[0] == BaseItemKind.Series) { if (query.IsPlayed.Value) { @@ -3914,257 +3712,271 @@ namespace Emby.Server.Implementations.Data if (query.ArtistIds.Length > 0) { - var clauses = new List<string>(); - var index = 0; - foreach (var artistId in query.ArtistIds) + clauseBuilder.Append('('); + for (var i = 0; i < query.ArtistIds.Length; i++) { - var paramName = "@ArtistIds" + index; - - clauses.Add("(guid in (select itemid from itemvalues where CleanValue = (select CleanName from TypedBaseItems where guid=" + paramName + ") and Type<=1))"); - if (statement != null) - { - statement.TryBind(paramName, artistId.ToByteArray()); - } - - index++; + clauseBuilder.Append("(guid in (select itemid from ItemValues where CleanValue = (select CleanName from TypedBaseItems where guid=@ArtistIds") + .Append(i) + .Append(") and Type<=1)) OR "); + statement?.TryBind("@ArtistIds" + i, query.ArtistIds[i]); } - var clause = "(" + string.Join(" OR ", clauses) + ")"; - whereClauses.Add(clause); + clauseBuilder.Length -= Or.Length; + whereClauses.Add(clauseBuilder.Append(')').ToString()); + clauseBuilder.Length = 0; } if (query.AlbumArtistIds.Length > 0) { - var clauses = new List<string>(); - var index = 0; - foreach (var artistId in query.AlbumArtistIds) + clauseBuilder.Append('('); + for (var i = 0; i < query.AlbumArtistIds.Length; i++) { - var paramName = "@ArtistIds" + index; - - clauses.Add("(guid in (select itemid from itemvalues where CleanValue = (select CleanName from TypedBaseItems where guid=" + paramName + ") and Type=1))"); - if (statement != null) - { - statement.TryBind(paramName, artistId.ToByteArray()); - } - - index++; + clauseBuilder.Append("(guid in (select itemid from ItemValues where CleanValue = (select CleanName from TypedBaseItems where guid=@ArtistIds") + .Append(i) + .Append(") and Type=1)) OR "); + statement?.TryBind("@ArtistIds" + i, query.AlbumArtistIds[i]); } - var clause = "(" + string.Join(" OR ", clauses) + ")"; - whereClauses.Add(clause); + clauseBuilder.Length -= Or.Length; + whereClauses.Add(clauseBuilder.Append(')').ToString()); + clauseBuilder.Length = 0; } if (query.ContributingArtistIds.Length > 0) { - var clauses = new List<string>(); - var index = 0; - foreach (var artistId in query.ContributingArtistIds) + clauseBuilder.Append('('); + for (var i = 0; i < query.ContributingArtistIds.Length; i++) { - var paramName = "@ArtistIds" + index; - - clauses.Add("((select CleanName from TypedBaseItems where guid=" + paramName + ") in (select CleanValue from itemvalues where ItemId=Guid and Type=0) AND (select CleanName from TypedBaseItems where guid=" + paramName + ") not in (select CleanValue from itemvalues where ItemId=Guid and Type=1))"); - if (statement != null) - { - statement.TryBind(paramName, artistId.ToByteArray()); - } - - index++; + clauseBuilder.Append("((select CleanName from TypedBaseItems where guid=@ArtistIds") + .Append(i) + .Append(") in (select CleanValue from ItemValues where ItemId=Guid and Type=0) AND (select CleanName from TypedBaseItems where guid=@ArtistIds") + .Append(i) + .Append(") not in (select CleanValue from ItemValues where ItemId=Guid and Type=1)) OR "); + statement?.TryBind("@ArtistIds" + i, query.ContributingArtistIds[i]); } - var clause = "(" + string.Join(" OR ", clauses) + ")"; - whereClauses.Add(clause); + clauseBuilder.Length -= Or.Length; + whereClauses.Add(clauseBuilder.Append(')').ToString()); + clauseBuilder.Length = 0; } if (query.AlbumIds.Length > 0) { - var clauses = new List<string>(); - var index = 0; - foreach (var albumId in query.AlbumIds) + clauseBuilder.Append('('); + for (var i = 0; i < query.AlbumIds.Length; i++) { - var paramName = "@AlbumIds" + index; - - clauses.Add("Album in (select Name from typedbaseitems where guid=" + paramName + ")"); - if (statement != null) - { - statement.TryBind(paramName, albumId.ToByteArray()); - } - - index++; + clauseBuilder.Append("Album in (select Name from typedbaseitems where guid=@AlbumIds") + .Append(i) + .Append(") OR "); + statement?.TryBind("@AlbumIds" + i, query.AlbumIds[i]); } - var clause = "(" + string.Join(" OR ", clauses) + ")"; - whereClauses.Add(clause); + clauseBuilder.Length -= Or.Length; + whereClauses.Add(clauseBuilder.Append(')').ToString()); + clauseBuilder.Length = 0; } if (query.ExcludeArtistIds.Length > 0) { - var clauses = new List<string>(); - var index = 0; - foreach (var artistId in query.ExcludeArtistIds) + clauseBuilder.Append('('); + for (var i = 0; i < query.ExcludeArtistIds.Length; i++) { - var paramName = "@ExcludeArtistId" + index; - - clauses.Add("(guid not in (select itemid from itemvalues where CleanValue = (select CleanName from TypedBaseItems where guid=" + paramName + ") and Type<=1))"); - if (statement != null) - { - statement.TryBind(paramName, artistId.ToByteArray()); - } - - index++; + clauseBuilder.Append("(guid not in (select itemid from ItemValues where CleanValue = (select CleanName from TypedBaseItems where guid=@ExcludeArtistId") + .Append(i) + .Append(") and Type<=1)) OR "); + statement?.TryBind("@ExcludeArtistId" + i, query.ExcludeArtistIds[i]); } - var clause = "(" + string.Join(" OR ", clauses) + ")"; - whereClauses.Add(clause); + clauseBuilder.Length -= Or.Length; + whereClauses.Add(clauseBuilder.Append(')').ToString()); + clauseBuilder.Length = 0; } if (query.GenreIds.Count > 0) { - var clauses = new List<string>(); - var index = 0; - foreach (var genreId in query.GenreIds) + clauseBuilder.Append('('); + for (var i = 0; i < query.GenreIds.Count; i++) { - var paramName = "@GenreId" + index; - - clauses.Add("(guid in (select itemid from itemvalues where CleanValue = (select CleanName from TypedBaseItems where guid=" + paramName + ") and Type=2))"); - if (statement != null) - { - statement.TryBind(paramName, genreId.ToByteArray()); - } - - index++; + clauseBuilder.Append("(guid in (select itemid from ItemValues where CleanValue = (select CleanName from TypedBaseItems where guid=@GenreId") + .Append(i) + .Append(") and Type=2)) OR "); + statement?.TryBind("@GenreId" + i, query.GenreIds[i]); } - var clause = "(" + string.Join(" OR ", clauses) + ")"; - whereClauses.Add(clause); + clauseBuilder.Length -= Or.Length; + whereClauses.Add(clauseBuilder.Append(')').ToString()); + clauseBuilder.Length = 0; } if (query.Genres.Count > 0) { - var clauses = new List<string>(); - var index = 0; - foreach (var item in query.Genres) + clauseBuilder.Append('('); + for (var i = 0; i < query.Genres.Count; i++) { - clauses.Add("@Genre" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=2)"); - if (statement != null) - { - statement.TryBind("@Genre" + index, GetCleanValue(item)); - } - - index++; + clauseBuilder.Append("@Genre") + .Append(i) + .Append(" in (select CleanValue from ItemValues where ItemId=Guid and Type=2) OR "); + statement?.TryBind("@Genre" + i, GetCleanValue(query.Genres[i])); } - var clause = "(" + string.Join(" OR ", clauses) + ")"; - whereClauses.Add(clause); + clauseBuilder.Length -= Or.Length; + whereClauses.Add(clauseBuilder.Append(')').ToString()); + clauseBuilder.Length = 0; } if (tags.Count > 0) { - var clauses = new List<string>(); - var index = 0; - foreach (var item in tags) + clauseBuilder.Append('('); + for (var i = 0; i < tags.Count; i++) { - clauses.Add("@Tag" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=4)"); - if (statement != null) - { - statement.TryBind("@Tag" + index, GetCleanValue(item)); - } - - index++; + clauseBuilder.Append("@Tag") + .Append(i) + .Append(" in (select CleanValue from ItemValues where ItemId=Guid and Type=4) OR "); + statement?.TryBind("@Tag" + i, GetCleanValue(tags[i])); } - var clause = "(" + string.Join(" OR ", clauses) + ")"; - whereClauses.Add(clause); + clauseBuilder.Length -= Or.Length; + whereClauses.Add(clauseBuilder.Append(')').ToString()); + clauseBuilder.Length = 0; } if (excludeTags.Count > 0) { - var clauses = new List<string>(); - var index = 0; - foreach (var item in excludeTags) + clauseBuilder.Append('('); + for (var i = 0; i < excludeTags.Count; i++) { - clauses.Add("@ExcludeTag" + index + " not in (select CleanValue from itemvalues where ItemId=Guid and Type=4)"); - if (statement != null) - { - statement.TryBind("@ExcludeTag" + index, GetCleanValue(item)); - } - - index++; + clauseBuilder.Append("@ExcludeTag") + .Append(i) + .Append(" not in (select CleanValue from ItemValues where ItemId=Guid and Type=4) OR "); + statement?.TryBind("@ExcludeTag" + i, GetCleanValue(excludeTags[i])); } - var clause = "(" + string.Join(" OR ", clauses) + ")"; - whereClauses.Add(clause); + clauseBuilder.Length -= Or.Length; + whereClauses.Add(clauseBuilder.Append(')').ToString()); + clauseBuilder.Length = 0; } if (query.StudioIds.Length > 0) { - var clauses = new List<string>(); - var index = 0; - foreach (var studioId in query.StudioIds) + clauseBuilder.Append('('); + for (var i = 0; i < query.StudioIds.Length; i++) { - var paramName = "@StudioId" + index; - - clauses.Add("(guid in (select itemid from itemvalues where CleanValue = (select CleanName from TypedBaseItems where guid=" + paramName + ") and Type=3))"); - - if (statement != null) - { - statement.TryBind(paramName, studioId.ToByteArray()); - } - - index++; + clauseBuilder.Append("(guid in (select itemid from ItemValues where CleanValue = (select CleanName from TypedBaseItems where guid=@StudioId") + .Append(i) + .Append(") and Type=3)) OR "); + statement?.TryBind("@StudioId" + i, query.StudioIds[i]); } - var clause = "(" + string.Join(" OR ", clauses) + ")"; - whereClauses.Add(clause); + clauseBuilder.Length -= Or.Length; + whereClauses.Add(clauseBuilder.Append(')').ToString()); + clauseBuilder.Length = 0; } if (query.OfficialRatings.Length > 0) { - var clauses = new List<string>(); - var index = 0; - foreach (var item in query.OfficialRatings) + clauseBuilder.Append('('); + for (var i = 0; i < query.OfficialRatings.Length; i++) { - clauses.Add("OfficialRating=@OfficialRating" + index); - if (statement != null) - { - statement.TryBind("@OfficialRating" + index, item); - } - - index++; + clauseBuilder.Append("OfficialRating=@OfficialRating").Append(i).Append(Or); + statement?.TryBind("@OfficialRating" + i, query.OfficialRatings[i]); } - var clause = "(" + string.Join(" OR ", clauses) + ")"; - whereClauses.Add(clause); + clauseBuilder.Length -= Or.Length; + whereClauses.Add(clauseBuilder.Append(')').ToString()); + clauseBuilder.Length = 0; } - if (query.MinParentalRating.HasValue) + clauseBuilder.Append('('); + if (query.HasParentalRating ?? false) { - whereClauses.Add("InheritedParentalRatingValue>=@MinParentalRating"); - if (statement != null) + clauseBuilder.Append("InheritedParentalRatingValue not null"); + if (query.MinParentalRating.HasValue) { - statement.TryBind("@MinParentalRating", query.MinParentalRating.Value); + clauseBuilder.Append(" AND InheritedParentalRatingValue >= @MinParentalRating"); + statement?.TryBind("@MinParentalRating", query.MinParentalRating.Value); } - } - if (query.MaxParentalRating.HasValue) - { - whereClauses.Add("InheritedParentalRatingValue<=@MaxParentalRating"); - if (statement != null) + if (query.MaxParentalRating.HasValue) { - statement.TryBind("@MaxParentalRating", query.MaxParentalRating.Value); + clauseBuilder.Append(" AND InheritedParentalRatingValue <= @MaxParentalRating"); + statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value); } } - - if (query.HasParentalRating.HasValue) + else if (query.BlockUnratedItems.Length > 0) { - if (query.HasParentalRating.Value) + const string ParamName = "@UnratedType"; + clauseBuilder.Append("(InheritedParentalRatingValue is null AND UnratedType not in ("); + + for (int i = 0; i < query.BlockUnratedItems.Length; i++) { - whereClauses.Add("InheritedParentalRatingValue > 0"); + clauseBuilder.Append(ParamName).Append(i).Append(','); + statement?.TryBind(ParamName + i, query.BlockUnratedItems[i].ToString()); } - else + + // Remove trailing comma + clauseBuilder.Length--; + clauseBuilder.Append("))"); + + if (query.MinParentalRating.HasValue || query.MaxParentalRating.HasValue) { - whereClauses.Add("InheritedParentalRatingValue = 0"); + clauseBuilder.Append(" OR ("); + } + + if (query.MinParentalRating.HasValue) + { + clauseBuilder.Append("InheritedParentalRatingValue >= @MinParentalRating"); + statement?.TryBind("@MinParentalRating", query.MinParentalRating.Value); + } + + if (query.MaxParentalRating.HasValue) + { + if (query.MinParentalRating.HasValue) + { + clauseBuilder.Append(" AND "); + } + + clauseBuilder.Append("InheritedParentalRatingValue <= @MaxParentalRating"); + statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value); + } + + if (query.MinParentalRating.HasValue || query.MaxParentalRating.HasValue) + { + clauseBuilder.Append(')'); + } + + if (!(query.MinParentalRating.HasValue || query.MaxParentalRating.HasValue)) + { + clauseBuilder.Append(" OR InheritedParentalRatingValue not null"); } } + else if (query.MinParentalRating.HasValue) + { + clauseBuilder.Append("InheritedParentalRatingValue is null OR (InheritedParentalRatingValue >= @MinParentalRating"); + statement?.TryBind("@MinParentalRating", query.MinParentalRating.Value); + + if (query.MaxParentalRating.HasValue) + { + clauseBuilder.Append(" AND InheritedParentalRatingValue <= @MaxParentalRating"); + statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value); + } + + clauseBuilder.Append(')'); + } + else if (query.MaxParentalRating.HasValue) + { + clauseBuilder.Append("InheritedParentalRatingValue is null OR InheritedParentalRatingValue <= @MaxParentalRating"); + statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value); + } + else if (!query.HasParentalRating ?? false) + { + clauseBuilder.Append("InheritedParentalRatingValue is null"); + } + + if (clauseBuilder.Length > 1) + { + whereClauses.Add(clauseBuilder.Append(')').ToString()); + clauseBuilder.Length = 0; + } if (query.HasOfficialRating.HasValue) { @@ -4205,37 +4017,25 @@ namespace Emby.Server.Implementations.Data if (!string.IsNullOrWhiteSpace(query.HasNoAudioTrackWithLanguage)) { whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Audio' and MediaStreams.Language=@HasNoAudioTrackWithLanguage limit 1) is null)"); - if (statement != null) - { - statement.TryBind("@HasNoAudioTrackWithLanguage", query.HasNoAudioTrackWithLanguage); - } + statement?.TryBind("@HasNoAudioTrackWithLanguage", query.HasNoAudioTrackWithLanguage); } if (!string.IsNullOrWhiteSpace(query.HasNoInternalSubtitleTrackWithLanguage)) { whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' and MediaStreams.IsExternal=0 and MediaStreams.Language=@HasNoInternalSubtitleTrackWithLanguage limit 1) is null)"); - if (statement != null) - { - statement.TryBind("@HasNoInternalSubtitleTrackWithLanguage", query.HasNoInternalSubtitleTrackWithLanguage); - } + statement?.TryBind("@HasNoInternalSubtitleTrackWithLanguage", query.HasNoInternalSubtitleTrackWithLanguage); } if (!string.IsNullOrWhiteSpace(query.HasNoExternalSubtitleTrackWithLanguage)) { whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' and MediaStreams.IsExternal=1 and MediaStreams.Language=@HasNoExternalSubtitleTrackWithLanguage limit 1) is null)"); - if (statement != null) - { - statement.TryBind("@HasNoExternalSubtitleTrackWithLanguage", query.HasNoExternalSubtitleTrackWithLanguage); - } + statement?.TryBind("@HasNoExternalSubtitleTrackWithLanguage", query.HasNoExternalSubtitleTrackWithLanguage); } if (!string.IsNullOrWhiteSpace(query.HasNoSubtitleTrackWithLanguage)) { whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' and MediaStreams.Language=@HasNoSubtitleTrackWithLanguage limit 1) is null)"); - if (statement != null) - { - statement.TryBind("@HasNoSubtitleTrackWithLanguage", query.HasNoSubtitleTrackWithLanguage); - } + statement?.TryBind("@HasNoSubtitleTrackWithLanguage", query.HasNoSubtitleTrackWithLanguage); } if (query.HasSubtitles.HasValue) @@ -4285,15 +4085,11 @@ namespace Emby.Server.Implementations.Data if (query.Years.Length == 1) { whereClauses.Add("ProductionYear=@Years"); - if (statement != null) - { - statement.TryBind("@Years", query.Years[0].ToString(CultureInfo.InvariantCulture)); - } + statement?.TryBind("@Years", query.Years[0].ToString(CultureInfo.InvariantCulture)); } else if (query.Years.Length > 1) { var val = string.Join(',', query.Years); - whereClauses.Add("ProductionYear in (" + val + ")"); } @@ -4301,10 +4097,7 @@ namespace Emby.Server.Implementations.Data if (isVirtualItem.HasValue) { whereClauses.Add("IsVirtualItem=@IsVirtualItem"); - if (statement != null) - { - statement.TryBind("@IsVirtualItem", isVirtualItem.Value); - } + statement?.TryBind("@IsVirtualItem", isVirtualItem.Value); } if (query.IsSpecialSeason.HasValue) @@ -4335,31 +4128,22 @@ namespace Emby.Server.Implementations.Data if (queryMediaTypes.Length == 1) { whereClauses.Add("MediaType=@MediaTypes"); - if (statement != null) - { - statement.TryBind("@MediaTypes", queryMediaTypes[0]); - } + statement?.TryBind("@MediaTypes", queryMediaTypes[0]); } else if (queryMediaTypes.Length > 1) { var val = string.Join(',', queryMediaTypes.Select(i => "'" + i + "'")); - whereClauses.Add("MediaType in (" + val + ")"); } if (query.ItemIds.Length > 0) { var includeIds = new List<string>(); - var index = 0; foreach (var id in query.ItemIds) { includeIds.Add("Guid = @IncludeId" + index); - if (statement != null) - { - statement.TryBind("@IncludeId" + index, id); - } - + statement?.TryBind("@IncludeId" + index, id); index++; } @@ -4369,41 +4153,32 @@ namespace Emby.Server.Implementations.Data if (query.ExcludeItemIds.Length > 0) { var excludeIds = new List<string>(); - var index = 0; foreach (var id in query.ExcludeItemIds) { excludeIds.Add("Guid <> @ExcludeId" + index); - if (statement != null) - { - statement.TryBind("@ExcludeId" + index, id); - } - + statement?.TryBind("@ExcludeId" + index, id); index++; } whereClauses.Add(string.Join(" AND ", excludeIds)); } - if (query.ExcludeProviderIds != null && query.ExcludeProviderIds.Count > 0) + if (query.ExcludeProviderIds is not null && query.ExcludeProviderIds.Count > 0) { var excludeIds = new List<string>(); var index = 0; foreach (var pair in query.ExcludeProviderIds) { - if (string.Equals(pair.Key, MetadataProvider.TmdbCollection.ToString(), StringComparison.OrdinalIgnoreCase)) + if (string.Equals(pair.Key, nameof(MetadataProvider.TmdbCollection), StringComparison.OrdinalIgnoreCase)) { continue; } var paramName = "@ExcludeProviderId" + index; excludeIds.Add("(ProviderIds is null or ProviderIds not like " + paramName + ")"); - if (statement != null) - { - statement.TryBind(paramName, "%" + pair.Key + "=" + pair.Value + "%"); - } - + statement?.TryBind(paramName, "%" + pair.Key + "=" + pair.Value + "%"); index++; break; @@ -4415,20 +4190,20 @@ namespace Emby.Server.Implementations.Data } } - if (query.HasAnyProviderId != null && query.HasAnyProviderId.Count > 0) + if (query.HasAnyProviderId is not null && query.HasAnyProviderId.Count > 0) { var hasProviderIds = new List<string>(); var index = 0; foreach (var pair in query.HasAnyProviderId) { - if (string.Equals(pair.Key, MetadataProvider.TmdbCollection.ToString(), StringComparison.OrdinalIgnoreCase)) + if (string.Equals(pair.Key, nameof(MetadataProvider.TmdbCollection), StringComparison.OrdinalIgnoreCase)) { continue; } // TODO this seems to be an idea for a better schema where ProviderIds are their own table - // buut this is not implemented + // but this is not implemented // hasProviderIds.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = '" + pair.Key + "'), '') <> " + paramName + ")"); // TODO this is a really BAD way to do it since the pair: @@ -4442,11 +4217,7 @@ namespace Emby.Server.Implementations.Data hasProviderIds.Add("ProviderIds like " + paramName); // this replaces the placeholder with a value, here: %key=val% - if (statement != null) - { - statement.TryBind(paramName, "%" + pair.Key + "=" + pair.Value + "%"); - } - + statement?.TryBind(paramName, "%" + pair.Key + "=" + pair.Value + "%"); index++; break; @@ -4523,11 +4294,7 @@ namespace Emby.Server.Implementations.Data if (query.AncestorIds.Length == 1) { whereClauses.Add("Guid in (select itemId from AncestorIds where AncestorId=@AncestorId)"); - - if (statement != null) - { - statement.TryBind("@AncestorId", query.AncestorIds[0]); - } + statement?.TryBind("@AncestorId", query.AncestorIds[0]); } if (query.AncestorIds.Length > 1) @@ -4540,55 +4307,47 @@ namespace Emby.Server.Implementations.Data { var inClause = "select guid from TypedBaseItems where PresentationUniqueKey=@AncestorWithPresentationUniqueKey"; whereClauses.Add(string.Format(CultureInfo.InvariantCulture, "Guid in (select itemId from AncestorIds where AncestorId in ({0}))", inClause)); - if (statement != null) - { - statement.TryBind("@AncestorWithPresentationUniqueKey", query.AncestorWithPresentationUniqueKey); - } + statement?.TryBind("@AncestorWithPresentationUniqueKey", query.AncestorWithPresentationUniqueKey); } if (!string.IsNullOrWhiteSpace(query.SeriesPresentationUniqueKey)) { whereClauses.Add("SeriesPresentationUniqueKey=@SeriesPresentationUniqueKey"); - - if (statement != null) - { - statement.TryBind("@SeriesPresentationUniqueKey", query.SeriesPresentationUniqueKey); - } + statement?.TryBind("@SeriesPresentationUniqueKey", query.SeriesPresentationUniqueKey); } - if (query.BlockUnratedItems.Length == 1) + if (query.ExcludeInheritedTags.Length > 0) { - whereClauses.Add("(InheritedParentalRatingValue > 0 or UnratedType <> @UnratedType)"); - if (statement != null) + var paramName = "@ExcludeInheritedTags"; + if (statement is null) { - statement.TryBind("@UnratedType", query.BlockUnratedItems[0].ToString()); + int index = 0; + 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 + { + for (int index = 0; index < query.ExcludeInheritedTags.Length; index++) + { + statement.TryBind(paramName + index, GetCleanValue(query.ExcludeInheritedTags[index])); + } } } - if (query.BlockUnratedItems.Length > 1) - { - var inClause = string.Join(',', query.BlockUnratedItems.Select(i => "'" + i.ToString() + "'")); - whereClauses.Add( - string.Format( - CultureInfo.InvariantCulture, - "(InheritedParentalRatingValue > 0 or UnratedType not in ({0}))", - inClause)); - } - - if (query.ExcludeInheritedTags.Length > 0) + if (query.IncludeInheritedTags.Length > 0) { - var paramName = "@ExcludeInheritedTags"; - if (statement == null) + var paramName = "@IncludeInheritedTags"; + if (statement is null) { int index = 0; - string excludedTags = string.Join(',', query.ExcludeInheritedTags.Select(t => paramName + index++)); - whereClauses.Add("((select CleanValue from itemvalues where ItemId=Guid and Type=6 and cleanvalue in (" + excludedTags + ")) is null)"); + string includedTags = string.Join(',', query.IncludeInheritedTags.Select(_ => paramName + index++)); + whereClauses.Add("((select CleanValue from ItemValues where ItemId=Guid and Type=6 and cleanvalue in (" + includedTags + ")) is not null)"); } else { - for (int index = 0; index < query.ExcludeInheritedTags.Length; index++) + for (int index = 0; index < query.IncludeInheritedTags.Length; index++) { - statement.TryBind(paramName + index, GetCleanValue(query.ExcludeInheritedTags[index])); + statement.TryBind(paramName + index, GetCleanValue(query.IncludeInheritedTags[index])); } } } @@ -4719,31 +4478,32 @@ namespace Emby.Server.Implementations.Data provider); } +#nullable disable private List<string> GetItemByNameTypesInQuery(InternalItemsQuery query) { var list = new List<string>(); - if (IsTypeInQuery(nameof(Person), query)) + if (IsTypeInQuery(BaseItemKind.Person, query)) { list.Add(typeof(Person).FullName); } - if (IsTypeInQuery(nameof(Genre), query)) + if (IsTypeInQuery(BaseItemKind.Genre, query)) { list.Add(typeof(Genre).FullName); } - if (IsTypeInQuery(nameof(MusicGenre), query)) + if (IsTypeInQuery(BaseItemKind.MusicGenre, query)) { list.Add(typeof(MusicGenre).FullName); } - if (IsTypeInQuery(nameof(MusicArtist), query)) + if (IsTypeInQuery(BaseItemKind.MusicArtist, query)) { list.Add(typeof(MusicArtist).FullName); } - if (IsTypeInQuery(nameof(Studio), query)) + if (IsTypeInQuery(BaseItemKind.Studio, query)) { list.Add(typeof(Studio).FullName); } @@ -4751,14 +4511,14 @@ namespace Emby.Server.Implementations.Data return list; } - private bool IsTypeInQuery(string type, InternalItemsQuery query) + private bool IsTypeInQuery(BaseItemKind type, InternalItemsQuery query) { - if (query.ExcludeItemTypes.Contains(type, StringComparer.OrdinalIgnoreCase)) + if (query.ExcludeItemTypes.Contains(type)) { return false; } - return query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(type, StringComparer.OrdinalIgnoreCase); + return query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(type); } private string GetCleanValue(string value) @@ -4788,7 +4548,7 @@ namespace Emby.Server.Implementations.Data return false; } - if (query.User == null) + if (query.User is null) { return false; } @@ -4798,166 +4558,75 @@ namespace Emby.Server.Implementations.Data return true; } - if (query.IncludeItemTypes.Contains(nameof(Episode), StringComparer.OrdinalIgnoreCase) - || query.IncludeItemTypes.Contains(nameof(Video), StringComparer.OrdinalIgnoreCase) - || query.IncludeItemTypes.Contains(nameof(Movie), StringComparer.OrdinalIgnoreCase) - || query.IncludeItemTypes.Contains(nameof(MusicVideo), StringComparer.OrdinalIgnoreCase) - || query.IncludeItemTypes.Contains(nameof(Series), StringComparer.OrdinalIgnoreCase) - || query.IncludeItemTypes.Contains(nameof(Season), StringComparer.OrdinalIgnoreCase)) - { - return true; - } - - return false; + 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 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) - }; - public void UpdateInheritedValues() { - string sql = string.Join( - ';', - new string[] - { - "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 + 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()) - { - 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 ; - } - - dict["Program"] = typeof(LiveTvProgram).FullName; - dict["TvChannel"] = typeof(LiveTvChannel).FullName; - - return dict; - } - - // Not crazy about having this all the way down here, but at least it's in one place - private readonly Dictionary<string, string> _types = GetTypeMapDictionary(); - - private string MapIncludeItemTypes(string value) - { - if (_types.TryGetValue(value, out string result)) - { - return result; - } - - if (IsValidType(value)) - { - return value; - } - - Logger.LogWarning("Unknown item type: {ItemType}", value); - return null; +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(); } public void DeleteItem(Guid id) { - if (id == Guid.Empty) + if (id.Equals(default)) { throw new ArgumentNullException(nameof(id)); } CheckDisposed(); - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - var idBlob = id.ToByteArray(); + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + // Delete people + ExecuteWithSingleParam(connection, "delete from People where ItemId=@Id", id); - // Delete people - ExecuteWithSingleParam(db, "delete from People where ItemId=@Id", idBlob); + // Delete chapters + ExecuteWithSingleParam(connection, "delete from " + ChaptersTableName + " where ItemId=@Id", id); - // Delete chapters - ExecuteWithSingleParam(db, "delete from " + ChaptersTableName + " where ItemId=@Id", idBlob); + // Delete media streams + ExecuteWithSingleParam(connection, "delete from mediastreams where ItemId=@Id", id); - // Delete media streams - ExecuteWithSingleParam(db, "delete from mediastreams where ItemId=@Id", idBlob); + // Delete ancestors + ExecuteWithSingleParam(connection, "delete from AncestorIds where ItemId=@Id", id); - // Delete ancestors - ExecuteWithSingleParam(db, "delete from AncestorIds where ItemId=@Id", idBlob); + // Delete item values + ExecuteWithSingleParam(connection, "delete from ItemValues where ItemId=@Id", id); - // Delete item values - ExecuteWithSingleParam(db, "delete from ItemValues where ItemId=@Id", idBlob); + // Delete the item + ExecuteWithSingleParam(connection, "delete from TypedBaseItems where guid=@Id", id); - // Delete the item - ExecuteWithSingleParam(db, "delete from TypedBaseItems where guid=@Id", idBlob); - }, TransactionMode); - } + transaction.Commit(); } - private void ExecuteWithSingleParam(IDatabaseConnection db, string query, ReadOnlySpan<byte> value) + private void ExecuteWithSingleParam(SqliteConnection db, string query, Guid value) { using (var statement = PrepareStatement(db, query)) { statement.TryBind("@Id", value); - statement.MoveNext(); + statement.ExecuteNonQuery(); } } public List<string> GetPeopleNames(InternalPeopleQuery query) { - if (query == null) - { - throw new ArgumentNullException(nameof(query)); - } + ArgumentNullException.ThrowIfNull(query); CheckDisposed(); @@ -4967,7 +4636,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type if (whereClauses.Count != 0) { - commandText.Append(" where ").Append(string.Join(" AND ", whereClauses)); + commandText.Append(" where ").AppendJoin(" AND ", whereClauses); } commandText.Append(" order by ListOrder"); @@ -4977,73 +4646,65 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type commandText.Append(" LIMIT ").Append(query.Limit); } - using (var connection = GetConnection(true)) + var list = new List<string>(); + using (var connection = GetConnection()) + using (var statement = PrepareStatement(connection, commandText.ToString())) { - var list = new List<string>(); - using (var statement = PrepareStatement(connection, commandText.ToString())) - { - // Run this again to bind the params - GetPeopleWhereClauses(query, statement); + // Run this again to bind the params + GetPeopleWhereClauses(query, statement); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(row.GetString(0)); - } + foreach (var row in statement.ExecuteQuery()) + { + list.Add(row.GetString(0)); } - - return list; } + + return list; } public List<PersonInfo> GetPeople(InternalPeopleQuery query) { - if (query == null) - { - throw new ArgumentNullException(nameof(query)); - } + ArgumentNullException.ThrowIfNull(query); CheckDisposed(); - var commandText = "select ItemId, Name, Role, PersonType, SortOrder from People p"; + StringBuilder commandText = new StringBuilder("select ItemId, Name, Role, PersonType, SortOrder from People p"); var whereClauses = GetPeopleWhereClauses(query, null); if (whereClauses.Count != 0) { - commandText += " where " + string.Join(" AND ", whereClauses); + commandText.Append(" where ").AppendJoin(" AND ", whereClauses); } - commandText += " order by ListOrder"; + commandText.Append(" order by ListOrder"); if (query.Limit > 0) { - commandText += " LIMIT " + query.Limit; + commandText.Append(" LIMIT ").Append(query.Limit); } - using (var connection = GetConnection(true)) + var list = new List<PersonInfo>(); + using (var connection = GetConnection()) + using (var statement = PrepareStatement(connection, commandText.ToString())) { - var list = new List<PersonInfo>(); + // Run this again to bind the params + GetPeopleWhereClauses(query, statement); - using (var statement = PrepareStatement(connection, commandText)) + foreach (var row in statement.ExecuteQuery()) { - // Run this again to bind the params - GetPeopleWhereClauses(query, statement); - - foreach (var row in statement.ExecuteQuery()) - { - list.Add(GetPerson(row)); - } + list.Add(GetPerson(row)); } - - return list; } + + return list; } - private List<string> GetPeopleWhereClauses(InternalPeopleQuery query, IStatement statement) + private List<string> GetPeopleWhereClauses(InternalPeopleQuery query, SqliteCommand statement) { var whereClauses = new List<string>(); - if (query.User != null && query.IsFavorite.HasValue) + if (query.User is not null && query.IsFavorite.HasValue) { whereClauses.Add(@"p.Name IN ( SELECT Name FROM TypedBaseItems WHERE UserDataKey IN ( @@ -5051,18 +4712,19 @@ 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.Equals(Guid.Empty)) + if (!query.ItemId.Equals(default)) { whereClauses.Add("ItemId=@ItemId"); - statement?.TryBind("@ItemId", query.ItemId.ToByteArray()); + statement?.TryBind("@ItemId", query.ItemId); } - if (!query.AppearsInItemId.Equals(Guid.Empty)) + if (!query.AppearsInItemId.Equals(default)) { whereClauses.Add("p.Name in (Select Name from People where ItemId=@AppearsInItemId)"); - statement?.TryBind("@AppearsInItemId", query.AppearsInItemId.ToByteArray()); + statement?.TryBind("@AppearsInItemId", query.AppearsInItemId); } var queryPersonTypes = query.PersonTypes.Where(IsValidPersonType).ToList(); @@ -5105,35 +4767,23 @@ AND Type = @InternalPersonType)"); statement?.TryBind("@NameContains", "%" + query.NameContains + "%"); } - if (query.User != null) - { - statement?.TryBind("@UserId", query.User.InternalId); - } - return whereClauses; } - private void UpdateAncestors(Guid itemId, List<Guid> ancestorIds, IDatabaseConnection db, IStatement deleteAncestorsStatement) + private void UpdateAncestors(Guid itemId, List<Guid> ancestorIds, SqliteConnection db, SqliteCommand deleteAncestorsStatement) { - if (itemId.Equals(Guid.Empty)) + if (itemId.Equals(default)) { throw new ArgumentNullException(nameof(itemId)); } - if (ancestorIds == null) - { - throw new ArgumentNullException(nameof(ancestorIds)); - } + ArgumentNullException.ThrowIfNull(ancestorIds); CheckDisposed(); - Span<byte> itemIdBlob = stackalloc byte[16]; - itemId.TryWriteBytes(itemIdBlob); - // First delete - deleteAncestorsStatement.Reset(); - deleteAncestorsStatement.TryBind("@ItemId", itemIdBlob); - deleteAncestorsStatement.MoveNext(); + deleteAncestorsStatement.TryBind("@ItemId", itemId); + deleteAncestorsStatement.ExecuteNonQuery(); if (ancestorIds.Count == 0) { @@ -5150,55 +4800,53 @@ AND Type = @InternalPersonType)"); i.ToString(CultureInfo.InvariantCulture)); } - // Remove last , + // Remove trailing comma insertText.Length--; using (var statement = PrepareStatement(db, insertText.ToString())) { - statement.TryBind("@ItemId", itemIdBlob); + statement.TryBind("@ItemId", itemId); for (var i = 0; i < ancestorIds.Count; i++) { var index = i.ToString(CultureInfo.InvariantCulture); var ancestorId = ancestorIds[i]; - ancestorId.TryWriteBytes(itemIdBlob); - statement.TryBind("@AncestorId" + index, itemIdBlob); + statement.TryBind("@AncestorId" + index, ancestorId); statement.TryBind("@AncestorIdText" + index, ancestorId.ToString("N", CultureInfo.InvariantCulture)); } - statement.Reset(); - statement.MoveNext(); + statement.ExecuteNonQuery(); } } - 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); } @@ -5245,8 +4893,6 @@ AND Type = @InternalPersonType)"); { CheckDisposed(); - var now = DateTime.UtcNow; - var stringBuilder = new StringBuilder("Select Value From ItemValues where Type", 128); if (itemValueTypes.Length == 1) { @@ -5278,7 +4924,8 @@ AND Type = @InternalPersonType)"); var commandText = stringBuilder.ToString(); var list = new List<string>(); - using (var connection = GetConnection(true)) + using (new QueryTimeLogger(Logger, commandText)) + using (var connection = GetConnection()) using (var statement = PrepareStatement(connection, commandText)) { foreach (var row in statement.ExecuteQuery()) @@ -5290,16 +4937,12 @@ AND Type = @InternalPersonType)"); } } - LogQueryTime("GetItemValueNames", commandText, now); 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) - { - throw new ArgumentNullException(nameof(query)); - } + ArgumentNullException.ThrowIfNull(query); if (!query.Limit.HasValue) { @@ -5308,8 +4951,6 @@ AND Type = @InternalPersonType)"); CheckDisposed(); - var now = DateTime.UtcNow; - var typeClause = itemValueTypes.Length == 1 ? ("Type=" + itemValueTypes[0]) : ("Type in (" + string.Join(',', itemValueTypes) + ")"); @@ -5350,7 +4991,7 @@ AND Type = @InternalPersonType)"); stringBuilder.Clear(); } - List<string> columns = _retriveItemColumns.ToList(); + 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)) { @@ -5430,7 +5071,7 @@ AND Type = @InternalPersonType)"); .Append(" group by PresentationUniqueKey"); if (query.OrderBy.Count != 0 - || query.SimilarTo != null + || query.SimilarTo is not null || !string.IsNullOrEmpty(query.SearchTerm)) { stringBuilder.Append(GetOrderByText(query)); @@ -5483,92 +5124,90 @@ AND Type = @InternalPersonType)"); var list = new List<(BaseItem, ItemCounts)>(); var result = new QueryResult<(BaseItem, ItemCounts)>(); - using (var connection = GetConnection(true)) + using (new QueryTimeLogger(Logger, commandText)) + using (var connection = GetConnection()) + using (var transaction = connection.BeginTransaction(deferred: true)) { - connection.RunInTransaction( - db => + if (!isReturningZeroItems) + { + using (var statement = PrepareStatement(connection, commandText)) { - if (!isReturningZeroItems) + statement.TryBind("@SelectType", returnType); + if (EnableJoinUserData(query)) { - using (var statement = PrepareStatement(db, commandText)) - { - statement.TryBind("@SelectType", returnType); - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } - - if (typeSubQuery != 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); - if (item != null) - { - var countStartColumn = columns.Count - 1; - - list.Add((item, GetItemCounts(row, countStartColumn, typesToCount))); - } - } - } + statement.TryBind("@UserId", query.User.InternalId); } - if (query.EnableTotalRecordCount) + 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()) { - using (var statement = PrepareStatement(db, countText)) + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + if (item is not null) { - statement.TryBind("@SelectType", returnType); - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } - - if (typeSubQuery != null) - { - GetWhereClauses(typeSubQuery, null); - } - - BindSimilarParams(query, statement); - BindSearchParams(query, statement); - GetWhereClauses(innerQuery, statement); - GetWhereClauses(outerQuery, statement); - - result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); + var countStartColumn = columns.Count - 1; + + list.Add((item, GetItemCounts(row, countStartColumn, typesToCount))); } } - }, - ReadTransactionMode); - } + } + } + + 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); - LogQueryTime("GetItemValues", commandText, now); + 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(IReadOnlyList<ResultSetValue> reader, int countStartColumn, string[] typesToCount) + private static ItemCounts GetItemCounts(SqliteDataReader reader, int countStartColumn, BaseItemKind[] typesToCount) { var counts = new ItemCounts(); @@ -5619,7 +5258,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)>(); @@ -5641,32 +5280,32 @@ AND Type = @InternalPersonType)"); list.AddRange(inheritedTags.Select(i => (6, i))); + // Remove all invalid values. + list.RemoveAll(i => string.IsNullOrEmpty(i.Item2)); + return list; } - private void UpdateItemValues(Guid itemId, List<(int, string)> values, IDatabaseConnection db) + private void UpdateItemValues(Guid itemId, List<(int MagicNumber, string Value)> values, SqliteConnection db) { - if (itemId.Equals(Guid.Empty)) + if (itemId.Equals(default)) { throw new ArgumentNullException(nameof(itemId)); } - if (values == null) - { - throw new ArgumentNullException(nameof(values)); - } + ArgumentNullException.ThrowIfNull(values); CheckDisposed(); - var guidBlob = itemId.ToByteArray(); - // First delete - db.Execute("delete from ItemValues where ItemId=@Id", guidBlob); + using var command = db.PrepareStatement("delete from ItemValues where ItemId=@Id"); + command.TryBind("@Id", itemId); + command.ExecuteNonQuery(); - InsertItemValues(guidBlob, values, db); + InsertItemValues(itemId, values, db); } - private void InsertItemValues(byte[] idBlob, List<(int, string)> values, IDatabaseConnection db) + private void InsertItemValues(Guid id, List<(int MagicNumber, string Value)> values, SqliteConnection db) { const int Limit = 100; var startIndex = 0; @@ -5685,12 +5324,12 @@ AND Type = @InternalPersonType)"); i); } - // Remove last comma + // Remove trailing comma insertText.Length--; using (var statement = PrepareStatement(db, insertText.ToString())) { - statement.TryBind("@ItemId", idBlob); + statement.TryBind("@ItemId", id); for (var i = startIndex; i < endIndex; i++) { @@ -5698,7 +5337,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)) @@ -5706,13 +5345,12 @@ 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)); } - statement.Reset(); - statement.MoveNext(); + statement.ExecuteNonQuery(); } startIndex += Limit; @@ -5722,34 +5360,29 @@ AND Type = @InternalPersonType)"); public void UpdatePeople(Guid itemId, List<PersonInfo> people) { - if (itemId.Equals(Guid.Empty)) + if (itemId.Equals(default)) { throw new ArgumentNullException(nameof(itemId)); } - if (people == null) - { - throw new ArgumentNullException(nameof(people)); - } + ArgumentNullException.ThrowIfNull(people); CheckDisposed(); - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - var itemIdBlob = itemId.ToByteArray(); + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + // First delete chapters + using var command = connection.CreateCommand(); + command.CommandText = "delete from People where ItemId=@ItemId"; + command.TryBind("@ItemId", itemId); + command.ExecuteNonQuery(); - // First delete chapters - db.Execute("delete from People where ItemId=@ItemId", itemIdBlob); + InsertPeople(itemId, people, connection); - InsertPeople(itemIdBlob, people, db); - }, TransactionMode); - } + transaction.Commit(); } - private void InsertPeople(byte[] idBlob, List<PersonInfo> people, IDatabaseConnection db) + private void InsertPeople(Guid id, List<PersonInfo> people, SqliteConnection db) { const int Limit = 100; var startIndex = 0; @@ -5768,12 +5401,12 @@ AND Type = @InternalPersonType)"); i.ToString(CultureInfo.InvariantCulture)); } - // Remove last comma + // Remove trailing comma insertText.Length--; using (var statement = PrepareStatement(db, insertText.ToString())) { - statement.TryBind("@ItemId", idBlob); + statement.TryBind("@ItemId", id); for (var i = startIndex; i < endIndex; i++) { @@ -5783,15 +5416,14 @@ AND Type = @InternalPersonType)"); statement.TryBind("@Name" + index, person.Name); statement.TryBind("@Role" + index, person.Role); - statement.TryBind("@PersonType" + index, person.Type); + statement.TryBind("@PersonType" + index, person.Type.ToString()); statement.TryBind("@SortOrder" + index, person.SortOrder); statement.TryBind("@ListOrder" + index, listIndex); listIndex++; } - statement.Reset(); - statement.MoveNext(); + statement.ExecuteNonQuery(); } startIndex += Limit; @@ -5799,7 +5431,7 @@ AND Type = @InternalPersonType)"); } } - private PersonInfo GetPerson(IReadOnlyList<ResultSetValue> reader) + private PersonInfo GetPerson(SqliteDataReader reader) { var item = new PersonInfo { @@ -5812,9 +5444,10 @@ AND Type = @InternalPersonType)"); item.Role = role; } - if (reader.TryGetString(3, out var type)) + if (reader.TryGetString(3, out var type) + && Enum.TryParse(type, true, out PersonKind personKind)) { - item.Type = type; + item.Type = personKind; } if (reader.TryGetInt32(4, out var sortOrder)) @@ -5829,10 +5462,7 @@ AND Type = @InternalPersonType)"); { CheckDisposed(); - if (query == null) - { - throw new ArgumentNullException(nameof(query)); - } + ArgumentNullException.ThrowIfNull(query); var cmdText = _mediaStreamSaveColumnsSelectQuery; @@ -5848,13 +5478,13 @@ AND Type = @InternalPersonType)"); cmdText += " order by StreamIndex ASC"; - using (var connection = GetConnection(true)) + using (var connection = GetConnection()) { var list = new List<MediaStream>(); using (var statement = PrepareStatement(connection, cmdText)) { - statement.TryBind("@ItemId", query.ItemId.ToByteArray()); + statement.TryBind("@ItemId", query.ItemId); if (query.Type.HasValue) { @@ -5876,38 +5506,32 @@ AND Type = @InternalPersonType)"); } } - public void SaveMediaStreams(Guid id, List<MediaStream> streams, CancellationToken cancellationToken) + public void SaveMediaStreams(Guid id, IReadOnlyList<MediaStream> streams, CancellationToken cancellationToken) { CheckDisposed(); - if (id == Guid.Empty) + if (id.Equals(default)) { throw new ArgumentNullException(nameof(id)); } - if (streams == null) - { - throw new ArgumentNullException(nameof(streams)); - } + ArgumentNullException.ThrowIfNull(streams); cancellationToken.ThrowIfCancellationRequested(); - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - var itemIdBlob = id.ToByteArray(); + 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(); - // First delete chapters - db.Execute("delete from mediastreams where ItemId=@ItemId", itemIdBlob); + InsertMediaStreams(id, streams, connection); - InsertMediaStreams(itemIdBlob, streams, db); - }, TransactionMode); - } + transaction.Commit(); } - private void InsertMediaStreams(byte[] idBlob, List<MediaStream> streams, IDatabaseConnection db) + private void InsertMediaStreams(Guid id, IReadOnlyList<MediaStream> streams, SqliteConnection db) { const int Limit = 10; var startIndex = 0; @@ -5939,7 +5563,7 @@ AND Type = @InternalPersonType)"); using (var statement = PrepareStatement(db, insertText.ToString())) { - statement.TryBind("@ItemId", idBlob); + statement.TryBind("@ItemId", id); for (var i = startIndex; i < endIndex; i++) { @@ -5975,6 +5599,7 @@ AND Type = @InternalPersonType)"); 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); @@ -5990,10 +5615,20 @@ AND Type = @InternalPersonType)"); 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.Reset(); - statement.MoveNext(); + statement.ExecuteNonQuery(); } startIndex += Limit; @@ -6002,19 +5637,18 @@ AND Type = @InternalPersonType)"); } /// <summary> - /// Gets the chapter. + /// Gets the media stream. /// </summary> /// <param name="reader">The reader.</param> - /// <returns>ChapterInfo.</returns> - private MediaStream GetMediaStream(IReadOnlyList<ResultSetValue> reader) + /// <returns>MediaStream.</returns> + private MediaStream GetMediaStream(SqliteDataReader reader) { var item = new MediaStream { - Index = reader[1].ToInt() + Index = reader.GetInt32(1), + Type = Enum.Parse<MediaStreamType>(reader.GetString(2), true) }; - item.Type = Enum.Parse<MediaStreamType>(reader[2].ToString(), true); - if (reader.TryGetString(3, out var codec)) { item.Codec = codec; @@ -6161,11 +5795,55 @@ AND Type = @InternalPersonType)"); 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 (item.Type == MediaStreamType.Subtitle) { item.LocalizedUndefined = _localization.GetLocalizedString("Undefined"); item.LocalizedDefault = _localization.GetLocalizedString("Default"); item.LocalizedForced = _localization.GetLocalizedString("Forced"); + item.LocalizedExternal = _localization.GetLocalizedString("External"); + item.LocalizedHearingImpaired = _localization.GetLocalizedString("HearingImpaired"); } return item; @@ -6175,10 +5853,7 @@ AND Type = @InternalPersonType)"); { CheckDisposed(); - if (query == null) - { - throw new ArgumentNullException(nameof(query)); - } + ArgumentNullException.ThrowIfNull(query); var cmdText = _mediaAttachmentSaveColumnsSelectQuery; @@ -6190,10 +5865,10 @@ AND Type = @InternalPersonType)"); cmdText += " order by AttachmentIndex ASC"; var list = new List<MediaAttachment>(); - using (var connection = GetConnection(true)) + using (var connection = GetConnection()) using (var statement = PrepareStatement(connection, cmdText)) { - statement.TryBind("@ItemId", query.ItemId.ToByteArray()); + statement.TryBind("@ItemId", query.ItemId); if (query.Index.HasValue) { @@ -6215,36 +5890,32 @@ AND Type = @InternalPersonType)"); CancellationToken cancellationToken) { CheckDisposed(); - if (id == Guid.Empty) + if (id.Equals(default)) { throw new ArgumentException("Guid can't be empty.", nameof(id)); } - if (attachments == null) - { - throw new ArgumentNullException(nameof(attachments)); - } + ArgumentNullException.ThrowIfNull(attachments); cancellationToken.ThrowIfCancellationRequested(); using (var connection = GetConnection()) + using (var transaction = connection.BeginTransaction()) + using (var command = connection.PrepareStatement("delete from mediaattachments where ItemId=@ItemId")) { - connection.RunInTransaction( - db => - { - var itemIdBlob = id.ToByteArray(); + command.TryBind("@ItemId", id); + command.ExecuteNonQuery(); - db.Execute("delete from mediaattachments where ItemId=@ItemId", itemIdBlob); + InsertMediaAttachments(id, attachments, connection, cancellationToken); - InsertMediaAttachments(itemIdBlob, attachments, db, cancellationToken); - }, TransactionMode); + transaction.Commit(); } } private void InsertMediaAttachments( - byte[] idBlob, + Guid id, IReadOnlyList<MediaAttachment> attachments, - IDatabaseConnection db, + SqliteConnection db, CancellationToken cancellationToken) { const int InsertAtOnce = 10; @@ -6256,14 +5927,13 @@ AND Type = @InternalPersonType)"); for (var i = startIndex; i < endIndex; i++) { - var index = i.ToString(CultureInfo.InvariantCulture); insertText.Append("(@ItemId, "); foreach (var column in _mediaAttachmentSaveColumns.Skip(1)) { insertText.Append('@') .Append(column) - .Append(index) + .Append(i) .Append(','); } @@ -6278,7 +5948,7 @@ AND Type = @InternalPersonType)"); using (var statement = PrepareStatement(db, insertText.ToString())) { - statement.TryBind("@ItemId", idBlob); + statement.TryBind("@ItemId", id); for (var i = startIndex; i < endIndex; i++) { @@ -6294,8 +5964,7 @@ AND Type = @InternalPersonType)"); statement.TryBind("@MIMEType" + index, attachment.MimeType); } - statement.Reset(); - statement.MoveNext(); + statement.ExecuteNonQuery(); } insertText.Length = _mediaAttachmentInsertPrefix.Length; @@ -6307,11 +5976,11 @@ AND Type = @InternalPersonType)"); /// </summary> /// <param name="reader">The reader.</param> /// <returns>MediaAttachment.</returns> - private MediaAttachment GetMediaAttachment(IReadOnlyList<ResultSetValue> reader) + private MediaAttachment GetMediaAttachment(SqliteDataReader reader) { var item = new MediaAttachment { - Index = reader[1].ToInt() + Index = reader.GetInt32(1) }; if (reader.TryGetString(2, out var codec)) @@ -6341,5 +6010,48 @@ AND Type = @InternalPersonType)"); return item; } + +#nullable enable + + private readonly struct QueryTimeLogger : IDisposable + { + private readonly ILogger _logger; + private readonly string _commandText; + private readonly string _methodName; + private readonly long _startTimestamp; + + public QueryTimeLogger(ILogger logger, string commandText, [CallerMemberName] string methodName = "") + { + _logger = logger; + _commandText = commandText; + _methodName = methodName; + _startTimestamp = logger.IsEnabled(LogLevel.Debug) ? Stopwatch.GetTimestamp() : -1; + } + + public void Dispose() + { + if (_startTimestamp == -1) + { + return; + } + + var elapsedMs = Stopwatch.GetElapsedTime(_startTimestamp).TotalMilliseconds; + +#if DEBUG + const int SlowThreshold = 100; +#else + const int SlowThreshold = 10; +#endif + + if (elapsedMs >= SlowThreshold) + { + _logger.LogDebug( + "{Method} query time (slow): {ElapsedMs}ms. Query: {Query}", + _methodName, + elapsedMs, + _commandText); + } + } + } } } |