diff options
Diffstat (limited to 'Emby.Server.Implementations/Data')
7 files changed, 396 insertions, 717 deletions
diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index 0654132f4..8c756a7f4 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -17,7 +17,7 @@ namespace Emby.Server.Implementations.Data /// Initializes a new instance of the <see cref="BaseSqliteRepository"/> class. /// </summary> /// <param name="logger">The logger.</param> - protected BaseSqliteRepository(ILogger logger) + protected BaseSqliteRepository(ILogger<BaseSqliteRepository> logger) { Logger = logger; } @@ -32,7 +32,7 @@ namespace Emby.Server.Implementations.Data /// Gets the logger. /// </summary> /// <value>The logger.</value> - protected ILogger Logger { get; } + protected ILogger<BaseSqliteRepository> Logger { get; } /// <summary> /// Gets the default connection flags. @@ -143,12 +143,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")) { @@ -162,7 +172,6 @@ namespace Emby.Server.Implementations.Data } return false; - }, ReadTransactionMode); } @@ -248,12 +257,12 @@ namespace Emby.Server.Implementations.Data public enum SynchronousMode { /// <summary> - /// SQLite continues without syncing as soon as it has handed data off to the operating system + /// SQLite continues without syncing as soon as it has handed data off to the operating system. /// </summary> Off = 0, /// <summary> - /// SQLite database engine will still sync at the most critical moments + /// SQLite database engine will still sync at the most critical moments. /// </summary> Normal = 1, diff --git a/Emby.Server.Implementations/Data/CleanDatabaseScheduledTask.cs b/Emby.Server.Implementations/Data/CleanDatabaseScheduledTask.cs index 37c678a5d..3de9d6371 100644 --- a/Emby.Server.Implementations/Data/CleanDatabaseScheduledTask.cs +++ b/Emby.Server.Implementations/Data/CleanDatabaseScheduledTask.cs @@ -12,7 +12,7 @@ namespace Emby.Server.Implementations.Data public class CleanDatabaseScheduledTask : ILibraryPostScanTask { private readonly ILibraryManager _libraryManager; - private readonly ILogger _logger; + private readonly ILogger<CleanDatabaseScheduledTask> _logger; public CleanDatabaseScheduledTask(ILibraryManager libraryManager, ILogger<CleanDatabaseScheduledTask> logger) { @@ -51,7 +51,6 @@ namespace Emby.Server.Implementations.Data _libraryManager.DeleteItem(item, new DeleteOptions { DeleteFileLocation = false - }); } diff --git a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs deleted file mode 100644 index d474f1c6b..000000000 --- a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs +++ /dev/null @@ -1,225 +0,0 @@ -#pragma warning disable CS1591 - -using System; -using System.Collections.Generic; -using System.Globalization; -using System.IO; -using System.Text.Json; -using System.Threading; -using MediaBrowser.Common.Configuration; -using MediaBrowser.Common.Extensions; -using MediaBrowser.Common.Json; -using MediaBrowser.Controller.Persistence; -using MediaBrowser.Model.Entities; -using MediaBrowser.Model.IO; -using Microsoft.Extensions.Logging; -using SQLitePCL.pretty; - -namespace Emby.Server.Implementations.Data -{ - /// <summary> - /// Class SQLiteDisplayPreferencesRepository. - /// </summary> - public class SqliteDisplayPreferencesRepository : BaseSqliteRepository, IDisplayPreferencesRepository - { - private readonly IFileSystem _fileSystem; - - private readonly JsonSerializerOptions _jsonOptions; - - public SqliteDisplayPreferencesRepository(ILogger<SqliteDisplayPreferencesRepository> logger, IApplicationPaths appPaths, IFileSystem fileSystem) - : base(logger) - { - _fileSystem = fileSystem; - - _jsonOptions = JsonDefaults.GetOptions(); - - DbFilePath = Path.Combine(appPaths.DataPath, "displaypreferences.db"); - } - - /// <summary> - /// Gets the name of the repository. - /// </summary> - /// <value>The name.</value> - public string Name => "SQLite"; - - public void Initialize() - { - try - { - InitializeInternal(); - } - catch (Exception ex) - { - Logger.LogError(ex, "Error loading database file. Will reset and retry."); - - _fileSystem.DeleteFile(DbFilePath); - - InitializeInternal(); - } - } - - /// <summary> - /// Opens the connection to the database - /// </summary> - /// <returns>Task.</returns> - private void InitializeInternal() - { - string[] queries = - { - "create table if not exists userdisplaypreferences (id GUID NOT NULL, userId GUID NOT NULL, client text NOT NULL, data BLOB NOT NULL)", - "create unique index if not exists userdisplaypreferencesindex on userdisplaypreferences (id, userId, client)" - }; - - using (var connection = GetConnection()) - { - connection.RunQueries(queries); - } - } - - /// <summary> - /// Save the display preferences associated with an item in the repo - /// </summary> - /// <param name="displayPreferences">The display preferences.</param> - /// <param name="userId">The user id.</param> - /// <param name="client">The client.</param> - /// <param name="cancellationToken">The cancellation token.</param> - /// <exception cref="ArgumentNullException">item</exception> - public void SaveDisplayPreferences(DisplayPreferences displayPreferences, Guid userId, string client, CancellationToken cancellationToken) - { - if (displayPreferences == null) - { - throw new ArgumentNullException(nameof(displayPreferences)); - } - - if (string.IsNullOrEmpty(displayPreferences.Id)) - { - throw new ArgumentException("Display preferences has an invalid Id", nameof(displayPreferences)); - } - - cancellationToken.ThrowIfCancellationRequested(); - - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => SaveDisplayPreferences(displayPreferences, userId, client, db), - TransactionMode); - } - } - - private void SaveDisplayPreferences(DisplayPreferences displayPreferences, Guid userId, string client, IDatabaseConnection connection) - { - var serialized = JsonSerializer.SerializeToUtf8Bytes(displayPreferences, _jsonOptions); - - using (var statement = connection.PrepareStatement("replace into userdisplaypreferences (id, userid, client, data) values (@id, @userId, @client, @data)")) - { - statement.TryBind("@id", new Guid(displayPreferences.Id).ToByteArray()); - statement.TryBind("@userId", userId.ToByteArray()); - statement.TryBind("@client", client); - statement.TryBind("@data", serialized); - - statement.MoveNext(); - } - } - - /// <summary> - /// Save all display preferences associated with a user in the repo - /// </summary> - /// <param name="displayPreferences">The display preferences.</param> - /// <param name="userId">The user id.</param> - /// <param name="cancellationToken">The cancellation token.</param> - /// <exception cref="ArgumentNullException">item</exception> - public void SaveAllDisplayPreferences(IEnumerable<DisplayPreferences> displayPreferences, Guid userId, CancellationToken cancellationToken) - { - if (displayPreferences == null) - { - throw new ArgumentNullException(nameof(displayPreferences)); - } - - cancellationToken.ThrowIfCancellationRequested(); - - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - foreach (var displayPreference in displayPreferences) - { - SaveDisplayPreferences(displayPreference, userId, displayPreference.Client, db); - } - }, - TransactionMode); - } - } - - /// <summary> - /// Gets the display preferences. - /// </summary> - /// <param name="displayPreferencesId">The display preferences id.</param> - /// <param name="userId">The user id.</param> - /// <param name="client">The client.</param> - /// <returns>Task{DisplayPreferences}.</returns> - /// <exception cref="ArgumentNullException">item</exception> - public DisplayPreferences GetDisplayPreferences(string displayPreferencesId, Guid userId, string client) - { - if (string.IsNullOrEmpty(displayPreferencesId)) - { - throw new ArgumentNullException(nameof(displayPreferencesId)); - } - - var guidId = displayPreferencesId.GetMD5(); - - using (var connection = GetConnection(true)) - { - using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where id = @id and userId=@userId and client=@client")) - { - statement.TryBind("@id", guidId.ToByteArray()); - statement.TryBind("@userId", userId.ToByteArray()); - statement.TryBind("@client", client); - - foreach (var row in statement.ExecuteQuery()) - { - return Get(row); - } - } - } - - return new DisplayPreferences - { - Id = guidId.ToString("N", CultureInfo.InvariantCulture) - }; - } - - /// <summary> - /// Gets all display preferences for the given user. - /// </summary> - /// <param name="userId">The user id.</param> - /// <returns>Task{DisplayPreferences}.</returns> - /// <exception cref="ArgumentNullException">item</exception> - public IEnumerable<DisplayPreferences> GetAllDisplayPreferences(Guid userId) - { - var list = new List<DisplayPreferences>(); - - using (var connection = GetConnection(true)) - using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where userId=@userId")) - { - statement.TryBind("@userId", userId.ToByteArray()); - - foreach (var row in statement.ExecuteQuery()) - { - list.Add(Get(row)); - } - } - - return list; - } - - private DisplayPreferences Get(IReadOnlyList<IResultSetValue> row) - => JsonSerializer.Deserialize<DisplayPreferences>(row[0].ToBlob(), _jsonOptions); - - public void SaveDisplayPreferences(DisplayPreferences displayPreferences, string userId, string client, CancellationToken cancellationToken) - => SaveDisplayPreferences(displayPreferences, new Guid(userId), client, cancellationToken); - - public DisplayPreferences GetDisplayPreferences(string displayPreferencesId, string userId, string client) - => GetDisplayPreferences(displayPreferencesId, new Guid(userId), client); - } -} diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index 716e5071d..70a6df977 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -234,7 +234,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 { diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index ca5cd6fdd..1c2aeda70 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -1,3 +1,5 @@ +#pragma warning disable CS1591 + using System; using System.Collections.Generic; using System.Globalization; @@ -7,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; @@ -33,7 +36,7 @@ using SQLitePCL.pretty; namespace Emby.Server.Implementations.Data { /// <summary> - /// Class SQLiteItemRepository + /// Class SQLiteItemRepository. /// </summary> public class SqliteItemRepository : BaseSqliteRepository, IItemRepository { @@ -100,7 +103,7 @@ namespace Emby.Server.Implementations.Data protected override TempStoreMode TempStore => TempStoreMode.Memory; /// <summary> - /// Opens the connection to the database + /// Opens the connection to the database. /// </summary> public void Initialize(SqliteUserDataRepository userDataRepo, IUserManager userManager) { @@ -135,7 +138,6 @@ namespace Emby.Server.Implementations.Data "pragma shrink_memory" }; - string[] postQueries = { // obsolete @@ -217,7 +219,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); @@ -319,7 +322,6 @@ namespace Emby.Server.Implementations.Data AddColumn(db, "MediaStreams", "ColorPrimaries", "TEXT", existingColumnNames); AddColumn(db, "MediaStreams", "ColorSpace", "TEXT", existingColumnNames); AddColumn(db, "MediaStreams", "ColorTransfer", "TEXT", existingColumnNames); - }, TransactionMode); connection.RunQueries(postQueries); @@ -399,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", @@ -438,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", @@ -449,105 +459,18 @@ 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 ("; - - for (var i = 0; i < saveColumns.Length; i++) - { - if (i != 0) - { - saveItemCommandCommandText += ","; - } + private static readonly string _mediaAttachmentSaveColumnsSelectQuery = + $"select {string.Join(',', _mediaAttachmentSaveColumns)} from mediaattachments where ItemId=@ItemId"; - 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 + /// Save a standard item in the repo. /// </summary> /// <param name="item">The item.</param> /// <param name="cancellationToken">The cancellation token.</param> @@ -573,7 +496,8 @@ 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")) { @@ -624,7 +548,8 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection()) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { SaveItemsInTranscation(db, tuples); }, TransactionMode); @@ -635,9 +560,9 @@ namespace Emby.Server.Implementations.Data { var statements = PrepareAll(db, new string[] { - GetSaveItemCommandText(), + SaveItemCommandText, "delete from AncestorIds where ItemId=@ItemId" - }).ToList(); + }); using (var saveItemStatement = statements[0]) using (var deleteAncestorsStatement = statements[1]) @@ -792,6 +717,7 @@ namespace Emby.Server.Implementations.Data { saveItemStatement.TryBindNull("@Width"); } + if (item.Height > 0) { saveItemStatement.TryBind("@Height", item.Height); @@ -931,6 +857,7 @@ namespace Emby.Server.Implementations.Data { saveItemStatement.TryBindNull("@SeriesName"); } + if (string.IsNullOrWhiteSpace(userDataKey)) { saveItemStatement.TryBindNull("@UserDataKey"); @@ -1006,6 +933,7 @@ namespace Emby.Server.Implementations.Data { artists = string.Join("|", hasArtists.Artists); } + saveItemStatement.TryBind("@Artists", artists); string albumArtists = null; @@ -1052,7 +980,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) @@ -1105,7 +1036,9 @@ namespace Emby.Server.Implementations.Data { continue; } - str.Append(ToValueString(i) + "|"); + + AppendItemImageInfo(str, i); + str.Append('|'); } str.Length -= 1; // Remove last | @@ -1139,26 +1072,26 @@ namespace Emby.Server.Implementations.Data item.ImageInfos = list.ToArray(); } - public string ToValueString(ItemImageInfo image) + public void AppendItemImageInfo(StringBuilder bldr, ItemImageInfo image) { - var delimeter = "*"; - - var path = image.Path; - - if (path == null) - { - path = 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); + const char Delimiter = '*'; + + var path = image.Path ?? string.Empty; + var hash = image.BlurHash ?? string.Empty; + + 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) @@ -1192,13 +1125,18 @@ namespace Emby.Server.Implementations.Data image.Width = width; image.Height = height; } + + if (parts.Length >= 6) + { + image.BlurHash = parts[5].Replace('/', '*').Replace('\\', '|'); + } } return image; } /// <summary> - /// Internal retrieve from items or users table + /// Internal retrieve from items or users table. /// </summary> /// <param name="id">The id.</param> /// <returns>BaseItem.</returns> @@ -1215,7 +1153,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); @@ -1360,6 +1298,7 @@ namespace Emby.Server.Implementations.Data hasStartDate.StartDate = reader[index].ReadDateTime(); } } + index++; } @@ -1367,12 +1306,14 @@ namespace Emby.Server.Implementations.Data { item.EndDate = reader[index].TryReadDateTime(); } + index++; if (!reader.IsDBNull(index)) { item.ChannelId = new Guid(reader.GetString(index)); } + index++; if (enableProgramAttributes) @@ -1383,24 +1324,28 @@ namespace Emby.Server.Implementations.Data { hasProgramAttributes.IsMovie = reader.GetBoolean(index); } + index++; if (!reader.IsDBNull(index)) { hasProgramAttributes.IsSeries = reader.GetBoolean(index); } + index++; if (!reader.IsDBNull(index)) { hasProgramAttributes.EpisodeTitle = reader.GetString(index); } + index++; if (!reader.IsDBNull(index)) { hasProgramAttributes.IsRepeat = reader.GetBoolean(index); } + index++; } else @@ -1413,6 +1358,7 @@ namespace Emby.Server.Implementations.Data { item.CommunityRating = reader.GetFloat(index); } + index++; if (HasField(query, ItemFields.CustomRating)) @@ -1421,6 +1367,7 @@ namespace Emby.Server.Implementations.Data { item.CustomRating = reader.GetString(index); } + index++; } @@ -1428,6 +1375,7 @@ namespace Emby.Server.Implementations.Data { item.IndexNumber = reader.GetInt32(index); } + index++; if (HasField(query, ItemFields.Settings)) @@ -1436,18 +1384,21 @@ namespace Emby.Server.Implementations.Data { item.IsLocked = reader.GetBoolean(index); } + index++; if (!reader.IsDBNull(index)) { item.PreferredMetadataLanguage = reader.GetString(index); } + index++; if (!reader.IsDBNull(index)) { item.PreferredMetadataCountryCode = reader.GetString(index); } + index++; } @@ -1457,6 +1408,7 @@ namespace Emby.Server.Implementations.Data { item.Width = reader.GetInt32(index); } + index++; } @@ -1466,6 +1418,7 @@ namespace Emby.Server.Implementations.Data { item.Height = reader.GetInt32(index); } + index++; } @@ -1475,6 +1428,7 @@ namespace Emby.Server.Implementations.Data { item.DateLastRefreshed = reader[index].ReadDateTime(); } + index++; } @@ -1482,18 +1436,21 @@ namespace Emby.Server.Implementations.Data { item.Name = reader.GetString(index); } + index++; if (!reader.IsDBNull(index)) { item.Path = RestorePath(reader.GetString(index)); } + index++; if (!reader.IsDBNull(index)) { item.PremiereDate = reader[index].TryReadDateTime(); } + index++; if (HasField(query, ItemFields.Overview)) @@ -1502,6 +1459,7 @@ namespace Emby.Server.Implementations.Data { item.Overview = reader.GetString(index); } + index++; } @@ -1509,18 +1467,21 @@ namespace Emby.Server.Implementations.Data { item.ParentIndexNumber = reader.GetInt32(index); } + index++; if (!reader.IsDBNull(index)) { item.ProductionYear = reader.GetInt32(index); } + index++; if (!reader.IsDBNull(index)) { item.OfficialRating = reader.GetString(index); } + index++; if (HasField(query, ItemFields.SortName)) @@ -1529,6 +1490,7 @@ namespace Emby.Server.Implementations.Data { item.ForcedSortName = reader.GetString(index); } + index++; } @@ -1536,12 +1498,14 @@ namespace Emby.Server.Implementations.Data { item.RunTimeTicks = reader.GetInt64(index); } + index++; if (!reader.IsDBNull(index)) { item.Size = reader.GetInt64(index); } + index++; if (HasField(query, ItemFields.DateCreated)) @@ -1550,6 +1514,7 @@ namespace Emby.Server.Implementations.Data { item.DateCreated = reader[index].ReadDateTime(); } + index++; } @@ -1557,6 +1522,7 @@ namespace Emby.Server.Implementations.Data { item.DateModified = reader[index].ReadDateTime(); } + index++; item.Id = reader.GetGuid(index); @@ -1568,6 +1534,7 @@ namespace Emby.Server.Implementations.Data { item.Genres = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); } + index++; } @@ -1575,6 +1542,7 @@ namespace Emby.Server.Implementations.Data { item.ParentId = reader.GetGuid(index); } + index++; if (!reader.IsDBNull(index)) @@ -1584,6 +1552,7 @@ namespace Emby.Server.Implementations.Data item.Audio = audio; } } + index++; // TODO: Even if not needed by apps, the server needs it internally @@ -1597,6 +1566,7 @@ namespace Emby.Server.Implementations.Data liveTvChannel.ServiceName = reader.GetString(index); } } + index++; } @@ -1604,6 +1574,7 @@ namespace Emby.Server.Implementations.Data { item.IsInMixedFolder = reader.GetBoolean(index); } + index++; if (HasField(query, ItemFields.DateLastSaved)) @@ -1612,6 +1583,7 @@ namespace Emby.Server.Implementations.Data { item.DateLastSaved = reader[index].ReadDateTime(); } + index++; } @@ -1619,18 +1591,20 @@ namespace Emby.Server.Implementations.Data { if (!reader.IsDBNull(index)) { - IEnumerable<MetadataFields> GetLockedFields(string s) + IEnumerable<MetadataField> GetLockedFields(string s) { foreach (var i in s.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries)) { - if (Enum.TryParse(i, true, out MetadataFields parsedValue)) + if (Enum.TryParse(i, true, out MetadataField parsedValue)) { yield return parsedValue; } } } + item.LockedFields = GetLockedFields(reader.GetString(index)).ToArray(); } + index++; } @@ -1640,6 +1614,7 @@ namespace Emby.Server.Implementations.Data { item.Studios = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); } + index++; } @@ -1649,6 +1624,7 @@ namespace Emby.Server.Implementations.Data { item.Tags = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); } + index++; } @@ -1668,9 +1644,11 @@ namespace Emby.Server.Implementations.Data } } } + trailer.TrailerTypes = GetTrailerTypes(reader.GetString(index)).ToArray(); } } + index++; } @@ -1680,6 +1658,7 @@ namespace Emby.Server.Implementations.Data { item.OriginalTitle = reader.GetString(index); } + index++; } @@ -1690,6 +1669,7 @@ namespace Emby.Server.Implementations.Data video.PrimaryVersionId = reader.GetString(index); } } + index++; if (HasField(query, ItemFields.DateLastMediaAdded)) @@ -1698,6 +1678,7 @@ namespace Emby.Server.Implementations.Data { folder.DateLastMediaAdded = reader[index].TryReadDateTime(); } + index++; } @@ -1705,18 +1686,21 @@ namespace Emby.Server.Implementations.Data { item.Album = reader.GetString(index); } + index++; if (!reader.IsDBNull(index)) { item.CriticRating = reader.GetFloat(index); } + index++; if (!reader.IsDBNull(index)) { item.IsVirtualItem = reader.GetBoolean(index); } + index++; if (item is IHasSeries hasSeriesName) @@ -1726,6 +1710,7 @@ namespace Emby.Server.Implementations.Data hasSeriesName.SeriesName = reader.GetString(index); } } + index++; if (hasEpisodeAttributes) @@ -1736,6 +1721,7 @@ namespace Emby.Server.Implementations.Data { episode.SeasonName = reader.GetString(index); } + index++; if (!reader.IsDBNull(index)) { @@ -1746,6 +1732,7 @@ namespace Emby.Server.Implementations.Data { index++; } + index++; } @@ -1759,6 +1746,7 @@ namespace Emby.Server.Implementations.Data hasSeries.SeriesId = reader.GetGuid(index); } } + index++; } @@ -1768,6 +1756,7 @@ namespace Emby.Server.Implementations.Data { item.PresentationUniqueKey = reader.GetString(index); } + index++; } @@ -1777,6 +1766,7 @@ namespace Emby.Server.Implementations.Data { item.InheritedParentalRatingValue = reader.GetInt32(index); } + index++; } @@ -1786,6 +1776,7 @@ namespace Emby.Server.Implementations.Data { item.ExternalSeriesId = reader.GetString(index); } + index++; } @@ -1795,6 +1786,7 @@ namespace Emby.Server.Implementations.Data { item.Tagline = reader.GetString(index); } + index++; } @@ -1802,6 +1794,7 @@ namespace Emby.Server.Implementations.Data { DeserializeProviderIds(reader.GetString(index), item); } + index++; if (query.DtoOptions.EnableImages) @@ -1810,6 +1803,7 @@ namespace Emby.Server.Implementations.Data { DeserializeImages(reader.GetString(index), item); } + index++; } @@ -1819,6 +1813,7 @@ namespace Emby.Server.Implementations.Data { item.ProductionLocations = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries).ToArray(); } + index++; } @@ -1828,6 +1823,7 @@ namespace Emby.Server.Implementations.Data { item.ExtraIds = SplitToGuids(reader.GetString(index)); } + index++; } @@ -1835,6 +1831,7 @@ namespace Emby.Server.Implementations.Data { item.TotalBitrate = reader.GetInt32(index); } + index++; if (!reader.IsDBNull(index)) @@ -1844,6 +1841,7 @@ namespace Emby.Server.Implementations.Data item.ExtraType = extraType; } } + index++; if (hasArtistFields) @@ -1852,12 +1850,14 @@ namespace Emby.Server.Implementations.Data { hasArtists.Artists = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); } + index++; if (item is IHasAlbumArtist hasAlbumArtists && !reader.IsDBNull(index)) { hasAlbumArtists.AlbumArtists = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); } + index++; } @@ -1865,6 +1865,7 @@ namespace Emby.Server.Implementations.Data { item.ExternalId = reader.GetString(index); } + index++; if (HasField(query, ItemFields.SeriesPresentationUniqueKey)) @@ -1876,6 +1877,7 @@ namespace Emby.Server.Implementations.Data hasSeries.SeriesPresentationUniqueKey = reader.GetString(index); } } + index++; } @@ -1885,6 +1887,7 @@ namespace Emby.Server.Implementations.Data { program.ShowId = reader.GetString(index); } + index++; } @@ -1892,6 +1895,7 @@ namespace Emby.Server.Implementations.Data { item.OwnerId = reader.GetGuid(index); } + index++; return item; @@ -1912,7 +1916,7 @@ namespace Emby.Server.Implementations.Data } /// <summary> - /// Gets chapters for an item + /// Gets chapters for an item. /// </summary> /// <param name="item">The item.</param> /// <returns>IEnumerable{ChapterInfo}.</returns> @@ -1940,7 +1944,7 @@ namespace Emby.Server.Implementations.Data } /// <summary> - /// Gets a single chapter for an item + /// Gets a single chapter for an item. /// </summary> /// <param name="item">The item.</param> /// <param name="index">The index.</param> @@ -1971,6 +1975,7 @@ namespace Emby.Server.Implementations.Data /// Gets the chapter. /// </summary> /// <param name="reader">The reader.</param> + /// <param name="item">The item.</param> /// <returns>ChapterInfo.</returns> private ChapterInfo GetChapter(IReadOnlyList<IResultSetValue> reader, BaseItem item) { @@ -2030,13 +2035,13 @@ 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); InsertChapters(idBlob, chapters, db); - }, TransactionMode); } } @@ -2262,7 +2267,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", @@ -2395,7 +2399,7 @@ namespace Emby.Server.Implementations.Data var item = query.SimilarTo; var builder = new StringBuilder(); - builder.Append("("); + builder.Append('('); if (string.IsNullOrEmpty(item.OfficialRating)) { @@ -2433,7 +2437,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)"); @@ -2467,6 +2471,7 @@ namespace Emby.Server.Implementations.Data { statement.TryBind("@SearchTermStartsWith", searchTerm + "%"); } + if (commandText.IndexOf("@SearchTermContains", StringComparison.OrdinalIgnoreCase) != -1) { statement.TryBind("@SearchTermContains", "%" + searchTerm + "%"); @@ -2698,22 +2703,85 @@ namespace Emby.Server.Implementations.Data private string FixUnicodeChars(string buffer) { - if (buffer.IndexOf('\u2013') > -1) buffer = buffer.Replace('\u2013', '-'); // en dash - if (buffer.IndexOf('\u2014') > -1) buffer = buffer.Replace('\u2014', '-'); // em dash - if (buffer.IndexOf('\u2015') > -1) buffer = buffer.Replace('\u2015', '-'); // horizontal bar - if (buffer.IndexOf('\u2017') > -1) buffer = buffer.Replace('\u2017', '_'); // double low line - if (buffer.IndexOf('\u2018') > -1) buffer = buffer.Replace('\u2018', '\''); // left single quotation mark - if (buffer.IndexOf('\u2019') > -1) buffer = buffer.Replace('\u2019', '\''); // right single quotation mark - if (buffer.IndexOf('\u201a') > -1) buffer = buffer.Replace('\u201a', ','); // single low-9 quotation mark - if (buffer.IndexOf('\u201b') > -1) buffer = buffer.Replace('\u201b', '\''); // single high-reversed-9 quotation mark - if (buffer.IndexOf('\u201c') > -1) buffer = buffer.Replace('\u201c', '\"'); // left double quotation mark - if (buffer.IndexOf('\u201d') > -1) buffer = buffer.Replace('\u201d', '\"'); // right double quotation mark - if (buffer.IndexOf('\u201e') > -1) buffer = buffer.Replace('\u201e', '\"'); // double low-9 quotation mark - if (buffer.IndexOf('\u2026') > -1) buffer = buffer.Replace("\u2026", "..."); // horizontal ellipsis - if (buffer.IndexOf('\u2032') > -1) buffer = buffer.Replace('\u2032', '\''); // prime - if (buffer.IndexOf('\u2033') > -1) buffer = buffer.Replace('\u2033', '\"'); // double prime - if (buffer.IndexOf('\u0060') > -1) buffer = buffer.Replace('\u0060', '\''); // grave accent - if (buffer.IndexOf('\u00B4') > -1) buffer = buffer.Replace('\u00B4', '\''); // acute accent + 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; } @@ -2726,7 +2794,7 @@ namespace Emby.Server.Implementations.Data foreach (var providerId in newItem.ProviderIds) { - if (providerId.Key == MetadataProviders.TmdbCollection.ToString()) + if (providerId.Key == MetadataProvider.TmdbCollection.ToString()) { continue; } @@ -2737,6 +2805,7 @@ namespace Emby.Server.Implementations.Data { items[i] = newItem; } + return; } } @@ -2829,6 +2898,7 @@ namespace Emby.Server.Implementations.Data { statementTexts.Add(commandText); } + if (query.EnableTotalRecordCount) { commandText = string.Empty; @@ -2855,10 +2925,10 @@ namespace Emby.Server.Implementations.Data var result = new QueryResult<BaseItem>(); using (var connection = GetConnection(true)) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { - - var statements = PrepareAll(db, statementTexts).ToList(); + var statements = PrepareAll(db, statementTexts); if (!isReturningZeroItems) { @@ -2896,7 +2966,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)) { @@ -3225,7 +3295,6 @@ namespace Emby.Server.Implementations.Data } } - var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; var statementTexts = new List<string>(); @@ -3233,6 +3302,7 @@ namespace Emby.Server.Implementations.Data { statementTexts.Add(commandText); } + if (query.EnableTotalRecordCount) { commandText = string.Empty; @@ -3259,9 +3329,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) { @@ -3287,7 +3358,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)) { @@ -3586,11 +3657,13 @@ namespace Emby.Server.Implementations.Data whereClauses.Add("IndexNumber=@IndexNumber"); statement?.TryBind("@IndexNumber", query.IndexNumber.Value); } + if (query.ParentIndexNumber.HasValue) { whereClauses.Add("ParentIndexNumber=@ParentIndexNumber"); statement?.TryBind("@ParentIndexNumber", query.ParentIndexNumber.Value); } + if (query.ParentIndexNumberNotEquals.HasValue) { whereClauses.Add("(ParentIndexNumber<>@ParentIndexNumberNotEquals or ParentIndexNumber is null)"); @@ -3648,26 +3721,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) @@ -3687,23 +3765,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)) @@ -3876,6 +3966,7 @@ namespace Emby.Server.Implementations.Data { statement.TryBind(paramName, artistId.ToByteArray()); } + index++; } @@ -3896,6 +3987,7 @@ namespace Emby.Server.Implementations.Data { statement.TryBind(paramName, artistId.ToByteArray()); } + index++; } @@ -3916,8 +4008,10 @@ namespace Emby.Server.Implementations.Data { statement.TryBind(paramName, artistId.ToByteArray()); } + index++; } + var clause = "(" + string.Join(" OR ", clauses) + ")"; whereClauses.Add(clause); } @@ -3935,8 +4029,10 @@ namespace Emby.Server.Implementations.Data { statement.TryBind(paramName, albumId.ToByteArray()); } + index++; } + var clause = "(" + string.Join(" OR ", clauses) + ")"; whereClauses.Add(clause); } @@ -3954,8 +4050,10 @@ namespace Emby.Server.Implementations.Data { statement.TryBind(paramName, artistId.ToByteArray()); } + index++; } + var clause = "(" + string.Join(" OR ", clauses) + ")"; whereClauses.Add(clause); } @@ -3973,8 +4071,10 @@ namespace Emby.Server.Implementations.Data { statement.TryBind(paramName, genreId.ToByteArray()); } + index++; } + var clause = "(" + string.Join(" OR ", clauses) + ")"; whereClauses.Add(clause); } @@ -3990,8 +4090,10 @@ namespace Emby.Server.Implementations.Data { statement.TryBind("@Genre" + index, GetCleanValue(item)); } + index++; } + var clause = "(" + string.Join(" OR ", clauses) + ")"; whereClauses.Add(clause); } @@ -4007,8 +4109,10 @@ namespace Emby.Server.Implementations.Data { statement.TryBind("@Tag" + index, GetCleanValue(item)); } + index++; } + var clause = "(" + string.Join(" OR ", clauses) + ")"; whereClauses.Add(clause); } @@ -4024,8 +4128,10 @@ namespace Emby.Server.Implementations.Data { statement.TryBind("@ExcludeTag" + index, GetCleanValue(item)); } + index++; } + var clause = "(" + string.Join(" OR ", clauses) + ")"; whereClauses.Add(clause); } @@ -4044,8 +4150,10 @@ namespace Emby.Server.Implementations.Data { statement.TryBind(paramName, studioId.ToByteArray()); } + index++; } + var clause = "(" + string.Join(" OR ", clauses) + ")"; whereClauses.Add(clause); } @@ -4061,8 +4169,10 @@ namespace Emby.Server.Implementations.Data { statement.TryBind("@OfficialRating" + index, item); } + index++; } + var clause = "(" + string.Join(" OR ", clauses) + ")"; whereClauses.Add(clause); } @@ -4218,7 +4328,7 @@ namespace Emby.Server.Implementations.Data whereClauses.Add("ProductionYear=@Years"); if (statement != null) { - statement.TryBind("@Years", query.Years[0].ToString()); + statement.TryBind("@Years", query.Years[0].ToString(CultureInfo.InvariantCulture)); } } else if (query.Years.Length > 1) @@ -4237,6 +4347,7 @@ namespace Emby.Server.Implementations.Data statement.TryBind("@IsVirtualItem", isVirtualItem.Value); } } + if (query.IsSpecialSeason.HasValue) { if (query.IsSpecialSeason.Value) @@ -4248,6 +4359,7 @@ namespace Emby.Server.Implementations.Data whereClauses.Add("IndexNumber <> 0"); } } + if (query.IsUnaired.HasValue) { if (query.IsUnaired.Value) @@ -4259,6 +4371,7 @@ namespace Emby.Server.Implementations.Data whereClauses.Add("PremiereDate < DATETIME('now')"); } } + var queryMediaTypes = query.MediaTypes.Where(IsValidMediaType).ToArray(); if (queryMediaTypes.Length == 1) { @@ -4274,6 +4387,7 @@ namespace Emby.Server.Implementations.Data whereClauses.Add("MediaType in (" + val + ")"); } + if (query.ItemIds.Length > 0) { var includeIds = new List<string>(); @@ -4286,11 +4400,13 @@ namespace Emby.Server.Implementations.Data { statement.TryBind("@IncludeId" + index, id); } + index++; } whereClauses.Add("(" + string.Join(" OR ", includeIds) + ")"); } + if (query.ExcludeItemIds.Length > 0) { var excludeIds = new List<string>(); @@ -4303,6 +4419,7 @@ namespace Emby.Server.Implementations.Data { statement.TryBind("@ExcludeId" + index, id); } + index++; } @@ -4316,7 +4433,7 @@ namespace Emby.Server.Implementations.Data var index = 0; foreach (var pair in query.ExcludeProviderIds) { - if (string.Equals(pair.Key, MetadataProviders.TmdbCollection.ToString(), StringComparison.OrdinalIgnoreCase)) + if (string.Equals(pair.Key, MetadataProvider.TmdbCollection.ToString(), StringComparison.OrdinalIgnoreCase)) { continue; } @@ -4327,6 +4444,7 @@ namespace Emby.Server.Implementations.Data { statement.TryBind(paramName, "%" + pair.Key + "=" + pair.Value + "%"); } + index++; break; @@ -4345,14 +4463,14 @@ namespace Emby.Server.Implementations.Data var index = 0; foreach (var pair in query.HasAnyProviderId) { - if (string.Equals(pair.Key, MetadataProviders.TmdbCollection.ToString(), StringComparison.OrdinalIgnoreCase)) + if (string.Equals(pair.Key, MetadataProvider.TmdbCollection.ToString(), StringComparison.OrdinalIgnoreCase)) { continue; } // TODO this seems to be an idea for a better schema where ProviderIds are their own table // buut this is not implemented - //hasProviderIds.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = '" + pair.Key + "'), '') <> " + paramName + ")"); + // hasProviderIds.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = '" + pair.Key + "'), '') <> " + paramName + ")"); // TODO this is a really BAD way to do it since the pair: // Tmdb, 1234 matches Tmdb=1234 but also Tmdb=1234567 @@ -4369,6 +4487,7 @@ namespace Emby.Server.Implementations.Data { statement.TryBind(paramName, "%" + pair.Key + "=" + pair.Value + "%"); } + index++; break; @@ -4419,6 +4538,7 @@ namespace Emby.Server.Implementations.Data { whereClauses.Add("(TopParentId=@TopParentId)"); } + if (statement != null) { statement.TryBind("@TopParentId", queryTopParentIds[0].ToString("N", CultureInfo.InvariantCulture)); @@ -4456,15 +4576,17 @@ namespace Emby.Server.Implementations.Data statement.TryBind("@AncestorId", query.AncestorIds[0]); } } + 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); @@ -4489,10 +4611,15 @@ namespace Emby.Server.Implementations.Data statement.TryBind("@UnratedType", query.BlockUnratedItems[0].ToString()); } } + if (query.BlockUnratedItems.Length > 1) { - var inClause = string.Join(",", query.BlockUnratedItems.Select(i => "'" + i.ToString() + "'")); - whereClauses.Add(string.Format("(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) @@ -4501,7 +4628,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 @@ -4778,10 +4905,10 @@ 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); } } @@ -4830,7 +4957,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(); @@ -4964,6 +5092,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type statement.TryBind("@ItemId", query.ItemId.ToByteArray()); } } + if (!query.AppearsInItemId.Equals(Guid.Empty)) { whereClauses.Add("Name in (Select Name from People where ItemId=@AppearsInItemId)"); @@ -4972,6 +5101,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type statement.TryBind("@AppearsInItemId", query.AppearsInItemId.ToByteArray()); } } + var queryPersonTypes = query.PersonTypes.Where(IsValidPersonType).ToList(); if (queryPersonTypes.Count == 1) @@ -4988,6 +5118,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type whereClauses.Add("PersonType in (" + val + ")"); } + var queryExcludePersonTypes = query.ExcludePersonTypes.Where(IsValidPersonType).ToList(); if (queryExcludePersonTypes.Count == 1) @@ -5004,6 +5135,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type whereClauses.Add("PersonType not in (" + val + ")"); } + if (query.MaxListOrder.HasValue) { whereClauses.Add("ListOrder<=@MaxListOrder"); @@ -5012,6 +5144,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type statement.TryBind("@MaxListOrder", query.MaxListOrder.Value); } } + if (!string.IsNullOrWhiteSpace(query.NameContains)) { whereClauses.Add("Name like @NameContains"); @@ -5038,7 +5171,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(); @@ -5054,14 +5188,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("(@ItemId, @AncestorId{0}, @AncestorIdText{0})", i.ToString(CultureInfo.InvariantCulture)); + insertText.AppendFormat( + CultureInfo.InvariantCulture, + "(@ItemId, @AncestorId{0}, @AncestorIdText{0}),", + i.ToString(CultureInfo.InvariantCulture)); } + // Remove last , + insertText.Length--; + using (var statement = PrepareStatement(db, insertText.ToString())) { statement.TryBind("@ItemId", itemIdBlob); @@ -5071,8 +5206,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)); } @@ -5151,6 +5287,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type var typeString = string.Join(",", withItemTypes.Select(i => "'" + i + "'")); commandText += " AND ItemId In (select guid from typedbaseitems where type in (" + typeString + "))"; } + if (excludeItemTypes.Count > 0) { var typeString = string.Join(",", excludeItemTypes.Select(i => "'" + i + "'")); @@ -5172,7 +5309,6 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } } } - } LogQueryTime("GetItemValueNames", commandText, now); @@ -5229,7 +5365,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type itemCountColumns = new Dictionary<string, string>() { - { "itemTypes", "(" + itemCountColumnQuery + ") as itemTypes"} + { "itemTypes", "(" + itemCountColumnQuery + ") as itemTypes" } }; } @@ -5352,7 +5488,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type connection.RunInTransaction( db => { - var statements = PrepareAll(db, statementTexts).ToList(); + var statements = PrepareAll(db, statementTexts); if (!isReturningZeroItems) { @@ -5403,7 +5539,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type + GetJoinUserDataText(query) + whereText; - using (var statement = statements[statements.Count - 1]) + using (var statement = statements[statements.Length - 1]) { statement.TryBind("@SelectType", returnType); if (EnableJoinUserData(query)) @@ -5551,10 +5687,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++) @@ -5596,6 +5732,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } startIndex += Limit; + insertText.Length = StartInsertText.Length; } } @@ -5615,7 +5752,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(); @@ -5623,7 +5761,6 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type db.Execute("delete from People where ItemId=@ItemId", itemIdBlob); InsertPeople(itemIdBlob, people, db); - }, TransactionMode); } } @@ -5634,10 +5771,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++) { @@ -5671,6 +5808,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } startIndex += Limit; + insertText.Length = StartInsertText.Length; } } @@ -5709,10 +5847,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) { @@ -5772,7 +5907,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(); @@ -5780,7 +5916,6 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type db.Execute("delete from mediastreams where ItemId=@ItemId", itemIdBlob); InsertMediaStreams(itemIdBlob, streams, db); - }, TransactionMode); } } @@ -5790,18 +5925,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++) @@ -5884,10 +6010,10 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } startIndex += Limit; + insertText.Length = _mediaStreamSaveColumnsInsertQuery.Length; } } - /// <summary> /// Gets the chapter. /// </summary> @@ -6067,10 +6193,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) { @@ -6119,14 +6242,14 @@ 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(); db.Execute("delete from mediaattachments where ItemId=@ItemId", itemIdBlob); InsertMediaAttachments(itemIdBlob, attachments, db, cancellationToken); - }, TransactionMode); } } @@ -6139,10 +6262,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++) @@ -6152,7 +6274,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; @@ -6185,6 +6310,8 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type statement.Reset(); statement.MoveNext(); } + + insertText.Length = _mediaAttachmentInsertPrefix.Length; } } @@ -6192,7 +6319,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type /// Gets the attachment. /// </summary> /// <param name="reader">The reader.</param> - /// <returns>MediaAttachment</returns> + /// <returns>MediaAttachment.</returns> private MediaAttachment GetMediaAttachment(IReadOnlyList<IResultSetValue> reader) { var item = new MediaAttachment diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index 6ee6230fc..2c4e8e0fc 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -4,6 +4,7 @@ using System; using System.Collections.Generic; using System.IO; using System.Threading; +using Jellyfin.Data.Entities; using MediaBrowser.Common.Configuration; using MediaBrowser.Controller.Entities; using MediaBrowser.Controller.Library; @@ -43,7 +44,8 @@ namespace Emby.Server.Implementations.Data var users = userDatasTableExists ? null : userManager.Users; - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { db.ExecuteAll(string.Join(";", new[] { @@ -134,10 +136,12 @@ namespace Emby.Server.Implementations.Data { throw new ArgumentNullException(nameof(userData)); } + if (internalUserId <= 0) { throw new ArgumentNullException(nameof(internalUserId)); } + if (string.IsNullOrEmpty(key)) { throw new ArgumentNullException(nameof(key)); @@ -152,6 +156,7 @@ namespace Emby.Server.Implementations.Data { throw new ArgumentNullException(nameof(userData)); } + if (internalUserId <= 0) { throw new ArgumentNullException(nameof(internalUserId)); @@ -174,7 +179,8 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection()) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { SaveUserData(db, internalUserId, key, userData); }, TransactionMode); @@ -234,7 +240,7 @@ namespace Emby.Server.Implementations.Data } /// <summary> - /// Persist all user data for the specified user + /// Persist all user data for the specified user. /// </summary> private void PersistAllUserData(long internalUserId, UserItemData[] userDataList, CancellationToken cancellationToken) { @@ -242,7 +248,8 @@ namespace Emby.Server.Implementations.Data using (var connection = GetConnection()) { - connection.RunInTransaction(db => + connection.RunInTransaction( + db => { foreach (var userItemData in userDataList) { @@ -308,7 +315,7 @@ namespace Emby.Server.Implementations.Data } /// <summary> - /// Return all user-data associated with the given user + /// Return all user-data associated with the given user. /// </summary> /// <param name="internalUserId"></param> /// <returns></returns> @@ -338,7 +345,7 @@ namespace Emby.Server.Implementations.Data } /// <summary> - /// Read a row from the specified reader into the provided userData object + /// Read a row from the specified reader into the provided userData object. /// </summary> /// <param name="reader"></param> private UserItemData ReadRow(IReadOnlyList<IResultSetValue> reader) @@ -346,7 +353,7 @@ namespace Emby.Server.Implementations.Data var userData = new UserItemData(); userData.Key = reader[0].ToString(); - //userData.UserId = reader[1].ReadGuidFromBlob(); + // userData.UserId = reader[1].ReadGuidFromBlob(); if (reader[2].SQLiteType != SQLiteType.Null) { diff --git a/Emby.Server.Implementations/Data/SqliteUserRepository.cs b/Emby.Server.Implementations/Data/SqliteUserRepository.cs deleted file mode 100644 index 0c3f26974..000000000 --- a/Emby.Server.Implementations/Data/SqliteUserRepository.cs +++ /dev/null @@ -1,240 +0,0 @@ -#pragma warning disable CS1591 - -using System; -using System.Collections.Generic; -using System.IO; -using System.Text.Json; -using MediaBrowser.Common.Json; -using MediaBrowser.Controller; -using MediaBrowser.Controller.Entities; -using MediaBrowser.Controller.Persistence; -using Microsoft.Extensions.Logging; -using SQLitePCL.pretty; - -namespace Emby.Server.Implementations.Data -{ - /// <summary> - /// Class SQLiteUserRepository - /// </summary> - public class SqliteUserRepository : BaseSqliteRepository, IUserRepository - { - private readonly JsonSerializerOptions _jsonOptions; - - public SqliteUserRepository( - ILogger<SqliteUserRepository> logger, - IServerApplicationPaths appPaths) - : base(logger) - { - _jsonOptions = JsonDefaults.GetOptions(); - - DbFilePath = Path.Combine(appPaths.DataPath, "users.db"); - } - - /// <summary> - /// Gets the name of the repository - /// </summary> - /// <value>The name.</value> - public string Name => "SQLite"; - - /// <summary> - /// Opens the connection to the database. - /// </summary> - public void Initialize() - { - using (var connection = GetConnection()) - { - var localUsersTableExists = TableExists(connection, "LocalUsersv2"); - - connection.RunQueries(new[] { - "create table if not exists LocalUsersv2 (Id INTEGER PRIMARY KEY, guid GUID NOT NULL, data BLOB NOT NULL)", - "drop index if exists idx_users" - }); - - if (!localUsersTableExists && TableExists(connection, "Users")) - { - TryMigrateToLocalUsersTable(connection); - } - - RemoveEmptyPasswordHashes(connection); - } - } - - private void TryMigrateToLocalUsersTable(ManagedConnection connection) - { - try - { - connection.RunQueries(new[] - { - "INSERT INTO LocalUsersv2 (guid, data) SELECT guid,data from users" - }); - } - catch (Exception ex) - { - Logger.LogError(ex, "Error migrating users database"); - } - } - - private void RemoveEmptyPasswordHashes(ManagedConnection connection) - { - foreach (var user in RetrieveAllUsers(connection)) - { - // If the user password is the sha1 hash of the empty string, remove it - if (!string.Equals(user.Password, "DA39A3EE5E6B4B0D3255BFEF95601890AFD80709", StringComparison.Ordinal) - && !string.Equals(user.Password, "$SHA1$DA39A3EE5E6B4B0D3255BFEF95601890AFD80709", StringComparison.Ordinal)) - { - continue; - } - - user.Password = null; - var serialized = JsonSerializer.SerializeToUtf8Bytes(user, _jsonOptions); - - connection.RunInTransaction(db => - { - using (var statement = db.PrepareStatement("update LocalUsersv2 set data=@data where Id=@InternalId")) - { - statement.TryBind("@InternalId", user.InternalId); - statement.TryBind("@data", serialized); - statement.MoveNext(); - } - }, TransactionMode); - } - } - - /// <summary> - /// Save a user in the repo - /// </summary> - public void CreateUser(User user) - { - if (user == null) - { - throw new ArgumentNullException(nameof(user)); - } - - var serialized = JsonSerializer.SerializeToUtf8Bytes(user, _jsonOptions); - - using (var connection = GetConnection()) - { - connection.RunInTransaction(db => - { - using (var statement = db.PrepareStatement("insert into LocalUsersv2 (guid, data) values (@guid, @data)")) - { - statement.TryBind("@guid", user.Id.ToByteArray()); - statement.TryBind("@data", serialized); - - statement.MoveNext(); - } - - var createdUser = GetUser(user.Id, connection); - - if (createdUser == null) - { - throw new ApplicationException("created user should never be null"); - } - - user.InternalId = createdUser.InternalId; - - }, TransactionMode); - } - } - - public void UpdateUser(User user) - { - if (user == null) - { - throw new ArgumentNullException(nameof(user)); - } - - var serialized = JsonSerializer.SerializeToUtf8Bytes(user, _jsonOptions); - - using (var connection = GetConnection()) - { - connection.RunInTransaction(db => - { - using (var statement = db.PrepareStatement("update LocalUsersv2 set data=@data where Id=@InternalId")) - { - statement.TryBind("@InternalId", user.InternalId); - statement.TryBind("@data", serialized); - statement.MoveNext(); - } - - }, TransactionMode); - } - } - - private User GetUser(Guid guid, ManagedConnection connection) - { - using (var statement = connection.PrepareStatement("select id,guid,data from LocalUsersv2 where guid=@guid")) - { - statement.TryBind("@guid", guid); - - foreach (var row in statement.ExecuteQuery()) - { - return GetUser(row); - } - } - - return null; - } - - private User GetUser(IReadOnlyList<IResultSetValue> row) - { - var id = row[0].ToInt64(); - var guid = row[1].ReadGuidFromBlob(); - - var user = JsonSerializer.Deserialize<User>(row[2].ToBlob(), _jsonOptions); - user.InternalId = id; - user.Id = guid; - return user; - } - - /// <summary> - /// Retrieve all users from the database - /// </summary> - /// <returns>IEnumerable{User}.</returns> - public List<User> RetrieveAllUsers() - { - using (var connection = GetConnection(true)) - { - return new List<User>(RetrieveAllUsers(connection)); - } - } - - /// <summary> - /// Retrieve all users from the database - /// </summary> - /// <returns>IEnumerable{User}.</returns> - private IEnumerable<User> RetrieveAllUsers(ManagedConnection connection) - { - foreach (var row in connection.Query("select id,guid,data from LocalUsersv2")) - { - yield return GetUser(row); - } - } - - /// <summary> - /// Deletes the user. - /// </summary> - /// <param name="user">The user.</param> - /// <returns>Task.</returns> - /// <exception cref="ArgumentNullException">user</exception> - public void DeleteUser(User user) - { - if (user == null) - { - throw new ArgumentNullException(nameof(user)); - } - - using (var connection = GetConnection()) - { - connection.RunInTransaction(db => - { - using (var statement = db.PrepareStatement("delete from LocalUsersv2 where Id=@id")) - { - statement.TryBind("@id", user.InternalId); - statement.MoveNext(); - } - }, TransactionMode); - } - } - } -} |