SqliteItemRepository.cs 52 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493
  1. using MediaBrowser.Common.Configuration;
  2. using MediaBrowser.Controller.Entities;
  3. using MediaBrowser.Controller.Entities.Audio;
  4. using MediaBrowser.Controller.Entities.Movies;
  5. using MediaBrowser.Controller.Entities.TV;
  6. using MediaBrowser.Controller.LiveTv;
  7. using MediaBrowser.Controller.Persistence;
  8. using MediaBrowser.Model.Entities;
  9. using MediaBrowser.Model.Logging;
  10. using MediaBrowser.Model.Querying;
  11. using MediaBrowser.Model.Serialization;
  12. using System;
  13. using System.Collections.Generic;
  14. using System.Data;
  15. using System.Globalization;
  16. using System.IO;
  17. using System.Linq;
  18. using System.Runtime.Serialization;
  19. using System.Threading;
  20. using System.Threading.Tasks;
  21. namespace MediaBrowser.Server.Implementations.Persistence
  22. {
  23. /// <summary>
  24. /// Class SQLiteItemRepository
  25. /// </summary>
  26. public class SqliteItemRepository : IItemRepository
  27. {
  28. private IDbConnection _connection;
  29. private readonly ILogger _logger;
  30. private readonly TypeMapper _typeMapper = new TypeMapper();
  31. /// <summary>
  32. /// Gets the name of the repository
  33. /// </summary>
  34. /// <value>The name.</value>
  35. public string Name
  36. {
  37. get
  38. {
  39. return "SQLite";
  40. }
  41. }
  42. /// <summary>
  43. /// Gets the json serializer.
  44. /// </summary>
  45. /// <value>The json serializer.</value>
  46. private readonly IJsonSerializer _jsonSerializer;
  47. /// <summary>
  48. /// The _app paths
  49. /// </summary>
  50. private readonly IApplicationPaths _appPaths;
  51. /// <summary>
  52. /// The _save item command
  53. /// </summary>
  54. private IDbCommand _saveItemCommand;
  55. private readonly string _criticReviewsPath;
  56. private SqliteChapterRepository _chapterRepository;
  57. private SqliteMediaStreamsRepository _mediaStreamsRepository;
  58. private IDbCommand _deleteChildrenCommand;
  59. private IDbCommand _saveChildrenCommand;
  60. private IDbCommand _deleteItemCommand;
  61. private IDbCommand _deletePeopleCommand;
  62. private IDbCommand _savePersonCommand;
  63. private const int LatestSchemaVersion = 2;
  64. /// <summary>
  65. /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class.
  66. /// </summary>
  67. /// <param name="appPaths">The app paths.</param>
  68. /// <param name="jsonSerializer">The json serializer.</param>
  69. /// <param name="logManager">The log manager.</param>
  70. /// <exception cref="System.ArgumentNullException">
  71. /// appPaths
  72. /// or
  73. /// jsonSerializer
  74. /// </exception>
  75. public SqliteItemRepository(IApplicationPaths appPaths, IJsonSerializer jsonSerializer, ILogManager logManager)
  76. {
  77. if (appPaths == null)
  78. {
  79. throw new ArgumentNullException("appPaths");
  80. }
  81. if (jsonSerializer == null)
  82. {
  83. throw new ArgumentNullException("jsonSerializer");
  84. }
  85. _appPaths = appPaths;
  86. _jsonSerializer = jsonSerializer;
  87. _criticReviewsPath = Path.Combine(_appPaths.DataPath, "critic-reviews");
  88. _logger = logManager.GetLogger(GetType().Name);
  89. var chapterDbFile = Path.Combine(_appPaths.DataPath, "chapters.db");
  90. var chapterConnection = SqliteExtensions.ConnectToDb(chapterDbFile, _logger).Result;
  91. _chapterRepository = new SqliteChapterRepository(chapterConnection, logManager);
  92. var mediaStreamsDbFile = Path.Combine(_appPaths.DataPath, "mediainfo.db");
  93. var mediaStreamsConnection = SqliteExtensions.ConnectToDb(mediaStreamsDbFile, _logger).Result;
  94. _mediaStreamsRepository = new SqliteMediaStreamsRepository(mediaStreamsConnection, logManager);
  95. }
  96. /// <summary>
  97. /// Opens the connection to the database
  98. /// </summary>
  99. /// <returns>Task.</returns>
  100. public async Task Initialize()
  101. {
  102. var dbFile = Path.Combine(_appPaths.DataPath, "library.db");
  103. _connection = await SqliteExtensions.ConnectToDb(dbFile, _logger).ConfigureAwait(false);
  104. string[] queries = {
  105. "create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB)",
  106. "create index if not exists idx_TypedBaseItems on TypedBaseItems(guid)",
  107. "create table if not exists ChildrenIds (ParentId GUID, ItemId GUID, PRIMARY KEY (ParentId, ItemId))",
  108. "create index if not exists idx_ChildrenIds on ChildrenIds(ParentId,ItemId)",
  109. "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)",
  110. //pragmas
  111. "pragma temp_store = memory",
  112. "pragma shrink_memory"
  113. };
  114. _connection.RunQueries(queries, _logger);
  115. _connection.AddColumn(_logger, "TypedBaseItems", "Path", "Text");
  116. _connection.AddColumn(_logger, "TypedBaseItems", "StartDate", "DATETIME");
  117. _connection.AddColumn(_logger, "TypedBaseItems", "EndDate", "DATETIME");
  118. _connection.AddColumn(_logger, "TypedBaseItems", "ChannelId", "Text");
  119. _connection.AddColumn(_logger, "TypedBaseItems", "IsMovie", "BIT");
  120. _connection.AddColumn(_logger, "TypedBaseItems", "IsSports", "BIT");
  121. _connection.AddColumn(_logger, "TypedBaseItems", "IsKids", "BIT");
  122. _connection.AddColumn(_logger, "TypedBaseItems", "CommunityRating", "Float");
  123. _connection.AddColumn(_logger, "TypedBaseItems", "CustomRating", "Text");
  124. _connection.AddColumn(_logger, "TypedBaseItems", "IndexNumber", "INT");
  125. _connection.AddColumn(_logger, "TypedBaseItems", "IsLocked", "BIT");
  126. _connection.AddColumn(_logger, "TypedBaseItems", "Name", "Text");
  127. _connection.AddColumn(_logger, "TypedBaseItems", "OfficialRating", "Text");
  128. _connection.AddColumn(_logger, "TypedBaseItems", "MediaType", "Text");
  129. _connection.AddColumn(_logger, "TypedBaseItems", "Overview", "Text");
  130. _connection.AddColumn(_logger, "TypedBaseItems", "ParentIndexNumber", "INT");
  131. _connection.AddColumn(_logger, "TypedBaseItems", "PremiereDate", "DATETIME");
  132. _connection.AddColumn(_logger, "TypedBaseItems", "ProductionYear", "INT");
  133. _connection.AddColumn(_logger, "TypedBaseItems", "ParentId", "GUID");
  134. _connection.AddColumn(_logger, "TypedBaseItems", "Genres", "Text");
  135. _connection.AddColumn(_logger, "TypedBaseItems", "ParentalRatingValue", "INT");
  136. _connection.AddColumn(_logger, "TypedBaseItems", "SchemaVersion", "INT");
  137. PrepareStatements();
  138. _mediaStreamsRepository.Initialize();
  139. _chapterRepository.Initialize();
  140. }
  141. /// <summary>
  142. /// The _write lock
  143. /// </summary>
  144. private readonly SemaphoreSlim _writeLock = new SemaphoreSlim(1, 1);
  145. /// <summary>
  146. /// Prepares the statements.
  147. /// </summary>
  148. private void PrepareStatements()
  149. {
  150. var saveColumns = new List<string>
  151. {
  152. "guid",
  153. "type",
  154. "data",
  155. "Path",
  156. "StartDate",
  157. "EndDate",
  158. "ChannelId",
  159. "IsKids",
  160. "IsMovie",
  161. "IsSports",
  162. "CommunityRating",
  163. "CustomRating",
  164. "IndexNumber",
  165. "IsLocked",
  166. "Name",
  167. "OfficialRating",
  168. "MediaType",
  169. "Overview",
  170. "ParentIndexNumber",
  171. "PremiereDate",
  172. "ProductionYear",
  173. "ParentId",
  174. "Genres",
  175. "ParentalRatingValue",
  176. "SchemaVersion"
  177. };
  178. _saveItemCommand = _connection.CreateCommand();
  179. _saveItemCommand.CommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values (@1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14, @15, @16, @17, @18, @19, @20, @21, @22, @23, @24, @25)";
  180. for (var i = 1; i <= saveColumns.Count; i++)
  181. {
  182. _saveItemCommand.Parameters.Add(_saveItemCommand, "@" + i.ToString(CultureInfo.InvariantCulture));
  183. }
  184. _deleteChildrenCommand = _connection.CreateCommand();
  185. _deleteChildrenCommand.CommandText = "delete from ChildrenIds where ParentId=@ParentId";
  186. _deleteChildrenCommand.Parameters.Add(_deleteChildrenCommand, "@ParentId");
  187. _deleteItemCommand = _connection.CreateCommand();
  188. _deleteItemCommand.CommandText = "delete from TypedBaseItems where guid=@Id";
  189. _deleteItemCommand.Parameters.Add(_deleteItemCommand, "@Id");
  190. _saveChildrenCommand = _connection.CreateCommand();
  191. _saveChildrenCommand.CommandText = "replace into ChildrenIds (ParentId, ItemId) values (@ParentId, @ItemId)";
  192. _saveChildrenCommand.Parameters.Add(_saveChildrenCommand, "@ParentId");
  193. _saveChildrenCommand.Parameters.Add(_saveChildrenCommand, "@ItemId");
  194. _deletePeopleCommand = _connection.CreateCommand();
  195. _deletePeopleCommand.CommandText = "delete from People where ItemId=@Id";
  196. _deletePeopleCommand.Parameters.Add(_deletePeopleCommand, "@Id");
  197. _savePersonCommand = _connection.CreateCommand();
  198. _savePersonCommand.CommandText = "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values (@ItemId, @Name, @Role, @PersonType, @SortOrder, @ListOrder)";
  199. _savePersonCommand.Parameters.Add(_savePersonCommand, "@ItemId");
  200. _savePersonCommand.Parameters.Add(_savePersonCommand, "@Name");
  201. _savePersonCommand.Parameters.Add(_savePersonCommand, "@Role");
  202. _savePersonCommand.Parameters.Add(_savePersonCommand, "@PersonType");
  203. _savePersonCommand.Parameters.Add(_savePersonCommand, "@SortOrder");
  204. _savePersonCommand.Parameters.Add(_savePersonCommand, "@ListOrder");
  205. }
  206. /// <summary>
  207. /// Save a standard item in the repo
  208. /// </summary>
  209. /// <param name="item">The item.</param>
  210. /// <param name="cancellationToken">The cancellation token.</param>
  211. /// <returns>Task.</returns>
  212. /// <exception cref="System.ArgumentNullException">item</exception>
  213. public Task SaveItem(BaseItem item, CancellationToken cancellationToken)
  214. {
  215. if (item == null)
  216. {
  217. throw new ArgumentNullException("item");
  218. }
  219. return SaveItems(new[] { item }, cancellationToken);
  220. }
  221. /// <summary>
  222. /// Saves the items.
  223. /// </summary>
  224. /// <param name="items">The items.</param>
  225. /// <param name="cancellationToken">The cancellation token.</param>
  226. /// <returns>Task.</returns>
  227. /// <exception cref="System.ArgumentNullException">
  228. /// items
  229. /// or
  230. /// cancellationToken
  231. /// </exception>
  232. public async Task SaveItems(IEnumerable<BaseItem> items, CancellationToken cancellationToken)
  233. {
  234. if (items == null)
  235. {
  236. throw new ArgumentNullException("items");
  237. }
  238. cancellationToken.ThrowIfCancellationRequested();
  239. CheckDisposed();
  240. await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  241. IDbTransaction transaction = null;
  242. try
  243. {
  244. transaction = _connection.BeginTransaction();
  245. foreach (var item in items)
  246. {
  247. cancellationToken.ThrowIfCancellationRequested();
  248. var index = 0;
  249. _saveItemCommand.GetParameter(index++).Value = item.Id;
  250. _saveItemCommand.GetParameter(index++).Value = item.GetType().FullName;
  251. _saveItemCommand.GetParameter(index++).Value = _jsonSerializer.SerializeToBytes(item);
  252. _saveItemCommand.GetParameter(index++).Value = item.Path;
  253. var hasStartDate = item as IHasStartDate;
  254. if (hasStartDate != null)
  255. {
  256. _saveItemCommand.GetParameter(index++).Value = hasStartDate.StartDate;
  257. }
  258. else
  259. {
  260. _saveItemCommand.GetParameter(index++).Value = null;
  261. }
  262. _saveItemCommand.GetParameter(index++).Value = item.EndDate;
  263. _saveItemCommand.GetParameter(index++).Value = item.ChannelId;
  264. var hasProgramAttributes = item as IHasProgramAttributes;
  265. if (hasProgramAttributes != null)
  266. {
  267. _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsKids;
  268. _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsMovie;
  269. _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsSports;
  270. }
  271. else
  272. {
  273. _saveItemCommand.GetParameter(index++).Value = null;
  274. _saveItemCommand.GetParameter(index++).Value = null;
  275. _saveItemCommand.GetParameter(index++).Value = null;
  276. }
  277. _saveItemCommand.GetParameter(index++).Value = item.CommunityRating;
  278. _saveItemCommand.GetParameter(index++).Value = item.CustomRating;
  279. _saveItemCommand.GetParameter(index++).Value = item.IndexNumber;
  280. _saveItemCommand.GetParameter(index++).Value = item.IsLocked;
  281. _saveItemCommand.GetParameter(index++).Value = item.Name;
  282. _saveItemCommand.GetParameter(index++).Value = item.OfficialRating;
  283. _saveItemCommand.GetParameter(index++).Value = item.MediaType;
  284. _saveItemCommand.GetParameter(index++).Value = item.Overview;
  285. _saveItemCommand.GetParameter(index++).Value = item.ParentIndexNumber;
  286. _saveItemCommand.GetParameter(index++).Value = item.PremiereDate;
  287. _saveItemCommand.GetParameter(index++).Value = item.ProductionYear;
  288. if (item.ParentId == Guid.Empty)
  289. {
  290. _saveItemCommand.GetParameter(index++).Value = null;
  291. }
  292. else
  293. {
  294. _saveItemCommand.GetParameter(index++).Value = item.ParentId;
  295. }
  296. _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Genres.ToArray());
  297. _saveItemCommand.GetParameter(index++).Value = item.GetParentalRatingValue();
  298. _saveItemCommand.GetParameter(index++).Value = LatestSchemaVersion;
  299. _saveItemCommand.Transaction = transaction;
  300. _saveItemCommand.ExecuteNonQuery();
  301. }
  302. transaction.Commit();
  303. }
  304. catch (OperationCanceledException)
  305. {
  306. if (transaction != null)
  307. {
  308. transaction.Rollback();
  309. }
  310. throw;
  311. }
  312. catch (Exception e)
  313. {
  314. _logger.ErrorException("Failed to save items:", e);
  315. if (transaction != null)
  316. {
  317. transaction.Rollback();
  318. }
  319. throw;
  320. }
  321. finally
  322. {
  323. if (transaction != null)
  324. {
  325. transaction.Dispose();
  326. }
  327. _writeLock.Release();
  328. }
  329. }
  330. /// <summary>
  331. /// Internal retrieve from items or users table
  332. /// </summary>
  333. /// <param name="id">The id.</param>
  334. /// <returns>BaseItem.</returns>
  335. /// <exception cref="System.ArgumentNullException">id</exception>
  336. /// <exception cref="System.ArgumentException"></exception>
  337. public BaseItem RetrieveItem(Guid id)
  338. {
  339. if (id == Guid.Empty)
  340. {
  341. throw new ArgumentNullException("id");
  342. }
  343. CheckDisposed();
  344. using (var cmd = _connection.CreateCommand())
  345. {
  346. cmd.CommandText = "select type,data from TypedBaseItems where guid = @guid";
  347. cmd.Parameters.Add(cmd, "@guid", DbType.Guid).Value = id;
  348. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow))
  349. {
  350. if (reader.Read())
  351. {
  352. return GetItem(reader);
  353. }
  354. }
  355. return null;
  356. }
  357. }
  358. private BaseItem GetItem(IDataReader reader)
  359. {
  360. var typeString = reader.GetString(0);
  361. var type = _typeMapper.GetType(typeString);
  362. if (type == null)
  363. {
  364. _logger.Debug("Unknown type {0}", typeString);
  365. return null;
  366. }
  367. using (var stream = reader.GetMemoryStream(1))
  368. {
  369. try
  370. {
  371. return _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem;
  372. }
  373. catch (SerializationException ex)
  374. {
  375. _logger.ErrorException("Error deserializing item", ex);
  376. return null;
  377. }
  378. }
  379. }
  380. /// <summary>
  381. /// Gets the critic reviews.
  382. /// </summary>
  383. /// <param name="itemId">The item id.</param>
  384. /// <returns>Task{IEnumerable{ItemReview}}.</returns>
  385. public IEnumerable<ItemReview> GetCriticReviews(Guid itemId)
  386. {
  387. try
  388. {
  389. var path = Path.Combine(_criticReviewsPath, itemId + ".json");
  390. return _jsonSerializer.DeserializeFromFile<List<ItemReview>>(path);
  391. }
  392. catch (DirectoryNotFoundException)
  393. {
  394. return new List<ItemReview>();
  395. }
  396. catch (FileNotFoundException)
  397. {
  398. return new List<ItemReview>();
  399. }
  400. }
  401. private readonly Task _cachedTask = Task.FromResult(true);
  402. /// <summary>
  403. /// Saves the critic reviews.
  404. /// </summary>
  405. /// <param name="itemId">The item id.</param>
  406. /// <param name="criticReviews">The critic reviews.</param>
  407. /// <returns>Task.</returns>
  408. public Task SaveCriticReviews(Guid itemId, IEnumerable<ItemReview> criticReviews)
  409. {
  410. Directory.CreateDirectory(_criticReviewsPath);
  411. var path = Path.Combine(_criticReviewsPath, itemId + ".json");
  412. _jsonSerializer.SerializeToFile(criticReviews.ToList(), path);
  413. return _cachedTask;
  414. }
  415. /// <summary>
  416. /// Gets chapters for an item
  417. /// </summary>
  418. /// <param name="id">The id.</param>
  419. /// <returns>IEnumerable{ChapterInfo}.</returns>
  420. /// <exception cref="System.ArgumentNullException">id</exception>
  421. public IEnumerable<ChapterInfo> GetChapters(Guid id)
  422. {
  423. CheckDisposed();
  424. return _chapterRepository.GetChapters(id);
  425. }
  426. /// <summary>
  427. /// Gets a single chapter for an item
  428. /// </summary>
  429. /// <param name="id">The id.</param>
  430. /// <param name="index">The index.</param>
  431. /// <returns>ChapterInfo.</returns>
  432. /// <exception cref="System.ArgumentNullException">id</exception>
  433. public ChapterInfo GetChapter(Guid id, int index)
  434. {
  435. CheckDisposed();
  436. return _chapterRepository.GetChapter(id, index);
  437. }
  438. /// <summary>
  439. /// Saves the chapters.
  440. /// </summary>
  441. /// <param name="id">The id.</param>
  442. /// <param name="chapters">The chapters.</param>
  443. /// <param name="cancellationToken">The cancellation token.</param>
  444. /// <returns>Task.</returns>
  445. /// <exception cref="System.ArgumentNullException">
  446. /// id
  447. /// or
  448. /// chapters
  449. /// or
  450. /// cancellationToken
  451. /// </exception>
  452. public Task SaveChapters(Guid id, IEnumerable<ChapterInfo> chapters, CancellationToken cancellationToken)
  453. {
  454. CheckDisposed();
  455. return _chapterRepository.SaveChapters(id, chapters, cancellationToken);
  456. }
  457. /// <summary>
  458. /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
  459. /// </summary>
  460. public void Dispose()
  461. {
  462. Dispose(true);
  463. GC.SuppressFinalize(this);
  464. }
  465. private readonly object _disposeLock = new object();
  466. private bool _disposed;
  467. private void CheckDisposed()
  468. {
  469. if (_disposed)
  470. {
  471. throw new ObjectDisposedException(GetType().Name + " has been disposed and cannot be accessed.");
  472. }
  473. }
  474. /// <summary>
  475. /// Releases unmanaged and - optionally - managed resources.
  476. /// </summary>
  477. /// <param name="dispose"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param>
  478. protected virtual void Dispose(bool dispose)
  479. {
  480. if (dispose)
  481. {
  482. _disposed = true;
  483. try
  484. {
  485. lock (_disposeLock)
  486. {
  487. _writeLock.Wait();
  488. if (_connection != null)
  489. {
  490. if (_connection.IsOpen())
  491. {
  492. _connection.Close();
  493. }
  494. _connection.Dispose();
  495. _connection = null;
  496. }
  497. if (_chapterRepository != null)
  498. {
  499. _chapterRepository.Dispose();
  500. _chapterRepository = null;
  501. }
  502. if (_mediaStreamsRepository != null)
  503. {
  504. _mediaStreamsRepository.Dispose();
  505. _mediaStreamsRepository = null;
  506. }
  507. }
  508. }
  509. catch (Exception ex)
  510. {
  511. _logger.ErrorException("Error disposing database", ex);
  512. }
  513. }
  514. }
  515. public IEnumerable<Guid> GetChildren(Guid parentId)
  516. {
  517. if (parentId == Guid.Empty)
  518. {
  519. throw new ArgumentNullException("parentId");
  520. }
  521. CheckDisposed();
  522. using (var cmd = _connection.CreateCommand())
  523. {
  524. cmd.CommandText = "select ItemId from ChildrenIds where ParentId = @ParentId";
  525. cmd.Parameters.Add(cmd, "@ParentId", DbType.Guid).Value = parentId;
  526. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  527. {
  528. while (reader.Read())
  529. {
  530. yield return reader.GetGuid(0);
  531. }
  532. }
  533. }
  534. }
  535. public IEnumerable<BaseItem> GetChildrenItems(Guid parentId)
  536. {
  537. if (parentId == Guid.Empty)
  538. {
  539. throw new ArgumentNullException("parentId");
  540. }
  541. CheckDisposed();
  542. using (var cmd = _connection.CreateCommand())
  543. {
  544. cmd.CommandText = "select type,data from TypedBaseItems where guid in (select ItemId from ChildrenIds where ParentId = @ParentId)";
  545. cmd.Parameters.Add(cmd, "@ParentId", DbType.Guid).Value = parentId;
  546. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  547. {
  548. while (reader.Read())
  549. {
  550. var item = GetItem(reader);
  551. if (item != null)
  552. {
  553. yield return item;
  554. }
  555. }
  556. }
  557. }
  558. }
  559. public IEnumerable<BaseItem> GetItemsOfType(Type type)
  560. {
  561. if (type == null)
  562. {
  563. throw new ArgumentNullException("type");
  564. }
  565. CheckDisposed();
  566. using (var cmd = _connection.CreateCommand())
  567. {
  568. cmd.CommandText = "select type,data from TypedBaseItems where type = @type";
  569. cmd.Parameters.Add(cmd, "@type", DbType.String).Value = type.FullName;
  570. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  571. {
  572. while (reader.Read())
  573. {
  574. var item = GetItem(reader);
  575. if (item != null)
  576. {
  577. yield return item;
  578. }
  579. }
  580. }
  581. }
  582. }
  583. public QueryResult<BaseItem> GetItems(InternalItemsQuery query)
  584. {
  585. if (query == null)
  586. {
  587. throw new ArgumentNullException("query");
  588. }
  589. CheckDisposed();
  590. using (var cmd = _connection.CreateCommand())
  591. {
  592. cmd.CommandText = "select type,data from TypedBaseItems";
  593. var whereClauses = GetWhereClauses(query, cmd, false);
  594. var whereTextWithoutPaging = whereClauses.Count == 0 ?
  595. string.Empty :
  596. " where " + string.Join(" AND ", whereClauses.ToArray());
  597. whereClauses = GetWhereClauses(query, cmd, true);
  598. var whereText = whereClauses.Count == 0 ?
  599. string.Empty :
  600. " where " + string.Join(" AND ", whereClauses.ToArray());
  601. cmd.CommandText += whereText;
  602. cmd.CommandText += GetOrderByText(query);
  603. if (query.Limit.HasValue)
  604. {
  605. cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture);
  606. }
  607. cmd.CommandText += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging;
  608. _logger.Debug(cmd.CommandText);
  609. var list = new List<BaseItem>();
  610. var count = 0;
  611. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
  612. {
  613. while (reader.Read())
  614. {
  615. var item = GetItem(reader);
  616. if (item != null)
  617. {
  618. list.Add(item);
  619. }
  620. }
  621. if (reader.NextResult() && reader.Read())
  622. {
  623. count = reader.GetInt32(0);
  624. }
  625. }
  626. return new QueryResult<BaseItem>()
  627. {
  628. Items = list.ToArray(),
  629. TotalRecordCount = count
  630. };
  631. }
  632. }
  633. private string GetOrderByText(InternalItemsQuery query)
  634. {
  635. if (query.SortBy == null || query.SortBy.Length == 0)
  636. {
  637. return string.Empty;
  638. }
  639. var sortOrder = query.SortOrder == SortOrder.Descending ? "DESC" : "ASC";
  640. return " ORDER BY " + string.Join(",", query.SortBy.Select(i => MapOrderByField(i) + " " + sortOrder).ToArray());
  641. }
  642. private string MapOrderByField(string name)
  643. {
  644. if (string.Equals(name, "sortname", StringComparison.OrdinalIgnoreCase))
  645. {
  646. return "name";
  647. }
  648. return name;
  649. }
  650. public List<Guid> GetItemIdsList(InternalItemsQuery query)
  651. {
  652. if (query == null)
  653. {
  654. throw new ArgumentNullException("query");
  655. }
  656. CheckDisposed();
  657. using (var cmd = _connection.CreateCommand())
  658. {
  659. cmd.CommandText = "select guid from TypedBaseItems";
  660. var whereClauses = GetWhereClauses(query, cmd, true);
  661. var whereText = whereClauses.Count == 0 ?
  662. string.Empty :
  663. " where " + string.Join(" AND ", whereClauses.ToArray());
  664. cmd.CommandText += whereText;
  665. cmd.CommandText += GetOrderByText(query);
  666. if (query.Limit.HasValue)
  667. {
  668. cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture);
  669. }
  670. var list = new List<Guid>();
  671. _logger.Debug(cmd.CommandText);
  672. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
  673. {
  674. while (reader.Read())
  675. {
  676. list.Add(reader.GetGuid(0));
  677. }
  678. }
  679. return list;
  680. }
  681. }
  682. public QueryResult<Guid> GetItemIds(InternalItemsQuery query)
  683. {
  684. if (query == null)
  685. {
  686. throw new ArgumentNullException("query");
  687. }
  688. CheckDisposed();
  689. using (var cmd = _connection.CreateCommand())
  690. {
  691. cmd.CommandText = "select guid from TypedBaseItems";
  692. var whereClauses = GetWhereClauses(query, cmd, false);
  693. var whereTextWithoutPaging = whereClauses.Count == 0 ?
  694. string.Empty :
  695. " where " + string.Join(" AND ", whereClauses.ToArray());
  696. whereClauses = GetWhereClauses(query, cmd, true);
  697. var whereText = whereClauses.Count == 0 ?
  698. string.Empty :
  699. " where " + string.Join(" AND ", whereClauses.ToArray());
  700. cmd.CommandText += whereText;
  701. cmd.CommandText += GetOrderByText(query);
  702. if (query.Limit.HasValue)
  703. {
  704. cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture);
  705. }
  706. cmd.CommandText += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging;
  707. var list = new List<Guid>();
  708. var count = 0;
  709. _logger.Debug(cmd.CommandText);
  710. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
  711. {
  712. while (reader.Read())
  713. {
  714. list.Add(reader.GetGuid(0));
  715. }
  716. if (reader.NextResult() && reader.Read())
  717. {
  718. count = reader.GetInt32(0);
  719. }
  720. }
  721. return new QueryResult<Guid>()
  722. {
  723. Items = list.ToArray(),
  724. TotalRecordCount = count
  725. };
  726. }
  727. }
  728. private List<string> GetWhereClauses(InternalItemsQuery query, IDbCommand cmd, bool addPaging)
  729. {
  730. var whereClauses = new List<string>();
  731. if (query.IsCurrentSchema.HasValue)
  732. {
  733. if (query.IsCurrentSchema.Value)
  734. {
  735. whereClauses.Add("(SchemaVersion not null AND SchemaVersion=@SchemaVersion)");
  736. }
  737. else
  738. {
  739. whereClauses.Add("(SchemaVersion is null or SchemaVersion<>@SchemaVersion)");
  740. }
  741. cmd.Parameters.Add(cmd, "@SchemaVersion", DbType.Int32).Value = LatestSchemaVersion;
  742. }
  743. if (query.IsMovie.HasValue)
  744. {
  745. whereClauses.Add("IsMovie=@IsMovie");
  746. cmd.Parameters.Add(cmd, "@IsMovie", DbType.Boolean).Value = query.IsMovie;
  747. }
  748. if (query.IsKids.HasValue)
  749. {
  750. whereClauses.Add("IsKids=@IsKids");
  751. cmd.Parameters.Add(cmd, "@IsKids", DbType.Boolean).Value = query.IsKids;
  752. }
  753. if (query.IsSports.HasValue)
  754. {
  755. whereClauses.Add("IsSports=@IsSports");
  756. cmd.Parameters.Add(cmd, "@IsSports", DbType.Boolean).Value = query.IsSports;
  757. }
  758. var includeTypes = query.IncludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray();
  759. if (includeTypes.Length == 1)
  760. {
  761. whereClauses.Add("type=@type");
  762. cmd.Parameters.Add(cmd, "@type", DbType.String).Value = includeTypes[0];
  763. }
  764. else if (includeTypes.Length > 1)
  765. {
  766. var inClause = string.Join(",", includeTypes.Select(i => "'" + i + "'").ToArray());
  767. whereClauses.Add(string.Format("type in ({0})", inClause));
  768. }
  769. if (query.ChannelIds.Length == 1)
  770. {
  771. whereClauses.Add("ChannelId=@ChannelId");
  772. cmd.Parameters.Add(cmd, "@ChannelId", DbType.String).Value = query.ChannelIds[0];
  773. }
  774. if (query.ChannelIds.Length > 1)
  775. {
  776. var inClause = string.Join(",", query.ChannelIds.Select(i => "'" + i + "'").ToArray());
  777. whereClauses.Add(string.Format("ChannelId in ({0})", inClause));
  778. }
  779. if (query.MinEndDate.HasValue)
  780. {
  781. whereClauses.Add("EndDate>=@MinEndDate");
  782. cmd.Parameters.Add(cmd, "@MinEndDate", DbType.Date).Value = query.MinEndDate.Value;
  783. }
  784. if (query.MaxEndDate.HasValue)
  785. {
  786. whereClauses.Add("EndDate<=@MaxEndDate");
  787. cmd.Parameters.Add(cmd, "@MaxEndDate", DbType.Date).Value = query.MaxEndDate.Value;
  788. }
  789. if (query.MinStartDate.HasValue)
  790. {
  791. whereClauses.Add("StartDate>=@MinStartDate");
  792. cmd.Parameters.Add(cmd, "@MinStartDate", DbType.Date).Value = query.MinStartDate.Value;
  793. }
  794. if (query.MaxStartDate.HasValue)
  795. {
  796. whereClauses.Add("StartDate<=@MaxStartDate");
  797. cmd.Parameters.Add(cmd, "@MaxStartDate", DbType.Date).Value = query.MaxStartDate.Value;
  798. }
  799. if (query.IsAiring.HasValue)
  800. {
  801. if (query.IsAiring.Value)
  802. {
  803. whereClauses.Add("StartDate<=@MaxStartDate");
  804. cmd.Parameters.Add(cmd, "@MaxStartDate", DbType.Date).Value = DateTime.UtcNow;
  805. whereClauses.Add("EndDate>=@MinEndDate");
  806. cmd.Parameters.Add(cmd, "@MinEndDate", DbType.Date).Value = DateTime.UtcNow;
  807. }
  808. else
  809. {
  810. whereClauses.Add("(StartDate>@IsAiringDate OR EndDate < @IsAiringDate)");
  811. cmd.Parameters.Add(cmd, "@IsAiringDate", DbType.Date).Value = DateTime.UtcNow;
  812. }
  813. }
  814. if (!string.IsNullOrWhiteSpace(query.Person))
  815. {
  816. whereClauses.Add("Guid in (select ItemId from People where Name=@PersonName)");
  817. cmd.Parameters.Add(cmd, "@PersonName", DbType.String).Value = query.Person;
  818. }
  819. if (!string.IsNullOrWhiteSpace(query.NameContains))
  820. {
  821. whereClauses.Add("Name like @NameContains");
  822. cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%" + query.NameContains + "%";
  823. }
  824. if (query.Genres.Length > 0)
  825. {
  826. var genres = new List<string>();
  827. var index = 0;
  828. foreach (var genre in query.Genres)
  829. {
  830. genres.Add("Genres like @Genres" + index);
  831. cmd.Parameters.Add(cmd, "@Genres" + index, DbType.String).Value = "%" + genre + "%";
  832. index++;
  833. }
  834. var genreCaluse = "(" + string.Join(" OR ", genres.ToArray()) + ")";
  835. whereClauses.Add(genreCaluse);
  836. }
  837. if (query.MaxParentalRating.HasValue)
  838. {
  839. whereClauses.Add("(ParentalRatingValue is NULL OR ParentalRatingValue<=@MaxParentalRating)");
  840. cmd.Parameters.Add(cmd, "@MaxParentalRating", DbType.Int32).Value = query.MaxParentalRating.Value;
  841. }
  842. if (query.HasParentalRating.HasValue)
  843. {
  844. if (query.HasParentalRating.Value)
  845. {
  846. whereClauses.Add("ParentalRatingValue NOT NULL");
  847. }
  848. else
  849. {
  850. whereClauses.Add("ParentalRatingValue IS NULL");
  851. }
  852. }
  853. if (addPaging)
  854. {
  855. if (query.StartIndex.HasValue && query.StartIndex.Value > 0)
  856. {
  857. var pagingWhereText = whereClauses.Count == 0 ?
  858. string.Empty :
  859. " where " + string.Join(" AND ", whereClauses.ToArray());
  860. var orderBy = GetOrderByText(query);
  861. whereClauses.Add(string.Format("guid NOT IN (SELECT guid FROM TypedBaseItems {0}" + orderBy + " LIMIT {1})",
  862. pagingWhereText,
  863. query.StartIndex.Value.ToString(CultureInfo.InvariantCulture)));
  864. }
  865. }
  866. return whereClauses;
  867. }
  868. private static readonly Type[] KnownTypes =
  869. {
  870. typeof(LiveTvProgram),
  871. typeof(LiveTvChannel),
  872. typeof(LiveTvVideoRecording),
  873. typeof(LiveTvAudioRecording),
  874. typeof(Series),
  875. typeof(LiveTvAudioRecording),
  876. typeof(LiveTvVideoRecording),
  877. typeof(Audio),
  878. typeof(MusicAlbum),
  879. typeof(MusicArtist),
  880. typeof(MusicGenre),
  881. typeof(MusicVideo),
  882. typeof(Movie),
  883. typeof(BoxSet),
  884. typeof(Episode),
  885. typeof(Season),
  886. typeof(Series),
  887. typeof(Book),
  888. typeof(CollectionFolder),
  889. typeof(Folder),
  890. typeof(Game),
  891. typeof(GameGenre),
  892. typeof(GameSystem),
  893. typeof(Genre),
  894. typeof(Person),
  895. typeof(Photo),
  896. typeof(PhotoAlbum),
  897. typeof(Studio),
  898. typeof(UserRootFolder),
  899. typeof(UserView),
  900. typeof(Video),
  901. typeof(Year)
  902. };
  903. private static Dictionary<string, string[]> GetTypeMapDictionary()
  904. {
  905. var dict = new Dictionary<string, string[]>();
  906. foreach (var t in KnownTypes)
  907. {
  908. dict[t.Name] = new[] { t.FullName };
  909. }
  910. dict["Recording"] = new[] { typeof(LiveTvAudioRecording).FullName, typeof(LiveTvVideoRecording).FullName };
  911. dict["Program"] = new[] { typeof(LiveTvProgram).FullName };
  912. dict["TvChannel"] = new[] { typeof(LiveTvChannel).FullName };
  913. return dict;
  914. }
  915. // Not crazy about having this all the way down here, but at least it's in one place
  916. readonly Dictionary<string, string[]> _types = GetTypeMapDictionary();
  917. private IEnumerable<string> MapIncludeItemTypes(string value)
  918. {
  919. string[] result;
  920. if (_types.TryGetValue(value, out result))
  921. {
  922. return result;
  923. }
  924. return new[] { value };
  925. }
  926. public async Task DeleteItem(Guid id, CancellationToken cancellationToken)
  927. {
  928. if (id == Guid.Empty)
  929. {
  930. throw new ArgumentNullException("id");
  931. }
  932. CheckDisposed();
  933. await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  934. IDbTransaction transaction = null;
  935. try
  936. {
  937. transaction = _connection.BeginTransaction();
  938. // First delete children
  939. _deleteChildrenCommand.GetParameter(0).Value = id;
  940. _deleteChildrenCommand.Transaction = transaction;
  941. _deleteChildrenCommand.ExecuteNonQuery();
  942. // Delete people
  943. _deletePeopleCommand.GetParameter(0).Value = id;
  944. _deletePeopleCommand.Transaction = transaction;
  945. _deletePeopleCommand.ExecuteNonQuery();
  946. // Delete the item
  947. _deleteItemCommand.GetParameter(0).Value = id;
  948. _deleteItemCommand.Transaction = transaction;
  949. _deleteItemCommand.ExecuteNonQuery();
  950. transaction.Commit();
  951. }
  952. catch (OperationCanceledException)
  953. {
  954. if (transaction != null)
  955. {
  956. transaction.Rollback();
  957. }
  958. throw;
  959. }
  960. catch (Exception e)
  961. {
  962. _logger.ErrorException("Failed to save children:", e);
  963. if (transaction != null)
  964. {
  965. transaction.Rollback();
  966. }
  967. throw;
  968. }
  969. finally
  970. {
  971. if (transaction != null)
  972. {
  973. transaction.Dispose();
  974. }
  975. _writeLock.Release();
  976. }
  977. }
  978. public async Task SaveChildren(Guid parentId, IEnumerable<Guid> children, CancellationToken cancellationToken)
  979. {
  980. if (parentId == Guid.Empty)
  981. {
  982. throw new ArgumentNullException("parentId");
  983. }
  984. if (children == null)
  985. {
  986. throw new ArgumentNullException("children");
  987. }
  988. CheckDisposed();
  989. await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  990. IDbTransaction transaction = null;
  991. try
  992. {
  993. transaction = _connection.BeginTransaction();
  994. // First delete
  995. _deleteChildrenCommand.GetParameter(0).Value = parentId;
  996. _deleteChildrenCommand.Transaction = transaction;
  997. _deleteChildrenCommand.ExecuteNonQuery();
  998. foreach (var id in children)
  999. {
  1000. cancellationToken.ThrowIfCancellationRequested();
  1001. _saveChildrenCommand.GetParameter(0).Value = parentId;
  1002. _saveChildrenCommand.GetParameter(1).Value = id;
  1003. _saveChildrenCommand.Transaction = transaction;
  1004. _saveChildrenCommand.ExecuteNonQuery();
  1005. }
  1006. transaction.Commit();
  1007. }
  1008. catch (OperationCanceledException)
  1009. {
  1010. if (transaction != null)
  1011. {
  1012. transaction.Rollback();
  1013. }
  1014. throw;
  1015. }
  1016. catch (Exception e)
  1017. {
  1018. _logger.ErrorException("Failed to save children:", e);
  1019. if (transaction != null)
  1020. {
  1021. transaction.Rollback();
  1022. }
  1023. throw;
  1024. }
  1025. finally
  1026. {
  1027. if (transaction != null)
  1028. {
  1029. transaction.Dispose();
  1030. }
  1031. _writeLock.Release();
  1032. }
  1033. }
  1034. public IEnumerable<MediaStream> GetMediaStreams(MediaStreamQuery query)
  1035. {
  1036. CheckDisposed();
  1037. return _mediaStreamsRepository.GetMediaStreams(query);
  1038. }
  1039. public Task SaveMediaStreams(Guid id, IEnumerable<MediaStream> streams, CancellationToken cancellationToken)
  1040. {
  1041. CheckDisposed();
  1042. return _mediaStreamsRepository.SaveMediaStreams(id, streams, cancellationToken);
  1043. }
  1044. public List<string> GetPeopleNames(InternalPeopleQuery query)
  1045. {
  1046. if (query == null)
  1047. {
  1048. throw new ArgumentNullException("query");
  1049. }
  1050. CheckDisposed();
  1051. using (var cmd = _connection.CreateCommand())
  1052. {
  1053. cmd.CommandText = "select Distinct Name from People";
  1054. var whereClauses = GetPeopleWhereClauses(query, cmd);
  1055. if (whereClauses.Count > 0)
  1056. {
  1057. cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray());
  1058. }
  1059. cmd.CommandText += " order by ListOrder";
  1060. var list = new List<string>();
  1061. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  1062. {
  1063. while (reader.Read())
  1064. {
  1065. list.Add(reader.GetString(0));
  1066. }
  1067. }
  1068. return list;
  1069. }
  1070. }
  1071. public List<PersonInfo> GetPeople(InternalPeopleQuery query)
  1072. {
  1073. if (query == null)
  1074. {
  1075. throw new ArgumentNullException("query");
  1076. }
  1077. CheckDisposed();
  1078. using (var cmd = _connection.CreateCommand())
  1079. {
  1080. cmd.CommandText = "select ItemId, Name, Role, PersonType, SortOrder from People";
  1081. var whereClauses = GetPeopleWhereClauses(query, cmd);
  1082. if (whereClauses.Count > 0)
  1083. {
  1084. cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray());
  1085. }
  1086. cmd.CommandText += " order by ListOrder";
  1087. var list = new List<PersonInfo>();
  1088. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  1089. {
  1090. while (reader.Read())
  1091. {
  1092. list.Add(GetPerson(reader));
  1093. }
  1094. }
  1095. return list;
  1096. }
  1097. }
  1098. private List<string> GetPeopleWhereClauses(InternalPeopleQuery query, IDbCommand cmd)
  1099. {
  1100. var whereClauses = new List<string>();
  1101. if (query.ItemId != Guid.Empty)
  1102. {
  1103. whereClauses.Add("ItemId=@ItemId");
  1104. cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = query.ItemId;
  1105. }
  1106. if (query.AppearsInItemId != Guid.Empty)
  1107. {
  1108. whereClauses.Add("Name in (Select Name from People where ItemId=@AppearsInItemId)");
  1109. cmd.Parameters.Add(cmd, "@AppearsInItemId", DbType.Guid).Value = query.AppearsInItemId;
  1110. }
  1111. if (query.PersonTypes.Count == 1)
  1112. {
  1113. whereClauses.Add("PersonType=@PersonType");
  1114. cmd.Parameters.Add(cmd, "@PersonType", DbType.String).Value = query.PersonTypes[0];
  1115. }
  1116. if (query.PersonTypes.Count > 1)
  1117. {
  1118. var val = string.Join(",", query.PersonTypes.Select(i => "'" + i + "'").ToArray());
  1119. whereClauses.Add("PersonType in (" + val + ")");
  1120. }
  1121. if (query.ExcludePersonTypes.Count == 1)
  1122. {
  1123. whereClauses.Add("PersonType<>@PersonType");
  1124. cmd.Parameters.Add(cmd, "@PersonType", DbType.String).Value = query.ExcludePersonTypes[0];
  1125. }
  1126. if (query.ExcludePersonTypes.Count > 1)
  1127. {
  1128. var val = string.Join(",", query.ExcludePersonTypes.Select(i => "'" + i + "'").ToArray());
  1129. whereClauses.Add("PersonType not in (" + val + ")");
  1130. }
  1131. if (query.MaxListOrder.HasValue)
  1132. {
  1133. whereClauses.Add("ListOrder<=@MaxListOrder");
  1134. cmd.Parameters.Add(cmd, "@MaxListOrder", DbType.Int32).Value = query.MaxListOrder.Value;
  1135. }
  1136. if (!string.IsNullOrWhiteSpace(query.NameContains))
  1137. {
  1138. whereClauses.Add("Name like @NameContains");
  1139. cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%" + query.NameContains + "%";
  1140. }
  1141. return whereClauses;
  1142. }
  1143. public async Task UpdatePeople(Guid itemId, List<PersonInfo> people)
  1144. {
  1145. if (itemId == Guid.Empty)
  1146. {
  1147. throw new ArgumentNullException("itemId");
  1148. }
  1149. if (people == null)
  1150. {
  1151. throw new ArgumentNullException("people");
  1152. }
  1153. CheckDisposed();
  1154. var cancellationToken = CancellationToken.None;
  1155. await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  1156. IDbTransaction transaction = null;
  1157. try
  1158. {
  1159. transaction = _connection.BeginTransaction();
  1160. // First delete
  1161. _deletePeopleCommand.GetParameter(0).Value = itemId;
  1162. _deletePeopleCommand.Transaction = transaction;
  1163. _deletePeopleCommand.ExecuteNonQuery();
  1164. var listIndex = 0;
  1165. foreach (var person in people)
  1166. {
  1167. cancellationToken.ThrowIfCancellationRequested();
  1168. _savePersonCommand.GetParameter(0).Value = itemId;
  1169. _savePersonCommand.GetParameter(1).Value = person.Name;
  1170. _savePersonCommand.GetParameter(2).Value = person.Role;
  1171. _savePersonCommand.GetParameter(3).Value = person.Type;
  1172. _savePersonCommand.GetParameter(4).Value = person.SortOrder;
  1173. _savePersonCommand.GetParameter(5).Value = listIndex;
  1174. _savePersonCommand.Transaction = transaction;
  1175. _savePersonCommand.ExecuteNonQuery();
  1176. listIndex++;
  1177. }
  1178. transaction.Commit();
  1179. }
  1180. catch (OperationCanceledException)
  1181. {
  1182. if (transaction != null)
  1183. {
  1184. transaction.Rollback();
  1185. }
  1186. throw;
  1187. }
  1188. catch (Exception e)
  1189. {
  1190. _logger.ErrorException("Failed to save people:", e);
  1191. if (transaction != null)
  1192. {
  1193. transaction.Rollback();
  1194. }
  1195. throw;
  1196. }
  1197. finally
  1198. {
  1199. if (transaction != null)
  1200. {
  1201. transaction.Dispose();
  1202. }
  1203. _writeLock.Release();
  1204. }
  1205. }
  1206. private PersonInfo GetPerson(IDataReader reader)
  1207. {
  1208. var item = new PersonInfo();
  1209. item.ItemId = reader.GetGuid(0);
  1210. item.Name = reader.GetString(1);
  1211. if (!reader.IsDBNull(2))
  1212. {
  1213. item.Role = reader.GetString(2);
  1214. }
  1215. if (!reader.IsDBNull(3))
  1216. {
  1217. item.Type = reader.GetString(3);
  1218. }
  1219. if (!reader.IsDBNull(4))
  1220. {
  1221. item.SortOrder = reader.GetInt32(4);
  1222. }
  1223. return item;
  1224. }
  1225. }
  1226. }