diff options
Diffstat (limited to 'MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs')
| -rw-r--r-- | MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs | 906 |
1 files changed, 856 insertions, 50 deletions
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 00ebf7ea6..7fbd9ee89 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -18,6 +18,7 @@ using System.Linq; using System.Runtime.Serialization; using System.Threading; using System.Threading.Tasks; +using MediaBrowser.Controller.Channels; namespace MediaBrowser.Server.Implementations.Persistence { @@ -62,9 +63,6 @@ namespace MediaBrowser.Server.Implementations.Persistence private readonly string _criticReviewsPath; - private SqliteChapterRepository _chapterRepository; - private SqliteMediaStreamsRepository _mediaStreamsRepository; - private IDbCommand _deleteChildrenCommand; private IDbCommand _saveChildrenCommand; private IDbCommand _deleteItemCommand; @@ -72,7 +70,13 @@ namespace MediaBrowser.Server.Implementations.Persistence private IDbCommand _deletePeopleCommand; private IDbCommand _savePersonCommand; - private const int LatestSchemaVersion = 6; + private IDbCommand _deleteChaptersCommand; + private IDbCommand _saveChapterCommand; + + private IDbCommand _deleteStreamsCommand; + private IDbCommand _saveStreamCommand; + + private const int LatestSchemaVersion = 13; /// <summary> /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. @@ -102,16 +106,10 @@ namespace MediaBrowser.Server.Implementations.Persistence _criticReviewsPath = Path.Combine(_appPaths.DataPath, "critic-reviews"); _logger = logManager.GetLogger(GetType().Name); - - var chapterDbFile = Path.Combine(_appPaths.DataPath, "chapters.db"); - 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); } + private const string ChaptersTableName = "Chapters2"; + /// <summary> /// Opens the connection to the database /// </summary> @@ -122,6 +120,9 @@ namespace MediaBrowser.Server.Implementations.Persistence _connection = await SqliteExtensions.ConnectToDb(dbFile, _logger).ConfigureAwait(false); + var createMediaStreamsTableCommand + = "create table if not exists mediastreams (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 = { "create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB)", @@ -132,6 +133,12 @@ namespace MediaBrowser.Server.Implementations.Persistence "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)", + "create table if not exists "+ChaptersTableName+" (ItemId GUID, ChapterIndex INT, StartPositionTicks BIGINT, Name TEXT, ImagePath TEXT, PRIMARY KEY (ItemId, ChapterIndex))", + "create index if not exists idx_"+ChaptersTableName+" on "+ChaptersTableName+"(ItemId, ChapterIndex)", + + createMediaStreamsTableCommand, + "create index if not exists idx_mediastreams on mediastreams(ItemId, StreamIndex)", + //pragmas "pragma temp_store = memory", @@ -175,11 +182,81 @@ namespace MediaBrowser.Server.Implementations.Persistence _connection.AddColumn(_logger, "TypedBaseItems", "ForcedSortName", "Text"); _connection.AddColumn(_logger, "TypedBaseItems", "IsOffline", "BIT"); + _connection.AddColumn(_logger, "TypedBaseItems", "LocationType", "Text"); + + _connection.AddColumn(_logger, "TypedBaseItems", "IsSeries", "BIT"); + _connection.AddColumn(_logger, "TypedBaseItems", "IsLive", "BIT"); + _connection.AddColumn(_logger, "TypedBaseItems", "IsNews", "BIT"); + _connection.AddColumn(_logger, "TypedBaseItems", "IsPremiere", "BIT"); + + _connection.AddColumn(_logger, "TypedBaseItems", "EpisodeTitle", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "IsRepeat", "BIT"); + + _connection.AddColumn(_logger, "TypedBaseItems", "PreferredMetadataLanguage", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "PreferredMetadataCountryCode", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "IsHD", "BIT"); + _connection.AddColumn(_logger, "TypedBaseItems", "ExternalEtag", "Text"); + _connection.AddColumn(_logger, "TypedBaseItems", "DateLastRefreshed", "DATETIME"); PrepareStatements(); - _mediaStreamsRepository.Initialize(); - _chapterRepository.Initialize(); + new MediaStreamColumns(_connection, _logger).AddColumns(); + + var chapterDbFile = Path.Combine(_appPaths.DataPath, "chapters.db"); + if (File.Exists(chapterDbFile)) + { + MigrateChapters(chapterDbFile); + } + + var mediaStreamsDbFile = Path.Combine(_appPaths.DataPath, "mediainfo.db"); + if (File.Exists(mediaStreamsDbFile)) + { + MigrateMediaStreams(mediaStreamsDbFile); + } + } + + private void MigrateMediaStreams(string file) + { + var backupFile = file + ".bak"; + File.Copy(file, backupFile, true); + SqliteExtensions.Attach(_connection, backupFile, "MediaInfoOld"); + + var columns = string.Join(",", _mediaStreamSaveColumns); + + string[] queries = { + "REPLACE INTO mediastreams("+columns+") SELECT "+columns+" FROM MediaInfoOld.mediastreams;" + }; + + try + { + _connection.RunQueries(queries, _logger); + File.Delete(file); + } + catch (Exception ex) + { + _logger.ErrorException("Error migrating media info database", ex); + } + } + + private void MigrateChapters(string file) + { + var backupFile = file + ".bak"; + File.Copy(file, backupFile, true); + SqliteExtensions.Attach(_connection, backupFile, "ChaptersOld"); + + string[] queries = { + "REPLACE INTO "+ChaptersTableName+"(ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath) SELECT ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath FROM ChaptersOld.Chapters;" + }; + + try + { + _connection.RunQueries(queries, _logger); + File.Delete(file); + } + catch (Exception ex) + { + _logger.ErrorException("Error migrating chapter database", ex); + } } /// <summary> @@ -187,11 +264,63 @@ namespace MediaBrowser.Server.Implementations.Persistence /// </summary> private readonly SemaphoreSlim _writeLock = new SemaphoreSlim(1, 1); - private string[] _retriveItemColumns = + private readonly string[] _retriveItemColumns = { "type", "data", - "IsOffline" + "StartDate", + "EndDate", + "IsOffline", + "ChannelId", + "IsMovie", + "IsSports", + "IsKids", + "IsSeries", + "IsLive", + "IsNews", + "IsPremiere", + "EpisodeTitle", + "IsRepeat", + "CommunityRating", + "CustomRating", + "IndexNumber", + "IsLocked", + "PreferredMetadataLanguage", + "PreferredMetadataCountryCode", + "IsHD", + "ExternalEtag", + "DateLastRefreshed" + }; + + private readonly string[] _mediaStreamSaveColumns = + { + "ItemId", + "StreamIndex", + "StreamType", + "Codec", + "Language", + "ChannelLayout", + "Profile", + "AspectRatio", + "Path", + "IsInterlaced", + "BitRate", + "Channels", + "SampleRate", + "IsDefault", + "IsForced", + "IsExternal", + "Height", + "Width", + "AverageFrameRate", + "RealFrameRate", + "Level", + "PixelFormat", + "BitDepth", + "IsAnamorphic", + "RefFrames", + "IsCabac", + "KeyFrames" }; /// <summary> @@ -211,6 +340,12 @@ namespace MediaBrowser.Server.Implementations.Persistence "IsKids", "IsMovie", "IsSports", + "IsSeries", + "IsLive", + "IsNews", + "IsPremiere", + "EpisodeTitle", + "IsRepeat", "CommunityRating", "CustomRating", "IndexNumber", @@ -235,7 +370,13 @@ namespace MediaBrowser.Server.Implementations.Persistence "DateCreated", "DateModified", "ForcedSortName", - "IsOffline" + "IsOffline", + "LocationType", + "PreferredMetadataLanguage", + "PreferredMetadataCountryCode", + "IsHD", + "ExternalEtag", + "DateLastRefreshed" }; _saveItemCommand = _connection.CreateCommand(); _saveItemCommand.CommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values ("; @@ -265,6 +406,7 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveChildrenCommand.Parameters.Add(_saveChildrenCommand, "@ParentId"); _saveChildrenCommand.Parameters.Add(_saveChildrenCommand, "@ItemId"); + // People _deletePeopleCommand = _connection.CreateCommand(); _deletePeopleCommand.CommandText = "delete from People where ItemId=@Id"; _deletePeopleCommand.Parameters.Add(_deletePeopleCommand, "@Id"); @@ -277,6 +419,36 @@ namespace MediaBrowser.Server.Implementations.Persistence _savePersonCommand.Parameters.Add(_savePersonCommand, "@PersonType"); _savePersonCommand.Parameters.Add(_savePersonCommand, "@SortOrder"); _savePersonCommand.Parameters.Add(_savePersonCommand, "@ListOrder"); + + // Chapters + _deleteChaptersCommand = _connection.CreateCommand(); + _deleteChaptersCommand.CommandText = "delete from " + ChaptersTableName + " where ItemId=@ItemId"; + _deleteChaptersCommand.Parameters.Add(_deleteChaptersCommand, "@ItemId"); + + _saveChapterCommand = _connection.CreateCommand(); + _saveChapterCommand.CommandText = "replace into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath) values (@ItemId, @ChapterIndex, @StartPositionTicks, @Name, @ImagePath)"; + + _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ItemId"); + _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ChapterIndex"); + _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@StartPositionTicks"); + _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@Name"); + _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ImagePath"); + + // MediaStreams + _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(",", _mediaStreamSaveColumns), + string.Join(",", _mediaStreamSaveColumns.Select(i => "@" + i).ToArray())); + + foreach (var col in _mediaStreamSaveColumns) + { + _saveStreamCommand.Parameters.Add(_saveStreamCommand, "@" + col); + } } /// <summary> @@ -357,12 +529,24 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsKids; _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsMovie; _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsSports; + _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsSeries; + _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsLive; + _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsNews; + _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsPremiere; + _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.EpisodeTitle; + _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsRepeat; } else { _saveItemCommand.GetParameter(index++).Value = null; _saveItemCommand.GetParameter(index++).Value = null; _saveItemCommand.GetParameter(index++).Value = null; + _saveItemCommand.GetParameter(index++).Value = null; + _saveItemCommand.GetParameter(index++).Value = null; + _saveItemCommand.GetParameter(index++).Value = null; + _saveItemCommand.GetParameter(index++).Value = null; + _saveItemCommand.GetParameter(index++).Value = null; + _saveItemCommand.GetParameter(index++).Value = null; } _saveItemCommand.GetParameter(index++).Value = item.CommunityRating; @@ -405,6 +589,21 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = item.ForcedSortName; _saveItemCommand.GetParameter(index++).Value = item.IsOffline; + _saveItemCommand.GetParameter(index++).Value = item.LocationType.ToString(); + + _saveItemCommand.GetParameter(index++).Value = item.PreferredMetadataLanguage; + _saveItemCommand.GetParameter(index++).Value = item.PreferredMetadataCountryCode; + _saveItemCommand.GetParameter(index++).Value = item.IsHD; + _saveItemCommand.GetParameter(index++).Value = item.ExternalEtag; + + if (item.DateLastRefreshed == default(DateTime)) + { + _saveItemCommand.GetParameter(index++).Value = null; + } + else + { + _saveItemCommand.GetParameter(index++).Value = item.DateLastRefreshed; + } _saveItemCommand.Transaction = transaction; @@ -511,7 +710,120 @@ namespace MediaBrowser.Server.Implementations.Persistence if (!reader.IsDBNull(2)) { - item.IsOffline = reader.GetBoolean(2); + var hasStartDate = item as IHasStartDate; + if (hasStartDate != null) + { + hasStartDate.StartDate = reader.GetDateTime(2).ToUniversalTime(); + } + } + + if (!reader.IsDBNull(3)) + { + item.EndDate = reader.GetDateTime(3).ToUniversalTime(); + } + + if (!reader.IsDBNull(4)) + { + item.IsOffline = reader.GetBoolean(4); + } + + if (!reader.IsDBNull(5)) + { + item.ChannelId = reader.GetString(5); + } + + var hasProgramAttributes = item as IHasProgramAttributes; + if (hasProgramAttributes != null) + { + if (!reader.IsDBNull(6)) + { + hasProgramAttributes.IsMovie = reader.GetBoolean(6); + } + + if (!reader.IsDBNull(7)) + { + hasProgramAttributes.IsSports = reader.GetBoolean(7); + } + + if (!reader.IsDBNull(8)) + { + hasProgramAttributes.IsKids = reader.GetBoolean(8); + } + + if (!reader.IsDBNull(9)) + { + hasProgramAttributes.IsSeries = reader.GetBoolean(9); + } + + if (!reader.IsDBNull(10)) + { + hasProgramAttributes.IsLive = reader.GetBoolean(10); + } + + if (!reader.IsDBNull(11)) + { + hasProgramAttributes.IsNews = reader.GetBoolean(11); + } + + if (!reader.IsDBNull(12)) + { + hasProgramAttributes.IsPremiere = reader.GetBoolean(12); + } + + if (!reader.IsDBNull(13)) + { + hasProgramAttributes.EpisodeTitle = reader.GetString(13); + } + + if (!reader.IsDBNull(14)) + { + hasProgramAttributes.IsRepeat = reader.GetBoolean(14); + } + } + + if (!reader.IsDBNull(15)) + { + item.CommunityRating = reader.GetFloat(15); + } + + if (!reader.IsDBNull(16)) + { + item.CustomRating = reader.GetString(16); + } + + if (!reader.IsDBNull(17)) + { + item.IndexNumber = reader.GetInt32(17); + } + + if (!reader.IsDBNull(18)) + { + item.IsLocked = reader.GetBoolean(18); + } + + if (!reader.IsDBNull(19)) + { + item.PreferredMetadataLanguage = reader.GetString(19); + } + + if (!reader.IsDBNull(20)) + { + item.PreferredMetadataCountryCode = reader.GetString(20); + } + + if (!reader.IsDBNull(21)) + { + item.IsHD = reader.GetBoolean(21); + } + + if (!reader.IsDBNull(22)) + { + item.ExternalEtag = reader.GetString(22); + } + + if (!reader.IsDBNull(23)) + { + item.DateLastRefreshed = reader.GetDateTime(23).ToUniversalTime(); } return item; @@ -567,7 +879,25 @@ namespace MediaBrowser.Server.Implementations.Persistence public IEnumerable<ChapterInfo> GetChapters(Guid id) { CheckDisposed(); - return _chapterRepository.GetChapters(id); + if (id == Guid.Empty) + { + throw new ArgumentNullException("id"); + } + + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"; + + cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = id; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + yield return GetChapter(reader); + } + } + } } /// <summary> @@ -580,7 +910,52 @@ namespace MediaBrowser.Server.Implementations.Persistence public ChapterInfo GetChapter(Guid id, int index) { CheckDisposed(); - return _chapterRepository.GetChapter(id, index); + if (id == Guid.Empty) + { + throw new ArgumentNullException("id"); + } + + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex"; + + cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = id; + cmd.Parameters.Add(cmd, "@ChapterIndex", DbType.Int32).Value = index; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) + { + if (reader.Read()) + { + return GetChapter(reader); + } + } + return null; + } + } + + /// <summary> + /// Gets the chapter. + /// </summary> + /// <param name="reader">The reader.</param> + /// <returns>ChapterInfo.</returns> + private ChapterInfo GetChapter(IDataReader reader) + { + var chapter = new ChapterInfo + { + StartPositionTicks = reader.GetInt64(0) + }; + + if (!reader.IsDBNull(1)) + { + chapter.Name = reader.GetString(1); + } + + if (!reader.IsDBNull(2)) + { + chapter.ImagePath = reader.GetString(2); + } + + return chapter; } /// <summary> @@ -597,10 +972,87 @@ namespace MediaBrowser.Server.Implementations.Persistence /// or /// cancellationToken /// </exception> - public Task SaveChapters(Guid id, IEnumerable<ChapterInfo> chapters, CancellationToken cancellationToken) + public async Task SaveChapters(Guid id, IEnumerable<ChapterInfo> chapters, CancellationToken cancellationToken) { CheckDisposed(); - return _chapterRepository.SaveChapters(id, chapters, cancellationToken); + + if (id == Guid.Empty) + { + throw new ArgumentNullException("id"); + } + + if (chapters == null) + { + throw new ArgumentNullException("chapters"); + } + + cancellationToken.ThrowIfCancellationRequested(); + + await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); + + IDbTransaction transaction = null; + + try + { + transaction = _connection.BeginTransaction(); + + // First delete chapters + _deleteChaptersCommand.GetParameter(0).Value = id; + + _deleteChaptersCommand.Transaction = transaction; + + _deleteChaptersCommand.ExecuteNonQuery(); + + var index = 0; + + foreach (var chapter in chapters) + { + cancellationToken.ThrowIfCancellationRequested(); + + _saveChapterCommand.GetParameter(0).Value = id; + _saveChapterCommand.GetParameter(1).Value = index; + _saveChapterCommand.GetParameter(2).Value = chapter.StartPositionTicks; + _saveChapterCommand.GetParameter(3).Value = chapter.Name; + _saveChapterCommand.GetParameter(4).Value = chapter.ImagePath; + + _saveChapterCommand.Transaction = transaction; + + _saveChapterCommand.ExecuteNonQuery(); + + index++; + } + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + catch (Exception e) + { + _logger.ErrorException("Failed to save chapters:", e); + + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + + _writeLock.Release(); + } } /// <summary> @@ -649,18 +1101,6 @@ namespace MediaBrowser.Server.Implementations.Persistence _connection.Dispose(); _connection = null; } - - if (_chapterRepository != null) - { - _chapterRepository.Dispose(); - _chapterRepository = null; - } - - if (_mediaStreamsRepository != null) - { - _mediaStreamsRepository.Dispose(); - _mediaStreamsRepository = null; - } } } catch (Exception ex) @@ -882,6 +1322,75 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + public QueryResult<Tuple<Guid, string>> GetItemIdsWithPath(InternalItemsQuery query) + { + if (query == null) + { + throw new ArgumentNullException("query"); + } + + CheckDisposed(); + + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "select guid,path 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; + + cmd.CommandText += GetOrderByText(query); + + 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<Tuple<Guid, string>>(); + var count = 0; + + _logger.Debug(cmd.CommandText); + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) + { + while (reader.Read()) + { + var id = reader.GetGuid(0); + string path = null; + + if (!reader.IsDBNull(1)) + { + path = reader.GetString(1); + } + list.Add(new Tuple<Guid, string>(id, path)); + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } + } + + return new QueryResult<Tuple<Guid, string>>() + { + Items = list.ToArray(), + TotalRecordCount = count + }; + } + } + public QueryResult<Guid> GetItemIds(InternalItemsQuery query) { if (query == null) @@ -960,6 +1469,16 @@ namespace MediaBrowser.Server.Implementations.Persistence } cmd.Parameters.Add(cmd, "@SchemaVersion", DbType.Int32).Value = LatestSchemaVersion; } + if (query.IsOffline.HasValue) + { + whereClauses.Add("IsOffline=@IsOffline"); + cmd.Parameters.Add(cmd, "@IsOffline", DbType.Boolean).Value = query.IsOffline; + } + if (query.LocationType.HasValue) + { + whereClauses.Add("LocationType=@LocationType"); + cmd.Parameters.Add(cmd, "@LocationType", DbType.String).Value = query.LocationType.Value; + } if (query.IsMovie.HasValue) { whereClauses.Add("IsMovie=@IsMovie"); @@ -1011,6 +1530,12 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add(string.Format("ChannelId in ({0})", inClause)); } + if (query.ParentId.HasValue) + { + whereClauses.Add("ParentId=@ParentId"); + cmd.Parameters.Add(cmd, "@ParentId", DbType.Guid).Value = query.ParentId.Value; + } + if (query.MinEndDate.HasValue) { whereClauses.Add("EndDate>=@MinEndDate"); @@ -1130,8 +1655,6 @@ namespace MediaBrowser.Server.Implementations.Persistence typeof(LiveTvVideoRecording), typeof(LiveTvAudioRecording), typeof(Series), - typeof(LiveTvAudioRecording), - typeof(LiveTvVideoRecording), typeof(Audio), typeof(MusicAlbum), typeof(MusicArtist), @@ -1156,7 +1679,8 @@ namespace MediaBrowser.Server.Implementations.Persistence typeof(UserRootFolder), typeof(UserView), typeof(Video), - typeof(Year) + typeof(Year), + typeof(Channel) }; private static Dictionary<string, string[]> GetTypeMapDictionary() @@ -1216,11 +1740,21 @@ namespace MediaBrowser.Server.Implementations.Persistence _deletePeopleCommand.Transaction = transaction; _deletePeopleCommand.ExecuteNonQuery(); + // Delete chapters + _deleteChaptersCommand.GetParameter(0).Value = id; + _deleteChaptersCommand.Transaction = transaction; + _deleteChaptersCommand.ExecuteNonQuery(); + + // Delete media streams + _deleteStreamsCommand.GetParameter(0).Value = id; + _deleteStreamsCommand.Transaction = transaction; + _deleteStreamsCommand.ExecuteNonQuery(); + // Delete the item _deleteItemCommand.GetParameter(0).Value = id; _deleteItemCommand.Transaction = transaction; _deleteItemCommand.ExecuteNonQuery(); - + transaction.Commit(); } catch (OperationCanceledException) @@ -1327,18 +1861,6 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - public IEnumerable<MediaStream> GetMediaStreams(MediaStreamQuery query) - { - CheckDisposed(); - return _mediaStreamsRepository.GetMediaStreams(query); - } - - public Task SaveMediaStreams(Guid id, IEnumerable<MediaStream> streams, CancellationToken cancellationToken) - { - CheckDisposed(); - return _mediaStreamsRepository.SaveMediaStreams(id, streams, cancellationToken); - } - public List<string> GetPeopleNames(InternalPeopleQuery query) { if (query == null) @@ -1567,5 +2089,289 @@ namespace MediaBrowser.Server.Implementations.Persistence return item; } + + public IEnumerable<MediaStream> GetMediaStreams(MediaStreamQuery query) + { + CheckDisposed(); + + if (query == null) + { + throw new ArgumentNullException("query"); + } + + using (var cmd = _connection.CreateCommand()) + { + var cmdText = "select " + string.Join(",", _mediaStreamSaveColumns) + " 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); + } + } + } + } + + public async Task SaveMediaStreams(Guid id, IEnumerable<MediaStream> streams, CancellationToken cancellationToken) + { + CheckDisposed(); + + 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(); + + var index = 0; + + _saveStreamCommand.GetParameter(index++).Value = id; + _saveStreamCommand.GetParameter(index++).Value = stream.Index; + _saveStreamCommand.GetParameter(index++).Value = stream.Type.ToString(); + _saveStreamCommand.GetParameter(index++).Value = stream.Codec; + _saveStreamCommand.GetParameter(index++).Value = stream.Language; + _saveStreamCommand.GetParameter(index++).Value = stream.ChannelLayout; + _saveStreamCommand.GetParameter(index++).Value = stream.Profile; + _saveStreamCommand.GetParameter(index++).Value = stream.AspectRatio; + _saveStreamCommand.GetParameter(index++).Value = stream.Path; + + _saveStreamCommand.GetParameter(index++).Value = stream.IsInterlaced; + + _saveStreamCommand.GetParameter(index++).Value = stream.BitRate; + _saveStreamCommand.GetParameter(index++).Value = stream.Channels; + _saveStreamCommand.GetParameter(index++).Value = stream.SampleRate; + + _saveStreamCommand.GetParameter(index++).Value = stream.IsDefault; + _saveStreamCommand.GetParameter(index++).Value = stream.IsForced; + _saveStreamCommand.GetParameter(index++).Value = stream.IsExternal; + + _saveStreamCommand.GetParameter(index++).Value = stream.Width; + _saveStreamCommand.GetParameter(index++).Value = stream.Height; + _saveStreamCommand.GetParameter(index++).Value = stream.AverageFrameRate; + _saveStreamCommand.GetParameter(index++).Value = stream.RealFrameRate; + _saveStreamCommand.GetParameter(index++).Value = stream.Level; + _saveStreamCommand.GetParameter(index++).Value = stream.PixelFormat; + _saveStreamCommand.GetParameter(index++).Value = stream.BitDepth; + _saveStreamCommand.GetParameter(index++).Value = stream.IsAnamorphic; + _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(); + } + + 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(); + } + } + + /// <summary> + /// Gets the chapter. + /// </summary> + /// <param name="reader">The reader.</param> + /// <returns>ChapterInfo.</returns> + 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); + } + + if (!reader.IsDBNull(21)) + { + item.PixelFormat = reader.GetString(21); + } + + if (!reader.IsDBNull(22)) + { + item.BitDepth = reader.GetInt32(22); + } + + if (!reader.IsDBNull(23)) + { + item.IsAnamorphic = reader.GetBoolean(23); + } + + if (!reader.IsDBNull(24)) + { + item.RefFrames = reader.GetInt32(24); + } + + if (!reader.IsDBNull(25)) + { + 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; + } + } }
\ No newline at end of file |