diff options
Diffstat (limited to 'MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs')
| -rw-r--r-- | MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs | 2273 |
1 files changed, 2029 insertions, 244 deletions
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index af275faee..63dd29e0d 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -1,4 +1,3 @@ -using MediaBrowser.Common.Configuration; using MediaBrowser.Controller.Entities; using MediaBrowser.Controller.Entities.Audio; using MediaBrowser.Controller.Entities.Movies; @@ -16,10 +15,14 @@ using System.Globalization; using System.IO; using System.Linq; using System.Runtime.Serialization; +using System.Text; using System.Threading; using System.Threading.Tasks; +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 @@ -54,7 +57,7 @@ namespace MediaBrowser.Server.Implementations.Persistence /// <summary> /// The _app paths /// </summary> - private readonly IApplicationPaths _appPaths; + private readonly IServerConfigurationManager _config; /// <summary> /// The _save item command @@ -77,79 +80,110 @@ namespace MediaBrowser.Server.Implementations.Persistence private IDbCommand _deleteAncestorsCommand; private IDbCommand _saveAncestorCommand; + private IDbCommand _deleteUserDataKeysCommand; + private IDbCommand _saveUserDataKeysCommand; + + private IDbCommand _deleteItemValuesCommand; + private IDbCommand _saveItemValuesCommand; + + private IDbCommand _deleteProviderIdsCommand; + private IDbCommand _saveProviderIdsCommand; + + private IDbCommand _deleteImagesCommand; + private IDbCommand _saveImagesCommand; + private IDbCommand _updateInheritedRatingCommand; + private IDbCommand _updateInheritedTagsCommand; - private const int LatestSchemaVersion = 53; + public const int LatestSchemaVersion = 108; /// <summary> /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. /// </summary> - /// <param name="appPaths">The app paths.</param> - /// <param name="jsonSerializer">The json serializer.</param> - /// <param name="logManager">The log manager.</param> - /// <exception cref="System.ArgumentNullException"> - /// appPaths - /// or - /// jsonSerializer - /// </exception> - public SqliteItemRepository(IApplicationPaths appPaths, IJsonSerializer jsonSerializer, ILogManager logManager) - : base(logManager) + public SqliteItemRepository(IServerConfigurationManager config, IJsonSerializer jsonSerializer, ILogManager logManager, IDbConnector connector) + : base(logManager, connector) { - if (appPaths == null) + if (config == null) { - throw new ArgumentNullException("appPaths"); + throw new ArgumentNullException("config"); } if (jsonSerializer == null) { throw new ArgumentNullException("jsonSerializer"); } - _appPaths = appPaths; + _config = config; _jsonSerializer = jsonSerializer; - _criticReviewsPath = Path.Combine(_appPaths.DataPath, "critic-reviews"); + _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 cacheSize = _config.Configuration.SqliteCacheSize; + if (cacheSize <= 0) + { + cacheSize = Math.Min(Environment.ProcessorCount * 50000, 200000); + } + + var connection = await DbConnector.Connect(DbFilePath, false, false, 0 - cacheSize).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() + public async Task Initialize(SqliteUserDataRepository userDataRepo) { - var dbFile = Path.Combine(_appPaths.DataPath, "library.db"); - - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).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, IsCabac BIT NULL, CodecTag TEXT NULL, Comment 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_TypedBaseItems on TypedBaseItems(guid)", - "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)", - "create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)", "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 Priority INT, PRIMARY KEY (ItemId, UserDataKey))", + + "create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT, CleanValue TEXT)", + + "create table if not exists ProviderIds (ItemId GUID, Name TEXT, Value TEXT, PRIMARY KEY (ItemId, Name))", + // 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+" on "+ChaptersTableName+"(ItemId, ChapterIndex)", createMediaStreamsTableCommand, - "create index if not exists idx_mediastreams on mediastreams(ItemId, StreamIndex)", - //pragmas - "pragma temp_store = memory", + "create index if not exists idx_mediastreams1 on mediastreams(ItemId)", - "pragma shrink_memory" }; _connection.RunQueries(queries, Logger); @@ -223,84 +257,95 @@ namespace MediaBrowser.Server.Implementations.Persistence _connection.AddColumn(Logger, "TypedBaseItems", "TrailerTypes", "Text"); _connection.AddColumn(Logger, "TypedBaseItems", "CriticRating", "Float"); _connection.AddColumn(Logger, "TypedBaseItems", "CriticRatingSummary", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "InheritedTags", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "CleanName", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "PresentationUniqueKey", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "SlugName", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "OriginalTitle", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "PrimaryVersionId", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "DateLastMediaAdded", "DATETIME"); + _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, "TypedBaseItems", "SeasonName", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "SeasonId", "GUID"); + _connection.AddColumn(Logger, "TypedBaseItems", "SeriesId", "GUID"); + _connection.AddColumn(Logger, "TypedBaseItems", "SeriesSortName", "Text"); + + _connection.AddColumn(Logger, "UserDataKeys", "Priority", "INT"); + _connection.AddColumn(Logger, "ItemValues", "CleanValue", "Text"); + + _connection.AddColumn(Logger, ChaptersTableName, "ImageDateModified", "DATETIME"); + + 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", + "drop index if exists idx_ItemValues3", + "drop index if exists idx_ItemValues4", + "drop index if exists idx_ItemValues5", + + "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_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)", + "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)", + + // covering index for getitemvalues + "create index if not exists idx_TypeTopParentIdGuid on TypedBaseItems(Type,TopParentId,Guid)", + + // 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_ItemValues6 on ItemValues(ItemId,Type,CleanValue)", + "create index if not exists idx_ItemValues7 on ItemValues(Type,CleanValue,ItemId)", + + // covering index + "create index if not exists idx_UserDataKeys3 on UserDataKeys(ItemId,Priority,UserDataKey)" + }; + + _connection.RunQueries(postQueries, Logger); PrepareStatements(); 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) - { - try - { - 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;" - }; - - _connection.RunQueries(queries, Logger); - } - catch (Exception ex) - { - Logger.ErrorException("Error migrating media info database", ex); - } - finally - { - TryDeleteFile(file); - } - } - - private void MigrateChapters(string file) - { - try - { - 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;" - }; - - _connection.RunQueries(queries, Logger); - } - catch (Exception ex) - { - Logger.ErrorException("Error migrating chapter database", ex); - } - finally - { - TryDeleteFile(file); - } - } - - private void TryDeleteFile(string file) - { - try - { - File.Delete(file); - } - catch (Exception ex) - { - Logger.ErrorException("Error deleting file {0}", ex, file); - } + DataExtensions.Attach(_connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb"); + await userDataRepo.Initialize(_connection, WriteLock).ConfigureAwait(false); + //await Vacuum(_connection).ConfigureAwait(false); } private readonly string[] _retriveItemColumns = @@ -355,7 +400,19 @@ namespace MediaBrowser.Server.Implementations.Persistence "Studios", "Tags", "SourceType", - "TrailerTypes" + "TrailerTypes", + "OriginalTitle", + "PrimaryVersionId", + "DateLastMediaAdded", + "Album", + "CriticRating", + "CriticRatingSummary", + "IsVirtualItem", + "SeriesName", + "SeasonName", + "SeasonId", + "SeriesId", + "SeriesSortName" }; private readonly string[] _mediaStreamSaveColumns = @@ -385,9 +442,13 @@ namespace MediaBrowser.Server.Implementations.Persistence "BitDepth", "IsAnamorphic", "RefFrames", - "IsCabac", "CodecTag", - "Comment" + "Comment", + "NalLengthSize", + "IsAvc", + "Title", + "TimeBase", + "CodecTimeBase" }; /// <summary> @@ -400,7 +461,7 @@ namespace MediaBrowser.Server.Implementations.Persistence "guid", "type", "data", - "Path", + "Path", "StartDate", "EndDate", "ChannelId", @@ -459,7 +520,22 @@ namespace MediaBrowser.Server.Implementations.Persistence "SourceType", "TrailerTypes", "CriticRating", - "CriticRatingSummary" + "CriticRatingSummary", + "InheritedTags", + "CleanName", + "PresentationUniqueKey", + "SlugName", + "OriginalTitle", + "PrimaryVersionId", + "DateLastMediaAdded", + "Album", + "IsVirtualItem", + "SeriesName", + "UserDataKey", + "SeasonName", + "SeasonId", + "SeriesId", + "SeriesSortName" }; _saveItemCommand = _connection.CreateCommand(); _saveItemCommand.CommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values ("; @@ -518,6 +594,7 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@StartPositionTicks"); _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@Name"); _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ImagePath"); + _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ImageDateModified"); // MediaStreams _deleteStreamsCommand = _connection.CreateCommand(); @@ -537,8 +614,61 @@ namespace MediaBrowser.Server.Implementations.Persistence _updateInheritedRatingCommand = _connection.CreateCommand(); _updateInheritedRatingCommand.CommandText = "Update TypedBaseItems set InheritedParentalRatingValue=@InheritedParentalRatingValue where Guid=@Guid"; - _updateInheritedRatingCommand.Parameters.Add(_updateInheritedRatingCommand, "@InheritedParentalRatingValue"); _updateInheritedRatingCommand.Parameters.Add(_updateInheritedRatingCommand, "@Guid"); + _updateInheritedRatingCommand.Parameters.Add(_updateInheritedRatingCommand, "@InheritedParentalRatingValue"); + + _updateInheritedTagsCommand = _connection.CreateCommand(); + _updateInheritedTagsCommand.CommandText = "Update TypedBaseItems set InheritedTags=@InheritedTags where Guid=@Guid"; + _updateInheritedTagsCommand.Parameters.Add(_updateInheritedTagsCommand, "@Guid"); + _updateInheritedTagsCommand.Parameters.Add(_updateInheritedTagsCommand, "@InheritedTags"); + + // user data + _deleteUserDataKeysCommand = _connection.CreateCommand(); + _deleteUserDataKeysCommand.CommandText = "delete from UserDataKeys where ItemId=@Id"; + _deleteUserDataKeysCommand.Parameters.Add(_deleteUserDataKeysCommand, "@Id"); + + _saveUserDataKeysCommand = _connection.CreateCommand(); + _saveUserDataKeysCommand.CommandText = "insert into UserDataKeys (ItemId, UserDataKey, Priority) values (@ItemId, @UserDataKey, @Priority)"; + _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@ItemId"); + _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@UserDataKey"); + _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@Priority"); + + // item values + _deleteItemValuesCommand = _connection.CreateCommand(); + _deleteItemValuesCommand.CommandText = "delete from ItemValues where ItemId=@Id"; + _deleteItemValuesCommand.Parameters.Add(_deleteItemValuesCommand, "@Id"); + + _saveItemValuesCommand = _connection.CreateCommand(); + _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(); + _deleteProviderIdsCommand.CommandText = "delete from ProviderIds where ItemId=@Id"; + _deleteProviderIdsCommand.Parameters.Add(_deleteProviderIdsCommand, "@Id"); + + _saveProviderIdsCommand = _connection.CreateCommand(); + _saveProviderIdsCommand.CommandText = "insert into ProviderIds (ItemId, Name, Value) values (@ItemId, @Name, @Value)"; + _saveProviderIdsCommand.Parameters.Add(_saveProviderIdsCommand, "@ItemId"); + _saveProviderIdsCommand.Parameters.Add(_saveProviderIdsCommand, "@Name"); + _saveProviderIdsCommand.Parameters.Add(_saveProviderIdsCommand, "@Value"); + + // images + _deleteImagesCommand = _connection.CreateCommand(); + _deleteImagesCommand.CommandText = "delete from Images where ItemId=@Id"; + _deleteImagesCommand.Parameters.Add(_deleteImagesCommand, "@Id"); + + _saveImagesCommand = _connection.CreateCommand(); + _saveImagesCommand.CommandText = "insert into Images (ItemId, ImageType, Path, DateModified, IsPlaceHolder, SortOrder) values (@ItemId, @ImageType, @Path, @DateModified, @IsPlaceHolder, @SortOrder)"; + _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@ItemId"); + _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@ImageType"); + _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@Path"); + _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@DateModified"); + _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@IsPlaceHolder"); + _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@SortOrder"); } /// <summary> @@ -696,7 +826,15 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = item.DateLastRefreshed; } - _saveItemCommand.GetParameter(index++).Value = item.DateLastSaved; + if (item.DateLastSaved == default(DateTime)) + { + _saveItemCommand.GetParameter(index++).Value = null; + } + else + { + _saveItemCommand.GetParameter(index++).Value = item.DateLastSaved; + } + _saveItemCommand.GetParameter(index++).Value = item.IsInMixedFolder; _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.LockedFields.Select(i => i.ToString()).ToArray()); _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Studios.ToArray()); @@ -712,7 +850,15 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = item.ServiceName; - _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Tags.ToArray()); + if (item.Tags.Count > 0) + { + _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Tags.ToArray()); + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + } + _saveItemCommand.GetParameter(index++).Value = item.IsFolder; _saveItemCommand.GetParameter(index++).Value = item.GetBlockUnratedType().ToString(); @@ -741,7 +887,7 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = item.SourceType.ToString(); var trailer = item as Trailer; - if (trailer != null) + if (trailer != null && trailer.TrailerTypes.Count > 0) { _saveItemCommand.GetParameter(index++).Value = string.Join("|", trailer.TrailerTypes.Select(i => i.ToString()).ToArray()); } @@ -752,7 +898,89 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = item.CriticRating; _saveItemCommand.GetParameter(index++).Value = item.CriticRatingSummary; - + + var inheritedTags = item.GetInheritedTags(); + if (inheritedTags.Count > 0) + { + _saveItemCommand.GetParameter(index++).Value = string.Join("|", inheritedTags.ToArray()); + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + } + + if (string.IsNullOrWhiteSpace(item.Name)) + { + _saveItemCommand.GetParameter(index++).Value = null; + } + else + { + _saveItemCommand.GetParameter(index++).Value = item.Name.RemoveDiacritics(); + } + + _saveItemCommand.GetParameter(index++).Value = item.PresentationUniqueKey; + _saveItemCommand.GetParameter(index++).Value = item.SlugName; + _saveItemCommand.GetParameter(index++).Value = item.OriginalTitle; + + var video = item as Video; + if (video != null) + { + _saveItemCommand.GetParameter(index++).Value = video.PrimaryVersionId; + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + } + + var folder = item as Folder; + if (folder != null && folder.DateLastMediaAdded.HasValue) + { + _saveItemCommand.GetParameter(index++).Value = folder.DateLastMediaAdded.Value; + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + } + + _saveItemCommand.GetParameter(index++).Value = item.Album; + + _saveItemCommand.GetParameter(index++).Value = item.IsVirtualItem || (!item.IsFolder && item.LocationType == LocationType.Virtual); + + var hasSeries = item as IHasSeries; + if (hasSeries != null) + { + _saveItemCommand.GetParameter(index++).Value = hasSeries.FindSeriesName(); + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + } + + _saveItemCommand.GetParameter(index++).Value = item.GetUserDataKeys().FirstOrDefault(); + + var episode = item as Episode; + if (episode != null) + { + _saveItemCommand.GetParameter(index++).Value = episode.FindSeasonName(); + _saveItemCommand.GetParameter(index++).Value = episode.FindSeasonId(); + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + _saveItemCommand.GetParameter(index++).Value = null; + } + + if (hasSeries != null) + { + _saveItemCommand.GetParameter(index++).Value = hasSeries.FindSeriesId(); + _saveItemCommand.GetParameter(index++).Value = hasSeries.FindSeriesSortName(); + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + _saveItemCommand.GetParameter(index++).Value = null; + } + _saveItemCommand.Transaction = transaction; _saveItemCommand.ExecuteNonQuery(); @@ -761,6 +989,11 @@ namespace MediaBrowser.Server.Implementations.Persistence { UpdateAncestors(item.Id, item.GetAncestorIds().Distinct().ToList(), transaction); } + + 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, GetItemValuesToSave(item), transaction); } transaction.Commit(); @@ -836,7 +1069,7 @@ namespace MediaBrowser.Server.Implementations.Persistence if (type == null) { - Logger.Debug("Unknown type {0}", typeString); + //Logger.Debug("Unknown type {0}", typeString); return null; } @@ -1125,6 +1358,102 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + var index = 51; + + if (!reader.IsDBNull(index)) + { + item.OriginalTitle = reader.GetString(index); + } + index++; + + var video = item as Video; + if (video != null) + { + if (!reader.IsDBNull(index)) + { + video.PrimaryVersionId = reader.GetString(index); + } + } + index++; + + var folder = item as Folder; + if (folder != null && !reader.IsDBNull(index)) + { + folder.DateLastMediaAdded = reader.GetDateTime(index).ToUniversalTime(); + } + index++; + + if (!reader.IsDBNull(index)) + { + item.Album = reader.GetString(index); + } + index++; + + if (!reader.IsDBNull(index)) + { + item.CriticRating = reader.GetFloat(index); + } + index++; + + if (!reader.IsDBNull(index)) + { + item.CriticRatingSummary = reader.GetString(index); + } + index++; + + if (!reader.IsDBNull(index)) + { + item.IsVirtualItem = reader.GetBoolean(index); + } + index++; + + var hasSeries = item as IHasSeries; + if (hasSeries != null) + { + if (!reader.IsDBNull(index)) + { + hasSeries.SeriesName = reader.GetString(index); + } + } + index++; + + var episode = item as Episode; + if (episode != null) + { + if (!reader.IsDBNull(index)) + { + episode.SeasonName = reader.GetString(index); + } + index++; + if (!reader.IsDBNull(index)) + { + episode.SeasonId = reader.GetGuid(index); + } + } + else + { + index++; + } + index++; + + if (hasSeries != null) + { + if (!reader.IsDBNull(index)) + { + hasSeries.SeriesId = reader.GetGuid(index); + } + } + index++; + + if (hasSeries != null) + { + if (!reader.IsDBNull(index)) + { + hasSeries.SeriesSortName = reader.GetString(index); + } + } + index++; + return item; } @@ -1182,10 +1511,11 @@ namespace MediaBrowser.Server.Implementations.Persistence { throw new ArgumentNullException("id"); } + var list = new List<ChapterInfo>(); using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"; + cmd.CommandText = "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"; cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = id; @@ -1193,10 +1523,12 @@ namespace MediaBrowser.Server.Implementations.Persistence { while (reader.Read()) { - yield return GetChapter(reader); + list.Add(GetChapter(reader)); } } } + + return list; } /// <summary> @@ -1216,7 +1548,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex"; + cmd.CommandText = "select StartPositionTicks,Name,ImagePath,ImageDateModified 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; @@ -1254,6 +1586,11 @@ namespace MediaBrowser.Server.Implementations.Persistence chapter.ImagePath = reader.GetString(2); } + if (!reader.IsDBNull(3)) + { + chapter.ImageDateModified = reader.GetDateTime(3).ToUniversalTime(); + } + return chapter; } @@ -1271,7 +1608,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(); @@ -1313,6 +1650,7 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveChapterCommand.GetParameter(2).Value = chapter.StartPositionTicks; _saveChapterCommand.GetParameter(3).Value = chapter.Name; _saveChapterCommand.GetParameter(4).Value = chapter.ImagePath; + _saveChapterCommand.GetParameter(5).Value = chapter.ImageDateModified; _saveChapterCommand.Transaction = transaction; @@ -1368,37 +1706,167 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - public IEnumerable<BaseItem> GetItemsOfType(Type type) + private bool EnableJoinUserData(InternalItemsQuery query) { - if (type == null) + if (query.User == null) { - throw new ArgumentNullException("type"); + return false; } - CheckDisposed(); + if (query.SimilarTo != null && query.User != null) + { + return true; + } - using (var cmd = _connection.CreateCommand()) + if (query.SortBy != null && query.SortBy.Length > 0) + { + if (query.SortBy.Contains(ItemSortBy.IsFavoriteOrLiked, StringComparer.OrdinalIgnoreCase)) + { + return true; + } + if (query.SortBy.Contains(ItemSortBy.IsPlayed, StringComparer.OrdinalIgnoreCase)) + { + return true; + } + if (query.SortBy.Contains(ItemSortBy.IsUnplayed, StringComparer.OrdinalIgnoreCase)) + { + return true; + } + if (query.SortBy.Contains(ItemSortBy.PlayCount, StringComparer.OrdinalIgnoreCase)) + { + return true; + } + if (query.SortBy.Contains(ItemSortBy.DatePlayed, StringComparer.OrdinalIgnoreCase)) + { + return true; + } + } + + if (query.IsFavoriteOrLiked.HasValue) { - cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where type = @type"; + return true; + } - cmd.Parameters.Add(cmd, "@type", DbType.String).Value = type.FullName; + if (query.IsFavorite.HasValue) + { + return true; + } - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) - { - while (reader.Read()) - { - var item = GetItem(reader); + if (query.IsResumable.HasValue) + { + return true; + } - if (item != null) - { - yield return item; - } - } - } + if (query.IsPlayed.HasValue) + { + return true; + } + + if (query.IsLiked.HasValue) + { + return true; } + + return false; } - public IEnumerable<BaseItem> GetItemList(InternalItemsQuery query) + private string[] GetFinalColumnsToSelect(InternalItemsQuery query, string[] startColumns, IDbCommand cmd) + { + var list = startColumns.ToList(); + + if (EnableJoinUserData(query)) + { + list.Add("UserDataDb.UserData.UserId"); + list.Add("UserDataDb.UserData.lastPlayedDate"); + list.Add("UserDataDb.UserData.playbackPositionTicks"); + list.Add("UserDataDb.UserData.playcount"); + list.Add("UserDataDb.UserData.isFavorite"); + list.Add("UserDataDb.UserData.played"); + list.Add("UserDataDb.UserData.rating"); + } + + if (query.SimilarTo != null) + { + var item = query.SimilarTo; + + var builder = new StringBuilder(); + builder.Append("("); + + builder.Append("((OfficialRating=@ItemOfficialRating) * 10)"); + //builder.Append("+ ((ProductionYear=@ItemProductionYear) * 10)"); + + builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 10 Then 2 Else 0 End )"); + builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 5 Then 2 Else 0 End )"); + + //// genres + builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=2 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=2)) * 10)"); + + //// tags + builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=4 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=4)) * 10)"); + + builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=5 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=5)) * 10)"); + + builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=3 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=3)) * 3)"); + + //builder.Append("+ ((Select count(Name) from People where ItemId=Guid and Name in (select Name from People where ItemId=@SimilarItemId)) * 3)"); + + ////builder.Append("(select group_concat((Select Name from People where ItemId=Guid and Name in (Select Name from People where ItemId=@SimilarItemId)), '|'))"); + + builder.Append(") as SimilarityScore"); + + list.Add(builder.ToString()); + cmd.Parameters.Add(cmd, "@ItemOfficialRating", DbType.String).Value = item.OfficialRating; + cmd.Parameters.Add(cmd, "@ItemProductionYear", DbType.Int32).Value = item.ProductionYear ?? 0; + cmd.Parameters.Add(cmd, "@SimilarItemId", DbType.Guid).Value = item.Id; + + var excludeIds = query.ExcludeItemIds.ToList(); + excludeIds.Add(item.Id.ToString("N")); + query.ExcludeItemIds = excludeIds.ToArray(); + + query.ExcludeProviderIds = item.ProviderIds; + } + + return list.ToArray(); + } + + private string GetJoinUserDataText(InternalItemsQuery query) + { + if (!EnableJoinUserData(query)) + { + return string.Empty; + } + + 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 (EnableGroupByPresentationUniqueKey(query)) + { + groups.Add("PresentationUniqueKey"); + } + + if (groups.Count > 0) + { + return " Group by " + string.Join(",", groups.ToArray()); + } + + return string.Empty; + } + + private string GetFromText(string alias = "A") + { + return " from TypedBaseItems " + alias; + } + + public List<BaseItem> GetItemList(InternalItemsQuery query) { if (query == null) { @@ -1407,11 +1875,27 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); + var now = DateTime.UtcNow; + + var list = new List<BaseItem>(); + + // Hack for right now since we currently don't support filtering out these duplicates within a query + if (query.Limit.HasValue && query.EnableGroupByMetadataKey) + { + query.Limit = query.Limit.Value + 4; + } + using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + GetFromText(); + cmd.CommandText += GetJoinUserDataText(query); + + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } - var whereClauses = GetWhereClauses(query, cmd, true); + var whereClauses = GetWhereClauses(query, cmd); var whereText = whereClauses.Count == 0 ? string.Empty : @@ -1419,27 +1903,115 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; + cmd.CommandText += GetGroupBy(query); + cmd.CommandText += GetOrderByText(query); - if (query.Limit.HasValue) + if (query.Limit.HasValue || query.StartIndex.HasValue) { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); - } + var offset = query.StartIndex ?? 0; - //Logger.Debug(cmd.CommandText); + 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); + } + } using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { + LogQueryTime("GetItemList", cmd, now); + while (reader.Read()) { var item = GetItem(reader); if (item != null) { - yield return item; + list.Add(item); } } } } + + // Hack for right now since we currently don't support filtering out these duplicates within a query + if (query.EnableGroupByMetadataKey) + { + var limit = query.Limit ?? int.MaxValue; + limit -= 4; + var newList = new List<BaseItem>(); + + foreach (var item in list) + { + AddItem(newList, item); + + if (newList.Count >= limit) + { + break; + } + } + + list = newList; + } + + return list; + } + + private void AddItem(List<BaseItem> items, BaseItem newItem) + { + var providerIds = newItem.ProviderIds.ToList(); + + for (var i = 0; i < items.Count; i++) + { + var item = items[i]; + + foreach (var providerId in providerIds) + { + if (providerId.Key == MetadataProviders.TmdbCollection.ToString()) + { + continue; + } + if (item.GetProviderId(providerId.Key) == providerId.Value) + { + if (newItem.SourceType == SourceType.Library) + { + items[i] = newItem; + } + return; + } + } + } + + items.Add(newItem); + } + + private void LogQueryTime(string methodName, IDbCommand cmd, DateTime startDate) + { + var elapsed = (DateTime.UtcNow - startDate).TotalMilliseconds; + + var slowThreshold = 1000; + +#if DEBUG + slowThreshold = 50; +#endif + + if (elapsed >= slowThreshold) + { + Logger.Debug("{2} query time (slow): {0}ms. Query: {1}", + Convert.ToInt32(elapsed), + cmd.CommandText, + methodName); + } + else + { + //Logger.Debug("{2} query time: {0}ms. Query: {1}", + // Convert.ToInt32(elapsed), + // cmd.CommandText, + // methodName); + } } public QueryResult<BaseItem> GetItems(InternalItemsQuery query) @@ -1451,52 +2023,109 @@ 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(",", _retriveItemColumns) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + GetFromText(); + cmd.CommandText += GetJoinUserDataText(query); - var whereClauses = GetWhereClauses(query, cmd, false); + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } + + var whereClauses = GetWhereClauses(query, cmd); 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 += GetGroupBy(query); + cmd.CommandText += GetOrderByText(query); - if (query.Limit.HasValue) + if (query.Limit.HasValue || query.StartIndex.HasValue) { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); + 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 += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging; + cmd.CommandText += ";"; - //Logger.Debug(cmd.CommandText); + var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; + + if (isReturningZeroItems) + { + cmd.CommandText = ""; + } + + if (EnableGroupByPresentationUniqueKey(query)) + { + cmd.CommandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); + } + else + { + cmd.CommandText += " select count (guid)" + GetFromText(); + } + + cmd.CommandText += GetJoinUserDataText(query); + cmd.CommandText += whereTextWithoutPaging; var list = new List<BaseItem>(); var count = 0; using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { - while (reader.Read()) + LogQueryTime("GetItems", cmd, now); + + 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); + } } } @@ -1510,33 +2139,109 @@ namespace MediaBrowser.Server.Implementations.Persistence private string GetOrderByText(InternalItemsQuery query) { + if (query.SimilarTo != null) + { + if (query.SortBy == null || query.SortBy.Length == 0) + { + if (query.User != null) + { + query.SortBy = new[] { ItemSortBy.IsPlayed, "SimilarityScore", ItemSortBy.Random }; + } + else + { + query.SortBy = new[] { "SimilarityScore", ItemSortBy.Random }; + } + query.SortOrder = SortOrder.Descending; + } + } + if (query.SortBy == null || query.SortBy.Length == 0) { return string.Empty; } - var sortOrder = query.SortOrder == SortOrder.Descending ? "DESC" : "ASC"; + var isAscending = query.SortOrder != SortOrder.Descending; - return " ORDER BY " + string.Join(",", query.SortBy.Select(i => MapOrderByField(i) + " " + sortOrder).ToArray()); + return " ORDER BY " + string.Join(",", query.SortBy.Select(i => + { + var columnMap = MapOrderByField(i, query); + var columnAscending = isAscending; + if (columnMap.Item2) + { + columnAscending = !columnAscending; + } + + var sortOrder = columnAscending ? "ASC" : "DESC"; + + return columnMap.Item1 + " " + sortOrder; + }).ToArray()); } - private string MapOrderByField(string name) + private Tuple<string, bool> MapOrderByField(string name, InternalItemsQuery query) { if (string.Equals(name, ItemSortBy.AirTime, StringComparison.OrdinalIgnoreCase)) { // TODO - return "SortName"; + return new Tuple<string, bool>("SortName", false); } if (string.Equals(name, ItemSortBy.Runtime, StringComparison.OrdinalIgnoreCase)) { - return "RuntimeTicks"; + return new Tuple<string, bool>("RuntimeTicks", false); } if (string.Equals(name, ItemSortBy.Random, StringComparison.OrdinalIgnoreCase)) { - return "RANDOM()"; + return new Tuple<string, bool>("RANDOM()", false); + } + if (string.Equals(name, ItemSortBy.DatePlayed, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("LastPlayedDate", false); + } + if (string.Equals(name, ItemSortBy.PlayCount, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("PlayCount", false); + } + if (string.Equals(name, ItemSortBy.IsFavoriteOrLiked, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("IsFavorite", true); + } + if (string.Equals(name, ItemSortBy.IsFolder, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("IsFolder", true); + } + if (string.Equals(name, ItemSortBy.IsPlayed, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("played", true); + } + if (string.Equals(name, ItemSortBy.IsUnplayed, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("played", false); + } + if (string.Equals(name, ItemSortBy.DateLastContentAdded, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("DateLastMediaAdded", false); + } + if (string.Equals(name, ItemSortBy.Artist, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("(select CleanValue from itemvalues where ItemId=Guid and Type=0 LIMIT 1)", false); + } + if (string.Equals(name, ItemSortBy.AlbumArtist, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("(select CleanValue from itemvalues where ItemId=Guid and Type=1 LIMIT 1)", false); + } + if (string.Equals(name, ItemSortBy.OfficialRating, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("ParentalRatingValue", false); + } + if (string.Equals(name, ItemSortBy.Studio, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("(select CleanValue 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 name; + return new Tuple<string, bool>(name, false); } public List<Guid> GetItemIdsList(InternalItemsQuery query) @@ -1548,11 +2253,19 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); + var now = DateTime.UtcNow; + using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select guid from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + GetFromText(); + cmd.CommandText += GetJoinUserDataText(query); + + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } - var whereClauses = GetWhereClauses(query, cmd, true); + var whereClauses = GetWhereClauses(query, cmd); var whereText = whereClauses.Count == 0 ? string.Empty : @@ -1560,19 +2273,31 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; + cmd.CommandText += GetGroupBy(query); + cmd.CommandText += GetOrderByText(query); - if (query.Limit.HasValue) + if (query.Limit.HasValue || query.StartIndex.HasValue) { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); + 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); + } } var list = new List<Guid>(); - //Logger.Debug(cmd.CommandText); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { + LogQueryTime("GetItemIdsList", cmd, now); + while (reader.Read()) { list.Add(reader.GetGuid(0)); @@ -1596,25 +2321,35 @@ namespace MediaBrowser.Server.Implementations.Persistence { cmd.CommandText = "select guid,path from TypedBaseItems"; - var whereClauses = GetWhereClauses(query, cmd, false); + var whereClauses = GetWhereClauses(query, cmd); 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 += GetGroupBy(query); + cmd.CommandText += GetOrderByText(query); - if (query.Limit.HasValue) + if (query.Limit.HasValue || query.StartIndex.HasValue) { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); + 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 += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging; @@ -1661,17 +2396,29 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); - using (var cmd = _connection.CreateCommand()) + if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0)) { - cmd.CommandText = "select guid from TypedBaseItems"; + var list = GetItemIdsList(query); + return new QueryResult<Guid> + { + Items = list.ToArray(), + TotalRecordCount = list.Count + }; + } - var whereClauses = GetWhereClauses(query, cmd, false); + var now = DateTime.UtcNow; - var whereTextWithoutPaging = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + GetFromText(); + + var whereClauses = GetWhereClauses(query, cmd); + cmd.CommandText += GetJoinUserDataText(query); - whereClauses = GetWhereClauses(query, cmd, true); + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } var whereText = whereClauses.Count == 0 ? string.Empty : @@ -1679,22 +2426,44 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; + cmd.CommandText += GetGroupBy(query); + cmd.CommandText += GetOrderByText(query); - if (query.Limit.HasValue) + if (query.Limit.HasValue || query.StartIndex.HasValue) { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); + 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 += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging; + if (EnableGroupByPresentationUniqueKey(query)) + { + cmd.CommandText += "; select count (distinct PresentationUniqueKey)" + GetFromText(); + } + else + { + cmd.CommandText += "; select count (guid)" + GetFromText(); + } + + cmd.CommandText += GetJoinUserDataText(query); + cmd.CommandText += whereText; var list = new List<Guid>(); var count = 0; - //Logger.Debug(cmd.CommandText); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { + LogQueryTime("GetItemIds", cmd, now); + while (reader.Read()) { list.Add(reader.GetGuid(0)); @@ -1714,10 +2483,14 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - private List<string> GetWhereClauses(InternalItemsQuery query, IDbCommand cmd, bool addPaging) + 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)"); + } if (query.IsCurrentSchema.HasValue) { if (query.IsCurrentSchema.Value) @@ -1747,7 +2520,24 @@ namespace MediaBrowser.Server.Implementations.Persistence } if (query.IsMovie.HasValue) { - whereClauses.Add("IsMovie=@IsMovie"); + var alternateTypes = new List<string>(); + if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Movie).Name)) + { + alternateTypes.Add(typeof(Movie).FullName); + } + if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Trailer).Name)) + { + alternateTypes.Add(typeof(Trailer).FullName); + } + + if (alternateTypes.Count == 0) + { + whereClauses.Add("IsMovie=@IsMovie"); + } + else + { + whereClauses.Add("(IsMovie is null OR IsMovie=@IsMovie)"); + } cmd.Parameters.Add(cmd, "@IsMovie", DbType.Boolean).Value = query.IsMovie; } if (query.IsKids.HasValue) @@ -1769,8 +2559,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) { @@ -1813,6 +2603,12 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.Parameters.Add(cmd, "@Path", DbType.String).Value = query.Path; } + if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey)) + { + whereClauses.Add("PresentationUniqueKey=@PresentationUniqueKey"); + cmd.Parameters.Add(cmd, "@PresentationUniqueKey", DbType.String).Value = query.PresentationUniqueKey; + } + if (query.MinCommunityRating.HasValue) { whereClauses.Add("CommunityRating>=@MinCommunityRating"); @@ -1837,11 +2633,21 @@ namespace MediaBrowser.Server.Implementations.Persistence // cmd.Parameters.Add(cmd, "@MaxPlayers", DbType.Int32).Value = query.MaxPlayers.Value; //} + if (query.IndexNumber.HasValue) + { + whereClauses.Add("IndexNumber=@IndexNumber"); + cmd.Parameters.Add(cmd, "@IndexNumber", DbType.Int32).Value = query.IndexNumber.Value; + } if (query.ParentIndexNumber.HasValue) { - whereClauses.Add("ParentIndexNumber=@MinEndDate"); + 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"); @@ -1913,20 +2719,6 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add(clause); } - if (query.ExcludeTrailerTypes.Length > 0) - { - var clauses = new List<string>(); - var index = 0; - foreach (var type in query.ExcludeTrailerTypes) - { - clauses.Add("TrailerTypes not like @TrailerTypes" + index); - cmd.Parameters.Add(cmd, "@TrailerTypes" + index, DbType.String).Value = "%" + type + "%"; - index++; - } - var clause = "(" + string.Join(" AND ", clauses.ToArray()) + ")"; - whereClauses.Add(clause); - } - if (query.IsAiring.HasValue) { if (query.IsAiring.Value) @@ -1944,16 +2736,176 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + if (query.PersonIds.Length > 0) + { + // Todo: improve without having to do this + query.Person = query.PersonIds.Select(i => RetrieveItem(new Guid(i))).Where(i => i != null).Select(i => i.Name).FirstOrDefault(); + } + if (!string.IsNullOrWhiteSpace(query.Person)) { whereClauses.Add("Guid in (select ItemId from People where Name=@PersonName)"); cmd.Parameters.Add(cmd, "@PersonName", DbType.String).Value = query.Person; } + if (!string.IsNullOrWhiteSpace(query.SlugName)) + { + whereClauses.Add("SlugName=@SlugName"); + cmd.Parameters.Add(cmd, "@SlugName", DbType.String).Value = query.SlugName; + } + + if (!string.IsNullOrWhiteSpace(query.MinSortName)) + { + whereClauses.Add("SortName>=@MinSortName"); + cmd.Parameters.Add(cmd, "@MinSortName", DbType.String).Value = query.MinSortName; + } + + if (!string.IsNullOrWhiteSpace(query.Name)) + { + whereClauses.Add("CleanName=@Name"); + cmd.Parameters.Add(cmd, "@Name", DbType.String).Value = query.Name.RemoveDiacritics(); + } + if (!string.IsNullOrWhiteSpace(query.NameContains)) { - whereClauses.Add("Name like @NameContains"); - cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%" + query.NameContains + "%"; + whereClauses.Add("CleanName like @NameContains"); + cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%" + query.NameContains.RemoveDiacritics() + "%"; + } + if (!string.IsNullOrWhiteSpace(query.NameStartsWith)) + { + whereClauses.Add("SortName like @NameStartsWith"); + cmd.Parameters.Add(cmd, "@NameStartsWith", DbType.String).Value = query.NameStartsWith + "%"; + } + if (!string.IsNullOrWhiteSpace(query.NameStartsWithOrGreater)) + { + whereClauses.Add("SortName >= @NameStartsWithOrGreater"); + // lowercase this because SortName is stored as lowercase + cmd.Parameters.Add(cmd, "@NameStartsWithOrGreater", DbType.String).Value = query.NameStartsWithOrGreater.ToLower(); + } + if (!string.IsNullOrWhiteSpace(query.NameLessThan)) + { + whereClauses.Add("SortName < @NameLessThan"); + // lowercase this because SortName is stored as lowercase + cmd.Parameters.Add(cmd, "@NameLessThan", DbType.String).Value = query.NameLessThan.ToLower(); + } + + if (query.ImageTypes.Length > 0 && _config.Configuration.SchemaVersion >= 87) + { + var requiredImageIndex = 0; + + foreach (var requiredImage in query.ImageTypes) + { + var paramName = "@RequiredImageType" + requiredImageIndex; + whereClauses.Add("(select path from images where ItemId=Guid and ImageType=" + paramName + " limit 1) not null"); + cmd.Parameters.Add(cmd, paramName, DbType.Int32).Value = (int)requiredImage; + requiredImageIndex++; + } + } + + if (query.IsLiked.HasValue) + { + if (query.IsLiked.Value) + { + whereClauses.Add("rating>=@UserRating"); + cmd.Parameters.Add(cmd, "@UserRating", DbType.Double).Value = UserItemData.MinLikeValue; + } + else + { + whereClauses.Add("(rating is null or rating<@UserRating)"); + cmd.Parameters.Add(cmd, "@UserRating", DbType.Double).Value = UserItemData.MinLikeValue; + } + } + + if (query.IsFavoriteOrLiked.HasValue) + { + if (query.IsFavoriteOrLiked.Value) + { + whereClauses.Add("IsFavorite=@IsFavoriteOrLiked"); + } + else + { + whereClauses.Add("(IsFavorite is null or IsFavorite=@IsFavoriteOrLiked)"); + } + cmd.Parameters.Add(cmd, "@IsFavoriteOrLiked", DbType.Boolean).Value = query.IsFavoriteOrLiked.Value; + } + + if (query.IsFavorite.HasValue) + { + if (query.IsFavorite.Value) + { + whereClauses.Add("IsFavorite=@IsFavorite"); + } + else + { + whereClauses.Add("(IsFavorite is null or IsFavorite=@IsFavorite)"); + } + cmd.Parameters.Add(cmd, "@IsFavorite", DbType.Boolean).Value = query.IsFavorite.Value; + } + + if (EnableJoinUserData(query)) + { + if (query.IsPlayed.HasValue) + { + if (query.IsPlayed.Value) + { + whereClauses.Add("(played=@IsPlayed)"); + } + else + { + whereClauses.Add("(played is null or played=@IsPlayed)"); + } + cmd.Parameters.Add(cmd, "@IsPlayed", DbType.Boolean).Value = query.IsPlayed.Value; + } + } + + if (query.IsResumable.HasValue) + { + if (query.IsResumable.Value) + { + whereClauses.Add("playbackPositionTicks > 0"); + } + else + { + whereClauses.Add("(playbackPositionTicks is null or playbackPositionTicks = 0)"); + } + } + + if (query.ArtistNames.Length > 0) + { + var clauses = new List<string>(); + var index = 0; + foreach (var artist in query.ArtistNames) + { + 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()) + ")"; + whereClauses.Add(clause); + } + + if (query.ExcludeArtistIds.Length > 0) + { + var clauses = new List<string>(); + var index = 0; + foreach (var artistId in query.ExcludeArtistIds) + { + var artistItem = RetrieveItem(new Guid(artistId)); + if (artistItem != null) + { + clauses.Add("@ExcludeArtistName" + index + " not in (select CleanValue from itemvalues where ItemId=Guid and Type <= 1)"); + cmd.Parameters.Add(cmd, "@ExcludeArtistName" + index, DbType.String).Value = artistItem.Name.RemoveDiacritics(); + index++; + } + } + var clause = "(" + string.Join(" AND ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); + } + + if (query.GenreIds.Length > 0) + { + // Todo: improve without having to do this + query.Genres = query.GenreIds.Select(i => RetrieveItem(new Guid(i))).Where(i => i != null).Select(i => i.Name).ToArray(); } if (query.Genres.Length > 0) @@ -1962,8 +2914,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Genres) { - clauses.Add("Genres like @Genres" + index); - cmd.Parameters.Add(cmd, "@Genres" + 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()) + ")"; @@ -1976,22 +2928,56 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Tags) { - clauses.Add("Tags like @Tags" + index); - cmd.Parameters.Add(cmd, "@Tags" + 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()) + ")"; whereClauses.Add(clause); } + if (query.StudioIds.Length > 0) + { + // Todo: improve without having to do this + query.Studios = query.StudioIds.Select(i => RetrieveItem(new Guid(i))).Where(i => i != null).Select(i => i.Name).ToArray(); + } + if (query.Studios.Length > 0) { var clauses = new List<string>(); var index = 0; foreach (var item in query.Studios) { - clauses.Add("Studios like @Studios" + index); - cmd.Parameters.Add(cmd, "@Studios" + 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()) + ")"; + whereClauses.Add(clause); + } + + if (query.Keywords.Length > 0) + { + var clauses = new List<string>(); + var index = 0; + foreach (var item in query.Keywords) + { + 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()) + ")"; + whereClauses.Add(clause); + } + + if (query.OfficialRatings.Length > 0) + { + var clauses = new List<string>(); + var index = 0; + foreach (var item in query.OfficialRatings) + { + clauses.Add("OfficialRating=@OfficialRating" + index); + cmd.Parameters.Add(cmd, "@OfficialRating" + index, DbType.String).Value = item; index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2056,8 +3042,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) { @@ -2067,8 +3060,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) { @@ -2076,10 +3076,55 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add("LocationType not in (" + val + ")"); } + if (query.IsVirtualItem.HasValue) + { + if (_config.Configuration.SchemaVersion >= 90) + { + whereClauses.Add("IsVirtualItem=@IsVirtualItem"); + cmd.Parameters.Add(cmd, "@IsVirtualItem", DbType.Boolean).Value = query.IsVirtualItem.Value; + } + else if (!query.IsVirtualItem.Value) + { + whereClauses.Add("LocationType<>'Virtual'"); + } + } + if (query.IsUnaired.HasValue) + { + if (query.IsUnaired.Value) + { + whereClauses.Add("PremiereDate >= DATETIME('now')"); + } + else + { + whereClauses.Add("PremiereDate < DATETIME('now')"); + } + } + if (query.IsMissing.HasValue && _config.Configuration.SchemaVersion >= 90) + { + if (query.IsMissing.Value) + { + whereClauses.Add("(IsVirtualItem=1 AND PremiereDate < DATETIME('now'))"); + } + else + { + whereClauses.Add("(IsVirtualItem=0 OR PremiereDate >= DATETIME('now'))"); + } + } + if (query.IsVirtualUnaired.HasValue && _config.Configuration.SchemaVersion >= 90) + { + if (query.IsVirtualUnaired.Value) + { + whereClauses.Add("(IsVirtualItem=1 AND PremiereDate >= DATETIME('now'))"); + } + else + { + whereClauses.Add("(IsVirtualItem=0 OR PremiereDate < DATETIME('now'))"); + } + } if (query.MediaTypes.Length == 1) { whereClauses.Add("MediaType=@MediaTypes"); - cmd.Parameters.Add(cmd, "@MediaTypes", DbType.String).Value = query.MediaTypes[0].ToString(); + cmd.Parameters.Add(cmd, "@MediaTypes", DbType.String).Value = query.MediaTypes[0]; } if (query.MediaTypes.Length > 1) { @@ -2087,8 +3132,96 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add("MediaType in (" + val + ")"); } + if (query.ItemIds.Length > 0) + { + var excludeIds = new List<string>(); - var enableItemsByName = query.IncludeItemsByName ?? query.IncludeItemTypes.Length > 0; + 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>(); + + var index = 0; + foreach (var id in query.ExcludeItemIds) + { + excludeIds.Add("Guid <> @ExcludeId" + index); + cmd.Parameters.Add(cmd, "@ExcludeId" + index, DbType.Guid).Value = new Guid(id); + index++; + } + + whereClauses.Add(string.Join(" AND ", excludeIds.ToArray())); + } + + if (query.ExcludeProviderIds.Count > 0) + { + var excludeIds = new List<string>(); + + var index = 0; + foreach (var pair in query.ExcludeProviderIds) + { + if (string.Equals(pair.Key, MetadataProviders.TmdbCollection.ToString(), StringComparison.OrdinalIgnoreCase)) + { + continue; + } + + var paramName = "@ExcludeProviderId" + index; + excludeIds.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = '" + pair.Key + "'), '') <> " + paramName + ")"); + cmd.Parameters.Add(cmd, paramName, DbType.String).Value = pair.Value; + index++; + } + + whereClauses.Add(string.Join(" AND ", excludeIds.ToArray())); + } + + if (query.HasImdbId.HasValue) + { + var fn = query.HasImdbId.Value ? "<>" : "="; + whereClauses.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = 'Imdb'), '') " + fn + " '')"); + } + + if (query.HasTmdbId.HasValue) + { + var fn = query.HasTmdbId.Value ? "<>" : "="; + whereClauses.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = 'Tmdb'), '') " + fn + " '')"); + } + + if (query.HasTvdbId.HasValue) + { + var fn = query.HasTvdbId.Value ? "<>" : "="; + whereClauses.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = 'Tvdb'), '') " + fn + " '')"); + } + + if (query.AlbumNames.Length > 0) + { + var clause = "("; + + var index = 0; + foreach (var name in query.AlbumNames) + { + if (index > 0) + { + clause += " OR "; + } + clause += "Album=@AlbumName" + index; + cmd.Parameters.Add(cmd, "@AlbumName" + index, DbType.String).Value = name; + index++; + } + + clause += ")"; + whereClauses.Add(clause); + } + + //var enableItemsByName = query.IncludeItemsByName ?? query.IncludeItemTypes.Length > 0; + var enableItemsByName = query.IncludeItemsByName ?? false; if (query.TopParentIds.Length == 1) { @@ -2128,6 +3261,12 @@ namespace MediaBrowser.Server.Implementations.Persistence var inClause = string.Join(",", query.AncestorIds.Select(i => "'" + new Guid(i).ToString("N") + "'").ToArray()); whereClauses.Add(string.Format("Guid in (select itemId from AncestorIds where AncestorIdText in ({0}))", inClause)); } + if (!string.IsNullOrWhiteSpace(query.AncestorWithPresentationUniqueKey)) + { + var inClause = "select guid from TypedBaseItems where PresentationUniqueKey=@AncestorWithPresentationUniqueKey"; + whereClauses.Add(string.Format("Guid in (select itemId from AncestorIds where AncestorId in ({0}))", inClause)); + cmd.Parameters.Add(cmd, "@AncestorWithPresentationUniqueKey", DbType.String).Value = query.AncestorWithPresentationUniqueKey; + } if (query.BlockUnratedItems.Length == 1) { @@ -2143,28 +3282,58 @@ namespace MediaBrowser.Server.Implementations.Persistence var excludeTagIndex = 0; foreach (var excludeTag in query.ExcludeTags) { - whereClauses.Add("Tags not like @excludeTag" + excludeTagIndex); + whereClauses.Add("(Tags is null OR Tags not like @excludeTag" + excludeTagIndex + ")"); cmd.Parameters.Add(cmd, "@excludeTag" + excludeTagIndex, DbType.String).Value = "%" + excludeTag + "%"; excludeTagIndex++; } - if (addPaging) + excludeTagIndex = 0; + foreach (var excludeTag in query.ExcludeInheritedTags) { - if (query.StartIndex.HasValue && query.StartIndex.Value > 0) - { - var pagingWhereText = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + whereClauses.Add("(InheritedTags is null OR InheritedTags not like @excludeInheritedTag" + excludeTagIndex + ")"); + cmd.Parameters.Add(cmd, "@excludeInheritedTag" + excludeTagIndex, DbType.String).Value = "%" + excludeTag + "%"; + excludeTagIndex++; + } - var orderBy = GetOrderByText(query); + return whereClauses; + } - whereClauses.Add(string.Format("guid NOT IN (SELECT guid FROM TypedBaseItems {0}" + orderBy + " LIMIT {1})", - pagingWhereText, - query.StartIndex.Value.ToString(CultureInfo.InvariantCulture))); - } + private bool EnableGroupByPresentationUniqueKey(InternalItemsQuery query) + { + if (!query.GroupByPresentationUniqueKey) + { + return false; } - return whereClauses; + if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey)) + { + return false; + } + + if (query.User == null) + { + return false; + } + + if (query.IncludeItemTypes.Length == 0) + { + return true; + } + + var types = new[] { + typeof(Episode).Name, + typeof(Video).Name , + typeof(Movie).Name , + typeof(MusicVideo).Name , + typeof(Series).Name , + typeof(Season).Name }; + + if (types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase))) + { + return true; + } + + return false; } private static readonly Type[] KnownTypes = @@ -2208,6 +3377,88 @@ namespace MediaBrowser.Server.Implementations.Persistence public async Task UpdateInheritedValues(CancellationToken cancellationToken) { + await UpdateInheritedParentalRating(cancellationToken).ConfigureAwait(false); + await UpdateInheritedTags(cancellationToken).ConfigureAwait(false); + } + + private async Task UpdateInheritedTags(CancellationToken cancellationToken) + { + var newValues = new List<Tuple<Guid, string>>(); + + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "select Guid,InheritedTags,(select group_concat(Tags, '|') from TypedBaseItems where (guid=outer.guid) OR (guid in (Select AncestorId from AncestorIds where ItemId=Outer.guid))) as NewInheritedTags from typedbaseitems as Outer where NewInheritedTags <> InheritedTags"; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + var id = reader.GetGuid(0); + string value = reader.IsDBNull(2) ? null : reader.GetString(2); + + newValues.Add(new Tuple<Guid, string>(id, value)); + } + } + } + + Logger.Debug("UpdateInheritedTags - {0} rows", newValues.Count); + if (newValues.Count == 0) + { + return; + } + + await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); + + IDbTransaction transaction = null; + + try + { + transaction = _connection.BeginTransaction(); + + foreach (var item in newValues) + { + _updateInheritedTagsCommand.GetParameter(0).Value = item.Item1; + _updateInheritedTagsCommand.GetParameter(1).Value = item.Item2; + + _updateInheritedTagsCommand.Transaction = transaction; + _updateInheritedTagsCommand.ExecuteNonQuery(); + } + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + catch (Exception e) + { + Logger.ErrorException("Error running query:", e); + + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + + WriteLock.Release(); + } + } + + private async Task UpdateInheritedParentalRating(CancellationToken cancellationToken) + { var newValues = new List<Tuple<Guid, int>>(); using (var cmd = _connection.CreateCommand()) @@ -2226,6 +3477,7 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + Logger.Debug("UpdateInheritedParentalRatings - {0} rows", newValues.Count); if (newValues.Count == 0) { return; @@ -2283,7 +3535,7 @@ namespace MediaBrowser.Server.Implementations.Persistence private static Dictionary<string, string[]> GetTypeMapDictionary() { - var dict = new Dictionary<string, string[]>(); + var dict = new Dictionary<string, string[]>(StringComparer.OrdinalIgnoreCase); foreach (var t in KnownTypes) { @@ -2348,6 +3600,26 @@ namespace MediaBrowser.Server.Implementations.Persistence _deleteAncestorsCommand.Transaction = transaction; _deleteAncestorsCommand.ExecuteNonQuery(); + // Delete user data keys + _deleteUserDataKeysCommand.GetParameter(0).Value = id; + _deleteUserDataKeysCommand.Transaction = transaction; + _deleteUserDataKeysCommand.ExecuteNonQuery(); + + // Delete item values + _deleteItemValuesCommand.GetParameter(0).Value = id; + _deleteItemValuesCommand.Transaction = transaction; + _deleteItemValuesCommand.ExecuteNonQuery(); + + // Delete provider ids + _deleteProviderIdsCommand.GetParameter(0).Value = id; + _deleteProviderIdsCommand.Transaction = transaction; + _deleteProviderIdsCommand.ExecuteNonQuery(); + + // Delete images + _deleteImagesCommand.GetParameter(0).Value = id; + _deleteImagesCommand.Transaction = transaction; + _deleteImagesCommand.ExecuteNonQuery(); + // Delete the item _deleteItemCommand.GetParameter(0).Value = id; _deleteItemCommand.Transaction = transaction; @@ -2540,6 +3812,490 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + 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) + { + var itemCountColumnQuery = "select group_concat(type, '|')" + GetFromText("B"); + + var typeSubQuery = 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 whereClauses = GetWhereClauses(typeSubQuery, cmd, "itemTypes"); + + whereClauses.Add("guid in (select ItemId from ItemValues where ItemValues.CleanValue=A.CleanName AND Type=@ItemValueType)"); + + var typeWhereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); + + itemCountColumnQuery += typeWhereText; + + //itemCountColumnQuery += ")"; + + itemCountColumns.Add(new Tuple<string, string>("itemTypes", "(" + itemCountColumnQuery + ") as itemTypes")); + } + + 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"; + + if (typesToCount.Count == 0) + { + whereText += " And CleanName In (Select CleanValue from ItemValues where Type=@ItemValueType AND ItemId in (select guid from TypedBaseItems" + innerWhereText + "))"; + } + else + { + //whereText += " And itemTypes not null"; + 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; + + //Logger.Debug("GetItemValues: " + cmd.CommandText); + + 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 - 1; + + 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(); + + if (typesToCount.Count == 0) + { + return counts; + } + + var typeString = reader.IsDBNull(countStartColumn) ? null : reader.GetString(countStartColumn); + + if (string.IsNullOrWhiteSpace(typeString)) + { + return counts; + } + + var allTypes = typeString.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries) + .ToLookup(i => i).ToList(); + + foreach (var type in allTypes) + { + var value = type.ToList().Count; + var typeName = type.Key; + + if (string.Equals(typeName, typeof(Series).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.SeriesCount = value; + } + else if (string.Equals(typeName, typeof(Episode).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.EpisodeCount = value; + } + else if (string.Equals(typeName, typeof(Movie).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.MovieCount = value; + } + else if (string.Equals(typeName, typeof(MusicAlbum).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.AlbumCount = value; + } + else if (string.Equals(typeName, typeof(Audio).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.SongCount = value; + } + else if (string.Equals(typeName, typeof(Game).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.GameCount = value; + } + else if (string.Equals(typeName, typeof(Trailer).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.TrailerCount = value; + } + counts.ItemCount += value; + } + + return counts; + } + + private List<Tuple<int, string>> GetItemValuesToSave(BaseItem item) + { + var list = new List<Tuple<int, string>>(); + + var hasArtist = item as IHasArtist; + if (hasArtist != null) + { + list.AddRange(hasArtist.Artists.Select(i => new Tuple<int, string>(0, i))); + } + + var hasAlbumArtist = item as IHasAlbumArtist; + if (hasAlbumArtist != null) + { + list.AddRange(hasAlbumArtist.AlbumArtists.Select(i => new Tuple<int, string>(1, i))); + } + + list.AddRange(item.Genres.Select(i => new Tuple<int, string>(2, i))); + list.AddRange(item.Studios.Select(i => new Tuple<int, string>(3, i))); + list.AddRange(item.Tags.Select(i => new Tuple<int, string>(4, i))); + list.AddRange(item.Keywords.Select(i => new Tuple<int, string>(5, i))); + + return list; + } + + private void UpdateImages(Guid itemId, List<ItemImageInfo> images, IDbTransaction transaction) + { + if (itemId == Guid.Empty) + { + throw new ArgumentNullException("itemId"); + } + + if (images == null) + { + throw new ArgumentNullException("images"); + } + + CheckDisposed(); + + // First delete + _deleteImagesCommand.GetParameter(0).Value = itemId; + _deleteImagesCommand.Transaction = transaction; + + _deleteImagesCommand.ExecuteNonQuery(); + + var index = 0; + foreach (var image in images) + { + _saveImagesCommand.GetParameter(0).Value = itemId; + _saveImagesCommand.GetParameter(1).Value = image.Type; + _saveImagesCommand.GetParameter(2).Value = image.Path; + + if (image.DateModified == default(DateTime)) + { + _saveImagesCommand.GetParameter(3).Value = null; + } + else + { + _saveImagesCommand.GetParameter(3).Value = image.DateModified; + } + + _saveImagesCommand.GetParameter(4).Value = image.IsPlaceholder; + _saveImagesCommand.GetParameter(5).Value = index; + + _saveImagesCommand.Transaction = transaction; + + _saveImagesCommand.ExecuteNonQuery(); + index++; + } + } + + private void UpdateProviderIds(Guid itemId, Dictionary<string, string> values, IDbTransaction transaction) + { + if (itemId == Guid.Empty) + { + throw new ArgumentNullException("itemId"); + } + + if (values == null) + { + throw new ArgumentNullException("values"); + } + + // Just in case there might be case-insensitive duplicates, strip them out now + var newValues = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase); + foreach (var pair in values) + { + newValues[pair.Key] = pair.Value; + } + + CheckDisposed(); + + // First delete + _deleteProviderIdsCommand.GetParameter(0).Value = itemId; + _deleteProviderIdsCommand.Transaction = transaction; + + _deleteProviderIdsCommand.ExecuteNonQuery(); + + foreach (var pair in newValues) + { + _saveProviderIdsCommand.GetParameter(0).Value = itemId; + _saveProviderIdsCommand.GetParameter(1).Value = pair.Key; + _saveProviderIdsCommand.GetParameter(2).Value = pair.Value; + _saveProviderIdsCommand.Transaction = transaction; + + _saveProviderIdsCommand.ExecuteNonQuery(); + } + } + + private void UpdateItemValues(Guid itemId, List<Tuple<int, string>> values, IDbTransaction transaction) + { + if (itemId == Guid.Empty) + { + throw new ArgumentNullException("itemId"); + } + + if (values == null) + { + throw new ArgumentNullException("keys"); + } + + CheckDisposed(); + + // First delete + _deleteItemValuesCommand.GetParameter(0).Value = itemId; + _deleteItemValuesCommand.Transaction = transaction; + + _deleteItemValuesCommand.ExecuteNonQuery(); + + foreach (var pair in values) + { + _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(); + } + } + + private void UpdateUserDataKeys(Guid itemId, List<string> keys, IDbTransaction transaction) + { + if (itemId == Guid.Empty) + { + throw new ArgumentNullException("itemId"); + } + + if (keys == null) + { + throw new ArgumentNullException("keys"); + } + + CheckDisposed(); + + // First delete + _deleteUserDataKeysCommand.GetParameter(0).Value = itemId; + _deleteUserDataKeysCommand.Transaction = transaction; + + _deleteUserDataKeysCommand.ExecuteNonQuery(); + var index = 0; + + foreach (var key in keys) + { + _saveUserDataKeysCommand.GetParameter(0).Value = itemId; + _saveUserDataKeysCommand.GetParameter(1).Value = key; + _saveUserDataKeysCommand.GetParameter(2).Value = index; + index++; + _saveUserDataKeysCommand.Transaction = transaction; + + _saveUserDataKeysCommand.ExecuteNonQuery(); + } + } + public async Task UpdatePeople(Guid itemId, List<PersonInfo> people) { if (itemId == Guid.Empty) @@ -2656,6 +4412,8 @@ namespace MediaBrowser.Server.Implementations.Persistence throw new ArgumentNullException("query"); } + var list = new List<MediaStream>(); + using (var cmd = _connection.CreateCommand()) { var cmdText = "select " + string.Join(",", _mediaStreamSaveColumns) + " from mediastreams where"; @@ -2683,13 +4441,15 @@ namespace MediaBrowser.Server.Implementations.Persistence { while (reader.Read()) { - yield return GetMediaStream(reader); + list.Add(GetMediaStream(reader)); } } } + + 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(); @@ -2755,10 +4515,15 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveStreamCommand.GetParameter(index++).Value = stream.BitDepth; _saveStreamCommand.GetParameter(index++).Value = stream.IsAnamorphic; _saveStreamCommand.GetParameter(index++).Value = stream.RefFrames; - _saveStreamCommand.GetParameter(index++).Value = stream.IsCabac; _saveStreamCommand.GetParameter(index++).Value = stream.CodecTag; _saveStreamCommand.GetParameter(index++).Value = stream.Comment; + _saveStreamCommand.GetParameter(index++).Value = stream.NalLengthSize; + _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(); @@ -2909,17 +4674,37 @@ namespace MediaBrowser.Server.Implementations.Persistence if (!reader.IsDBNull(25)) { - item.IsCabac = reader.GetBoolean(25); + item.CodecTag = reader.GetString(25); } if (!reader.IsDBNull(26)) { - item.CodecTag = reader.GetString(26); + item.Comment = reader.GetString(26); } if (!reader.IsDBNull(27)) { - item.Comment = reader.GetString(27); + item.NalLengthSize = reader.GetString(27); + } + + if (!reader.IsDBNull(28)) + { + item.IsAVC = reader.GetBoolean(28); + } + + if (!reader.IsDBNull(29)) + { + 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; |