diff options
Diffstat (limited to 'Emby.Server.Implementations/Data/SqliteUserDataRepository.cs')
| -rw-r--r-- | Emby.Server.Implementations/Data/SqliteUserDataRepository.cs | 715 |
1 files changed, 359 insertions, 356 deletions
diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index c6b08c4b3..a66536179 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -1,356 +1,359 @@ -//using System; -//using System.Collections.Generic; -//using System.IO; -//using System.Linq; -//using System.Threading; -//using System.Threading.Tasks; -//using MediaBrowser.Common.Configuration; -//using MediaBrowser.Controller.Entities; -//using MediaBrowser.Controller.Persistence; -//using MediaBrowser.Model.Logging; -//using SQLitePCL.pretty; - -//namespace Emby.Server.Implementations.Data -//{ -// public class SqliteUserDataRepository : BaseSqliteRepository, IUserDataRepository -// { -// private SQLiteDatabaseConnection _connection; - -// public SqliteUserDataRepository(ILogger logger, IApplicationPaths appPaths) -// : base(logger) -// { -// DbFilePath = Path.Combine(appPaths.DataPath, "userdata_v2.db"); -// } - -// protected override bool EnableConnectionPooling -// { -// get { return false; } -// } - -// /// <summary> -// /// Gets the name of the repository -// /// </summary> -// /// <value>The name.</value> -// public string Name -// { -// get -// { -// return "SQLite"; -// } -// } - -// /// <summary> -// /// Opens the connection to the database -// /// </summary> -// /// <returns>Task.</returns> -// public void Initialize(SQLiteDatabaseConnection connection, ReaderWriterLockSlim writeLock) -// { -// WriteLock.Dispose(); -// WriteLock = writeLock; -// _connection = connection; - -// string[] queries = { - -// "create table if not exists UserDataDb.userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null)", - -// "drop index if exists UserDataDb.idx_userdata", -// "drop index if exists UserDataDb.idx_userdata1", -// "drop index if exists UserDataDb.idx_userdata2", -// "drop index if exists UserDataDb.userdataindex1", - -// "create unique index if not exists UserDataDb.userdataindex on userdata (key, userId)", -// "create index if not exists UserDataDb.userdataindex2 on userdata (key, userId, played)", -// "create index if not exists UserDataDb.userdataindex3 on userdata (key, userId, playbackPositionTicks)", -// "create index if not exists UserDataDb.userdataindex4 on userdata (key, userId, isFavorite)", - -// //pragmas -// "pragma temp_store = memory", - -// "pragma shrink_memory" -// }; - -// _connection.RunQueries(queries); - -// connection.RunInTransaction(db => -// { -// var existingColumnNames = GetColumnNames(db, "userdata"); - -// AddColumn(db, "userdata", "AudioStreamIndex", "int", existingColumnNames); -// AddColumn(db, "userdata", "SubtitleStreamIndex", "int", existingColumnNames); -// }); -// } - -// /// <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 Task SaveUserData(Guid userId, string key, UserItemData userData, CancellationToken cancellationToken) -// { -// if (userData == null) -// { -// throw new ArgumentNullException("userData"); -// } -// if (userId == Guid.Empty) -// { -// throw new ArgumentNullException("userId"); -// } -// if (string.IsNullOrEmpty(key)) -// { -// throw new ArgumentNullException("key"); -// } - -// return PersistUserData(userId, key, userData, cancellationToken); -// } - -// public Task SaveAllUserData(Guid userId, IEnumerable<UserItemData> userData, CancellationToken cancellationToken) -// { -// if (userData == null) -// { -// throw new ArgumentNullException("userData"); -// } -// if (userId == Guid.Empty) -// { -// throw new ArgumentNullException("userId"); -// } - -// return PersistAllUserData(userId, userData.ToList(), cancellationToken); -// } - -// /// <summary> -// /// Persists 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> -// public async Task PersistUserData(Guid userId, string key, UserItemData userData, CancellationToken cancellationToken) -// { -// cancellationToken.ThrowIfCancellationRequested(); - -// using (WriteLock.Write()) -// { -// _connection.RunInTransaction(db => -// { -// SaveUserData(db, userId, key, userData); -// }); -// } -// } - -// private void SaveUserData(IDatabaseConnection db, Guid userId, string key, UserItemData userData) -// { -// using (var statement = _connection.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)")) -// { -// statement.TryBind("@UserId", userId.ToGuidParamValue()); -// statement.TryBind("@Key", key); - -// if (userData.Rating.HasValue) -// { -// statement.TryBind("@rating", userData.Rating.Value); -// } -// else -// { -// statement.TryBindNull("@rating"); -// } - -// statement.TryBind("@played", userData.Played); -// statement.TryBind("@playCount", userData.PlayCount); -// statement.TryBind("@isFavorite", userData.IsFavorite); -// statement.TryBind("@playbackPositionTicks", userData.PlaybackPositionTicks); - -// if (userData.LastPlayedDate.HasValue) -// { -// statement.TryBind("@lastPlayedDate", userData.LastPlayedDate.Value.ToDateTimeParamValue()); -// } -// else -// { -// statement.TryBindNull("@lastPlayedDate"); -// } - -// if (userData.AudioStreamIndex.HasValue) -// { -// statement.TryBind("@AudioStreamIndex", userData.AudioStreamIndex.Value); -// } -// else -// { -// statement.TryBindNull("@AudioStreamIndex"); -// } - -// if (userData.SubtitleStreamIndex.HasValue) -// { -// statement.TryBind("@SubtitleStreamIndex", userData.SubtitleStreamIndex.Value); -// } -// else -// { -// statement.TryBindNull("@SubtitleStreamIndex"); -// } - -// statement.MoveNext(); -// } -// } - -// /// <summary> -// /// Persist all user data for the specified user -// /// </summary> -// private async Task PersistAllUserData(Guid userId, List<UserItemData> userDataList, CancellationToken cancellationToken) -// { -// cancellationToken.ThrowIfCancellationRequested(); - -// using (WriteLock.Write()) -// { -// _connection.RunInTransaction(db => -// { -// foreach (var userItemData in userDataList) -// { -// SaveUserData(db, userId, userItemData.Key, userItemData); -// } -// }); -// } -// } - -// /// <summary> -// /// Gets the user data. -// /// </summary> -// /// <param name="userId">The user id.</param> -// /// <param name="key">The key.</param> -// /// <returns>Task{UserItemData}.</returns> -// /// <exception cref="System.ArgumentNullException"> -// /// userId -// /// or -// /// key -// /// </exception> -// public UserItemData GetUserData(Guid userId, string key) -// { -// if (userId == Guid.Empty) -// { -// throw new ArgumentNullException("userId"); -// } -// if (string.IsNullOrEmpty(key)) -// { -// throw new ArgumentNullException("key"); -// } - -// using (var statement = _connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where key =@Key and userId=@UserId")) -// { -// statement.TryBind("@UserId", userId.ToGuidParamValue()); -// statement.TryBind("@Key", key); - -// foreach (var row in statement.ExecuteQuery()) -// { -// return ReadRow(row); -// } -// } - -// return null; -// } - -// public UserItemData GetUserData(Guid userId, List<string> keys) -// { -// if (userId == Guid.Empty) -// { -// throw new ArgumentNullException("userId"); -// } -// if (keys == null) -// { -// throw new ArgumentNullException("keys"); -// } - -// if (keys.Count == 0) -// { -// return null; -// } - -// return GetUserData(userId, keys[0]); -// } - -// /// <summary> -// /// Return all user-data associated with the given user -// /// </summary> -// /// <param name="userId"></param> -// /// <returns></returns> -// public IEnumerable<UserItemData> GetAllUserData(Guid userId) -// { -// if (userId == Guid.Empty) -// { -// throw new ArgumentNullException("userId"); -// } - -// var list = new List<UserItemData>(); - -// using (WriteLock.Read()) -// { -// using (var statement = _connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@UserId")) -// { -// statement.TryBind("@UserId", userId.ToGuidParamValue()); - -// foreach (var row in statement.ExecuteQuery()) -// { -// list.Add(ReadRow(row)); -// } -// } -// } - -// return list; -// } - -// /// <summary> -// /// Read a row from the specified reader into the provided userData object -// /// </summary> -// /// <param name="reader"></param> -// private UserItemData ReadRow(IReadOnlyList<IResultSetValue> reader) -// { -// var userData = new UserItemData(); - -// userData.Key = reader[0].ToString(); -// userData.UserId = reader[1].ReadGuid(); - -// if (reader[2].SQLiteType != SQLiteType.Null) -// { -// userData.Rating = reader[2].ToDouble(); -// } - -// userData.Played = reader[3].ToBool(); -// userData.PlayCount = reader[4].ToInt(); -// userData.IsFavorite = reader[5].ToBool(); -// userData.PlaybackPositionTicks = reader[6].ToInt64(); - -// if (reader[7].SQLiteType != SQLiteType.Null) -// { -// userData.LastPlayedDate = reader[7].ReadDateTime(); -// } - -// if (reader[8].SQLiteType != SQLiteType.Null) -// { -// userData.AudioStreamIndex = reader[8].ToInt(); -// } - -// if (reader[9].SQLiteType != SQLiteType.Null) -// { -// userData.SubtitleStreamIndex = reader[9].ToInt(); -// } - -// return userData; -// } - -// protected override void Dispose(bool dispose) -// { -// // handled by library database -// } - -// protected override void CloseConnection() -// { -// // handled by library database -// } -// } -//}
\ No newline at end of file +using System; +using System.Collections.Generic; +using System.IO; +using System.Linq; +using System.Threading; +using System.Threading.Tasks; +using MediaBrowser.Common.Configuration; +using MediaBrowser.Controller.Entities; +using MediaBrowser.Controller.Persistence; +using MediaBrowser.Model.Logging; +using SQLitePCL.pretty; + +namespace Emby.Server.Implementations.Data +{ + public class SqliteUserDataRepository : BaseSqliteRepository, IUserDataRepository + { + private SQLiteDatabaseConnection _connection; + + public SqliteUserDataRepository(ILogger logger, IApplicationPaths appPaths) + : base(logger) + { + DbFilePath = Path.Combine(appPaths.DataPath, "userdata_v2.db"); + } + + protected override bool EnableConnectionPooling + { + get { return false; } + } + + /// <summary> + /// Gets the name of the repository + /// </summary> + /// <value>The name.</value> + public string Name + { + get + { + return "SQLite"; + } + } + + /// <summary> + /// Opens the connection to the database + /// </summary> + /// <returns>Task.</returns> + public void Initialize(SQLiteDatabaseConnection connection, ReaderWriterLockSlim writeLock) + { + WriteLock.Dispose(); + WriteLock = writeLock; + _connection = connection; + + string[] queries = { + + "create table if not exists UserDataDb.userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null)", + + "drop index if exists UserDataDb.idx_userdata", + "drop index if exists UserDataDb.idx_userdata1", + "drop index if exists UserDataDb.idx_userdata2", + "drop index if exists UserDataDb.userdataindex1", + + "create unique index if not exists UserDataDb.userdataindex on userdata (key, userId)", + "create index if not exists UserDataDb.userdataindex2 on userdata (key, userId, played)", + "create index if not exists UserDataDb.userdataindex3 on userdata (key, userId, playbackPositionTicks)", + "create index if not exists UserDataDb.userdataindex4 on userdata (key, userId, isFavorite)", + + //pragmas + "pragma temp_store = memory", + + "pragma shrink_memory" + }; + + _connection.RunQueries(queries); + + connection.RunInTransaction(db => + { + var existingColumnNames = GetColumnNames(db, "userdata"); + + AddColumn(db, "userdata", "AudioStreamIndex", "int", existingColumnNames); + AddColumn(db, "userdata", "SubtitleStreamIndex", "int", existingColumnNames); + }); + } + + /// <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 Task SaveUserData(Guid userId, string key, UserItemData userData, CancellationToken cancellationToken) + { + if (userData == null) + { + throw new ArgumentNullException("userData"); + } + if (userId == Guid.Empty) + { + throw new ArgumentNullException("userId"); + } + if (string.IsNullOrEmpty(key)) + { + throw new ArgumentNullException("key"); + } + + return PersistUserData(userId, key, userData, cancellationToken); + } + + public Task SaveAllUserData(Guid userId, IEnumerable<UserItemData> userData, CancellationToken cancellationToken) + { + if (userData == null) + { + throw new ArgumentNullException("userData"); + } + if (userId == Guid.Empty) + { + throw new ArgumentNullException("userId"); + } + + return PersistAllUserData(userId, userData.ToList(), cancellationToken); + } + + /// <summary> + /// Persists 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> + public async Task PersistUserData(Guid userId, string key, UserItemData userData, CancellationToken cancellationToken) + { + cancellationToken.ThrowIfCancellationRequested(); + + using (WriteLock.Write()) + { + _connection.RunInTransaction(db => + { + SaveUserData(db, userId, key, userData); + }); + } + } + + private void SaveUserData(IDatabaseConnection db, Guid userId, string key, UserItemData userData) + { + using (var statement = _connection.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)")) + { + statement.TryBind("@UserId", userId.ToGuidParamValue()); + statement.TryBind("@Key", key); + + if (userData.Rating.HasValue) + { + statement.TryBind("@rating", userData.Rating.Value); + } + else + { + statement.TryBindNull("@rating"); + } + + statement.TryBind("@played", userData.Played); + statement.TryBind("@playCount", userData.PlayCount); + statement.TryBind("@isFavorite", userData.IsFavorite); + statement.TryBind("@playbackPositionTicks", userData.PlaybackPositionTicks); + + if (userData.LastPlayedDate.HasValue) + { + statement.TryBind("@lastPlayedDate", userData.LastPlayedDate.Value.ToDateTimeParamValue()); + } + else + { + statement.TryBindNull("@lastPlayedDate"); + } + + if (userData.AudioStreamIndex.HasValue) + { + statement.TryBind("@AudioStreamIndex", userData.AudioStreamIndex.Value); + } + else + { + statement.TryBindNull("@AudioStreamIndex"); + } + + if (userData.SubtitleStreamIndex.HasValue) + { + statement.TryBind("@SubtitleStreamIndex", userData.SubtitleStreamIndex.Value); + } + else + { + statement.TryBindNull("@SubtitleStreamIndex"); + } + + statement.MoveNext(); + } + } + + /// <summary> + /// Persist all user data for the specified user + /// </summary> + private async Task PersistAllUserData(Guid userId, List<UserItemData> userDataList, CancellationToken cancellationToken) + { + cancellationToken.ThrowIfCancellationRequested(); + + using (WriteLock.Write()) + { + _connection.RunInTransaction(db => + { + foreach (var userItemData in userDataList) + { + SaveUserData(db, userId, userItemData.Key, userItemData); + } + }); + } + } + + /// <summary> + /// Gets the user data. + /// </summary> + /// <param name="userId">The user id.</param> + /// <param name="key">The key.</param> + /// <returns>Task{UserItemData}.</returns> + /// <exception cref="System.ArgumentNullException"> + /// userId + /// or + /// key + /// </exception> + public UserItemData GetUserData(Guid userId, string key) + { + if (userId == Guid.Empty) + { + throw new ArgumentNullException("userId"); + } + if (string.IsNullOrEmpty(key)) + { + throw new ArgumentNullException("key"); + } + + using (WriteLock.Write()) + { + using (var statement = _connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where key =@Key and userId=@UserId")) + { + statement.TryBind("@UserId", userId.ToGuidParamValue()); + statement.TryBind("@Key", key); + + foreach (var row in statement.ExecuteQuery()) + { + return ReadRow(row); + } + } + } + + return null; + } + + public UserItemData GetUserData(Guid userId, List<string> keys) + { + if (userId == Guid.Empty) + { + throw new ArgumentNullException("userId"); + } + if (keys == null) + { + throw new ArgumentNullException("keys"); + } + + if (keys.Count == 0) + { + return null; + } + + return GetUserData(userId, keys[0]); + } + + /// <summary> + /// Return all user-data associated with the given user + /// </summary> + /// <param name="userId"></param> + /// <returns></returns> + public IEnumerable<UserItemData> GetAllUserData(Guid userId) + { + if (userId == Guid.Empty) + { + throw new ArgumentNullException("userId"); + } + + var list = new List<UserItemData>(); + + using (WriteLock.Write()) + { + using (var statement = _connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@UserId")) + { + statement.TryBind("@UserId", userId.ToGuidParamValue()); + + foreach (var row in statement.ExecuteQuery()) + { + list.Add(ReadRow(row)); + } + } + } + + return list; + } + + /// <summary> + /// Read a row from the specified reader into the provided userData object + /// </summary> + /// <param name="reader"></param> + private UserItemData ReadRow(IReadOnlyList<IResultSetValue> reader) + { + var userData = new UserItemData(); + + userData.Key = reader[0].ToString(); + userData.UserId = reader[1].ReadGuid(); + + if (reader[2].SQLiteType != SQLiteType.Null) + { + userData.Rating = reader[2].ToDouble(); + } + + userData.Played = reader[3].ToBool(); + userData.PlayCount = reader[4].ToInt(); + userData.IsFavorite = reader[5].ToBool(); + userData.PlaybackPositionTicks = reader[6].ToInt64(); + + if (reader[7].SQLiteType != SQLiteType.Null) + { + userData.LastPlayedDate = reader[7].ReadDateTime(); + } + + if (reader[8].SQLiteType != SQLiteType.Null) + { + userData.AudioStreamIndex = reader[8].ToInt(); + } + + if (reader[9].SQLiteType != SQLiteType.Null) + { + userData.SubtitleStreamIndex = reader[9].ToInt(); + } + + return userData; + } + + protected override void Dispose(bool dispose) + { + // handled by library database + } + + protected override void CloseConnection() + { + // handled by library database + } + } +}
\ No newline at end of file |