diff options
Diffstat (limited to 'MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs')
| -rw-r--r-- | MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs | 730 |
1 files changed, 605 insertions, 125 deletions
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 460a67ca7..6d067e345 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -22,6 +22,7 @@ using MediaBrowser.Common.Extensions; using MediaBrowser.Controller.Channels; using MediaBrowser.Controller.Configuration; using MediaBrowser.Controller.Playlists; +using MediaBrowser.Model.Dto; using MediaBrowser.Model.LiveTv; namespace MediaBrowser.Server.Implementations.Persistence @@ -94,13 +95,13 @@ namespace MediaBrowser.Server.Implementations.Persistence private IDbCommand _updateInheritedRatingCommand; private IDbCommand _updateInheritedTagsCommand; - public const int LatestSchemaVersion = 89; + public const int LatestSchemaVersion = 96; /// <summary> /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. /// </summary> - public SqliteItemRepository(IServerConfigurationManager config, IJsonSerializer jsonSerializer, ILogManager logManager) - : base(logManager) + public SqliteItemRepository(IServerConfigurationManager config, IJsonSerializer jsonSerializer, ILogManager logManager, IDbConnector connector) + : base(logManager, connector) { if (config == null) { @@ -115,61 +116,72 @@ namespace MediaBrowser.Server.Implementations.Persistence _jsonSerializer = jsonSerializer; _criticReviewsPath = Path.Combine(_config.ApplicationPaths.DataPath, "critic-reviews"); + DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); } private const string ChaptersTableName = "Chapters2"; + protected override async Task<IDbConnection> CreateConnection(bool isReadOnly = false) + { + var connection = await DbConnector.Connect(DbFilePath, false, false, 10000).ConfigureAwait(false); + + connection.RunQueries(new[] + { + "pragma temp_store = memory", + "pragma default_temp_store = memory", + "PRAGMA locking_mode=EXCLUSIVE" + + }, Logger); + + return connection; + } + /// <summary> /// Opens the connection to the database /// </summary> /// <returns>Task.</returns> - public async Task Initialize(IDbConnector dbConnector) + public async Task Initialize(SqliteUserDataRepository userDataRepo) { - var dbFile = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); - - _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); + _connection = await CreateConnection(false).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, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))"; + = "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, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, TimeBase TEXT NULL, CodecTimeBase TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))"; string[] queries = { "create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB, ParentId GUID, Path TEXT)", "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)", "create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)", - "create index if not exists idx_TypedBaseItems2 on TypedBaseItems(Type,Guid)", "create table if not exists AncestorIds (ItemId GUID, AncestorId GUID, AncestorIdText TEXT, PRIMARY KEY (ItemId, AncestorId))", "create index if not exists idx_AncestorIds1 on AncestorIds(AncestorId)", "create index if not exists idx_AncestorIds2 on AncestorIds(AncestorIdText)", - "create table if not exists UserDataKeys (ItemId GUID, UserDataKey TEXT, PRIMARY KEY (ItemId, UserDataKey))", - "create index if not exists idx_UserDataKeys1 on UserDataKeys(ItemId)", + "create table if not exists UserDataKeys (ItemId GUID, UserDataKey TEXT Priority INT, PRIMARY KEY (ItemId, UserDataKey))", - "create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT)", - "create index if not exists idx_ItemValues on ItemValues(ItemId)", + "create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT, CleanValue TEXT)", + //"create index if not exists idx_ItemValues on ItemValues(ItemId)", "create index if not exists idx_ItemValues2 on ItemValues(ItemId,Type)", "create table if not exists ProviderIds (ItemId GUID, Name TEXT, Value TEXT, PRIMARY KEY (ItemId, Name))", - "create index if not exists Idx_ProviderIds on ProviderIds(ItemId)", + // covering index + "create index if not exists Idx_ProviderIds1 on ProviderIds(ItemId,Name,Value)", "create table if not exists Images (ItemId GUID NOT NULL, Path TEXT NOT NULL, ImageType INT NOT NULL, DateModified DATETIME, IsPlaceHolder BIT NOT NULL, SortOrder INT)", "create index if not exists idx_Images on Images(ItemId)", "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)", - "create index if not exists idxPeopleItemId on People(ItemId)", + + "drop index if exists idxPeopleItemId", + "create index if not exists idxPeopleItemId1 on People(ItemId,ListOrder)", "create index if not exists idxPeopleName on People(Name)", "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+"1 on "+ChaptersTableName+"(ItemId)", createMediaStreamsTableCommand, - "create index if not exists idx_mediastreams1 on mediastreams(ItemId)", - //pragmas - "pragma temp_store = memory", + "create index if not exists idx_mediastreams1 on mediastreams(ItemId)", - "pragma shrink_memory" }; _connection.RunQueries(queries, Logger); @@ -254,16 +266,60 @@ namespace MediaBrowser.Server.Implementations.Persistence _connection.AddColumn(Logger, "TypedBaseItems", "Album", "Text"); _connection.AddColumn(Logger, "TypedBaseItems", "IsVirtualItem", "BIT"); _connection.AddColumn(Logger, "TypedBaseItems", "SeriesName", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "UserDataKey", "Text"); _connection.AddColumn(Logger, "UserDataKeys", "Priority", "INT"); + _connection.AddColumn(Logger, "ItemValues", "CleanValue", "Text"); string[] postQueries = - { + + { + // obsolete + "drop index if exists idx_TypedBaseItems", + "drop index if exists idx_mediastreams", + "drop index if exists idx_"+ChaptersTableName, + "drop index if exists idx_UserDataKeys1", + "drop index if exists idx_UserDataKeys2", + "drop index if exists idx_TypeTopParentId3", + "drop index if exists idx_TypeTopParentId2", + "drop index if exists idx_TypeTopParentId4", + "drop index if exists idx_Type", + "drop index if exists idx_TypeTopParentId", + "drop index if exists idx_GuidType", + "drop index if exists idx_TopParentId", + "drop index if exists idx_TypeTopParentId6", + "drop index if exists idx_ItemValues2", + "drop index if exists Idx_ProviderIds", + "create index if not exists idx_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)", - "create index if not exists idx_Type on TypedBaseItems(Type)", - "create index if not exists idx_TopParentId on TypedBaseItems(TopParentId)", - "create index if not exists idx_TypeTopParentId on TypedBaseItems(Type,TopParentId)" - }; + "create index if not exists idx_GuidTypeIsFolderIsVirtualItem on TypedBaseItems(Guid,Type,IsFolder,IsVirtualItem)", + //"create index if not exists idx_GuidMediaTypeIsFolderIsVirtualItem on TypedBaseItems(Guid,MediaType,IsFolder,IsVirtualItem)", + "create index if not exists idx_CleanNameType on TypedBaseItems(CleanName,Type)", + + // covering index + "create index if not exists idx_TopParentIdGuid on TypedBaseItems(TopParentId,Guid)", + + // live tv programs + "create index if not exists idx_TypeTopParentIdStartDate on TypedBaseItems(Type,TopParentId,StartDate)", + + // used by movie suggestions + "create index if not exists idx_TypeTopParentIdGroup on TypedBaseItems(Type,TopParentId,PresentationUniqueKey)", + "create index if not exists idx_TypeTopParentId5 on TypedBaseItems(TopParentId,IsVirtualItem)", + + // latest items + "create index if not exists idx_TypeTopParentId9 on TypedBaseItems(TopParentId,Type,IsVirtualItem,PresentationUniqueKey,DateCreated)", + "create index if not exists idx_TypeTopParentId8 on TypedBaseItems(TopParentId,IsFolder,IsVirtualItem,PresentationUniqueKey,DateCreated)", + + // resume + "create index if not exists idx_TypeTopParentId7 on TypedBaseItems(TopParentId,MediaType,IsVirtualItem,PresentationUniqueKey)", + + // items by name + "create index if not exists idx_ItemValues3 on ItemValues(ItemId,Type,CleanValue)", + "create index if not exists idx_ItemValues4 on ItemValues(ItemId,Type,Value,CleanValue)", + + // covering index + "create index if not exists idx_UserDataKeys3 on UserDataKeys(ItemId,Priority,UserDataKey)" + }; _connection.RunQueries(postQueries, Logger); @@ -272,6 +328,8 @@ namespace MediaBrowser.Server.Implementations.Persistence new MediaStreamColumns(_connection, Logger).AddColumns(); DataExtensions.Attach(_connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb"); + await userDataRepo.Initialize(_connection).ConfigureAwait(false); + //await Vacuum(_connection).ConfigureAwait(false); } private readonly string[] _retriveItemColumns = @@ -368,7 +426,9 @@ namespace MediaBrowser.Server.Implementations.Persistence "Comment", "NalLengthSize", "IsAvc", - "Title" + "Title", + "TimeBase", + "CodecTimeBase" }; /// <summary> @@ -451,7 +511,8 @@ namespace MediaBrowser.Server.Implementations.Persistence "DateLastMediaAdded", "Album", "IsVirtualItem", - "SeriesName" + "SeriesName", + "UserDataKey" }; _saveItemCommand = _connection.CreateCommand(); _saveItemCommand.CommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values ("; @@ -554,10 +615,11 @@ namespace MediaBrowser.Server.Implementations.Persistence _deleteItemValuesCommand.Parameters.Add(_deleteItemValuesCommand, "@Id"); _saveItemValuesCommand = _connection.CreateCommand(); - _saveItemValuesCommand.CommandText = "insert into ItemValues (ItemId, Type, Value) values (@ItemId, @Type, @Value)"; + _saveItemValuesCommand.CommandText = "insert into ItemValues (ItemId, Type, Value, CleanValue) values (@ItemId, @Type, @Value, @CleanValue)"; _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@ItemId"); _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Type"); _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Value"); + _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@CleanValue"); // provider ids _deleteProviderIdsCommand = _connection.CreateCommand(); @@ -867,15 +929,7 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = item.Album; - var season = item as Season; - if (season != null && season.IsVirtualItem.HasValue) - { - _saveItemCommand.GetParameter(index++).Value = season.IsVirtualItem.Value; - } - else - { - _saveItemCommand.GetParameter(index++).Value = null; - } + _saveItemCommand.GetParameter(index++).Value = item.IsVirtualItem || (!item.IsFolder && item.LocationType == LocationType.Virtual); var hasSeries = item as IHasSeries; if (hasSeries != null) @@ -887,6 +941,8 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = null; } + _saveItemCommand.GetParameter(index++).Value = item.GetUserDataKeys().FirstOrDefault(); + _saveItemCommand.Transaction = transaction; _saveItemCommand.ExecuteNonQuery(); @@ -899,7 +955,7 @@ namespace MediaBrowser.Server.Implementations.Persistence UpdateUserDataKeys(item.Id, item.GetUserDataKeys().Distinct(StringComparer.OrdinalIgnoreCase).ToList(), transaction); UpdateImages(item.Id, item.ImageInfos, transaction); UpdateProviderIds(item.Id, item.ProviderIds, transaction); - UpdateItemValues(item.Id, GetItemValues(item), transaction); + UpdateItemValues(item.Id, GetItemValuesToSave(item), transaction); } transaction.Commit(); @@ -1304,10 +1360,9 @@ namespace MediaBrowser.Server.Implementations.Persistence item.CriticRatingSummary = reader.GetString(57); } - var season = item as Season; - if (season != null && !reader.IsDBNull(58)) + if (!reader.IsDBNull(58)) { - season.IsVirtualItem = reader.GetBoolean(58); + item.IsVirtualItem = reader.GetBoolean(58); } return item; @@ -1459,7 +1514,7 @@ namespace MediaBrowser.Server.Implementations.Persistence /// or /// cancellationToken /// </exception> - public async Task SaveChapters(Guid id, IEnumerable<ChapterInfo> chapters, CancellationToken cancellationToken) + public async Task SaveChapters(Guid id, List<ChapterInfo> chapters, CancellationToken cancellationToken) { CheckDisposed(); @@ -1686,10 +1741,42 @@ namespace MediaBrowser.Server.Implementations.Persistence return string.Empty; } - return " left join UserDataDb.UserData on (select UserDataKey from UserDataKeys where ItemId=Guid order by Priority LIMIT 1)=UserDataDb.UserData.Key"; + if (_config.Configuration.SchemaVersion >= 96) + { + return " left join UserDataDb.UserData on UserDataKey=UserDataDb.UserData.Key And (UserId=@UserId)"; + } + + return " left join UserDataDb.UserData on (select UserDataKey from UserDataKeys where ItemId=Guid order by Priority LIMIT 1)=UserDataDb.UserData.Key And (UserId=@UserId)"; + } + + private string GetGroupBy(InternalItemsQuery query) + { + var groups = new List<string>(); + + if (!string.IsNullOrWhiteSpace(query.GroupByAncestorOfType)) + { + groups.Add("(Select PresentationUniqueKey from TypedBaseItems B where B.Type = 'MediaBrowser.Controller.Entities.TV.Series' And B.Guid in (Select AncestorId from AncestorIds where ItemId=A.Guid))"); + } + + if (EnableGroupByPresentationUniqueKey(query)) + { + groups.Add("PresentationUniqueKey"); + } + + if (groups.Count > 0) + { + return " Group by " + string.Join(",", groups.ToArray()); + } + + return string.Empty; } - public IEnumerable<BaseItem> GetItemList(InternalItemsQuery query) + private string GetFromText() + { + return " from TypedBaseItems A"; + } + + public List<BaseItem> GetItemList(InternalItemsQuery query) { if (query == null) { @@ -1700,9 +1787,11 @@ namespace MediaBrowser.Server.Implementations.Persistence var now = DateTime.UtcNow; + var list = new List<BaseItem>(); + using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + GetFromText(); cmd.CommandText += GetJoinUserDataText(query); if (EnableJoinUserData(query)) @@ -1718,22 +1807,22 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; - if (EnableGroupByPresentationUniqueKey(query)) - { - cmd.CommandText += " Group by PresentationUniqueKey"; - } + cmd.CommandText += GetGroupBy(query); cmd.CommandText += GetOrderByText(query); if (query.Limit.HasValue || query.StartIndex.HasValue) { - var limit = query.Limit ?? int.MaxValue; + var offset = query.StartIndex ?? 0; - cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); + if (query.Limit.HasValue || offset > 0) + { + cmd.CommandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + } - if (query.StartIndex.HasValue) + if (offset > 0) { - cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + cmd.CommandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); } } @@ -1746,11 +1835,13 @@ namespace MediaBrowser.Server.Implementations.Persistence var item = GetItem(reader); if (item != null) { - yield return item; + list.Add(item); } } } } + + return list; } private void LogQueryTime(string methodName, IDbCommand cmd, DateTime startDate) @@ -1760,7 +1851,7 @@ namespace MediaBrowser.Server.Implementations.Persistence var slowThreshold = 1000; #if DEBUG - slowThreshold = 100; + slowThreshold = 50; #endif if (elapsed >= slowThreshold) @@ -1788,11 +1879,21 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); + if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0)) + { + var list = GetItemList(query); + return new QueryResult<BaseItem> + { + Items = list.ToArray(), + TotalRecordCount = list.Count + }; + } + var now = DateTime.UtcNow; using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + GetFromText(); cmd.CommandText += GetJoinUserDataText(query); if (EnableJoinUserData(query)) @@ -1812,32 +1913,41 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; - if (EnableGroupByPresentationUniqueKey(query)) - { - cmd.CommandText += " Group by PresentationUniqueKey"; - } + cmd.CommandText += GetGroupBy(query); cmd.CommandText += GetOrderByText(query); if (query.Limit.HasValue || query.StartIndex.HasValue) { - var limit = query.Limit ?? int.MaxValue; + var offset = query.StartIndex ?? 0; - cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); + if (query.Limit.HasValue || offset > 0) + { + cmd.CommandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + } - if (query.StartIndex.HasValue) + if (offset > 0) { - cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + cmd.CommandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); } } + cmd.CommandText += ";"; + + var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; + + if (isReturningZeroItems) + { + cmd.CommandText = ""; + } + if (EnableGroupByPresentationUniqueKey(query)) { - cmd.CommandText += "; select count (distinct PresentationUniqueKey) from TypedBaseItems"; + cmd.CommandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); } else { - cmd.CommandText += "; select count (guid) from TypedBaseItems"; + cmd.CommandText += " select count (guid)" + GetFromText(); } cmd.CommandText += GetJoinUserDataText(query); @@ -1850,18 +1960,28 @@ namespace MediaBrowser.Server.Implementations.Persistence { LogQueryTime("GetItems", cmd, now); - while (reader.Read()) + if (isReturningZeroItems) { - var item = GetItem(reader); - if (item != null) + if (reader.Read()) { - list.Add(item); + count = reader.GetInt32(0); } } - - if (reader.NextResult() && reader.Read()) + else { - count = reader.GetInt32(0); + while (reader.Read()) + { + var item = GetItem(reader); + if (item != null) + { + list.Add(item); + } + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } } } @@ -1881,11 +2001,11 @@ namespace MediaBrowser.Server.Implementations.Persistence { if (query.User != null) { - query.SortBy = new[] { "SimilarityScore", "IsPlayed", "Random" }; + query.SortBy = new[] { "SimilarityScore", ItemSortBy.IsPlayed, ItemSortBy.Random }; } else { - query.SortBy = new[] { "SimilarityScore", "Random" }; + query.SortBy = new[] { "SimilarityScore", ItemSortBy.Random }; } query.SortOrder = SortOrder.Descending; } @@ -1900,7 +2020,7 @@ namespace MediaBrowser.Server.Implementations.Persistence return " ORDER BY " + string.Join(",", query.SortBy.Select(i => { - var columnMap = MapOrderByField(i); + var columnMap = MapOrderByField(i, query); var columnAscending = isAscending; if (columnMap.Item2) { @@ -1913,7 +2033,7 @@ namespace MediaBrowser.Server.Implementations.Persistence }).ToArray()); } - private Tuple<string, bool> MapOrderByField(string name) + private Tuple<string, bool> MapOrderByField(string name, InternalItemsQuery query) { if (string.Equals(name, ItemSortBy.AirTime, StringComparison.OrdinalIgnoreCase)) { @@ -1972,6 +2092,10 @@ namespace MediaBrowser.Server.Implementations.Persistence { return new Tuple<string, bool>("(select value from itemvalues where ItemId=Guid and Type=3 LIMIT 1)", false); } + if (string.Equals(name, ItemSortBy.SeriesDatePlayed, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText(query) + " where B.Guid in (Select ItemId from AncestorIds where AncestorId in (select guid from typedbaseitems c where C.Type = 'MediaBrowser.Controller.Entities.TV.Series' And C.Guid in (Select AncestorId from AncestorIds where ItemId=A.Guid))))", false); + } return new Tuple<string, bool>(name, false); } @@ -1989,7 +2113,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + GetFromText(); cmd.CommandText += GetJoinUserDataText(query); if (EnableJoinUserData(query)) @@ -2005,22 +2129,22 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; - if (EnableGroupByPresentationUniqueKey(query)) - { - cmd.CommandText += " Group by PresentationUniqueKey"; - } + cmd.CommandText += GetGroupBy(query); cmd.CommandText += GetOrderByText(query); if (query.Limit.HasValue || query.StartIndex.HasValue) { - var limit = query.Limit ?? int.MaxValue; + var offset = query.StartIndex ?? 0; - cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); + if (query.Limit.HasValue || offset > 0) + { + cmd.CommandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + } - if (query.StartIndex.HasValue) + if (offset > 0) { - cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + cmd.CommandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); } } @@ -2065,22 +2189,22 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; - if (EnableGroupByPresentationUniqueKey(query)) - { - cmd.CommandText += " Group by PresentationUniqueKey"; - } + cmd.CommandText += GetGroupBy(query); cmd.CommandText += GetOrderByText(query); if (query.Limit.HasValue || query.StartIndex.HasValue) { - var limit = query.Limit ?? int.MaxValue; + var offset = query.StartIndex ?? 0; - cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); + if (query.Limit.HasValue || offset > 0) + { + cmd.CommandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + } - if (query.StartIndex.HasValue) + if (offset > 0) { - cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + cmd.CommandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); } } @@ -2128,11 +2252,21 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); + if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0)) + { + var list = GetItemIdsList(query); + return new QueryResult<Guid> + { + Items = list.ToArray(), + TotalRecordCount = list.Count + }; + } + var now = DateTime.UtcNow; using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + GetFromText(); var whereClauses = GetWhereClauses(query, cmd); cmd.CommandText += GetJoinUserDataText(query); @@ -2148,32 +2282,32 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; - if (EnableGroupByPresentationUniqueKey(query)) - { - cmd.CommandText += " Group by PresentationUniqueKey"; - } + cmd.CommandText += GetGroupBy(query); cmd.CommandText += GetOrderByText(query); if (query.Limit.HasValue || query.StartIndex.HasValue) { - var limit = query.Limit ?? int.MaxValue; + var offset = query.StartIndex ?? 0; - cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture); + if (query.Limit.HasValue || offset > 0) + { + cmd.CommandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + } - if (query.StartIndex.HasValue) + if (offset > 0) { - cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture); + cmd.CommandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); } } if (EnableGroupByPresentationUniqueKey(query)) { - cmd.CommandText += "; select count (distinct PresentationUniqueKey) from TypedBaseItems"; + cmd.CommandText += "; select count (distinct PresentationUniqueKey)" + GetFromText(); } else { - cmd.CommandText += "; select count (guid) from TypedBaseItems"; + cmd.CommandText += "; select count (guid)" + GetFromText(); } cmd.CommandText += GetJoinUserDataText(query); @@ -2205,13 +2339,13 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - private List<string> GetWhereClauses(InternalItemsQuery query, IDbCommand cmd) + private List<string> GetWhereClauses(InternalItemsQuery query, IDbCommand cmd, string paramSuffix = "") { var whereClauses = new List<string>(); if (EnableJoinUserData(query)) { - whereClauses.Add("(UserId is null or UserId=@UserId)"); + //whereClauses.Add("(UserId is null or UserId=@UserId)"); } if (query.IsCurrentSchema.HasValue) { @@ -2281,8 +2415,8 @@ 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]; + whereClauses.Add("type=@type" + paramSuffix); + cmd.Parameters.Add(cmd, "@type" + paramSuffix, DbType.String).Value = includeTypes[0]; } else if (includeTypes.Length > 1) { @@ -2365,6 +2499,11 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add("ParentIndexNumber=@ParentIndexNumber"); cmd.Parameters.Add(cmd, "@ParentIndexNumber", DbType.Int32).Value = query.ParentIndexNumber.Value; } + if (query.ParentIndexNumberNotEquals.HasValue) + { + whereClauses.Add("(ParentIndexNumber<>@ParentIndexNumberNotEquals or ParentIndexNumber is null)"); + cmd.Parameters.Add(cmd, "@ParentIndexNumberNotEquals", DbType.Int32).Value = query.ParentIndexNumberNotEquals.Value; + } if (query.MinEndDate.HasValue) { whereClauses.Add("EndDate>=@MinEndDate"); @@ -2581,8 +2720,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var artist in query.ArtistNames) { - clauses.Add("@ArtistName" + index + " in (select value from itemvalues where ItemId=Guid and Type <= 1)"); - cmd.Parameters.Add(cmd, "@ArtistName" + index, DbType.String).Value = artist; + clauses.Add("@ArtistName" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type <= 1)"); + cmd.Parameters.Add(cmd, "@ArtistName" + index, DbType.String).Value = artist.RemoveDiacritics(); index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2595,8 +2734,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Genres) { - clauses.Add("@Genre" + index + " in (select value from itemvalues where ItemId=Guid and Type=2)"); - cmd.Parameters.Add(cmd, "@Genre" + index, DbType.String).Value = item; + clauses.Add("@Genre" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=2)"); + cmd.Parameters.Add(cmd, "@Genre" + index, DbType.String).Value = item.RemoveDiacritics(); index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2609,8 +2748,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Tags) { - clauses.Add("@Tag" + index + " in (select value from itemvalues where ItemId=Guid and Type=4)"); - cmd.Parameters.Add(cmd, "@Tag" + index, DbType.String).Value = item; + clauses.Add("@Tag" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=4)"); + cmd.Parameters.Add(cmd, "@Tag" + index, DbType.String).Value = item.RemoveDiacritics(); index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2623,8 +2762,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Studios) { - clauses.Add("@Studio" + index + " in (select value from itemvalues where ItemId=Guid and Type=3)"); - cmd.Parameters.Add(cmd, "@Studio" + index, DbType.String).Value = item; + clauses.Add("@Studio" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=3)"); + cmd.Parameters.Add(cmd, "@Studio" + index, DbType.String).Value = item.RemoveDiacritics(); index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2637,8 +2776,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Keywords) { - clauses.Add("@Keyword" + index + " in (select value from itemvalues where ItemId=Guid and Type=5)"); - cmd.Parameters.Add(cmd, "@Keyword" + index, DbType.String).Value = item; + clauses.Add("@Keyword" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=5)"); + cmd.Parameters.Add(cmd, "@Keyword" + index, DbType.String).Value = item.RemoveDiacritics(); index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2717,8 +2856,15 @@ namespace MediaBrowser.Server.Implementations.Persistence if (query.LocationTypes.Length == 1) { - whereClauses.Add("LocationType=@LocationType"); - cmd.Parameters.Add(cmd, "@LocationType", DbType.String).Value = query.LocationTypes[0].ToString(); + if (query.LocationTypes[0] == LocationType.Virtual && _config.Configuration.SchemaVersion >= 90) + { + query.IsVirtualItem = true; + } + else + { + whereClauses.Add("LocationType=@LocationType"); + cmd.Parameters.Add(cmd, "@LocationType", DbType.String).Value = query.LocationTypes[0].ToString(); + } } else if (query.LocationTypes.Length > 1) { @@ -2728,8 +2874,15 @@ namespace MediaBrowser.Server.Implementations.Persistence } if (query.ExcludeLocationTypes.Length == 1) { - whereClauses.Add("LocationType<>@ExcludeLocationTypes"); - cmd.Parameters.Add(cmd, "@ExcludeLocationTypes", DbType.String).Value = query.ExcludeLocationTypes[0].ToString(); + if (query.ExcludeLocationTypes[0] == LocationType.Virtual && _config.Configuration.SchemaVersion >= 90) + { + query.IsVirtualItem = false; + } + else + { + whereClauses.Add("LocationType<>@ExcludeLocationTypes"); + cmd.Parameters.Add(cmd, "@ExcludeLocationTypes", DbType.String).Value = query.ExcludeLocationTypes[0].ToString(); + } } else if (query.ExcludeLocationTypes.Length > 1) { @@ -2737,6 +2890,11 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add("LocationType not in (" + val + ")"); } + if (query.IsVirtualItem.HasValue) + { + whereClauses.Add("IsVirtualItem=@IsVirtualItem"); + cmd.Parameters.Add(cmd, "@IsVirtualItem", DbType.Boolean).Value = query.IsVirtualItem.Value; + } if (query.MediaTypes.Length == 1) { whereClauses.Add("MediaType=@MediaTypes"); @@ -2748,6 +2906,20 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add("MediaType in (" + val + ")"); } + if (query.ItemIds.Length > 0) + { + var excludeIds = new List<string>(); + + var index = 0; + foreach (var id in query.ItemIds) + { + excludeIds.Add("Guid = @IncludeId" + index); + cmd.Parameters.Add(cmd, "@IncludeId" + index, DbType.Guid).Value = new Guid(id); + index++; + } + + whereClauses.Add(string.Join(" OR ", excludeIds.ToArray())); + } if (query.ExcludeItemIds.Length > 0) { var excludeIds = new List<string>(); @@ -3414,7 +3586,294 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - private List<Tuple<int, string>> GetItemValues(BaseItem item) + public QueryResult<Tuple<BaseItem, ItemCounts>> GetArtists(InternalItemsQuery query) + { + return GetItemValues(query, 0, typeof(MusicArtist).FullName); + } + + public QueryResult<Tuple<BaseItem, ItemCounts>> GetAlbumArtists(InternalItemsQuery query) + { + return GetItemValues(query, 1, typeof(MusicArtist).FullName); + } + + public QueryResult<Tuple<BaseItem, ItemCounts>> GetStudios(InternalItemsQuery query) + { + return GetItemValues(query, 3, typeof(Studio).FullName); + } + + public QueryResult<Tuple<BaseItem, ItemCounts>> GetGenres(InternalItemsQuery query) + { + return GetItemValues(query, 2, typeof(Genre).FullName); + } + + public QueryResult<Tuple<BaseItem, ItemCounts>> GetGameGenres(InternalItemsQuery query) + { + return GetItemValues(query, 2, typeof(GameGenre).FullName); + } + + public QueryResult<Tuple<BaseItem, ItemCounts>> GetMusicGenres(InternalItemsQuery query) + { + return GetItemValues(query, 2, typeof(MusicGenre).FullName); + } + + private QueryResult<Tuple<BaseItem, ItemCounts>> GetItemValues(InternalItemsQuery query, int itemValueType, string returnType) + { + if (query == null) + { + throw new ArgumentNullException("query"); + } + + if (!query.Limit.HasValue) + { + query.EnableTotalRecordCount = false; + } + + CheckDisposed(); + + var now = DateTime.UtcNow; + + using (var cmd = _connection.CreateCommand()) + { + var itemCountColumns = new List<Tuple<string, string>>(); + + var typesToCount = query.IncludeItemTypes.ToList(); + + if (typesToCount.Count == 0) + { + //typesToCount.Add("Item"); + } + + foreach (var type in typesToCount) + { + var itemCountColumnQuery = "Select Count(Value) from ItemValues where ItemValues.CleanValue=CleanName AND Type=@ItemValueType AND ItemId in ("; + itemCountColumnQuery += "select guid" + GetFromText(); + + var typeSubQuery = new InternalItemsQuery(query.User) + { + ExcludeItemTypes = query.ExcludeItemTypes, + MediaTypes = query.MediaTypes, + AncestorIds = query.AncestorIds, + ExcludeItemIds = query.ExcludeItemIds, + ItemIds = query.ItemIds, + TopParentIds = query.TopParentIds, + ParentId = query.ParentId, + IsPlayed = query.IsPlayed + }; + if (string.Equals(type, "Item", StringComparison.OrdinalIgnoreCase)) + { + typeSubQuery.IncludeItemTypes = query.IncludeItemTypes; + } + else + { + typeSubQuery.IncludeItemTypes = new[] { type }; + } + var whereClauses = GetWhereClauses(typeSubQuery, cmd, type); + + var typeWhereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); + + itemCountColumnQuery += typeWhereText; + + itemCountColumnQuery += ")"; + + var columnName = type + "Count"; + + itemCountColumns.Add(new Tuple<string, string>(columnName, "(" + itemCountColumnQuery + ") as " + columnName)); + } + + var columns = _retriveItemColumns.ToList(); + columns.AddRange(itemCountColumns.Select(i => i.Item2).ToArray()); + + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, columns.ToArray(), cmd)) + GetFromText(); + cmd.CommandText += GetJoinUserDataText(query); + + var innerQuery = new InternalItemsQuery(query.User) + { + ExcludeItemTypes = query.ExcludeItemTypes, + IncludeItemTypes = query.IncludeItemTypes, + MediaTypes = query.MediaTypes, + AncestorIds = query.AncestorIds, + ExcludeItemIds = query.ExcludeItemIds, + ItemIds = query.ItemIds, + TopParentIds = query.TopParentIds, + ParentId = query.ParentId, + IsPlayed = query.IsPlayed + }; + + var innerWhereClauses = GetWhereClauses(innerQuery, cmd); + + var innerWhereText = innerWhereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", innerWhereClauses.ToArray()); + + var whereText = " where Type=@SelectType"; + whereText += " And CleanName In (Select CleanValue from ItemValues where Type=@ItemValueType AND ItemId in (select guid from TypedBaseItems" + innerWhereText + "))"; + + var outerQuery = new InternalItemsQuery(query.User) + { + IsFavorite = query.IsFavorite, + IsFavoriteOrLiked = query.IsFavoriteOrLiked, + IsLiked = query.IsLiked, + IsLocked = query.IsLocked, + NameLessThan = query.NameLessThan, + NameStartsWith = query.NameStartsWith, + NameStartsWithOrGreater = query.NameStartsWithOrGreater, + AlbumArtistStartsWithOrGreater = query.AlbumArtistStartsWithOrGreater, + Tags = query.Tags, + OfficialRatings = query.OfficialRatings, + Genres = query.GenreIds, + Years = query.Years + }; + + var outerWhereClauses = GetWhereClauses(outerQuery, cmd); + + whereText += outerWhereClauses.Count == 0 ? + string.Empty : + " AND " + string.Join(" AND ", outerWhereClauses.ToArray()); + //cmd.CommandText += GetGroupBy(query); + + cmd.CommandText += whereText; + cmd.CommandText += " group by PresentationUniqueKey"; + + cmd.Parameters.Add(cmd, "@SelectType", DbType.String).Value = returnType; + cmd.Parameters.Add(cmd, "@ItemValueType", DbType.Int32).Value = itemValueType; + + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } + + cmd.CommandText += " order by SortName"; + + if (query.Limit.HasValue || query.StartIndex.HasValue) + { + var offset = query.StartIndex ?? 0; + + if (query.Limit.HasValue || offset > 0) + { + cmd.CommandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + } + + if (offset > 0) + { + cmd.CommandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); + } + } + + cmd.CommandText += ";"; + + var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; + + if (isReturningZeroItems) + { + cmd.CommandText = ""; + } + + if (query.EnableTotalRecordCount) + { + cmd.CommandText += "select count (distinct PresentationUniqueKey)" + GetFromText(); + + cmd.CommandText += GetJoinUserDataText(query); + cmd.CommandText += whereText; + } + else + { + cmd.CommandText = cmd.CommandText.TrimEnd(';'); + } + + var list = new List<Tuple<BaseItem, ItemCounts>>(); + var count = 0; + + var commandBehavior = isReturningZeroItems || !query.EnableTotalRecordCount + ? (CommandBehavior.SequentialAccess | CommandBehavior.SingleResult) + : CommandBehavior.SequentialAccess; + + using (var reader = cmd.ExecuteReader(commandBehavior)) + { + LogQueryTime("GetItemValues", cmd, now); + + if (isReturningZeroItems) + { + if (reader.Read()) + { + count = reader.GetInt32(0); + } + } + else + { + while (reader.Read()) + { + var item = GetItem(reader); + if (item != null) + { + var countStartColumn = columns.Count - typesToCount.Count; + + list.Add(new Tuple<BaseItem, ItemCounts>(item, GetItemCounts(reader, countStartColumn, typesToCount))); + } + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } + } + } + + if (count == 0) + { + count = list.Count; + } + + return new QueryResult<Tuple<BaseItem, ItemCounts>> + { + Items = list.ToArray(), + TotalRecordCount = count + }; + + } + } + + private ItemCounts GetItemCounts(IDataReader reader, int countStartColumn, List<string> typesToCount) + { + var counts = new ItemCounts(); + + for (var i = 0; i < typesToCount.Count; i++) + { + var value = reader.GetInt32(countStartColumn + i); + + var type = typesToCount[i]; + if (string.Equals(type, "Series", StringComparison.OrdinalIgnoreCase)) + { + counts.SeriesCount = value; + } + else if (string.Equals(type, "Episode", StringComparison.OrdinalIgnoreCase)) + { + counts.EpisodeCount = value; + } + else if (string.Equals(type, "Movie", StringComparison.OrdinalIgnoreCase)) + { + counts.MovieCount = value; + } + else if (string.Equals(type, "MusicAlbum", StringComparison.OrdinalIgnoreCase)) + { + counts.AlbumCount = value; + } + else if (string.Equals(type, "Audio", StringComparison.OrdinalIgnoreCase)) + { + counts.SongCount = value; + } + else if (string.Equals(type, "Game", StringComparison.OrdinalIgnoreCase)) + { + counts.GameCount = value; + } + counts.ItemCount += value; + } + + return counts; + } + + private List<Tuple<int, string>> GetItemValuesToSave(BaseItem item) { var list = new List<Tuple<int, string>>(); @@ -3540,6 +3999,14 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemValuesCommand.GetParameter(0).Value = itemId; _saveItemValuesCommand.GetParameter(1).Value = pair.Item1; _saveItemValuesCommand.GetParameter(2).Value = pair.Item2; + if (pair.Item2 == null) + { + _saveItemValuesCommand.GetParameter(3).Value = null; + } + else + { + _saveItemValuesCommand.GetParameter(3).Value = pair.Item2.RemoveDiacritics(); + } _saveItemValuesCommand.Transaction = transaction; _saveItemValuesCommand.ExecuteNonQuery(); @@ -3732,7 +4199,7 @@ namespace MediaBrowser.Server.Implementations.Persistence return list; } - public async Task SaveMediaStreams(Guid id, IEnumerable<MediaStream> streams, CancellationToken cancellationToken) + public async Task SaveMediaStreams(Guid id, List<MediaStream> streams, CancellationToken cancellationToken) { CheckDisposed(); @@ -3805,6 +4272,9 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveStreamCommand.GetParameter(index++).Value = stream.IsAVC; _saveStreamCommand.GetParameter(index++).Value = stream.Title; + _saveStreamCommand.GetParameter(index++).Value = stream.TimeBase; + _saveStreamCommand.GetParameter(index++).Value = stream.CodecTimeBase; + _saveStreamCommand.Transaction = transaction; _saveStreamCommand.ExecuteNonQuery(); } @@ -3977,6 +4447,16 @@ namespace MediaBrowser.Server.Implementations.Persistence item.Title = reader.GetString(29); } + if (!reader.IsDBNull(30)) + { + item.TimeBase = reader.GetString(30); + } + + if (!reader.IsDBNull(31)) + { + item.CodecTimeBase = reader.GetString(31); + } + return item; } |