diff options
Diffstat (limited to 'Emby.Server.Implementations/Data')
7 files changed, 711 insertions, 942 deletions
diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index fba81306b..a5bb47afb 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -1,183 +1,144 @@ using System; using System.Collections.Generic; -using System.Globalization; using System.Linq; using System.Threading; using Microsoft.Extensions.Logging; -using SQLitePCL; using SQLitePCL.pretty; namespace Emby.Server.Implementations.Data { public abstract class BaseSqliteRepository : IDisposable { - protected string DbFilePath { get; set; } - protected ReaderWriterLockSlim WriteLock; - - protected ILogger Logger { get; private set; } + private bool _disposed = false; protected BaseSqliteRepository(ILogger logger) { Logger = logger; - - WriteLock = new ReaderWriterLockSlim(LockRecursionPolicy.NoRecursion); } - protected TransactionMode TransactionMode => TransactionMode.Deferred; + /// <summary> + /// Gets or sets the path to the DB file. + /// </summary> + /// <value>Path to the DB file.</value> + protected string DbFilePath { get; set; } - protected TransactionMode ReadTransactionMode => TransactionMode.Deferred; + /// <summary> + /// Gets the logger. + /// </summary> + /// <value>The logger.</value> + protected ILogger Logger { get; } - internal static int ThreadSafeMode { get; set; } + /// <summary> + /// Gets the default connection flags. + /// </summary> + /// <value>The default connection flags.</value> + protected virtual ConnectionFlags DefaultConnectionFlags => ConnectionFlags.NoMutex; - static BaseSqliteRepository() - { - SQLite3.EnableSharedCache = false; + /// <summary> + /// Gets the transaction mode. + /// </summary> + /// <value>The transaction mode.</value>> + protected TransactionMode TransactionMode => TransactionMode.Deferred; - int rc = raw.sqlite3_config(raw.SQLITE_CONFIG_MEMSTATUS, 0); - //CheckOk(rc); + /// <summary> + /// Gets the transaction mode for read-only operations. + /// </summary> + /// <value>The transaction mode.</value> + protected TransactionMode ReadTransactionMode => TransactionMode.Deferred; - rc = raw.sqlite3_config(raw.SQLITE_CONFIG_MULTITHREAD, 1); - //rc = raw.sqlite3_config(raw.SQLITE_CONFIG_SINGLETHREAD, 1); - //rc = raw.sqlite3_config(raw.SQLITE_CONFIG_SERIALIZED, 1); - //CheckOk(rc); + /// <summary> + /// Gets the cache size. + /// </summary> + /// <value>The cache size or null.</value> + protected virtual int? CacheSize => null; - rc = raw.sqlite3_enable_shared_cache(1); + /// <summary> + /// Gets the journal mode. + /// </summary> + /// <value>The journal mode.</value> + protected virtual string JournalMode => "WAL"; - ThreadSafeMode = raw.sqlite3_threadsafe(); - } + /// <summary> + /// Gets the page size. + /// </summary> + /// <value>The page size or null.</value> + protected virtual int? PageSize => null; + + /// <summary> + /// Gets the temp store mode. + /// </summary> + /// <value>The temp store mode.</value> + /// <see cref="TempStoreMode"/> + protected virtual TempStoreMode TempStore => TempStoreMode.Default; - private static bool _versionLogged; + /// <summary> + /// Gets the synchronous mode. + /// </summary> + /// <value>The synchronous mode or null.</value> + /// <see cref="SynchronousMode"/> + protected virtual SynchronousMode? Synchronous => null; - private string _defaultWal; - protected ManagedConnection _connection; + /// <summary> + /// Gets or sets the write lock. + /// </summary> + /// <value>The write lock.</value> + protected SemaphoreSlim WriteLock { get; set; } = new SemaphoreSlim(1, 1); - protected virtual bool EnableSingleConnection => true; + /// <summary> + /// Gets or sets the write connection. + /// </summary> + /// <value>The write connection.</value> + protected SQLiteDatabaseConnection WriteConnection { get; set; } - protected ManagedConnection CreateConnection(bool isReadOnly = false) + protected ManagedConnection GetConnection(bool _ = false) { - if (_connection != null) + WriteLock.Wait(); + if (WriteConnection != null) { - return _connection; + return new ManagedConnection(WriteConnection, WriteLock); } - lock (WriteLock) - { - if (!_versionLogged) - { - _versionLogged = true; - Logger.LogInformation("Sqlite version: " + SQLite3.Version); - Logger.LogInformation("Sqlite compiler options: " + string.Join(",", SQLite3.CompilerOptions.ToArray())); - } - - ConnectionFlags connectionFlags; - - if (isReadOnly) - { - //Logger.LogInformation("Opening read connection"); - //connectionFlags = ConnectionFlags.ReadOnly; - connectionFlags = ConnectionFlags.Create; - connectionFlags |= ConnectionFlags.ReadWrite; - } - else - { - //Logger.LogInformation("Opening write connection"); - connectionFlags = ConnectionFlags.Create; - connectionFlags |= ConnectionFlags.ReadWrite; - } + WriteConnection = SQLite3.Open( + DbFilePath, + DefaultConnectionFlags | ConnectionFlags.Create | ConnectionFlags.ReadWrite, + null); - if (EnableSingleConnection) - { - connectionFlags |= ConnectionFlags.PrivateCache; - } - else - { - connectionFlags |= ConnectionFlags.SharedCached; - } - - connectionFlags |= ConnectionFlags.NoMutex; - - var db = SQLite3.Open(DbFilePath, connectionFlags, null); - - try - { - if (string.IsNullOrWhiteSpace(_defaultWal)) - { - _defaultWal = db.Query("PRAGMA journal_mode").SelectScalarString().First(); - - Logger.LogInformation("Default journal_mode for {0} is {1}", DbFilePath, _defaultWal); - } - - var queries = new List<string> - { - //"PRAGMA cache size=-10000" - //"PRAGMA read_uncommitted = true", - "PRAGMA synchronous=Normal" - }; - - if (CacheSize.HasValue) - { - queries.Add("PRAGMA cache_size=" + CacheSize.Value.ToString(CultureInfo.InvariantCulture)); - } + if (CacheSize.HasValue) + { + WriteConnection.Execute("PRAGMA cache_size=" + CacheSize.Value); + } - if (EnableTempStoreMemory) - { - queries.Add("PRAGMA temp_store = memory"); - } - else - { - queries.Add("PRAGMA temp_store = file"); - } + if (!string.IsNullOrWhiteSpace(JournalMode)) + { + WriteConnection.Execute("PRAGMA journal_mode=" + JournalMode); + } - foreach (var query in queries) - { - db.Execute(query); - } - } - catch - { - using (db) - { + if (Synchronous.HasValue) + { + WriteConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); + } - } + if (PageSize.HasValue) + { + WriteConnection.Execute("PRAGMA page_size=" + PageSize.Value); + } - throw; - } + WriteConnection.Execute("PRAGMA temp_store=" + (int)TempStore); - _connection = new ManagedConnection(db, false); + // Configuration and pragmas can affect VACUUM so it needs to be last. + WriteConnection.Execute("VACUUM"); - return _connection; - } + return new ManagedConnection(WriteConnection, WriteLock); } public IStatement PrepareStatement(ManagedConnection connection, string sql) - { - return connection.PrepareStatement(sql); - } - - public IStatement PrepareStatementSafe(ManagedConnection connection, string sql) - { - return connection.PrepareStatement(sql); - } + => connection.PrepareStatement(sql); public IStatement PrepareStatement(IDatabaseConnection connection, string sql) - { - return connection.PrepareStatement(sql); - } - - public IStatement PrepareStatementSafe(IDatabaseConnection connection, string sql) - { - return connection.PrepareStatement(sql); - } - - public List<IStatement> PrepareAll(IDatabaseConnection connection, IEnumerable<string> sql) - { - return PrepareAllSafe(connection, sql); - } + => connection.PrepareStatement(sql); - public List<IStatement> PrepareAllSafe(IDatabaseConnection connection, IEnumerable<string> sql) - { - return sql.Select(connection.PrepareStatement).ToList(); - } + public IEnumerable<IStatement> PrepareAll(IDatabaseConnection connection, IEnumerable<string> sql) + => sql.Select(connection.PrepareStatement); protected bool TableExists(ManagedConnection connection, string name) { @@ -199,40 +160,33 @@ namespace Emby.Server.Implementations.Data }, ReadTransactionMode); } - protected void RunDefaultInitialization(ManagedConnection db) + protected List<string> GetColumnNames(IDatabaseConnection connection, string table) { - var queries = new List<string> - { - "PRAGMA journal_mode=WAL", - "PRAGMA page_size=4096", - "PRAGMA synchronous=Normal" - }; + var columnNames = new List<string>(); - if (EnableTempStoreMemory) - { - queries.AddRange(new List<string> - { - "pragma default_temp_store = memory", - "pragma temp_store = memory" - }); - } - else + foreach (var row in connection.Query("PRAGMA table_info(" + table + ")")) { - queries.AddRange(new List<string> + if (row[1].SQLiteType != SQLiteType.Null) { - "pragma temp_store = file" - }); + var name = row[1].ToString(); + + columnNames.Add(name); + } } - db.ExecuteAll(string.Join(";", queries)); - Logger.LogInformation("PRAGMA synchronous=" + db.Query("PRAGMA synchronous").SelectScalarString().First()); + return columnNames; } - protected virtual bool EnableTempStoreMemory => false; + protected void AddColumn(IDatabaseConnection connection, string table, string columnName, string type, List<string> existingColumnNames) + { + if (existingColumnNames.Contains(columnName, StringComparer.OrdinalIgnoreCase)) + { + return; + } - protected virtual int? CacheSize => null; + connection.Execute("alter table " + table + " add column " + columnName + " " + type + " NULL"); + } - private bool _disposed; protected void CheckDisposed() { if (_disposed) @@ -241,139 +195,94 @@ namespace Emby.Server.Implementations.Data } } + /// <inheritdoc /> public void Dispose() { - _disposed = true; Dispose(true); + GC.SuppressFinalize(this); } - private readonly object _disposeLock = new object(); - /// <summary> /// Releases unmanaged and - optionally - managed resources. /// </summary> /// <param name="dispose"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param> protected virtual void Dispose(bool dispose) { - if (dispose) + if (_disposed) { - DisposeConnection(); + return; } - } - private void DisposeConnection() - { - try + if (dispose) { - lock (_disposeLock) + WriteLock.Wait(); + try { - using (WriteLock.Write()) - { - if (_connection != null) - { - using (_connection) - { - _connection.Close(); - } - _connection = null; - } - - CloseConnection(); - } + WriteConnection?.Dispose(); } + finally + { + WriteLock.Release(); + } + + WriteLock.Dispose(); } - catch (Exception ex) - { - Logger.LogError(ex, "Error disposing database"); - } - } - protected virtual void CloseConnection() - { + WriteConnection = null; + WriteLock = null; + _disposed = true; } + } - protected List<string> GetColumnNames(IDatabaseConnection connection, string table) - { - var list = new List<string>(); - - foreach (var row in connection.Query("PRAGMA table_info(" + table + ")")) - { - if (row[1].SQLiteType != SQLiteType.Null) - { - var name = row[1].ToString(); - - list.Add(name); - } - } + /// <summary> + /// The disk synchronization mode, controls how aggressively SQLite will write data + /// all the way out to physical storage. + /// </summary> + public enum SynchronousMode + { + /// <summary> + /// SQLite continues without syncing as soon as it has handed data off to the operating system + /// </summary> + Off = 0, - return list; - } + /// <summary> + /// SQLite database engine will still sync at the most critical moments + /// </summary> + Normal = 1, - protected void AddColumn(IDatabaseConnection connection, string table, string columnName, string type, List<string> existingColumnNames) - { - if (existingColumnNames.Contains(columnName, StringComparer.OrdinalIgnoreCase)) - { - return; - } + /// <summary> + /// SQLite database engine will use the xSync method of the VFS + /// to ensure that all content is safely written to the disk surface prior to continuing. + /// </summary> + Full = 2, - connection.Execute("alter table " + table + " add column " + columnName + " " + type + " NULL"); - } + /// <summary> + /// EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal + /// is synced after that journal is unlinked to commit a transaction in DELETE mode. + /// </summary> + Extra = 3 } - public static class ReaderWriterLockSlimExtensions + /// <summary> + /// Storage mode used by temporary database files. + /// </summary> + public enum TempStoreMode { - private sealed class ReadLockToken : IDisposable - { - private ReaderWriterLockSlim _sync; - public ReadLockToken(ReaderWriterLockSlim sync) - { - _sync = sync; - sync.EnterReadLock(); - } - public void Dispose() - { - if (_sync != null) - { - _sync.ExitReadLock(); - _sync = null; - } - } - } - private sealed class WriteLockToken : IDisposable - { - private ReaderWriterLockSlim _sync; - public WriteLockToken(ReaderWriterLockSlim sync) - { - _sync = sync; - sync.EnterWriteLock(); - } - public void Dispose() - { - if (_sync != null) - { - _sync.ExitWriteLock(); - _sync = null; - } - } - } + /// <summary> + /// The compile-time C preprocessor macro SQLITE_TEMP_STORE + /// is used to determine where temporary tables and indices are stored. + /// </summary> + Default = 0, - public static IDisposable Read(this ReaderWriterLockSlim obj) - { - //if (BaseSqliteRepository.ThreadSafeMode > 0) - //{ - // return new DummyToken(); - //} - return new WriteLockToken(obj); - } + /// <summary> + /// Temporary tables and indices are stored in a file. + /// </summary> + File = 1, - public static IDisposable Write(this ReaderWriterLockSlim obj) - { - //if (BaseSqliteRepository.ThreadSafeMode > 0) - //{ - // return new DummyToken(); - //} - return new WriteLockToken(obj); - } + /// <summary> + /// Temporary tables and indices are kept in as if they were pure in-memory databases memory. + /// </summary> + Memory = 2 } } diff --git a/Emby.Server.Implementations/Data/ManagedConnection.cs b/Emby.Server.Implementations/Data/ManagedConnection.cs index b8f1e581a..4c3424410 100644 --- a/Emby.Server.Implementations/Data/ManagedConnection.cs +++ b/Emby.Server.Implementations/Data/ManagedConnection.cs @@ -1,79 +1,78 @@ using System; using System.Collections.Generic; +using System.Threading; using SQLitePCL.pretty; namespace Emby.Server.Implementations.Data { public class ManagedConnection : IDisposable { - private SQLiteDatabaseConnection db; - private readonly bool _closeOnDispose; + private SQLiteDatabaseConnection _db; + private readonly SemaphoreSlim _writeLock; + private bool _disposed = false; - public ManagedConnection(SQLiteDatabaseConnection db, bool closeOnDispose) + public ManagedConnection(SQLiteDatabaseConnection db, SemaphoreSlim writeLock) { - this.db = db; - _closeOnDispose = closeOnDispose; + _db = db; + _writeLock = writeLock; } public IStatement PrepareStatement(string sql) { - return db.PrepareStatement(sql); + return _db.PrepareStatement(sql); } public IEnumerable<IStatement> PrepareAll(string sql) { - return db.PrepareAll(sql); + return _db.PrepareAll(sql); } public void ExecuteAll(string sql) { - db.ExecuteAll(sql); + _db.ExecuteAll(sql); } public void Execute(string sql, params object[] values) { - db.Execute(sql, values); + _db.Execute(sql, values); } public void RunQueries(string[] sql) { - db.RunQueries(sql); + _db.RunQueries(sql); } public void RunInTransaction(Action<IDatabaseConnection> action, TransactionMode mode) { - db.RunInTransaction(action, mode); + _db.RunInTransaction(action, mode); } public T RunInTransaction<T>(Func<IDatabaseConnection, T> action, TransactionMode mode) { - return db.RunInTransaction(action, mode); + return _db.RunInTransaction(action, mode); } public IEnumerable<IReadOnlyList<IResultSetValue>> Query(string sql) { - return db.Query(sql); + return _db.Query(sql); } public IEnumerable<IReadOnlyList<IResultSetValue>> Query(string sql, params object[] values) { - return db.Query(sql, values); - } - - public void Close() - { - using (db) - { - - } + return _db.Query(sql, values); } public void Dispose() { - if (_closeOnDispose) + if (_disposed) { - Close(); + return; } + + _writeLock.Release(); + + _db = null; // Don't dispose it + _disposed = true; } } } diff --git a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs index 47552806d..b1c17b92e 100644 --- a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs @@ -1,5 +1,6 @@ using System; using System.Collections.Generic; +using System.Globalization; using System.IO; using System.Threading; using MediaBrowser.Common.Configuration; @@ -18,13 +19,13 @@ namespace Emby.Server.Implementations.Data /// </summary> public class SqliteDisplayPreferencesRepository : BaseSqliteRepository, IDisplayPreferencesRepository { - protected IFileSystem FileSystem { get; private set; } + private readonly IFileSystem _fileSystem; - public SqliteDisplayPreferencesRepository(ILoggerFactory loggerFactory, IJsonSerializer jsonSerializer, IApplicationPaths appPaths, IFileSystem fileSystem) - : base(loggerFactory.CreateLogger(nameof(SqliteDisplayPreferencesRepository))) + public SqliteDisplayPreferencesRepository(ILogger<SqliteDisplayPreferencesRepository> logger, IJsonSerializer jsonSerializer, IApplicationPaths appPaths, IFileSystem fileSystem) + : base(logger) { _jsonSerializer = jsonSerializer; - FileSystem = fileSystem; + _fileSystem = fileSystem; DbFilePath = Path.Combine(appPaths.DataPath, "displaypreferences.db"); } @@ -49,7 +50,7 @@ namespace Emby.Server.Implementations.Data { Logger.LogError(ex, "Error loading database file. Will reset and retry."); - FileSystem.DeleteFile(DbFilePath); + _fileSystem.DeleteFile(DbFilePath); InitializeInternal(); } @@ -61,10 +62,8 @@ namespace Emby.Server.Implementations.Data /// <returns>Task.</returns> private void InitializeInternal() { - using (var connection = CreateConnection()) + using (var connection = GetConnection()) { - RunDefaultInitialization(connection); - string[] queries = { "create table if not exists userdisplaypreferences (id GUID NOT NULL, userId GUID NOT NULL, client text NOT NULL, data BLOB NOT NULL)", @@ -98,15 +97,12 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - SaveDisplayPreferences(displayPreferences, userId, client, db); - }, TransactionMode); - } + SaveDisplayPreferences(displayPreferences, userId, client, db); + }, TransactionMode); } } @@ -142,18 +138,15 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + foreach (var displayPreference in displayPreferences) { - foreach (var displayPreference in displayPreferences) - { - SaveDisplayPreferences(displayPreference, userId, displayPreference.Client, db); - } - }, TransactionMode); - } + SaveDisplayPreferences(displayPreference, userId, displayPreference.Client, db); + } + }, TransactionMode); } } @@ -174,27 +167,24 @@ namespace Emby.Server.Implementations.Data var guidId = displayPreferencesId.GetMD5(); - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where id = @id and userId=@userId and client=@client")) { - using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where id = @id and userId=@userId and client=@client")) - { - statement.TryBind("@id", guidId.ToGuidBlob()); - statement.TryBind("@userId", userId.ToGuidBlob()); - statement.TryBind("@client", client); - - foreach (var row in statement.ExecuteQuery()) - { - return Get(row); - } - } + statement.TryBind("@id", guidId.ToGuidBlob()); + statement.TryBind("@userId", userId.ToGuidBlob()); + statement.TryBind("@client", client); - return new DisplayPreferences + foreach (var row in statement.ExecuteQuery()) { - Id = guidId.ToString("N") - }; + return Get(row); + } } + + return new DisplayPreferences + { + Id = guidId.ToString("N", CultureInfo.InvariantCulture) + }; } } @@ -208,18 +198,15 @@ namespace Emby.Server.Implementations.Data { var list = new List<DisplayPreferences>(); - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where userId=@userId")) { - using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where userId=@userId")) - { - statement.TryBind("@userId", userId.ToGuidBlob()); + statement.TryBind("@userId", userId.ToGuidBlob()); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(Get(row)); - } + foreach (var row in statement.ExecuteQuery()) + { + list.Add(Get(row)); } } } @@ -228,13 +215,7 @@ namespace Emby.Server.Implementations.Data } private DisplayPreferences Get(IReadOnlyList<IResultSetValue> row) - { - using (var stream = new MemoryStream(row[0].ToBlob())) - { - stream.Position = 0; - return _jsonSerializer.DeserializeFromStream<DisplayPreferences>(stream); - } - } + => _jsonSerializer.DeserializeFromString<DisplayPreferences>(row.GetString(0)); public void SaveDisplayPreferences(DisplayPreferences displayPreferences, string userId, string client, CancellationToken cancellationToken) { diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index a486cb1a0..0fb2c10fd 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -18,10 +18,6 @@ namespace Emby.Server.Implementations.Data connection.RunInTransaction(conn => { - //foreach (var query in queries) - //{ - // conn.Execute(query); - //} conn.ExecuteAll(string.Join(";", queries)); }); } @@ -38,7 +34,8 @@ namespace Emby.Server.Implementations.Data public static Guid ReadGuidFromBlob(this IResultSetValue result) { - return new Guid(result.ToBlob()); + // TODO: Remove ToArray when upgrading to netstandard2.1 + return new Guid(result.ToBlob().ToArray()); } public static string ToDateTimeParamValue(this DateTime dateValue) @@ -141,7 +138,7 @@ namespace Emby.Server.Implementations.Data } } - public static void Attach(ManagedConnection db, string path, string alias) + public static void Attach(SQLiteDatabaseConnection db, string path, string alias) { var commandText = string.Format("attach @path as {0};", alias); diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 8841a9a50..3ca0728fe 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -36,13 +36,9 @@ namespace Emby.Server.Implementations.Data /// </summary> public class SqliteItemRepository : BaseSqliteRepository, IItemRepository { - private readonly TypeMapper _typeMapper; + private const string ChaptersTableName = "Chapters2"; - /// <summary> - /// Gets the name of the repository - /// </summary> - /// <value>The name.</value> - public string Name => "SQLite"; + private readonly TypeMapper _typeMapper; /// <summary> /// Gets the json serializer. @@ -54,12 +50,9 @@ namespace Emby.Server.Implementations.Data /// The _app paths /// </summary> private readonly IServerConfigurationManager _config; - private IServerApplicationHost _appHost; - + private readonly IServerApplicationHost _appHost; private readonly ILocalizationManager _localization; - public IImageProcessor ImageProcessor { get; set; } - /// <summary> /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. /// </summary> @@ -90,21 +83,24 @@ namespace Emby.Server.Implementations.Data DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); } - private const string ChaptersTableName = "Chapters2"; + /// <inheritdoc /> + public string Name => "SQLite"; + /// <inheritdoc /> protected override int? CacheSize => 20000; - protected override bool EnableTempStoreMemory => true; + /// <inheritdoc /> + protected override TempStoreMode TempStore => TempStoreMode.Memory; + + public IImageProcessor ImageProcessor { get; set; } /// <summary> /// Opens the connection to the database /// </summary> public void Initialize(SqliteUserDataRepository userDataRepo, IUserManager userManager) { - using (var connection = CreateConnection()) + using (var connection = GetConnection()) { - RunDefaultInitialization(connection); - const string createMediaStreamsTableCommand = "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, TimeBase TEXT NULL, CodecTimeBase TEXT NULL, ColorPrimaries TEXT NULL, ColorSpace TEXT NULL, ColorTransfer TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))"; @@ -319,7 +315,7 @@ namespace Emby.Server.Implementations.Data connection.RunQueries(postQueries); } - userDataRepo.Initialize(WriteLock, _connection, userManager); + userDataRepo.Initialize(userManager, WriteLock, WriteConnection); } private static readonly string[] _retriveItemColumns = @@ -520,10 +516,11 @@ namespace Emby.Server.Implementations.Data { saveItemCommandCommandText += ","; } + saveItemCommandCommandText += "@" + saveColumns[i]; } - saveItemCommandCommandText += ")"; - return saveItemCommandCommandText; + + return saveItemCommandCommandText + ")"; } /// <summary> @@ -551,21 +548,18 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var saveImagesStatement = base.PrepareStatement(db, "Update TypedBaseItems set Images=@Images where guid=@Id")) { - using (var saveImagesStatement = PrepareStatement(db, "Update TypedBaseItems set Images=@Images where guid=@Id")) - { - saveImagesStatement.TryBind("@Id", item.Id.ToGuidBlob()); - saveImagesStatement.TryBind("@Images", SerializeImages(item)); + saveImagesStatement.TryBind("@Id", item.Id.ToGuidBlob()); + saveImagesStatement.TryBind("@Images", SerializeImages(item)); - saveImagesStatement.MoveNext(); - } - }, TransactionMode); - } + saveImagesStatement.MoveNext(); + } + }, TransactionMode); } } @@ -605,26 +599,22 @@ namespace Emby.Server.Implementations.Data tuples.Add((item, ancestorIds, topParent, userdataKey, inheritedTags)); } - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - SaveItemsInTranscation(db, tuples); - - }, TransactionMode); - } + SaveItemsInTranscation(db, tuples); + }, TransactionMode); } } private void SaveItemsInTranscation(IDatabaseConnection db, IEnumerable<(BaseItem, List<Guid>, BaseItem, string, List<string>)> tuples) { - var statements = PrepareAllSafe(db, new string[] + var statements = PrepareAll(db, new string[] { GetSaveItemCommandText(), "delete from AncestorIds where ItemId=@ItemId" - }); + }).ToList(); using (var saveItemStatement = statements[0]) using (var deleteAncestorsStatement = statements[1]) @@ -706,7 +696,7 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBindNull("@EndDate"); } - saveItemStatement.TryBind("@ChannelId", item.ChannelId.Equals(Guid.Empty) ? null : item.ChannelId.ToString("N")); + saveItemStatement.TryBind("@ChannelId", item.ChannelId.Equals(Guid.Empty) ? null : item.ChannelId.ToString("N", CultureInfo.InvariantCulture)); if (item is IHasProgramAttributes hasProgramAttributes) { @@ -861,7 +851,7 @@ namespace Emby.Server.Implementations.Data } else { - saveItemStatement.TryBind("@TopParentId", topParent.Id.ToString("N")); + saveItemStatement.TryBind("@TopParentId", topParent.Id.ToString("N", CultureInfo.InvariantCulture)); } if (item is Trailer trailer && trailer.TrailerTypes.Length > 0) @@ -999,6 +989,7 @@ namespace Emby.Server.Implementations.Data { albumArtists = string.Join("|", hasAlbumArtists.AlbumArtists); } + saveItemStatement.TryBind("@AlbumArtists", albumArtists); saveItemStatement.TryBind("@ExternalId", item.ExternalId); @@ -1035,6 +1026,7 @@ namespace Emby.Server.Implementations.Data { continue; } + str.Append($"{i.Key}={i.Value}|"); } @@ -1042,6 +1034,7 @@ namespace Emby.Server.Implementations.Data { return null; } + str.Length -= 1; // Remove last | return str.ToString(); } @@ -1079,6 +1072,7 @@ namespace Emby.Server.Implementations.Data { return null; } + StringBuilder str = new StringBuilder(); foreach (var i in images) { @@ -1088,6 +1082,7 @@ namespace Emby.Server.Implementations.Data } str.Append(ToValueString(i) + "|"); } + str.Length -= 1; // Remove last | return str.ToString(); } @@ -1193,23 +1188,20 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + using (var statement = PrepareStatement(connection, "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid")) { - using (var statement = PrepareStatementSafe(connection, "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid")) - { - statement.TryBind("@guid", id); + statement.TryBind("@guid", id); - foreach (var row in statement.ExecuteQuery()) - { - return GetItem(row, new InternalItemsQuery()); - } + foreach (var row in statement.ExecuteQuery()) + { + return GetItem(row, new InternalItemsQuery()); } - - return null; } } + + return null; } private bool TypeRequiresDeserialization(Type type) @@ -1306,18 +1298,13 @@ namespace Emby.Server.Implementations.Data if (TypeRequiresDeserialization(type)) { - using (var stream = new MemoryStream(reader[1].ToBlob())) + try { - stream.Position = 0; - - try - { - item = _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem; - } - catch (SerializationException ex) - { - Logger.LogError(ex, "Error deserializing item"); - } + item = _jsonSerializer.DeserializeFromString(reader.GetString(1), type) as BaseItem; + } + catch (SerializationException ex) + { + Logger.LogError(ex, "Error deserializing item"); } } @@ -1909,24 +1896,21 @@ namespace Emby.Server.Implementations.Data { CheckDisposed(); - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + var chapters = new List<ChapterInfo>(); + + using (var statement = PrepareStatement(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc")) { - var list = new List<ChapterInfo>(); + statement.TryBind("@ItemId", item.Id); - using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc")) + foreach (var row in statement.ExecuteQuery()) { - statement.TryBind("@ItemId", item.Id); - - foreach (var row in statement.ExecuteQuery()) - { - list.Add(GetChapter(row, item)); - } + chapters.Add(GetChapter(row, item)); } - - return list; } + + return chapters; } } @@ -1941,22 +1925,20 @@ namespace Emby.Server.Implementations.Data { CheckDisposed(); - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + using (var statement = PrepareStatement(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex")) { - using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex")) - { - statement.TryBind("@ItemId", item.Id); - statement.TryBind("@ChapterIndex", index); + statement.TryBind("@ItemId", item.Id); + statement.TryBind("@ChapterIndex", index); - foreach (var row in statement.ExecuteQuery()) - { - return GetChapter(row, item); - } + foreach (var row in statement.ExecuteQuery()) + { + return GetChapter(row, item); } } } + return null; } @@ -2012,21 +1994,18 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException(nameof(chapters)); } - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - var idBlob = id.ToGuidBlob(); + var idBlob = id.ToGuidBlob(); // First delete chapters db.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", idBlob); - InsertChapters(idBlob, chapters, db); + InsertChapters(idBlob, chapters, db); - }, TransactionMode); - } + }, TransactionMode); } } @@ -2048,7 +2027,7 @@ namespace Emby.Server.Implementations.Data } insertText.Length -= 1; // Remove last , - using (var statement = PrepareStatementSafe(db, insertText.ToString())) + using (var statement = PrepareStatement(db, insertText.ToString())) { statement.TryBind("@ItemId", idBlob); @@ -2551,29 +2530,25 @@ namespace Emby.Server.Implementations.Data commandText += " where " + string.Join(" AND ", whereClauses); } - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + using (var statement = PrepareStatement(connection, commandText)) { - using (var statement = PrepareStatementSafe(connection, commandText)) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - var count = statement.ExecuteQuery().SelectScalarInt().First(); - LogQueryTime("GetCount", commandText, now); - return count; - } + var count = statement.ExecuteQuery().SelectScalarInt().First(); + LogQueryTime("GetCount", commandText, now); + return count; } - } } @@ -2624,67 +2599,64 @@ namespace Emby.Server.Implementations.Data } } - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) - { - var list = new List<BaseItem>(); + var items = new List<BaseItem>(); - using (var statement = PrepareStatementSafe(connection, commandText)) + using (var statement = PrepareStatement(connection, commandText)) + { + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - var hasEpisodeAttributes = HasEpisodeAttributes(query); - var hasServiceName = HasServiceName(query); - var hasProgramAttributes = HasProgramAttributes(query); - var hasStartDate = HasStartDate(query); - var hasTrailerTypes = HasTrailerTypes(query); - var hasArtistFields = HasArtistFields(query); - var hasSeriesFields = HasSeriesFields(query); + var hasEpisodeAttributes = HasEpisodeAttributes(query); + var hasServiceName = HasServiceName(query); + var hasProgramAttributes = HasProgramAttributes(query); + var hasStartDate = HasStartDate(query); + var hasTrailerTypes = HasTrailerTypes(query); + var hasArtistFields = HasArtistFields(query); + var hasSeriesFields = HasSeriesFields(query); - foreach (var row in statement.ExecuteQuery()) + foreach (var row in statement.ExecuteQuery()) + { + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + if (item != null) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); - if (item != null) - { - list.Add(item); - } + items.Add(item); } } + } + + // Hack for right now since we currently don't support filtering out these duplicates within a query + if (query.EnableGroupByMetadataKey) + { + var limit = query.Limit ?? int.MaxValue; + limit -= 4; + var newList = new List<BaseItem>(); - // Hack for right now since we currently don't support filtering out these duplicates within a query - if (query.EnableGroupByMetadataKey) + foreach (var item in items) { - var limit = query.Limit ?? int.MaxValue; - limit -= 4; - var newList = new List<BaseItem>(); + AddItem(newList, item); - foreach (var item in list) + if (newList.Count >= limit) { - AddItem(newList, item); - - if (newList.Count >= limit) - { - break; - } + break; } - - list = newList; } - LogQueryTime("GetItemList", commandText, now); - - return list; + items = newList; } + + LogQueryTime("GetItemList", commandText, now); + + return items; } } @@ -2845,75 +2817,72 @@ namespace Emby.Server.Implementations.Data statementTexts.Add(commandText); } - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + return connection.RunInTransaction(db => { - return connection.RunInTransaction(db => - { - var result = new QueryResult<BaseItem>(); - var statements = PrepareAllSafe(db, statementTexts); + var result = new QueryResult<BaseItem>(); + var statements = PrepareAll(db, statementTexts).ToList(); - if (!isReturningZeroItems) + if (!isReturningZeroItems) + { + using (var statement = statements[0]) { - using (var statement = statements[0]) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); // Running this again will bind the params GetWhereClauses(query, statement); - var hasEpisodeAttributes = HasEpisodeAttributes(query); - var hasServiceName = HasServiceName(query); - var hasProgramAttributes = HasProgramAttributes(query); - var hasStartDate = HasStartDate(query); - var hasTrailerTypes = HasTrailerTypes(query); - var hasArtistFields = HasArtistFields(query); - var hasSeriesFields = HasSeriesFields(query); + var hasEpisodeAttributes = HasEpisodeAttributes(query); + var hasServiceName = HasServiceName(query); + var hasProgramAttributes = HasProgramAttributes(query); + var hasStartDate = HasStartDate(query); + var hasTrailerTypes = HasTrailerTypes(query); + var hasArtistFields = HasArtistFields(query); + var hasSeriesFields = HasSeriesFields(query); - foreach (var row in statement.ExecuteQuery()) + foreach (var row in statement.ExecuteQuery()) + { + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + if (item != null) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); - if (item != null) - { - list.Add(item); - } + list.Add(item); } } } + } - if (query.EnableTotalRecordCount) + if (query.EnableTotalRecordCount) + { + using (var statement = statements[statements.Count - 1]) { - using (var statement = statements[statements.Count - 1]) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); // Running this again will bind the params GetWhereClauses(query, statement); - result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); - } + result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); } + } - LogQueryTime("GetItems", commandText, now); + LogQueryTime("GetItems", commandText, now); - result.Items = list.ToArray(); - return result; + result.Items = list.ToArray(); + return result; - }, ReadTransactionMode); - } + }, ReadTransactionMode); } } @@ -3080,35 +3049,32 @@ namespace Emby.Server.Implementations.Data } } - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) - { - var list = new List<Guid>(); + var list = new List<Guid>(); - using (var statement = PrepareStatementSafe(connection, commandText)) + using (var statement = PrepareStatement(connection, commandText)) + { + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(row[0].ReadGuidFromBlob()); - } + foreach (var row in statement.ExecuteQuery()) + { + list.Add(row[0].ReadGuidFromBlob()); } + } - LogQueryTime("GetItemList", commandText, now); + LogQueryTime("GetItemList", commandText, now); - return list; - } + return list; } } @@ -3149,39 +3115,36 @@ namespace Emby.Server.Implementations.Data } } - using (WriteLock.Read()) + var list = new List<Tuple<Guid, string>>(); + using (var connection = GetConnection(true)) { - var list = new List<Tuple<Guid, string>>(); - using (var connection = CreateConnection(true)) + using (var statement = PrepareStatement(connection, commandText)) { - using (var statement = PrepareStatementSafe(connection, commandText)) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - foreach (var row in statement.ExecuteQuery()) - { - var id = row.GetGuid(0); - string path = null; + foreach (var row in statement.ExecuteQuery()) + { + var id = row.GetGuid(0); + string path = null; - if (!row.IsDBNull(1)) - { - path = row.GetString(1); - } - list.Add(new Tuple<Guid, string>(id, path)); + if (!row.IsDBNull(1)) + { + path = row.GetString(1); } + list.Add(new Tuple<Guid, string>(id, path)); } } + } - LogQueryTime("GetItemIdsWithPath", commandText, now); + LogQueryTime("GetItemIdsWithPath", commandText, now); - return list; - } + return list; } public QueryResult<Guid> GetItemIds(InternalItemsQuery query) @@ -3265,64 +3228,61 @@ namespace Emby.Server.Implementations.Data statementTexts.Add(commandText); } - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + return connection.RunInTransaction(db => { - return connection.RunInTransaction(db => - { - var result = new QueryResult<Guid>(); + var result = new QueryResult<Guid>(); - var statements = PrepareAllSafe(db, statementTexts); + var statements = PrepareAll(db, statementTexts).ToList(); - if (!isReturningZeroItems) + if (!isReturningZeroItems) + { + using (var statement = statements[0]) { - using (var statement = statements[0]) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); // Running this again will bind the params GetWhereClauses(query, statement); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(row[0].ReadGuidFromBlob()); - } + foreach (var row in statement.ExecuteQuery()) + { + list.Add(row[0].ReadGuidFromBlob()); } } + } - if (query.EnableTotalRecordCount) + if (query.EnableTotalRecordCount) + { + using (var statement = statements[statements.Count - 1]) { - using (var statement = statements[statements.Count - 1]) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); // Running this again will bind the params GetWhereClauses(query, statement); - result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); - } + result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); } + } - LogQueryTime("GetItemIds", commandText, now); + LogQueryTime("GetItemIds", commandText, now); - result.Items = list.ToArray(); - return result; + result.Items = list.ToArray(); + return result; - }, ReadTransactionMode); - } + }, ReadTransactionMode); } } @@ -3583,12 +3543,12 @@ namespace Emby.Server.Implementations.Data whereClauses.Add("ChannelId=@ChannelId"); if (statement != null) { - statement.TryBind("@ChannelId", query.ChannelIds[0].ToString("N")); + statement.TryBind("@ChannelId", query.ChannelIds[0].ToString("N", CultureInfo.InvariantCulture)); } } else if (query.ChannelIds.Length > 1) { - var inClause = string.Join(",", query.ChannelIds.Select(i => "'" + i.ToString("N") + "'")); + var inClause = string.Join(",", query.ChannelIds.Select(i => "'" + i.ToString("N", CultureInfo.InvariantCulture) + "'")); whereClauses.Add($"ChannelId in ({inClause})"); } @@ -4572,12 +4532,12 @@ namespace Emby.Server.Implementations.Data } if (statement != null) { - statement.TryBind("@TopParentId", queryTopParentIds[0].ToString("N")); + statement.TryBind("@TopParentId", queryTopParentIds[0].ToString("N", CultureInfo.InvariantCulture)); } } else if (queryTopParentIds.Length > 1) { - var val = string.Join(",", queryTopParentIds.Select(i => "'" + i.ToString("N") + "'")); + var val = string.Join(",", queryTopParentIds.Select(i => "'" + i.ToString("N", CultureInfo.InvariantCulture) + "'")); if (enableItemsByName && includedItemByNameTypes.Count == 1) { @@ -4609,7 +4569,7 @@ namespace Emby.Server.Implementations.Data } if (query.AncestorIds.Length > 1) { - var inClause = string.Join(",", query.AncestorIds.Select(i => "'" + i.ToString("N") + "'")); + 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)); } if (!string.IsNullOrWhiteSpace(query.AncestorWithPresentationUniqueKey)) @@ -4672,7 +4632,7 @@ namespace Emby.Server.Implementations.Data foreach (var folderId in query.BoxSetLibraryFolders) { - folderIdQueries.Add("data like '%" + folderId.ToString("N") + "%'"); + folderIdQueries.Add("data like '%" + folderId.ToString("N", CultureInfo.InvariantCulture) + "%'"); } whereClauses.Add("(" + string.Join(" OR ", folderIdQueries) + ")"); @@ -4899,14 +4859,12 @@ namespace Emby.Server.Implementations.Data private void UpdateInheritedTags(CancellationToken cancellationToken) { - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + connection.ExecuteAll(string.Join(";", new string[] { - connection.ExecuteAll(string.Join(";", new string[] - { "delete from itemvalues where type = 6", "insert into itemvalues (ItemId, Type, Value, CleanValue) select ItemId, 6, Value, CleanValue from ItemValues where Type=4", @@ -4916,10 +4874,9 @@ FROM AncestorIds LEFT JOIN ItemValues ON (AncestorIds.AncestorId = ItemValues.ItemId) where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type = 4 " - })); + })); - }, TransactionMode); - } + }, TransactionMode); } } @@ -4965,13 +4922,11 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type CheckDisposed(); - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - var idBlob = id.ToGuidBlob(); + var idBlob = id.ToGuidBlob(); // Delete people ExecuteWithSingleParam(db, "delete from People where ItemId=@Id", idBlob); @@ -4990,8 +4945,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type // Delete the item ExecuteWithSingleParam(db, "delete from TypedBaseItems where guid=@Id", idBlob); - }, TransactionMode); - } + }, TransactionMode); } } @@ -5025,23 +4979,20 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type commandText += " order by ListOrder"; - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + var list = new List<string>(); + using (var statement = PrepareStatement(connection, commandText)) { - var list = new List<string>(); - using (var statement = PrepareStatementSafe(connection, commandText)) - { - // Run this again to bind the params - GetPeopleWhereClauses(query, statement); + // Run this again to bind the params + GetPeopleWhereClauses(query, statement); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(row.GetString(0)); - } + foreach (var row in statement.ExecuteQuery()) + { + list.Add(row.GetString(0)); } - return list; } + return list; } } @@ -5065,25 +5016,22 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type commandText += " order by ListOrder"; - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + var list = new List<PersonInfo>(); + + using (var statement = PrepareStatement(connection, commandText)) { - var list = new List<PersonInfo>(); + // Run this again to bind the params + GetPeopleWhereClauses(query, statement); - using (var statement = PrepareStatementSafe(connection, commandText)) + foreach (var row in statement.ExecuteQuery()) { - // Run this again to bind the params - GetPeopleWhereClauses(query, statement); - - foreach (var row in statement.ExecuteQuery()) - { - list.Add(GetPerson(row)); - } + list.Add(GetPerson(row)); } - - return list; } + + return list; } } @@ -5197,7 +5145,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type insertText.AppendFormat("(@ItemId, @AncestorId{0}, @AncestorIdText{0})", i.ToString(CultureInfo.InvariantCulture)); } - using (var statement = PrepareStatementSafe(db, insertText.ToString())) + using (var statement = PrepareStatement(db, insertText.ToString())) { statement.TryBind("@ItemId", itemIdBlob); @@ -5208,7 +5156,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type var ancestorId = ancestorIds[i]; statement.TryBind("@AncestorId" + index, ancestorId.ToGuidBlob()); - statement.TryBind("@AncestorIdText" + index, ancestorId.ToString("N")); + statement.TryBind("@AncestorIdText" + index, ancestorId.ToString("N", CultureInfo.InvariantCulture)); } statement.Reset(); @@ -5294,27 +5242,24 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type commandText += " Group By CleanValue"; - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) - { - var list = new List<string>(); + var list = new List<string>(); - using (var statement = PrepareStatementSafe(connection, commandText)) + using (var statement = PrepareStatement(connection, commandText)) + { + foreach (var row in statement.ExecuteQuery()) { - foreach (var row in statement.ExecuteQuery()) + if (!row.IsDBNull(0)) { - if (!row.IsDBNull(0)) - { - list.Add(row.GetString(0)); - } + list.Add(row.GetString(0)); } } + } - LogQueryTime("GetItemValueNames", commandText, now); + LogQueryTime("GetItemValueNames", commandText, now); - return list; - } + return list; } } @@ -5483,100 +5428,97 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type statementTexts.Add(countText); } - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + return connection.RunInTransaction(db => { - return connection.RunInTransaction(db => - { - var list = new List<(BaseItem, ItemCounts)>(); - var result = new QueryResult<(BaseItem, ItemCounts)>(); + var list = new List<(BaseItem, ItemCounts)>(); + var result = new QueryResult<(BaseItem, ItemCounts)>(); - var statements = PrepareAllSafe(db, statementTexts); + var statements = PrepareAll(db, statementTexts).ToList(); - if (!isReturningZeroItems) + if (!isReturningZeroItems) + { + using (var statement = statements[0]) { - using (var statement = statements[0]) + statement.TryBind("@SelectType", returnType); + if (EnableJoinUserData(query)) { - statement.TryBind("@SelectType", returnType); - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - if (typeSubQuery != null) - { - GetWhereClauses(typeSubQuery, null); - } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); - GetWhereClauses(innerQuery, statement); - GetWhereClauses(outerQuery, statement); - - var hasEpisodeAttributes = HasEpisodeAttributes(query); - var hasProgramAttributes = HasProgramAttributes(query); - var hasServiceName = HasServiceName(query); - var hasStartDate = HasStartDate(query); - var hasTrailerTypes = HasTrailerTypes(query); - var hasArtistFields = HasArtistFields(query); - var hasSeriesFields = HasSeriesFields(query); - - foreach (var row in statement.ExecuteQuery()) + if (typeSubQuery != null) + { + GetWhereClauses(typeSubQuery, null); + } + BindSimilarParams(query, statement); + BindSearchParams(query, statement); + GetWhereClauses(innerQuery, statement); + GetWhereClauses(outerQuery, statement); + + var hasEpisodeAttributes = HasEpisodeAttributes(query); + var hasProgramAttributes = HasProgramAttributes(query); + var hasServiceName = HasServiceName(query); + var hasStartDate = HasStartDate(query); + var hasTrailerTypes = HasTrailerTypes(query); + var hasArtistFields = HasArtistFields(query); + var hasSeriesFields = HasSeriesFields(query); + + foreach (var row in statement.ExecuteQuery()) + { + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + if (item != null) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); - if (item != null) - { - var countStartColumn = columns.Count - 1; + var countStartColumn = columns.Count - 1; - list.Add((item, GetItemCounts(row, countStartColumn, typesToCount))); - } + list.Add((item, GetItemCounts(row, countStartColumn, typesToCount))); } - - LogQueryTime("GetItemValues", commandText, now); } + + LogQueryTime("GetItemValues", commandText, now); } + } - if (query.EnableTotalRecordCount) - { - commandText = "select " - + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct PresentationUniqueKey)" })) - + GetFromText() - + GetJoinUserDataText(query) - + whereText; + 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 = statements[statements.Count - 1]) + { + statement.TryBind("@SelectType", returnType); + if (EnableJoinUserData(query)) { - statement.TryBind("@SelectType", returnType); - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - if (typeSubQuery != null) - { - GetWhereClauses(typeSubQuery, null); - } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); - GetWhereClauses(innerQuery, statement); - GetWhereClauses(outerQuery, statement); + if (typeSubQuery != null) + { + GetWhereClauses(typeSubQuery, null); + } + BindSimilarParams(query, statement); + BindSearchParams(query, statement); + GetWhereClauses(innerQuery, statement); + GetWhereClauses(outerQuery, statement); - result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); + result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); - LogQueryTime("GetItemValues", commandText, now); - } + LogQueryTime("GetItemValues", commandText, now); } + } - if (result.TotalRecordCount == 0) - { - result.TotalRecordCount = list.Count; - } - result.Items = list.ToArray(); + if (result.TotalRecordCount == 0) + { + result.TotalRecordCount = list.Count; + } + result.Items = list.ToArray(); - return result; + return result; - }, ReadTransactionMode); - } + }, ReadTransactionMode); } } @@ -5632,6 +5574,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type { counts.TrailerCount = value; } + counts.ItemCount += value; } @@ -5708,7 +5651,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type isSubsequentRow = true; } - using (var statement = PrepareStatementSafe(db, insertText.ToString())) + using (var statement = PrepareStatement(db, insertText.ToString())) { statement.TryBind("@ItemId", idBlob); @@ -5753,22 +5696,18 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type CheckDisposed(); - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - var itemIdBlob = itemId.ToGuidBlob(); + var itemIdBlob = itemId.ToGuidBlob(); // First delete chapters db.Execute("delete from People where ItemId=@ItemId", itemIdBlob); - InsertPeople(itemIdBlob, people, db); + InsertPeople(itemIdBlob, people, db); - }, TransactionMode); - - } + }, TransactionMode); } } @@ -5796,7 +5735,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type isSubsequentRow = true; } - using (var statement = PrepareStatementSafe(db, insertText.ToString())) + using (var statement = PrepareStatement(db, insertText.ToString())) { statement.TryBind("@ItemId", idBlob); @@ -5874,34 +5813,31 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type cmdText += " order by StreamIndex ASC"; - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + var list = new List<MediaStream>(); + + using (var statement = PrepareStatement(connection, cmdText)) { - var list = new List<MediaStream>(); + statement.TryBind("@ItemId", query.ItemId.ToGuidBlob()); - using (var statement = PrepareStatementSafe(connection, cmdText)) + if (query.Type.HasValue) { - statement.TryBind("@ItemId", query.ItemId.ToGuidBlob()); - - if (query.Type.HasValue) - { - statement.TryBind("@StreamType", query.Type.Value.ToString()); - } - - if (query.Index.HasValue) - { - statement.TryBind("@StreamIndex", query.Index.Value); - } + statement.TryBind("@StreamType", query.Type.Value.ToString()); + } - foreach (var row in statement.ExecuteQuery()) - { - list.Add(GetMediaStream(row)); - } + if (query.Index.HasValue) + { + statement.TryBind("@StreamIndex", query.Index.Value); } - return list; + foreach (var row in statement.ExecuteQuery()) + { + list.Add(GetMediaStream(row)); + } } + + return list; } } @@ -5921,21 +5857,18 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - var itemIdBlob = id.ToGuidBlob(); + var itemIdBlob = id.ToGuidBlob(); // First delete chapters db.Execute("delete from mediastreams where ItemId=@ItemId", itemIdBlob); - InsertMediaStreams(itemIdBlob, streams, db); + InsertMediaStreams(itemIdBlob, streams, db); - }, TransactionMode); - } + }, TransactionMode); } } @@ -5969,7 +5902,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type insertText.Append(")"); } - using (var statement = PrepareStatementSafe(db, insertText.ToString())) + using (var statement = PrepareStatement(db, insertText.ToString())) { statement.TryBind("@ItemId", idBlob); diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index 4109b7ad1..9d4855bcf 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -7,7 +7,6 @@ using MediaBrowser.Common.Configuration; using MediaBrowser.Controller.Entities; using MediaBrowser.Controller.Library; using MediaBrowser.Controller.Persistence; -using MediaBrowser.Model.IO; using Microsoft.Extensions.Logging; using SQLitePCL.pretty; @@ -33,19 +32,19 @@ namespace Emby.Server.Implementations.Data /// Opens the connection to the database /// </summary> /// <returns>Task.</returns> - public void Initialize(ReaderWriterLockSlim writeLock, ManagedConnection managedConnection, IUserManager userManager) + public void Initialize(IUserManager userManager, SemaphoreSlim dbLock, SQLiteDatabaseConnection dbConnection) { - _connection = managedConnection; - WriteLock.Dispose(); - WriteLock = writeLock; + WriteLock = dbLock; + WriteConnection?.Dispose(); + WriteConnection = dbConnection; - using (var connection = CreateConnection()) + using (var connection = GetConnection()) { var userDatasTableExists = TableExists(connection, "UserDatas"); var userDataTableExists = TableExists(connection, "userdata"); - var users = userDatasTableExists ? null : userManager.Users.ToArray(); + var users = userDatasTableExists ? null : userManager.Users; connection.RunInTransaction(db => { @@ -85,7 +84,7 @@ namespace Emby.Server.Implementations.Data } } - private void ImportUserIds(IDatabaseConnection db, User[] users) + private void ImportUserIds(IDatabaseConnection db, IEnumerable<User> users) { var userIdsWithUserData = GetAllUserIdsWithUserData(db); @@ -129,8 +128,6 @@ namespace Emby.Server.Implementations.Data return list; } - protected override bool EnableTempStoreMemory => true; - /// <summary> /// Saves the user data. /// </summary> @@ -178,15 +175,12 @@ namespace Emby.Server.Implementations.Data { cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - SaveUserData(db, internalUserId, key, userData); - }, TransactionMode); - } + SaveUserData(db, internalUserId, key, userData); + }, TransactionMode); } } @@ -249,18 +243,15 @@ namespace Emby.Server.Implementations.Data { cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + foreach (var userItemData in userDataList) { - foreach (var userItemData in userDataList) - { - SaveUserData(db, internalUserId, userItemData.Key, userItemData); - } - }, TransactionMode); - } + SaveUserData(db, internalUserId, userItemData.Key, userItemData); + } + }, TransactionMode); } } @@ -281,28 +272,26 @@ namespace Emby.Server.Implementations.Data { throw new ArgumentNullException(nameof(internalUserId)); } + if (string.IsNullOrEmpty(key)) { throw new ArgumentNullException(nameof(key)); } - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) + using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from UserDatas where key =@Key and userId=@UserId")) { - using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from UserDatas where key =@Key and userId=@UserId")) - { - statement.TryBind("@UserId", internalUserId); - statement.TryBind("@Key", key); + statement.TryBind("@UserId", internalUserId); + statement.TryBind("@Key", key); - foreach (var row in statement.ExecuteQuery()) - { - return ReadRow(row); - } + foreach (var row in statement.ExecuteQuery()) + { + return ReadRow(row); } - - return null; } + + return null; } } @@ -335,18 +324,15 @@ namespace Emby.Server.Implementations.Data var list = new List<UserItemData>(); - using (WriteLock.Read()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from UserDatas where userId=@UserId")) { - using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from UserDatas where userId=@UserId")) - { - statement.TryBind("@UserId", internalUserId); + statement.TryBind("@UserId", internalUserId); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(ReadRow(row)); - } + foreach (var row in statement.ExecuteQuery()) + { + list.Add(ReadRow(row)); } } } @@ -392,15 +378,5 @@ namespace Emby.Server.Implementations.Data return userData; } - - protected override void Dispose(bool dispose) - { - // handled by library database - } - - protected override void CloseConnection() - { - // handled by library database - } } } diff --git a/Emby.Server.Implementations/Data/SqliteUserRepository.cs b/Emby.Server.Implementations/Data/SqliteUserRepository.cs index 5957b2903..11629b389 100644 --- a/Emby.Server.Implementations/Data/SqliteUserRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserRepository.cs @@ -18,10 +18,10 @@ namespace Emby.Server.Implementations.Data private readonly IJsonSerializer _jsonSerializer; public SqliteUserRepository( - ILoggerFactory loggerFactory, + ILogger<SqliteUserRepository> logger, IServerApplicationPaths appPaths, IJsonSerializer jsonSerializer) - : base(loggerFactory.CreateLogger(nameof(SqliteUserRepository))) + : base(logger) { _jsonSerializer = jsonSerializer; @@ -35,15 +35,12 @@ namespace Emby.Server.Implementations.Data public string Name => "SQLite"; /// <summary> - /// Opens the connection to the database + /// Opens the connection to the database. /// </summary> - /// <returns>Task.</returns> public void Initialize() { - using (var connection = CreateConnection()) + using (var connection = GetConnection()) { - RunDefaultInitialization(connection); - var localUsersTableExists = TableExists(connection, "LocalUsersv2"); connection.RunQueries(new[] { @@ -56,7 +53,7 @@ namespace Emby.Server.Implementations.Data TryMigrateToLocalUsersTable(connection); } - RemoveEmptyPasswordHashes(); + RemoveEmptyPasswordHashes(connection); } } @@ -75,9 +72,9 @@ namespace Emby.Server.Implementations.Data } } - private void RemoveEmptyPasswordHashes() + private void RemoveEmptyPasswordHashes(ManagedConnection connection) { - foreach (var user in RetrieveAllUsers()) + 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) @@ -89,22 +86,16 @@ namespace Emby.Server.Implementations.Data user.Password = null; var serialized = _jsonSerializer.SerializeToBytes(user); - using (WriteLock.Write()) - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var statement = db.PrepareStatement("update LocalUsersv2 set data=@data where Id=@InternalId")) { - 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); - } + statement.TryBind("@InternalId", user.InternalId); + statement.TryBind("@data", serialized); + statement.MoveNext(); + } + }, TransactionMode); } - } /// <summary> @@ -119,31 +110,28 @@ namespace Emby.Server.Implementations.Data var serialized = _jsonSerializer.SerializeToBytes(user); - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var statement = db.PrepareStatement("insert into LocalUsersv2 (guid, data) values (@guid, @data)")) { - using (var statement = db.PrepareStatement("insert into LocalUsersv2 (guid, data) values (@guid, @data)")) - { - statement.TryBind("@guid", user.Id.ToGuidBlob()); - statement.TryBind("@data", serialized); + statement.TryBind("@guid", user.Id.ToGuidBlob()); + statement.TryBind("@data", serialized); - statement.MoveNext(); - } + statement.MoveNext(); + } - var createdUser = GetUser(user.Id, false); + var createdUser = GetUser(user.Id, connection); - if (createdUser == null) - { - throw new ApplicationException("created user should never be null"); - } + if (createdUser == null) + { + throw new ApplicationException("created user should never be null"); + } - user.InternalId = createdUser.InternalId; + user.InternalId = createdUser.InternalId; - }, TransactionMode); - } + }, TransactionMode); } } @@ -156,39 +144,30 @@ namespace Emby.Server.Implementations.Data var serialized = _jsonSerializer.SerializeToBytes(user); - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var statement = db.PrepareStatement("update LocalUsersv2 set data=@data where Id=@InternalId")) { - 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); - } + statement.TryBind("@InternalId", user.InternalId); + statement.TryBind("@data", serialized); + statement.MoveNext(); + } + + }, TransactionMode); } } - private User GetUser(Guid guid, bool openLock) + private User GetUser(Guid guid, ManagedConnection connection) { - using (openLock ? WriteLock.Read() : null) + using (var statement = connection.PrepareStatement("select id,guid,data from LocalUsersv2 where guid=@guid")) { - using (var connection = CreateConnection(true)) - { - using (var statement = connection.PrepareStatement("select id,guid,data from LocalUsersv2 where guid=@guid")) - { - statement.TryBind("@guid", guid); + statement.TryBind("@guid", guid); - foreach (var row in statement.ExecuteQuery()) - { - return GetUser(row); - } - } + foreach (var row in statement.ExecuteQuery()) + { + return GetUser(row); } } @@ -200,14 +179,10 @@ namespace Emby.Server.Implementations.Data var id = row[0].ToInt64(); var guid = row[1].ReadGuidFromBlob(); - using (var stream = new MemoryStream(row[2].ToBlob())) - { - stream.Position = 0; - var user = _jsonSerializer.DeserializeFromStream<User>(stream); - user.InternalId = id; - user.Id = guid; - return user; - } + var user = _jsonSerializer.DeserializeFromString<User>(row.GetString(2)); + user.InternalId = id; + user.Id = guid; + return user; } /// <summary> @@ -216,20 +191,22 @@ namespace Emby.Server.Implementations.Data /// <returns>IEnumerable{User}.</returns> public List<User> RetrieveAllUsers() { - var list = new List<User>(); - - using (WriteLock.Read()) + using (var connection = GetConnection(true)) { - using (var connection = CreateConnection(true)) - { - foreach (var row in connection.Query("select id,guid,data from LocalUsersv2")) - { - list.Add(GetUser(row)); - } - } + return new List<User>(RetrieveAllUsers(connection)); } + } - return list; + /// <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> @@ -245,19 +222,16 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException(nameof(user)); } - using (WriteLock.Write()) + using (var connection = GetConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var statement = db.PrepareStatement("delete from LocalUsersv2 where Id=@id")) { - using (var statement = db.PrepareStatement("delete from LocalUsersv2 where Id=@id")) - { - statement.TryBind("@id", user.InternalId); - statement.MoveNext(); - } - }, TransactionMode); - } + statement.TryBind("@id", user.InternalId); + statement.MoveNext(); + } + }, TransactionMode); } } } |