SyncRepository.cs 29 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Globalization;
  4. using System.IO;
  5. using System.Linq;
  6. using System.Threading.Tasks;
  7. using Emby.Server.Implementations.Data;
  8. using MediaBrowser.Controller;
  9. using MediaBrowser.Controller.Sync;
  10. using MediaBrowser.Model.Dto;
  11. using MediaBrowser.Model.Logging;
  12. using MediaBrowser.Model.Querying;
  13. using MediaBrowser.Model.Serialization;
  14. using MediaBrowser.Model.Sync;
  15. using SQLitePCL.pretty;
  16. namespace Emby.Server.Implementations.Sync
  17. {
  18. public class SyncRepository : BaseSqliteRepository, ISyncRepository
  19. {
  20. private readonly CultureInfo _usCulture = new CultureInfo("en-US");
  21. private readonly IJsonSerializer _json;
  22. public SyncRepository(ILogger logger, IJsonSerializer json, IServerApplicationPaths appPaths)
  23. : base(logger)
  24. {
  25. _json = json;
  26. DbFilePath = Path.Combine(appPaths.DataPath, "sync14.db");
  27. }
  28. private class SyncSummary
  29. {
  30. public Dictionary<string, int> Items { get; set; }
  31. public SyncSummary()
  32. {
  33. Items = new Dictionary<string, int>();
  34. }
  35. }
  36. public void Initialize()
  37. {
  38. using (var connection = CreateConnection())
  39. {
  40. string[] queries = {
  41. "create table if not exists SyncJobs (Id GUID PRIMARY KEY, TargetId TEXT NOT NULL, Name TEXT NOT NULL, Profile TEXT, Quality TEXT, Bitrate INT, Status TEXT NOT NULL, Progress FLOAT, UserId TEXT NOT NULL, ItemIds TEXT NOT NULL, Category TEXT, ParentId TEXT, UnwatchedOnly BIT, ItemLimit INT, SyncNewContent BIT, DateCreated DateTime, DateLastModified DateTime, ItemCount int)",
  42. "create table if not exists SyncJobItems (Id GUID PRIMARY KEY, ItemId TEXT, ItemName TEXT, MediaSourceId TEXT, JobId TEXT, TemporaryPath TEXT, OutputPath TEXT, Status TEXT, TargetId TEXT, DateCreated DateTime, Progress FLOAT, AdditionalFiles TEXT, MediaSource TEXT, IsMarkedForRemoval BIT, JobItemIndex INT, ItemDateModifiedTicks BIGINT)",
  43. "drop index if exists idx_SyncJobItems2",
  44. "drop index if exists idx_SyncJobItems3",
  45. "drop index if exists idx_SyncJobs1",
  46. "drop index if exists idx_SyncJobs",
  47. "drop index if exists idx_SyncJobItems1",
  48. "create index if not exists idx_SyncJobItems4 on SyncJobItems(TargetId,ItemId,Status,Progress,DateCreated)",
  49. "create index if not exists idx_SyncJobItems5 on SyncJobItems(TargetId,Status,ItemId,Progress)",
  50. "create index if not exists idx_SyncJobs2 on SyncJobs(TargetId,Status,ItemIds,Progress)",
  51. "pragma shrink_memory"
  52. };
  53. connection.RunQueries(queries);
  54. connection.RunInTransaction(db =>
  55. {
  56. var existingColumnNames = GetColumnNames(db, "SyncJobs");
  57. AddColumn(db, "SyncJobs", "Profile", "TEXT", existingColumnNames);
  58. AddColumn(db, "SyncJobs", "Bitrate", "INT", existingColumnNames);
  59. existingColumnNames = GetColumnNames(db, "SyncJobItems");
  60. AddColumn(db, "SyncJobItems", "ItemDateModifiedTicks", "BIGINT", existingColumnNames);
  61. });
  62. }
  63. }
  64. private const string BaseJobSelectText = "select Id, TargetId, Name, Profile, Quality, Bitrate, Status, Progress, UserId, ItemIds, Category, ParentId, UnwatchedOnly, ItemLimit, SyncNewContent, DateCreated, DateLastModified, ItemCount from SyncJobs";
  65. private const string BaseJobItemSelectText = "select Id, ItemId, ItemName, MediaSourceId, JobId, TemporaryPath, OutputPath, Status, TargetId, DateCreated, Progress, AdditionalFiles, MediaSource, IsMarkedForRemoval, JobItemIndex, ItemDateModifiedTicks from SyncJobItems";
  66. public SyncJob GetJob(string id)
  67. {
  68. if (string.IsNullOrEmpty(id))
  69. {
  70. throw new ArgumentNullException("id");
  71. }
  72. CheckDisposed();
  73. var guid = new Guid(id);
  74. if (guid == Guid.Empty)
  75. {
  76. throw new ArgumentNullException("id");
  77. }
  78. using (WriteLock.Read())
  79. {
  80. using (var connection = CreateConnection(true))
  81. {
  82. var commandText = BaseJobSelectText + " where Id=?";
  83. var paramList = new List<object>();
  84. paramList.Add(guid.ToGuidParamValue());
  85. foreach (var row in connection.Query(commandText, paramList.ToArray()))
  86. {
  87. return GetJob(row);
  88. }
  89. return null;
  90. }
  91. }
  92. }
  93. private SyncJob GetJob(IReadOnlyList<IResultSetValue> reader)
  94. {
  95. var info = new SyncJob
  96. {
  97. Id = reader[0].ReadGuid().ToString("N"),
  98. TargetId = reader[1].ToString(),
  99. Name = reader[2].ToString()
  100. };
  101. if (reader[3].SQLiteType != SQLiteType.Null)
  102. {
  103. info.Profile = reader[3].ToString();
  104. }
  105. if (reader[4].SQLiteType != SQLiteType.Null)
  106. {
  107. info.Quality = reader[4].ToString();
  108. }
  109. if (reader[5].SQLiteType != SQLiteType.Null)
  110. {
  111. info.Bitrate = reader[5].ToInt();
  112. }
  113. if (reader[6].SQLiteType != SQLiteType.Null)
  114. {
  115. info.Status = (SyncJobStatus)Enum.Parse(typeof(SyncJobStatus), reader[6].ToString(), true);
  116. }
  117. if (reader[7].SQLiteType != SQLiteType.Null)
  118. {
  119. info.Progress = reader[7].ToDouble();
  120. }
  121. if (reader[8].SQLiteType != SQLiteType.Null)
  122. {
  123. info.UserId = reader[8].ToString();
  124. }
  125. if (reader[9].SQLiteType != SQLiteType.Null)
  126. {
  127. info.RequestedItemIds = reader[9].ToString().Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
  128. }
  129. if (reader[10].SQLiteType != SQLiteType.Null)
  130. {
  131. info.Category = (SyncCategory)Enum.Parse(typeof(SyncCategory), reader[10].ToString(), true);
  132. }
  133. if (reader[11].SQLiteType != SQLiteType.Null)
  134. {
  135. info.ParentId = reader[11].ToString();
  136. }
  137. if (reader[12].SQLiteType != SQLiteType.Null)
  138. {
  139. info.UnwatchedOnly = reader[12].ToBool();
  140. }
  141. if (reader[13].SQLiteType != SQLiteType.Null)
  142. {
  143. info.ItemLimit = reader[13].ToInt();
  144. }
  145. info.SyncNewContent = reader[14].ToBool();
  146. info.DateCreated = reader[15].ReadDateTime();
  147. info.DateLastModified = reader[16].ReadDateTime();
  148. info.ItemCount = reader[17].ToInt();
  149. return info;
  150. }
  151. public Task Create(SyncJob job)
  152. {
  153. return InsertOrUpdate(job, true);
  154. }
  155. public Task Update(SyncJob job)
  156. {
  157. return InsertOrUpdate(job, false);
  158. }
  159. private async Task InsertOrUpdate(SyncJob job, bool insert)
  160. {
  161. if (job == null)
  162. {
  163. throw new ArgumentNullException("job");
  164. }
  165. CheckDisposed();
  166. using (WriteLock.Write())
  167. {
  168. using (var connection = CreateConnection())
  169. {
  170. string commandText;
  171. var paramList = new List<object>();
  172. if (insert)
  173. {
  174. commandText = "insert into SyncJobs (Id, TargetId, Name, Profile, Quality, Bitrate, Status, Progress, UserId, ItemIds, Category, ParentId, UnwatchedOnly, ItemLimit, SyncNewContent, DateCreated, DateLastModified, ItemCount) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  175. }
  176. else
  177. {
  178. commandText = "update SyncJobs set TargetId=?,Name=?,Profile=?,Quality=?,Bitrate=?,Status=?,Progress=?,UserId=?,ItemIds=?,Category=?,ParentId=?,UnwatchedOnly=?,ItemLimit=?,SyncNewContent=?,DateCreated=?,DateLastModified=?,ItemCount=? where Id=?";
  179. }
  180. paramList.Add(job.Id.ToGuidParamValue());
  181. paramList.Add(job.TargetId);
  182. paramList.Add(job.Name);
  183. paramList.Add(job.Profile);
  184. paramList.Add(job.Quality);
  185. paramList.Add(job.Bitrate);
  186. paramList.Add(job.Status.ToString());
  187. paramList.Add(job.Progress);
  188. paramList.Add(job.UserId);
  189. paramList.Add(string.Join(",", job.RequestedItemIds.ToArray()));
  190. paramList.Add(job.Category);
  191. paramList.Add(job.ParentId);
  192. paramList.Add(job.UnwatchedOnly);
  193. paramList.Add(job.ItemLimit);
  194. paramList.Add(job.SyncNewContent);
  195. paramList.Add(job.DateCreated.ToDateTimeParamValue());
  196. paramList.Add(job.DateLastModified.ToDateTimeParamValue());
  197. paramList.Add(job.ItemCount);
  198. connection.RunInTransaction(conn =>
  199. {
  200. conn.Execute(commandText, paramList.ToArray());
  201. });
  202. }
  203. }
  204. }
  205. public async Task DeleteJob(string id)
  206. {
  207. if (string.IsNullOrWhiteSpace(id))
  208. {
  209. throw new ArgumentNullException("id");
  210. }
  211. CheckDisposed();
  212. using (WriteLock.Write())
  213. {
  214. using (var connection = CreateConnection())
  215. {
  216. connection.RunInTransaction(conn =>
  217. {
  218. conn.Execute("delete from SyncJobs where Id=?", id.ToGuidParamValue());
  219. conn.Execute("delete from SyncJobItems where JobId=?", id);
  220. });
  221. }
  222. }
  223. }
  224. public QueryResult<SyncJob> GetJobs(SyncJobQuery query)
  225. {
  226. if (query == null)
  227. {
  228. throw new ArgumentNullException("query");
  229. }
  230. CheckDisposed();
  231. using (WriteLock.Read())
  232. {
  233. using (var connection = CreateConnection(true))
  234. {
  235. var commandText = BaseJobSelectText;
  236. var paramList = new List<object>();
  237. var whereClauses = new List<string>();
  238. if (query.Statuses.Length > 0)
  239. {
  240. var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray());
  241. whereClauses.Add(string.Format("Status in ({0})", statuses));
  242. }
  243. if (!string.IsNullOrWhiteSpace(query.TargetId))
  244. {
  245. whereClauses.Add("TargetId=?");
  246. paramList.Add(query.TargetId);
  247. }
  248. if (!string.IsNullOrWhiteSpace(query.ExcludeTargetIds))
  249. {
  250. var excludeIds = (query.ExcludeTargetIds ?? string.Empty).Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
  251. if (excludeIds.Length == 1)
  252. {
  253. whereClauses.Add("TargetId<>?");
  254. paramList.Add(excludeIds[0]);
  255. }
  256. else if (excludeIds.Length > 1)
  257. {
  258. whereClauses.Add("TargetId<>?");
  259. paramList.Add(excludeIds[0]);
  260. }
  261. }
  262. if (!string.IsNullOrWhiteSpace(query.UserId))
  263. {
  264. whereClauses.Add("UserId=?");
  265. paramList.Add(query.UserId);
  266. }
  267. if (query.SyncNewContent.HasValue)
  268. {
  269. whereClauses.Add("SyncNewContent=?");
  270. paramList.Add(query.SyncNewContent.Value);
  271. }
  272. commandText += " mainTable";
  273. var whereTextWithoutPaging = whereClauses.Count == 0 ?
  274. string.Empty :
  275. " where " + string.Join(" AND ", whereClauses.ToArray());
  276. var startIndex = query.StartIndex ?? 0;
  277. if (startIndex > 0)
  278. {
  279. whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM SyncJobs ORDER BY (Select Max(DateLastModified) from SyncJobs where TargetId=mainTable.TargetId) DESC, DateLastModified DESC LIMIT {0})",
  280. startIndex.ToString(_usCulture)));
  281. }
  282. if (whereClauses.Count > 0)
  283. {
  284. commandText += " where " + string.Join(" AND ", whereClauses.ToArray());
  285. }
  286. commandText += " ORDER BY (Select Max(DateLastModified) from SyncJobs where TargetId=mainTable.TargetId) DESC, DateLastModified DESC";
  287. if (query.Limit.HasValue)
  288. {
  289. commandText += " LIMIT " + query.Limit.Value.ToString(_usCulture);
  290. }
  291. var list = new List<SyncJob>();
  292. var count = connection.Query("select count (Id) from SyncJobs" + whereTextWithoutPaging, paramList.ToArray())
  293. .SelectScalarInt()
  294. .First();
  295. foreach (var row in connection.Query(commandText, paramList.ToArray()))
  296. {
  297. list.Add(GetJob(row));
  298. }
  299. return new QueryResult<SyncJob>()
  300. {
  301. Items = list.ToArray(),
  302. TotalRecordCount = count
  303. };
  304. }
  305. }
  306. }
  307. public SyncJobItem GetJobItem(string id)
  308. {
  309. if (string.IsNullOrEmpty(id))
  310. {
  311. throw new ArgumentNullException("id");
  312. }
  313. CheckDisposed();
  314. using (WriteLock.Read())
  315. {
  316. var guid = new Guid(id);
  317. using (var connection = CreateConnection(true))
  318. {
  319. var commandText = BaseJobItemSelectText + " where Id=?";
  320. var paramList = new List<object>();
  321. paramList.Add(guid.ToGuidParamValue());
  322. foreach (var row in connection.Query(commandText, paramList.ToArray()))
  323. {
  324. return GetJobItem(row);
  325. }
  326. return null;
  327. }
  328. }
  329. }
  330. private QueryResult<T> GetJobItemReader<T>(SyncJobItemQuery query, string baseSelectText, Func<IReadOnlyList<IResultSetValue>, T> itemFactory)
  331. {
  332. if (query == null)
  333. {
  334. throw new ArgumentNullException("query");
  335. }
  336. using (WriteLock.Read())
  337. {
  338. using (var connection = CreateConnection(true))
  339. {
  340. var commandText = baseSelectText;
  341. var paramList = new List<object>();
  342. var whereClauses = new List<string>();
  343. if (!string.IsNullOrWhiteSpace(query.JobId))
  344. {
  345. whereClauses.Add("JobId=?");
  346. paramList.Add(query.JobId);
  347. }
  348. if (!string.IsNullOrWhiteSpace(query.ItemId))
  349. {
  350. whereClauses.Add("ItemId=?");
  351. paramList.Add(query.ItemId);
  352. }
  353. if (!string.IsNullOrWhiteSpace(query.TargetId))
  354. {
  355. whereClauses.Add("TargetId=?");
  356. paramList.Add(query.TargetId);
  357. }
  358. if (query.Statuses.Length > 0)
  359. {
  360. var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray());
  361. whereClauses.Add(string.Format("Status in ({0})", statuses));
  362. }
  363. var whereTextWithoutPaging = whereClauses.Count == 0 ?
  364. string.Empty :
  365. " where " + string.Join(" AND ", whereClauses.ToArray());
  366. var startIndex = query.StartIndex ?? 0;
  367. if (startIndex > 0)
  368. {
  369. whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM SyncJobItems ORDER BY JobItemIndex, DateCreated LIMIT {0})",
  370. startIndex.ToString(_usCulture)));
  371. }
  372. if (whereClauses.Count > 0)
  373. {
  374. commandText += " where " + string.Join(" AND ", whereClauses.ToArray());
  375. }
  376. commandText += " ORDER BY JobItemIndex, DateCreated";
  377. if (query.Limit.HasValue)
  378. {
  379. commandText += " LIMIT " + query.Limit.Value.ToString(_usCulture);
  380. }
  381. var list = new List<T>();
  382. var count = connection.Query("select count (Id) from SyncJobItems" + whereTextWithoutPaging, paramList.ToArray())
  383. .SelectScalarInt()
  384. .First();
  385. foreach (var row in connection.Query(commandText, paramList.ToArray()))
  386. {
  387. list.Add(itemFactory(row));
  388. }
  389. return new QueryResult<T>()
  390. {
  391. Items = list.ToArray(),
  392. TotalRecordCount = count
  393. };
  394. }
  395. }
  396. }
  397. public Dictionary<string, SyncedItemProgress> GetSyncedItemProgresses(SyncJobItemQuery query)
  398. {
  399. var result = new Dictionary<string, SyncedItemProgress>();
  400. var now = DateTime.UtcNow;
  401. using (WriteLock.Read())
  402. {
  403. using (var connection = CreateConnection(true))
  404. {
  405. var commandText = "select ItemId,Status,Progress from SyncJobItems";
  406. var whereClauses = new List<string>();
  407. if (!string.IsNullOrWhiteSpace(query.TargetId))
  408. {
  409. whereClauses.Add("TargetId=@TargetId");
  410. }
  411. if (query.Statuses.Length > 0)
  412. {
  413. var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray());
  414. whereClauses.Add(string.Format("Status in ({0})", statuses));
  415. }
  416. if (whereClauses.Count > 0)
  417. {
  418. commandText += " where " + string.Join(" AND ", whereClauses.ToArray());
  419. }
  420. using (var statement = connection.PrepareStatement(commandText))
  421. {
  422. if (!string.IsNullOrWhiteSpace(query.TargetId))
  423. {
  424. statement.TryBind("@TargetId", query.TargetId);
  425. }
  426. foreach (var row in statement.ExecuteQuery())
  427. {
  428. AddStatusResult(row, result, false);
  429. }
  430. LogQueryTime("GetSyncedItemProgresses", commandText, now);
  431. }
  432. commandText = commandText
  433. .Replace("select ItemId,Status,Progress from SyncJobItems", "select ItemIds,Status,Progress from SyncJobs")
  434. .Replace("'Synced'", "'Completed','CompletedWithError'");
  435. now = DateTime.UtcNow;
  436. using (var statement = connection.PrepareStatement(commandText))
  437. {
  438. if (!string.IsNullOrWhiteSpace(query.TargetId))
  439. {
  440. statement.TryBind("@TargetId", query.TargetId);
  441. }
  442. foreach (var row in statement.ExecuteQuery())
  443. {
  444. AddStatusResult(row, result, true);
  445. }
  446. LogQueryTime("GetSyncedItemProgresses", commandText, now);
  447. }
  448. }
  449. }
  450. return result;
  451. }
  452. private void LogQueryTime(string methodName, string commandText, DateTime startDate)
  453. {
  454. var elapsed = (DateTime.UtcNow - startDate).TotalMilliseconds;
  455. var slowThreshold = 1000;
  456. #if DEBUG
  457. slowThreshold = 50;
  458. #endif
  459. if (elapsed >= slowThreshold)
  460. {
  461. Logger.Debug("{2} query time (slow): {0}ms. Query: {1}",
  462. Convert.ToInt32(elapsed),
  463. commandText,
  464. methodName);
  465. }
  466. else
  467. {
  468. //Logger.Debug("{2} query time: {0}ms. Query: {1}",
  469. // Convert.ToInt32(elapsed),
  470. // cmd.CommandText,
  471. // methodName);
  472. }
  473. }
  474. private void AddStatusResult(IReadOnlyList<IResultSetValue> reader, Dictionary<string, SyncedItemProgress> result, bool multipleIds)
  475. {
  476. if (reader[0].SQLiteType == SQLiteType.Null)
  477. {
  478. return;
  479. }
  480. var itemIds = new List<string>();
  481. var ids = reader[0].ToString();
  482. if (multipleIds)
  483. {
  484. itemIds = ids.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
  485. }
  486. else
  487. {
  488. itemIds.Add(ids);
  489. }
  490. if (reader[1].SQLiteType != SQLiteType.Null)
  491. {
  492. SyncJobItemStatus status;
  493. var statusString = reader[1].ToString();
  494. if (string.Equals(statusString, "Completed", StringComparison.OrdinalIgnoreCase) ||
  495. string.Equals(statusString, "CompletedWithError", StringComparison.OrdinalIgnoreCase))
  496. {
  497. status = SyncJobItemStatus.Synced;
  498. }
  499. else
  500. {
  501. status = (SyncJobItemStatus)Enum.Parse(typeof(SyncJobItemStatus), statusString, true);
  502. }
  503. if (status == SyncJobItemStatus.Synced)
  504. {
  505. foreach (var itemId in itemIds)
  506. {
  507. result[itemId] = new SyncedItemProgress
  508. {
  509. Status = SyncJobItemStatus.Synced
  510. };
  511. }
  512. }
  513. else
  514. {
  515. double progress = reader[2].SQLiteType == SQLiteType.Null ? 0.0 : reader[2].ToDouble();
  516. foreach (var itemId in itemIds)
  517. {
  518. SyncedItemProgress currentStatus;
  519. if (!result.TryGetValue(itemId, out currentStatus) || (currentStatus.Status != SyncJobItemStatus.Synced && progress >= currentStatus.Progress))
  520. {
  521. result[itemId] = new SyncedItemProgress
  522. {
  523. Status = status,
  524. Progress = progress
  525. };
  526. }
  527. }
  528. }
  529. }
  530. }
  531. public QueryResult<SyncJobItem> GetJobItems(SyncJobItemQuery query)
  532. {
  533. return GetJobItemReader(query, BaseJobItemSelectText, GetJobItem);
  534. }
  535. public Task Create(SyncJobItem jobItem)
  536. {
  537. return InsertOrUpdate(jobItem, true);
  538. }
  539. public Task Update(SyncJobItem jobItem)
  540. {
  541. return InsertOrUpdate(jobItem, false);
  542. }
  543. private async Task InsertOrUpdate(SyncJobItem jobItem, bool insert)
  544. {
  545. if (jobItem == null)
  546. {
  547. throw new ArgumentNullException("jobItem");
  548. }
  549. CheckDisposed();
  550. using (WriteLock.Write())
  551. {
  552. using (var connection = CreateConnection())
  553. {
  554. string commandText;
  555. if (insert)
  556. {
  557. commandText = "insert into SyncJobItems (Id, ItemId, ItemName, MediaSourceId, JobId, TemporaryPath, OutputPath, Status, TargetId, DateCreated, Progress, AdditionalFiles, MediaSource, IsMarkedForRemoval, JobItemIndex, ItemDateModifiedTicks) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  558. }
  559. else
  560. {
  561. // cmd
  562. commandText = "update SyncJobItems set ItemId=?,ItemName=?,MediaSourceId=?,JobId=?,TemporaryPath=?,OutputPath=?,Status=?,TargetId=?,DateCreated=?,Progress=?,AdditionalFiles=?,MediaSource=?,IsMarkedForRemoval=?,JobItemIndex=?,ItemDateModifiedTicks=? where Id=?";
  563. }
  564. var paramList = new List<object>();
  565. paramList.Add(jobItem.Id.ToGuidParamValue());
  566. paramList.Add(jobItem.ItemId);
  567. paramList.Add(jobItem.ItemName);
  568. paramList.Add(jobItem.MediaSourceId);
  569. paramList.Add(jobItem.JobId);
  570. paramList.Add(jobItem.TemporaryPath);
  571. paramList.Add(jobItem.OutputPath);
  572. paramList.Add(jobItem.Status.ToString());
  573. paramList.Add(jobItem.TargetId);
  574. paramList.Add(jobItem.DateCreated.ToDateTimeParamValue());
  575. paramList.Add(jobItem.Progress);
  576. paramList.Add(_json.SerializeToString(jobItem.AdditionalFiles));
  577. paramList.Add(jobItem.MediaSource == null ? null : _json.SerializeToString(jobItem.MediaSource));
  578. paramList.Add(jobItem.IsMarkedForRemoval);
  579. paramList.Add(jobItem.JobItemIndex);
  580. paramList.Add(jobItem.ItemDateModifiedTicks);
  581. connection.RunInTransaction(conn =>
  582. {
  583. conn.Execute(commandText, paramList.ToArray());
  584. });
  585. }
  586. }
  587. }
  588. private SyncJobItem GetJobItem(IReadOnlyList<IResultSetValue> reader)
  589. {
  590. var info = new SyncJobItem
  591. {
  592. Id = reader[0].ReadGuid().ToString("N"),
  593. ItemId = reader[1].ToString()
  594. };
  595. if (reader[2].SQLiteType != SQLiteType.Null)
  596. {
  597. info.ItemName = reader[2].ToString();
  598. }
  599. if (reader[3].SQLiteType != SQLiteType.Null)
  600. {
  601. info.MediaSourceId = reader[3].ToString();
  602. }
  603. info.JobId = reader[4].ToString();
  604. if (reader[5].SQLiteType != SQLiteType.Null)
  605. {
  606. info.TemporaryPath = reader[5].ToString();
  607. }
  608. if (reader[6].SQLiteType != SQLiteType.Null)
  609. {
  610. info.OutputPath = reader[6].ToString();
  611. }
  612. if (reader[7].SQLiteType != SQLiteType.Null)
  613. {
  614. info.Status = (SyncJobItemStatus)Enum.Parse(typeof(SyncJobItemStatus), reader[7].ToString(), true);
  615. }
  616. info.TargetId = reader[8].ToString();
  617. info.DateCreated = reader[9].ReadDateTime();
  618. if (reader[10].SQLiteType != SQLiteType.Null)
  619. {
  620. info.Progress = reader[10].ToDouble();
  621. }
  622. if (reader[11].SQLiteType != SQLiteType.Null)
  623. {
  624. var json = reader[11].ToString();
  625. if (!string.IsNullOrWhiteSpace(json))
  626. {
  627. info.AdditionalFiles = _json.DeserializeFromString<List<ItemFileInfo>>(json);
  628. }
  629. }
  630. if (reader[12].SQLiteType != SQLiteType.Null)
  631. {
  632. var json = reader[12].ToString();
  633. if (!string.IsNullOrWhiteSpace(json))
  634. {
  635. info.MediaSource = _json.DeserializeFromString<MediaSourceInfo>(json);
  636. }
  637. }
  638. info.IsMarkedForRemoval = reader[13].ToBool();
  639. info.JobItemIndex = reader[14].ToInt();
  640. if (reader[15].SQLiteType != SQLiteType.Null)
  641. {
  642. info.ItemDateModifiedTicks = reader[15].ToInt64();
  643. }
  644. return info;
  645. }
  646. }
  647. }