SqliteProviderInfoRepository.cs 8.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272
  1. using MediaBrowser.Controller.Providers;
  2. using MediaBrowser.Model.Logging;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.Data;
  6. using System.Linq;
  7. using System.Threading;
  8. using System.Threading.Tasks;
  9. namespace MediaBrowser.Server.Implementations.Persistence
  10. {
  11. class SqliteProviderInfoRepository
  12. {
  13. private IDbConnection _connection;
  14. private readonly ILogger _logger;
  15. private IDbCommand _deleteInfosCommand;
  16. private IDbCommand _saveInfoCommand;
  17. public SqliteProviderInfoRepository(IDbConnection connection, ILogManager logManager)
  18. {
  19. _connection = connection;
  20. _logger = logManager.GetLogger(GetType().Name);
  21. }
  22. private SqliteShrinkMemoryTimer _shrinkMemoryTimer;
  23. /// <summary>
  24. /// Opens the connection to the database
  25. /// </summary>
  26. /// <returns>Task.</returns>
  27. public void Initialize()
  28. {
  29. var createTableCommand
  30. = "create table if not exists providerinfos ";
  31. createTableCommand += "(ItemId GUID, ProviderId GUID, ProviderVersion TEXT, FileStamp GUID, LastRefreshStatus TEXT, LastRefreshed datetime, PRIMARY KEY (ItemId, ProviderId))";
  32. string[] queries = {
  33. createTableCommand,
  34. "create index if not exists idx_providerinfos on providerinfos(ItemId, ProviderId)",
  35. //pragmas
  36. "pragma temp_store = memory",
  37. "pragma shrink_memory"
  38. };
  39. _connection.RunQueries(queries, _logger);
  40. PrepareStatements();
  41. _shrinkMemoryTimer = new SqliteShrinkMemoryTimer(_connection, _writeLock, _logger);
  42. }
  43. private static readonly string[] SaveColumns =
  44. {
  45. "ItemId",
  46. "ProviderId",
  47. "ProviderVersion",
  48. "FileStamp",
  49. "LastRefreshStatus",
  50. "LastRefreshed"
  51. };
  52. private readonly string[] _selectColumns = SaveColumns.Skip(1).ToArray();
  53. /// <summary>
  54. /// The _write lock
  55. /// </summary>
  56. private readonly SemaphoreSlim _writeLock = new SemaphoreSlim(1, 1);
  57. /// <summary>
  58. /// Prepares the statements.
  59. /// </summary>
  60. private void PrepareStatements()
  61. {
  62. _deleteInfosCommand = _connection.CreateCommand();
  63. _deleteInfosCommand.CommandText = "delete from providerinfos where ItemId=@ItemId";
  64. _deleteInfosCommand.Parameters.Add(_deleteInfosCommand, "@ItemId");
  65. _saveInfoCommand = _connection.CreateCommand();
  66. _saveInfoCommand.CommandText = string.Format("replace into providerinfos ({0}) values ({1})",
  67. string.Join(",", SaveColumns),
  68. string.Join(",", SaveColumns.Select(i => "@" + i).ToArray()));
  69. foreach (var col in SaveColumns)
  70. {
  71. _saveInfoCommand.Parameters.Add(_saveInfoCommand, "@" + col);
  72. }
  73. }
  74. public IEnumerable<BaseProviderInfo> GetBaseProviderInfos(Guid itemId)
  75. {
  76. if (itemId == Guid.Empty)
  77. {
  78. throw new ArgumentNullException("itemId");
  79. }
  80. using (var cmd = _connection.CreateCommand())
  81. {
  82. var cmdText = "select " + string.Join(",", _selectColumns) + " from providerinfos where";
  83. cmdText += " ItemId=@ItemId";
  84. cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = itemId;
  85. cmd.CommandText = cmdText;
  86. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  87. {
  88. while (reader.Read())
  89. {
  90. yield return GetBaseProviderInfo(reader);
  91. }
  92. }
  93. }
  94. }
  95. /// <summary>
  96. /// Gets the chapter.
  97. /// </summary>
  98. /// <param name="reader">The reader.</param>
  99. /// <returns>ChapterInfo.</returns>
  100. private BaseProviderInfo GetBaseProviderInfo(IDataReader reader)
  101. {
  102. var item = new BaseProviderInfo
  103. {
  104. ProviderId = reader.GetGuid(0)
  105. };
  106. if (!reader.IsDBNull(1))
  107. {
  108. item.ProviderVersion = reader.GetString(1);
  109. }
  110. item.FileStamp = reader.GetGuid(2);
  111. item.LastRefreshStatus = (ProviderRefreshStatus)Enum.Parse(typeof(ProviderRefreshStatus), reader.GetString(3), true);
  112. item.LastRefreshed = reader.GetDateTime(4).ToUniversalTime();
  113. return item;
  114. }
  115. public async Task SaveProviderInfos(Guid id, IEnumerable<BaseProviderInfo> infos, CancellationToken cancellationToken)
  116. {
  117. if (id == Guid.Empty)
  118. {
  119. throw new ArgumentNullException("id");
  120. }
  121. if (infos == null)
  122. {
  123. throw new ArgumentNullException("infos");
  124. }
  125. cancellationToken.ThrowIfCancellationRequested();
  126. await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  127. IDbTransaction transaction = null;
  128. try
  129. {
  130. transaction = _connection.BeginTransaction();
  131. // First delete chapters
  132. _deleteInfosCommand.GetParameter(0).Value = id;
  133. _deleteInfosCommand.Transaction = transaction;
  134. _deleteInfosCommand.ExecuteNonQuery();
  135. foreach (var stream in infos)
  136. {
  137. cancellationToken.ThrowIfCancellationRequested();
  138. _saveInfoCommand.GetParameter(0).Value = id;
  139. _saveInfoCommand.GetParameter(1).Value = stream.ProviderId;
  140. _saveInfoCommand.GetParameter(2).Value = stream.ProviderVersion;
  141. _saveInfoCommand.GetParameter(3).Value = stream.FileStamp;
  142. _saveInfoCommand.GetParameter(4).Value = stream.LastRefreshStatus.ToString();
  143. _saveInfoCommand.GetParameter(5).Value = stream.LastRefreshed;
  144. _saveInfoCommand.Transaction = transaction;
  145. _saveInfoCommand.ExecuteNonQuery();
  146. }
  147. transaction.Commit();
  148. }
  149. catch (OperationCanceledException)
  150. {
  151. if (transaction != null)
  152. {
  153. transaction.Rollback();
  154. }
  155. throw;
  156. }
  157. catch (Exception e)
  158. {
  159. _logger.ErrorException("Failed to save provider info:", e);
  160. if (transaction != null)
  161. {
  162. transaction.Rollback();
  163. }
  164. throw;
  165. }
  166. finally
  167. {
  168. if (transaction != null)
  169. {
  170. transaction.Dispose();
  171. }
  172. _writeLock.Release();
  173. }
  174. }
  175. /// <summary>
  176. /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
  177. /// </summary>
  178. public void Dispose()
  179. {
  180. Dispose(true);
  181. GC.SuppressFinalize(this);
  182. }
  183. private readonly object _disposeLock = new object();
  184. /// <summary>
  185. /// Releases unmanaged and - optionally - managed resources.
  186. /// </summary>
  187. /// <param name="dispose"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param>
  188. protected virtual void Dispose(bool dispose)
  189. {
  190. if (dispose)
  191. {
  192. try
  193. {
  194. lock (_disposeLock)
  195. {
  196. if (_shrinkMemoryTimer != null)
  197. {
  198. _shrinkMemoryTimer.Dispose();
  199. _shrinkMemoryTimer = null;
  200. }
  201. if (_connection != null)
  202. {
  203. if (_connection.IsOpen())
  204. {
  205. _connection.Close();
  206. }
  207. _connection.Dispose();
  208. _connection = null;
  209. }
  210. }
  211. }
  212. catch (Exception ex)
  213. {
  214. _logger.ErrorException("Error disposing database", ex);
  215. }
  216. }
  217. }
  218. }
  219. }