diff options
Diffstat (limited to 'Emby.Server.Implementations/Data/SqliteItemRepository.cs')
| -rw-r--r-- | Emby.Server.Implementations/Data/SqliteItemRepository.cs | 249 |
1 files changed, 85 insertions, 164 deletions
diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index a6390b1ef..331ffc134 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -9,6 +9,7 @@ using System.Text; using System.Text.Json; using System.Threading; using Emby.Server.Implementations.Playlists; +using Jellyfin.Data.Enums; using MediaBrowser.Common.Extensions; using MediaBrowser.Common.Json; using MediaBrowser.Controller; @@ -400,6 +401,8 @@ namespace Emby.Server.Implementations.Data "OwnerId" }; + private static readonly string _retriveItemColumnsSelectQuery = $"select {string.Join(',', _retriveItemColumns)} from TypedBaseItems where guid = @guid"; + private static readonly string[] _mediaStreamSaveColumns = { "ItemId", @@ -439,6 +442,12 @@ namespace Emby.Server.Implementations.Data "ColorTransfer" }; + private static readonly string _mediaStreamSaveColumnsInsertQuery = + $"insert into mediastreams ({string.Join(',', _mediaStreamSaveColumns)}) values "; + + private static readonly string _mediaStreamSaveColumnsSelectQuery = + $"select {string.Join(',', _mediaStreamSaveColumns)} from mediastreams where ItemId=@ItemId"; + private static readonly string[] _mediaAttachmentSaveColumns = { "ItemId", @@ -450,102 +459,15 @@ namespace Emby.Server.Implementations.Data "MIMEType" }; - private static readonly string _mediaAttachmentInsertPrefix; - - private static string GetSaveItemCommandText() - { - var saveColumns = new[] - { - "guid", - "type", - "data", - "Path", - "StartDate", - "EndDate", - "ChannelId", - "IsMovie", - "IsSeries", - "EpisodeTitle", - "IsRepeat", - "CommunityRating", - "CustomRating", - "IndexNumber", - "IsLocked", - "Name", - "OfficialRating", - "MediaType", - "Overview", - "ParentIndexNumber", - "PremiereDate", - "ProductionYear", - "ParentId", - "Genres", - "InheritedParentalRatingValue", - "SortName", - "ForcedSortName", - "RunTimeTicks", - "Size", - "DateCreated", - "DateModified", - "PreferredMetadataLanguage", - "PreferredMetadataCountryCode", - "Width", - "Height", - "DateLastRefreshed", - "DateLastSaved", - "IsInMixedFolder", - "LockedFields", - "Studios", - "Audio", - "ExternalServiceId", - "Tags", - "IsFolder", - "UnratedType", - "TopParentId", - "TrailerTypes", - "CriticRating", - "CleanName", - "PresentationUniqueKey", - "OriginalTitle", - "PrimaryVersionId", - "DateLastMediaAdded", - "Album", - "IsVirtualItem", - "SeriesName", - "UserDataKey", - "SeasonName", - "SeasonId", - "SeriesId", - "ExternalSeriesId", - "Tagline", - "ProviderIds", - "Images", - "ProductionLocations", - "ExtraIds", - "TotalBitrate", - "ExtraType", - "Artists", - "AlbumArtists", - "ExternalId", - "SeriesPresentationUniqueKey", - "ShowId", - "OwnerId" - }; - - var saveItemCommandCommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns) + ") values ("; + private static readonly string _mediaAttachmentSaveColumnsSelectQuery = + $"select {string.Join(',', _mediaAttachmentSaveColumns)} from mediaattachments where ItemId=@ItemId"; - for (var i = 0; i < saveColumns.Length; i++) - { - if (i != 0) - { - saveItemCommandCommandText += ","; - } - - saveItemCommandCommandText += "@" + saveColumns[i]; - } + private static readonly string _mediaAttachmentInsertPrefix; - return saveItemCommandCommandText + ")"; - } + private const string SaveItemCommandText = + @"replace into TypedBaseItems + (guid,type,data,Path,StartDate,EndDate,ChannelId,IsMovie,IsSeries,EpisodeTitle,IsRepeat,CommunityRating,CustomRating,IndexNumber,IsLocked,Name,OfficialRating,MediaType,Overview,ParentIndexNumber,PremiereDate,ProductionYear,ParentId,Genres,InheritedParentalRatingValue,SortName,ForcedSortName,RunTimeTicks,Size,DateCreated,DateModified,PreferredMetadataLanguage,PreferredMetadataCountryCode,Width,Height,DateLastRefreshed,DateLastSaved,IsInMixedFolder,LockedFields,Studios,Audio,ExternalServiceId,Tags,IsFolder,UnratedType,TopParentId,TrailerTypes,CriticRating,CleanName,PresentationUniqueKey,OriginalTitle,PrimaryVersionId,DateLastMediaAdded,Album,IsVirtualItem,SeriesName,UserDataKey,SeasonName,SeasonId,SeriesId,ExternalSeriesId,Tagline,ProviderIds,Images,ProductionLocations,ExtraIds,TotalBitrate,ExtraType,Artists,AlbumArtists,ExternalId,SeriesPresentationUniqueKey,ShowId,OwnerId) + values (@guid,@type,@data,@Path,@StartDate,@EndDate,@ChannelId,@IsMovie,@IsSeries,@EpisodeTitle,@IsRepeat,@CommunityRating,@CustomRating,@IndexNumber,@IsLocked,@Name,@OfficialRating,@MediaType,@Overview,@ParentIndexNumber,@PremiereDate,@ProductionYear,@ParentId,@Genres,@InheritedParentalRatingValue,@SortName,@ForcedSortName,@RunTimeTicks,@Size,@DateCreated,@DateModified,@PreferredMetadataLanguage,@PreferredMetadataCountryCode,@Width,@Height,@DateLastRefreshed,@DateLastSaved,@IsInMixedFolder,@LockedFields,@Studios,@Audio,@ExternalServiceId,@Tags,@IsFolder,@UnratedType,@TopParentId,@TrailerTypes,@CriticRating,@CleanName,@PresentationUniqueKey,@OriginalTitle,@PrimaryVersionId,@DateLastMediaAdded,@Album,@IsVirtualItem,@SeriesName,@UserDataKey,@SeasonName,@SeasonId,@SeriesId,@ExternalSeriesId,@Tagline,@ProviderIds,@Images,@ProductionLocations,@ExtraIds,@TotalBitrate,@ExtraType,@Artists,@AlbumArtists,@ExternalId,@SeriesPresentationUniqueKey,@ShowId,@OwnerId)"; /// <summary> /// Save a standard item in the repo. @@ -636,7 +558,7 @@ namespace Emby.Server.Implementations.Data { var statements = PrepareAll(db, new string[] { - GetSaveItemCommandText(), + SaveItemCommandText, "delete from AncestorIds where ItemId=@ItemId" }).ToList(); @@ -1056,7 +978,10 @@ namespace Emby.Server.Implementations.Data continue; } - str.Append($"{i.Key}={i.Value}|"); + str.Append(i.Key) + .Append('=') + .Append(i.Value) + .Append('|'); } if (str.Length == 0) @@ -1110,7 +1035,8 @@ namespace Emby.Server.Implementations.Data continue; } - str.Append(ToValueString(i) + "|"); + AppendItemImageInfo(str, i); + str.Append('|'); } str.Length -= 1; // Remove last | @@ -1144,26 +1070,26 @@ namespace Emby.Server.Implementations.Data item.ImageInfos = list.ToArray(); } - public string ToValueString(ItemImageInfo image) + public void AppendItemImageInfo(StringBuilder bldr, ItemImageInfo image) { - const string Delimeter = "*"; + const char Delimiter = '*'; var path = image.Path ?? string.Empty; var hash = image.BlurHash ?? string.Empty; - return GetPathToSave(path) + - Delimeter + - image.DateModified.Ticks.ToString(CultureInfo.InvariantCulture) + - Delimeter + - image.Type + - Delimeter + - image.Width.ToString(CultureInfo.InvariantCulture) + - Delimeter + - image.Height.ToString(CultureInfo.InvariantCulture) + - Delimeter + - // Replace delimiters with other characters. - // This can be removed when we migrate to a proper DB. - hash.Replace('*', '/').Replace('|', '\\'); + bldr.Append(GetPathToSave(path)) + .Append(Delimiter) + .Append(image.DateModified.Ticks) + .Append(Delimiter) + .Append(image.Type) + .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('|', '\\')); } public ItemImageInfo ItemImageInfoFromValueString(string value) @@ -1225,7 +1151,7 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection(true)) { - using (var statement = PrepareStatement(connection, "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid")) + using (var statement = PrepareStatement(connection, _retriveItemColumnsSelectQuery)) { statement.TryBind("@guid", id); @@ -2471,7 +2397,7 @@ namespace Emby.Server.Implementations.Data var item = query.SimilarTo; var builder = new StringBuilder(); - builder.Append("("); + builder.Append('('); if (string.IsNullOrEmpty(item.OfficialRating)) { @@ -2509,7 +2435,7 @@ namespace Emby.Server.Implementations.Data if (!string.IsNullOrEmpty(query.SearchTerm)) { var builder = new StringBuilder(); - builder.Append("("); + builder.Append('('); builder.Append("((CleanName like @SearchTermStartsWith or (OriginalTitle not null and OriginalTitle like @SearchTermStartsWith)) * 10)"); @@ -2775,82 +2701,82 @@ namespace Emby.Server.Implementations.Data private string FixUnicodeChars(string buffer) { - if (buffer.IndexOf('\u2013') > -1) + if (buffer.IndexOf('\u2013', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u2013', '-'); // en dash } - if (buffer.IndexOf('\u2014') > -1) + if (buffer.IndexOf('\u2014', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u2014', '-'); // em dash } - if (buffer.IndexOf('\u2015') > -1) + if (buffer.IndexOf('\u2015', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u2015', '-'); // horizontal bar } - if (buffer.IndexOf('\u2017') > -1) + if (buffer.IndexOf('\u2017', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u2017', '_'); // double low line } - if (buffer.IndexOf('\u2018') > -1) + if (buffer.IndexOf('\u2018', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u2018', '\''); // left single quotation mark } - if (buffer.IndexOf('\u2019') > -1) + if (buffer.IndexOf('\u2019', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u2019', '\''); // right single quotation mark } - if (buffer.IndexOf('\u201a') > -1) + if (buffer.IndexOf('\u201a', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u201a', ','); // single low-9 quotation mark } - if (buffer.IndexOf('\u201b') > -1) + if (buffer.IndexOf('\u201b', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u201b', '\''); // single high-reversed-9 quotation mark } - if (buffer.IndexOf('\u201c') > -1) + if (buffer.IndexOf('\u201c', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u201c', '\"'); // left double quotation mark } - if (buffer.IndexOf('\u201d') > -1) + if (buffer.IndexOf('\u201d', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u201d', '\"'); // right double quotation mark } - if (buffer.IndexOf('\u201e') > -1) + if (buffer.IndexOf('\u201e', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u201e', '\"'); // double low-9 quotation mark } - if (buffer.IndexOf('\u2026') > -1) + if (buffer.IndexOf('\u2026', StringComparison.Ordinal) > -1) { - buffer = buffer.Replace("\u2026", "..."); // horizontal ellipsis + buffer = buffer.Replace("\u2026", "...", StringComparison.Ordinal); // horizontal ellipsis } - if (buffer.IndexOf('\u2032') > -1) + if (buffer.IndexOf('\u2032', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u2032', '\''); // prime } - if (buffer.IndexOf('\u2033') > -1) + if (buffer.IndexOf('\u2033', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u2033', '\"'); // double prime } - if (buffer.IndexOf('\u0060') > -1) + if (buffer.IndexOf('\u0060', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u0060', '\''); // grave accent } - if (buffer.IndexOf('\u00B4') > -1) + if (buffer.IndexOf('\u00B4', StringComparison.Ordinal) > -1) { buffer = buffer.Replace('\u00B4', '\''); // acute accent } @@ -2999,7 +2925,6 @@ namespace Emby.Server.Implementations.Data { connection.RunInTransaction(db => { - var statements = PrepareAll(db, statementTexts).ToList(); if (!isReturningZeroItems) @@ -4635,13 +4560,13 @@ namespace Emby.Server.Implementations.Data if (query.AncestorIds.Length > 1) { var inClause = string.Join(",", query.AncestorIds.Select(i => "'" + i.ToString("N", CultureInfo.InvariantCulture) + "'")); - whereClauses.Add(string.Format("Guid in (select itemId from AncestorIds where AncestorIdText in ({0}))", inClause)); + whereClauses.Add(string.Format(CultureInfo.InvariantCulture, "Guid in (select itemId from AncestorIds where AncestorIdText in ({0}))", inClause)); } if (!string.IsNullOrWhiteSpace(query.AncestorWithPresentationUniqueKey)) { var inClause = "select guid from TypedBaseItems where PresentationUniqueKey=@AncestorWithPresentationUniqueKey"; - whereClauses.Add(string.Format("Guid in (select itemId from AncestorIds where AncestorId in ({0}))", inClause)); + whereClauses.Add(string.Format(CultureInfo.InvariantCulture, "Guid in (select itemId from AncestorIds where AncestorId in ({0}))", inClause)); if (statement != null) { statement.TryBind("@AncestorWithPresentationUniqueKey", query.AncestorWithPresentationUniqueKey); @@ -4669,8 +4594,12 @@ namespace Emby.Server.Implementations.Data if (query.BlockUnratedItems.Length > 1) { - var inClause = string.Join(",", query.BlockUnratedItems.Select(i => "'" + i.ToString() + "'")); - whereClauses.Add(string.Format("(InheritedParentalRatingValue > 0 or UnratedType not in ({0}))", inClause)); + var inClause = string.Join(',', query.BlockUnratedItems.Select(i => "'" + i.ToString() + "'")); + whereClauses.Add( + string.Format( + CultureInfo.InvariantCulture, + "(InheritedParentalRatingValue > 0 or UnratedType not in ({0}))", + inClause)); } if (query.ExcludeInheritedTags.Length > 0) @@ -4679,7 +4608,7 @@ namespace Emby.Server.Implementations.Data if (statement == null) { int index = 0; - string excludedTags = string.Join(",", query.ExcludeInheritedTags.Select(t => paramName + index++)); + string excludedTags = string.Join(',', query.ExcludeInheritedTags.Select(t => paramName + index++)); whereClauses.Add("((select CleanValue from itemvalues where ItemId=Guid and Type=6 and cleanvalue in (" + excludedTags + ")) is null)"); } else @@ -5238,7 +5167,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type { if (i > 0) { - insertText.Append(","); + insertText.Append(','); } insertText.AppendFormat("(@ItemId, @AncestorId{0}, @AncestorIdText{0})", i.ToString(CultureInfo.InvariantCulture)); @@ -5733,10 +5662,10 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type const int Limit = 100; var startIndex = 0; + const string StartInsertText = "insert into ItemValues (ItemId, Type, Value, CleanValue) values "; + var insertText = new StringBuilder(StartInsertText); while (startIndex < values.Count) { - var insertText = new StringBuilder("insert into ItemValues (ItemId, Type, Value, CleanValue) values "); - var endIndex = Math.Min(values.Count, startIndex + Limit); for (var i = startIndex; i < endIndex; i++) @@ -5778,6 +5707,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } startIndex += Limit; + insertText.Length = StartInsertText.Length; } } @@ -5815,10 +5745,10 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type var startIndex = 0; var listIndex = 0; + const string StartInsertText = "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values "; + var insertText = new StringBuilder(StartInsertText); while (startIndex < people.Count) { - var insertText = new StringBuilder("insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values "); - var endIndex = Math.Min(people.Count, startIndex + Limit); for (var i = startIndex; i < endIndex; i++) { @@ -5852,6 +5782,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } startIndex += Limit; + insertText.Length = StartInsertText.Length; } } @@ -5890,10 +5821,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type throw new ArgumentNullException(nameof(query)); } - var cmdText = "select " - + string.Join(",", _mediaStreamSaveColumns) - + " from mediastreams where" - + " ItemId=@ItemId"; + var cmdText = _mediaStreamSaveColumnsSelectQuery; if (query.Type.HasValue) { @@ -5970,18 +5898,9 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type const int Limit = 10; var startIndex = 0; + var insertText = new StringBuilder(_mediaStreamSaveColumnsInsertQuery); while (startIndex < streams.Count) { - var insertText = new StringBuilder("insert into mediastreams ("); - foreach (var column in _mediaStreamSaveColumns) - { - insertText.Append(column).Append(','); - } - - // Remove last comma - insertText.Length--; - insertText.Append(") values "); - var endIndex = Math.Min(streams.Count, startIndex + Limit); for (var i = startIndex; i < endIndex; i++) @@ -6064,6 +5983,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } startIndex += Limit; + insertText.Length = _mediaStreamSaveColumnsInsertQuery.Length; } } @@ -6247,10 +6167,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type throw new ArgumentNullException(nameof(query)); } - var cmdText = "select " - + string.Join(",", _mediaAttachmentSaveColumns) - + " from mediaattachments where" - + " ItemId=@ItemId"; + var cmdText = _mediaAttachmentSaveColumnsSelectQuery; if (query.Index.HasValue) { @@ -6318,10 +6235,9 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type { const int InsertAtOnce = 10; + var insertText = new StringBuilder(_mediaAttachmentInsertPrefix); for (var startIndex = 0; startIndex < attachments.Count; startIndex += InsertAtOnce) { - var insertText = new StringBuilder(_mediaAttachmentInsertPrefix); - var endIndex = Math.Min(attachments.Count, startIndex + InsertAtOnce); for (var i = startIndex; i < endIndex; i++) @@ -6331,7 +6247,10 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type foreach (var column in _mediaAttachmentSaveColumns.Skip(1)) { - insertText.Append("@" + column + index + ","); + insertText.Append('@') + .Append(column) + .Append(index) + .Append(','); } insertText.Length -= 1; @@ -6364,6 +6283,8 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type statement.Reset(); statement.MoveNext(); } + + insertText.Length = _mediaAttachmentInsertPrefix.Length; } } |