diff options
Diffstat (limited to 'MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs')
| -rw-r--r-- | MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs | 693 |
1 files changed, 676 insertions, 17 deletions
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index c5a9db87b..852fbd76c 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -1,14 +1,19 @@ using MediaBrowser.Common.Configuration; using MediaBrowser.Controller.Entities; +using MediaBrowser.Controller.Entities.TV; +using MediaBrowser.Controller.LiveTv; using MediaBrowser.Controller.Persistence; using MediaBrowser.Model.Entities; using MediaBrowser.Model.Logging; +using MediaBrowser.Model.Querying; using MediaBrowser.Model.Serialization; using System; using System.Collections.Generic; using System.Data; +using System.Globalization; using System.IO; using System.Linq; +using System.Runtime.Serialization; using System.Threading; using System.Threading.Tasks; @@ -62,6 +67,8 @@ namespace MediaBrowser.Server.Implementations.Persistence private IDbCommand _saveChildrenCommand; private IDbCommand _deleteItemCommand; + private IDbCommand _deletePeopleCommand; + private IDbCommand _savePersonCommand; /// <summary> /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. /// </summary> @@ -118,6 +125,8 @@ namespace MediaBrowser.Server.Implementations.Persistence "create table if not exists ChildrenIds (ParentId GUID, ItemId GUID, PRIMARY KEY (ParentId, ItemId))", "create index if not exists idx_ChildrenIds on ChildrenIds(ParentId,ItemId)", + "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)", + //pragmas "pragma temp_store = memory", @@ -126,6 +135,27 @@ namespace MediaBrowser.Server.Implementations.Persistence _connection.RunQueries(queries, _logger); + _connection.AddColumn(_logger, "TypedBaseItems", "Path", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "StartDate", "DATETIME"); + _connection.AddColumn(_logger, "TypedBaseItems", "EndDate", "DATETIME"); + _connection.AddColumn(_logger, "TypedBaseItems", "ChannelId", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "IsMovie", "BIT"); + _connection.AddColumn(_logger, "TypedBaseItems", "IsSports", "BIT"); + _connection.AddColumn(_logger, "TypedBaseItems", "IsKids", "BIT"); + _connection.AddColumn(_logger, "TypedBaseItems", "CommunityRating", "Float"); + _connection.AddColumn(_logger, "TypedBaseItems", "CustomRating", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "IndexNumber", "INT"); + _connection.AddColumn(_logger, "TypedBaseItems", "IsLocked", "BIT"); + _connection.AddColumn(_logger, "TypedBaseItems", "Name", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "OfficialRating", "Text"); + + _connection.AddColumn(_logger, "TypedBaseItems", "MediaType", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "Overview", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "ParentIndexNumber", "INT"); + _connection.AddColumn(_logger, "TypedBaseItems", "PremiereDate", "DATETIME"); + _connection.AddColumn(_logger, "TypedBaseItems", "ProductionYear", "INT"); + _connection.AddColumn(_logger, "TypedBaseItems", "ParentId", "GUID"); + PrepareStatements(); _mediaStreamsRepository.Initialize(); @@ -142,11 +172,37 @@ namespace MediaBrowser.Server.Implementations.Persistence /// </summary> private void PrepareStatements() { + var saveColumns = new List<string> + { + "guid", + "type", + "data", + "Path", + "StartDate", + "EndDate", + "ChannelId", + "IsKids", + "IsMovie", + "IsSports", + "CommunityRating", + "CustomRating", + "IndexNumber", + "IsLocked", + "Name", + "OfficialRating", + "MediaType", + "Overview", + "ParentIndexNumber", + "PremiereDate", + "ProductionYear", + "ParentId" + }; _saveItemCommand = _connection.CreateCommand(); - _saveItemCommand.CommandText = "replace into TypedBaseItems (guid, type, data) values (@1, @2, @3)"; - _saveItemCommand.Parameters.Add(_saveItemCommand, "@1"); - _saveItemCommand.Parameters.Add(_saveItemCommand, "@2"); - _saveItemCommand.Parameters.Add(_saveItemCommand, "@3"); + _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)"; + for (var i = 1; i <= saveColumns.Count; i++) + { + _saveItemCommand.Parameters.Add(_saveItemCommand, "@" + i.ToString(CultureInfo.InvariantCulture)); + } _deleteChildrenCommand = _connection.CreateCommand(); _deleteChildrenCommand.CommandText = "delete from ChildrenIds where ParentId=@ParentId"; @@ -155,11 +211,24 @@ namespace MediaBrowser.Server.Implementations.Persistence _deleteItemCommand = _connection.CreateCommand(); _deleteItemCommand.CommandText = "delete from TypedBaseItems where guid=@Id"; _deleteItemCommand.Parameters.Add(_deleteItemCommand, "@Id"); - + _saveChildrenCommand = _connection.CreateCommand(); _saveChildrenCommand.CommandText = "replace into ChildrenIds (ParentId, ItemId) values (@ParentId, @ItemId)"; _saveChildrenCommand.Parameters.Add(_saveChildrenCommand, "@ParentId"); _saveChildrenCommand.Parameters.Add(_saveChildrenCommand, "@ItemId"); + + _deletePeopleCommand = _connection.CreateCommand(); + _deletePeopleCommand.CommandText = "delete from People where ItemId=@Id"; + _deletePeopleCommand.Parameters.Add(_deletePeopleCommand, "@Id"); + + _savePersonCommand = _connection.CreateCommand(); + _savePersonCommand.CommandText = "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values (@ItemId, @Name, @Role, @PersonType, @SortOrder, @ListOrder)"; + _savePersonCommand.Parameters.Add(_savePersonCommand, "@ItemId"); + _savePersonCommand.Parameters.Add(_savePersonCommand, "@Name"); + _savePersonCommand.Parameters.Add(_savePersonCommand, "@Role"); + _savePersonCommand.Parameters.Add(_savePersonCommand, "@PersonType"); + _savePersonCommand.Parameters.Add(_savePersonCommand, "@SortOrder"); + _savePersonCommand.Parameters.Add(_savePersonCommand, "@ListOrder"); } /// <summary> @@ -200,7 +269,7 @@ namespace MediaBrowser.Server.Implementations.Persistence cancellationToken.ThrowIfCancellationRequested(); CheckDisposed(); - + await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); IDbTransaction transaction = null; @@ -213,9 +282,64 @@ namespace MediaBrowser.Server.Implementations.Persistence { cancellationToken.ThrowIfCancellationRequested(); - _saveItemCommand.GetParameter(0).Value = item.Id; - _saveItemCommand.GetParameter(1).Value = item.GetType().FullName; - _saveItemCommand.GetParameter(2).Value = _jsonSerializer.SerializeToBytes(item); + var index = 0; + + _saveItemCommand.GetParameter(index++).Value = item.Id; + _saveItemCommand.GetParameter(index++).Value = item.GetType().FullName; + _saveItemCommand.GetParameter(index++).Value = _jsonSerializer.SerializeToBytes(item); + + _saveItemCommand.GetParameter(index++).Value = item.Path; + + var hasStartDate = item as IHasStartDate; + if (hasStartDate != null) + { + _saveItemCommand.GetParameter(index++).Value = hasStartDate.StartDate; + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + } + + _saveItemCommand.GetParameter(index++).Value = item.EndDate; + _saveItemCommand.GetParameter(index++).Value = item.ChannelId; + + var hasProgramAttributes = item as IHasProgramAttributes; + if (hasProgramAttributes != null) + { + _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsKids; + _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsMovie; + _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsSports; + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + _saveItemCommand.GetParameter(index++).Value = null; + _saveItemCommand.GetParameter(index++).Value = null; + } + + _saveItemCommand.GetParameter(index++).Value = item.CommunityRating; + _saveItemCommand.GetParameter(index++).Value = item.CustomRating; + + _saveItemCommand.GetParameter(index++).Value = item.IndexNumber; + _saveItemCommand.GetParameter(index++).Value = item.IsLocked; + + _saveItemCommand.GetParameter(index++).Value = item.Name; + _saveItemCommand.GetParameter(index++).Value = item.OfficialRating; + + _saveItemCommand.GetParameter(index++).Value = item.MediaType; + _saveItemCommand.GetParameter(index++).Value = item.Overview; + _saveItemCommand.GetParameter(index++).Value = item.ParentIndexNumber; + _saveItemCommand.GetParameter(index++).Value = item.PremiereDate; + _saveItemCommand.GetParameter(index++).Value = item.ProductionYear; + + if (item.ParentId == Guid.Empty) + { + _saveItemCommand.GetParameter(index++).Value = null; + } + else + { + _saveItemCommand.GetParameter(index++).Value = item.ParentId; + } _saveItemCommand.Transaction = transaction; @@ -254,7 +378,7 @@ namespace MediaBrowser.Server.Implementations.Persistence _writeLock.Release(); } } - + /// <summary> /// Internal retrieve from items or users table /// </summary> @@ -270,7 +394,7 @@ namespace MediaBrowser.Server.Implementations.Persistence } CheckDisposed(); - + using (var cmd = _connection.CreateCommand()) { cmd.CommandText = "select type,data from TypedBaseItems where guid = @guid"; @@ -302,7 +426,15 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var stream = reader.GetMemoryStream(1)) { - return _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem; + try + { + return _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem; + } + catch (SerializationException ex) + { + _logger.ErrorException("Error deserializing item", ex); + return null; + } } } @@ -467,7 +599,7 @@ namespace MediaBrowser.Server.Implementations.Persistence } CheckDisposed(); - + using (var cmd = _connection.CreateCommand()) { cmd.CommandText = "select ItemId from ChildrenIds where ParentId = @ParentId"; @@ -492,7 +624,7 @@ namespace MediaBrowser.Server.Implementations.Persistence } CheckDisposed(); - + using (var cmd = _connection.CreateCommand()) { cmd.CommandText = "select type,data from TypedBaseItems where guid in (select ItemId from ChildrenIds where ParentId = @ParentId)"; @@ -544,6 +676,299 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + public QueryResult<BaseItem> GetItems(InternalItemsQuery query) + { + if (query == null) + { + throw new ArgumentNullException("query"); + } + + CheckDisposed(); + + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "select type,data from TypedBaseItems"; + + var whereClauses = GetWhereClauses(query, cmd, false); + + var whereTextWithoutPaging = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); + + whereClauses = GetWhereClauses(query, cmd, true); + + var whereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); + + cmd.CommandText += whereText; + + if (query.Limit.HasValue) + { + cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); + } + + cmd.CommandText += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging; + + var list = new List<BaseItem>(); + var count = 0; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) + { + while (reader.Read()) + { + var item = GetItem(reader); + if (item != null) + { + list.Add(item); + } + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } + } + + return new QueryResult<BaseItem>() + { + Items = list.ToArray(), + TotalRecordCount = count + }; + } + } + + public List<Guid> GetItemIdsList(InternalItemsQuery query) + { + if (query == null) + { + throw new ArgumentNullException("query"); + } + + CheckDisposed(); + + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "select guid from TypedBaseItems"; + + var whereClauses = GetWhereClauses(query, cmd, true); + + var whereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); + + cmd.CommandText += whereText; + + if (query.Limit.HasValue) + { + cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); + } + + var list = new List<Guid>(); + + _logger.Debug(cmd.CommandText); + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) + { + while (reader.Read()) + { + list.Add(reader.GetGuid(0)); + } + } + + return list; + } + } + + public QueryResult<Guid> GetItemIds(InternalItemsQuery query) + { + if (query == null) + { + throw new ArgumentNullException("query"); + } + + CheckDisposed(); + + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "select guid from TypedBaseItems"; + + var whereClauses = GetWhereClauses(query, cmd, false); + + var whereTextWithoutPaging = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); + + whereClauses = GetWhereClauses(query, cmd, true); + + var whereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); + + cmd.CommandText += whereText; + + if (query.Limit.HasValue) + { + cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); + } + + cmd.CommandText += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging; + + var list = new List<Guid>(); + var count = 0; + + _logger.Debug(cmd.CommandText); + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) + { + while (reader.Read()) + { + list.Add(reader.GetGuid(0)); + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } + } + + return new QueryResult<Guid>() + { + Items = list.ToArray(), + TotalRecordCount = count + }; + } + } + + private List<string> GetWhereClauses(InternalItemsQuery query, IDbCommand cmd, bool addPaging) + { + var whereClauses = new List<string>(); + + if (query.IsMovie.HasValue) + { + whereClauses.Add("IsMovie=@IsMovie"); + cmd.Parameters.Add(cmd, "@IsMovie", DbType.Boolean).Value = query.IsMovie; + } + if (query.IsKids.HasValue) + { + whereClauses.Add("IsKids=@IsKids"); + cmd.Parameters.Add(cmd, "@IsKids", DbType.Boolean).Value = query.IsKids; + } + if (query.IsSports.HasValue) + { + whereClauses.Add("IsSports=@IsSports"); + cmd.Parameters.Add(cmd, "@IsSports", DbType.Boolean).Value = query.IsSports; + } + + var includeTypes = query.IncludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray(); + + if (includeTypes.Length == 1) + { + whereClauses.Add("type=@type"); + cmd.Parameters.Add(cmd, "@type", DbType.String).Value = includeTypes[0]; + } + if (includeTypes.Length > 1) + { + var inClause = string.Join(",", includeTypes.Select(i => "'" + i + "'").ToArray()); + whereClauses.Add(string.Format("type in ({0})", inClause)); + } + if (query.ChannelIds.Length == 1) + { + whereClauses.Add("ChannelId=@ChannelId"); + cmd.Parameters.Add(cmd, "@ChannelId", DbType.String).Value = query.ChannelIds[0]; + } + if (query.ChannelIds.Length > 1) + { + var inClause = string.Join(",", query.ChannelIds.Select(i => "'" + i + "'").ToArray()); + whereClauses.Add(string.Format("ChannelId in ({0})", inClause)); + } + + if (query.MinEndDate.HasValue) + { + whereClauses.Add("EndDate>=@MinEndDate"); + cmd.Parameters.Add(cmd, "@MinEndDate", DbType.Date).Value = query.MinEndDate.Value; + } + + if (query.MaxEndDate.HasValue) + { + whereClauses.Add("EndDate<=@MaxEndDate"); + cmd.Parameters.Add(cmd, "@MaxEndDate", DbType.Date).Value = query.MaxEndDate.Value; + } + + if (query.MinStartDate.HasValue) + { + whereClauses.Add("StartDate>=@MinStartDate"); + cmd.Parameters.Add(cmd, "@MinStartDate", DbType.Date).Value = query.MinStartDate.Value; + } + + if (query.MaxStartDate.HasValue) + { + whereClauses.Add("StartDate<=@MaxStartDate"); + cmd.Parameters.Add(cmd, "@MaxStartDate", DbType.Date).Value = query.MaxStartDate.Value; + } + + if (query.IsAiring.HasValue) + { + if (query.IsAiring.Value) + { + whereClauses.Add("StartDate<=@MaxStartDate"); + cmd.Parameters.Add(cmd, "@MaxStartDate", DbType.Date).Value = DateTime.UtcNow; + + whereClauses.Add("EndDate>=@MinEndDate"); + cmd.Parameters.Add(cmd, "@MinEndDate", DbType.Date).Value = DateTime.UtcNow; + } + else + { + whereClauses.Add("(StartDate>@IsAiringDate OR EndDate < @IsAiringDate)"); + cmd.Parameters.Add(cmd, "@IsAiringDate", DbType.Date).Value = DateTime.UtcNow; + } + } + + if (!string.IsNullOrWhiteSpace(query.Person)) + { + whereClauses.Add("Guid in (select ItemId from People where Name=@PersonName)"); + cmd.Parameters.Add(cmd, "@PersonName", DbType.String).Value = query.Person; + } + + if (addPaging) + { + if (query.StartIndex.HasValue && query.StartIndex.Value > 0) + { + var pagingWhereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); + + whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM TypedBaseItems {0} ORDER BY DateCreated DESC LIMIT {1})", + pagingWhereText, + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture))); + } + } + + return whereClauses; + } + + // Not crazy about having this all the way down here, but at least it's in one place + readonly Dictionary<string, string[]> _types = new Dictionary<string, string[]>(StringComparer.OrdinalIgnoreCase) + { + {typeof(LiveTvProgram).Name, new []{typeof(LiveTvProgram).FullName}}, + {typeof(LiveTvChannel).Name, new []{typeof(LiveTvChannel).FullName}}, + {typeof(LiveTvVideoRecording).Name, new []{typeof(LiveTvVideoRecording).FullName}}, + {typeof(LiveTvAudioRecording).Name, new []{typeof(LiveTvAudioRecording).FullName}}, + {typeof(Series).Name, new []{typeof(Series).FullName}}, + {"Recording", new []{typeof(LiveTvAudioRecording).FullName, typeof(LiveTvVideoRecording).FullName}} + }; + + private IEnumerable<string> MapIncludeItemTypes(string value) + { + string[] result; + if (_types.TryGetValue(value, out result)) + { + return result; + } + + return new[] { value }; + } + public IEnumerable<Guid> GetItemIdsOfType(Type type) { if (type == null) @@ -577,7 +1002,7 @@ namespace MediaBrowser.Server.Implementations.Persistence } CheckDisposed(); - + await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); IDbTransaction transaction = null; @@ -591,11 +1016,16 @@ namespace MediaBrowser.Server.Implementations.Persistence _deleteChildrenCommand.Transaction = transaction; _deleteChildrenCommand.ExecuteNonQuery(); + // Delete people + _deletePeopleCommand.GetParameter(0).Value = id; + _deletePeopleCommand.Transaction = transaction; + _deletePeopleCommand.ExecuteNonQuery(); + // Delete the item _deleteItemCommand.GetParameter(0).Value = id; _deleteItemCommand.Transaction = transaction; _deleteItemCommand.ExecuteNonQuery(); - + transaction.Commit(); } catch (OperationCanceledException) @@ -642,7 +1072,7 @@ namespace MediaBrowser.Server.Implementations.Persistence } CheckDisposed(); - + await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); IDbTransaction transaction = null; @@ -713,5 +1143,234 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); return _mediaStreamsRepository.SaveMediaStreams(id, streams, cancellationToken); } + + public List<string> GetPeopleNames(InternalPeopleQuery query) + { + if (query == null) + { + throw new ArgumentNullException("query"); + } + + CheckDisposed(); + + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "select Distinct Name from People"; + + var whereClauses = GetPeopleWhereClauses(query, cmd); + + if (whereClauses.Count > 0) + { + cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray()); + } + + cmd.CommandText += " order by ListOrder"; + + var list = new List<string>(); + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + list.Add(reader.GetString(0)); + } + } + + return list; + } + } + + public List<PersonInfo> GetPeople(InternalPeopleQuery query) + { + if (query == null) + { + throw new ArgumentNullException("query"); + } + + CheckDisposed(); + + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "select ItemId, Name, Role, PersonType, SortOrder from People"; + + var whereClauses = GetPeopleWhereClauses(query, cmd); + + if (whereClauses.Count > 0) + { + cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray()); + } + + cmd.CommandText += " order by ListOrder"; + + var list = new List<PersonInfo>(); + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + list.Add(GetPerson(reader)); + } + } + + return list; + } + } + + private List<string> GetPeopleWhereClauses(InternalPeopleQuery query, IDbCommand cmd) + { + var whereClauses = new List<string>(); + + if (query.ItemId != Guid.Empty) + { + whereClauses.Add("ItemId=@ItemId"); + cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = query.ItemId; + } + if (query.AppearsInItemId != Guid.Empty) + { + whereClauses.Add("Name in (Select Name from People where ItemId=@AppearsInItemId)"); + cmd.Parameters.Add(cmd, "@AppearsInItemId", DbType.Guid).Value = query.AppearsInItemId; + } + if (query.PersonTypes.Count == 1) + { + whereClauses.Add("PersonType=@PersonType"); + cmd.Parameters.Add(cmd, "@PersonType", DbType.String).Value = query.PersonTypes[0]; + } + if (query.PersonTypes.Count > 1) + { + var val = string.Join(",", query.PersonTypes.Select(i => "'" + i + "'").ToArray()); + + whereClauses.Add("PersonType in (" + val + ")"); + } + if (query.ExcludePersonTypes.Count == 1) + { + whereClauses.Add("PersonType<>@PersonType"); + cmd.Parameters.Add(cmd, "@PersonType", DbType.String).Value = query.ExcludePersonTypes[0]; + } + if (query.ExcludePersonTypes.Count > 1) + { + var val = string.Join(",", query.ExcludePersonTypes.Select(i => "'" + i + "'").ToArray()); + + whereClauses.Add("PersonType not in (" + val + ")"); + } + if (query.MaxListOrder.HasValue) + { + whereClauses.Add("ListOrder<=@MaxListOrder"); + cmd.Parameters.Add(cmd, "@MaxListOrder", DbType.Int32).Value = query.MaxListOrder.Value; + } + if (!string.IsNullOrWhiteSpace(query.NameContains)) + { + whereClauses.Add("Name like @NameContains"); + cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%"+query.NameContains+"%"; + } + + return whereClauses; + } + + public async Task UpdatePeople(Guid itemId, List<PersonInfo> people) + { + if (itemId == Guid.Empty) + { + throw new ArgumentNullException("itemId"); + } + + if (people == null) + { + throw new ArgumentNullException("people"); + } + + CheckDisposed(); + + var cancellationToken = CancellationToken.None; + + await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); + + IDbTransaction transaction = null; + + try + { + transaction = _connection.BeginTransaction(); + + // First delete + _deletePeopleCommand.GetParameter(0).Value = itemId; + _deletePeopleCommand.Transaction = transaction; + + _deletePeopleCommand.ExecuteNonQuery(); + + var listIndex = 0; + + foreach (var person in people) + { + cancellationToken.ThrowIfCancellationRequested(); + + _savePersonCommand.GetParameter(0).Value = itemId; + _savePersonCommand.GetParameter(1).Value = person.Name; + _savePersonCommand.GetParameter(2).Value = person.Role; + _savePersonCommand.GetParameter(3).Value = person.Type; + _savePersonCommand.GetParameter(4).Value = person.SortOrder; + _savePersonCommand.GetParameter(5).Value = listIndex; + + _savePersonCommand.Transaction = transaction; + + _savePersonCommand.ExecuteNonQuery(); + listIndex++; + } + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + catch (Exception e) + { + _logger.ErrorException("Failed to save people:", e); + + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + + _writeLock.Release(); + } + } + + private PersonInfo GetPerson(IDataReader reader) + { + var item = new PersonInfo(); + + item.ItemId = reader.GetGuid(0); + item.Name = reader.GetString(1); + + if (!reader.IsDBNull(2)) + { + item.Role = reader.GetString(2); + } + + if (!reader.IsDBNull(3)) + { + item.Type = reader.GetString(3); + } + + if (!reader.IsDBNull(4)) + { + item.SortOrder = reader.GetInt32(4); + } + + return item; + } } }
\ No newline at end of file |