diff options
Diffstat (limited to 'MediaBrowser.Server.Implementations/Persistence')
3 files changed, 357 insertions, 12 deletions
diff --git a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs new file mode 100644 index 000000000..0b45c468a --- /dev/null +++ b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs @@ -0,0 +1,164 @@ +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(); + + if (itemId == Guid.Empty) + { + // Somehow some invalid data got into the db. It probably predates the boundary checking + continue; + } + + 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 3ec45c4de..00ebf7ea6 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -71,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> @@ -159,6 +162,19 @@ namespace MediaBrowser.Server.Implementations.Persistence _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(); @@ -171,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> @@ -201,14 +224,33 @@ namespace MediaBrowser.Server.Implementations.Persistence "ProductionYear", "ParentId", "Genres", - "ParentalRatingValue" + "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, @23, @24)"; + _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"; @@ -350,6 +392,20 @@ namespace MediaBrowser.Server.Implementations.Persistence _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(); @@ -406,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)) @@ -433,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) { @@ -445,6 +508,13 @@ namespace MediaBrowser.Server.Implementations.Persistence return null; } } + + if (!reader.IsDBNull(2)) + { + item.IsOffline = reader.GetBoolean(2); + } + + return item; } /// <summary> @@ -636,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; @@ -666,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; @@ -696,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); @@ -712,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); @@ -719,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; @@ -747,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) @@ -768,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); @@ -816,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); @@ -853,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"); @@ -870,7 +977,6 @@ namespace MediaBrowser.Server.Implementations.Persistence } var includeTypes = query.IncludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray(); - if (includeTypes.Length == 1) { whereClauses.Add("type=@type"); @@ -881,6 +987,19 @@ namespace MediaBrowser.Server.Implementations.Persistence 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"); @@ -977,6 +1096,14 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + 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) @@ -985,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))); } 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(); } |