SqliteItemRepository.cs 17 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515
  1. using MediaBrowser.Common.Configuration;
  2. using MediaBrowser.Controller.Entities;
  3. using MediaBrowser.Controller.Persistence;
  4. using MediaBrowser.Model.Entities;
  5. using MediaBrowser.Model.Logging;
  6. using MediaBrowser.Model.Serialization;
  7. using System;
  8. using System.Collections.Generic;
  9. using System.Data;
  10. using System.IO;
  11. using System.Linq;
  12. using System.Threading;
  13. using System.Threading.Tasks;
  14. namespace MediaBrowser.Server.Implementations.Persistence
  15. {
  16. /// <summary>
  17. /// Class SQLiteItemRepository
  18. /// </summary>
  19. public class SqliteItemRepository : IItemRepository
  20. {
  21. private IDbConnection _connection;
  22. private readonly ILogger _logger;
  23. private readonly TypeMapper _typeMapper = new TypeMapper();
  24. /// <summary>
  25. /// Gets the name of the repository
  26. /// </summary>
  27. /// <value>The name.</value>
  28. public string Name
  29. {
  30. get
  31. {
  32. return "SQLite";
  33. }
  34. }
  35. /// <summary>
  36. /// Gets the json serializer.
  37. /// </summary>
  38. /// <value>The json serializer.</value>
  39. private readonly IJsonSerializer _jsonSerializer;
  40. /// <summary>
  41. /// The _app paths
  42. /// </summary>
  43. private readonly IApplicationPaths _appPaths;
  44. /// <summary>
  45. /// The _save item command
  46. /// </summary>
  47. private IDbCommand _saveItemCommand;
  48. private readonly string _criticReviewsPath;
  49. private SqliteChapterRepository _chapterRepository;
  50. private IDbCommand _deleteChildrenCommand;
  51. private IDbCommand _saveChildrenCommand;
  52. /// <summary>
  53. /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class.
  54. /// </summary>
  55. /// <param name="appPaths">The app paths.</param>
  56. /// <param name="jsonSerializer">The json serializer.</param>
  57. /// <param name="logManager">The log manager.</param>
  58. /// <exception cref="System.ArgumentNullException">
  59. /// appPaths
  60. /// or
  61. /// jsonSerializer
  62. /// </exception>
  63. public SqliteItemRepository(IApplicationPaths appPaths, IJsonSerializer jsonSerializer, ILogManager logManager)
  64. {
  65. if (appPaths == null)
  66. {
  67. throw new ArgumentNullException("appPaths");
  68. }
  69. if (jsonSerializer == null)
  70. {
  71. throw new ArgumentNullException("jsonSerializer");
  72. }
  73. _appPaths = appPaths;
  74. _jsonSerializer = jsonSerializer;
  75. _criticReviewsPath = Path.Combine(_appPaths.DataPath, "critic-reviews");
  76. _logger = logManager.GetLogger(GetType().Name);
  77. var chapterDbFile = Path.Combine(_appPaths.DataPath, "chapters.db");
  78. var chapterConnection = SqliteExtensions.ConnectToDb(chapterDbFile, _logger).Result;
  79. _chapterRepository = new SqliteChapterRepository(chapterConnection, logManager);
  80. }
  81. /// <summary>
  82. /// Opens the connection to the database
  83. /// </summary>
  84. /// <returns>Task.</returns>
  85. public async Task Initialize()
  86. {
  87. var dbFile = Path.Combine(_appPaths.DataPath, "library.db");
  88. _connection = await SqliteExtensions.ConnectToDb(dbFile, _logger).ConfigureAwait(false);
  89. string[] queries = {
  90. "create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB)",
  91. "create index if not exists idx_TypedBaseItems on TypedBaseItems(guid)",
  92. "create table if not exists ChildrenIds (ParentId GUID, ItemId GUID, PRIMARY KEY (ParentId, ItemId))",
  93. "create index if not exists idx_ChildrenIds on ChildrenIds(ParentId,ItemId)",
  94. //pragmas
  95. "pragma temp_store = memory"
  96. };
  97. _connection.RunQueries(queries, _logger);
  98. PrepareStatements();
  99. _chapterRepository.Initialize();
  100. }
  101. /// <summary>
  102. /// The _write lock
  103. /// </summary>
  104. private readonly SemaphoreSlim _writeLock = new SemaphoreSlim(1, 1);
  105. /// <summary>
  106. /// Prepares the statements.
  107. /// </summary>
  108. private void PrepareStatements()
  109. {
  110. _saveItemCommand = _connection.CreateCommand();
  111. _saveItemCommand.CommandText = "replace into TypedBaseItems (guid, type, data) values (@1, @2, @3)";
  112. _saveItemCommand.Parameters.Add(_saveItemCommand, "@1");
  113. _saveItemCommand.Parameters.Add(_saveItemCommand, "@2");
  114. _saveItemCommand.Parameters.Add(_saveItemCommand, "@3");
  115. _deleteChildrenCommand = _connection.CreateCommand();
  116. _deleteChildrenCommand.CommandText = "delete from ChildrenIds where ParentId=@ParentId";
  117. _deleteChildrenCommand.Parameters.Add(_deleteChildrenCommand, "@ParentId");
  118. _saveChildrenCommand = _connection.CreateCommand();
  119. _saveChildrenCommand.CommandText = "replace into ChildrenIds (ParentId, ItemId) values (@ParentId, @ItemId)";
  120. _saveChildrenCommand.Parameters.Add(_saveChildrenCommand, "@ParentId");
  121. _saveChildrenCommand.Parameters.Add(_saveChildrenCommand, "@ItemId");
  122. }
  123. /// <summary>
  124. /// Save a standard item in the repo
  125. /// </summary>
  126. /// <param name="item">The item.</param>
  127. /// <param name="cancellationToken">The cancellation token.</param>
  128. /// <returns>Task.</returns>
  129. /// <exception cref="System.ArgumentNullException">item</exception>
  130. public Task SaveItem(BaseItem item, CancellationToken cancellationToken)
  131. {
  132. if (item == null)
  133. {
  134. throw new ArgumentNullException("item");
  135. }
  136. return SaveItems(new[] { item }, cancellationToken);
  137. }
  138. /// <summary>
  139. /// Saves the items.
  140. /// </summary>
  141. /// <param name="items">The items.</param>
  142. /// <param name="cancellationToken">The cancellation token.</param>
  143. /// <returns>Task.</returns>
  144. /// <exception cref="System.ArgumentNullException">
  145. /// items
  146. /// or
  147. /// cancellationToken
  148. /// </exception>
  149. public async Task SaveItems(IEnumerable<BaseItem> items, CancellationToken cancellationToken)
  150. {
  151. if (items == null)
  152. {
  153. throw new ArgumentNullException("items");
  154. }
  155. cancellationToken.ThrowIfCancellationRequested();
  156. await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  157. IDbTransaction transaction = null;
  158. try
  159. {
  160. transaction = _connection.BeginTransaction();
  161. foreach (var item in items)
  162. {
  163. cancellationToken.ThrowIfCancellationRequested();
  164. _saveItemCommand.GetParameter(0).Value = item.Id;
  165. _saveItemCommand.GetParameter(1).Value = item.GetType().FullName;
  166. _saveItemCommand.GetParameter(2).Value = _jsonSerializer.SerializeToBytes(item);
  167. _saveItemCommand.Transaction = transaction;
  168. _saveItemCommand.ExecuteNonQuery();
  169. }
  170. transaction.Commit();
  171. }
  172. catch (OperationCanceledException)
  173. {
  174. if (transaction != null)
  175. {
  176. transaction.Rollback();
  177. }
  178. throw;
  179. }
  180. catch (Exception e)
  181. {
  182. _logger.ErrorException("Failed to save items:", e);
  183. if (transaction != null)
  184. {
  185. transaction.Rollback();
  186. }
  187. throw;
  188. }
  189. finally
  190. {
  191. if (transaction != null)
  192. {
  193. transaction.Dispose();
  194. }
  195. _writeLock.Release();
  196. }
  197. }
  198. /// <summary>
  199. /// Internal retrieve from items or users table
  200. /// </summary>
  201. /// <param name="id">The id.</param>
  202. /// <returns>BaseItem.</returns>
  203. /// <exception cref="System.ArgumentNullException">id</exception>
  204. /// <exception cref="System.ArgumentException"></exception>
  205. public BaseItem RetrieveItem(Guid id)
  206. {
  207. if (id == Guid.Empty)
  208. {
  209. throw new ArgumentNullException("id");
  210. }
  211. using (var cmd = _connection.CreateCommand())
  212. {
  213. cmd.CommandText = "select type,data from TypedBaseItems where guid = @guid";
  214. cmd.Parameters.Add(cmd, "@guid", DbType.Guid).Value = id;
  215. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow))
  216. {
  217. if (reader.Read())
  218. {
  219. var typeString = reader.GetString(0);
  220. var type = _typeMapper.GetType(typeString);
  221. if (type == null)
  222. {
  223. _logger.Debug("Unknown type {0}", typeString);
  224. return null;
  225. }
  226. using (var stream = reader.GetMemoryStream(1))
  227. {
  228. return _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem;
  229. }
  230. }
  231. }
  232. return null;
  233. }
  234. }
  235. /// <summary>
  236. /// Gets the critic reviews.
  237. /// </summary>
  238. /// <param name="itemId">The item id.</param>
  239. /// <returns>Task{IEnumerable{ItemReview}}.</returns>
  240. public IEnumerable<ItemReview> GetCriticReviews(Guid itemId)
  241. {
  242. try
  243. {
  244. var path = Path.Combine(_criticReviewsPath, itemId + ".json");
  245. return _jsonSerializer.DeserializeFromFile<List<ItemReview>>(path);
  246. }
  247. catch (DirectoryNotFoundException)
  248. {
  249. return new List<ItemReview>();
  250. }
  251. catch (FileNotFoundException)
  252. {
  253. return new List<ItemReview>();
  254. }
  255. }
  256. /// <summary>
  257. /// Saves the critic reviews.
  258. /// </summary>
  259. /// <param name="itemId">The item id.</param>
  260. /// <param name="criticReviews">The critic reviews.</param>
  261. /// <returns>Task.</returns>
  262. public Task SaveCriticReviews(Guid itemId, IEnumerable<ItemReview> criticReviews)
  263. {
  264. Directory.CreateDirectory(_criticReviewsPath);
  265. var path = Path.Combine(_criticReviewsPath, itemId + ".json");
  266. _jsonSerializer.SerializeToFile(criticReviews.ToList(), path);
  267. return Task.FromResult(true);
  268. }
  269. /// <summary>
  270. /// Gets chapters for an item
  271. /// </summary>
  272. /// <param name="id">The id.</param>
  273. /// <returns>IEnumerable{ChapterInfo}.</returns>
  274. /// <exception cref="System.ArgumentNullException">id</exception>
  275. public IEnumerable<ChapterInfo> GetChapters(Guid id)
  276. {
  277. return _chapterRepository.GetChapters(id);
  278. }
  279. /// <summary>
  280. /// Gets a single chapter for an item
  281. /// </summary>
  282. /// <param name="id">The id.</param>
  283. /// <param name="index">The index.</param>
  284. /// <returns>ChapterInfo.</returns>
  285. /// <exception cref="System.ArgumentNullException">id</exception>
  286. public ChapterInfo GetChapter(Guid id, int index)
  287. {
  288. return _chapterRepository.GetChapter(id, index);
  289. }
  290. /// <summary>
  291. /// Saves the chapters.
  292. /// </summary>
  293. /// <param name="id">The id.</param>
  294. /// <param name="chapters">The chapters.</param>
  295. /// <param name="cancellationToken">The cancellation token.</param>
  296. /// <returns>Task.</returns>
  297. /// <exception cref="System.ArgumentNullException">
  298. /// id
  299. /// or
  300. /// chapters
  301. /// or
  302. /// cancellationToken
  303. /// </exception>
  304. public Task SaveChapters(Guid id, IEnumerable<ChapterInfo> chapters, CancellationToken cancellationToken)
  305. {
  306. return _chapterRepository.SaveChapters(id, chapters, cancellationToken);
  307. }
  308. /// <summary>
  309. /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
  310. /// </summary>
  311. public void Dispose()
  312. {
  313. Dispose(true);
  314. GC.SuppressFinalize(this);
  315. }
  316. private readonly object _disposeLock = new object();
  317. /// <summary>
  318. /// Releases unmanaged and - optionally - managed resources.
  319. /// </summary>
  320. /// <param name="dispose"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param>
  321. protected virtual void Dispose(bool dispose)
  322. {
  323. if (dispose)
  324. {
  325. try
  326. {
  327. lock (_disposeLock)
  328. {
  329. if (_connection != null)
  330. {
  331. if (_connection.IsOpen())
  332. {
  333. _connection.Close();
  334. }
  335. _connection.Dispose();
  336. _connection = null;
  337. }
  338. }
  339. }
  340. catch (Exception ex)
  341. {
  342. _logger.ErrorException("Error disposing database", ex);
  343. }
  344. if (_chapterRepository != null)
  345. {
  346. _chapterRepository.Dispose();
  347. _chapterRepository = null;
  348. }
  349. }
  350. }
  351. public IEnumerable<Guid> GetChildren(Guid parentId)
  352. {
  353. if (parentId == Guid.Empty)
  354. {
  355. throw new ArgumentNullException("parentId");
  356. }
  357. using (var cmd = _connection.CreateCommand())
  358. {
  359. cmd.CommandText = "select ItemId from ChildrenIds where ParentId = @ParentId";
  360. cmd.Parameters.Add(cmd, "@ParentId", DbType.Guid).Value = parentId;
  361. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  362. {
  363. while (reader.Read())
  364. {
  365. yield return reader.GetGuid(0);
  366. }
  367. }
  368. }
  369. }
  370. public async Task SaveChildren(Guid parentId, IEnumerable<Guid> children, CancellationToken cancellationToken)
  371. {
  372. if (parentId == Guid.Empty)
  373. {
  374. throw new ArgumentNullException("parentId");
  375. }
  376. if (children == null)
  377. {
  378. throw new ArgumentNullException("children");
  379. }
  380. cancellationToken.ThrowIfCancellationRequested();
  381. await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  382. IDbTransaction transaction = null;
  383. try
  384. {
  385. transaction = _connection.BeginTransaction();
  386. // First delete
  387. _deleteChildrenCommand.GetParameter(0).Value = parentId;
  388. _deleteChildrenCommand.Transaction = transaction;
  389. _deleteChildrenCommand.ExecuteNonQuery();
  390. foreach (var id in children)
  391. {
  392. cancellationToken.ThrowIfCancellationRequested();
  393. _saveChildrenCommand.GetParameter(0).Value = parentId;
  394. _saveChildrenCommand.GetParameter(1).Value = id;
  395. _saveChildrenCommand.Transaction = transaction;
  396. _saveChildrenCommand.ExecuteNonQuery();
  397. }
  398. transaction.Commit();
  399. }
  400. catch (OperationCanceledException)
  401. {
  402. if (transaction != null)
  403. {
  404. transaction.Rollback();
  405. }
  406. throw;
  407. }
  408. catch (Exception e)
  409. {
  410. _logger.ErrorException("Failed to save children:", e);
  411. if (transaction != null)
  412. {
  413. transaction.Rollback();
  414. }
  415. throw;
  416. }
  417. finally
  418. {
  419. if (transaction != null)
  420. {
  421. transaction.Dispose();
  422. }
  423. _writeLock.Release();
  424. }
  425. }
  426. }
  427. }