diff options
Diffstat (limited to 'Emby.Server.Implementations/Data/SqliteUserDataRepository.cs')
| -rw-r--r-- | Emby.Server.Implementations/Data/SqliteUserDataRepository.cs | 209 |
1 files changed, 100 insertions, 109 deletions
diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index ad5c60ede..07d64a2b0 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -9,12 +9,12 @@ using MediaBrowser.Controller.Persistence; using MediaBrowser.Model.IO; using MediaBrowser.Model.Logging; using SQLitePCL.pretty; +using MediaBrowser.Controller.Library; namespace Emby.Server.Implementations.Data { public class SqliteUserDataRepository : BaseSqliteRepository, IUserDataRepository { - private readonly string _importFile; private readonly IFileSystem _fileSystem; public SqliteUserDataRepository(ILogger logger, IApplicationPaths appPaths, IFileSystem fileSystem) @@ -22,7 +22,6 @@ namespace Emby.Server.Implementations.Data { _fileSystem = fileSystem; DbFilePath = Path.Combine(appPaths.DataPath, "library.db"); - _importFile = Path.Combine(appPaths.DataPath, "userdata_v2.db"); } /// <summary> @@ -41,7 +40,7 @@ namespace Emby.Server.Implementations.Data /// Opens the connection to the database /// </summary> /// <returns>Task.</returns> - public void Initialize(ReaderWriterLockSlim writeLock, ManagedConnection managedConnection) + public void Initialize(ReaderWriterLockSlim writeLock, ManagedConnection managedConnection, IUserManager userManager) { _connection = managedConnection; @@ -50,138 +49,134 @@ namespace Emby.Server.Implementations.Data using (var connection = CreateConnection()) { - string[] queries = { + var userDatasTableExists = TableExists(connection, "UserDatas"); + var userDataTableExists = TableExists(connection, "userdata"); - "create table if not exists userdata (key nvarchar not null, userId GUID not null, rating float null, played bit not null, playCount int not null, isFavorite bit not null, playbackPositionTicks bigint not null, lastPlayedDate datetime null)", - - "create table if not exists DataSettings (IsUserDataImported bit)", - - "drop index if exists idx_userdata", - "drop index if exists idx_userdata1", - "drop index if exists idx_userdata2", - "drop index if exists userdataindex1", - - "create unique index if not exists userdataindex on userdata (key, userId)", - "create index if not exists userdataindex2 on userdata (key, userId, played)", - "create index if not exists userdataindex3 on userdata (key, userId, playbackPositionTicks)", - "create index if not exists userdataindex4 on userdata (key, userId, isFavorite)", - - "pragma shrink_memory" - }; - - connection.RunQueries(queries); + var users = userDatasTableExists ? null : userManager.Users.ToArray(); connection.RunInTransaction(db => { - var existingColumnNames = GetColumnNames(db, "userdata"); + db.ExecuteAll(string.Join(";", new[] { + + "create table if not exists UserDatas (key nvarchar not null, userId INT not null, rating float null, played bit not null, playCount int not null, isFavorite bit not null, playbackPositionTicks bigint not null, lastPlayedDate datetime null, AudioStreamIndex INT, SubtitleStreamIndex INT)", + + "drop index if exists idx_userdata", + "drop index if exists idx_userdata1", + "drop index if exists idx_userdata2", + "drop index if exists userdataindex1", + "drop index if exists userdataindex", + "drop index if exists userdataindex3", + "drop index if exists userdataindex4", + "create unique index if not exists UserDatasIndex1 on UserDatas (key, userId)", + "create index if not exists UserDatasIndex2 on UserDatas (key, userId, played)", + "create index if not exists UserDatasIndex3 on UserDatas (key, userId, playbackPositionTicks)", + "create index if not exists UserDatasIndex4 on UserDatas (key, userId, isFavorite)" + })); + + if (userDataTableExists) + { + var existingColumnNames = GetColumnNames(db, "userdata"); - AddColumn(db, "userdata", "AudioStreamIndex", "int", existingColumnNames); - AddColumn(db, "userdata", "SubtitleStreamIndex", "int", existingColumnNames); - }, TransactionMode); + AddColumn(db, "userdata", "InternalUserId", "int", existingColumnNames); + AddColumn(db, "userdata", "AudioStreamIndex", "int", existingColumnNames); + AddColumn(db, "userdata", "SubtitleStreamIndex", "int", existingColumnNames); - try - { - ImportUserDataIfNeeded(connection); - } - catch (Exception ex) - { - Logger.ErrorException("Error in ImportUserDataIfNeeded", ex); - } - } - } + if (!userDatasTableExists) + { + ImportUserIds(db, users); - protected override bool EnableTempStoreMemory - { - get - { - return true; + db.ExecuteAll("INSERT INTO UserDatas (key, userId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex) SELECT key, InternalUserId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex from userdata where InternalUserId not null"); + } + } + }, TransactionMode); } } - private void ImportUserDataIfNeeded(ManagedConnection connection) + private void ImportUserIds(IDatabaseConnection db, User[] users) { - if (!_fileSystem.FileExists(_importFile)) + var userIdsWithUserData = GetAllUserIdsWithUserData(db); + + using (var statement = db.PrepareStatement("update userdata set InternalUserId=@InternalUserId where UserId=@UserId")) { - return; - } + foreach (var user in users) + { + if (!userIdsWithUserData.Contains(user.Id)) + { + continue; + } - var fileToImport = _importFile; - var isImported = connection.Query("select IsUserDataImported from DataSettings").SelectScalarBool().FirstOrDefault(); + statement.TryBind("@UserId", user.Id.ToGuidBlob()); + statement.TryBind("@InternalUserId", user.InternalId); - if (isImported) - { - return; + statement.MoveNext(); + statement.Reset(); + } } + } - ImportUserData(connection, fileToImport); + private List<Guid> GetAllUserIdsWithUserData(IDatabaseConnection db) + { + List<Guid> list = new List<Guid>(); - connection.RunInTransaction(db => + using (var statement = PrepareStatement(db, "select DISTINCT UserId from UserData where UserId not null")) { - using (var statement = db.PrepareStatement("replace into DataSettings (IsUserDataImported) values (@IsUserDataImported)")) + foreach (var row in statement.ExecuteQuery()) { - statement.TryBind("@IsUserDataImported", true); - statement.MoveNext(); + try + { + list.Add(row[0].ReadGuidFromBlob()); + } + catch + { + + } } - }, TransactionMode); + } + + return list; } - private void ImportUserData(ManagedConnection connection, string file) + protected override bool EnableTempStoreMemory { - SqliteExtensions.Attach(connection, file, "UserDataBackup"); - - var columns = "key, userId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex"; - - connection.RunInTransaction(db => + get { - db.Execute("REPLACE INTO userdata(" + columns + ") SELECT " + columns + " FROM UserDataBackup.userdata;"); - }, TransactionMode); + return true; + } } /// <summary> /// Saves the user data. /// </summary> - /// <param name="userId">The user id.</param> - /// <param name="key">The key.</param> - /// <param name="userData">The user data.</param> - /// <param name="cancellationToken">The cancellation token.</param> - /// <returns>Task.</returns> - /// <exception cref="System.ArgumentNullException">userData - /// or - /// cancellationToken - /// or - /// userId - /// or - /// userDataId</exception> - public void SaveUserData(Guid userId, string key, UserItemData userData, CancellationToken cancellationToken) + public void SaveUserData(long internalUserId, string key, UserItemData userData, CancellationToken cancellationToken) { if (userData == null) { throw new ArgumentNullException("userData"); } - if (userId == Guid.Empty) + if (internalUserId <= 0) { - throw new ArgumentNullException("userId"); + throw new ArgumentNullException("internalUserId"); } if (string.IsNullOrEmpty(key)) { throw new ArgumentNullException("key"); } - PersistUserData(userId, key, userData, cancellationToken); + PersistUserData(internalUserId, key, userData, cancellationToken); } - public void SaveAllUserData(Guid userId, UserItemData[] userData, CancellationToken cancellationToken) + public void SaveAllUserData(long internalUserId, UserItemData[] userData, CancellationToken cancellationToken) { if (userData == null) { throw new ArgumentNullException("userData"); } - if (userId == Guid.Empty) + if (internalUserId <= 0) { - throw new ArgumentNullException("userId"); + throw new ArgumentNullException("internalUserId"); } - PersistAllUserData(userId, userData, cancellationToken); + PersistAllUserData(internalUserId, userData, cancellationToken); } /// <summary> @@ -192,7 +187,7 @@ namespace Emby.Server.Implementations.Data /// <param name="userData">The user data.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>Task.</returns> - public void PersistUserData(Guid userId, string key, UserItemData userData, CancellationToken cancellationToken) + public void PersistUserData(long internalUserId, string key, UserItemData userData, CancellationToken cancellationToken) { cancellationToken.ThrowIfCancellationRequested(); @@ -202,17 +197,17 @@ namespace Emby.Server.Implementations.Data { connection.RunInTransaction(db => { - SaveUserData(db, userId, key, userData); + SaveUserData(db, internalUserId, key, userData); }, TransactionMode); } } } - private void SaveUserData(IDatabaseConnection db, Guid userId, string key, UserItemData userData) + private void SaveUserData(IDatabaseConnection db, long internalUserId, string key, UserItemData userData) { - using (var statement = db.PrepareStatement("replace into userdata (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)")) + using (var statement = db.PrepareStatement("replace into UserDatas (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)")) { - statement.TryBind("@userId", userId.ToGuidBlob()); + statement.TryBind("@userId", internalUserId); statement.TryBind("@key", key); if (userData.Rating.HasValue) @@ -263,7 +258,7 @@ namespace Emby.Server.Implementations.Data /// <summary> /// Persist all user data for the specified user /// </summary> - private void PersistAllUserData(Guid userId, UserItemData[] userDataList, CancellationToken cancellationToken) + private void PersistAllUserData(long internalUserId, UserItemData[] userDataList, CancellationToken cancellationToken) { cancellationToken.ThrowIfCancellationRequested(); @@ -275,7 +270,7 @@ namespace Emby.Server.Implementations.Data { foreach (var userItemData in userDataList) { - SaveUserData(db, userId, userItemData.Key, userItemData); + SaveUserData(db, internalUserId, userItemData.Key, userItemData); } }, TransactionMode); } @@ -293,11 +288,11 @@ namespace Emby.Server.Implementations.Data /// or /// key /// </exception> - public UserItemData GetUserData(Guid userId, string key) + public UserItemData GetUserData(long internalUserId, string key) { - if (userId == Guid.Empty) + if (internalUserId <= 0) { - throw new ArgumentNullException("userId"); + throw new ArgumentNullException("internalUserId"); } if (string.IsNullOrEmpty(key)) { @@ -308,9 +303,9 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection(true)) { - using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata 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", userId.ToGuidBlob()); + statement.TryBind("@UserId", internalUserId); statement.TryBind("@Key", key); foreach (var row in statement.ExecuteQuery()) @@ -324,12 +319,8 @@ namespace Emby.Server.Implementations.Data } } - public UserItemData GetUserData(Guid userId, List<string> keys) + public UserItemData GetUserData(long internalUserId, List<string> keys) { - if (userId == Guid.Empty) - { - throw new ArgumentNullException("userId"); - } if (keys == null) { throw new ArgumentNullException("keys"); @@ -340,7 +331,7 @@ namespace Emby.Server.Implementations.Data return null; } - return GetUserData(userId, keys[0]); + return GetUserData(internalUserId, keys[0]); } /// <summary> @@ -348,11 +339,11 @@ namespace Emby.Server.Implementations.Data /// </summary> /// <param name="userId"></param> /// <returns></returns> - public List<UserItemData> GetAllUserData(Guid userId) + public List<UserItemData> GetAllUserData(long internalUserId) { - if (userId == Guid.Empty) + if (internalUserId <= 0) { - throw new ArgumentNullException("userId"); + throw new ArgumentNullException("internalUserId"); } var list = new List<UserItemData>(); @@ -361,9 +352,9 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection()) { - using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata 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", userId.ToGuidBlob()); + statement.TryBind("@UserId", internalUserId); foreach (var row in statement.ExecuteQuery()) { @@ -385,7 +376,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) { @@ -399,7 +390,7 @@ namespace Emby.Server.Implementations.Data if (reader[7].SQLiteType != SQLiteType.Null) { - userData.LastPlayedDate = reader[7].ReadDateTime(); + userData.LastPlayedDate = reader[7].TryReadDateTime(); } if (reader[8].SQLiteType != SQLiteType.Null) |