From 5f0d8000a5ec26fd66c5f188f3bb517bb139b74b Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Thu, 5 Dec 2013 22:39:44 -0500 Subject: moved media streams to the database --- .../BdInfo/BdInfoExaminer.cs | 4 +- .../Dto/DtoService.cs | 6 +- .../MediaBrowser.Server.Implementations.csproj | 1 + .../Persistence/SqliteItemRepository.cs | 24 ++ .../Persistence/SqliteMediaStreamsRepository.cs | 377 +++++++++++++++++++++ .../Persistence/SqliteUserDataRepository.cs | 18 +- .../ScheduledTasks/ChapterImagesTask.cs | 2 +- .../Sorting/VideoBitRateComparer.cs | 12 +- 8 files changed, 416 insertions(+), 28 deletions(-) create mode 100644 MediaBrowser.Server.Implementations/Persistence/SqliteMediaStreamsRepository.cs (limited to 'MediaBrowser.Server.Implementations') diff --git a/MediaBrowser.Server.Implementations/BdInfo/BdInfoExaminer.cs b/MediaBrowser.Server.Implementations/BdInfo/BdInfoExaminer.cs index 06768f353b..18f1b92fe7 100644 --- a/MediaBrowser.Server.Implementations/BdInfo/BdInfoExaminer.cs +++ b/MediaBrowser.Server.Implementations/BdInfo/BdInfoExaminer.cs @@ -102,7 +102,7 @@ namespace MediaBrowser.Server.Implementations.BdInfo Width = videoStream.Width, Height = videoStream.Height, Codec = videoStream.CodecShortName, - ScanType = videoStream.IsInterlaced ? "interlaced" : "progressive", + IsInterlaced = videoStream.IsInterlaced, Type = MediaStreamType.Video, Index = streams.Count }; @@ -146,7 +146,7 @@ namespace MediaBrowser.Server.Implementations.BdInfo { stream.Channels = audioStream.ChannelCount + 1; } - + streams.Add(stream); } diff --git a/MediaBrowser.Server.Implementations/Dto/DtoService.cs b/MediaBrowser.Server.Implementations/Dto/DtoService.cs index bb9ee7e148..ba9dd170df 100644 --- a/MediaBrowser.Server.Implementations/Dto/DtoService.cs +++ b/MediaBrowser.Server.Implementations/Dto/DtoService.cs @@ -1034,7 +1034,11 @@ namespace MediaBrowser.Server.Implementations.Dto if (iHasMediaStreams != null) { - dto.MediaStreams = iHasMediaStreams.MediaStreams; + dto.MediaStreams = _itemRepo.GetMediaStreams(new MediaStreamQuery + { + ItemId = item.Id + + }).ToList(); } } diff --git a/MediaBrowser.Server.Implementations/MediaBrowser.Server.Implementations.csproj b/MediaBrowser.Server.Implementations/MediaBrowser.Server.Implementations.csproj index 54fbb502ec..a4eec43f7e 100644 --- a/MediaBrowser.Server.Implementations/MediaBrowser.Server.Implementations.csproj +++ b/MediaBrowser.Server.Implementations/MediaBrowser.Server.Implementations.csproj @@ -168,6 +168,7 @@ + diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index fc2a6de248..799b74fe28 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -56,6 +56,7 @@ namespace MediaBrowser.Server.Implementations.Persistence private readonly string _criticReviewsPath; private SqliteChapterRepository _chapterRepository; + private SqliteMediaStreamsRepository _mediaStreamsRepository; private IDbCommand _deleteChildrenCommand; private IDbCommand _saveChildrenCommand; @@ -94,6 +95,12 @@ namespace MediaBrowser.Server.Implementations.Persistence var chapterConnection = SqliteExtensions.ConnectToDb(chapterDbFile, _logger).Result; _chapterRepository = new SqliteChapterRepository(chapterConnection, logManager); + + var mediaStreamsDbFile = Path.Combine(_appPaths.DataPath, "mediainfo.db"); + + var mediaStreamsConnection = SqliteExtensions.ConnectToDb(mediaStreamsDbFile, _logger).Result; + + _mediaStreamsRepository = new SqliteMediaStreamsRepository(mediaStreamsConnection, logManager); } /// @@ -122,6 +129,7 @@ namespace MediaBrowser.Server.Implementations.Persistence PrepareStatements(); + _mediaStreamsRepository.Initialize(); _chapterRepository.Initialize(); } @@ -413,6 +421,12 @@ namespace MediaBrowser.Server.Implementations.Persistence _chapterRepository.Dispose(); _chapterRepository = null; } + + if (_mediaStreamsRepository != null) + { + _mediaStreamsRepository.Dispose(); + _mediaStreamsRepository = null; + } } } @@ -511,5 +525,15 @@ namespace MediaBrowser.Server.Implementations.Persistence _writeLock.Release(); } } + + public IEnumerable GetMediaStreams(MediaStreamQuery query) + { + return _mediaStreamsRepository.GetMediaStreams(query); + } + + public Task SaveMediaStreams(Guid id, IEnumerable streams, CancellationToken cancellationToken) + { + return _mediaStreamsRepository.SaveMediaStreams(id, streams, cancellationToken); + } } } \ No newline at end of file diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteMediaStreamsRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteMediaStreamsRepository.cs new file mode 100644 index 0000000000..ba189396a0 --- /dev/null +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteMediaStreamsRepository.cs @@ -0,0 +1,377 @@ +using MediaBrowser.Controller.Persistence; +using MediaBrowser.Model.Entities; +using MediaBrowser.Model.Logging; +using System; +using System.Collections.Generic; +using System.Data; +using System.Linq; +using System.Threading; +using System.Threading.Tasks; + +namespace MediaBrowser.Server.Implementations.Persistence +{ + class SqliteMediaStreamsRepository + { + private IDbConnection _connection; + + private readonly ILogger _logger; + + private IDbCommand _deleteStreamsCommand; + private IDbCommand _saveStreamCommand; + + public SqliteMediaStreamsRepository(IDbConnection connection, ILogManager logManager) + { + _connection = connection; + + _logger = logManager.GetLogger(GetType().Name); + } + + /// + /// Opens the connection to the database + /// + /// Task. + public void Initialize() + { + var createTableCommand + = "create table if not exists mediastreams "; + + 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, PRIMARY KEY (ItemId, StreamIndex))"; + + string[] queries = { + + createTableCommand, + "create index if not exists idx_mediastreams on mediastreams(ItemId, StreamIndex)", + + //pragmas + "pragma temp_store = memory" + }; + + _connection.RunQueries(queries, _logger); + + PrepareStatements(); + } + + private readonly string[] _saveColumns = + { + "ItemId", + "StreamIndex", + "StreamType", + "Codec", + "Language", + "ChannelLayout", + "Profile", + "AspectRatio", + "Path", + "IsInterlaced", + "BitRate", + "Channels", + "SampleRate", + "IsDefault", + "IsForced", + "IsExternal", + "Height", + "Width", + "AverageFrameRate", + "RealFrameRate", + "Level" + }; + + /// + /// The _write lock + /// + private readonly SemaphoreSlim _writeLock = new SemaphoreSlim(1, 1); + + /// + /// Prepares the statements. + /// + private void PrepareStatements() + { + _deleteStreamsCommand = _connection.CreateCommand(); + _deleteStreamsCommand.CommandText = "delete from mediastreams where ItemId=@ItemId"; + _deleteStreamsCommand.Parameters.Add(_deleteStreamsCommand, "@ItemId"); + + _saveStreamCommand = _connection.CreateCommand(); + + _saveStreamCommand.CommandText = string.Format("replace into mediastreams ({0}) values ({1})", + string.Join(",", _saveColumns), + string.Join(",", _saveColumns.Select(i => "@" + i).ToArray())); + + foreach (var col in _saveColumns) + { + _saveStreamCommand.Parameters.Add(_saveStreamCommand, "@" + col); + } + } + + public IEnumerable GetMediaStreams(MediaStreamQuery query) + { + if (query == null) + { + throw new ArgumentNullException("query"); + } + + using (var cmd = _connection.CreateCommand()) + { + var cmdText = "select " + string.Join(",", _saveColumns) + " from mediastreams where"; + + cmdText += " ItemId=@ItemId"; + cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = query.ItemId; + + if (query.Type.HasValue) + { + cmdText += " AND StreamType=@StreamType"; + cmd.Parameters.Add(cmd, "@StreamType", DbType.String).Value = query.Type.Value.ToString(); + } + + if (query.Index.HasValue) + { + cmdText += " AND StreamIndex=@StreamIndex"; + cmd.Parameters.Add(cmd, "@StreamIndex", DbType.Int32).Value = query.Index.Value; + } + + cmdText += " order by StreamIndex ASC"; + + cmd.CommandText = cmdText; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + yield return GetMediaStream(reader); + } + } + } + } + + /// + /// Gets the chapter. + /// + /// The reader. + /// ChapterInfo. + private MediaStream GetMediaStream(IDataReader reader) + { + var item = new MediaStream + { + Index = reader.GetInt32(1) + }; + + item.Type = (MediaStreamType)Enum.Parse(typeof(MediaStreamType), reader.GetString(2), true); + + if (!reader.IsDBNull(3)) + { + item.Codec = reader.GetString(3); + } + + if (!reader.IsDBNull(4)) + { + item.Language = reader.GetString(4); + } + + if (!reader.IsDBNull(5)) + { + item.ChannelLayout = reader.GetString(5); + } + + if (!reader.IsDBNull(6)) + { + item.Profile = reader.GetString(6); + } + + if (!reader.IsDBNull(7)) + { + item.AspectRatio = reader.GetString(7); + } + + if (!reader.IsDBNull(8)) + { + item.Path = reader.GetString(8); + } + + item.IsInterlaced = reader.GetBoolean(9); + + if (!reader.IsDBNull(10)) + { + item.BitRate = reader.GetInt32(10); + } + + if (!reader.IsDBNull(11)) + { + item.Channels = reader.GetInt32(11); + } + + if (!reader.IsDBNull(12)) + { + item.SampleRate = reader.GetInt32(12); + } + + item.IsDefault = reader.GetBoolean(13); + item.IsForced = reader.GetBoolean(14); + item.IsExternal = reader.GetBoolean(15); + + if (!reader.IsDBNull(16)) + { + item.Width = reader.GetInt32(16); + } + + if (!reader.IsDBNull(17)) + { + item.Height = reader.GetInt32(17); + } + + if (!reader.IsDBNull(18)) + { + item.AverageFrameRate = reader.GetFloat(18); + } + + if (!reader.IsDBNull(19)) + { + item.RealFrameRate = reader.GetFloat(19); + } + + if (!reader.IsDBNull(20)) + { + item.Level = reader.GetFloat(20); + } + + return item; + } + + public async Task SaveMediaStreams(Guid id, IEnumerable streams, CancellationToken cancellationToken) + { + if (id == Guid.Empty) + { + throw new ArgumentNullException("id"); + } + + if (streams == null) + { + throw new ArgumentNullException("streams"); + } + + cancellationToken.ThrowIfCancellationRequested(); + + await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); + + IDbTransaction transaction = null; + + try + { + transaction = _connection.BeginTransaction(); + + // First delete chapters + _deleteStreamsCommand.GetParameter(0).Value = id; + + _deleteStreamsCommand.Transaction = transaction; + + _deleteStreamsCommand.ExecuteNonQuery(); + + foreach (var stream in streams) + { + cancellationToken.ThrowIfCancellationRequested(); + + _saveStreamCommand.GetParameter(0).Value = id; + _saveStreamCommand.GetParameter(1).Value = stream.Index; + _saveStreamCommand.GetParameter(2).Value = stream.Type.ToString(); + _saveStreamCommand.GetParameter(3).Value = stream.Codec; + _saveStreamCommand.GetParameter(4).Value = stream.Language; + _saveStreamCommand.GetParameter(5).Value = stream.ChannelLayout; + _saveStreamCommand.GetParameter(6).Value = stream.Profile; + _saveStreamCommand.GetParameter(7).Value = stream.AspectRatio; + _saveStreamCommand.GetParameter(8).Value = stream.Path; + + _saveStreamCommand.GetParameter(9).Value = stream.IsInterlaced; + + _saveStreamCommand.GetParameter(10).Value = stream.BitRate; + _saveStreamCommand.GetParameter(11).Value = stream.Channels; + _saveStreamCommand.GetParameter(12).Value = stream.SampleRate; + + _saveStreamCommand.GetParameter(13).Value = stream.IsDefault; + _saveStreamCommand.GetParameter(14).Value = stream.IsForced; + _saveStreamCommand.GetParameter(15).Value = stream.IsExternal; + + _saveStreamCommand.GetParameter(16).Value = stream.Width; + _saveStreamCommand.GetParameter(17).Value = stream.Height; + _saveStreamCommand.GetParameter(18).Value = stream.AverageFrameRate; + _saveStreamCommand.GetParameter(19).Value = stream.RealFrameRate; + _saveStreamCommand.GetParameter(20).Value = stream.Level; + + _saveStreamCommand.Transaction = transaction; + _saveStreamCommand.ExecuteNonQuery(); + } + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + catch (Exception e) + { + _logger.ErrorException("Failed to save media streams:", e); + + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + + _writeLock.Release(); + } + } + + /// + /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources. + /// + public void Dispose() + { + Dispose(true); + GC.SuppressFinalize(this); + } + + private readonly object _disposeLock = new object(); + + /// + /// Releases unmanaged and - optionally - managed resources. + /// + /// true to release both managed and unmanaged resources; false to release only unmanaged resources. + protected virtual void Dispose(bool dispose) + { + if (dispose) + { + try + { + lock (_disposeLock) + { + if (_connection != null) + { + if (_connection.IsOpen()) + { + _connection.Close(); + } + + _connection.Dispose(); + _connection = null; + } + } + } + catch (Exception ex) + { + _logger.ErrorException("Error disposing database", ex); + } + } + } + } +} + diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs index a9d5d8746e..c174eb08e0 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs @@ -31,36 +31,26 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - private readonly IJsonSerializer _jsonSerializer; - /// /// The _app paths /// private readonly IApplicationPaths _appPaths; /// - /// Initializes a new instance of the class. + /// Initializes a new instance of the class. /// /// The app paths. - /// The json serializer. /// The log manager. - /// - /// jsonSerializer + /// jsonSerializer /// or - /// appPaths - /// - public SqliteUserDataRepository(IApplicationPaths appPaths, IJsonSerializer jsonSerializer, ILogManager logManager) + /// appPaths + public SqliteUserDataRepository(IApplicationPaths appPaths, ILogManager logManager) { - if (jsonSerializer == null) - { - throw new ArgumentNullException("jsonSerializer"); - } if (appPaths == null) { throw new ArgumentNullException("appPaths"); } - _jsonSerializer = jsonSerializer; _appPaths = appPaths; _logger = logManager.GetLogger(GetType().Name); } diff --git a/MediaBrowser.Server.Implementations/ScheduledTasks/ChapterImagesTask.cs b/MediaBrowser.Server.Implementations/ScheduledTasks/ChapterImagesTask.cs index c82899948d..9270b879aa 100644 --- a/MediaBrowser.Server.Implementations/ScheduledTasks/ChapterImagesTask.cs +++ b/MediaBrowser.Server.Implementations/ScheduledTasks/ChapterImagesTask.cs @@ -101,7 +101,7 @@ namespace MediaBrowser.Server.Implementations.ScheduledTasks // Limit to video files to reduce changes of ffmpeg crash dialog foreach (var item in newItems - .Where(i => i.LocationType == LocationType.FileSystem && i.VideoType == VideoType.VideoFile && string.IsNullOrEmpty(i.PrimaryImagePath) && i.MediaStreams.Any(m => m.Type == MediaStreamType.Video)) + .Where(i => i.LocationType == LocationType.FileSystem && i.VideoType == VideoType.VideoFile && string.IsNullOrEmpty(i.PrimaryImagePath) && i.DefaultVideoStreamIndex.HasValue) .Take(2)) { try diff --git a/MediaBrowser.Server.Implementations/Sorting/VideoBitRateComparer.cs b/MediaBrowser.Server.Implementations/Sorting/VideoBitRateComparer.cs index 469eb5d6ae..cbf6ebac66 100644 --- a/MediaBrowser.Server.Implementations/Sorting/VideoBitRateComparer.cs +++ b/MediaBrowser.Server.Implementations/Sorting/VideoBitRateComparer.cs @@ -1,8 +1,6 @@ using MediaBrowser.Controller.Entities; using MediaBrowser.Controller.Sorting; -using MediaBrowser.Model.Entities; using MediaBrowser.Model.Querying; -using System.Linq; namespace MediaBrowser.Server.Implementations.Sorting { @@ -21,17 +19,11 @@ namespace MediaBrowser.Server.Implementations.Sorting private int GetValue(BaseItem item) { - var video = item as IHasMediaStreams; + var video = item as Video; if (video != null) { - var videoStream = video.MediaStreams - .FirstOrDefault(i => i.Type == MediaStreamType.Video); - - if (videoStream != null) - { - return videoStream.BitRate ?? 0; - } + return video.VideoBitRate ?? 0; } return 0; -- cgit v1.2.3