diff options
| author | Joshua M. Boniface <joshua@boniface.me> | 2021-08-18 02:46:59 -0400 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2021-08-18 02:46:59 -0400 |
| commit | 72d3f7020ad80ce1a53eeae8c5d57abeb22a4679 (patch) | |
| tree | dd43e663838cdc7d99a4af565523df58ae23c856 /Emby.Server.Implementations/Data | |
| parent | 7aef0fce444e6d8e06386553ec7ea1401a01bbb1 (diff) | |
| parent | e5cbafdb6b47377052e0d638908ef96e30a997d6 (diff) | |
Merge branch 'master' into patch-2
Diffstat (limited to 'Emby.Server.Implementations/Data')
6 files changed, 1030 insertions, 933 deletions
diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index 8a3716380..6f23a0888 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -1,3 +1,5 @@ +#nullable disable + #pragma warning disable CS1591 using System; @@ -143,12 +145,22 @@ namespace Emby.Server.Implementations.Data public IStatement PrepareStatement(IDatabaseConnection connection, string sql) => connection.PrepareStatement(sql); - public IEnumerable<IStatement> PrepareAll(IDatabaseConnection connection, IEnumerable<string> sql) - => sql.Select(connection.PrepareStatement); + public IStatement[] PrepareAll(IDatabaseConnection connection, IReadOnlyList<string> sql) + { + int len = sql.Count; + IStatement[] statements = new IStatement[len]; + for (int i = 0; i < len; i++) + { + statements[i] = connection.PrepareStatement(sql[i]); + } + + return statements; + } protected bool TableExists(ManagedConnection connection, string name) { - return connection.RunInTransaction(db => + return connection.RunInTransaction( + db => { using (var statement = PrepareStatement(db, "select DISTINCT tbl_name from sqlite_master")) { @@ -171,11 +183,9 @@ namespace Emby.Server.Implementations.Data foreach (var row in connection.Query("PRAGMA table_info(" + table + ")")) { - if (row[1].SQLiteType != SQLiteType.Null) + if (row.TryGetString(1, out var columnName)) { - var name = row[1].ToString(); - - columnNames.Add(name); + columnNames.Add(columnName); } } diff --git a/Emby.Server.Implementations/Data/ManagedConnection.cs b/Emby.Server.Implementations/Data/ManagedConnection.cs index 5c094ddd2..afc8966f9 100644 --- a/Emby.Server.Implementations/Data/ManagedConnection.cs +++ b/Emby.Server.Implementations/Data/ManagedConnection.cs @@ -9,7 +9,7 @@ namespace Emby.Server.Implementations.Data { public class ManagedConnection : IDisposable { - private SQLiteDatabaseConnection _db; + private SQLiteDatabaseConnection? _db; private readonly SemaphoreSlim _writeLock; private bool _disposed = false; @@ -54,12 +54,12 @@ namespace Emby.Server.Implementations.Data return _db.RunInTransaction(action, mode); } - public IEnumerable<IReadOnlyList<IResultSetValue>> Query(string sql) + public IEnumerable<IReadOnlyList<ResultSetValue>> Query(string sql) { return _db.Query(sql); } - public IEnumerable<IReadOnlyList<IResultSetValue>> Query(string sql, params object[] values) + public IEnumerable<IReadOnlyList<ResultSetValue>> Query(string sql, params object[] values) { return _db.Query(sql, values); } diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index 716e5071d..3289e7609 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -1,7 +1,9 @@ +#nullable disable #pragma warning disable CS1591 using System; using System.Collections.Generic; +using System.Diagnostics; using System.Globalization; using SQLitePCL.pretty; @@ -59,11 +61,11 @@ namespace Emby.Server.Implementations.Data connection.RunInTransaction(conn => { - conn.ExecuteAll(string.Join(";", queries)); + conn.ExecuteAll(string.Join(';', queries)); }); } - public static Guid ReadGuidFromBlob(this IResultSetValue result) + public static Guid ReadGuidFromBlob(this ResultSetValue result) { return new Guid(result.ToBlob()); } @@ -84,7 +86,7 @@ namespace Emby.Server.Implementations.Data private static string GetDateTimeKindFormat(DateTimeKind kind) => (kind == DateTimeKind.Utc) ? DatetimeFormatUtc : DatetimeFormatLocal; - public static DateTime ReadDateTime(this IResultSetValue result) + public static DateTime ReadDateTime(this ResultSetValue result) { var dateText = result.ToString(); @@ -95,72 +97,147 @@ namespace Emby.Server.Implementations.Data DateTimeStyles.None).ToUniversalTime(); } - public static DateTime? TryReadDateTime(this IResultSetValue result) + public static bool TryReadDateTime(this IReadOnlyList<ResultSetValue> reader, int index, out DateTime result) { - var dateText = result.ToString(); + var item = reader[index]; + if (item.IsDbNull()) + { + result = default; + return false; + } + + var dateText = item.ToString(); if (DateTime.TryParseExact(dateText, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.None, out var dateTimeResult)) { - return dateTimeResult.ToUniversalTime(); + result = dateTimeResult.ToUniversalTime(); + return true; } - return null; + result = default; + return false; } - public static void Attach(SQLiteDatabaseConnection db, string path, string alias) + public static bool TryGetGuid(this IReadOnlyList<ResultSetValue> reader, int index, out Guid result) { - var commandText = string.Format( - CultureInfo.InvariantCulture, - "attach @path as {0};", - alias); - - using (var statement = db.PrepareStatement(commandText)) + var item = reader[index]; + if (item.IsDbNull()) { - statement.TryBind("@path", path); - statement.MoveNext(); + result = default; + return false; } + + result = item.ReadGuidFromBlob(); + return true; } - public static bool IsDBNull(this IReadOnlyList<IResultSetValue> result, int index) + public static bool IsDbNull(this ResultSetValue result) { - return result[index].SQLiteType == SQLiteType.Null; + return result.SQLiteType == SQLiteType.Null; } - public static string GetString(this IReadOnlyList<IResultSetValue> result, int index) + public static string GetString(this IReadOnlyList<ResultSetValue> result, int index) { return result[index].ToString(); } - public static bool GetBoolean(this IReadOnlyList<IResultSetValue> result, int index) + public static bool TryGetString(this IReadOnlyList<ResultSetValue> reader, int index, out string result) + { + result = null; + var item = reader[index]; + if (item.IsDbNull()) + { + return false; + } + + result = item.ToString(); + return true; + } + + public static bool GetBoolean(this IReadOnlyList<ResultSetValue> result, int index) { return result[index].ToBool(); } - public static int GetInt32(this IReadOnlyList<IResultSetValue> result, int index) + public static bool TryGetBoolean(this IReadOnlyList<ResultSetValue> reader, int index, out bool result) + { + var item = reader[index]; + if (item.IsDbNull()) + { + result = default; + return false; + } + + result = item.ToBool(); + return true; + } + + public static bool TryGetInt32(this IReadOnlyList<ResultSetValue> reader, int index, out int result) { - return result[index].ToInt(); + var item = reader[index]; + if (item.IsDbNull()) + { + result = default; + return false; + } + + result = item.ToInt(); + return true; } - public static long GetInt64(this IReadOnlyList<IResultSetValue> result, int index) + public static long GetInt64(this IReadOnlyList<ResultSetValue> result, int index) { return result[index].ToInt64(); } - public static float GetFloat(this IReadOnlyList<IResultSetValue> result, int index) + public static bool TryGetInt64(this IReadOnlyList<ResultSetValue> reader, int index, out long result) + { + var item = reader[index]; + if (item.IsDbNull()) + { + result = default; + return false; + } + + result = item.ToInt64(); + return true; + } + + public static bool TryGetSingle(this IReadOnlyList<ResultSetValue> reader, int index, out float result) { - return result[index].ToFloat(); + var item = reader[index]; + if (item.IsDbNull()) + { + result = default; + return false; + } + + result = item.ToFloat(); + return true; + } + + public static bool TryGetDouble(this IReadOnlyList<ResultSetValue> reader, int index, out double result) + { + var item = reader[index]; + if (item.IsDbNull()) + { + result = default; + return false; + } + + result = item.ToDouble(); + return true; } - public static Guid GetGuid(this IReadOnlyList<IResultSetValue> result, int index) + public static Guid GetGuid(this IReadOnlyList<ResultSetValue> result, int index) { return result[index].ReadGuidFromBlob(); } + [Conditional("DEBUG")] private static void CheckName(string name) { -#if DEBUG throw new ArgumentException("Invalid param name: " + name, nameof(name)); -#endif } public static void TryBind(this IStatement statement, string name, double value) @@ -234,7 +311,9 @@ namespace Emby.Server.Implementations.Data { if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) { - bindParam.Bind(value.ToByteArray()); + Span<byte> byteValue = stackalloc byte[16]; + value.TryWriteBytes(byteValue); + bindParam.Bind(byteValue); } else { @@ -362,7 +441,7 @@ namespace Emby.Server.Implementations.Data } } - public static IEnumerable<IReadOnlyList<IResultSetValue>> ExecuteQuery(this IStatement statement) + public static IEnumerable<IReadOnlyList<ResultSetValue>> ExecuteQuery(this IStatement statement) { while (statement.MoveNext()) { diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 5bf740cfc..2cb10765f 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -1,6 +1,9 @@ +#nullable disable + #pragma warning disable CS1591 using System; +using System.Buffers.Text; using System.Collections.Generic; using System.Globalization; using System.IO; @@ -8,10 +11,12 @@ using System.Linq; using System.Text; using System.Text.Json; using System.Threading; +using Diacritics.Extensions; using Emby.Server.Implementations.Playlists; using Jellyfin.Data.Enums; +using Jellyfin.Extensions; +using Jellyfin.Extensions.Json; using MediaBrowser.Common.Extensions; -using MediaBrowser.Common.Json; using MediaBrowser.Controller; using MediaBrowser.Controller.Channels; using MediaBrowser.Controller.Configuration; @@ -40,6 +45,7 @@ namespace Emby.Server.Implementations.Data /// </summary> public class SqliteItemRepository : BaseSqliteRepository, IItemRepository { + private const string FromText = " from TypedBaseItems A"; private const string ChaptersTableName = "Chapters2"; private readonly IServerConfigurationManager _config; @@ -88,7 +94,7 @@ namespace Emby.Server.Implementations.Data _imageProcessor = imageProcessor; _typeMapper = new TypeMapper(); - _jsonOptions = JsonDefaults.GetOptions(); + _jsonOptions = JsonDefaults.Options; DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); } @@ -138,7 +144,6 @@ namespace Emby.Server.Implementations.Data "pragma shrink_memory" }; - string[] postQueries = { // obsolete @@ -220,7 +225,8 @@ namespace Emby.Server.Implementations.Data { connection.RunQueries(queries); - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { var existingColumnNames = GetColumnNames(db, "AncestorIds"); AddColumn(db, "AncestorIds", "AncestorIdText", "Text", existingColumnNames); @@ -496,12 +502,13 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection()) { - connection.RunInTransaction(db => + 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)); + saveImagesStatement.TryBind("@Images", SerializeImages(item.ImageInfos)); saveImagesStatement.MoveNext(); } @@ -547,7 +554,8 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection()) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { SaveItemsInTranscation(db, tuples); }, TransactionMode); @@ -560,7 +568,7 @@ namespace Emby.Server.Implementations.Data { SaveItemCommandText, "delete from AncestorIds where ItemId=@ItemId" - }).ToList(); + }); using (var saveItemStatement = statements[0]) using (var deleteAncestorsStatement = statements[1]) @@ -685,7 +693,7 @@ namespace Emby.Server.Implementations.Data if (item.Genres.Length > 0) { - saveItemStatement.TryBind("@Genres", string.Join("|", item.Genres)); + saveItemStatement.TryBind("@Genres", string.Join('|', item.Genres)); } else { @@ -747,7 +755,7 @@ namespace Emby.Server.Implementations.Data if (item.LockedFields.Length > 0) { - saveItemStatement.TryBind("@LockedFields", string.Join("|", item.LockedFields)); + saveItemStatement.TryBind("@LockedFields", string.Join('|', item.LockedFields)); } else { @@ -756,7 +764,7 @@ namespace Emby.Server.Implementations.Data if (item.Studios.Length > 0) { - saveItemStatement.TryBind("@Studios", string.Join("|", item.Studios)); + saveItemStatement.TryBind("@Studios", string.Join('|', item.Studios)); } else { @@ -783,7 +791,7 @@ namespace Emby.Server.Implementations.Data if (item.Tags.Length > 0) { - saveItemStatement.TryBind("@Tags", string.Join("|", item.Tags)); + saveItemStatement.TryBind("@Tags", string.Join('|', item.Tags)); } else { @@ -805,7 +813,7 @@ namespace Emby.Server.Implementations.Data if (item is Trailer trailer && trailer.TrailerTypes.Length > 0) { - saveItemStatement.TryBind("@TrailerTypes", string.Join("|", trailer.TrailerTypes)); + saveItemStatement.TryBind("@TrailerTypes", string.Join('|', trailer.TrailerTypes)); } else { @@ -895,12 +903,12 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@ExternalSeriesId", item.ExternalSeriesId); saveItemStatement.TryBind("@Tagline", item.Tagline); - saveItemStatement.TryBind("@ProviderIds", SerializeProviderIds(item)); - saveItemStatement.TryBind("@Images", SerializeImages(item)); + saveItemStatement.TryBind("@ProviderIds", SerializeProviderIds(item.ProviderIds)); + saveItemStatement.TryBind("@Images", SerializeImages(item.ImageInfos)); if (item.ProductionLocations.Length > 0) { - saveItemStatement.TryBind("@ProductionLocations", string.Join("|", item.ProductionLocations)); + saveItemStatement.TryBind("@ProductionLocations", string.Join('|', item.ProductionLocations)); } else { @@ -909,7 +917,7 @@ namespace Emby.Server.Implementations.Data if (item.ExtraIds.Length > 0) { - saveItemStatement.TryBind("@ExtraIds", string.Join("|", item.ExtraIds)); + saveItemStatement.TryBind("@ExtraIds", string.Join('|', item.ExtraIds)); } else { @@ -929,7 +937,7 @@ namespace Emby.Server.Implementations.Data string artists = null; if (item is IHasArtist hasArtists && hasArtists.Artists.Count > 0) { - artists = string.Join("|", hasArtists.Artists); + artists = string.Join('|', hasArtists.Artists); } saveItemStatement.TryBind("@Artists", artists); @@ -938,7 +946,7 @@ namespace Emby.Server.Implementations.Data if (item is IHasAlbumArtist hasAlbumArtists && hasAlbumArtists.AlbumArtists.Count > 0) { - albumArtists = string.Join("|", hasAlbumArtists.AlbumArtists); + albumArtists = string.Join('|', hasAlbumArtists.AlbumArtists); } saveItemStatement.TryBind("@AlbumArtists", albumArtists); @@ -966,10 +974,10 @@ namespace Emby.Server.Implementations.Data saveItemStatement.MoveNext(); } - private static string SerializeProviderIds(BaseItem item) + internal static string SerializeProviderIds(Dictionary<string, string> providerIds) { StringBuilder str = new StringBuilder(); - foreach (var i in item.ProviderIds) + foreach (var i in providerIds) { // Ideally we shouldn't need this IsNullOrWhiteSpace check, // but we're seeing some cases of bad data slip through @@ -993,35 +1001,25 @@ namespace Emby.Server.Implementations.Data return str.ToString(); } - private static void DeserializeProviderIds(string value, BaseItem item) + internal static void DeserializeProviderIds(string value, IHasProviderIds item) { if (string.IsNullOrWhiteSpace(value)) { return; } - if (item.ProviderIds.Count > 0) - { - return; - } - - var parts = value.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); - - foreach (var part in parts) + foreach (var part in value.SpanSplit('|')) { - var idParts = part.Split('='); - - if (idParts.Length == 2) + var providerDelimiterIndex = part.IndexOf('='); + if (providerDelimiterIndex != -1 && providerDelimiterIndex == part.LastIndexOf('=')) { - item.SetProviderId(idParts[0], idParts[1]); + item.SetProviderId(part.Slice(0, providerDelimiterIndex).ToString(), part.Slice(providerDelimiterIndex + 1).ToString()); } } } - private string SerializeImages(BaseItem item) + internal string SerializeImages(ItemImageInfo[] images) { - var images = item.ImageInfos; - if (images.Length == 0) { return null; @@ -1043,39 +1041,48 @@ namespace Emby.Server.Implementations.Data return str.ToString(); } - private void DeserializeImages(string value, BaseItem item) + internal ItemImageInfo[] DeserializeImages(string value) { if (string.IsNullOrWhiteSpace(value)) { - return; + return Array.Empty<ItemImageInfo>(); } - if (item.ImageInfos.Length > 0) - { - return; - } + // TODO The following is an ugly performance optimization, but it's extremely unlikely that the data in the database would be malformed + var valueSpan = value.AsSpan(); + var count = valueSpan.Count('|') + 1; - var parts = value.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); - var list = new List<ItemImageInfo>(); - foreach (var part in parts) + var position = 0; + var result = new ItemImageInfo[count]; + foreach (var part in valueSpan.Split('|')) { var image = ItemImageInfoFromValueString(part); if (image != null) { - list.Add(image); + result[position++] = image; } } - item.ImageInfos = list.ToArray(); + if (position == count) + { + return result; + } + + if (position == 0) + { + return Array.Empty<ItemImageInfo>(); + } + + // Extremely unlikely, but somehow one or more of the image strings were malformed. Cut the array. + return result[..position]; } - public void AppendItemImageInfo(StringBuilder bldr, ItemImageInfo image) + private void AppendItemImageInfo(StringBuilder bldr, ItemImageInfo image) { const char Delimiter = '*'; var path = image.Path ?? string.Empty; - var hash = image.BlurHash ?? string.Empty; bldr.Append(GetPathToSave(path)) .Append(Delimiter) @@ -1085,48 +1092,105 @@ namespace Emby.Server.Implementations.Data .Append(Delimiter) .Append(image.Width) .Append(Delimiter) - .Append(image.Height) - .Append(Delimiter) - // Replace delimiters with other characters. - // This can be removed when we migrate to a proper DB. - .Append(hash.Replace('*', '/').Replace('|', '\\')); + .Append(image.Height); + + var hash = image.BlurHash; + if (!string.IsNullOrEmpty(hash)) + { + bldr.Append(Delimiter) + // Replace delimiters with other characters. + // This can be removed when we migrate to a proper DB. + .Append(hash.Replace('*', '/').Replace('|', '\\')); + } } - public ItemImageInfo ItemImageInfoFromValueString(string value) + internal ItemImageInfo ItemImageInfoFromValueString(ReadOnlySpan<char> value) { - var parts = value.Split(new[] { '*' }, StringSplitOptions.None); + var nextSegment = value.IndexOf('*'); + if (nextSegment == -1) + { + return null; + } - if (parts.Length < 3) + ReadOnlySpan<char> path = value[..nextSegment]; + value = value[(nextSegment + 1)..]; + nextSegment = value.IndexOf('*'); + if (nextSegment == -1) { return null; } - var image = new ItemImageInfo(); + ReadOnlySpan<char> dateModified = value[..nextSegment]; + value = value[(nextSegment + 1)..]; + nextSegment = value.IndexOf('*'); + if (nextSegment == -1) + { + nextSegment = value.Length; + } - image.Path = RestorePath(parts[0]); + ReadOnlySpan<char> imageType = value[..nextSegment]; - if (long.TryParse(parts[1], NumberStyles.Any, CultureInfo.InvariantCulture, out var ticks)) + var image = new ItemImageInfo + { + Path = RestorePath(path.ToString()) + }; + + if (long.TryParse(dateModified, NumberStyles.Any, CultureInfo.InvariantCulture, out var ticks)) { image.DateModified = new DateTime(ticks, DateTimeKind.Utc); } - if (Enum.TryParse(parts[2], true, out ImageType type)) + if (Enum.TryParse(imageType.ToString(), true, out ImageType type)) { image.Type = type; } - if (parts.Length >= 5) + // Optional parameters: width*height*blurhash + if (nextSegment + 1 < value.Length - 1) { - if (int.TryParse(parts[3], NumberStyles.Integer, CultureInfo.InvariantCulture, out var width) - && int.TryParse(parts[4], NumberStyles.Integer, CultureInfo.InvariantCulture, out var height)) + value = value[(nextSegment + 1)..]; + nextSegment = value.IndexOf('*'); + if (nextSegment == -1 || nextSegment == value.Length) + { + return image; + } + + ReadOnlySpan<char> widthSpan = value[..nextSegment]; + + value = value[(nextSegment + 1)..]; + nextSegment = value.IndexOf('*'); + if (nextSegment == -1) + { + nextSegment = value.Length; + } + + ReadOnlySpan<char> heightSpan = value[..nextSegment]; + + if (int.TryParse(widthSpan, NumberStyles.Integer, CultureInfo.InvariantCulture, out var width) + && int.TryParse(heightSpan, NumberStyles.Integer, CultureInfo.InvariantCulture, out var height)) { image.Width = width; image.Height = height; } - if (parts.Length >= 6) + if (nextSegment < value.Length - 1) { - image.BlurHash = parts[5].Replace('/', '*').Replace('\\', '|'); + value = value[(nextSegment + 1)..]; + var length = value.Length; + + Span<char> blurHashSpan = stackalloc char[length]; + for (int i = 0; i < length; i++) + { + var c = value[i]; + blurHashSpan[i] = c switch + { + '/' => '*', + '\\' => '|', + _ => c + }; + } + + image.BlurHash = new string(blurHashSpan); } } @@ -1239,12 +1303,12 @@ namespace Emby.Server.Implementations.Data return true; } - private BaseItem GetItem(IReadOnlyList<IResultSetValue> reader, InternalItemsQuery query) + private BaseItem GetItem(IReadOnlyList<ResultSetValue> 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<IResultSetValue> reader, InternalItemsQuery query, bool enableProgramAttributes, bool hasEpisodeAttributes, bool hasServiceName, bool queryHasStartDate, bool hasTrailerTypes, bool hasArtistFields, bool hasSeriesFields) + private BaseItem GetItem(IReadOnlyList<ResultSetValue> reader, InternalItemsQuery query, bool enableProgramAttributes, bool hasEpisodeAttributes, bool hasServiceName, bool queryHasStartDate, bool hasTrailerTypes, bool hasArtistFields, bool hasSeriesFields) { var typeString = reader.GetString(0); @@ -1289,27 +1353,30 @@ namespace Emby.Server.Implementations.Data if (queryHasStartDate) { - if (!reader.IsDBNull(index)) + if (item is IHasStartDate hasStartDate && reader.TryReadDateTime(index, out var startDate)) { - if (item is IHasStartDate hasStartDate) - { - hasStartDate.StartDate = reader[index].ReadDateTime(); - } + hasStartDate.StartDate = startDate; } index++; } - if (!reader.IsDBNull(index)) + if (reader.TryReadDateTime(index++, out var endDate)) { - item.EndDate = reader[index].TryReadDateTime(); + item.EndDate = endDate; } - index++; - - if (!reader.IsDBNull(index)) + var channelId = reader[index]; + if (!channelId.IsDbNull()) { - item.ChannelId = new Guid(reader.GetString(index)); + 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; } index++; @@ -1318,33 +1385,25 @@ namespace Emby.Server.Implementations.Data { if (item is IHasProgramAttributes hasProgramAttributes) { - if (!reader.IsDBNull(index)) + if (reader.TryGetBoolean(index++, out var isMovie)) { - hasProgramAttributes.IsMovie = reader.GetBoolean(index); + hasProgramAttributes.IsMovie = isMovie; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetBoolean(index++, out var isSeries)) { - hasProgramAttributes.IsSeries = reader.GetBoolean(index); + hasProgramAttributes.IsSeries = isSeries; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var episodeTitle)) { - hasProgramAttributes.EpisodeTitle = reader.GetString(index); + hasProgramAttributes.EpisodeTitle = episodeTitle; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetBoolean(index++, out var isRepeat)) { - hasProgramAttributes.IsRepeat = reader.GetBoolean(index); + hasProgramAttributes.IsRepeat = isRepeat; } - - index++; } else { @@ -1352,246 +1411,194 @@ namespace Emby.Server.Implementations.Data } } - if (!reader.IsDBNull(index)) + if (reader.TryGetSingle(index++, out var communityRating)) { - item.CommunityRating = reader.GetFloat(index); + item.CommunityRating = communityRating; } - index++; - if (HasField(query, ItemFields.CustomRating)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var customRating)) { - item.CustomRating = reader.GetString(index); + item.CustomRating = customRating; } - - index++; } - if (!reader.IsDBNull(index)) + if (reader.TryGetInt32(index++, out var indexNumber)) { - item.IndexNumber = reader.GetInt32(index); + item.IndexNumber = indexNumber; } - index++; - if (HasField(query, ItemFields.Settings)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetBoolean(index++, out var isLocked)) { - item.IsLocked = reader.GetBoolean(index); + item.IsLocked = isLocked; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var preferredMetadataLanguage)) { - item.PreferredMetadataLanguage = reader.GetString(index); + item.PreferredMetadataLanguage = preferredMetadataLanguage; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var preferredMetadataCountryCode)) { - item.PreferredMetadataCountryCode = reader.GetString(index); + item.PreferredMetadataCountryCode = preferredMetadataCountryCode; } - - index++; } if (HasField(query, ItemFields.Width)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetInt32(index++, out var width)) { - item.Width = reader.GetInt32(index); + item.Width = width; } - - index++; } if (HasField(query, ItemFields.Height)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetInt32(index++, out var height)) { - item.Height = reader.GetInt32(index); + item.Height = height; } - - index++; } if (HasField(query, ItemFields.DateLastRefreshed)) { - if (!reader.IsDBNull(index)) + if (reader.TryReadDateTime(index++, out var dateLastRefreshed)) { - item.DateLastRefreshed = reader[index].ReadDateTime(); + item.DateLastRefreshed = dateLastRefreshed; } - - index++; } - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var name)) { - item.Name = reader.GetString(index); + item.Name = name; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var restorePath)) { - item.Path = RestorePath(reader.GetString(index)); + item.Path = RestorePath(restorePath); } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryReadDateTime(index++, out var premiereDate)) { - item.PremiereDate = reader[index].TryReadDateTime(); + item.PremiereDate = premiereDate; } - index++; - if (HasField(query, ItemFields.Overview)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var overview)) { - item.Overview = reader.GetString(index); + item.Overview = overview; } - - index++; } - if (!reader.IsDBNull(index)) + if (reader.TryGetInt32(index++, out var parentIndexNumber)) { - item.ParentIndexNumber = reader.GetInt32(index); + item.ParentIndexNumber = parentIndexNumber; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetInt32(index++, out var productionYear)) { - item.ProductionYear = reader.GetInt32(index); + item.ProductionYear = productionYear; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var officialRating)) { - item.OfficialRating = reader.GetString(index); + item.OfficialRating = officialRating; } - index++; - if (HasField(query, ItemFields.SortName)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var forcedSortName)) { - item.ForcedSortName = reader.GetString(index); + item.ForcedSortName = forcedSortName; } - - index++; } - if (!reader.IsDBNull(index)) + if (reader.TryGetInt64(index++, out var runTimeTicks)) { - item.RunTimeTicks = reader.GetInt64(index); + item.RunTimeTicks = runTimeTicks; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetInt64(index++, out var size)) { - item.Size = reader.GetInt64(index); + item.Size = size; } - index++; - if (HasField(query, ItemFields.DateCreated)) { - if (!reader.IsDBNull(index)) + if (reader.TryReadDateTime(index++, out var dateCreated)) { - item.DateCreated = reader[index].ReadDateTime(); + item.DateCreated = dateCreated; } - - index++; } - if (!reader.IsDBNull(index)) + if (reader.TryReadDateTime(index++, out var dateModified)) { - item.DateModified = reader[index].ReadDateTime(); + item.DateModified = dateModified; } - index++; - - item.Id = reader.GetGuid(index); - index++; + item.Id = reader.GetGuid(index++); if (HasField(query, ItemFields.Genres)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var genres)) { - item.Genres = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + item.Genres = genres.Split('|', StringSplitOptions.RemoveEmptyEntries); } - - index++; } - if (!reader.IsDBNull(index)) + if (reader.TryGetGuid(index++, out var parentId)) { - item.ParentId = reader.GetGuid(index); + item.ParentId = parentId; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var audioString)) { - if (Enum.TryParse(reader.GetString(index), true, out ProgramAudio audio)) + // 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; } } - index++; - // TODO: Even if not needed by apps, the server needs it internally // But get this excluded from contexts where it is not needed if (hasServiceName) { if (item is LiveTvChannel liveTvChannel) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index, out var serviceName)) { - liveTvChannel.ServiceName = reader.GetString(index); + liveTvChannel.ServiceName = serviceName; } } index++; } - if (!reader.IsDBNull(index)) + if (reader.TryGetBoolean(index++, out var isInMixedFolder)) { - item.IsInMixedFolder = reader.GetBoolean(index); + item.IsInMixedFolder = isInMixedFolder; } - index++; - if (HasField(query, ItemFields.DateLastSaved)) { - if (!reader.IsDBNull(index)) + if (reader.TryReadDateTime(index++, out var dateLastSaved)) { - item.DateLastSaved = reader[index].ReadDateTime(); + item.DateLastSaved = dateLastSaved; } - - index++; } if (HasField(query, ItemFields.Settings)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var lockedFields)) { IEnumerable<MetadataField> GetLockedFields(string s) { - foreach (var i in s.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries)) + foreach (var i in s.Split('|', StringSplitOptions.RemoveEmptyEntries)) { if (Enum.TryParse(i, true, out MetadataField parsedValue)) { @@ -1600,41 +1607,35 @@ namespace Emby.Server.Implementations.Data } } - item.LockedFields = GetLockedFields(reader.GetString(index)).ToArray(); + item.LockedFields = GetLockedFields(lockedFields).ToArray(); } - - index++; } if (HasField(query, ItemFields.Studios)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var studios)) { - item.Studios = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + item.Studios = studios.Split('|', StringSplitOptions.RemoveEmptyEntries); } - - index++; } if (HasField(query, ItemFields.Tags)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var tags)) { - item.Tags = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + item.Tags = tags.Split('|', StringSplitOptions.RemoveEmptyEntries); } - - index++; } if (hasTrailerTypes) { if (item is Trailer trailer) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index, out var trailerTypes)) { IEnumerable<TrailerType> GetTrailerTypes(string s) { - foreach (var i in s.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries)) + foreach (var i in s.Split('|', StringSplitOptions.RemoveEmptyEntries)) { if (Enum.TryParse(i, true, out TrailerType parsedValue)) { @@ -1643,7 +1644,7 @@ namespace Emby.Server.Implementations.Data } } - trailer.TrailerTypes = GetTrailerTypes(reader.GetString(index)).ToArray(); + trailer.TrailerTypes = GetTrailerTypes(trailerTypes).ToArray(); } } @@ -1652,19 +1653,17 @@ namespace Emby.Server.Implementations.Data if (HasField(query, ItemFields.OriginalTitle)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var originalTitle)) { - item.OriginalTitle = reader.GetString(index); + item.OriginalTitle = originalTitle; } - - index++; } if (item is Video video) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index, out var primaryVersionId)) { - video.PrimaryVersionId = reader.GetString(index); + video.PrimaryVersionId = primaryVersionId; } } @@ -1672,40 +1671,34 @@ namespace Emby.Server.Implementations.Data if (HasField(query, ItemFields.DateLastMediaAdded)) { - if (item is Folder folder && !reader.IsDBNull(index)) + if (item is Folder folder && reader.TryReadDateTime(index, out var dateLastMediaAdded)) { - folder.DateLastMediaAdded = reader[index].TryReadDateTime(); + folder.DateLastMediaAdded = dateLastMediaAdded; } index++; } - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var album)) { - item.Album = reader.GetString(index); + item.Album = album; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetSingle(index++, out var criticRating)) { - item.CriticRating = reader.GetFloat(index); + item.CriticRating = criticRating; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetBoolean(index++, out var isVirtualItem)) { - item.IsVirtualItem = reader.GetBoolean(index); + item.IsVirtualItem = isVirtualItem; } - index++; - if (item is IHasSeries hasSeriesName) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index, out var seriesName)) { - hasSeriesName.SeriesName = reader.GetString(index); + hasSeriesName.SeriesName = seriesName; } } @@ -1715,15 +1708,15 @@ namespace Emby.Server.Implementations.Data { if (item is Episode episode) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index, out var seasonName)) { - episode.SeasonName = reader.GetString(index); + episode.SeasonName = seasonName; } index++; - if (!reader.IsDBNull(index)) + if (reader.TryGetGuid(index, out var seasonId)) { - episode.SeasonId = reader.GetGuid(index); + episode.SeasonId = seasonId; } } else @@ -1739,9 +1732,9 @@ namespace Emby.Server.Implementations.Data { if (hasSeries != null) { - if (!reader.IsDBNull(index)) + if (reader.TryGetGuid(index, out var seriesId)) { - hasSeries.SeriesId = reader.GetGuid(index); + hasSeries.SeriesId = seriesId; } } @@ -1750,56 +1743,48 @@ namespace Emby.Server.Implementations.Data if (HasField(query, ItemFields.PresentationUniqueKey)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var presentationUniqueKey)) { - item.PresentationUniqueKey = reader.GetString(index); + item.PresentationUniqueKey = presentationUniqueKey; } - - index++; } if (HasField(query, ItemFields.InheritedParentalRatingValue)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetInt32(index++, out var parentalRating)) { - item.InheritedParentalRatingValue = reader.GetInt32(index); + item.InheritedParentalRatingValue = parentalRating; } - - index++; } if (HasField(query, ItemFields.ExternalSeriesId)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var externalSeriesId)) { - item.ExternalSeriesId = reader.GetString(index); + item.ExternalSeriesId = externalSeriesId; } - - index++; } if (HasField(query, ItemFields.Taglines)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var tagLine)) { - item.Tagline = reader.GetString(index); + item.Tagline = tagLine; } - - index++; } - if (!reader.IsDBNull(index)) + if (item.ProviderIds.Count == 0 && reader.TryGetString(index, out var providerIds)) { - DeserializeProviderIds(reader.GetString(index), item); + DeserializeProviderIds(providerIds, item); } index++; if (query.DtoOptions.EnableImages) { - if (!reader.IsDBNull(index)) + if (item.ImageInfos.Length == 0 && reader.TryGetString(index, out var imageInfos)) { - DeserializeImages(reader.GetString(index), item); + item.ImageInfos = DeserializeImages(imageInfos); } index++; @@ -1807,72 +1792,62 @@ namespace Emby.Server.Implementations.Data if (HasField(query, ItemFields.ProductionLocations)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var productionLocations)) { - item.ProductionLocations = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries).ToArray(); + item.ProductionLocations = productionLocations.Split('|', StringSplitOptions.RemoveEmptyEntries); } - - index++; } if (HasField(query, ItemFields.ExtraIds)) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var extraIds)) { - item.ExtraIds = SplitToGuids(reader.GetString(index)); + item.ExtraIds = SplitToGuids(extraIds); } - - index++; } - if (!reader.IsDBNull(index)) + if (reader.TryGetInt32(index++, out var totalBitrate)) { - item.TotalBitrate = reader.GetInt32(index); + item.TotalBitrate = totalBitrate; } - index++; - - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var extraTypeString)) { - if (Enum.TryParse(reader.GetString(index), true, out ExtraType extraType)) + if (Enum.TryParse(extraTypeString, true, out ExtraType extraType)) { item.ExtraType = extraType; } } - index++; - if (hasArtistFields) { - if (item is IHasArtist hasArtists && !reader.IsDBNull(index)) + if (item is IHasArtist hasArtists && reader.TryGetString(index, out var artists)) { - hasArtists.Artists = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + hasArtists.Artists = artists.Split('|', StringSplitOptions.RemoveEmptyEntries); } index++; - if (item is IHasAlbumArtist hasAlbumArtists && !reader.IsDBNull(index)) + if (item is IHasAlbumArtist hasAlbumArtists && reader.TryGetString(index, out var albumArtists)) { - hasAlbumArtists.AlbumArtists = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + hasAlbumArtists.AlbumArtists = albumArtists.Split('|', StringSplitOptions.RemoveEmptyEntries); } index++; } - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index++, out var externalId)) { - item.ExternalId = reader.GetString(index); + item.ExternalId = externalId; } - index++; - if (HasField(query, ItemFields.SeriesPresentationUniqueKey)) { if (hasSeries != null) { - if (!reader.IsDBNull(index)) + if (reader.TryGetString(index, out var seriesPresentationUniqueKey)) { - hasSeries.SeriesPresentationUniqueKey = reader.GetString(index); + hasSeries.SeriesPresentationUniqueKey = seriesPresentationUniqueKey; } } @@ -1881,21 +1856,19 @@ namespace Emby.Server.Implementations.Data if (enableProgramAttributes) { - if (item is LiveTvProgram program && !reader.IsDBNull(index)) + if (item is LiveTvProgram program && reader.TryGetString(index, out var showId)) { - program.ShowId = reader.GetString(index); + program.ShowId = showId; } index++; } - if (!reader.IsDBNull(index)) + if (reader.TryGetGuid(index, out var ownerId)) { - item.OwnerId = reader.GetGuid(index); + item.OwnerId = ownerId; } - index++; - return item; } @@ -1975,21 +1948,21 @@ namespace Emby.Server.Implementations.Data /// <param name="reader">The reader.</param> /// <param name="item">The item.</param> /// <returns>ChapterInfo.</returns> - private ChapterInfo GetChapter(IReadOnlyList<IResultSetValue> reader, BaseItem item) + private ChapterInfo GetChapter(IReadOnlyList<ResultSetValue> reader, BaseItem item) { var chapter = new ChapterInfo { StartPositionTicks = reader.GetInt64(0) }; - if (!reader.IsDBNull(1)) + if (reader.TryGetString(1, out var chapterName)) { - chapter.Name = reader.GetString(1); + chapter.Name = chapterName; } - if (!reader.IsDBNull(2)) + if (reader.TryGetString(2, out var imagePath)) { - chapter.ImagePath = reader.GetString(2); + chapter.ImagePath = imagePath; if (!string.IsNullOrEmpty(chapter.ImagePath)) { @@ -2004,9 +1977,9 @@ namespace Emby.Server.Implementations.Data } } - if (!reader.IsDBNull(3)) + if (reader.TryReadDateTime(3, out var imageDateModified)) { - chapter.ImageDateModified = reader[3].ReadDateTime(); + chapter.ImageDateModified = imageDateModified; } return chapter; @@ -2033,7 +2006,8 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection()) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { // First delete chapters db.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", idBlob); @@ -2113,30 +2087,7 @@ namespace Emby.Server.Implementations.Data || query.IsLiked.HasValue; } - private readonly ItemFields[] _allFields = Enum.GetNames(typeof(ItemFields)) - .Select(i => (ItemFields)Enum.Parse(typeof(ItemFields), i, true)) - .ToArray(); - - private string[] GetColumnNamesFromField(ItemFields field) - { - switch (field) - { - case ItemFields.Settings: - return new[] { "IsLocked", "PreferredMetadataCountryCode", "PreferredMetadataLanguage", "LockedFields" }; - case ItemFields.ServiceName: - return new[] { "ExternalServiceId" }; - case ItemFields.SortName: - return new[] { "ForcedSortName" }; - case ItemFields.Taglines: - return new[] { "Tagline" }; - case ItemFields.Tags: - return new[] { "Tags" }; - case ItemFields.IsHD: - return Array.Empty<string>(); - default: - return new[] { field.ToString() }; - } - } + private readonly ItemFields[] _allFields = Enum.GetValues<ItemFields>(); private bool HasField(InternalItemsQuery query, ItemFields name) { @@ -2264,7 +2215,6 @@ namespace Emby.Server.Implementations.Data return query.IncludeItemTypes.Contains("Trailer", StringComparer.OrdinalIgnoreCase); } - private static readonly HashSet<string> _artistExcludeParentTypes = new HashSet<string>(StringComparer.OrdinalIgnoreCase) { "Series", @@ -2319,77 +2269,98 @@ namespace Emby.Server.Implementations.Data return query.IncludeItemTypes.Any(x => _seriesTypes.Contains(x)); } - private List<string> GetFinalColumnsToSelect(InternalItemsQuery query, IEnumerable<string> startColumns) + private void SetFinalColumnsToSelect(InternalItemsQuery query, List<string> columns) { - var list = startColumns.ToList(); - foreach (var field in _allFields) { if (!HasField(query, field)) { - foreach (var fieldToRemove in GetColumnNamesFromField(field)) + switch (field) { - list.Remove(fieldToRemove); + case ItemFields.Settings: + columns.Remove("IsLocked"); + columns.Remove("PreferredMetadataCountryCode"); + columns.Remove("PreferredMetadataLanguage"); + columns.Remove("LockedFields"); + break; + case ItemFields.ServiceName: + columns.Remove("ExternalServiceId"); + break; + case ItemFields.SortName: + columns.Remove("ForcedSortName"); + break; + case ItemFields.Taglines: + columns.Remove("Tagline"); + break; + case ItemFields.Tags: + columns.Remove("Tags"); + break; + case ItemFields.IsHD: + // do nothing + break; + default: + columns.Remove(field.ToString()); + break; } } } if (!HasProgramAttributes(query)) { - list.Remove("IsMovie"); - list.Remove("IsSeries"); - list.Remove("EpisodeTitle"); - list.Remove("IsRepeat"); - list.Remove("ShowId"); + columns.Remove("IsMovie"); + columns.Remove("IsSeries"); + columns.Remove("EpisodeTitle"); + columns.Remove("IsRepeat"); + columns.Remove("ShowId"); } if (!HasEpisodeAttributes(query)) { - list.Remove("SeasonName"); - list.Remove("SeasonId"); + columns.Remove("SeasonName"); + columns.Remove("SeasonId"); } if (!HasStartDate(query)) { - list.Remove("StartDate"); + columns.Remove("StartDate"); } if (!HasTrailerTypes(query)) { - list.Remove("TrailerTypes"); + columns.Remove("TrailerTypes"); } if (!HasArtistFields(query)) { - list.Remove("AlbumArtists"); - list.Remove("Artists"); + columns.Remove("AlbumArtists"); + columns.Remove("Artists"); } if (!HasSeriesFields(query)) { - list.Remove("SeriesId"); + columns.Remove("SeriesId"); } if (!HasEpisodeAttributes(query)) { - list.Remove("SeasonName"); - list.Remove("SeasonId"); + columns.Remove("SeasonName"); + columns.Remove("SeasonId"); } if (!query.DtoOptions.EnableImages) { - list.Remove("Images"); + columns.Remove("Images"); } if (EnableJoinUserData(query)) { - list.Add("UserDatas.UserId"); - list.Add("UserDatas.lastPlayedDate"); - list.Add("UserDatas.playbackPositionTicks"); - list.Add("UserDatas.playcount"); - list.Add("UserDatas.isFavorite"); - list.Add("UserDatas.played"); - list.Add("UserDatas.rating"); + columns.Add("UserDatas.UserId"); + columns.Add("UserDatas.lastPlayedDate"); + columns.Add("UserDatas.playbackPositionTicks"); + columns.Add("UserDatas.playcount"); + columns.Add("UserDatas.isFavorite"); + columns.Add("UserDatas.played"); + columns.Add("UserDatas.rating"); } if (query.SimilarTo != null) @@ -2401,11 +2372,11 @@ namespace Emby.Server.Implementations.Data if (string.IsNullOrEmpty(item.OfficialRating)) { - builder.Append("((OfficialRating is null) * 10)"); + builder.Append("(OfficialRating is null * 10)"); } else { - builder.Append("((OfficialRating=@ItemOfficialRating) * 10)"); + builder.Append("(OfficialRating=@ItemOfficialRating * 10)"); } if (item.ProductionYear.HasValue) @@ -2414,12 +2385,30 @@ namespace Emby.Server.Implementations.Data builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 5 Then 5 Else 0 End )"); } - //// genres, tags - builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId)) * 10)"); + // 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))"); + + if (item is MusicArtist) + { + // Match albums where the artist is AlbumArtist against other albums. + // It is assumed that similar albums => similar artists. + builder.Append( + @"+ (WITH artistValues AS ( + SELECT DISTINCT albumValues.CleanValue + FROM ItemValues albumValues + INNER JOIN ItemValues artistAlbums ON albumValues.ItemId = artistAlbums.ItemId + INNER JOIN TypedBaseItems artistItem ON artistAlbums.CleanValue = artistItem.CleanName AND artistAlbums.TYPE = 1 AND artistItem.Guid = @SimilarItemId + ), similarArtist AS ( + SELECT albumValues.ItemId + FROM ItemValues albumValues + INNER JOIN ItemValues artistAlbums ON albumValues.ItemId = artistAlbums.ItemId + INNER JOIN TypedBaseItems artistItem ON artistAlbums.CleanValue = artistItem.CleanName AND artistAlbums.TYPE = 1 AND artistItem.Guid = A.Guid + ) SELECT COUNT(DISTINCT(CleanValue)) * 10 FROM ItemValues WHERE ItemId IN (SELECT ItemId FROM similarArtist) AND CleanValue IN (SELECT CleanValue FROM artistValues))"); + } builder.Append(") as SimilarityScore"); - list.Add(builder.ToString()); + columns.Add(builder.ToString()); var oldLen = query.ExcludeItemIds.Length; var newLen = oldLen + item.ExtraIds.Length + 1; @@ -2446,10 +2435,8 @@ namespace Emby.Server.Implementations.Data builder.Append(") as SearchScore"); - list.Add(builder.ToString()); + columns.Add(builder.ToString()); } - - return list; } private void BindSearchParams(InternalItemsQuery query, IStatement statement) @@ -2515,31 +2502,25 @@ namespace Emby.Server.Implementations.Data private string GetGroupBy(InternalItemsQuery query) { - var groups = new List<string>(); - - if (EnableGroupByPresentationUniqueKey(query)) + var enableGroupByPresentationUniqueKey = EnableGroupByPresentationUniqueKey(query); + if (enableGroupByPresentationUniqueKey && query.GroupBySeriesPresentationUniqueKey) { - groups.Add("PresentationUniqueKey"); + return " Group by PresentationUniqueKey, SeriesPresentationUniqueKey"; } - if (query.GroupBySeriesPresentationUniqueKey) + if (enableGroupByPresentationUniqueKey) { - groups.Add("SeriesPresentationUniqueKey"); + return " Group by PresentationUniqueKey"; } - if (groups.Count > 0) + if (query.GroupBySeriesPresentationUniqueKey) { - return " Group by " + string.Join(",", groups); + return " Group by SeriesPresentationUniqueKey"; } return string.Empty; } - private string GetFromText(string alias = "A") - { - return " from TypedBaseItems " + alias; - } - public int GetCount(InternalItemsQuery query) { if (query == null) @@ -2557,17 +2538,21 @@ namespace Emby.Server.Implementations.Data query.Limit = query.Limit.Value + 4; } - var commandText = "select " - + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count(distinct PresentationUniqueKey)" })) - + GetFromText() - + GetJoinUserDataText(query); + var columns = new List<string> { "count(distinct PresentationUniqueKey)" }; + 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) { - commandText += " where " + string.Join(" AND ", whereClauses); + commandTextBuilder.Append(" where ") + .AppendJoin(" AND ", whereClauses); } + var commandText = commandTextBuilder.ToString(); int count; using (var connection = GetConnection(true)) { @@ -2609,20 +2594,23 @@ namespace Emby.Server.Implementations.Data query.Limit = query.Limit.Value + 4; } - var commandText = "select " - + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns)) - + GetFromText() - + GetJoinUserDataText(query); + var columns = _retriveItemColumns.ToList(); + SetFinalColumnsToSelect(query, columns); + var commandTextBuilder = new StringBuilder("select ", 1024) + .AppendJoin(',', columns) + .Append(FromText) + .Append(GetJoinUserDataText(query)); var whereClauses = GetWhereClauses(query, null); if (whereClauses.Count != 0) { - commandText += " where " + string.Join(" AND ", whereClauses); + commandTextBuilder.Append(" where ") + .AppendJoin(" AND ", whereClauses); } - commandText += GetGroupBy(query) - + GetOrderByText(query); + commandTextBuilder.Append(GetGroupBy(query)) + .Append(GetOrderByText(query)); if (query.Limit.HasValue || query.StartIndex.HasValue) { @@ -2630,15 +2618,18 @@ 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 commandText = commandTextBuilder.ToString(); var items = new List<BaseItem>(); using (var connection = GetConnection(true)) { @@ -2701,87 +2692,22 @@ namespace Emby.Server.Implementations.Data private string FixUnicodeChars(string buffer) { - if (buffer.IndexOf('\u2013', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u2013', '-'); // en dash - } - - if (buffer.IndexOf('\u2014', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u2014', '-'); // em dash - } - - if (buffer.IndexOf('\u2015', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u2015', '-'); // horizontal bar - } - - if (buffer.IndexOf('\u2017', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u2017', '_'); // double low line - } - - if (buffer.IndexOf('\u2018', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u2018', '\''); // left single quotation mark - } - - if (buffer.IndexOf('\u2019', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u2019', '\''); // right single quotation mark - } - - if (buffer.IndexOf('\u201a', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u201a', ','); // single low-9 quotation mark - } - - if (buffer.IndexOf('\u201b', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u201b', '\''); // single high-reversed-9 quotation mark - } - - if (buffer.IndexOf('\u201c', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u201c', '\"'); // left double quotation mark - } - - if (buffer.IndexOf('\u201d', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u201d', '\"'); // right double quotation mark - } - - if (buffer.IndexOf('\u201e', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u201e', '\"'); // double low-9 quotation mark - } - - if (buffer.IndexOf('\u2026', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace("\u2026", "...", StringComparison.Ordinal); // horizontal ellipsis - } - - if (buffer.IndexOf('\u2032', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u2032', '\''); // prime - } - - if (buffer.IndexOf('\u2033', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u2033', '\"'); // double prime - } - - if (buffer.IndexOf('\u0060', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u0060', '\''); // grave accent - } - - if (buffer.IndexOf('\u00B4', StringComparison.Ordinal) > -1) - { - buffer = buffer.Replace('\u00B4', '\''); // acute accent - } - - return buffer; + buffer = buffer.Replace('\u2013', '-'); // en dash + buffer = buffer.Replace('\u2014', '-'); // em dash + buffer = buffer.Replace('\u2015', '-'); // horizontal bar + buffer = buffer.Replace('\u2017', '_'); // double low line + buffer = buffer.Replace('\u2018', '\''); // left single quotation mark + buffer = buffer.Replace('\u2019', '\''); // right single quotation mark + buffer = buffer.Replace('\u201a', ','); // single low-9 quotation mark + buffer = buffer.Replace('\u201b', '\''); // single high-reversed-9 quotation mark + buffer = buffer.Replace('\u201c', '\"'); // left double quotation mark + buffer = buffer.Replace('\u201d', '\"'); // right double quotation mark + buffer = buffer.Replace('\u201e', '\"'); // double low-9 quotation mark + buffer = buffer.Replace("\u2026", "...", StringComparison.Ordinal); // horizontal ellipsis + buffer = buffer.Replace('\u2032', '\''); // prime + buffer = buffer.Replace('\u2033', '\"'); // double prime + buffer = buffer.Replace('\u0060', '\''); // grave accent + return buffer.Replace('\u00B4', '\''); // acute accent } private void AddItem(List<BaseItem> items, BaseItem newItem) @@ -2859,20 +2785,27 @@ namespace Emby.Server.Implementations.Data query.Limit = query.Limit.Value + 4; } - var commandText = "select " - + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns)) - + GetFromText() - + GetJoinUserDataText(query); + var columns = _retriveItemColumns.ToList(); + SetFinalColumnsToSelect(query, columns); + var commandTextBuilder = new StringBuilder("select ", 512) + .AppendJoin(',', columns) + .Append(FromText) + .Append(GetJoinUserDataText(query)); var whereClauses = GetWhereClauses(query, null); var whereText = whereClauses.Count == 0 ? string.Empty : - " where " + string.Join(" AND ", whereClauses); + string.Join(" AND ", whereClauses); - commandText += whereText - + GetGroupBy(query) - + GetOrderByText(query); + if (!string.IsNullOrEmpty(whereText)) + { + commandTextBuilder.Append(" where ") + .Append(whereText); + } + + commandTextBuilder.Append(GetGroupBy(query)) + .Append(GetOrderByText(query)); if (query.Limit.HasValue || query.StartIndex.HasValue) { @@ -2880,56 +2813,73 @@ 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>(); + var itemQuery = string.Empty; + var totalRecordCountQuery = string.Empty; if (!isReturningZeroItems) { - statementTexts.Add(commandText); + itemQuery = commandTextBuilder.ToString(); } if (query.EnableTotalRecordCount) { - commandText = string.Empty; + commandTextBuilder.Clear(); + + commandTextBuilder.Append(" select "); + List<string> columnsToSelect; if (EnableGroupByPresentationUniqueKey(query)) { - commandText += " select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct PresentationUniqueKey)" })) + GetFromText(); + columnsToSelect = new List<string> { "count (distinct PresentationUniqueKey)" }; } else if (query.GroupBySeriesPresentationUniqueKey) { - commandText += " select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct SeriesPresentationUniqueKey)" })) + GetFromText(); + columnsToSelect = new List<string> { "count (distinct SeriesPresentationUniqueKey)" }; } else { - commandText += " select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (guid)" })) + GetFromText(); + columnsToSelect = new List<string> { "count (guid)" }; } - commandText += GetJoinUserDataText(query) - + whereText; - statementTexts.Add(commandText); + SetFinalColumnsToSelect(query, columnsToSelect); + + commandTextBuilder.AppendJoin(',', columnsToSelect) + .Append(FromText) + .Append(GetJoinUserDataText(query)); + if (!string.IsNullOrEmpty(whereText)) + { + commandTextBuilder.Append(" where ") + .Append(whereText); + } + + totalRecordCountQuery = commandTextBuilder.ToString(); } var list = new List<BaseItem>(); var result = new QueryResult<BaseItem>(); using (var connection = GetConnection(true)) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { - var statements = PrepareAll(db, statementTexts).ToList(); + var itemQueryStatement = PrepareStatement(db, itemQuery); + var totalRecordCountQueryStatement = PrepareStatement(db, totalRecordCountQuery); if (!isReturningZeroItems) { - using (var statement = statements[0]) + using (var statement = itemQueryStatement) { if (EnableJoinUserData(query)) { @@ -2959,11 +2909,14 @@ namespace Emby.Server.Implementations.Data } } } + + LogQueryTime("GetItems.ItemQuery", itemQuery, now); } + now = DateTime.UtcNow; if (query.EnableTotalRecordCount) { - using (var statement = statements[statements.Count - 1]) + using (var statement = totalRecordCountQueryStatement) { if (EnableJoinUserData(query)) { @@ -2978,11 +2931,12 @@ namespace Emby.Server.Implementations.Data result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); } + + LogQueryTime("GetItems.TotalRecordCount", totalRecordCountQuery, now); } }, ReadTransactionMode); } - LogQueryTime("GetItems", commandText, now); result.Items = list; return result; } @@ -3018,7 +2972,7 @@ namespace Emby.Server.Implementations.Data return string.Empty; } - return " ORDER BY " + string.Join(",", orderBy.Select(i => + return " ORDER BY " + string.Join(',', orderBy.Select(i => { var columnMap = MapOrderByField(i.Item1, query); @@ -3115,19 +3069,22 @@ namespace Emby.Server.Implementations.Data var now = DateTime.UtcNow; - var commandText = "select " - + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" })) - + GetFromText() - + GetJoinUserDataText(query); + 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) { - commandText += " where " + string.Join(" AND ", whereClauses); + commandTextBuilder.Append(" where ") + .AppendJoin(" AND ", whereClauses); } - commandText += GetGroupBy(query) - + GetOrderByText(query); + commandTextBuilder.Append(GetGroupBy(query)) + .Append(GetOrderByText(query)); if (query.Limit.HasValue || query.StartIndex.HasValue) { @@ -3135,15 +3092,18 @@ 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 commandText = commandTextBuilder.ToString(); var list = new List<Guid>(); using (var connection = GetConnection(true)) { @@ -3182,7 +3142,9 @@ namespace Emby.Server.Implementations.Data var now = DateTime.UtcNow; - var commandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid", "path" })) + GetFromText(); + 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) @@ -3224,12 +3186,8 @@ namespace Emby.Server.Implementations.Data foreach (var row in statement.ExecuteQuery()) { var id = row.GetGuid(0); - string path = null; - if (!row.IsDBNull(1)) - { - path = row.GetString(1); - } + row.TryGetString(1, out var path); list.Add(new Tuple<Guid, string>(id, path)); } @@ -3262,9 +3220,11 @@ namespace Emby.Server.Implementations.Data var now = DateTime.UtcNow; + var columns = new List<string> { "guid" }; + SetFinalColumnsToSelect(query, columns); var commandText = "select " - + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" })) - + GetFromText() + + string.Join(',', columns) + + FromText + GetJoinUserDataText(query); var whereClauses = GetWhereClauses(query, null); @@ -3292,7 +3252,6 @@ namespace Emby.Server.Implementations.Data } } - var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; var statementTexts = new List<string>(); @@ -3305,19 +3264,23 @@ namespace Emby.Server.Implementations.Data { commandText = string.Empty; + List<string> columnsToSelect; if (EnableGroupByPresentationUniqueKey(query)) { - commandText += " select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct PresentationUniqueKey)" })) + GetFromText(); + columnsToSelect = new List<string> { "count (distinct PresentationUniqueKey)" }; } else if (query.GroupBySeriesPresentationUniqueKey) { - commandText += " select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct SeriesPresentationUniqueKey)" })) + GetFromText(); + columnsToSelect = new List<string> { "count (distinct SeriesPresentationUniqueKey)" }; } else { - commandText += " select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (guid)" })) + GetFromText(); + columnsToSelect = new List<string> { "count (guid)" }; } + SetFinalColumnsToSelect(query, columnsToSelect); + commandText += " select " + string.Join(',', columnsToSelect) + FromText; + commandText += GetJoinUserDataText(query) + whereText; statementTexts.Add(commandText); @@ -3327,9 +3290,10 @@ namespace Emby.Server.Implementations.Data var result = new QueryResult<Guid>(); using (var connection = GetConnection(true)) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { - var statements = PrepareAll(db, statementTexts).ToList(); + var statements = PrepareAll(db, statementTexts); if (!isReturningZeroItems) { @@ -3355,7 +3319,7 @@ namespace Emby.Server.Implementations.Data if (query.EnableTotalRecordCount) { - using (var statement = statements[statements.Count - 1]) + using (var statement = statements[statements.Length - 1]) { if (EnableJoinUserData(query)) { @@ -3563,11 +3527,11 @@ namespace Emby.Server.Implementations.Data statement?.TryBind("@IsFolder", query.IsFolder); } - var includeTypes = query.IncludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray(); + var includeTypes = query.IncludeItemTypes.Select(MapIncludeItemTypes).Where(x => x != null).ToArray(); // Only specify excluded types if no included types are specified if (includeTypes.Length == 0) { - var excludeTypes = query.ExcludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray(); + var excludeTypes = query.ExcludeItemTypes.Select(MapIncludeItemTypes).Where(x => x != null).ToArray(); if (excludeTypes.Length == 1) { whereClauses.Add("type<>@type"); @@ -3575,7 +3539,7 @@ namespace Emby.Server.Implementations.Data } else if (excludeTypes.Length > 1) { - var inClause = string.Join(",", excludeTypes.Select(i => "'" + i + "'")); + var inClause = string.Join(',', excludeTypes.Select(i => "'" + i + "'")); whereClauses.Add($"type not in ({inClause})"); } } @@ -3586,18 +3550,18 @@ namespace Emby.Server.Implementations.Data } else if (includeTypes.Length > 1) { - var inClause = string.Join(",", includeTypes.Select(i => "'" + i + "'")); + var inClause = string.Join(',', includeTypes.Select(i => "'" + i + "'")); whereClauses.Add($"type in ({inClause})"); } - if (query.ChannelIds.Length == 1) + if (query.ChannelIds.Count == 1) { whereClauses.Add("ChannelId=@ChannelId"); statement?.TryBind("@ChannelId", query.ChannelIds[0].ToString("N", CultureInfo.InvariantCulture)); } - else if (query.ChannelIds.Length > 1) + else if (query.ChannelIds.Count > 1) { - var inClause = string.Join(",", query.ChannelIds.Select(i => "'" + i.ToString("N", CultureInfo.InvariantCulture) + "'")); + var inClause = string.Join(',', query.ChannelIds.Select(i => "'" + i.ToString("N", CultureInfo.InvariantCulture) + "'")); whereClauses.Add($"ChannelId in ({inClause})"); } @@ -3718,26 +3682,31 @@ namespace Emby.Server.Implementations.Data statement?.TryBind("@MaxPremiereDate", query.MaxPremiereDate.Value); } + StringBuilder clauseBuilder = new StringBuilder(); + const string Or = " OR "; + var trailerTypes = query.TrailerTypes; int trailerTypesLen = trailerTypes.Length; if (trailerTypesLen > 0) { - const string Or = " OR "; - StringBuilder clause = new StringBuilder("(", trailerTypesLen * 32); + clauseBuilder.Append('('); + for (int i = 0; i < trailerTypesLen; i++) { var paramName = "@TrailerTypes" + i; - clause.Append("TrailerTypes like ") + clauseBuilder.Append("TrailerTypes like ") .Append(paramName) .Append(Or); statement?.TryBind(paramName, "%" + trailerTypes[i] + "%"); } // Remove last " OR " - clause.Length -= Or.Length; - clause.Append(')'); + clauseBuilder.Length -= Or.Length; + clauseBuilder.Append(')'); - whereClauses.Add(clause.ToString()); + whereClauses.Add(clauseBuilder.ToString()); + + clauseBuilder.Length = 0; } if (query.IsAiring.HasValue) @@ -3757,23 +3726,35 @@ namespace Emby.Server.Implementations.Data } } - if (query.PersonIds.Length > 0) + int personIdsLen = query.PersonIds.Length; + if (personIdsLen > 0) { // TODO: Should this query with CleanName ? - var clauses = new List<string>(); - var index = 0; - foreach (var personId in query.PersonIds) + clauseBuilder.Append('('); + + Span<byte> idBytes = stackalloc byte[16]; + for (int i = 0; i < personIdsLen; i++) { - var paramName = "@PersonId" + index; + string paramName = "@PersonId" + i; + clauseBuilder.Append("(guid in (select itemid from People where Name = (select Name from TypedBaseItems where guid=") + .Append(paramName) + .Append("))) OR "); - clauses.Add("(guid in (select itemid from People where Name = (select Name from TypedBaseItems where guid=" + paramName + ")))"); - statement?.TryBind(paramName, personId.ToByteArray()); - index++; + if (statement != null) + { + query.PersonIds[i].TryWriteBytes(idBytes); + statement.TryBind(paramName, idBytes); + } } - var clause = "(" + string.Join(" OR ", clauses) + ")"; - whereClauses.Add(clause); + // Remove last " OR " + clauseBuilder.Length -= Or.Length; + clauseBuilder.Append(')'); + + whereClauses.Add(clauseBuilder.ToString()); + + clauseBuilder.Length = 0; } if (!string.IsNullOrWhiteSpace(query.Person)) @@ -3894,7 +3875,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], typeof(Series).Name, StringComparison.OrdinalIgnoreCase)) + if (query.IncludeItemTypes.Length == 1 && string.Equals(query.IncludeItemTypes[0], nameof(Series), StringComparison.OrdinalIgnoreCase)) { if (query.IsPlayed.Value) { @@ -4038,7 +4019,7 @@ namespace Emby.Server.Implementations.Data whereClauses.Add(clause); } - if (query.GenreIds.Length > 0) + if (query.GenreIds.Count > 0) { var clauses = new List<string>(); var index = 0; @@ -4059,7 +4040,7 @@ namespace Emby.Server.Implementations.Data whereClauses.Add(clause); } - if (query.Genres.Length > 0) + if (query.Genres.Count > 0) { var clauses = new List<string>(); var index = 0; @@ -4313,7 +4294,7 @@ namespace Emby.Server.Implementations.Data } else if (query.Years.Length > 1) { - var val = string.Join(",", query.Years); + var val = string.Join(',', query.Years); whereClauses.Add("ProductionYear in (" + val + ")"); } @@ -4363,7 +4344,7 @@ namespace Emby.Server.Implementations.Data } else if (queryMediaTypes.Length > 1) { - var val = string.Join(",", queryMediaTypes.Select(i => "'" + i + "'")); + var val = string.Join(',', queryMediaTypes.Select(i => "'" + i + "'")); whereClauses.Add("MediaType in (" + val + ")"); } @@ -4406,7 +4387,7 @@ namespace Emby.Server.Implementations.Data whereClauses.Add(string.Join(" AND ", excludeIds)); } - if (query.ExcludeProviderIds.Count > 0) + if (query.ExcludeProviderIds != null && query.ExcludeProviderIds.Count > 0) { var excludeIds = new List<string>(); @@ -4436,7 +4417,7 @@ namespace Emby.Server.Implementations.Data } } - if (query.HasAnyProviderId.Count > 0) + if (query.HasAnyProviderId != null && query.HasAnyProviderId.Count > 0) { var hasProviderIds = new List<string>(); @@ -4460,7 +4441,7 @@ namespace Emby.Server.Implementations.Data var paramName = "@HasAnyProviderId" + index; // this is a search for the placeholder - hasProviderIds.Add("ProviderIds like " + paramName + ""); + hasProviderIds.Add("ProviderIds like " + paramName); // this replaces the placeholder with a value, here: %key=val% if (statement != null) @@ -4481,69 +4462,63 @@ namespace Emby.Server.Implementations.Data if (query.HasImdbId.HasValue) { - whereClauses.Add("ProviderIds like '%imdb=%'"); + whereClauses.Add(GetProviderIdClause(query.HasImdbId.Value, "imdb")); } if (query.HasTmdbId.HasValue) { - whereClauses.Add("ProviderIds like '%tmdb=%'"); + whereClauses.Add(GetProviderIdClause(query.HasTmdbId.Value, "tmdb")); } if (query.HasTvdbId.HasValue) { - whereClauses.Add("ProviderIds like '%tvdb=%'"); + whereClauses.Add(GetProviderIdClause(query.HasTvdbId.Value, "tvdb")); } - var includedItemByNameTypes = GetItemByNameTypesInQuery(query).SelectMany(MapIncludeItemTypes).ToList(); - var enableItemsByName = (query.IncludeItemsByName ?? false) && includedItemByNameTypes.Count > 0; - var queryTopParentIds = query.TopParentIds; - if (queryTopParentIds.Length == 1) + if (queryTopParentIds.Length > 0) { - if (enableItemsByName && includedItemByNameTypes.Count == 1) + var includedItemByNameTypes = GetItemByNameTypesInQuery(query); + var enableItemsByName = (query.IncludeItemsByName ?? false) && includedItemByNameTypes.Count > 0; + + if (queryTopParentIds.Length == 1) { - whereClauses.Add("(TopParentId=@TopParentId or Type=@IncludedItemByNameType)"); - if (statement != null) + if (enableItemsByName && includedItemByNameTypes.Count == 1) { - statement.TryBind("@IncludedItemByNameType", includedItemByNameTypes[0]); + whereClauses.Add("(TopParentId=@TopParentId or Type=@IncludedItemByNameType)"); + statement?.TryBind("@IncludedItemByNameType", includedItemByNameTypes[0]); + } + else if (enableItemsByName && includedItemByNameTypes.Count > 1) + { + var itemByNameTypeVal = string.Join(',', includedItemByNameTypes.Select(i => "'" + i + "'")); + whereClauses.Add("(TopParentId=@TopParentId or Type in (" + itemByNameTypeVal + "))"); + } + else + { + whereClauses.Add("(TopParentId=@TopParentId)"); } - } - else if (enableItemsByName && includedItemByNameTypes.Count > 1) - { - var itemByNameTypeVal = string.Join(",", includedItemByNameTypes.Select(i => "'" + i + "'")); - whereClauses.Add("(TopParentId=@TopParentId or Type in (" + itemByNameTypeVal + "))"); - } - else - { - whereClauses.Add("(TopParentId=@TopParentId)"); - } - if (statement != null) - { - statement.TryBind("@TopParentId", queryTopParentIds[0].ToString("N", CultureInfo.InvariantCulture)); + statement?.TryBind("@TopParentId", queryTopParentIds[0].ToString("N", CultureInfo.InvariantCulture)); } - } - else if (queryTopParentIds.Length > 1) - { - var val = string.Join(",", queryTopParentIds.Select(i => "'" + i.ToString("N", CultureInfo.InvariantCulture) + "'")); - - if (enableItemsByName && includedItemByNameTypes.Count == 1) + else if (queryTopParentIds.Length > 1) { - whereClauses.Add("(Type=@IncludedItemByNameType or TopParentId in (" + val + "))"); - if (statement != null) + var val = string.Join(',', queryTopParentIds.Select(i => "'" + i.ToString("N", CultureInfo.InvariantCulture) + "'")); + + if (enableItemsByName && includedItemByNameTypes.Count == 1) { - statement.TryBind("@IncludedItemByNameType", includedItemByNameTypes[0]); + whereClauses.Add("(Type=@IncludedItemByNameType or TopParentId in (" + val + "))"); + statement?.TryBind("@IncludedItemByNameType", includedItemByNameTypes[0]); + } + else if (enableItemsByName && includedItemByNameTypes.Count > 1) + { + var itemByNameTypeVal = string.Join(',', includedItemByNameTypes.Select(i => "'" + i + "'")); + whereClauses.Add("(Type in (" + itemByNameTypeVal + ") or TopParentId in (" + val + "))"); + } + else + { + whereClauses.Add("TopParentId in (" + val + ")"); } - } - else if (enableItemsByName && includedItemByNameTypes.Count > 1) - { - var itemByNameTypeVal = string.Join(",", includedItemByNameTypes.Select(i => "'" + i + "'")); - whereClauses.Add("(Type in (" + itemByNameTypeVal + ") or TopParentId in (" + val + "))"); - } - else - { - whereClauses.Add("TopParentId in (" + val + ")"); } } @@ -4559,7 +4534,7 @@ namespace Emby.Server.Implementations.Data if (query.AncestorIds.Length > 1) { - var inClause = string.Join(",", query.AncestorIds.Select(i => "'" + i.ToString("N", CultureInfo.InvariantCulture) + "'")); + var inClause = string.Join(',', query.AncestorIds.Select(i => "'" + i.ToString("N", CultureInfo.InvariantCulture) + "'")); whereClauses.Add(string.Format(CultureInfo.InvariantCulture, "Guid in (select itemId from AncestorIds where AncestorIdText in ({0}))", inClause)); } @@ -4731,33 +4706,48 @@ namespace Emby.Server.Implementations.Data return whereClauses; } + /// <summary> + /// Formats a where clause for the specified provider. + /// </summary> + /// <param name="includeResults">Whether or not to include items with this provider's ids.</param> + /// <param name="provider">Provider name.</param> + /// <returns>Formatted SQL clause.</returns> + private string GetProviderIdClause(bool includeResults, string provider) + { + return string.Format( + CultureInfo.InvariantCulture, + "ProviderIds {0} like '%{1}=%'", + includeResults ? string.Empty : "not", + provider); + } + private List<string> GetItemByNameTypesInQuery(InternalItemsQuery query) { var list = new List<string>(); - if (IsTypeInQuery(typeof(Person).Name, query)) + if (IsTypeInQuery(nameof(Person), query)) { - list.Add(typeof(Person).Name); + list.Add(typeof(Person).FullName); } - if (IsTypeInQuery(typeof(Genre).Name, query)) + if (IsTypeInQuery(nameof(Genre), query)) { - list.Add(typeof(Genre).Name); + list.Add(typeof(Genre).FullName); } - if (IsTypeInQuery(typeof(MusicGenre).Name, query)) + if (IsTypeInQuery(nameof(MusicGenre), query)) { - list.Add(typeof(MusicGenre).Name); + list.Add(typeof(MusicGenre).FullName); } - if (IsTypeInQuery(typeof(MusicArtist).Name, query)) + if (IsTypeInQuery(nameof(MusicArtist), query)) { - list.Add(typeof(MusicArtist).Name); + list.Add(typeof(MusicArtist).FullName); } - if (IsTypeInQuery(typeof(Studio).Name, query)) + if (IsTypeInQuery(nameof(Studio), query)) { - list.Add(typeof(Studio).Name); + list.Add(typeof(Studio).FullName); } return list; @@ -4810,17 +4800,12 @@ namespace Emby.Server.Implementations.Data return true; } - var types = new[] - { - typeof(Episode).Name, - typeof(Video).Name, - typeof(Movie).Name, - typeof(MusicVideo).Name, - typeof(Series).Name, - typeof(Season).Name - }; - - if (types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase))) + 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; } @@ -4862,15 +4847,10 @@ namespace Emby.Server.Implementations.Data typeof(AggregateFolder) }; - public void UpdateInheritedValues(CancellationToken cancellationToken) - { - UpdateInheritedTags(cancellationToken); - } - - private void UpdateInheritedTags(CancellationToken cancellationToken) + public void UpdateInheritedValues() { string sql = string.Join( - ";", + ';', new string[] { "delete from itemvalues where type = 6", @@ -4885,44 +4865,46 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type using (var connection = GetConnection()) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { connection.ExecuteAll(sql); }, TransactionMode); } } - private static Dictionary<string, string[]> GetTypeMapDictionary() + private static Dictionary<string, string> GetTypeMapDictionary() { - var dict = new Dictionary<string, string[]>(StringComparer.OrdinalIgnoreCase); + var dict = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase); foreach (var t in _knownTypes) { - dict[t.Name] = new[] { t.FullName }; + dict[t.Name] = t.FullName ; } - dict["Program"] = new[] { typeof(LiveTvProgram).FullName }; - dict["TvChannel"] = new[] { typeof(LiveTvChannel).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 readonly Dictionary<string, string> _types = GetTypeMapDictionary(); - private string[] MapIncludeItemTypes(string value) + private string MapIncludeItemTypes(string value) { - if (_types.TryGetValue(value, out string[] result)) + if (_types.TryGetValue(value, out string result)) { return result; } if (IsValidType(value)) { - return new[] { value }; + return value; } - return Array.Empty<string>(); + Logger.LogWarning("Unknown item type: {ItemType}", value); + return null; } public void DeleteItem(Guid id) @@ -4936,7 +4918,8 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type using (var connection = GetConnection()) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { var idBlob = id.ToByteArray(); @@ -4980,26 +4963,26 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type CheckDisposed(); - var commandText = "select Distinct Name from People"; + var commandText = new StringBuilder("select Distinct p.Name from People p"); var whereClauses = GetPeopleWhereClauses(query, null); if (whereClauses.Count != 0) { - commandText += " where " + string.Join(" AND ", whereClauses); + commandText.Append(" where ").Append(string.Join(" 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<string>(); - using (var statement = PrepareStatement(connection, commandText)) + using (var statement = PrepareStatement(connection, commandText.ToString())) { // Run this again to bind the params GetPeopleWhereClauses(query, statement); @@ -5023,7 +5006,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type CheckDisposed(); - var commandText = "select ItemId, Name, Role, PersonType, SortOrder from People"; + var commandText = "select ItemId, Name, Role, PersonType, SortOrder from People p"; var whereClauses = GetPeopleWhereClauses(query, null); @@ -5062,22 +5045,26 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type { var whereClauses = new List<string>(); + if (query.User != null && query.IsFavorite.HasValue) + { + whereClauses.Add(@"p.Name IN ( +SELECT Name FROM TypedBaseItems WHERE UserDataKey IN ( +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); + } + if (!query.ItemId.Equals(Guid.Empty)) { whereClauses.Add("ItemId=@ItemId"); - if (statement != null) - { - statement.TryBind("@ItemId", query.ItemId.ToByteArray()); - } + statement?.TryBind("@ItemId", query.ItemId.ToByteArray()); } if (!query.AppearsInItemId.Equals(Guid.Empty)) { - whereClauses.Add("Name in (Select Name from People where ItemId=@AppearsInItemId)"); - if (statement != null) - { - statement.TryBind("@AppearsInItemId", query.AppearsInItemId.ToByteArray()); - } + whereClauses.Add("p.Name in (Select Name from People where ItemId=@AppearsInItemId)"); + statement?.TryBind("@AppearsInItemId", query.AppearsInItemId.ToByteArray()); } var queryPersonTypes = query.PersonTypes.Where(IsValidPersonType).ToList(); @@ -5085,14 +5072,11 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type if (queryPersonTypes.Count == 1) { whereClauses.Add("PersonType=@PersonType"); - if (statement != null) - { - statement.TryBind("@PersonType", queryPersonTypes[0]); - } + statement?.TryBind("@PersonType", queryPersonTypes[0]); } else if (queryPersonTypes.Count > 1) { - var val = string.Join(",", queryPersonTypes.Select(i => "'" + i + "'")); + var val = string.Join(',', queryPersonTypes.Select(i => "'" + i + "'")); whereClauses.Add("PersonType in (" + val + ")"); } @@ -5102,14 +5086,11 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type if (queryExcludePersonTypes.Count == 1) { whereClauses.Add("PersonType<>@PersonType"); - if (statement != null) - { - statement.TryBind("@PersonType", queryExcludePersonTypes[0]); - } + statement?.TryBind("@PersonType", queryExcludePersonTypes[0]); } else if (queryExcludePersonTypes.Count > 1) { - var val = string.Join(",", queryExcludePersonTypes.Select(i => "'" + i + "'")); + var val = string.Join(',', queryExcludePersonTypes.Select(i => "'" + i + "'")); whereClauses.Add("PersonType not in (" + val + ")"); } @@ -5117,19 +5098,18 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type if (query.MaxListOrder.HasValue) { whereClauses.Add("ListOrder<=@MaxListOrder"); - if (statement != null) - { - statement.TryBind("@MaxListOrder", query.MaxListOrder.Value); - } + statement?.TryBind("@MaxListOrder", query.MaxListOrder.Value); } if (!string.IsNullOrWhiteSpace(query.NameContains)) { - whereClauses.Add("Name like @NameContains"); - if (statement != null) - { - statement.TryBind("@NameContains", "%" + query.NameContains + "%"); - } + whereClauses.Add("p.Name like @NameContains"); + statement?.TryBind("@NameContains", "%" + query.NameContains + "%"); + } + + if (query.User != null) + { + statement?.TryBind("@UserId", query.User.InternalId); } return whereClauses; @@ -5149,7 +5129,8 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type CheckDisposed(); - var itemIdBlob = itemId.ToByteArray(); + Span<byte> itemIdBlob = stackalloc byte[16]; + itemId.TryWriteBytes(itemIdBlob); // First delete deleteAncestorsStatement.Reset(); @@ -5165,17 +5146,15 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type for (var i = 0; i < ancestorIds.Count; i++) { - if (i > 0) - { - insertText.Append(','); - } - insertText.AppendFormat( CultureInfo.InvariantCulture, - "(@ItemId, @AncestorId{0}, @AncestorIdText{0})", + "(@ItemId, @AncestorId{0}, @AncestorIdText{0}),", i.ToString(CultureInfo.InvariantCulture)); } + // Remove last , + insertText.Length--; + using (var statement = PrepareStatement(db, insertText.ToString())) { statement.TryBind("@ItemId", itemIdBlob); @@ -5185,8 +5164,9 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type var index = i.ToString(CultureInfo.InvariantCulture); var ancestorId = ancestorIds[i]; + ancestorId.TryWriteBytes(itemIdBlob); - statement.TryBind("@AncestorId" + index, ancestorId.ToByteArray()); + statement.TryBind("@AncestorId" + index, itemIdBlob); statement.TryBind("@AncestorIdText" + index, ancestorId.ToString("N", CultureInfo.InvariantCulture)); } @@ -5227,64 +5207,87 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type public List<string> GetStudioNames() { - return GetItemValueNames(new[] { 3 }, new List<string>(), new List<string>()); + return GetItemValueNames(new[] { 3 }, Array.Empty<string>(), Array.Empty<string>()); } public List<string> GetAllArtistNames() { - return GetItemValueNames(new[] { 0, 1 }, new List<string>(), new List<string>()); + return GetItemValueNames(new[] { 0, 1 }, Array.Empty<string>(), Array.Empty<string>()); } public List<string> GetMusicGenreNames() { - return GetItemValueNames(new[] { 2 }, new List<string> { "Audio", "MusicVideo", "MusicAlbum", "MusicArtist" }, new List<string>()); + return GetItemValueNames( + new[] { 2 }, + new string[] + { + typeof(Audio).FullName, + typeof(MusicVideo).FullName, + typeof(MusicAlbum).FullName, + typeof(MusicArtist).FullName + }, + Array.Empty<string>()); } public List<string> GetGenreNames() { - return GetItemValueNames(new[] { 2 }, new List<string>(), new List<string> { "Audio", "MusicVideo", "MusicAlbum", "MusicArtist" }); + return GetItemValueNames( + new[] { 2 }, + Array.Empty<string>(), + new string[] + { + typeof(Audio).FullName, + typeof(MusicVideo).FullName, + typeof(MusicAlbum).FullName, + typeof(MusicArtist).FullName + }); } - private List<string> GetItemValueNames(int[] itemValueTypes, List<string> withItemTypes, List<string> excludeItemTypes) + private List<string> GetItemValueNames(int[] itemValueTypes, IReadOnlyList<string> withItemTypes, IReadOnlyList<string> excludeItemTypes) { CheckDisposed(); - withItemTypes = withItemTypes.SelectMany(MapIncludeItemTypes).ToList(); - excludeItemTypes = excludeItemTypes.SelectMany(MapIncludeItemTypes).ToList(); - var now = DateTime.UtcNow; - var typeClause = itemValueTypes.Length == 1 ? - ("Type=" + itemValueTypes[0].ToString(CultureInfo.InvariantCulture)) : - ("Type in (" + string.Join(",", itemValueTypes.Select(i => i.ToString(CultureInfo.InvariantCulture))) + ")"); - - var commandText = "Select Value From ItemValues where " + typeClause; + var stringBuilder = new StringBuilder("Select Value From ItemValues where Type", 128); + if (itemValueTypes.Length == 1) + { + stringBuilder.Append('=') + .Append(itemValueTypes[0]); + } + else + { + stringBuilder.Append(" in (") + .AppendJoin(',', itemValueTypes) + .Append(')'); + } if (withItemTypes.Count > 0) { - var typeString = string.Join(",", withItemTypes.Select(i => "'" + i + "'")); - commandText += " AND ItemId In (select guid from typedbaseitems where type in (" + typeString + "))"; + stringBuilder.Append(" AND ItemId In (select guid from typedbaseitems where type in (") + .AppendJoinInSingleQuotes(',', withItemTypes) + .Append("))"); } if (excludeItemTypes.Count > 0) { - var typeString = string.Join(",", excludeItemTypes.Select(i => "'" + i + "'")); - commandText += " AND ItemId not In (select guid from typedbaseitems where type in (" + typeString + "))"; + stringBuilder.Append(" AND ItemId not In (select guid from typedbaseitems where type in (") + .AppendJoinInSingleQuotes(',', excludeItemTypes) + .Append("))"); } - commandText += " Group By CleanValue"; + stringBuilder.Append(" Group By CleanValue"); + var commandText = stringBuilder.ToString(); var list = new List<string>(); using (var connection = GetConnection(true)) + using (var statement = PrepareStatement(connection, commandText)) { - using (var statement = PrepareStatement(connection, commandText)) + foreach (var row in statement.ExecuteQuery()) { - foreach (var row in statement.ExecuteQuery()) + if (row.TryGetString(0, out var result)) { - if (!row.IsDBNull(0)) - { - list.Add(row.GetString(0)); - } + list.Add(result); } } } @@ -5310,18 +5313,19 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type var now = DateTime.UtcNow; var typeClause = itemValueTypes.Length == 1 ? - ("Type=" + itemValueTypes[0].ToString(CultureInfo.InvariantCulture)) : - ("Type in (" + string.Join(",", itemValueTypes.Select(i => i.ToString(CultureInfo.InvariantCulture))) + ")"); + ("Type=" + itemValueTypes[0]) : + ("Type in (" + string.Join(',', itemValueTypes) + ")"); InternalItemsQuery typeSubQuery = null; - Dictionary<string, string> itemCountColumns = null; + string itemCountColumns = null; + var stringBuilder = new StringBuilder(1024); var typesToCount = query.IncludeItemTypes; if (typesToCount.Length > 0) { - var itemCountColumnQuery = "select group_concat(type, '|')" + GetFromText("B"); + stringBuilder.Append("(select group_concat(type, '|') from TypedBaseItems B"); typeSubQuery = new InternalItemsQuery(query.User) { @@ -5337,20 +5341,22 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type }; var whereClauses = GetWhereClauses(typeSubQuery, null); - whereClauses.Add("guid in (select ItemId from ItemValues where ItemValues.CleanValue=A.CleanName AND " + typeClause + ")"); - - itemCountColumnQuery += " where " + string.Join(" AND ", whereClauses); + stringBuilder.Append(" where ") + .AppendJoin(" AND ", whereClauses) + .Append(" AND ") + .Append("guid in (select ItemId from ItemValues where ItemValues.CleanValue=A.CleanName AND ") + .Append(typeClause) + .Append(")) as itemTypes"); - itemCountColumns = new Dictionary<string, string>() - { - { "itemTypes", "(" + itemCountColumnQuery + ") as itemTypes"} - }; + itemCountColumns = stringBuilder.ToString(); + stringBuilder.Clear(); } List<string> columns = _retriveItemColumns.ToList(); - if (itemCountColumns != null) + // Unfortunately we need to add it to columns to ensure the order of the columns in the select + if (!string.IsNullOrEmpty(itemCountColumns)) { - columns.AddRange(itemCountColumns.Values); + columns.Add(itemCountColumns); } // do this first before calling GetFinalColumnsToSelect, otherwise ExcludeItemIds will be set by SimilarTo @@ -5363,7 +5369,6 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type ItemIds = query.ItemIds, TopParentIds = query.TopParentIds, ParentId = query.ParentId, - IsPlayed = query.IsPlayed, IsAiring = query.IsAiring, IsMovie = query.IsMovie, IsSports = query.IsSports, @@ -5372,23 +5377,24 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type IsSeries = query.IsSeries }; - columns = GetFinalColumnsToSelect(query, columns); - - var commandText = "select " - + string.Join(",", columns) - + GetFromText() - + GetJoinUserDataText(query); + SetFinalColumnsToSelect(query, columns); var innerWhereClauses = GetWhereClauses(innerQuery, null); - var innerWhereText = innerWhereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", innerWhereClauses); + stringBuilder.Append(" where Type=@SelectType And CleanName In (Select CleanValue from ItemValues where ") + .Append(typeClause) + .Append(" AND ItemId in (select guid from TypedBaseItems"); + if (innerWhereClauses.Count > 0) + { + stringBuilder.Append(" where ") + .AppendJoin(" AND ", innerWhereClauses); + } - var whereText = " where Type=@SelectType And CleanName In (Select CleanValue from ItemValues where " + typeClause + " AND ItemId in (select guid from TypedBaseItems" + innerWhereText + "))"; + stringBuilder.Append("))"); var outerQuery = new InternalItemsQuery(query.User) { + IsPlayed = query.IsPlayed, IsFavorite = query.IsFavorite, IsFavoriteOrLiked = query.IsFavoriteOrLiked, IsLiked = query.IsLiked, @@ -5398,6 +5404,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type NameStartsWithOrGreater = query.NameStartsWithOrGreater, Tags = query.Tags, OfficialRatings = query.OfficialRatings, + StudioIds = query.StudioIds, GenreIds = query.GenreIds, Genres = query.Genres, Years = query.Years, @@ -5408,21 +5415,31 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type }; var outerWhereClauses = GetWhereClauses(outerQuery, null); - if (outerWhereClauses.Count != 0) { - whereText += " AND " + string.Join(" AND ", outerWhereClauses); + stringBuilder.Append(" AND ") + .AppendJoin(" AND ", outerWhereClauses); } - commandText += whereText + " group by PresentationUniqueKey"; + var whereText = stringBuilder.ToString(); + stringBuilder.Clear(); + + stringBuilder.Append("select ") + .AppendJoin(',', columns) + .Append(FromText) + .Append(GetJoinUserDataText(query)) + .Append(whereText) + .Append(" group by PresentationUniqueKey"); - if (query.SimilarTo != null || !string.IsNullOrEmpty(query.SearchTerm)) + if (query.OrderBy.Count != 0 + || query.SimilarTo != null + || !string.IsNullOrEmpty(query.SearchTerm)) { - commandText += GetOrderByText(query); + stringBuilder.Append(GetOrderByText(query)); } else { - commandText += " order by SortName"; + stringBuilder.Append(" order by SortName"); } if (query.Limit.HasValue || query.StartIndex.HasValue) @@ -5431,32 +5448,39 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type if (query.Limit.HasValue || offset > 0) { - commandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + stringBuilder.Append(" LIMIT ") + .Append(query.Limit ?? int.MaxValue); } if (offset > 0) { - commandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); + stringBuilder.Append(" OFFSET ") + .Append(offset); } } var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; - var statementTexts = new List<string>(); + string commandText = string.Empty; + if (!isReturningZeroItems) { - statementTexts.Add(commandText); + commandText = stringBuilder.ToString(); } + string countText = string.Empty; if (query.EnableTotalRecordCount) { - var countText = "select " - + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct PresentationUniqueKey)" })) - + GetFromText() - + GetJoinUserDataText(query) - + whereText; + stringBuilder.Clear(); + var columnsToSelect = new List<string> { "count (distinct PresentationUniqueKey)" }; + SetFinalColumnsToSelect(query, columnsToSelect); + stringBuilder.Append("select ") + .AppendJoin(',', columnsToSelect) + .Append(FromText) + .Append(GetJoinUserDataText(query)) + .Append(whereText); - statementTexts.Add(countText); + countText = stringBuilder.ToString(); } var list = new List<(BaseItem, ItemCounts)>(); @@ -5466,11 +5490,9 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type connection.RunInTransaction( db => { - var statements = PrepareAll(db, statementTexts).ToList(); - if (!isReturningZeroItems) { - using (var statement = statements[0]) + using (var statement = PrepareStatement(db, commandText)) { statement.TryBind("@SelectType", returnType); if (EnableJoinUserData(query)) @@ -5511,13 +5533,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type if (query.EnableTotalRecordCount) { - commandText = "select " - + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct PresentationUniqueKey)" })) - + GetFromText() - + GetJoinUserDataText(query) - + whereText; - - using (var statement = statements[statements.Count - 1]) + using (var statement = PrepareStatement(db, countText)) { statement.TryBind("@SelectType", returnType); if (EnableJoinUserData(query)) @@ -5554,7 +5570,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type return result; } - private ItemCounts GetItemCounts(IReadOnlyList<IResultSetValue> reader, int countStartColumn, string[] typesToCount) + private static ItemCounts GetItemCounts(IReadOnlyList<ResultSetValue> reader, int countStartColumn, string[] typesToCount) { var counts = new ItemCounts(); @@ -5563,51 +5579,43 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type return counts; } - var typeString = reader.IsDBNull(countStartColumn) ? null : reader.GetString(countStartColumn); - - if (string.IsNullOrWhiteSpace(typeString)) + if (!reader.TryGetString(countStartColumn, out var typeString)) { return counts; } - var allTypes = typeString.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries) - .ToLookup(x => x); - - foreach (var type in allTypes) + foreach (var typeName in typeString.AsSpan().Split('|')) { - var value = type.Count(); - var typeName = type.Key; - - if (string.Equals(typeName, typeof(Series).FullName, StringComparison.OrdinalIgnoreCase)) + if (typeName.Equals(typeof(Series).FullName, StringComparison.OrdinalIgnoreCase)) { - counts.SeriesCount = value; + counts.SeriesCount++; } - else if (string.Equals(typeName, typeof(Episode).FullName, StringComparison.OrdinalIgnoreCase)) + else if (typeName.Equals(typeof(Episode).FullName, StringComparison.OrdinalIgnoreCase)) { - counts.EpisodeCount = value; + counts.EpisodeCount++; } - else if (string.Equals(typeName, typeof(Movie).FullName, StringComparison.OrdinalIgnoreCase)) + else if (typeName.Equals(typeof(Movie).FullName, StringComparison.OrdinalIgnoreCase)) { - counts.MovieCount = value; + counts.MovieCount++; } - else if (string.Equals(typeName, typeof(MusicAlbum).FullName, StringComparison.OrdinalIgnoreCase)) + else if (typeName.Equals(typeof(MusicAlbum).FullName, StringComparison.OrdinalIgnoreCase)) { - counts.AlbumCount = value; + counts.AlbumCount++; } - else if (string.Equals(typeName, typeof(MusicArtist).FullName, StringComparison.OrdinalIgnoreCase)) + else if (typeName.Equals(typeof(MusicArtist).FullName, StringComparison.OrdinalIgnoreCase)) { - counts.ArtistCount = value; + counts.ArtistCount++; } - else if (string.Equals(typeName, typeof(Audio).FullName, StringComparison.OrdinalIgnoreCase)) + else if (typeName.Equals(typeof(Audio).FullName, StringComparison.OrdinalIgnoreCase)) { - counts.SongCount = value; + counts.SongCount++; } - else if (string.Equals(typeName, typeof(Trailer).FullName, StringComparison.OrdinalIgnoreCase)) + else if (typeName.Equals(typeof(Trailer).FullName, StringComparison.OrdinalIgnoreCase)) { - counts.TrailerCount = value; + counts.TrailerCount++; } - counts.ItemCount += value; + counts.ItemCount++; } return counts; @@ -5730,7 +5738,8 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type using (var connection = GetConnection()) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { var itemIdBlob = itemId.ToByteArray(); @@ -5755,7 +5764,10 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type var endIndex = Math.Min(people.Count, startIndex + Limit); for (var i = startIndex; i < endIndex; i++) { - insertText.AppendFormat("(@ItemId, @Name{0}, @Role{0}, @PersonType{0}, @SortOrder{0}, @ListOrder{0}),", i.ToString(CultureInfo.InvariantCulture)); + insertText.AppendFormat( + CultureInfo.InvariantCulture, + "(@ItemId, @Name{0}, @Role{0}, @PersonType{0}, @SortOrder{0}, @ListOrder{0}),", + i.ToString(CultureInfo.InvariantCulture)); } // Remove last comma @@ -5789,7 +5801,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } } - private PersonInfo GetPerson(IReadOnlyList<IResultSetValue> reader) + private PersonInfo GetPerson(IReadOnlyList<ResultSetValue> reader) { var item = new PersonInfo { @@ -5797,19 +5809,19 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type Name = reader.GetString(1) }; - if (!reader.IsDBNull(2)) + if (reader.TryGetString(2, out var role)) { - item.Role = reader.GetString(2); + item.Role = role; } - if (!reader.IsDBNull(3)) + if (reader.TryGetString(3, out var type)) { - item.Type = reader.GetString(3); + item.Type = type; } - if (!reader.IsDBNull(4)) + if (reader.TryGetInt32(4, out var sortOrder)) { - item.SortOrder = reader.GetInt32(4); + item.SortOrder = sortOrder; } return item; @@ -5884,7 +5896,8 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type using (var connection = GetConnection()) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { var itemIdBlob = id.ToByteArray(); @@ -5990,13 +6003,12 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } } - /// <summary> /// Gets the chapter. /// </summary> /// <param name="reader">The reader.</param> /// <returns>ChapterInfo.</returns> - private MediaStream GetMediaStream(IReadOnlyList<IResultSetValue> reader) + private MediaStream GetMediaStream(IReadOnlyList<ResultSetValue> reader) { var item = new MediaStream { @@ -6005,157 +6017,157 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type item.Type = Enum.Parse<MediaStreamType>(reader[2].ToString(), true); - if (reader[3].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(3, out var codec)) { - item.Codec = reader[3].ToString(); + item.Codec = codec; } - if (reader[4].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(4, out var language)) { - item.Language = reader[4].ToString(); + item.Language = language; } - if (reader[5].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(5, out var channelLayout)) { - item.ChannelLayout = reader[5].ToString(); + item.ChannelLayout = channelLayout; } - if (reader[6].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(6, out var profile)) { - item.Profile = reader[6].ToString(); + item.Profile = profile; } - if (reader[7].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(7, out var aspectRatio)) { - item.AspectRatio = reader[7].ToString(); + item.AspectRatio = aspectRatio; } - if (reader[8].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(8, out var path)) { - item.Path = RestorePath(reader[8].ToString()); + item.Path = RestorePath(path); } item.IsInterlaced = reader.GetBoolean(9); - if (reader[10].SQLiteType != SQLiteType.Null) + if (reader.TryGetInt32(10, out var bitrate)) { - item.BitRate = reader.GetInt32(10); + item.BitRate = bitrate; } - if (reader[11].SQLiteType != SQLiteType.Null) + if (reader.TryGetInt32(11, out var channels)) { - item.Channels = reader.GetInt32(11); + item.Channels = channels; } - if (reader[12].SQLiteType != SQLiteType.Null) + if (reader.TryGetInt32(12, out var sampleRate)) { - item.SampleRate = reader.GetInt32(12); + item.SampleRate = sampleRate; } item.IsDefault = reader.GetBoolean(13); item.IsForced = reader.GetBoolean(14); item.IsExternal = reader.GetBoolean(15); - if (reader[16].SQLiteType != SQLiteType.Null) + if (reader.TryGetInt32(16, out var width)) { - item.Width = reader.GetInt32(16); + item.Width = width; } - if (reader[17].SQLiteType != SQLiteType.Null) + if (reader.TryGetInt32(17, out var height)) { - item.Height = reader.GetInt32(17); + item.Height = height; } - if (reader[18].SQLiteType != SQLiteType.Null) + if (reader.TryGetSingle(18, out var averageFrameRate)) { - item.AverageFrameRate = reader.GetFloat(18); + item.AverageFrameRate = averageFrameRate; } - if (reader[19].SQLiteType != SQLiteType.Null) + if (reader.TryGetSingle(19, out var realFrameRate)) { - item.RealFrameRate = reader.GetFloat(19); + item.RealFrameRate = realFrameRate; } - if (reader[20].SQLiteType != SQLiteType.Null) + if (reader.TryGetSingle(20, out var level)) { - item.Level = reader.GetFloat(20); + item.Level = level; } - if (reader[21].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(21, out var pixelFormat)) { - item.PixelFormat = reader[21].ToString(); + item.PixelFormat = pixelFormat; } - if (reader[22].SQLiteType != SQLiteType.Null) + if (reader.TryGetInt32(22, out var bitDepth)) { - item.BitDepth = reader.GetInt32(22); + item.BitDepth = bitDepth; } - if (reader[23].SQLiteType != SQLiteType.Null) + if (reader.TryGetBoolean(23, out var isAnamorphic)) { - item.IsAnamorphic = reader.GetBoolean(23); + item.IsAnamorphic = isAnamorphic; } - if (reader[24].SQLiteType != SQLiteType.Null) + if (reader.TryGetInt32(24, out var refFrames)) { - item.RefFrames = reader.GetInt32(24); + item.RefFrames = refFrames; } - if (reader[25].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(25, out var codecTag)) { - item.CodecTag = reader.GetString(25); + item.CodecTag = codecTag; } - if (reader[26].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(26, out var comment)) { - item.Comment = reader.GetString(26); + item.Comment = comment; } - if (reader[27].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(27, out var nalLengthSize)) { - item.NalLengthSize = reader.GetString(27); + item.NalLengthSize = nalLengthSize; } - if (reader[28].SQLiteType != SQLiteType.Null) + if (reader.TryGetBoolean(28, out var isAVC)) { - item.IsAVC = reader[28].ToBool(); + item.IsAVC = isAVC; } - if (reader[29].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(29, out var title)) { - item.Title = reader[29].ToString(); + item.Title = title; } - if (reader[30].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(30, out var timeBase)) { - item.TimeBase = reader[30].ToString(); + item.TimeBase = timeBase; } - if (reader[31].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(31, out var codecTimeBase)) { - item.CodecTimeBase = reader[31].ToString(); + item.CodecTimeBase = codecTimeBase; } - if (reader[32].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(32, out var colorPrimaries)) { - item.ColorPrimaries = reader[32].ToString(); + item.ColorPrimaries = colorPrimaries; } - if (reader[33].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(33, out var colorSpace)) { - item.ColorSpace = reader[33].ToString(); + item.ColorSpace = colorSpace; } - if (reader[34].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(34, out var colorTransfer)) { - item.ColorTransfer = reader[34].ToString(); + item.ColorTransfer = colorTransfer; } if (item.Type == MediaStreamType.Subtitle) { - item.localizedUndefined = _localization.GetLocalizedString("Undefined"); - item.localizedDefault = _localization.GetLocalizedString("Default"); - item.localizedForced = _localization.GetLocalizedString("Forced"); + item.LocalizedUndefined = _localization.GetLocalizedString("Undefined"); + item.LocalizedDefault = _localization.GetLocalizedString("Default"); + item.LocalizedForced = _localization.GetLocalizedString("Forced"); } return item; @@ -6207,7 +6219,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type CheckDisposed(); if (id == Guid.Empty) { - throw new ArgumentException(nameof(id)); + throw new ArgumentException("Guid can't be empty.", nameof(id)); } if (attachments == null) @@ -6219,7 +6231,8 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type using (var connection = GetConnection()) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { var itemIdBlob = id.ToByteArray(); @@ -6296,36 +6309,36 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type /// </summary> /// <param name="reader">The reader.</param> /// <returns>MediaAttachment.</returns> - private MediaAttachment GetMediaAttachment(IReadOnlyList<IResultSetValue> reader) + private MediaAttachment GetMediaAttachment(IReadOnlyList<ResultSetValue> reader) { var item = new MediaAttachment { Index = reader[1].ToInt() }; - if (reader[2].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(2, out var codec)) { - item.Codec = reader[2].ToString(); + item.Codec = codec; } - if (reader[2].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(3, out var codecTag)) { - item.CodecTag = reader[3].ToString(); + item.CodecTag = codecTag; } - if (reader[4].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(4, out var comment)) { - item.Comment = reader[4].ToString(); + item.Comment = comment; } - if (reader[6].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(5, out var fileName)) { - item.FileName = reader[5].ToString(); + item.FileName = fileName; } - if (reader[6].SQLiteType != SQLiteType.Null) + if (reader.TryGetString(6, out var mimeType)) { - item.MimeType = reader[6].ToString(); + item.MimeType = mimeType; } return item; diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index 4a78aac8e..ef9af1dcd 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -1,3 +1,5 @@ +#nullable disable + #pragma warning disable CS1591 using System; @@ -44,9 +46,10 @@ namespace Emby.Server.Implementations.Data var users = userDatasTableExists ? null : userManager.Users; - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { - db.ExecuteAll(string.Join(";", new[] { + db.ExecuteAll(string.Join(';', new[] { "create table if not exists UserDatas (key nvarchar not null, userId INT not null, rating float null, played bit not null, playCount int not null, isFavorite bit not null, playbackPositionTicks bigint not null, lastPlayedDate datetime null, AudioStreamIndex INT, SubtitleStreamIndex INT)", @@ -178,7 +181,8 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection()) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { SaveUserData(db, internalUserId, key, userData); }, TransactionMode); @@ -246,7 +250,8 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection()) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { foreach (var userItemData in userDataList) { @@ -345,16 +350,16 @@ namespace Emby.Server.Implementations.Data /// Read a row from the specified reader into the provided userData object. /// </summary> /// <param name="reader"></param> - private UserItemData ReadRow(IReadOnlyList<IResultSetValue> reader) + private UserItemData ReadRow(IReadOnlyList<ResultSetValue> reader) { var userData = new UserItemData(); userData.Key = reader[0].ToString(); // userData.UserId = reader[1].ReadGuidFromBlob(); - if (reader[2].SQLiteType != SQLiteType.Null) + if (reader.TryGetDouble(2, out var rating)) { - userData.Rating = reader[2].ToDouble(); + userData.Rating = rating; } userData.Played = reader[3].ToBool(); @@ -362,19 +367,19 @@ namespace Emby.Server.Implementations.Data userData.IsFavorite = reader[5].ToBool(); userData.PlaybackPositionTicks = reader[6].ToInt64(); - if (reader[7].SQLiteType != SQLiteType.Null) + if (reader.TryReadDateTime(7, out var lastPlayedDate)) { - userData.LastPlayedDate = reader[7].TryReadDateTime(); + userData.LastPlayedDate = lastPlayedDate; } - if (reader[8].SQLiteType != SQLiteType.Null) + if (reader.TryGetInt32(8, out var audioStreamIndex)) { - userData.AudioStreamIndex = reader[8].ToInt(); + userData.AudioStreamIndex = audioStreamIndex; } - if (reader[9].SQLiteType != SQLiteType.Null) + if (reader.TryGetInt32(9, out var subtitleStreamIndex)) { - userData.SubtitleStreamIndex = reader[9].ToInt(); + userData.SubtitleStreamIndex = subtitleStreamIndex; } return userData; diff --git a/Emby.Server.Implementations/Data/TypeMapper.cs b/Emby.Server.Implementations/Data/TypeMapper.cs index 7044b1d19..064664e1f 100644 --- a/Emby.Server.Implementations/Data/TypeMapper.cs +++ b/Emby.Server.Implementations/Data/TypeMapper.cs @@ -13,7 +13,7 @@ namespace Emby.Server.Implementations.Data /// This holds all the types in the running assemblies /// so that we can de-serialize properly when we don't have strong types. /// </summary> - private readonly ConcurrentDictionary<string, Type> _typeMap = new ConcurrentDictionary<string, Type>(); + private readonly ConcurrentDictionary<string, Type?> _typeMap = new ConcurrentDictionary<string, Type?>(); /// <summary> /// Gets the type. @@ -21,26 +21,16 @@ namespace Emby.Server.Implementations.Data /// <param name="typeName">Name of the type.</param> /// <returns>Type.</returns> /// <exception cref="ArgumentNullException"><c>typeName</c> is null.</exception> - public Type GetType(string typeName) + public Type? GetType(string typeName) { if (string.IsNullOrEmpty(typeName)) { throw new ArgumentNullException(nameof(typeName)); } - return _typeMap.GetOrAdd(typeName, LookupType); - } - - /// <summary> - /// Lookups the type. - /// </summary> - /// <param name="typeName">Name of the type.</param> - /// <returns>Type.</returns> - private Type LookupType(string typeName) - { - return AppDomain.CurrentDomain.GetAssemblies() - .Select(a => a.GetType(typeName)) - .FirstOrDefault(t => t != null); + return _typeMap.GetOrAdd(typeName, k => AppDomain.CurrentDomain.GetAssemblies() + .Select(a => a.GetType(k)) + .FirstOrDefault(t => t != null)); } } } |