2
0

SqliteUserDataRepository.cs 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414
  1. using System;
  2. using System.Collections.Generic;
  3. using System.IO;
  4. using System.Linq;
  5. using System.Threading;
  6. using System.Threading.Tasks;
  7. using MediaBrowser.Common.Configuration;
  8. using MediaBrowser.Controller.Entities;
  9. using MediaBrowser.Controller.Persistence;
  10. using MediaBrowser.Model.IO;
  11. using MediaBrowser.Model.Logging;
  12. using SQLitePCL.pretty;
  13. namespace Emby.Server.Implementations.Data
  14. {
  15. public class SqliteUserDataRepository : BaseSqliteRepository, IUserDataRepository
  16. {
  17. private readonly string _importFile;
  18. private readonly IFileSystem _fileSystem;
  19. public SqliteUserDataRepository(ILogger logger, IApplicationPaths appPaths, IFileSystem fileSystem)
  20. : base(logger)
  21. {
  22. _fileSystem = fileSystem;
  23. DbFilePath = Path.Combine(appPaths.DataPath, "library.db");
  24. _importFile = Path.Combine(appPaths.DataPath, "userdata_v2.db");
  25. }
  26. /// <summary>
  27. /// Gets the name of the repository
  28. /// </summary>
  29. /// <value>The name.</value>
  30. public string Name
  31. {
  32. get
  33. {
  34. return "SQLite";
  35. }
  36. }
  37. /// <summary>
  38. /// Opens the connection to the database
  39. /// </summary>
  40. /// <returns>Task.</returns>
  41. public void Initialize(ReaderWriterLockSlim writeLock)
  42. {
  43. WriteLock.Dispose();
  44. WriteLock = writeLock;
  45. using (var connection = CreateConnection())
  46. {
  47. string[] queries = {
  48. "pragma temp_store = memory",
  49. "create table if not exists userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null)",
  50. "create table if not exists DataSettings (IsUserDataImported bit)",
  51. "drop index if exists idx_userdata",
  52. "drop index if exists idx_userdata1",
  53. "drop index if exists idx_userdata2",
  54. "drop index if exists userdataindex1",
  55. "create unique index if not exists userdataindex on userdata (key, userId)",
  56. "create index if not exists userdataindex2 on userdata (key, userId, played)",
  57. "create index if not exists userdataindex3 on userdata (key, userId, playbackPositionTicks)",
  58. "create index if not exists userdataindex4 on userdata (key, userId, isFavorite)",
  59. "pragma shrink_memory"
  60. };
  61. connection.RunQueries(queries);
  62. connection.RunInTransaction(db =>
  63. {
  64. var existingColumnNames = GetColumnNames(db, "userdata");
  65. AddColumn(db, "userdata", "AudioStreamIndex", "int", existingColumnNames);
  66. AddColumn(db, "userdata", "SubtitleStreamIndex", "int", existingColumnNames);
  67. });
  68. ImportUserDataIfNeeded(connection);
  69. }
  70. }
  71. private void ImportUserDataIfNeeded(IDatabaseConnection connection)
  72. {
  73. if (!_fileSystem.FileExists(_importFile))
  74. {
  75. return;
  76. }
  77. var fileToImport = _importFile;
  78. var isImported = connection.Query("select IsUserDataImported from DataSettings").SelectScalarBool().FirstOrDefault();
  79. if (isImported)
  80. {
  81. return;
  82. }
  83. ImportUserData(connection, fileToImport);
  84. connection.RunInTransaction(db =>
  85. {
  86. using (var statement = db.PrepareStatement("replace into DataSettings (IsUserDataImported) values (@IsUserDataImported)"))
  87. {
  88. statement.TryBind("@IsUserDataImported", true);
  89. statement.MoveNext();
  90. }
  91. });
  92. }
  93. private void ImportUserData(IDatabaseConnection connection, string file)
  94. {
  95. SqliteExtensions.Attach(connection, file, "UserDataBackup");
  96. var columns = "key, userId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex";
  97. connection.RunInTransaction(db =>
  98. {
  99. db.Execute("REPLACE INTO userdata(" + columns + ") SELECT " + columns + " FROM UserDataBackup.userdata;");
  100. });
  101. }
  102. /// <summary>
  103. /// Saves the user data.
  104. /// </summary>
  105. /// <param name="userId">The user id.</param>
  106. /// <param name="key">The key.</param>
  107. /// <param name="userData">The user data.</param>
  108. /// <param name="cancellationToken">The cancellation token.</param>
  109. /// <returns>Task.</returns>
  110. /// <exception cref="System.ArgumentNullException">userData
  111. /// or
  112. /// cancellationToken
  113. /// or
  114. /// userId
  115. /// or
  116. /// userDataId</exception>
  117. public Task SaveUserData(Guid userId, string key, UserItemData userData, CancellationToken cancellationToken)
  118. {
  119. if (userData == null)
  120. {
  121. throw new ArgumentNullException("userData");
  122. }
  123. if (userId == Guid.Empty)
  124. {
  125. throw new ArgumentNullException("userId");
  126. }
  127. if (string.IsNullOrEmpty(key))
  128. {
  129. throw new ArgumentNullException("key");
  130. }
  131. return PersistUserData(userId, key, userData, cancellationToken);
  132. }
  133. public Task SaveAllUserData(Guid userId, IEnumerable<UserItemData> userData, CancellationToken cancellationToken)
  134. {
  135. if (userData == null)
  136. {
  137. throw new ArgumentNullException("userData");
  138. }
  139. if (userId == Guid.Empty)
  140. {
  141. throw new ArgumentNullException("userId");
  142. }
  143. return PersistAllUserData(userId, userData.ToList(), cancellationToken);
  144. }
  145. /// <summary>
  146. /// Persists the user data.
  147. /// </summary>
  148. /// <param name="userId">The user id.</param>
  149. /// <param name="key">The key.</param>
  150. /// <param name="userData">The user data.</param>
  151. /// <param name="cancellationToken">The cancellation token.</param>
  152. /// <returns>Task.</returns>
  153. public async Task PersistUserData(Guid userId, string key, UserItemData userData, CancellationToken cancellationToken)
  154. {
  155. cancellationToken.ThrowIfCancellationRequested();
  156. using (var connection = CreateConnection())
  157. {
  158. using (WriteLock.Write())
  159. {
  160. connection.RunInTransaction(db =>
  161. {
  162. SaveUserData(db, userId, key, userData);
  163. });
  164. }
  165. }
  166. }
  167. private void SaveUserData(IDatabaseConnection db, Guid userId, string key, UserItemData userData)
  168. {
  169. 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)"))
  170. {
  171. statement.TryBind("@userId", userId.ToGuidParamValue());
  172. statement.TryBind("@key", key);
  173. if (userData.Rating.HasValue)
  174. {
  175. statement.TryBind("@rating", userData.Rating.Value);
  176. }
  177. else
  178. {
  179. statement.TryBindNull("@rating");
  180. }
  181. statement.TryBind("@played", userData.Played);
  182. statement.TryBind("@playCount", userData.PlayCount);
  183. statement.TryBind("@isFavorite", userData.IsFavorite);
  184. statement.TryBind("@playbackPositionTicks", userData.PlaybackPositionTicks);
  185. if (userData.LastPlayedDate.HasValue)
  186. {
  187. statement.TryBind("@lastPlayedDate", userData.LastPlayedDate.Value.ToDateTimeParamValue());
  188. }
  189. else
  190. {
  191. statement.TryBindNull("@lastPlayedDate");
  192. }
  193. if (userData.AudioStreamIndex.HasValue)
  194. {
  195. statement.TryBind("@AudioStreamIndex", userData.AudioStreamIndex.Value);
  196. }
  197. else
  198. {
  199. statement.TryBindNull("@AudioStreamIndex");
  200. }
  201. if (userData.SubtitleStreamIndex.HasValue)
  202. {
  203. statement.TryBind("@SubtitleStreamIndex", userData.SubtitleStreamIndex.Value);
  204. }
  205. else
  206. {
  207. statement.TryBindNull("@SubtitleStreamIndex");
  208. }
  209. statement.MoveNext();
  210. }
  211. }
  212. /// <summary>
  213. /// Persist all user data for the specified user
  214. /// </summary>
  215. private async Task PersistAllUserData(Guid userId, List<UserItemData> userDataList, CancellationToken cancellationToken)
  216. {
  217. cancellationToken.ThrowIfCancellationRequested();
  218. using (var connection = CreateConnection())
  219. {
  220. using (WriteLock.Write())
  221. {
  222. connection.RunInTransaction(db =>
  223. {
  224. foreach (var userItemData in userDataList)
  225. {
  226. SaveUserData(db, userId, userItemData.Key, userItemData);
  227. }
  228. });
  229. }
  230. }
  231. }
  232. /// <summary>
  233. /// Gets the user data.
  234. /// </summary>
  235. /// <param name="userId">The user id.</param>
  236. /// <param name="key">The key.</param>
  237. /// <returns>Task{UserItemData}.</returns>
  238. /// <exception cref="System.ArgumentNullException">
  239. /// userId
  240. /// or
  241. /// key
  242. /// </exception>
  243. public UserItemData GetUserData(Guid userId, string key)
  244. {
  245. if (userId == Guid.Empty)
  246. {
  247. throw new ArgumentNullException("userId");
  248. }
  249. if (string.IsNullOrEmpty(key))
  250. {
  251. throw new ArgumentNullException("key");
  252. }
  253. using (var connection = CreateConnection(true))
  254. {
  255. using (WriteLock.Read())
  256. {
  257. using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where key =@Key and userId=@UserId"))
  258. {
  259. statement.TryBind("@UserId", userId.ToGuidParamValue());
  260. statement.TryBind("@Key", key);
  261. foreach (var row in statement.ExecuteQuery())
  262. {
  263. return ReadRow(row);
  264. }
  265. }
  266. }
  267. }
  268. return null;
  269. }
  270. public UserItemData GetUserData(Guid userId, List<string> keys)
  271. {
  272. if (userId == Guid.Empty)
  273. {
  274. throw new ArgumentNullException("userId");
  275. }
  276. if (keys == null)
  277. {
  278. throw new ArgumentNullException("keys");
  279. }
  280. if (keys.Count == 0)
  281. {
  282. return null;
  283. }
  284. return GetUserData(userId, keys[0]);
  285. }
  286. /// <summary>
  287. /// Return all user-data associated with the given user
  288. /// </summary>
  289. /// <param name="userId"></param>
  290. /// <returns></returns>
  291. public IEnumerable<UserItemData> GetAllUserData(Guid userId)
  292. {
  293. if (userId == Guid.Empty)
  294. {
  295. throw new ArgumentNullException("userId");
  296. }
  297. var list = new List<UserItemData>();
  298. using (var connection = CreateConnection())
  299. {
  300. using (WriteLock.Read())
  301. {
  302. using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@UserId"))
  303. {
  304. statement.TryBind("@UserId", userId.ToGuidParamValue());
  305. foreach (var row in statement.ExecuteQuery())
  306. {
  307. list.Add(ReadRow(row));
  308. }
  309. }
  310. }
  311. }
  312. return list;
  313. }
  314. /// <summary>
  315. /// Read a row from the specified reader into the provided userData object
  316. /// </summary>
  317. /// <param name="reader"></param>
  318. private UserItemData ReadRow(IReadOnlyList<IResultSetValue> reader)
  319. {
  320. var userData = new UserItemData();
  321. userData.Key = reader[0].ToString();
  322. userData.UserId = reader[1].ReadGuid();
  323. if (reader[2].SQLiteType != SQLiteType.Null)
  324. {
  325. userData.Rating = reader[2].ToDouble();
  326. }
  327. userData.Played = reader[3].ToBool();
  328. userData.PlayCount = reader[4].ToInt();
  329. userData.IsFavorite = reader[5].ToBool();
  330. userData.PlaybackPositionTicks = reader[6].ToInt64();
  331. if (reader[7].SQLiteType != SQLiteType.Null)
  332. {
  333. userData.LastPlayedDate = reader[7].ReadDateTime();
  334. }
  335. if (reader[8].SQLiteType != SQLiteType.Null)
  336. {
  337. userData.AudioStreamIndex = reader[8].ToInt();
  338. }
  339. if (reader[9].SQLiteType != SQLiteType.Null)
  340. {
  341. userData.SubtitleStreamIndex = reader[9].ToInt();
  342. }
  343. return userData;
  344. }
  345. protected override void Dispose(bool dispose)
  346. {
  347. // handled by library database
  348. }
  349. protected override void CloseConnection()
  350. {
  351. // handled by library database
  352. }
  353. }
  354. }