diff options
Diffstat (limited to 'Emby.Server.Implementations/Data')
8 files changed, 585 insertions, 527 deletions
diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index 450688491..d05534ee7 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -4,7 +4,6 @@ using System; using System.Collections.Generic; -using System.Threading; using Jellyfin.Extensions; using Microsoft.Extensions.Logging; using SQLitePCL.pretty; @@ -27,10 +26,20 @@ namespace Emby.Server.Implementations.Data /// <summary> /// Gets or sets the path to the DB file. /// </summary> - /// <value>Path to the DB file.</value> protected string DbFilePath { get; set; } /// <summary> + /// Gets or sets the number of write connections to create. + /// </summary> + /// <value>Path to the DB file.</value> + protected int WriteConnectionsCount { get; set; } = 1; + + /// <summary> + /// Gets or sets the number of read connections to create. + /// </summary> + protected int ReadConnectionsCount { get; set; } = 1; + + /// <summary> /// Gets the logger. /// </summary> /// <value>The logger.</value> @@ -61,10 +70,22 @@ namespace Emby.Server.Implementations.Data protected virtual int? CacheSize => null; /// <summary> + /// Gets the locking mode. <see href="https://www.sqlite.org/pragma.html#pragma_locking_mode" />. + /// </summary> + protected virtual string LockingMode => "NORMAL"; + + /// <summary> /// Gets the journal mode. <see href="https://www.sqlite.org/pragma.html#pragma_journal_mode" />. /// </summary> /// <value>The journal mode.</value> - protected virtual string JournalMode => "TRUNCATE"; + protected virtual string JournalMode => "WAL"; + + /// <summary> + /// Gets the journal size limit. <see href="https://www.sqlite.org/pragma.html#pragma_journal_size_limit" />. + /// The default (-1) is overriden to prevent unconstrained WAL size, as reported by users. + /// </summary> + /// <value>The journal size limit.</value> + protected virtual int? JournalSizeLimit => 134_217_728; // 128MiB /// <summary> /// Gets the page size. @@ -77,86 +98,127 @@ namespace Emby.Server.Implementations.Data /// </summary> /// <value>The temp store mode.</value> /// <see cref="TempStoreMode"/> - protected virtual TempStoreMode TempStore => TempStoreMode.Default; + protected virtual TempStoreMode TempStore => TempStoreMode.Memory; /// <summary> /// Gets the synchronous mode. /// </summary> /// <value>The synchronous mode or null.</value> /// <see cref="SynchronousMode"/> - protected virtual SynchronousMode? Synchronous => null; + protected virtual SynchronousMode? Synchronous => SynchronousMode.Normal; /// <summary> /// Gets or sets the write lock. /// </summary> /// <value>The write lock.</value> - protected SemaphoreSlim WriteLock { get; set; } = new SemaphoreSlim(1, 1); + protected ConnectionPool WriteConnections { get; set; } /// <summary> /// Gets or sets the write connection. /// </summary> /// <value>The write connection.</value> - protected SQLiteDatabaseConnection WriteConnection { get; set; } + protected ConnectionPool ReadConnections { get; set; } - protected ManagedConnection GetConnection(bool readOnly = false) + public virtual void Initialize() { - WriteLock.Wait(); - if (WriteConnection != null) + WriteConnections = new ConnectionPool(WriteConnectionsCount, CreateWriteConnection); + ReadConnections = new ConnectionPool(ReadConnectionsCount, CreateReadConnection); + + // Configuration and pragmas can affect VACUUM so it needs to be last. + using (var connection = GetConnection()) { - return new ManagedConnection(WriteConnection, WriteLock); + connection.Execute("VACUUM"); } + } + + protected ManagedConnection GetConnection(bool readOnly = false) + => readOnly ? ReadConnections.GetConnection() : WriteConnections.GetConnection(); - WriteConnection = SQLite3.Open( + protected SQLiteDatabaseConnection CreateWriteConnection() + { + var writeConnection = SQLite3.Open( DbFilePath, DefaultConnectionFlags | ConnectionFlags.Create | ConnectionFlags.ReadWrite, null); if (CacheSize.HasValue) { - WriteConnection.Execute("PRAGMA cache_size=" + CacheSize.Value); + writeConnection.Execute("PRAGMA cache_size=" + CacheSize.Value); + } + + if (!string.IsNullOrWhiteSpace(LockingMode)) + { + writeConnection.Execute("PRAGMA locking_mode=" + LockingMode); } if (!string.IsNullOrWhiteSpace(JournalMode)) { - WriteConnection.Execute("PRAGMA journal_mode=" + JournalMode); + writeConnection.Execute("PRAGMA journal_mode=" + JournalMode); + } + + if (JournalSizeLimit.HasValue) + { + writeConnection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value); } if (Synchronous.HasValue) { - WriteConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); + writeConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); } if (PageSize.HasValue) { - WriteConnection.Execute("PRAGMA page_size=" + PageSize.Value); + writeConnection.Execute("PRAGMA page_size=" + PageSize.Value); } - WriteConnection.Execute("PRAGMA temp_store=" + (int)TempStore); - - // Configuration and pragmas can affect VACUUM so it needs to be last. - WriteConnection.Execute("VACUUM"); + writeConnection.Execute("PRAGMA temp_store=" + (int)TempStore); - return new ManagedConnection(WriteConnection, WriteLock); + return writeConnection; } - public IStatement PrepareStatement(ManagedConnection connection, string sql) - => connection.PrepareStatement(sql); + protected SQLiteDatabaseConnection CreateReadConnection() + { + var connection = SQLite3.Open( + DbFilePath, + DefaultConnectionFlags | ConnectionFlags.ReadOnly, + null); - public IStatement PrepareStatement(IDatabaseConnection connection, string sql) - => connection.PrepareStatement(sql); + if (CacheSize.HasValue) + { + connection.Execute("PRAGMA cache_size=" + CacheSize.Value); + } - public IStatement[] PrepareAll(IDatabaseConnection connection, IReadOnlyList<string> sql) - { - int len = sql.Count; - IStatement[] statements = new IStatement[len]; - for (int i = 0; i < len; i++) + if (!string.IsNullOrWhiteSpace(LockingMode)) + { + connection.Execute("PRAGMA locking_mode=" + LockingMode); + } + + if (!string.IsNullOrWhiteSpace(JournalMode)) + { + connection.Execute("PRAGMA journal_mode=" + JournalMode); + } + + if (JournalSizeLimit.HasValue) + { + connection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value); + } + + if (Synchronous.HasValue) { - statements[i] = connection.PrepareStatement(sql[i]); + connection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); } - return statements; + connection.Execute("PRAGMA temp_store=" + (int)TempStore); + + return connection; } + public IStatement PrepareStatement(ManagedConnection connection, string sql) + => connection.PrepareStatement(sql); + + public IStatement PrepareStatement(IDatabaseConnection connection, string sql) + => connection.PrepareStatement(sql); + protected bool TableExists(ManagedConnection connection, string name) { return connection.RunInTransaction( @@ -231,22 +293,10 @@ namespace Emby.Server.Implementations.Data if (dispose) { - WriteLock.Wait(); - try - { - WriteConnection?.Dispose(); - } - finally - { - WriteLock.Release(); - } - - WriteLock.Dispose(); + WriteConnections.Dispose(); + ReadConnections.Dispose(); } - WriteConnection = null; - WriteLock = null; - _disposed = true; } } diff --git a/Emby.Server.Implementations/Data/CleanDatabaseScheduledTask.cs b/Emby.Server.Implementations/Data/CleanDatabaseScheduledTask.cs index 3de9d6371..4516b89dc 100644 --- a/Emby.Server.Implementations/Data/CleanDatabaseScheduledTask.cs +++ b/Emby.Server.Implementations/Data/CleanDatabaseScheduledTask.cs @@ -44,7 +44,7 @@ namespace Emby.Server.Implementations.Data var item = _libraryManager.GetItemById(itemId); - if (item != null) + if (item is not null) { _logger.LogInformation("Cleaning item {0} type: {1} path: {2}", item.Name, item.GetType().Name, item.Path ?? string.Empty); diff --git a/Emby.Server.Implementations/Data/ConnectionPool.cs b/Emby.Server.Implementations/Data/ConnectionPool.cs new file mode 100644 index 000000000..5ea7e934f --- /dev/null +++ b/Emby.Server.Implementations/Data/ConnectionPool.cs @@ -0,0 +1,79 @@ +using System; +using System.Collections.Concurrent; +using SQLitePCL.pretty; + +namespace Emby.Server.Implementations.Data; + +/// <summary> +/// A pool of SQLite Database connections. +/// </summary> +public sealed class ConnectionPool : IDisposable +{ + private readonly BlockingCollection<SQLiteDatabaseConnection> _connections = new(); + private bool _disposed; + + /// <summary> + /// Initializes a new instance of the <see cref="ConnectionPool" /> class. + /// </summary> + /// <param name="count">The number of database connection to create.</param> + /// <param name="factory">Factory function to create the database connections.</param> + public ConnectionPool(int count, Func<SQLiteDatabaseConnection> factory) + { + for (int i = 0; i < count; i++) + { + _connections.Add(factory.Invoke()); + } + } + + /// <summary> + /// Gets a database connection from the pool if one is available, otherwise blocks. + /// </summary> + /// <returns>A database connection.</returns> + public ManagedConnection GetConnection() + { + if (_disposed) + { + ThrowObjectDisposedException(); + } + + return new ManagedConnection(_connections.Take(), this); + + static void ThrowObjectDisposedException() + { + throw new ObjectDisposedException(nameof(ConnectionPool)); + } + } + + /// <summary> + /// Return a database connection to the pool. + /// </summary> + /// <param name="connection">The database connection to return.</param> + public void Return(SQLiteDatabaseConnection connection) + { + if (_disposed) + { + connection.Dispose(); + return; + } + + _connections.Add(connection); + } + + /// <inheritdoc /> + public void Dispose() + { + if (_disposed) + { + return; + } + + foreach (var connection in _connections) + { + connection.Dispose(); + } + + _connections.Dispose(); + + _disposed = true; + } +} diff --git a/Emby.Server.Implementations/Data/ManagedConnection.cs b/Emby.Server.Implementations/Data/ManagedConnection.cs index 11e33278d..e84ed8f91 100644 --- a/Emby.Server.Implementations/Data/ManagedConnection.cs +++ b/Emby.Server.Implementations/Data/ManagedConnection.cs @@ -2,23 +2,22 @@ using System; using System.Collections.Generic; -using System.Threading; using SQLitePCL.pretty; namespace Emby.Server.Implementations.Data { public sealed class ManagedConnection : IDisposable { - private readonly SemaphoreSlim _writeLock; + private readonly ConnectionPool _pool; - private SQLiteDatabaseConnection? _db; + private SQLiteDatabaseConnection _db; private bool _disposed = false; - public ManagedConnection(SQLiteDatabaseConnection db, SemaphoreSlim writeLock) + public ManagedConnection(SQLiteDatabaseConnection db, ConnectionPool pool) { _db = db; - _writeLock = writeLock; + _pool = pool; } public IStatement PrepareStatement(string sql) @@ -73,9 +72,9 @@ namespace Emby.Server.Implementations.Data return; } - _writeLock.Release(); + _pool.Return(_db); - _db = null; // Don't dispose it + _db = null!; // Don't dispose it _disposed = true; } } diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index 736b8125d..4055b0ba1 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -253,7 +253,7 @@ namespace Emby.Server.Implementations.Data { if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) { - if (value == null) + if (value is null) { bindParam.BindNull(); } diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 371111dff..ca8f605a0 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -5,9 +5,11 @@ 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; @@ -23,6 +25,7 @@ using MediaBrowser.Controller.Entities; 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; @@ -32,6 +35,7 @@ using MediaBrowser.Model.Entities; using MediaBrowser.Model.Globalization; using MediaBrowser.Model.LiveTv; using MediaBrowser.Model.Querying; +using Microsoft.Extensions.Configuration; using Microsoft.Extensions.Logging; using SQLitePCL.pretty; @@ -47,8 +51,8 @@ namespace Emby.Server.Implementations.Data 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)"; + (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; @@ -108,6 +112,7 @@ namespace Emby.Server.Implementations.Data "PrimaryVersionId", "DateLastMediaAdded", "Album", + "LUFS", "CriticRating", "IsVirtualItem", "SeriesName", @@ -316,13 +321,15 @@ namespace Emby.Server.Implementations.Data /// <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) + IImageProcessor imageProcessor, + IConfiguration configuration) : base(logger) { _config = config; @@ -334,10 +341,13 @@ namespace Emby.Server.Implementations.Data _jsonOptions = JsonDefaults.Options; DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); + + CacheSize = configuration.GetSqliteCacheSize(); + ReadConnectionsCount = Environment.ProcessorCount * 2; } /// <inheritdoc /> - protected override int? CacheSize => 20000; + protected override int? CacheSize { get; } /// <inheritdoc /> protected override TempStoreMode TempStore => TempStoreMode.Memory; @@ -345,10 +355,10 @@ namespace Emby.Server.Implementations.Data /// <summary> /// Opens the connection to the database. /// </summary> - /// <param name="userDataRepo">The user data repository.</param> - /// <param name="userManager">The user manager.</param> - public void Initialize(SqliteUserDataRepository userDataRepo, IUserManager userManager) + 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))"; @@ -357,8 +367,6 @@ namespace Emby.Server.Implementations.Data 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))", @@ -383,39 +391,6 @@ namespace Emby.Server.Implementations.Data 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)", @@ -456,6 +431,9 @@ namespace Emby.Server.Implementations.Data // 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()) @@ -518,6 +496,7 @@ namespace Emby.Server.Implementations.Data AddColumn(db, "TypedBaseItems", "PrimaryVersionId", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "DateLastMediaAdded", "DATETIME", existingColumnNames); AddColumn(db, "TypedBaseItems", "Album", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "LUFS", "Float", existingColumnNames); AddColumn(db, "TypedBaseItems", "IsVirtualItem", "BIT", existingColumnNames); AddColumn(db, "TypedBaseItems", "SeriesName", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "UserDataKey", "Text", existingColumnNames); @@ -581,8 +560,6 @@ namespace Emby.Server.Implementations.Data connection.RunQueries(postQueries); } - - userDataRepo.Initialize(userManager, WriteLock, WriteConnection); } public void SaveImages(BaseItem item) @@ -616,7 +593,7 @@ namespace Emby.Server.Implementations.Data /// <exception cref="ArgumentNullException"> /// <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) { ArgumentNullException.ThrowIfNull(items); @@ -624,9 +601,11 @@ namespace Emby.Server.Implementations.Data 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; @@ -636,7 +615,7 @@ 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()) @@ -652,14 +631,8 @@ namespace Emby.Server.Implementations.Data private void SaveItemsInTransaction(IDatabaseConnection db, IEnumerable<(BaseItem Item, List<Guid> AncestorIds, BaseItem TopParent, string UserDataKey, List<string> InheritedTags)> tuples) { - var statements = PrepareAll(db, new string[] - { - 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) @@ -691,7 +664,7 @@ namespace Emby.Server.Implementations.Data private string GetPathToSave(string path) { - if (path == null) + if (path is null) { return null; } @@ -890,7 +863,7 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@UnratedType", item.GetBlockUnratedType().ToString()); - if (topParent == null) + if (topParent is null) { saveItemStatement.TryBindNull("@TopParentId"); } @@ -941,6 +914,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) @@ -1146,7 +1120,7 @@ namespace Emby.Server.Implementations.Data { var image = ItemImageInfoFromValueString(part); - if (image != null) + if (image is not null) { result[position++] = image; } @@ -1225,7 +1199,7 @@ 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) { @@ -1314,15 +1288,13 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); using (var connection = GetConnection(true)) + using (var statement = PrepareStatement(connection, _retrieveItemColumnsSelectQuery)) { - using (var statement = PrepareStatement(connection, _retrieveItemColumnsSelectQuery)) - { - 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()); } } @@ -1337,7 +1309,8 @@ namespace Emby.Server.Implementations.Data { return false; } - else if (type == typeof(UserRootFolder)) + + if (type == typeof(UserRootFolder)) { return false; } @@ -1347,55 +1320,68 @@ namespace Emby.Server.Implementations.Data { return false; } - else if (type == typeof(MusicArtist)) + + if (type == typeof(MusicArtist)) { return false; } - else if (type == typeof(Person)) + + if (type == typeof(Person)) { return false; } - else if (type == typeof(MusicGenre)) + + if (type == typeof(MusicGenre)) { return false; } - else if (type == typeof(Genre)) + + if (type == typeof(Genre)) { return false; } - else if (type == typeof(Studio)) + + if (type == typeof(Studio)) { return false; } - else if (type == typeof(PlaylistsFolder)) + + if (type == typeof(PlaylistsFolder)) { return false; } - else if (type == typeof(PhotoAlbum)) + + if (type == typeof(PhotoAlbum)) { return false; } - else if (type == typeof(Year)) + + if (type == typeof(Year)) { return false; } - else if (type == typeof(Book)) + + if (type == typeof(Book)) { return false; } - else if (type == typeof(LiveTvProgram)) + + if (type == typeof(LiveTvProgram)) { return false; } - else if (type == typeof(AudioBook)) + + if (type == typeof(AudioBook)) { return false; } - else if (type == typeof(Audio)) + + if (type == typeof(Audio)) { return false; } - else if (type == typeof(MusicAlbum)) + + if (type == typeof(MusicAlbum)) { return false; } @@ -1414,7 +1400,7 @@ namespace Emby.Server.Implementations.Data var type = _typeMapper.GetType(typeString); - if (type == null) + if (type is null) { return null; } @@ -1433,7 +1419,7 @@ namespace Emby.Server.Implementations.Data } } - if (item == null) + if (item is null) { try { @@ -1444,7 +1430,7 @@ namespace Emby.Server.Implementations.Data } } - if (item == null) + if (item is null) { return null; } @@ -1779,6 +1765,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; @@ -1825,7 +1816,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)) { @@ -1938,7 +1929,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)) { @@ -1986,22 +1977,19 @@ namespace Emby.Server.Implementations.Data { CheckDisposed(); + var chapters = new List<ChapterInfo>(); using (var connection = GetConnection(true)) + 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; } /// <inheritdoc /> @@ -2010,16 +1998,14 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); using (var connection = GetConnection(true)) + 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); } } @@ -2151,7 +2137,7 @@ namespace Emby.Server.Implementations.Data private static bool EnableJoinUserData(InternalItemsQuery query) { - if (query.User == null) + if (query.User is null) { return false; } @@ -2205,7 +2191,7 @@ namespace Emby.Server.Implementations.Data private bool HasProgramAttributes(InternalItemsQuery query) { - if (query.ParentType != null && _programExcludeParentTypes.Contains(query.ParentType.Value)) + if (query.ParentType is not null && _programExcludeParentTypes.Contains(query.ParentType.Value)) { return false; } @@ -2220,7 +2206,7 @@ namespace Emby.Server.Implementations.Data private bool HasServiceName(InternalItemsQuery query) { - if (query.ParentType != null && _programExcludeParentTypes.Contains(query.ParentType.Value)) + if (query.ParentType is not null && _programExcludeParentTypes.Contains(query.ParentType.Value)) { return false; } @@ -2235,7 +2221,7 @@ namespace Emby.Server.Implementations.Data private bool HasStartDate(InternalItemsQuery query) { - if (query.ParentType != null && _programExcludeParentTypes.Contains(query.ParentType.Value)) + if (query.ParentType is not null && _programExcludeParentTypes.Contains(query.ParentType.Value)) { return false; } @@ -2270,7 +2256,7 @@ namespace Emby.Server.Implementations.Data private bool HasArtistFields(InternalItemsQuery query) { - if (query.ParentType != null && _artistExcludeParentTypes.Contains(query.ParentType.Value)) + if (query.ParentType is not null && _artistExcludeParentTypes.Contains(query.ParentType.Value)) { return false; } @@ -2392,20 +2378,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) @@ -2416,6 +2406,7 @@ 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 People where ItemId=Guid and Name in (select Name from People where ItemId=@SimilarItemId))"); if (item is MusicArtist) { @@ -2456,10 +2447,12 @@ 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("+ ((Tags not null and Tags like @SearchTermContains) * 5)"); } builder.Append(") as SearchScore"); @@ -2496,7 +2489,7 @@ namespace Emby.Server.Implementations.Data { var item = query.SimilarTo; - if (item == null) + if (item is null) { return; } @@ -2517,6 +2510,11 @@ namespace Emby.Server.Implementations.Data { statement.TryBind("@SimilarItemId", item.Id); } + + if (commandText.Contains("@InheritedParentalRatingValue", StringComparison.OrdinalIgnoreCase)) + { + statement.TryBind("@InheritedParentalRatingValue", item.InheritedParentalRatingValue); + } } private string GetJoinUserDataText(InternalItemsQuery query) @@ -2556,8 +2554,6 @@ namespace Emby.Server.Implementations.Data 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) { @@ -2579,28 +2575,24 @@ namespace Emby.Server.Implementations.Data } var commandText = commandTextBuilder.ToString(); - int count; + + using (new QueryTimeLogger(Logger, commandText)) using (var connection = GetConnection(true)) + 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.ExecuteQuery().SelectScalarInt().First(); } - - LogQueryTime("GetCount", commandText, now); - return count; } public List<BaseItem> GetItemList(InternalItemsQuery query) @@ -2609,8 +2601,6 @@ namespace Emby.Server.Implementations.Data 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) { @@ -2654,61 +2644,58 @@ namespace Emby.Server.Implementations.Data var commandText = commandTextBuilder.ToString(); var items = new List<BaseItem>(); + using (new QueryTimeLogger(Logger, commandText)) using (var connection = GetConnection(true)) + 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; } @@ -2761,26 +2748,6 @@ 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) { ArgumentNullException.ThrowIfNull(query); @@ -2796,8 +2763,6 @@ namespace Emby.Server.Implementations.Data 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) { @@ -2893,12 +2858,10 @@ namespace Emby.Server.Implementations.Data connection.RunInTransaction( db => { - var itemQueryStatement = PrepareStatement(db, itemQuery); - var totalRecordCountQueryStatement = PrepareStatement(db, totalRecordCountQuery); - if (!isReturningZeroItems) { - using (var statement = itemQueryStatement) + using (new QueryTimeLogger(Logger, itemQuery, "GetItems.ItemQuery")) + using (var statement = PrepareStatement(db, itemQuery)) { if (EnableJoinUserData(query)) { @@ -2922,20 +2885,18 @@ namespace Emby.Server.Implementations.Data foreach (var row in statement.ExecuteQuery()) { var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); - if (item != null) + if (item is not null) { list.Add(item); } } } - - LogQueryTime("GetItems.ItemQuery", itemQuery, now); } - now = DateTime.UtcNow; if (query.EnableTotalRecordCount) { - using (var statement = totalRecordCountQueryStatement) + using (new QueryTimeLogger(Logger, totalRecordCountQuery, "GetItems.TotalRecordCount")) + using (var statement = PrepareStatement(db, totalRecordCountQuery)) { if (EnableJoinUserData(query)) { @@ -2950,8 +2911,6 @@ namespace Emby.Server.Implementations.Data result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); } - - LogQueryTime("GetItems.TotalRecordCount", totalRecordCountQuery, now); } }, ReadTransactionMode); @@ -2965,7 +2924,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) @@ -3154,6 +3113,11 @@ namespace Emby.Server.Implementations.Data return ItemSortBy.SimilarityScore; } + if (string.Equals(name, ItemSortBy.SearchScore, StringComparison.OrdinalIgnoreCase)) + { + return ItemSortBy.SearchScore; + } + // Unknown SortBy, just sort by the SortName. return ItemSortBy.SortName; } @@ -3164,8 +3128,6 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - var now = DateTime.UtcNow; - var columns = new List<string> { "guid" }; SetFinalColumnsToSelect(query, columns); var commandTextBuilder = new StringBuilder("select ", 256) @@ -3202,29 +3164,27 @@ namespace Emby.Server.Implementations.Data var commandText = commandTextBuilder.ToString(); var list = new List<Guid>(); + using (new QueryTimeLogger(Logger, commandText)) using (var connection = GetConnection(true)) + using (var statement = PrepareStatement(connection, commandText)) { - 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); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(row[0].ReadGuidFromBlob()); - } + foreach (var row in statement.ExecuteQuery()) + { + list.Add(row[0].ReadGuidFromBlob()); } } - LogQueryTime("GetItemList", commandText, now); return list; } @@ -3256,7 +3216,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, IStatement? statement) { if (query.IsResumable ?? false) { @@ -3390,7 +3351,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)); } @@ -3677,7 +3638,7 @@ 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); @@ -3728,10 +3689,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) + "%"); } } @@ -3857,13 +3817,8 @@ namespace Emby.Server.Implementations.Data foreach (var artistId in query.ArtistIds) { 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); - } - + statement?.TryBind(paramName, artistId); index++; } @@ -3878,13 +3833,8 @@ namespace Emby.Server.Implementations.Data foreach (var artistId in query.AlbumArtistIds) { 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); - } - + statement?.TryBind(paramName, artistId); index++; } @@ -3899,13 +3849,8 @@ namespace Emby.Server.Implementations.Data foreach (var artistId in query.ContributingArtistIds) { 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); - } - + statement?.TryBind(paramName, artistId); index++; } @@ -3920,13 +3865,8 @@ namespace Emby.Server.Implementations.Data foreach (var albumId in query.AlbumIds) { var paramName = "@AlbumIds" + index; - clauses.Add("Album in (select Name from typedbaseitems where guid=" + paramName + ")"); - if (statement != null) - { - statement.TryBind(paramName, albumId); - } - + statement?.TryBind(paramName, albumId); index++; } @@ -3941,13 +3881,8 @@ namespace Emby.Server.Implementations.Data foreach (var artistId in query.ExcludeArtistIds) { 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); - } - + statement?.TryBind(paramName, artistId); index++; } @@ -3962,13 +3897,8 @@ namespace Emby.Server.Implementations.Data foreach (var genreId in query.GenreIds) { 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); - } - + statement?.TryBind(paramName, genreId); index++; } @@ -3983,11 +3913,7 @@ namespace Emby.Server.Implementations.Data foreach (var item in query.Genres) { clauses.Add("@Genre" + index + " in (select CleanValue from ItemValues where ItemId=Guid and Type=2)"); - if (statement != null) - { - statement.TryBind("@Genre" + index, GetCleanValue(item)); - } - + statement?.TryBind("@Genre" + index, GetCleanValue(item)); index++; } @@ -4002,11 +3928,7 @@ namespace Emby.Server.Implementations.Data foreach (var item in tags) { clauses.Add("@Tag" + index + " in (select CleanValue from ItemValues where ItemId=Guid and Type=4)"); - if (statement != null) - { - statement.TryBind("@Tag" + index, GetCleanValue(item)); - } - + statement?.TryBind("@Tag" + index, GetCleanValue(item)); index++; } @@ -4021,11 +3943,7 @@ namespace Emby.Server.Implementations.Data foreach (var item in excludeTags) { 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)); - } - + statement?.TryBind("@ExcludeTag" + index, GetCleanValue(item)); index++; } @@ -4040,14 +3958,8 @@ namespace Emby.Server.Implementations.Data foreach (var studioId in query.StudioIds) { 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); - } - + statement?.TryBind(paramName, studioId); index++; } @@ -4062,11 +3974,7 @@ namespace Emby.Server.Implementations.Data foreach (var item in query.OfficialRatings) { clauses.Add("OfficialRating=@OfficialRating" + index); - if (statement != null) - { - statement.TryBind("@OfficialRating" + index, item); - } - + statement?.TryBind("@OfficialRating" + index, item); index++; } @@ -4074,35 +3982,97 @@ namespace Emby.Server.Implementations.Data whereClauses.Add(clause); } - if (query.MinParentalRating.HasValue) + var ratingClauseBuilder = new StringBuilder("("); + if (query.HasParentalRating ?? false) { - whereClauses.Add("InheritedParentalRatingValue>=@MinParentalRating"); - if (statement != null) + ratingClauseBuilder.Append("InheritedParentalRatingValue not null"); + if (query.MinParentalRating.HasValue) { - statement.TryBind("@MinParentalRating", query.MinParentalRating.Value); + ratingClauseBuilder.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); + ratingClauseBuilder.Append(" AND InheritedParentalRatingValue <= @MaxParentalRating"); + statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value); } } - - if (query.HasParentalRating.HasValue) + else if (query.BlockUnratedItems.Length > 0) { - if (query.HasParentalRating.Value) + var paramName = "@UnratedType"; + var index = 0; + string blockedUnratedItems = string.Join(',', query.BlockUnratedItems.Select(_ => paramName + index++)); + ratingClauseBuilder.Append("(InheritedParentalRatingValue is null AND UnratedType not in (" + blockedUnratedItems + "))"); + + if (statement is not null) + { + for (var ind = 0; ind < query.BlockUnratedItems.Length; ind++) + { + statement.TryBind(paramName + ind, query.BlockUnratedItems[ind].ToString()); + } + } + + if (query.MinParentalRating.HasValue || query.MaxParentalRating.HasValue) { - whereClauses.Add("InheritedParentalRatingValue > 0"); + ratingClauseBuilder.Append(" OR ("); } - else + + if (query.MinParentalRating.HasValue) + { + ratingClauseBuilder.Append("InheritedParentalRatingValue >= @MinParentalRating"); + statement?.TryBind("@MinParentalRating", query.MinParentalRating.Value); + } + + if (query.MaxParentalRating.HasValue) { - whereClauses.Add("InheritedParentalRatingValue = 0"); + if (query.MinParentalRating.HasValue) + { + ratingClauseBuilder.Append(" AND "); + } + + ratingClauseBuilder.Append("InheritedParentalRatingValue <= @MaxParentalRating"); + statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value); + } + + if (query.MinParentalRating.HasValue || query.MaxParentalRating.HasValue) + { + ratingClauseBuilder.Append(")"); + } + + if (!(query.MinParentalRating.HasValue || query.MaxParentalRating.HasValue)) + { + ratingClauseBuilder.Append(" OR InheritedParentalRatingValue not null"); } } + else if (query.MinParentalRating.HasValue) + { + ratingClauseBuilder.Append("InheritedParentalRatingValue is null OR (InheritedParentalRatingValue >= @MinParentalRating"); + statement?.TryBind("@MinParentalRating", query.MinParentalRating.Value); + + if (query.MaxParentalRating.HasValue) + { + ratingClauseBuilder.Append(" AND InheritedParentalRatingValue <= @MaxParentalRating"); + statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value); + } + + ratingClauseBuilder.Append(")"); + } + else if (query.MaxParentalRating.HasValue) + { + ratingClauseBuilder.Append("InheritedParentalRatingValue is null OR InheritedParentalRatingValue <= @MaxParentalRating"); + statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value); + } + else if (!query.HasParentalRating ?? false) + { + ratingClauseBuilder.Append("InheritedParentalRatingValue is null"); + } + + var ratingClauseString = ratingClauseBuilder.ToString(); + if (!string.Equals(ratingClauseString, "(", StringComparison.OrdinalIgnoreCase)) + { + whereClauses.Add(ratingClauseString + ")"); + } if (query.HasOfficialRating.HasValue) { @@ -4143,37 +4113,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) @@ -4223,15 +4181,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 + ")"); } @@ -4239,10 +4193,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) @@ -4273,31 +4224,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++; } @@ -4307,23 +4249,18 @@ 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>(); @@ -4337,11 +4274,7 @@ namespace Emby.Server.Implementations.Data 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; @@ -4353,7 +4286,7 @@ 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>(); @@ -4366,7 +4299,7 @@ namespace Emby.Server.Implementations.Data } // 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: @@ -4380,11 +4313,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; @@ -4461,11 +4390,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) @@ -4478,45 +4403,19 @@ 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); - } - } - - if (query.BlockUnratedItems.Length == 1) - { - whereClauses.Add("(InheritedParentalRatingValue > 0 or UnratedType <> @UnratedType)"); - if (statement != null) - { - statement.TryBind("@UnratedType", query.BlockUnratedItems[0].ToString()); - } - } - - 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)); + statement?.TryBind("@SeriesPresentationUniqueKey", query.SeriesPresentationUniqueKey); } if (query.ExcludeInheritedTags.Length > 0) { var paramName = "@ExcludeInheritedTags"; - if (statement == null) + if (statement is null) { int index = 0; string excludedTags = string.Join(',', query.ExcludeInheritedTags.Select(_ => paramName + index++)); @@ -4531,6 +4430,24 @@ namespace Emby.Server.Implementations.Data } } + if (query.IncludeInheritedTags.Length > 0) + { + var paramName = "@IncludeInheritedTags"; + if (statement is null) + { + int index = 0; + 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.IncludeInheritedTags.Length; index++) + { + statement.TryBind(paramName + index, GetCleanValue(query.IncludeInheritedTags[index])); + } + } + } + if (query.SeriesStatuses.Length > 0) { var statuses = new List<string>(); @@ -4641,6 +4558,7 @@ namespace Emby.Server.Implementations.Data return whereClauses; } +#nullable disable /// <summary> /// Formats a where clause for the specified provider. @@ -4726,7 +4644,7 @@ namespace Emby.Server.Implementations.Data return false; } - if (query.User == null) + if (query.User is null) { return false; } @@ -4847,22 +4765,20 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type commandText.Append(" LIMIT ").Append(query.Limit); } + var list = new List<string>(); using (var connection = GetConnection(true)) + 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) @@ -4887,30 +4803,27 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type commandText += " LIMIT " + query.Limit; } + var list = new List<PersonInfo>(); using (var connection = GetConnection(true)) + using (var statement = PrepareStatement(connection, commandText)) { - 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) { 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 ( @@ -5105,8 +5018,6 @@ AND Type = @InternalPersonType)"); { CheckDisposed(); - var now = DateTime.UtcNow; - var stringBuilder = new StringBuilder("Select Value From ItemValues where Type", 128); if (itemValueTypes.Length == 1) { @@ -5138,6 +5049,7 @@ AND Type = @InternalPersonType)"); var commandText = stringBuilder.ToString(); var list = new List<string>(); + using (new QueryTimeLogger(Logger, commandText)) using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, commandText)) { @@ -5150,7 +5062,6 @@ AND Type = @InternalPersonType)"); } } - LogQueryTime("GetItemValueNames", commandText, now); return list; } @@ -5165,8 +5076,6 @@ AND Type = @InternalPersonType)"); CheckDisposed(); - var now = DateTime.UtcNow; - var typeClause = itemValueTypes.Length == 1 ? ("Type=" + itemValueTypes[0]) : ("Type in (" + string.Join(',', itemValueTypes) + ")"); @@ -5287,7 +5196,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)); @@ -5340,6 +5249,7 @@ AND Type = @InternalPersonType)"); var list = new List<(BaseItem, ItemCounts)>(); var result = new QueryResult<(BaseItem, ItemCounts)>(); + using (new QueryTimeLogger(Logger, commandText)) using (var connection = GetConnection(true)) { connection.RunInTransaction( @@ -5355,7 +5265,7 @@ AND Type = @InternalPersonType)"); statement.TryBind("@UserId", query.User.InternalId); } - if (typeSubQuery != null) + if (typeSubQuery is not null) { GetWhereClauses(typeSubQuery, null); } @@ -5376,7 +5286,7 @@ AND Type = @InternalPersonType)"); foreach (var row in statement.ExecuteQuery()) { var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); - if (item != null) + if (item is not null) { var countStartColumn = columns.Count - 1; @@ -5396,7 +5306,7 @@ AND Type = @InternalPersonType)"); statement.TryBind("@UserId", query.User.InternalId); } - if (typeSubQuery != null) + if (typeSubQuery is not null) { GetWhereClauses(typeSubQuery, null); } @@ -5413,8 +5323,6 @@ AND Type = @InternalPersonType)"); ReadTransactionMode); } - LogQueryTime("GetItemValues", commandText, now); - if (result.TotalRecordCount == 0) { result.TotalRecordCount = list.Count; @@ -5499,6 +5407,9 @@ AND Type = @InternalPersonType)"); list.AddRange(inheritedTags.Select(i => (6, i))); + // Remove all invalid values. + list.RemoveAll(i => string.IsNullOrEmpty(i.Item2)); + return list; } @@ -5636,7 +5547,7 @@ 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); @@ -5665,9 +5576,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)) @@ -6239,5 +6151,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); + } + } + } } } diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index 8d78d644d..a1e217ad1 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -7,7 +7,7 @@ using System.Collections.Generic; using System.IO; using System.Threading; using Jellyfin.Data.Entities; -using MediaBrowser.Common.Configuration; +using MediaBrowser.Controller.Configuration; using MediaBrowser.Controller.Entities; using MediaBrowser.Controller.Library; using MediaBrowser.Controller.Persistence; @@ -18,33 +18,32 @@ namespace Emby.Server.Implementations.Data { public class SqliteUserDataRepository : BaseSqliteRepository, IUserDataRepository { + private readonly IUserManager _userManager; + public SqliteUserDataRepository( ILogger<SqliteUserDataRepository> logger, - IApplicationPaths appPaths) + IServerConfigurationManager config, + IUserManager userManager) : base(logger) { - DbFilePath = Path.Combine(appPaths.DataPath, "library.db"); + _userManager = userManager; + + DbFilePath = Path.Combine(config.ApplicationPaths.DataPath, "library.db"); } /// <summary> /// Opens the connection to the database. /// </summary> - /// <param name="userManager">The user manager.</param> - /// <param name="dbLock">The lock to use for database IO.</param> - /// <param name="dbConnection">The connection to use for database IO.</param> - public void Initialize(IUserManager userManager, SemaphoreSlim dbLock, SQLiteDatabaseConnection dbConnection) + public override void Initialize() { - WriteLock.Dispose(); - WriteLock = dbLock; - WriteConnection?.Dispose(); - WriteConnection = dbConnection; + base.Initialize(); using (var connection = GetConnection()) { var userDatasTableExists = TableExists(connection, "UserDatas"); var userDataTableExists = TableExists(connection, "userdata"); - var users = userDatasTableExists ? null : userManager.Users; + var users = userDatasTableExists ? null : _userManager.Users; connection.RunInTransaction( db => @@ -140,10 +139,7 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException(nameof(userId)); } - if (string.IsNullOrEmpty(key)) - { - throw new ArgumentNullException(nameof(key)); - } + ArgumentException.ThrowIfNullOrEmpty(key); PersistUserData(userId, key, userData, cancellationToken); } @@ -274,10 +270,7 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException(nameof(userId)); } - if (string.IsNullOrEmpty(key)) - { - throw new ArgumentNullException(nameof(key)); - } + ArgumentException.ThrowIfNullOrEmpty(key); using (var connection = GetConnection(true)) { @@ -377,20 +370,5 @@ namespace Emby.Server.Implementations.Data return userData; } - -#pragma warning disable CA2215 - /// <inheritdoc/> - /// <remarks> - /// There is nothing to dispose here since <see cref="BaseSqliteRepository.WriteLock"/> and - /// <see cref="BaseSqliteRepository.WriteConnection"/> are managed by <see cref="SqliteItemRepository"/>. - /// See <see cref="Initialize(IUserManager, SemaphoreSlim, SQLiteDatabaseConnection)"/>. - /// </remarks> - protected override void Dispose(bool dispose) - { - // The write lock and connection for the item repository are shared with the user data repository - // since they point to the same database. The item repo has responsibility for disposing these two objects, - // so the user data repo should not attempt to dispose them as well - } -#pragma warning restore CA2215 } } diff --git a/Emby.Server.Implementations/Data/TypeMapper.cs b/Emby.Server.Implementations/Data/TypeMapper.cs index 064664e1f..9efcea842 100644 --- a/Emby.Server.Implementations/Data/TypeMapper.cs +++ b/Emby.Server.Implementations/Data/TypeMapper.cs @@ -23,14 +23,11 @@ namespace Emby.Server.Implementations.Data /// <exception cref="ArgumentNullException"><c>typeName</c> is null.</exception> public Type? GetType(string typeName) { - if (string.IsNullOrEmpty(typeName)) - { - throw new ArgumentNullException(nameof(typeName)); - } + ArgumentException.ThrowIfNullOrEmpty(typeName); return _typeMap.GetOrAdd(typeName, k => AppDomain.CurrentDomain.GetAssemblies() .Select(a => a.GetType(k)) - .FirstOrDefault(t => t != null)); + .FirstOrDefault(t => t is not null)); } } } |