diff options
Diffstat (limited to 'MediaBrowser.Server.Implementations/Persistence')
3 files changed, 453 insertions, 48 deletions
diff --git a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs new file mode 100644 index 000000000..9f87483ba --- /dev/null +++ b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs @@ -0,0 +1,158 @@ +using MediaBrowser.Common.Progress; +using MediaBrowser.Common.ScheduledTasks; +using MediaBrowser.Controller.Configuration; +using MediaBrowser.Controller.Entities; +using MediaBrowser.Controller.Library; +using MediaBrowser.Controller.LiveTv; +using MediaBrowser.Controller.Persistence; +using MediaBrowser.Model.Logging; +using System; +using System.Collections.Generic; +using System.Threading; +using System.Threading.Tasks; + +namespace MediaBrowser.Server.Implementations.Persistence +{ + class CleanDatabaseScheduledTask : IScheduledTask + { + private readonly ILibraryManager _libraryManager; + private readonly IItemRepository _itemRepo; + private readonly ILogger _logger; + private readonly IServerConfigurationManager _config; + + public CleanDatabaseScheduledTask(ILibraryManager libraryManager, IItemRepository itemRepo, ILogger logger, IServerConfigurationManager config) + { + _libraryManager = libraryManager; + _itemRepo = itemRepo; + _logger = logger; + _config = config; + } + + public string Name + { + get { return "Clean Database"; } + } + + public string Description + { + get { return "Deletes obsolete content from the database."; } + } + + public string Category + { + get { return "Library"; } + } + + public async Task Execute(CancellationToken cancellationToken, IProgress<double> progress) + { + var innerProgress = new ActionableProgress<double>(); + innerProgress.RegisterAction(p => progress.Report(.95 * p)); + + await UpdateToLatestSchema(cancellationToken, innerProgress).ConfigureAwait(false); + + innerProgress = new ActionableProgress<double>(); + innerProgress.RegisterAction(p => progress.Report(95 + (.05 * p))); + + //await CleanDeadItems(cancellationToken, innerProgress).ConfigureAwait(false); + + progress.Report(100); + } + + private async Task UpdateToLatestSchema(CancellationToken cancellationToken, IProgress<double> progress) + { + var itemIds = _libraryManager.GetItemIds(new InternalItemsQuery + { + IsCurrentSchema = false, + + // These are constantly getting regenerated so don't bother with them here + ExcludeItemTypes = new[] { typeof(LiveTvProgram).Name } + }); + + var numComplete = 0; + var numItems = itemIds.Count; + + _logger.Debug("Upgrading schema for {0} items", numItems); + + foreach (var itemId in itemIds) + { + cancellationToken.ThrowIfCancellationRequested(); + + var item = _libraryManager.GetItemById(itemId); + + if (item != null) + { + try + { + await _itemRepo.SaveItem(item, cancellationToken).ConfigureAwait(false); + } + catch (OperationCanceledException) + { + throw; + } + catch (Exception ex) + { + _logger.ErrorException("Error saving item", ex); + } + } + + numComplete++; + double percent = numComplete; + percent /= numItems; + progress.Report(percent * 100); + } + + if (!_config.Configuration.DisableStartupScan) + { + _config.Configuration.DisableStartupScan = true; + _config.SaveConfiguration(); + } + + progress.Report(100); + } + + private async Task CleanDeadItems(CancellationToken cancellationToken, IProgress<double> progress) + { + var itemIds = _libraryManager.GetItemIds(new InternalItemsQuery + { + HasDeadParentId = true + }); + + var numComplete = 0; + var numItems = itemIds.Count; + + _logger.Debug("Cleaning {0} items with dead parent links", numItems); + + foreach (var itemId in itemIds) + { + cancellationToken.ThrowIfCancellationRequested(); + + var item = _libraryManager.GetItemById(itemId); + + if (item != null) + { + _logger.Debug("Cleaning item {0} type: {1} path: {2}", item.Name, item.GetType().Name, item.Path ?? string.Empty); + + await _libraryManager.DeleteItem(item, new DeleteOptions + { + DeleteFileLocation = false + }); + } + + numComplete++; + double percent = numComplete; + percent /= numItems; + progress.Report(percent * 100); + } + + progress.Report(100); + } + + public IEnumerable<ITaskTrigger> GetDefaultTriggers() + { + return new ITaskTrigger[] + { + new IntervalTrigger{ Interval = TimeSpan.FromDays(1)} + }; + } + } +} diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 852fbd76c..00ebf7ea6 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -1,5 +1,7 @@ using MediaBrowser.Common.Configuration; using MediaBrowser.Controller.Entities; +using MediaBrowser.Controller.Entities.Audio; +using MediaBrowser.Controller.Entities.Movies; using MediaBrowser.Controller.Entities.TV; using MediaBrowser.Controller.LiveTv; using MediaBrowser.Controller.Persistence; @@ -69,6 +71,9 @@ namespace MediaBrowser.Server.Implementations.Persistence private IDbCommand _deletePeopleCommand; private IDbCommand _savePersonCommand; + + private const int LatestSchemaVersion = 6; + /// <summary> /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. /// </summary> @@ -155,6 +160,21 @@ namespace MediaBrowser.Server.Implementations.Persistence _connection.AddColumn(_logger, "TypedBaseItems", "PremiereDate", "DATETIME"); _connection.AddColumn(_logger, "TypedBaseItems", "ProductionYear", "INT"); _connection.AddColumn(_logger, "TypedBaseItems", "ParentId", "GUID"); + _connection.AddColumn(_logger, "TypedBaseItems", "Genres", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "ParentalRatingValue", "INT"); + _connection.AddColumn(_logger, "TypedBaseItems", "SchemaVersion", "INT"); + _connection.AddColumn(_logger, "TypedBaseItems", "SortName", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "RunTimeTicks", "BIGINT"); + + _connection.AddColumn(_logger, "TypedBaseItems", "OfficialRatingDescription", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "HomePageUrl", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "VoteCount", "INT"); + _connection.AddColumn(_logger, "TypedBaseItems", "DisplayMediaType", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "DateCreated", "DATETIME"); + _connection.AddColumn(_logger, "TypedBaseItems", "DateModified", "DATETIME"); + + _connection.AddColumn(_logger, "TypedBaseItems", "ForcedSortName", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "IsOffline", "BIT"); PrepareStatements(); @@ -167,6 +187,13 @@ namespace MediaBrowser.Server.Implementations.Persistence /// </summary> private readonly SemaphoreSlim _writeLock = new SemaphoreSlim(1, 1); + private string[] _retriveItemColumns = + { + "type", + "data", + "IsOffline" + }; + /// <summary> /// Prepares the statements. /// </summary> @@ -195,14 +222,35 @@ namespace MediaBrowser.Server.Implementations.Persistence "ParentIndexNumber", "PremiereDate", "ProductionYear", - "ParentId" + "ParentId", + "Genres", + "ParentalRatingValue", + "SchemaVersion", + "SortName", + "RunTimeTicks", + "OfficialRatingDescription", + "HomePageUrl", + "VoteCount", + "DisplayMediaType", + "DateCreated", + "DateModified", + "ForcedSortName", + "IsOffline" }; _saveItemCommand = _connection.CreateCommand(); - _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)"; + _saveItemCommand.CommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values ("; + for (var i = 1; i <= saveColumns.Count; i++) { + if (i > 1) + { + _saveItemCommand.CommandText += ","; + } + _saveItemCommand.CommandText += "@" + i.ToString(CultureInfo.InvariantCulture); + _saveItemCommand.Parameters.Add(_saveItemCommand, "@" + i.ToString(CultureInfo.InvariantCulture)); } + _saveItemCommand.CommandText += ")"; _deleteChildrenCommand = _connection.CreateCommand(); _deleteChildrenCommand.CommandText = "delete from ChildrenIds where ParentId=@ParentId"; @@ -341,6 +389,23 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = item.ParentId; } + _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Genres.ToArray()); + _saveItemCommand.GetParameter(index++).Value = item.GetParentalRatingValue(); + + _saveItemCommand.GetParameter(index++).Value = LatestSchemaVersion; + _saveItemCommand.GetParameter(index++).Value = item.SortName; + _saveItemCommand.GetParameter(index++).Value = item.RunTimeTicks; + + _saveItemCommand.GetParameter(index++).Value = item.OfficialRatingDescription; + _saveItemCommand.GetParameter(index++).Value = item.HomePageUrl; + _saveItemCommand.GetParameter(index++).Value = item.VoteCount; + _saveItemCommand.GetParameter(index++).Value = item.DisplayMediaType; + _saveItemCommand.GetParameter(index++).Value = item.DateCreated; + _saveItemCommand.GetParameter(index++).Value = item.DateModified; + + _saveItemCommand.GetParameter(index++).Value = item.ForcedSortName; + _saveItemCommand.GetParameter(index++).Value = item.IsOffline; + _saveItemCommand.Transaction = transaction; _saveItemCommand.ExecuteNonQuery(); @@ -397,7 +462,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select type,data from TypedBaseItems where guid = @guid"; + cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid"; cmd.Parameters.Add(cmd, "@guid", DbType.Guid).Value = id; using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) @@ -424,11 +489,18 @@ namespace MediaBrowser.Server.Implementations.Persistence return null; } + BaseItem item; + using (var stream = reader.GetMemoryStream(1)) { try { - return _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem; + item = _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem; + + if (item == null) + { + return null; + } } catch (SerializationException ex) { @@ -436,6 +508,13 @@ namespace MediaBrowser.Server.Implementations.Persistence return null; } } + + if (!reader.IsDBNull(2)) + { + item.IsOffline = reader.GetBoolean(2); + } + + return item; } /// <summary> @@ -627,7 +706,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select type,data from TypedBaseItems where guid in (select ItemId from ChildrenIds where ParentId = @ParentId)"; + cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid in (select ItemId from ChildrenIds where ParentId = @ParentId)"; cmd.Parameters.Add(cmd, "@ParentId", DbType.Guid).Value = parentId; @@ -657,7 +736,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select type,data from TypedBaseItems where type = @type"; + cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where type = @type"; cmd.Parameters.Add(cmd, "@type", DbType.String).Value = type.FullName; @@ -687,7 +766,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select type,data from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems"; var whereClauses = GetWhereClauses(query, cmd, false); @@ -703,6 +782,8 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; + cmd.CommandText += GetOrderByText(query); + if (query.Limit.HasValue) { cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); @@ -710,6 +791,8 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging; + _logger.Debug(cmd.CommandText); + var list = new List<BaseItem>(); var count = 0; @@ -738,6 +821,23 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + private string GetOrderByText(InternalItemsQuery query) + { + if (query.SortBy == null || query.SortBy.Length == 0) + { + return string.Empty; + } + + var sortOrder = query.SortOrder == SortOrder.Descending ? "DESC" : "ASC"; + + return " ORDER BY " + string.Join(",", query.SortBy.Select(i => MapOrderByField(i) + " " + sortOrder).ToArray()); + } + + private string MapOrderByField(string name) + { + return name; + } + public List<Guid> GetItemIdsList(InternalItemsQuery query) { if (query == null) @@ -759,6 +859,8 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; + cmd.CommandText += GetOrderByText(query); + if (query.Limit.HasValue) { cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); @@ -807,6 +909,8 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; + cmd.CommandText += GetOrderByText(query); + if (query.Limit.HasValue) { cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); @@ -844,6 +948,18 @@ namespace MediaBrowser.Server.Implementations.Persistence { var whereClauses = new List<string>(); + if (query.IsCurrentSchema.HasValue) + { + if (query.IsCurrentSchema.Value) + { + whereClauses.Add("(SchemaVersion not null AND SchemaVersion=@SchemaVersion)"); + } + else + { + whereClauses.Add("(SchemaVersion is null or SchemaVersion<>@SchemaVersion)"); + } + cmd.Parameters.Add(cmd, "@SchemaVersion", DbType.Int32).Value = LatestSchemaVersion; + } if (query.IsMovie.HasValue) { whereClauses.Add("IsMovie=@IsMovie"); @@ -861,17 +977,29 @@ namespace MediaBrowser.Server.Implementations.Persistence } 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) + else if (includeTypes.Length > 1) { var inClause = string.Join(",", includeTypes.Select(i => "'" + i + "'").ToArray()); whereClauses.Add(string.Format("type in ({0})", inClause)); } + + var excludeTypes = query.ExcludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray(); + if (excludeTypes.Length == 1) + { + whereClauses.Add("type<>@type"); + cmd.Parameters.Add(cmd, "@type", DbType.String).Value = excludeTypes[0]; + } + else if (excludeTypes.Length > 1) + { + var inClause = string.Join(",", excludeTypes.Select(i => "'" + i + "'").ToArray()); + whereClauses.Add(string.Format("type not in ({0})", inClause)); + } + if (query.ChannelIds.Length == 1) { whereClauses.Add("ChannelId=@ChannelId"); @@ -930,6 +1058,52 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.Parameters.Add(cmd, "@PersonName", DbType.String).Value = query.Person; } + if (!string.IsNullOrWhiteSpace(query.NameContains)) + { + whereClauses.Add("Name like @NameContains"); + cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%" + query.NameContains + "%"; + } + + if (query.Genres.Length > 0) + { + var genres = new List<string>(); + var index = 0; + foreach (var genre in query.Genres) + { + genres.Add("Genres like @Genres" + index); + cmd.Parameters.Add(cmd, "@Genres" + index, DbType.String).Value = "%" + genre + "%"; + index++; + } + var genreCaluse = "(" + string.Join(" OR ", genres.ToArray()) + ")"; + whereClauses.Add(genreCaluse); + } + + if (query.MaxParentalRating.HasValue) + { + whereClauses.Add("(ParentalRatingValue is NULL OR ParentalRatingValue<=@MaxParentalRating)"); + cmd.Parameters.Add(cmd, "@MaxParentalRating", DbType.Int32).Value = query.MaxParentalRating.Value; + } + + if (query.HasParentalRating.HasValue) + { + if (query.HasParentalRating.Value) + { + whereClauses.Add("ParentalRatingValue NOT NULL"); + } + else + { + whereClauses.Add("ParentalRatingValue IS NULL"); + } + } + + if (query.HasDeadParentId.HasValue) + { + if (query.HasDeadParentId.Value) + { + whereClauses.Add("ParentId NOT NULL AND ParentId NOT IN (select guid from TypedBaseItems)"); + } + } + if (addPaging) { if (query.StartIndex.HasValue && query.StartIndex.Value > 0) @@ -938,7 +1112,9 @@ namespace MediaBrowser.Server.Implementations.Persistence 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})", + var orderBy = GetOrderByText(query); + + whereClauses.Add(string.Format("guid NOT IN (SELECT guid FROM TypedBaseItems {0}" + orderBy + " LIMIT {1})", pagingWhereText, query.StartIndex.Value.ToString(CultureInfo.InvariantCulture))); } @@ -947,16 +1123,60 @@ namespace MediaBrowser.Server.Implementations.Persistence return whereClauses; } + private static readonly Type[] KnownTypes = + { + typeof(LiveTvProgram), + typeof(LiveTvChannel), + typeof(LiveTvVideoRecording), + typeof(LiveTvAudioRecording), + typeof(Series), + typeof(LiveTvAudioRecording), + typeof(LiveTvVideoRecording), + typeof(Audio), + typeof(MusicAlbum), + typeof(MusicArtist), + typeof(MusicGenre), + typeof(MusicVideo), + typeof(Movie), + typeof(BoxSet), + typeof(Episode), + typeof(Season), + typeof(Series), + typeof(Book), + typeof(CollectionFolder), + typeof(Folder), + typeof(Game), + typeof(GameGenre), + typeof(GameSystem), + typeof(Genre), + typeof(Person), + typeof(Photo), + typeof(PhotoAlbum), + typeof(Studio), + typeof(UserRootFolder), + typeof(UserView), + typeof(Video), + typeof(Year) + }; + + private static Dictionary<string, string[]> GetTypeMapDictionary() + { + var dict = new Dictionary<string, string[]>(); + + foreach (var t in KnownTypes) + { + dict[t.Name] = new[] { t.FullName }; + } + + dict["Recording"] = new[] { typeof(LiveTvAudioRecording).FullName, typeof(LiveTvVideoRecording).FullName }; + dict["Program"] = new[] { typeof(LiveTvProgram).FullName }; + dict["TvChannel"] = new[] { typeof(LiveTvChannel).FullName }; + + return dict; + } + // 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}} - }; + readonly Dictionary<string, string[]> _types = GetTypeMapDictionary(); private IEnumerable<string> MapIncludeItemTypes(string value) { @@ -969,31 +1189,6 @@ namespace MediaBrowser.Server.Implementations.Persistence return new[] { value }; } - public IEnumerable<Guid> GetItemIdsOfType(Type type) - { - if (type == null) - { - throw new ArgumentNullException("type"); - } - - CheckDisposed(); - - using (var cmd = _connection.CreateCommand()) - { - cmd.CommandText = "select guid from TypedBaseItems where type = @type"; - - cmd.Parameters.Add(cmd, "@type", DbType.String).Value = type.FullName; - - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) - { - while (reader.Read()) - { - yield return reader.GetGuid(0); - } - } - } - } - public async Task DeleteItem(Guid id, CancellationToken cancellationToken) { if (id == Guid.Empty) @@ -1260,7 +1455,7 @@ namespace MediaBrowser.Server.Implementations.Persistence if (!string.IsNullOrWhiteSpace(query.NameContains)) { whereClauses.Add("Name like @NameContains"); - cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%"+query.NameContains+"%"; + cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%" + query.NameContains + "%"; } return whereClauses; diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteMediaStreamsRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteMediaStreamsRepository.cs index 994397624..9e97858d0 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteMediaStreamsRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteMediaStreamsRepository.cs @@ -1,4 +1,5 @@ -using MediaBrowser.Controller.Persistence; +using System.Globalization; +using MediaBrowser.Controller.Persistence; using MediaBrowser.Model.Entities; using MediaBrowser.Model.Logging; using System; @@ -38,7 +39,7 @@ namespace MediaBrowser.Server.Implementations.Persistence // Add PixelFormat column - createTableCommand += "(ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, IsCabac BIT NULL, PRIMARY KEY (ItemId, StreamIndex))"; + createTableCommand += "(ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, IsCabac BIT NULL, KeyFrames TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))"; string[] queries = { @@ -58,6 +59,7 @@ namespace MediaBrowser.Server.Implementations.Persistence AddBitDepthCommand(); AddIsAnamorphicColumn(); AddIsCabacColumn(); + AddKeyFramesColumn(); AddRefFramesCommand(); PrepareStatements(); @@ -187,6 +189,37 @@ namespace MediaBrowser.Server.Implementations.Persistence _connection.RunQueries(new[] { builder.ToString() }, _logger); } + private void AddKeyFramesColumn() + { + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "PRAGMA table_info(mediastreams)"; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + if (!reader.IsDBNull(1)) + { + var name = reader.GetString(1); + + if (string.Equals(name, "KeyFrames", StringComparison.OrdinalIgnoreCase)) + { + return; + } + } + } + } + } + + var builder = new StringBuilder(); + + builder.AppendLine("alter table mediastreams"); + builder.AppendLine("add column KeyFrames TEXT NULL"); + + _connection.RunQueries(new[] { builder.ToString() }, _logger); + } + private void AddIsAnamorphicColumn() { using (var cmd = _connection.CreateCommand()) @@ -245,7 +278,8 @@ namespace MediaBrowser.Server.Implementations.Persistence "BitDepth", "IsAnamorphic", "RefFrames", - "IsCabac" + "IsCabac", + "KeyFrames" }; /// <summary> @@ -429,6 +463,15 @@ namespace MediaBrowser.Server.Implementations.Persistence item.IsCabac = reader.GetBoolean(25); } + if (!reader.IsDBNull(26)) + { + var frames = reader.GetString(26); + if (!string.IsNullOrWhiteSpace(frames)) + { + item.KeyFrames = frames.Split(',').Select(i => int.Parse(i, CultureInfo.InvariantCulture)).ToList(); + } + } + return item; } @@ -498,6 +541,15 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveStreamCommand.GetParameter(index++).Value = stream.RefFrames; _saveStreamCommand.GetParameter(index++).Value = stream.IsCabac; + if (stream.KeyFrames == null || stream.KeyFrames.Count == 0) + { + _saveStreamCommand.GetParameter(index++).Value = null; + } + else + { + _saveStreamCommand.GetParameter(index++).Value = string.Join(",", stream.KeyFrames.Select(i => i.ToString(CultureInfo.InvariantCulture)).ToArray()); + } + _saveStreamCommand.Transaction = transaction; _saveStreamCommand.ExecuteNonQuery(); } |