diff options
Diffstat (limited to 'Emby.Server.Implementations/Data')
8 files changed, 1447 insertions, 851 deletions
diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index d207c8d4f..76ebff3a8 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -189,6 +189,26 @@ namespace Emby.Server.Implementations.Data return sql.Select(connection.PrepareStatement).ToList(); } + protected bool TableExists(ManagedConnection connection, string name) + { + return connection.RunInTransaction(db => + { + using (var statement = PrepareStatement(db, "select DISTINCT tbl_name from sqlite_master")) + { + foreach (var row in statement.ExecuteQuery()) + { + if (string.Equals(name, row.GetString(0), StringComparison.OrdinalIgnoreCase)) + { + return true; + } + } + } + + return false; + + }, ReadTransactionMode); + } + protected void RunDefaultInitialization(ManagedConnection db) { var queries = new List<string> @@ -264,7 +284,6 @@ namespace Emby.Server.Implementations.Data { _disposed = true; Dispose(true); - GC.SuppressFinalize(this); } private readonly object _disposeLock = new object(); diff --git a/Emby.Server.Implementations/Data/CleanDatabaseScheduledTask.cs b/Emby.Server.Implementations/Data/CleanDatabaseScheduledTask.cs index 37ba2eb5f..8611cabc1 100644 --- a/Emby.Server.Implementations/Data/CleanDatabaseScheduledTask.cs +++ b/Emby.Server.Implementations/Data/CleanDatabaseScheduledTask.cs @@ -33,10 +33,11 @@ namespace Emby.Server.Implementations.Data public Task Run(IProgress<double> progress, CancellationToken cancellationToken) { - return CleanDeadItems(cancellationToken, progress); + CleanDeadItems(cancellationToken, progress); + return Task.CompletedTask; } - private async Task CleanDeadItems(CancellationToken cancellationToken, IProgress<double> progress) + private void CleanDeadItems(CancellationToken cancellationToken, IProgress<double> progress) { var itemIds = _libraryManager.GetItemIds(new InternalItemsQuery { @@ -58,11 +59,11 @@ namespace Emby.Server.Implementations.Data { _logger.Info("Cleaning item {0} type: {1} path: {2}", item.Name, item.GetType().Name, item.Path ?? string.Empty); - await item.Delete(new DeleteOptions + _libraryManager.DeleteItem(item, new DeleteOptions { DeleteFileLocation = false - }).ConfigureAwait(false); + }); } numComplete++; diff --git a/Emby.Server.Implementations/Data/ManagedConnection.cs b/Emby.Server.Implementations/Data/ManagedConnection.cs index 5d0fc8ebc..91a2dfdf6 100644 --- a/Emby.Server.Implementations/Data/ManagedConnection.cs +++ b/Emby.Server.Implementations/Data/ManagedConnection.cs @@ -77,7 +77,6 @@ namespace Emby.Server.Implementations.Data { Close(); } - GC.SuppressFinalize(this); } } } diff --git a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs index e6afcd410..09ff7e09d 100644 --- a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs @@ -18,14 +18,12 @@ namespace Emby.Server.Implementations.Data /// </summary> public class SqliteDisplayPreferencesRepository : BaseSqliteRepository, IDisplayPreferencesRepository { - private readonly IMemoryStreamFactory _memoryStreamProvider; protected IFileSystem FileSystem { get; private set; } - public SqliteDisplayPreferencesRepository(ILogger logger, IJsonSerializer jsonSerializer, IApplicationPaths appPaths, IMemoryStreamFactory memoryStreamProvider, IFileSystem fileSystem) + public SqliteDisplayPreferencesRepository(ILogger logger, IJsonSerializer jsonSerializer, IApplicationPaths appPaths, IFileSystem fileSystem) : base(logger) { _jsonSerializer = jsonSerializer; - _memoryStreamProvider = memoryStreamProvider; FileSystem = fileSystem; DbFilePath = Path.Combine(appPaths.DataPath, "displaypreferences.db"); } @@ -98,7 +96,7 @@ namespace Emby.Server.Implementations.Data { throw new ArgumentNullException("displayPreferences"); } - if (string.IsNullOrWhiteSpace(displayPreferences.Id)) + if (string.IsNullOrEmpty(displayPreferences.Id)) { throw new ArgumentNullException("displayPreferences.Id"); } @@ -119,7 +117,7 @@ namespace Emby.Server.Implementations.Data private void SaveDisplayPreferences(DisplayPreferences displayPreferences, Guid userId, string client, IDatabaseConnection connection) { - var serialized = _jsonSerializer.SerializeToBytes(displayPreferences, _memoryStreamProvider); + var serialized = _jsonSerializer.SerializeToBytes(displayPreferences); using (var statement = connection.PrepareStatement("replace into userdisplaypreferences (id, userid, client, data) values (@id, @userId, @client, @data)")) { @@ -174,7 +172,7 @@ namespace Emby.Server.Implementations.Data /// <exception cref="System.ArgumentNullException">item</exception> public DisplayPreferences GetDisplayPreferences(string displayPreferencesId, Guid userId, string client) { - if (string.IsNullOrWhiteSpace(displayPreferencesId)) + if (string.IsNullOrEmpty(displayPreferencesId)) { throw new ArgumentNullException("displayPreferencesId"); } @@ -236,7 +234,7 @@ namespace Emby.Server.Implementations.Data private DisplayPreferences Get(IReadOnlyList<IResultSetValue> row) { - using (var stream = _memoryStreamProvider.CreateNew(row[0].ToBlob())) + using (var stream = new MemoryStream(row[0].ToBlob())) { stream.Position = 0; return _jsonSerializer.DeserializeFromStream<DisplayPreferences>(stream); diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index d2c851b3c..a755c65f4 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -4,6 +4,7 @@ using System.Globalization; using MediaBrowser.Model.IO; using MediaBrowser.Model.Serialization; using SQLitePCL.pretty; +using System.IO; namespace Emby.Server.Implementations.Data { @@ -110,19 +111,33 @@ namespace Emby.Server.Implementations.Data DateTimeStyles.None).ToUniversalTime(); } + public static DateTime? TryReadDateTime(this IResultSetValue result) + { + var dateText = result.ToString(); + + DateTime dateTimeResult; + + if (DateTime.TryParseExact(dateText, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.None, out dateTimeResult)) + { + return dateTimeResult.ToUniversalTime(); + } + + return null; + } + /// <summary> /// Serializes to bytes. /// </summary> /// <returns>System.Byte[][].</returns> /// <exception cref="System.ArgumentNullException">obj</exception> - public static byte[] SerializeToBytes(this IJsonSerializer json, object obj, IMemoryStreamFactory streamProvider) + public static byte[] SerializeToBytes(this IJsonSerializer json, object obj) { if (obj == null) { throw new ArgumentNullException("obj"); } - using (var stream = streamProvider.CreateNew()) + using (var stream = new MemoryStream()) { json.SerializeToStream(obj, stream); return stream.ToArray(); diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 830d6447e..bde28c923 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -32,6 +32,9 @@ using SQLitePCL.pretty; using MediaBrowser.Model.System; using MediaBrowser.Model.Threading; using MediaBrowser.Model.Extensions; +using MediaBrowser.Controller; +using MediaBrowser.Controller.Drawing; +using MediaBrowser.Controller.Library; namespace Emby.Server.Implementations.Data { @@ -65,18 +68,16 @@ namespace Emby.Server.Implementations.Data /// </summary> private readonly IServerConfigurationManager _config; - private readonly string _criticReviewsPath; - - private readonly IMemoryStreamFactory _memoryStreamProvider; private readonly IFileSystem _fileSystem; private readonly IEnvironmentInfo _environmentInfo; - private readonly ITimerFactory _timerFactory; - private ITimer _shrinkMemoryTimer; + private IServerApplicationHost _appHost; + + public IImageProcessor ImageProcessor { get; set; } /// <summary> /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. /// </summary> - public SqliteItemRepository(IServerConfigurationManager config, IJsonSerializer jsonSerializer, ILogger logger, IMemoryStreamFactory memoryStreamProvider, IAssemblyInfo assemblyInfo, IFileSystem fileSystem, IEnvironmentInfo environmentInfo, ITimerFactory timerFactory) + public SqliteItemRepository(IServerConfigurationManager config, IServerApplicationHost appHost, IJsonSerializer jsonSerializer, ILogger logger, IAssemblyInfo assemblyInfo, IFileSystem fileSystem, IEnvironmentInfo environmentInfo, ITimerFactory timerFactory) : base(logger) { if (config == null) @@ -88,15 +89,13 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException("jsonSerializer"); } + _appHost = appHost; _config = config; _jsonSerializer = jsonSerializer; - _memoryStreamProvider = memoryStreamProvider; _fileSystem = fileSystem; _environmentInfo = environmentInfo; - _timerFactory = timerFactory; _typeMapper = new TypeMapper(assemblyInfo); - _criticReviewsPath = Path.Combine(_config.ApplicationPaths.DataPath, "critic-reviews"); DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); } @@ -118,28 +117,17 @@ namespace Emby.Server.Implementations.Data } } - protected override void CloseConnection() - { - if (_shrinkMemoryTimer != null) - { - _shrinkMemoryTimer.Dispose(); - _shrinkMemoryTimer = null; - } - - base.CloseConnection(); - } - /// <summary> /// Opens the connection to the database /// </summary> - public void Initialize(SqliteUserDataRepository userDataRepo) + public void Initialize(SqliteUserDataRepository userDataRepo, IUserManager userManager) { using (var connection = CreateConnection()) { RunDefaultInitialization(connection); 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, TimeBase TEXT NULL, CodecTimeBase 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, ColorPrimaries TEXT NULL, ColorSpace TEXT NULL, ColorTransfer TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))"; string[] queries = { "PRAGMA locking_mode=EXCLUSIVE", @@ -162,8 +150,6 @@ namespace Emby.Server.Implementations.Data createMediaStreamsTableCommand, - "create index if not exists idx_mediastreams1 on mediastreams(ItemId)", - "pragma shrink_memory" }; @@ -182,8 +168,6 @@ namespace Emby.Server.Implementations.Data AddColumn(db, "TypedBaseItems", "EndDate", "DATETIME", existingColumnNames); AddColumn(db, "TypedBaseItems", "ChannelId", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "IsMovie", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsSports", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsKids", "BIT", existingColumnNames); AddColumn(db, "TypedBaseItems", "CommunityRating", "Float", existingColumnNames); AddColumn(db, "TypedBaseItems", "CustomRating", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "IndexNumber", "INT", existingColumnNames); @@ -200,19 +184,13 @@ namespace Emby.Server.Implementations.Data AddColumn(db, "TypedBaseItems", "SortName", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "ForcedSortName", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "RunTimeTicks", "BIGINT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "HomePageUrl", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "DateCreated", "DATETIME", existingColumnNames); AddColumn(db, "TypedBaseItems", "DateModified", "DATETIME", existingColumnNames); AddColumn(db, "TypedBaseItems", "IsSeries", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsLive", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsNews", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsPremiere", "BIT", existingColumnNames); AddColumn(db, "TypedBaseItems", "EpisodeTitle", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "IsRepeat", "BIT", existingColumnNames); AddColumn(db, "TypedBaseItems", "PreferredMetadataLanguage", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "PreferredMetadataCountryCode", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsHD", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ExternalEtag", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "DateLastRefreshed", "DATETIME", existingColumnNames); AddColumn(db, "TypedBaseItems", "DateLastSaved", "DATETIME", existingColumnNames); AddColumn(db, "TypedBaseItems", "IsInMixedFolder", "BIT", existingColumnNames); @@ -244,8 +222,7 @@ namespace Emby.Server.Implementations.Data AddColumn(db, "TypedBaseItems", "ProviderIds", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "Images", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "ProductionLocations", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ThemeSongIds", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ThemeVideoIds", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "ExtraIds", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "TotalBitrate", "INT", existingColumnNames); AddColumn(db, "TypedBaseItems", "ExtraType", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "Artists", "Text", existingColumnNames); @@ -254,6 +231,8 @@ namespace Emby.Server.Implementations.Data AddColumn(db, "TypedBaseItems", "SeriesPresentationUniqueKey", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "ShowId", "Text", existingColumnNames); AddColumn(db, "TypedBaseItems", "OwnerId", "Text", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Width", "INT", existingColumnNames); + AddColumn(db, "TypedBaseItems", "Height", "INT", existingColumnNames); existingColumnNames = GetColumnNames(db, "ItemValues"); AddColumn(db, "ItemValues", "CleanValue", "Text", existingColumnNames); @@ -274,6 +253,11 @@ namespace Emby.Server.Implementations.Data AddColumn(db, "MediaStreams", "RefFrames", "INT", existingColumnNames); AddColumn(db, "MediaStreams", "KeyFrames", "TEXT", existingColumnNames); AddColumn(db, "MediaStreams", "IsAnamorphic", "BIT", existingColumnNames); + + AddColumn(db, "MediaStreams", "ColorPrimaries", "TEXT", existingColumnNames); + AddColumn(db, "MediaStreams", "ColorSpace", "TEXT", existingColumnNames); + AddColumn(db, "MediaStreams", "ColorTransfer", "TEXT", existingColumnNames); + }, TransactionMode); string[] postQueries = @@ -282,6 +266,7 @@ namespace Emby.Server.Implementations.Data // obsolete "drop index if exists idx_TypedBaseItems", "drop index if exists idx_mediastreams", + "drop index if exists idx_mediastreams1", "drop index if exists idx_"+ChaptersTableName, "drop index if exists idx_UserDataKeys1", "drop index if exists idx_UserDataKeys2", @@ -316,7 +301,6 @@ namespace Emby.Server.Implementations.Data "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 @@ -359,32 +343,7 @@ namespace Emby.Server.Implementations.Data //await Vacuum(_connection).ConfigureAwait(false); } - userDataRepo.Initialize(WriteLock, _connection); - - _shrinkMemoryTimer = _timerFactory.Create(OnShrinkMemoryTimerCallback, null, TimeSpan.FromMinutes(1), TimeSpan.FromMinutes(30)); - } - - private void OnShrinkMemoryTimerCallback(object state) - { - try - { - using (WriteLock.Write()) - { - using (var connection = CreateConnection()) - { - connection.RunQueries(new string[] - { - "pragma shrink_memory" - }); - } - } - - GC.Collect(); - } - catch (Exception ex) - { - Logger.ErrorException("Error running shrink memory", ex); - } + userDataRepo.Initialize(WriteLock, _connection, userManager); } private readonly string[] _retriveItemColumns = @@ -395,12 +354,7 @@ namespace Emby.Server.Implementations.Data "EndDate", "ChannelId", "IsMovie", - "IsSports", - "IsKids", "IsSeries", - "IsLive", - "IsNews", - "IsPremiere", "EpisodeTitle", "IsRepeat", "CommunityRating", @@ -409,8 +363,8 @@ namespace Emby.Server.Implementations.Data "IsLocked", "PreferredMetadataLanguage", "PreferredMetadataCountryCode", - "IsHD", - "ExternalEtag", + "Width", + "Height", "DateLastRefreshed", "Name", "Path", @@ -419,7 +373,6 @@ namespace Emby.Server.Implementations.Data "ParentIndexNumber", "ProductionYear", "OfficialRating", - "HomePageUrl", "ForcedSortName", "RunTimeTicks", "DateCreated", @@ -452,8 +405,7 @@ namespace Emby.Server.Implementations.Data "ProviderIds", "Images", "ProductionLocations", - "ThemeSongIds", - "ThemeVideoIds", + "ExtraIds", "TotalBitrate", "ExtraType", "Artists", @@ -497,7 +449,10 @@ namespace Emby.Server.Implementations.Data "IsAvc", "Title", "TimeBase", - "CodecTimeBase" + "CodecTimeBase", + "ColorPrimaries", + "ColorSpace", + "ColorTransfer" }; private string GetSaveItemCommandText() @@ -511,13 +466,8 @@ namespace Emby.Server.Implementations.Data "StartDate", "EndDate", "ChannelId", - "IsKids", "IsMovie", - "IsSports", "IsSeries", - "IsLive", - "IsNews", - "IsPremiere", "EpisodeTitle", "IsRepeat", "CommunityRating", @@ -537,13 +487,12 @@ namespace Emby.Server.Implementations.Data "SortName", "ForcedSortName", "RunTimeTicks", - "HomePageUrl", "DateCreated", "DateModified", "PreferredMetadataLanguage", "PreferredMetadataCountryCode", - "IsHD", - "ExternalEtag", + "Width", + "Height", "DateLastRefreshed", "DateLastSaved", "IsInMixedFolder", @@ -574,8 +523,7 @@ namespace Emby.Server.Implementations.Data "ProviderIds", "Images", "ProductionLocations", - "ThemeSongIds", - "ThemeVideoIds", + "ExtraIds", "TotalBitrate", "ExtraType", "Artists", @@ -699,46 +647,58 @@ namespace Emby.Server.Implementations.Data var statements = PrepareAllSafe(db, new string[] { GetSaveItemCommandText(), - "delete from AncestorIds where ItemId=@ItemId", - "insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values (@ItemId, @AncestorId, @AncestorIdText)" + "delete from AncestorIds where ItemId=@ItemId" + }).ToList(); using (var saveItemStatement = statements[0]) { using (var deleteAncestorsStatement = statements[1]) { - using (var updateAncestorsStatement = statements[2]) + foreach (var tuple in tuples) { - foreach (var tuple in tuples) + if (requiresReset) { - if (requiresReset) - { - saveItemStatement.Reset(); - } + saveItemStatement.Reset(); + } - var item = tuple.Item1; - var topParent = tuple.Item3; - var userDataKey = tuple.Item4; + var item = tuple.Item1; + var topParent = tuple.Item3; + var userDataKey = tuple.Item4; - SaveItem(item, topParent, userDataKey, saveItemStatement); - //Logger.Debug(_saveItemCommand.CommandText); + SaveItem(item, topParent, userDataKey, saveItemStatement); + //Logger.Debug(_saveItemCommand.CommandText); - var inheritedTags = tuple.Item5; + var inheritedTags = tuple.Item5; - if (item.SupportsAncestors) - { - UpdateAncestors(item.Id, tuple.Item2, db, deleteAncestorsStatement, updateAncestorsStatement); - } + if (item.SupportsAncestors) + { + UpdateAncestors(item.Id, tuple.Item2, db, deleteAncestorsStatement); + } - UpdateItemValues(item.Id, GetItemValuesToSave(item, inheritedTags), db); + UpdateItemValues(item.Id, GetItemValuesToSave(item, inheritedTags), db); - requiresReset = true; - } + requiresReset = true; } } } } + private string GetPathToSave(string path) + { + if (path == null) + { + return null; + } + + return _appHost.ReverseVirtualPath(path); + } + + private string RestorePath(string path) + { + return _appHost.ExpandVirtualPath(path); + } + private void SaveItem(BaseItem item, BaseItem topParent, string userDataKey, IStatement saveItemStatement) { saveItemStatement.TryBind("@guid", item.Id); @@ -746,14 +706,14 @@ namespace Emby.Server.Implementations.Data if (TypeRequiresDeserialization(item.GetType())) { - saveItemStatement.TryBind("@data", _jsonSerializer.SerializeToBytes(item, _memoryStreamProvider)); + saveItemStatement.TryBind("@data", _jsonSerializer.SerializeToBytes(item)); } else { saveItemStatement.TryBindNull("@data"); } - saveItemStatement.TryBind("@Path", item.Path); + saveItemStatement.TryBind("@Path", GetPathToSave(item.Path)); var hasStartDate = item as IHasStartDate; if (hasStartDate != null) @@ -774,30 +734,20 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBindNull("@EndDate"); } - saveItemStatement.TryBind("@ChannelId", item.ChannelId); + saveItemStatement.TryBind("@ChannelId", item.ChannelId.Equals(Guid.Empty) ? null : item.ChannelId.ToString("N")); var hasProgramAttributes = item as IHasProgramAttributes; if (hasProgramAttributes != null) { - saveItemStatement.TryBind("@IsKids", hasProgramAttributes.IsKids); saveItemStatement.TryBind("@IsMovie", hasProgramAttributes.IsMovie); - saveItemStatement.TryBind("@IsSports", hasProgramAttributes.IsSports); saveItemStatement.TryBind("@IsSeries", hasProgramAttributes.IsSeries); - saveItemStatement.TryBind("@IsLive", hasProgramAttributes.IsLive); - saveItemStatement.TryBind("@IsNews", hasProgramAttributes.IsNews); - saveItemStatement.TryBind("@IsPremiere", hasProgramAttributes.IsPremiere); saveItemStatement.TryBind("@EpisodeTitle", hasProgramAttributes.EpisodeTitle); saveItemStatement.TryBind("@IsRepeat", hasProgramAttributes.IsRepeat); } else { - saveItemStatement.TryBindNull("@IsKids"); saveItemStatement.TryBindNull("@IsMovie"); - saveItemStatement.TryBindNull("@IsSports"); saveItemStatement.TryBindNull("@IsSeries"); - saveItemStatement.TryBindNull("@IsLive"); - saveItemStatement.TryBindNull("@IsNews"); - saveItemStatement.TryBindNull("@IsPremiere"); saveItemStatement.TryBindNull("@EpisodeTitle"); saveItemStatement.TryBindNull("@IsRepeat"); } @@ -815,7 +765,7 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@ProductionYear", item.ProductionYear); var parentId = item.ParentId; - if (parentId == Guid.Empty) + if (parentId.Equals(Guid.Empty)) { saveItemStatement.TryBindNull("@ParentId"); } @@ -824,9 +774,9 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@ParentId", parentId); } - if (item.Genres.Count > 0) + if (item.Genres.Length > 0) { - saveItemStatement.TryBind("@Genres", string.Join("|", item.Genres.ToArray())); + saveItemStatement.TryBind("@Genres", string.Join("|", item.Genres)); } else { @@ -841,14 +791,28 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@RunTimeTicks", item.RunTimeTicks); - saveItemStatement.TryBind("@HomePageUrl", item.HomePageUrl); saveItemStatement.TryBind("@DateCreated", item.DateCreated); saveItemStatement.TryBind("@DateModified", item.DateModified); saveItemStatement.TryBind("@PreferredMetadataLanguage", item.PreferredMetadataLanguage); saveItemStatement.TryBind("@PreferredMetadataCountryCode", item.PreferredMetadataCountryCode); - saveItemStatement.TryBind("@IsHD", item.IsHD); - saveItemStatement.TryBind("@ExternalEtag", item.ExternalEtag); + + if (item.Width > 0) + { + saveItemStatement.TryBind("@Width", item.Width); + } + else + { + saveItemStatement.TryBindNull("@Width"); + } + if (item.Height > 0) + { + saveItemStatement.TryBind("@Height", item.Height); + } + else + { + saveItemStatement.TryBindNull("@Height"); + } if (item.DateLastRefreshed != default(DateTime)) { @@ -897,7 +861,15 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBindNull("@Audio"); } - saveItemStatement.TryBind("@ExternalServiceId", item.ServiceName); + var livetvChannel = item as LiveTvChannel; + if (livetvChannel != null) + { + saveItemStatement.TryBind("@ExternalServiceId", livetvChannel.ServiceName); + } + else + { + saveItemStatement.TryBindNull("@ExternalServiceId"); + } if (item.Tags.Length > 0) { @@ -924,7 +896,7 @@ namespace Emby.Server.Implementations.Data } var trailer = item as Trailer; - if (trailer != null && trailer.TrailerTypes.Count > 0) + if (trailer != null && trailer.TrailerTypes.Length > 0) { saveItemStatement.TryBind("@TrailerTypes", string.Join("|", trailer.TrailerTypes.Select(i => i.ToString()).ToArray())); } @@ -970,10 +942,10 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@Album", item.Album); saveItemStatement.TryBind("@IsVirtualItem", item.IsVirtualItem); - var hasSeries = item as IHasSeries; - if (hasSeries != null) + var hasSeriesName = item as IHasSeriesName; + if (hasSeriesName != null) { - saveItemStatement.TryBind("@SeriesName", hasSeries.SeriesName); + saveItemStatement.TryBind("@SeriesName", hasSeriesName.SeriesName); } else { @@ -993,7 +965,10 @@ namespace Emby.Server.Implementations.Data if (episode != null) { saveItemStatement.TryBind("@SeasonName", episode.SeasonName); - saveItemStatement.TryBind("@SeasonId", episode.SeasonId); + + var nullableSeasonId = episode.SeasonId.Equals(Guid.Empty) ? (Guid?)null : episode.SeasonId; + + saveItemStatement.TryBind("@SeasonId", nullableSeasonId); } else { @@ -1001,9 +976,12 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBindNull("@SeasonId"); } + var hasSeries = item as IHasSeries; if (hasSeries != null) { - saveItemStatement.TryBind("@SeriesId", hasSeries.SeriesId); + var nullableSeriesId = hasSeries.SeriesId.Equals(Guid.Empty) ? (Guid?)null : hasSeries.SeriesId; + + saveItemStatement.TryBind("@SeriesId", nullableSeriesId); saveItemStatement.TryBind("@SeriesPresentationUniqueKey", hasSeries.SeriesPresentationUniqueKey); } else @@ -1027,22 +1005,13 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBindNull("@ProductionLocations"); } - if (item.ThemeSongIds.Length > 0) + if (item.ExtraIds.Length > 0) { - saveItemStatement.TryBind("@ThemeSongIds", string.Join("|", item.ThemeSongIds.ToArray())); + saveItemStatement.TryBind("@ExtraIds", string.Join("|", item.ExtraIds.ToArray())); } else { - saveItemStatement.TryBindNull("@ThemeSongIds"); - } - - if (item.ThemeVideoIds.Length > 0) - { - saveItemStatement.TryBind("@ThemeVideoIds", string.Join("|", item.ThemeVideoIds.ToArray())); - } - else - { - saveItemStatement.TryBindNull("@ThemeVideoIds"); + saveItemStatement.TryBindNull("@ExtraIds"); } saveItemStatement.TryBind("@TotalBitrate", item.TotalBitrate); @@ -1089,7 +1058,7 @@ namespace Emby.Server.Implementations.Data } var ownerId = item.OwnerId; - if (ownerId != Guid.Empty) + if (!ownerId.Equals(Guid.Empty)) { saveItemStatement.TryBind("@OwnerId", ownerId); } @@ -1193,7 +1162,7 @@ namespace Emby.Server.Implementations.Data path = string.Empty; } - return path + + return GetPathToSave(path) + delimeter + image.DateModified.Ticks.ToString(CultureInfo.InvariantCulture) + delimeter + @@ -1215,7 +1184,7 @@ namespace Emby.Server.Implementations.Data var image = new ItemImageInfo(); - image.Path = parts[0]; + image.Path = RestorePath(parts[0]); long ticks; if (long.TryParse(parts[1], NumberStyles.Any, CultureInfo.InvariantCulture, out ticks)) @@ -1255,7 +1224,7 @@ namespace Emby.Server.Implementations.Data /// <exception cref="System.ArgumentException"></exception> public BaseItem RetrieveItem(Guid id) { - if (id == Guid.Empty) + if (id.Equals(Guid.Empty)) { throw new ArgumentNullException("id"); } @@ -1324,15 +1293,6 @@ namespace Emby.Server.Implementations.Data { return false; } - - if (type == typeof(ManualCollectionsFolder)) - { - return false; - } - if (type == typeof(CameraUploadsFolder)) - { - return false; - } if (type == typeof(PlaylistsFolder)) { return false; @@ -1351,22 +1311,10 @@ namespace Emby.Server.Implementations.Data { return false; } - if (type == typeof(RecordingGroup)) - { - return false; - } if (type == typeof(LiveTvProgram)) { return false; } - if (type == typeof(LiveTvAudioRecording)) - { - return false; - } - if (type == typeof(AudioPodcast)) - { - return false; - } if (type == typeof(AudioBook)) { return false; @@ -1386,10 +1334,10 @@ namespace Emby.Server.Implementations.Data private BaseItem GetItem(IReadOnlyList<IResultSetValue> reader, InternalItemsQuery query) { - return GetItem(reader, query, HasProgramAttributes(query), HasEpisodeAttributes(query), HasStartDate(query), HasTrailerTypes(query), HasArtistFields(query), HasSeriesFields(query)); + return GetItem(reader, query, HasProgramAttributes(query), HasEpisodeAttributes(query), HasServiceName(query), HasStartDate(query), HasTrailerTypes(query), HasArtistFields(query), HasSeriesFields(query)); } - private BaseItem GetItem(IReadOnlyList<IResultSetValue> reader, InternalItemsQuery query, bool enableProgramAttributes, bool hasEpisodeAttributes, bool queryHasStartDate, bool hasTrailerTypes, bool hasArtistFields, bool hasSeriesFields) + private BaseItem GetItem(IReadOnlyList<IResultSetValue> reader, InternalItemsQuery query, bool enableProgramAttributes, bool hasEpisodeAttributes, bool hasServiceName, bool queryHasStartDate, bool hasTrailerTypes, bool hasArtistFields, bool hasSeriesFields) { var typeString = reader.GetString(0); @@ -1406,7 +1354,7 @@ namespace Emby.Server.Implementations.Data if (TypeRequiresDeserialization(type)) { - using (var stream = _memoryStreamProvider.CreateNew(reader[1].ToBlob())) + using (var stream = new MemoryStream(reader[1].ToBlob())) { stream.Position = 0; @@ -1454,13 +1402,13 @@ namespace Emby.Server.Implementations.Data if (!reader.IsDBNull(index)) { - item.EndDate = reader[index].ReadDateTime(); + item.EndDate = reader[index].TryReadDateTime(); } index++; if (!reader.IsDBNull(index)) { - item.ChannelId = reader.GetString(index); + item.ChannelId = new Guid(reader.GetString(index)); } index++; @@ -1477,42 +1425,12 @@ namespace Emby.Server.Implementations.Data if (!reader.IsDBNull(index)) { - hasProgramAttributes.IsSports = reader.GetBoolean(index); - } - index++; - - if (!reader.IsDBNull(index)) - { - hasProgramAttributes.IsKids = reader.GetBoolean(index); - } - index++; - - if (!reader.IsDBNull(index)) - { hasProgramAttributes.IsSeries = reader.GetBoolean(index); } index++; if (!reader.IsDBNull(index)) { - hasProgramAttributes.IsLive = reader.GetBoolean(index); - } - index++; - - if (!reader.IsDBNull(index)) - { - hasProgramAttributes.IsNews = reader.GetBoolean(index); - } - index++; - - if (!reader.IsDBNull(index)) - { - hasProgramAttributes.IsPremiere = reader.GetBoolean(index); - } - index++; - - if (!reader.IsDBNull(index)) - { hasProgramAttributes.EpisodeTitle = reader.GetString(index); } index++; @@ -1525,7 +1443,7 @@ namespace Emby.Server.Implementations.Data } else { - index += 9; + index += 4; } } @@ -1571,17 +1489,20 @@ namespace Emby.Server.Implementations.Data index++; } - if (!reader.IsDBNull(index)) + if (HasField(query, ItemFields.Width)) { - item.IsHD = reader.GetBoolean(index); + if (!reader.IsDBNull(index)) + { + item.Width = reader.GetInt32(index); + } + index++; } - index++; - if (HasField(query, ItemFields.ExternalEtag)) + if (HasField(query, ItemFields.Height)) { if (!reader.IsDBNull(index)) { - item.ExternalEtag = reader.GetString(index); + item.Height = reader.GetInt32(index); } index++; } @@ -1603,13 +1524,13 @@ namespace Emby.Server.Implementations.Data if (!reader.IsDBNull(index)) { - item.Path = reader.GetString(index); + item.Path = RestorePath(reader.GetString(index)); } index++; if (!reader.IsDBNull(index)) { - item.PremiereDate = reader[index].ReadDateTime(); + item.PremiereDate = reader[index].TryReadDateTime(); } index++; @@ -1640,15 +1561,6 @@ namespace Emby.Server.Implementations.Data } index++; - if (HasField(query, ItemFields.HomePageUrl)) - { - if (!reader.IsDBNull(index)) - { - item.HomePageUrl = reader.GetString(index); - } - index++; - } - if (HasField(query, ItemFields.SortName)) { if (!reader.IsDBNull(index)) @@ -1686,7 +1598,7 @@ namespace Emby.Server.Implementations.Data { if (!reader.IsDBNull(index)) { - item.Genres = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList(); + item.Genres = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); } index++; } @@ -1709,11 +1621,18 @@ namespace Emby.Server.Implementations.Data // TODO: Even if not needed by apps, the server needs it internally // But get this excluded from contexts where it is not needed - if (!reader.IsDBNull(index)) + if (hasServiceName) { - item.ServiceName = reader.GetString(index); + var livetvChannel = item as LiveTvChannel; + if (livetvChannel != null) + { + if (!reader.IsDBNull(index)) + { + livetvChannel.ServiceName = reader.GetString(index); + } + } + index++; } - index++; if (!reader.IsDBNull(index)) { @@ -1785,7 +1704,7 @@ namespace Emby.Server.Implementations.Data } return (TrailerType?)null; - }).Where(i => i.HasValue).Select(i => i.Value).ToList(); + }).Where(i => i.HasValue).Select(i => i.Value).ToArray(); } } index++; @@ -1815,7 +1734,7 @@ namespace Emby.Server.Implementations.Data var folder = item as Folder; if (folder != null && !reader.IsDBNull(index)) { - folder.DateLastMediaAdded = reader[index].ReadDateTime(); + folder.DateLastMediaAdded = reader[index].TryReadDateTime(); } index++; } @@ -1838,18 +1757,15 @@ namespace Emby.Server.Implementations.Data } index++; - var hasSeries = item as IHasSeries; - if (hasSeriesFields) + var hasSeriesName = item as IHasSeriesName; + if (hasSeriesName != null) { - if (hasSeries != null) + if (!reader.IsDBNull(index)) { - if (!reader.IsDBNull(index)) - { - hasSeries.SeriesName = reader.GetString(index); - } + hasSeriesName.SeriesName = reader.GetString(index); } - index++; } + index++; if (hasEpisodeAttributes) { @@ -1873,6 +1789,7 @@ namespace Emby.Server.Implementations.Data index++; } + var hasSeries = item as IHasSeries; if (hasSeriesFields) { if (hasSeries != null) @@ -1945,20 +1862,11 @@ namespace Emby.Server.Implementations.Data index++; } - if (HasField(query, ItemFields.ThemeSongIds)) + if (HasField(query, ItemFields.ExtraIds)) { if (!reader.IsDBNull(index)) { - item.ThemeSongIds = SplitToGuids(reader.GetString(index)); - } - index++; - } - - if (HasField(query, ItemFields.ThemeVideoIds)) - { - if (!reader.IsDBNull(index)) - { - item.ThemeVideoIds = SplitToGuids(reader.GetString(index)); + item.ExtraIds = SplitToGuids(reader.GetString(index)); } index++; } @@ -2055,36 +1963,14 @@ namespace Emby.Server.Implementations.Data } /// <summary> - /// Gets the critic reviews. - /// </summary> - /// <param name="itemId">The item id.</param> - public List<ItemReview> GetCriticReviews(Guid itemId) - { - return new List<ItemReview>(); - } - - /// <summary> - /// Saves the critic reviews. - /// </summary> - /// <param name="itemId">The item id.</param> - /// <param name="criticReviews">The critic reviews.</param> - public void SaveCriticReviews(Guid itemId, IEnumerable<ItemReview> criticReviews) - { - } - - /// <summary> /// Gets chapters for an item /// </summary> /// <param name="id">The id.</param> /// <returns>IEnumerable{ChapterInfo}.</returns> /// <exception cref="System.ArgumentNullException">id</exception> - public List<ChapterInfo> GetChapters(Guid id) + public List<ChapterInfo> GetChapters(BaseItem item) { CheckDisposed(); - if (id == Guid.Empty) - { - throw new ArgumentNullException("id"); - } using (WriteLock.Read()) { @@ -2094,11 +1980,11 @@ namespace Emby.Server.Implementations.Data using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc")) { - statement.TryBind("@ItemId", id); + statement.TryBind("@ItemId", item.Id); foreach (var row in statement.ExecuteQuery()) { - list.Add(GetChapter(row)); + list.Add(GetChapter(row, item)); } } @@ -2114,13 +2000,9 @@ namespace Emby.Server.Implementations.Data /// <param name="index">The index.</param> /// <returns>ChapterInfo.</returns> /// <exception cref="System.ArgumentNullException">id</exception> - public ChapterInfo GetChapter(Guid id, int index) + public ChapterInfo GetChapter(BaseItem item, int index) { CheckDisposed(); - if (id == Guid.Empty) - { - throw new ArgumentNullException("id"); - } using (WriteLock.Read()) { @@ -2128,12 +2010,12 @@ namespace Emby.Server.Implementations.Data { using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex")) { - statement.TryBind("@ItemId", id); + statement.TryBind("@ItemId", item.Id); statement.TryBind("@ChapterIndex", index); foreach (var row in statement.ExecuteQuery()) { - return GetChapter(row); + return GetChapter(row, item); } } } @@ -2146,7 +2028,7 @@ namespace Emby.Server.Implementations.Data /// </summary> /// <param name="reader">The reader.</param> /// <returns>ChapterInfo.</returns> - private ChapterInfo GetChapter(IReadOnlyList<IResultSetValue> reader) + private ChapterInfo GetChapter(IReadOnlyList<IResultSetValue> reader, BaseItem item) { var chapter = new ChapterInfo { @@ -2161,6 +2043,11 @@ namespace Emby.Server.Implementations.Data if (!reader.IsDBNull(2)) { chapter.ImagePath = reader.GetString(2); + + if (!string.IsNullOrEmpty(chapter.ImagePath)) + { + chapter.ImageTag = ImageProcessor.GetImageCacheTag(item, chapter); + } } if (!reader.IsDBNull(3)) @@ -2178,7 +2065,7 @@ namespace Emby.Server.Implementations.Data { CheckDisposed(); - if (id == Guid.Empty) + if (id.Equals(Guid.Empty)) { throw new ArgumentNullException("id"); } @@ -2188,40 +2075,72 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException("chapters"); } - var index = 0; - using (WriteLock.Write()) { using (var connection = CreateConnection()) { connection.RunInTransaction(db => { + var idBlob = id.ToGuidBlob(); + // First delete chapters - db.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", id.ToGuidBlob()); + db.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", idBlob); - using (var saveChapterStatement = PrepareStatement(db, "replace into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath, ImageDateModified) values (@ItemId, @ChapterIndex, @StartPositionTicks, @Name, @ImagePath, @ImageDateModified)")) - { - foreach (var chapter in chapters) - { - if (index > 0) - { - saveChapterStatement.Reset(); - } + InsertChapters(idBlob, chapters, db); + + }, TransactionMode); + } + } + } - saveChapterStatement.TryBind("@ItemId", id.ToGuidBlob()); - saveChapterStatement.TryBind("@ChapterIndex", index); - saveChapterStatement.TryBind("@StartPositionTicks", chapter.StartPositionTicks); - saveChapterStatement.TryBind("@Name", chapter.Name); - saveChapterStatement.TryBind("@ImagePath", chapter.ImagePath); - saveChapterStatement.TryBind("@ImageDateModified", chapter.ImageDateModified); + private void InsertChapters(byte[] idBlob, List<ChapterInfo> chapters, IDatabaseConnection db) + { + var startIndex = 0; + var limit = 100; + var chapterIndex = 0; - saveChapterStatement.MoveNext(); + while (startIndex < chapters.Count) + { + var insertText = new StringBuilder("insert into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath, ImageDateModified) values "); - index++; - } - } - }, TransactionMode); + var endIndex = Math.Min(chapters.Count, startIndex + limit); + var isSubsequentRow = false; + + for (var i = startIndex; i < endIndex; i++) + { + if (isSubsequentRow) + { + insertText.Append(","); + } + + insertText.AppendFormat("(@ItemId, @ChapterIndex{0}, @StartPositionTicks{0}, @Name{0}, @ImagePath{0}, @ImageDateModified{0})", i.ToString(CultureInfo.InvariantCulture)); + isSubsequentRow = true; } + + using (var statement = PrepareStatementSafe(db, insertText.ToString())) + { + statement.TryBind("@ItemId", idBlob); + + for (var i = startIndex; i < endIndex; i++) + { + var index = i.ToString(CultureInfo.InvariantCulture); + + var chapter = chapters[i]; + + statement.TryBind("@ChapterIndex" + index, chapterIndex); + statement.TryBind("@StartPositionTicks" + index, chapter.StartPositionTicks); + statement.TryBind("@Name" + index, chapter.Name); + statement.TryBind("@ImagePath" + index, chapter.ImagePath); + statement.TryBind("@ImageDateModified" + index, chapter.ImageDateModified); + + chapterIndex++; + } + + statement.Reset(); + statement.MoveNext(); + } + + startIndex += limit; } } @@ -2232,11 +2151,6 @@ namespace Emby.Server.Implementations.Data return false; } - if (query.SimilarTo != null && query.User != null) - { - //return true; - } - var sortingFields = query.OrderBy.Select(i => i.Item1).ToList(); if (sortingFields.Contains(ItemSortBy.IsFavoriteOrLiked, StringComparer.OrdinalIgnoreCase)) @@ -2296,7 +2210,7 @@ namespace Emby.Server.Implementations.Data .Select(i => (ItemFields)Enum.Parse(typeof(ItemFields), i, true)) .ToList(); - private IEnumerable<string> GetColumnNamesFromField(ItemFields field) + private string[] GetColumnNamesFromField(ItemFields field) { if (field == ItemFields.Settings) { @@ -2318,17 +2232,20 @@ namespace Emby.Server.Implementations.Data { return new[] { "Tags" }; } + if (field == ItemFields.IsHD) + { + return Array.Empty<string>(); + } return new[] { field.ToString() }; } private bool HasField(InternalItemsQuery query, ItemFields name) { - var fields = query.DtoOptions.Fields; - switch (name) { - case ItemFields.HomePageUrl: + case ItemFields.Tags: + return query.DtoOptions.ContainsField(name) || HasProgramAttributes(query); case ItemFields.CustomRating: case ItemFields.ProductionLocations: case ItemFields.Settings: @@ -2336,23 +2253,20 @@ namespace Emby.Server.Implementations.Data case ItemFields.Taglines: case ItemFields.SortName: case ItemFields.Studios: - case ItemFields.Tags: - case ItemFields.ThemeSongIds: - case ItemFields.ThemeVideoIds: + case ItemFields.ExtraIds: case ItemFields.DateCreated: case ItemFields.Overview: case ItemFields.Genres: case ItemFields.DateLastMediaAdded: - case ItemFields.ExternalEtag: case ItemFields.PresentationUniqueKey: case ItemFields.InheritedParentalRatingValue: case ItemFields.ExternalSeriesId: case ItemFields.SeriesPresentationUniqueKey: case ItemFields.DateLastRefreshed: case ItemFields.DateLastSaved: - return fields.Contains(name); + return query.DtoOptions.ContainsField(name); case ItemFields.ServiceName: - return true; + return HasServiceName(query); default: return true; } @@ -2382,10 +2296,7 @@ namespace Emby.Server.Implementations.Data var types = new string[] { "Program", - "Recording", "TvChannel", - "LiveTvAudioRecording", - "LiveTvVideoRecording", "LiveTvProgram", "LiveTvTvChannel" }; @@ -2393,6 +2304,36 @@ namespace Emby.Server.Implementations.Data return types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase)); } + private bool HasServiceName(InternalItemsQuery query) + { + var excludeParentTypes = new string[] + { + "Series", + "Season", + "MusicAlbum", + "MusicArtist", + "PhotoAlbum" + }; + + if (excludeParentTypes.Contains(query.ParentType ?? string.Empty, StringComparer.OrdinalIgnoreCase)) + { + return false; + } + + if (query.IncludeItemTypes.Length == 0) + { + return true; + } + + var types = new string[] + { + "TvChannel", + "LiveTvTvChannel" + }; + + return types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase)); + } + private bool HasStartDate(InternalItemsQuery query) { var excludeParentTypes = new string[] @@ -2417,9 +2358,6 @@ namespace Emby.Server.Implementations.Data var types = new string[] { "Program", - "Recording", - "LiveTvAudioRecording", - "LiveTvVideoRecording", "LiveTvProgram" }; @@ -2481,9 +2419,7 @@ namespace Emby.Server.Implementations.Data "MusicAlbum", "MusicVideo", "AudioBook", - "AudioPodcast", - "LiveTvAudioRecording", - "Recording" + "AudioPodcast" }; return types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase)); @@ -2534,13 +2470,8 @@ namespace Emby.Server.Implementations.Data if (!HasProgramAttributes(query)) { - list.Remove("IsKids"); list.Remove("IsMovie"); - list.Remove("IsSports"); list.Remove("IsSeries"); - list.Remove("IsLive"); - list.Remove("IsNews"); - list.Remove("IsPremiere"); list.Remove("EpisodeTitle"); list.Remove("IsRepeat"); list.Remove("ShowId"); @@ -2571,7 +2502,6 @@ namespace Emby.Server.Implementations.Data if (!HasSeriesFields(query)) { list.Remove("SeriesId"); - list.Remove("SeriesName"); } if (!HasEpisodeAttributes(query)) @@ -2587,13 +2517,13 @@ namespace Emby.Server.Implementations.Data if (EnableJoinUserData(query)) { - list.Add("UserData.UserId"); - list.Add("UserData.lastPlayedDate"); - list.Add("UserData.playbackPositionTicks"); - list.Add("UserData.playcount"); - list.Add("UserData.isFavorite"); - list.Add("UserData.played"); - list.Add("UserData.rating"); + list.Add("UserDatas.UserId"); + list.Add("UserDatas.lastPlayedDate"); + list.Add("UserDatas.playbackPositionTicks"); + list.Add("UserDatas.playcount"); + list.Add("UserDatas.isFavorite"); + list.Add("UserDatas.played"); + list.Add("UserDatas.rating"); } if (query.SimilarTo != null) @@ -2603,14 +2533,24 @@ namespace Emby.Server.Implementations.Data var builder = new StringBuilder(); builder.Append("("); - builder.Append("((OfficialRating=@ItemOfficialRating) * 10)"); - //builder.Append("+ ((ProductionYear=@ItemProductionYear) * 10)"); + if (string.IsNullOrEmpty(item.OfficialRating)) + { + builder.Append("((OfficialRating is null) * 10)"); + } + else + { + builder.Append("((OfficialRating=@ItemOfficialRating) * 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 )"); + if (item.ProductionYear.HasValue) + { + //builder.Append("+ ((ProductionYear=@ItemProductionYear) * 10)"); + builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 10 Then 10 Else 0 End )"); + builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 5 Then 5 Else 0 End )"); + } //// genres, tags - builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type in (2,3,4,5) and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and Type in (2,3,4,5))) * 10)"); + builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId)) * 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)"); @@ -2623,24 +2563,56 @@ namespace Emby.Server.Implementations.Data list.Add(builder.ToString()); var excludeIds = query.ExcludeItemIds.ToList(); - excludeIds.Add(item.Id.ToString("N")); + excludeIds.Add(item.Id); + excludeIds.AddRange(item.ExtraIds); - if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Trailer).Name)) + query.ExcludeItemIds = excludeIds.ToArray(excludeIds.Count); + query.ExcludeProviderIds = item.ProviderIds; + } + + if (!string.IsNullOrEmpty(query.SearchTerm)) + { + var builder = new StringBuilder(); + builder.Append("("); + + builder.Append("((CleanName like @SearchTermStartsWith or (OriginalTitle not null and OriginalTitle like @SearchTermStartsWith)) * 10)"); + + if (query.SearchTerm.Length > 1) { - var hasTrailers = item as IHasTrailers; - if (hasTrailers != null) - { - excludeIds.AddRange(hasTrailers.GetTrailerIds().Select(i => i.ToString("N"))); - } + builder.Append("+ ((CleanName like @SearchTermContains or (OriginalTitle not null and OriginalTitle like @SearchTermContains)) * 10)"); } - query.ExcludeItemIds = excludeIds.ToArray(excludeIds.Count); - query.ExcludeProviderIds = item.ProviderIds; + builder.Append(") as SearchScore"); + + list.Add(builder.ToString()); } return list.ToArray(list.Count); } + private void BindSearchParams(InternalItemsQuery query, IStatement statement) + { + var searchTerm = query.SearchTerm; + + if (string.IsNullOrEmpty(searchTerm)) + { + return; + } + + searchTerm = FixUnicodeChars(searchTerm); + searchTerm = GetCleanValue(searchTerm); + + var commandText = statement.SQL; + if (commandText.IndexOf("@SearchTermStartsWith", StringComparison.OrdinalIgnoreCase) != -1) + { + statement.TryBind("@SearchTermStartsWith", searchTerm + "%"); + } + if (commandText.IndexOf("@SearchTermContains", StringComparison.OrdinalIgnoreCase) != -1) + { + statement.TryBind("@SearchTermContains", "%" + searchTerm + "%"); + } + } + private void BindSimilarParams(InternalItemsQuery query, IStatement statement) { var item = query.SimilarTo; @@ -2650,9 +2622,22 @@ namespace Emby.Server.Implementations.Data return; } - statement.TryBind("@ItemOfficialRating", item.OfficialRating); - statement.TryBind("@ItemProductionYear", item.ProductionYear ?? 0); - statement.TryBind("@SimilarItemId", item.Id); + var commandText = statement.SQL; + + if (commandText.IndexOf("@ItemOfficialRating", StringComparison.OrdinalIgnoreCase) != -1) + { + statement.TryBind("@ItemOfficialRating", item.OfficialRating); + } + + if (commandText.IndexOf("@ItemProductionYear", StringComparison.OrdinalIgnoreCase) != -1) + { + statement.TryBind("@ItemProductionYear", item.ProductionYear ?? 0); + } + + if (commandText.IndexOf("@SimilarItemId", StringComparison.OrdinalIgnoreCase) != -1) + { + statement.TryBind("@SimilarItemId", item.Id); + } } private string GetJoinUserDataText(InternalItemsQuery query) @@ -2662,7 +2647,7 @@ namespace Emby.Server.Implementations.Data return string.Empty; } - return " left join UserData on UserDataKey=UserData.Key And (UserId=@UserId)"; + return " left join UserDatas on UserDataKey=UserDatas.Key And (UserId=@UserId)"; } private string GetGroupBy(InternalItemsQuery query) @@ -2732,10 +2717,11 @@ namespace Emby.Server.Implementations.Data { if (EnableJoinUserData(query)) { - statement.TryBind("@UserId", query.User.Id); + statement.TryBind("@UserId", query.User.InternalId); } BindSimilarParams(query, statement); + BindSearchParams(query, statement); // Running this again will bind the params GetWhereClauses(query, statement); @@ -2808,15 +2794,17 @@ namespace Emby.Server.Implementations.Data { if (EnableJoinUserData(query)) { - statement.TryBind("@UserId", query.User.Id); + statement.TryBind("@UserId", query.User.InternalId); } BindSimilarParams(query, statement); + BindSearchParams(query, statement); // Running this again will bind the params GetWhereClauses(query, statement); var hasEpisodeAttributes = HasEpisodeAttributes(query); + var hasServiceName = HasServiceName(query); var hasProgramAttributes = HasProgramAttributes(query); var hasStartDate = HasStartDate(query); var hasTrailerTypes = HasTrailerTypes(query); @@ -2825,7 +2813,7 @@ namespace Emby.Server.Implementations.Data foreach (var row in statement.ExecuteQuery()) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); if (item != null) { list.Add(item); @@ -2860,6 +2848,28 @@ namespace Emby.Server.Implementations.Data } } + private string FixUnicodeChars(string buffer) + { + if (buffer.IndexOf('\u2013') > -1) buffer = buffer.Replace('\u2013', '-'); // en dash + if (buffer.IndexOf('\u2014') > -1) buffer = buffer.Replace('\u2014', '-'); // em dash + if (buffer.IndexOf('\u2015') > -1) buffer = buffer.Replace('\u2015', '-'); // horizontal bar + if (buffer.IndexOf('\u2017') > -1) buffer = buffer.Replace('\u2017', '_'); // double low line + if (buffer.IndexOf('\u2018') > -1) buffer = buffer.Replace('\u2018', '\''); // left single quotation mark + if (buffer.IndexOf('\u2019') > -1) buffer = buffer.Replace('\u2019', '\''); // right single quotation mark + if (buffer.IndexOf('\u201a') > -1) buffer = buffer.Replace('\u201a', ','); // single low-9 quotation mark + if (buffer.IndexOf('\u201b') > -1) buffer = buffer.Replace('\u201b', '\''); // single high-reversed-9 quotation mark + if (buffer.IndexOf('\u201c') > -1) buffer = buffer.Replace('\u201c', '\"'); // left double quotation mark + if (buffer.IndexOf('\u201d') > -1) buffer = buffer.Replace('\u201d', '\"'); // right double quotation mark + if (buffer.IndexOf('\u201e') > -1) buffer = buffer.Replace('\u201e', '\"'); // double low-9 quotation mark + if (buffer.IndexOf('\u2026') > -1) buffer = buffer.Replace("\u2026", "..."); // horizontal ellipsis + if (buffer.IndexOf('\u2032') > -1) buffer = buffer.Replace('\u2032', '\''); // prime + if (buffer.IndexOf('\u2033') > -1) buffer = buffer.Replace('\u2033', '\"'); // double prime + if (buffer.IndexOf('\u0060') > -1) buffer = buffer.Replace('\u0060', '\''); // grave accent + if (buffer.IndexOf('\u00B4') > -1) buffer = buffer.Replace('\u00B4', '\''); // acute accent + + return buffer; + } + private void AddItem(List<BaseItem> items, BaseItem newItem) { var providerIds = newItem.ProviderIds.ToList(); @@ -2989,15 +2999,15 @@ namespace Emby.Server.Implementations.Data if (EnableGroupByPresentationUniqueKey(query)) { - commandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); + commandText += " select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct PresentationUniqueKey)" })) + GetFromText(); } else if (query.GroupBySeriesPresentationUniqueKey) { - commandText += " select count (distinct SeriesPresentationUniqueKey)" + GetFromText(); + commandText += " select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct SeriesPresentationUniqueKey)" })) + GetFromText(); } else { - commandText += " select count (guid)" + GetFromText(); + commandText += " select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (guid)" })) + GetFromText(); } commandText += GetJoinUserDataText(query); @@ -3020,15 +3030,17 @@ namespace Emby.Server.Implementations.Data { if (EnableJoinUserData(query)) { - statement.TryBind("@UserId", query.User.Id); + statement.TryBind("@UserId", query.User.InternalId); } BindSimilarParams(query, statement); + BindSearchParams(query, statement); // Running this again will bind the params GetWhereClauses(query, statement); var hasEpisodeAttributes = HasEpisodeAttributes(query); + var hasServiceName = HasServiceName(query); var hasProgramAttributes = HasProgramAttributes(query); var hasStartDate = HasStartDate(query); var hasTrailerTypes = HasTrailerTypes(query); @@ -3037,7 +3049,7 @@ namespace Emby.Server.Implementations.Data foreach (var row in statement.ExecuteQuery()) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); if (item != null) { list.Add(item); @@ -3052,10 +3064,11 @@ namespace Emby.Server.Implementations.Data { if (EnableJoinUserData(query)) { - statement.TryBind("@UserId", query.User.Id); + statement.TryBind("@UserId", query.User.InternalId); } BindSimilarParams(query, statement); + BindSearchParams(query, statement); // Running this again will bind the params GetWhereClauses(query, statement); @@ -3083,12 +3096,19 @@ namespace Emby.Server.Implementations.Data { if (orderBy.Count == 0) { - orderBy.Add(new Tuple<string, SortOrder>("SimilarityScore", SortOrder.Descending)); - orderBy.Add(new Tuple<string, SortOrder>(ItemSortBy.Random, SortOrder.Ascending)); + orderBy.Add(new ValueTuple<string, SortOrder>("SimilarityScore", SortOrder.Descending)); + orderBy.Add(new ValueTuple<string, SortOrder>(ItemSortBy.Random, SortOrder.Ascending)); //orderBy.Add(new Tuple<string, SortOrder>(ItemSortBy.Random, SortOrder.Ascending)); } } + if (!string.IsNullOrEmpty(query.SearchTerm)) + { + orderBy = new List<(string, SortOrder)>(); + orderBy.Add(new ValueTuple<string, SortOrder>("SearchScore", SortOrder.Descending)); + orderBy.Add(new ValueTuple<string, SortOrder>(ItemSortBy.SortName, SortOrder.Ascending)); + } + query.OrderBy = orderBy.ToArray(); if (orderBy.Count == 0) @@ -3111,81 +3131,81 @@ namespace Emby.Server.Implementations.Data }).ToArray()); } - private Tuple<string, bool> MapOrderByField(string name, InternalItemsQuery query) + private ValueTuple<string, bool> MapOrderByField(string name, InternalItemsQuery query) { if (string.Equals(name, ItemSortBy.AirTime, StringComparison.OrdinalIgnoreCase)) { // TODO - return new Tuple<string, bool>("SortName", false); + return new ValueTuple<string, bool>("SortName", false); } if (string.Equals(name, ItemSortBy.Runtime, StringComparison.OrdinalIgnoreCase)) { - return new Tuple<string, bool>("RuntimeTicks", false); + return new ValueTuple<string, bool>("RuntimeTicks", false); } if (string.Equals(name, ItemSortBy.Random, StringComparison.OrdinalIgnoreCase)) { - return new Tuple<string, bool>("RANDOM()", false); + return new ValueTuple<string, bool>("RANDOM()", false); } if (string.Equals(name, ItemSortBy.DatePlayed, StringComparison.OrdinalIgnoreCase)) { if (query.GroupBySeriesPresentationUniqueKey) { - return new Tuple<string, bool>("MAX(LastPlayedDate)", false); + return new ValueTuple<string, bool>("MAX(LastPlayedDate)", false); } - return new Tuple<string, bool>("LastPlayedDate", false); + return new ValueTuple<string, bool>("LastPlayedDate", false); } if (string.Equals(name, ItemSortBy.PlayCount, StringComparison.OrdinalIgnoreCase)) { - return new Tuple<string, bool>("PlayCount", false); + return new ValueTuple<string, bool>("PlayCount", false); } if (string.Equals(name, ItemSortBy.IsFavoriteOrLiked, StringComparison.OrdinalIgnoreCase)) { // (Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 10 Then 2 Else 0 End ) - return new Tuple<string, bool>("(Select Case When IsFavorite is null Then 0 Else IsFavorite End )", true); + return new ValueTuple<string, bool>("(Select Case When IsFavorite is null Then 0 Else IsFavorite End )", true); } if (string.Equals(name, ItemSortBy.IsFolder, StringComparison.OrdinalIgnoreCase)) { - return new Tuple<string, bool>("IsFolder", true); + return new ValueTuple<string, bool>("IsFolder", true); } if (string.Equals(name, ItemSortBy.IsPlayed, StringComparison.OrdinalIgnoreCase)) { - return new Tuple<string, bool>("played", true); + return new ValueTuple<string, bool>("played", true); } if (string.Equals(name, ItemSortBy.IsUnplayed, StringComparison.OrdinalIgnoreCase)) { - return new Tuple<string, bool>("played", false); + return new ValueTuple<string, bool>("played", false); } if (string.Equals(name, ItemSortBy.DateLastContentAdded, StringComparison.OrdinalIgnoreCase)) { - return new Tuple<string, bool>("DateLastMediaAdded", false); + return new ValueTuple<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); + return new ValueTuple<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); + return new ValueTuple<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>("InheritedParentalRatingValue", false); + return new ValueTuple<string, bool>("InheritedParentalRatingValue", 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); + return new ValueTuple<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 Played=1 and B.SeriesPresentationUniqueKey=A.PresentationUniqueKey)", false); + return new ValueTuple<string, bool>("(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText(query) + " where Played=1 and B.SeriesPresentationUniqueKey=A.PresentationUniqueKey)", false); } if (string.Equals(name, ItemSortBy.SeriesSortName, StringComparison.OrdinalIgnoreCase)) { - return new Tuple<string, bool>("SeriesName", false); + return new ValueTuple<string, bool>("SeriesName", false); } - return new Tuple<string, bool>(name, false); + return new ValueTuple<string, bool>(name, false); } public List<Guid> GetItemIdsList(InternalItemsQuery query) @@ -3240,10 +3260,11 @@ namespace Emby.Server.Implementations.Data { if (EnableJoinUserData(query)) { - statement.TryBind("@UserId", query.User.Id); + statement.TryBind("@UserId", query.User.InternalId); } BindSimilarParams(query, statement); + BindSearchParams(query, statement); // Running this again will bind the params GetWhereClauses(query, statement); @@ -3311,7 +3332,7 @@ namespace Emby.Server.Implementations.Data { if (EnableJoinUserData(query)) { - statement.TryBind("@UserId", query.User.Id); + statement.TryBind("@UserId", query.User.InternalId); } // Running this again will bind the params @@ -3405,15 +3426,15 @@ namespace Emby.Server.Implementations.Data if (EnableGroupByPresentationUniqueKey(query)) { - commandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); + commandText += " select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct PresentationUniqueKey)" })) + GetFromText(); } else if (query.GroupBySeriesPresentationUniqueKey) { - commandText += " select count (distinct SeriesPresentationUniqueKey)" + GetFromText(); + commandText += " select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct SeriesPresentationUniqueKey)" })) + GetFromText(); } else { - commandText += " select count (guid)" + GetFromText(); + commandText += " select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (guid)" })) + GetFromText(); } commandText += GetJoinUserDataText(query); @@ -3437,10 +3458,11 @@ namespace Emby.Server.Implementations.Data { if (EnableJoinUserData(query)) { - statement.TryBind("@UserId", query.User.Id); + statement.TryBind("@UserId", query.User.InternalId); } BindSimilarParams(query, statement); + BindSearchParams(query, statement); // Running this again will bind the params GetWhereClauses(query, statement); @@ -3458,10 +3480,11 @@ namespace Emby.Server.Implementations.Data { if (EnableJoinUserData(query)) { - statement.TryBind("@UserId", query.User.Id); + statement.TryBind("@UserId", query.User.InternalId); } BindSimilarParams(query, statement); + BindSearchParams(query, statement); // Running this again will bind the params GetWhereClauses(query, statement); @@ -3527,14 +3550,69 @@ namespace Emby.Server.Implementations.Data { //whereClauses.Add("(UserId is null or UserId=@UserId)"); } + + var minWidth = query.MinWidth; + var maxWidth = query.MaxWidth; + if (query.IsHD.HasValue) { - whereClauses.Add("IsHD=@IsHD"); + var threshold = 1200; + if (query.IsHD.Value) + { + minWidth = threshold; + } + else + { + maxWidth = threshold - 1; + } + } + + if (query.Is4K.HasValue) + { + var threshold = 3800; + if (query.Is4K.Value) + { + minWidth = threshold; + } + else + { + maxWidth = threshold - 1; + } + } + + if (minWidth.HasValue) + { + whereClauses.Add("Width>=@MinWidth"); + if (statement != null) + { + statement.TryBind("@MinWidth", minWidth); + } + } + if (query.MinHeight.HasValue) + { + whereClauses.Add("Height>=@MinHeight"); if (statement != null) { - statement.TryBind("@IsHD", query.IsHD); + statement.TryBind("@MinHeight", query.MinHeight); } } + if (maxWidth.HasValue) + { + whereClauses.Add("Width<=@MaxWidth"); + if (statement != null) + { + statement.TryBind("@MaxWidth", maxWidth); + } + } + if (query.MaxHeight.HasValue) + { + whereClauses.Add("Height<=@MaxHeight"); + if (statement != null) + { + statement.TryBind("@MaxHeight", query.MaxHeight); + } + } + if (query.IsLocked.HasValue) { whereClauses.Add("IsLocked=@IsLocked"); @@ -3544,140 +3622,127 @@ namespace Emby.Server.Implementations.Data } } - var exclusiveProgramAttribtues = !(query.IsMovie ?? true) || - !(query.IsSports ?? true) || - !(query.IsKids ?? true) || - !(query.IsNews ?? true) || - !(query.IsSeries ?? true); + var tags = query.Tags.ToList(); + var excludeTags = query.ExcludeTags.ToList(); + + //if (!(query.IsMovie ?? true) || !(query.IsSeries ?? true)) + //{ + // if (query.IsMovie.HasValue) + // { + // 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"); + // if (statement != null) + // { + // statement.TryBind("@IsMovie", query.IsMovie); + // } + // } + // else + // { + // whereClauses.Add("(IsMovie is null OR IsMovie=@IsMovie)"); + // if (statement != null) + // { + // statement.TryBind("@IsMovie", query.IsMovie); + // } + // } + // } + //} + //else + //{ - if (exclusiveProgramAttribtues) + //} + + if (query.IsMovie ?? false) { - if (query.IsMovie.HasValue) - { - 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); - } + var programAttribtues = new List<string>(); - if (alternateTypes.Count == 0) - { - whereClauses.Add("IsMovie=@IsMovie"); - if (statement != null) - { - statement.TryBind("@IsMovie", query.IsMovie); - } - } - else - { - whereClauses.Add("(IsMovie is null OR IsMovie=@IsMovie)"); - if (statement != null) - { - statement.TryBind("@IsMovie", query.IsMovie); - } - } + var alternateTypes = new List<string>(); + if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Movie).Name)) + { + alternateTypes.Add(typeof(Movie).FullName); } - if (query.IsSeries.HasValue) + if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Trailer).Name)) { - whereClauses.Add("IsSeries=@IsSeries"); - if (statement != null) - { - statement.TryBind("@IsSeries", query.IsSeries); - } + alternateTypes.Add(typeof(Trailer).FullName); } - if (query.IsNews.HasValue) + + if (alternateTypes.Count == 0) { - whereClauses.Add("IsNews=@IsNews"); - if (statement != null) - { - statement.TryBind("@IsNews", query.IsNews); - } + programAttribtues.Add("IsMovie=@IsMovie"); } - if (query.IsKids.HasValue) + else { - whereClauses.Add("IsKids=@IsKids"); - if (statement != null) - { - statement.TryBind("@IsKids", query.IsKids); - } + programAttribtues.Add("(IsMovie is null OR IsMovie=@IsMovie)"); } - if (query.IsSports.HasValue) + + if (statement != null) { - whereClauses.Add("IsSports=@IsSports"); - if (statement != null) - { - statement.TryBind("@IsSports", query.IsSports); - } + statement.TryBind("@IsMovie", true); } + + whereClauses.Add("(" + string.Join(" OR ", programAttribtues.ToArray(programAttribtues.Count)) + ")"); } - else + else if (query.IsMovie.HasValue) { - var programAttribtues = new List<string>(); - if (query.IsMovie ?? false) + whereClauses.Add("IsMovie=@IsMovie"); + if (statement != null) { - 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); - } + statement.TryBind("@IsMovie", query.IsMovie); + } + } - if (alternateTypes.Count == 0) - { - programAttribtues.Add("IsMovie=@IsMovie"); - } - else - { - programAttribtues.Add("(IsMovie is null OR IsMovie=@IsMovie)"); - } + if (query.IsSeries.HasValue) + { + whereClauses.Add("IsSeries=@IsSeries"); + if (statement != null) + { + statement.TryBind("@IsSeries", query.IsSeries); + } + } - if (statement != null) - { - statement.TryBind("@IsMovie", true); - } + if (query.IsSports.HasValue) + { + if (query.IsSports.Value) + { + tags.Add("Sports"); } - if (query.IsSports ?? false) + else { - programAttribtues.Add("IsSports=@IsSports"); - if (statement != null) - { - statement.TryBind("@IsSports", query.IsSports); - } + excludeTags.Add("Sports"); } - if (query.IsNews ?? false) + } + + if (query.IsNews.HasValue) + { + if (query.IsNews.Value) { - programAttribtues.Add("IsNews=@IsNews"); - if (statement != null) - { - statement.TryBind("@IsNews", query.IsNews); - } + tags.Add("News"); } - if (query.IsSeries ?? false) + else { - programAttribtues.Add("IsSeries=@IsSeries"); - if (statement != null) - { - statement.TryBind("@IsSeries", query.IsSeries); - } + excludeTags.Add("News"); } - if (query.IsKids ?? false) + } + + if (query.IsKids.HasValue) + { + if (query.IsKids.Value) { - programAttribtues.Add("IsKids=@IsKids"); - if (statement != null) - { - statement.TryBind("@IsKids", query.IsKids); - } + tags.Add("Kids"); } - if (programAttribtues.Count > 0) + else { - whereClauses.Add("(" + string.Join(" OR ", programAttribtues.ToArray(programAttribtues.Count)) + ")"); + excludeTags.Add("Kids"); } } @@ -3686,6 +3751,11 @@ namespace Emby.Server.Implementations.Data whereClauses.Add("SimilarityScore > " + (query.MinSimilarityScore - 1).ToString(CultureInfo.InvariantCulture)); } + if (!string.IsNullOrEmpty(query.SearchTerm)) + { + whereClauses.Add("SearchScore > 0"); + } + if (query.IsFolder.HasValue) { whereClauses.Add("IsFolder=@IsFolder"); @@ -3710,50 +3780,55 @@ namespace Emby.Server.Implementations.Data whereClauses.Add(string.Format("type in ({0})", inClause)); } - var excludeTypes = query.ExcludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray(); - if (excludeTypes.Length == 1) + // Only specify excluded types if no included types are specified + if (includeTypes.Length == 0) { - whereClauses.Add("type<>@type"); - if (statement != null) + var excludeTypes = query.ExcludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray(); + if (excludeTypes.Length == 1) + { + whereClauses.Add("type<>@type"); + if (statement != null) + { + statement.TryBind("@type", excludeTypes[0]); + } + } + else if (excludeTypes.Length > 1) { - statement.TryBind("@type", excludeTypes[0]); + var inClause = string.Join(",", excludeTypes.Select(i => "'" + i + "'").ToArray()); + whereClauses.Add(string.Format("type not in ({0})", inClause)); } } - else if (excludeTypes.Length > 1) - { - var inClause = string.Join(",", excludeTypes.Select(i => "'" + i + "'").ToArray()); - whereClauses.Add(string.Format("type not in ({0})", inClause)); - } if (query.ChannelIds.Length == 1) { whereClauses.Add("ChannelId=@ChannelId"); if (statement != null) { - statement.TryBind("@ChannelId", query.ChannelIds[0]); + statement.TryBind("@ChannelId", query.ChannelIds[0].ToString("N")); } } else if (query.ChannelIds.Length > 1) { - var inClause = string.Join(",", query.ChannelIds.Where(IsValidId).Select(i => "'" + i + "'").ToArray()); + var inClause = string.Join(",", query.ChannelIds.Select(i => "'" + i.ToString("N") + "'").ToArray()); whereClauses.Add(string.Format("ChannelId in ({0})", inClause)); } - if (query.ParentId.HasValue) + if (!query.ParentId.Equals(Guid.Empty)) { whereClauses.Add("ParentId=@ParentId"); if (statement != null) { - statement.TryBind("@ParentId", query.ParentId.Value); + statement.TryBind("@ParentId", query.ParentId); } } if (!string.IsNullOrWhiteSpace(query.Path)) { + //whereClauses.Add("(Path=@Path COLLATE NOCASE)"); whereClauses.Add("Path=@Path"); if (statement != null) { - statement.TryBind("@Path", query.Path); + statement.TryBind("@Path", GetPathToSave(query.Path)); } } @@ -3847,21 +3922,37 @@ namespace Emby.Server.Implementations.Data statement.TryBind("@ParentIndexNumberNotEquals", query.ParentIndexNumberNotEquals.Value); } } - if (query.MinEndDate.HasValue) + + var minEndDate = query.MinEndDate; + var maxEndDate = query.MaxEndDate; + + if (query.HasAired.HasValue) + { + if (query.HasAired.Value) + { + maxEndDate = DateTime.UtcNow; + } + else + { + minEndDate = DateTime.UtcNow; + } + } + + if (minEndDate.HasValue) { whereClauses.Add("EndDate>=@MinEndDate"); if (statement != null) { - statement.TryBind("@MinEndDate", query.MinEndDate.Value); + statement.TryBind("@MinEndDate", minEndDate.Value); } } - if (query.MaxEndDate.HasValue) + if (maxEndDate.HasValue) { whereClauses.Add("EndDate<=@MaxEndDate"); if (statement != null) { - statement.TryBind("@MaxEndDate", query.MaxEndDate.Value); + statement.TryBind("@MaxEndDate", maxEndDate.Value); } } @@ -3955,7 +4046,8 @@ namespace Emby.Server.Implementations.Data { var paramName = "@PersonId" + index; - clauses.Add("(select Name from TypedBaseItems where guid=" + paramName + ") in (select Name from People where ItemId=Guid)"); + clauses.Add("(guid in (select itemid from People where Name = (select Name from TypedBaseItems where guid=" + paramName + ")))"); + if (statement != null) { statement.TryBind(paramName, personId.ToGuidBlob()); @@ -4012,14 +4104,19 @@ namespace Emby.Server.Implementations.Data } } - if (!string.IsNullOrWhiteSpace(query.NameContains)) + // These are the same, for now + var nameContains = query.NameContains; + if (!string.IsNullOrWhiteSpace(nameContains)) { - whereClauses.Add("CleanName like @NameContains"); + whereClauses.Add("(CleanName like @NameContains or OriginalTitle like @NameContains)"); if (statement != null) { - statement.TryBind("@NameContains", "%" + GetCleanValue(query.NameContains) + "%"); + nameContains = FixUnicodeChars(nameContains); + + statement.TryBind("@NameContains", "%" + GetCleanValue(nameContains) + "%"); } } + if (!string.IsNullOrWhiteSpace(query.NameStartsWith)) { whereClauses.Add("SortName like @NameStartsWith"); @@ -4111,17 +4208,32 @@ namespace Emby.Server.Implementations.Data { if (query.IsPlayed.HasValue) { - if (query.IsPlayed.Value) + // We should probably figure this out for all folders, but for right now, this is the only place where we need it + if (query.IncludeItemTypes.Length == 1 && string.Equals(query.IncludeItemTypes[0], typeof(Series).Name, StringComparison.OrdinalIgnoreCase)) { - whereClauses.Add("(played=@IsPlayed)"); + if (query.IsPlayed.Value) + { + whereClauses.Add("PresentationUniqueKey not in (select S.SeriesPresentationUniqueKey from TypedBaseitems S left join UserDatas UD on S.UserDataKey=UD.Key And UD.UserId=@UserId where Coalesce(UD.Played, 0)=0 and S.IsFolder=0 and S.IsVirtualItem=0 and S.SeriesPresentationUniqueKey not null)"); + } + else + { + whereClauses.Add("PresentationUniqueKey in (select S.SeriesPresentationUniqueKey from TypedBaseitems S left join UserDatas UD on S.UserDataKey=UD.Key And UD.UserId=@UserId where Coalesce(UD.Played, 0)=0 and S.IsFolder=0 and S.IsVirtualItem=0 and S.SeriesPresentationUniqueKey not null)"); + } } else { - whereClauses.Add("(played is null or played=@IsPlayed)"); - } - if (statement != null) - { - statement.TryBind("@IsPlayed", query.IsPlayed.Value); + if (query.IsPlayed.Value) + { + whereClauses.Add("(played=@IsPlayed)"); + } + else + { + whereClauses.Add("(played is null or played=@IsPlayed)"); + } + if (statement != null) + { + statement.TryBind("@IsPlayed", query.IsPlayed.Value); + } } } } @@ -4146,7 +4258,45 @@ namespace Emby.Server.Implementations.Data { var paramName = "@ArtistIds" + index; - clauses.Add("(select CleanName from TypedBaseItems where guid=" + paramName + ") in (select CleanValue from itemvalues where ItemId=Guid and Type<=1)"); + clauses.Add("(guid in (select itemid from itemvalues where CleanValue = (select CleanName from TypedBaseItems where guid=" + paramName + ") and Type<=1))"); + if (statement != null) + { + statement.TryBind(paramName, artistId.ToGuidBlob()); + } + index++; + } + var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); + } + + if (query.AlbumArtistIds.Length > 0) + { + var clauses = new List<string>(); + var index = 0; + foreach (var artistId in query.AlbumArtistIds) + { + var paramName = "@ArtistIds" + index; + + clauses.Add("(guid in (select itemid from itemvalues where CleanValue = (select CleanName from TypedBaseItems where guid=" + paramName + ") and Type=1))"); + if (statement != null) + { + statement.TryBind(paramName, artistId.ToGuidBlob()); + } + index++; + } + var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); + } + + if (query.ContributingArtistIds.Length > 0) + { + var clauses = new List<string>(); + var index = 0; + foreach (var artistId in query.ContributingArtistIds) + { + var paramName = "@ArtistIds" + index; + + clauses.Add("((select CleanName from TypedBaseItems where guid=" + paramName + ") in (select CleanValue from itemvalues where ItemId=Guid and Type=0) AND (select CleanName from TypedBaseItems where guid=" + paramName + ") not in (select CleanValue from itemvalues where ItemId=Guid and Type=1))"); if (statement != null) { statement.TryBind(paramName, artistId.ToGuidBlob()); @@ -4184,7 +4334,7 @@ namespace Emby.Server.Implementations.Data { var paramName = "@ExcludeArtistId" + index; - clauses.Add("(select CleanName from TypedBaseItems where guid=" + paramName + ") not in (select CleanValue from itemvalues where ItemId=Guid and Type<=1)"); + clauses.Add("(guid not in (select itemid from itemvalues where CleanValue = (select CleanName from TypedBaseItems where guid=" + paramName + ") and Type<=1))"); if (statement != null) { statement.TryBind(paramName, artistId.ToGuidBlob()); @@ -4203,7 +4353,7 @@ namespace Emby.Server.Implementations.Data { var paramName = "@GenreId" + index; - clauses.Add("(select CleanName from TypedBaseItems where guid=" + paramName + ") in (select CleanValue from itemvalues where ItemId=Guid and Type=2)"); + clauses.Add("(guid in (select itemid from itemvalues where CleanValue = (select CleanName from TypedBaseItems where guid=" + paramName + ") and Type=2))"); if (statement != null) { statement.TryBind(paramName, genreId.ToGuidBlob()); @@ -4231,11 +4381,11 @@ namespace Emby.Server.Implementations.Data whereClauses.Add(clause); } - if (query.Tags.Length > 0) + if (tags.Count > 0) { var clauses = new List<string>(); var index = 0; - foreach (var item in query.Tags) + foreach (var item in tags) { clauses.Add("@Tag" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=4)"); if (statement != null) @@ -4248,6 +4398,23 @@ namespace Emby.Server.Implementations.Data whereClauses.Add(clause); } + if (excludeTags.Count > 0) + { + var clauses = new List<string>(); + var index = 0; + foreach (var item in excludeTags) + { + clauses.Add("@ExcludeTag" + index + " not in (select CleanValue from itemvalues where ItemId=Guid and Type=4)"); + if (statement != null) + { + statement.TryBind("@ExcludeTag" + index, GetCleanValue(item)); + } + index++; + } + var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); + } + if (query.StudioIds.Length > 0) { var clauses = new List<string>(); @@ -4256,7 +4423,8 @@ namespace Emby.Server.Implementations.Data { var paramName = "@StudioId" + index; - clauses.Add("(select CleanName from TypedBaseItems where guid=" + paramName + ") in (select CleanValue from itemvalues where ItemId=Guid and Type=3)"); + clauses.Add("(guid in (select itemid from itemvalues where CleanValue = (select CleanName from TypedBaseItems where guid=" + paramName + ") and Type=3))"); + if (statement != null) { statement.TryBind(paramName, studioId.ToGuidBlob()); @@ -4314,6 +4482,18 @@ namespace Emby.Server.Implementations.Data } } + if (query.HasOfficialRating.HasValue) + { + if (query.HasOfficialRating.Value) + { + whereClauses.Add("(OfficialRating not null AND OfficialRating<>'')"); + } + else + { + whereClauses.Add("(OfficialRating is null OR OfficialRating='')"); + } + } + if (query.HasOverview.HasValue) { if (query.HasOverview.Value) @@ -4326,6 +4506,18 @@ namespace Emby.Server.Implementations.Data } } + if (query.HasOwnerId.HasValue) + { + if (query.HasOwnerId.Value) + { + whereClauses.Add("OwnerId not null"); + } + else + { + whereClauses.Add("OwnerId is null"); + } + } + if (!string.IsNullOrWhiteSpace(query.HasNoAudioTrackWithLanguage)) { whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Audio' and MediaStreams.Language=@HasNoAudioTrackWithLanguage limit 1) is null)"); @@ -4362,6 +4554,18 @@ namespace Emby.Server.Implementations.Data } } + if (query.HasSubtitles.HasValue) + { + if (query.HasSubtitles.Value) + { + whereClauses.Add("((select type from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' limit 1) not null)"); + } + else + { + whereClauses.Add("((select type from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' limit 1) is null)"); + } + } + if (query.HasChapterImages.HasValue) { if (query.HasChapterImages.Value) @@ -4379,6 +4583,21 @@ namespace Emby.Server.Implementations.Data whereClauses.Add("ParentId NOT NULL AND ParentId NOT IN (select guid from TypedBaseItems)"); } + if (query.IsDeadArtist.HasValue && query.IsDeadArtist.Value) + { + whereClauses.Add("CleanName not in (Select CleanValue From ItemValues where Type in (0,1))"); + } + + if (query.IsDeadStudio.HasValue && query.IsDeadStudio.Value) + { + whereClauses.Add("CleanName not in (Select CleanValue From ItemValues where Type = 3)"); + } + + if (query.IsDeadPerson.HasValue && query.IsDeadPerson.Value) + { + whereClauses.Add("Name not in (Select Name From People)"); + } + if (query.Years.Length == 1) { whereClauses.Add("ProductionYear=@Years"); @@ -4450,7 +4669,7 @@ namespace Emby.Server.Implementations.Data includeIds.Add("Guid = @IncludeId" + index); if (statement != null) { - statement.TryBind("@IncludeId" + index, new Guid(id)); + statement.TryBind("@IncludeId" + index, id); } index++; } @@ -4467,7 +4686,7 @@ namespace Emby.Server.Implementations.Data excludeIds.Add("Guid <> @ExcludeId" + index); if (statement != null) { - statement.TryBind("@ExcludeId" + index, new Guid(id)); + statement.TryBind("@ExcludeId" + index, id); } index++; } @@ -4499,7 +4718,40 @@ namespace Emby.Server.Implementations.Data break; } - whereClauses.Add(string.Join(" AND ", excludeIds.ToArray())); + if (excludeIds.Count > 0) + { + whereClauses.Add(string.Join(" AND ", excludeIds.ToArray())); + } + } + + if (query.HasAnyProviderId.Count > 0) + { + var hasProviderIds = new List<string>(); + + var index = 0; + foreach (var pair in query.HasAnyProviderId) + { + if (string.Equals(pair.Key, MetadataProviders.TmdbCollection.ToString(), StringComparison.OrdinalIgnoreCase)) + { + continue; + } + + var paramName = "@HasAnyProviderId" + index; + //hasProviderIds.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = '" + pair.Key + "'), '') <> " + paramName + ")"); + hasProviderIds.Add("ProviderIds like " + paramName + ""); + if (statement != null) + { + statement.TryBind(paramName, "%" + pair.Key + "=" + pair.Value + "%"); + } + index++; + + break; + } + + if (hasProviderIds.Count > 0) + { + whereClauses.Add("(" + string.Join(" OR ", hasProviderIds.ToArray()) + ")"); + } } if (query.HasImdbId.HasValue) @@ -4516,33 +4768,11 @@ namespace Emby.Server.Implementations.Data { whereClauses.Add("ProviderIds like '%tvdb=%'"); } - if (query.HasThemeSong.HasValue) - { - if (query.HasThemeSong.Value) - { - whereClauses.Add("ThemeSongIds not null"); - } - else - { - whereClauses.Add("ThemeSongIds is null"); - } - } - if (query.HasThemeVideo.HasValue) - { - if (query.HasThemeVideo.Value) - { - whereClauses.Add("ThemeVideoIds not null"); - } - else - { - whereClauses.Add("ThemeVideoIds is null"); - } - } var includedItemByNameTypes = GetItemByNameTypesInQuery(query).SelectMany(MapIncludeItemTypes).ToList(); var enableItemsByName = (query.IncludeItemsByName ?? false) && includedItemByNameTypes.Count > 0; - var queryTopParentIds = query.TopParentIds.Where(IsValidId).ToArray(); + var queryTopParentIds = query.TopParentIds; if (queryTopParentIds.Length == 1) { @@ -4565,12 +4795,12 @@ namespace Emby.Server.Implementations.Data } if (statement != null) { - statement.TryBind("@TopParentId", queryTopParentIds[0]); + statement.TryBind("@TopParentId", queryTopParentIds[0].ToString("N")); } } else if (queryTopParentIds.Length > 1) { - var val = string.Join(",", queryTopParentIds.Select(i => "'" + i + "'").ToArray()); + var val = string.Join(",", queryTopParentIds.Select(i => "'" + i.ToString("N") + "'").ToArray()); if (enableItemsByName && includedItemByNameTypes.Count == 1) { @@ -4597,12 +4827,12 @@ namespace Emby.Server.Implementations.Data if (statement != null) { - statement.TryBind("@AncestorId", new Guid(query.AncestorIds[0])); + statement.TryBind("@AncestorId", query.AncestorIds[0]); } } if (query.AncestorIds.Length > 1) { - var inClause = string.Join(",", query.AncestorIds.Select(i => "'" + new Guid(i).ToString("N") + "'").ToArray()); + var inClause = string.Join(",", query.AncestorIds.Select(i => "'" + i.ToString("N") + "'").ToArray()); whereClauses.Add(string.Format("Guid in (select itemId from AncestorIds where AncestorIdText in ({0}))", inClause)); } if (!string.IsNullOrWhiteSpace(query.AncestorWithPresentationUniqueKey)) @@ -4647,6 +4877,114 @@ namespace Emby.Server.Implementations.Data whereClauses.Add("((select CleanValue from itemvalues where ItemId=Guid and Type=6 and cleanvalue in (" + tagValuesList + ")) is null)"); } + if (query.SeriesStatuses.Length > 0) + { + var statuses = new List<string>(); + + foreach (var seriesStatus in query.SeriesStatuses) + { + statuses.Add("data like '%" + seriesStatus + "%'"); + } + + whereClauses.Add("(" + string.Join(" OR ", statuses.ToArray()) + ")"); + } + + if (query.BoxSetLibraryFolders.Length > 0) + { + var folderIdQueries = new List<string>(); + + foreach (var folderId in query.BoxSetLibraryFolders) + { + folderIdQueries.Add("data like '%" + folderId.ToString("N") + "%'"); + } + + whereClauses.Add("(" + string.Join(" OR ", folderIdQueries.ToArray()) + ")"); + } + + if (query.VideoTypes.Length > 0) + { + var videoTypes = new List<string>(); + + foreach (var videoType in query.VideoTypes) + { + videoTypes.Add("data like '%\"VideoType\":\"" + videoType.ToString() + "\"%'"); + } + + whereClauses.Add("(" + string.Join(" OR ", videoTypes.ToArray()) + ")"); + } + + if (query.Is3D.HasValue) + { + if (query.Is3D.Value) + { + whereClauses.Add("data like '%Video3DFormat%'"); + } + else + { + whereClauses.Add("data not like '%Video3DFormat%'"); + } + } + + if (query.IsPlaceHolder.HasValue) + { + if (query.IsPlaceHolder.Value) + { + whereClauses.Add("data like '%\"IsPlaceHolder\":true%'"); + } + else + { + whereClauses.Add("(data is null or data not like '%\"IsPlaceHolder\":true%')"); + } + } + + if (query.HasSpecialFeature.HasValue) + { + if (query.HasSpecialFeature.Value) + { + whereClauses.Add("ExtraIds not null"); + } + else + { + whereClauses.Add("ExtraIds is null"); + } + } + + if (query.HasTrailer.HasValue) + { + if (query.HasTrailer.Value) + { + whereClauses.Add("ExtraIds not null"); + } + else + { + whereClauses.Add("ExtraIds is null"); + } + } + + if (query.HasThemeSong.HasValue) + { + if (query.HasThemeSong.Value) + { + whereClauses.Add("ExtraIds not null"); + } + else + { + whereClauses.Add("ExtraIds is null"); + } + } + + if (query.HasThemeVideo.HasValue) + { + if (query.HasThemeVideo.Value) + { + whereClauses.Add("ExtraIds not null"); + } + else + { + whereClauses.Add("ExtraIds is null"); + } + } + return whereClauses; } @@ -4749,8 +5087,6 @@ namespace Emby.Server.Implementations.Data { typeof(LiveTvProgram), typeof(LiveTvChannel), - typeof(LiveTvVideoRecording), - typeof(LiveTvAudioRecording), typeof(Series), typeof(Audio), typeof(MusicAlbum), @@ -4759,7 +5095,6 @@ namespace Emby.Server.Implementations.Data typeof(MusicVideo), typeof(Movie), typeof(Playlist), - typeof(AudioPodcast), typeof(AudioBook), typeof(Trailer), typeof(BoxSet), @@ -4825,7 +5160,6 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type dict[t.Name] = new[] { t.FullName }; } - dict["Recording"] = new[] { typeof(LiveTvAudioRecording).FullName, typeof(LiveTvVideoRecording).FullName }; dict["Program"] = new[] { typeof(LiveTvProgram).FullName }; dict["TvChannel"] = new[] { typeof(LiveTvChannel).FullName }; @@ -4848,7 +5182,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type public void DeleteItem(Guid id, CancellationToken cancellationToken) { - if (id == Guid.Empty) + if (id.Equals(Guid.Empty)) { throw new ArgumentNullException("id"); } @@ -4861,23 +5195,25 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type { connection.RunInTransaction(db => { + var idBlob = id.ToGuidBlob(); + // Delete people - ExecuteWithSingleParam(db, "delete from People where ItemId=@Id", id.ToGuidBlob()); + ExecuteWithSingleParam(db, "delete from People where ItemId=@Id", idBlob); // Delete chapters - ExecuteWithSingleParam(db, "delete from " + ChaptersTableName + " where ItemId=@Id", id.ToGuidBlob()); + ExecuteWithSingleParam(db, "delete from " + ChaptersTableName + " where ItemId=@Id", idBlob); // Delete media streams - ExecuteWithSingleParam(db, "delete from mediastreams where ItemId=@Id", id.ToGuidBlob()); + ExecuteWithSingleParam(db, "delete from mediastreams where ItemId=@Id", idBlob); // Delete ancestors - ExecuteWithSingleParam(db, "delete from AncestorIds where ItemId=@Id", id.ToGuidBlob()); + ExecuteWithSingleParam(db, "delete from AncestorIds where ItemId=@Id", idBlob); // Delete item values - ExecuteWithSingleParam(db, "delete from ItemValues where ItemId=@Id", id.ToGuidBlob()); + ExecuteWithSingleParam(db, "delete from ItemValues where ItemId=@Id", idBlob); // Delete the item - ExecuteWithSingleParam(db, "delete from TypedBaseItems where guid=@Id", id.ToGuidBlob()); + ExecuteWithSingleParam(db, "delete from TypedBaseItems where guid=@Id", idBlob); }, TransactionMode); } } @@ -4979,7 +5315,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type { var whereClauses = new List<string>(); - if (query.ItemId != Guid.Empty) + if (!query.ItemId.Equals(Guid.Empty)) { whereClauses.Add("ItemId=@ItemId"); if (statement != null) @@ -4987,7 +5323,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type statement.TryBind("@ItemId", query.ItemId.ToGuidBlob()); } } - if (query.AppearsInItemId != Guid.Empty) + if (!query.AppearsInItemId.Equals(Guid.Empty)) { whereClauses.Add("Name in (Select Name from People where ItemId=@AppearsInItemId)"); if (statement != null) @@ -5047,9 +5383,9 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type return whereClauses; } - private void UpdateAncestors(Guid itemId, List<Guid> ancestorIds, IDatabaseConnection db, IStatement deleteAncestorsStatement, IStatement updateAncestorsStatement) + private void UpdateAncestors(Guid itemId, List<Guid> ancestorIds, IDatabaseConnection db, IStatement deleteAncestorsStatement) { - if (itemId == Guid.Empty) + if (itemId.Equals(Guid.Empty)) { throw new ArgumentNullException("itemId"); } @@ -5061,18 +5397,46 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type CheckDisposed(); + var itemIdBlob = itemId.ToGuidBlob(); + // First delete deleteAncestorsStatement.Reset(); - deleteAncestorsStatement.TryBind("@ItemId", itemId.ToGuidBlob()); + deleteAncestorsStatement.TryBind("@ItemId", itemIdBlob); deleteAncestorsStatement.MoveNext(); - foreach (var ancestorId in ancestorIds) + if (ancestorIds.Count == 0) { - updateAncestorsStatement.Reset(); - updateAncestorsStatement.TryBind("@ItemId", itemId.ToGuidBlob()); - updateAncestorsStatement.TryBind("@AncestorId", ancestorId.ToGuidBlob()); - updateAncestorsStatement.TryBind("@AncestorIdText", ancestorId.ToString("N")); - updateAncestorsStatement.MoveNext(); + return; + } + + var insertText = new StringBuilder("insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values "); + + for (var i = 0; i < ancestorIds.Count; i++) + { + if (i > 0) + { + insertText.Append(","); + } + + insertText.AppendFormat("(@ItemId, @AncestorId{0}, @AncestorIdText{0})", i.ToString(CultureInfo.InvariantCulture)); + } + + using (var statement = PrepareStatementSafe(db, insertText.ToString())) + { + statement.TryBind("@ItemId", itemIdBlob); + + for (var i = 0; i < ancestorIds.Count; i++) + { + var index = i.ToString(CultureInfo.InvariantCulture); + + var ancestorId = ancestorIds[i]; + + statement.TryBind("@AncestorId" + index, ancestorId.ToGuidBlob()); + statement.TryBind("@AncestorIdText" + index, ancestorId.ToString("N")); + } + + statement.Reset(); + statement.MoveNext(); } } @@ -5249,18 +5613,13 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type var columns = _retriveItemColumns.ToList(); columns.AddRange(itemCountColumns.Select(i => i.Item2).ToArray()); - columns = GetFinalColumnsToSelect(query, columns.ToArray()).ToList(); - - var commandText = "select " + string.Join(",", columns.ToArray()) + GetFromText(); - commandText += GetJoinUserDataText(query); - + // do this first before calling GetFinalColumnsToSelect, otherwise ExcludeItemIds will be set by SimilarTo 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, @@ -5273,6 +5632,11 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type IsSeries = query.IsSeries }; + columns = GetFinalColumnsToSelect(query, columns.ToArray()).ToList(); + + var commandText = "select " + string.Join(",", columns.ToArray()) + GetFromText(); + commandText += GetJoinUserDataText(query); + var innerWhereClauses = GetWhereClauses(innerQuery, null); var innerWhereText = innerWhereClauses.Count == 0 ? @@ -5305,7 +5669,10 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type GenreIds = query.GenreIds, Genres = query.Genres, Years = query.Years, - NameContains = query.NameContains + NameContains = query.NameContains, + SearchTerm = query.SearchTerm, + SimilarTo = query.SimilarTo, + ExcludeItemIds = query.ExcludeItemIds }; var outerWhereClauses = GetWhereClauses(outerQuery, null); @@ -5318,7 +5685,14 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type commandText += whereText; commandText += " group by PresentationUniqueKey"; - commandText += " order by SortName"; + if (query.SimilarTo != null || !string.IsNullOrEmpty(query.SearchTerm)) + { + commandText += GetOrderByText(query); + } + else + { + commandText += " order by SortName"; + } if (query.Limit.HasValue || query.StartIndex.HasValue) { @@ -5344,7 +5718,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } if (query.EnableTotalRecordCount) { - var countText = "select count (distinct PresentationUniqueKey)" + GetFromText(); + var countText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct PresentationUniqueKey)" })) + GetFromText(); countText += GetJoinUserDataText(query); countText += whereText; @@ -5360,6 +5734,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type var list = new List<Tuple<BaseItem, ItemCounts>>(); var result = new QueryResult<Tuple<BaseItem, ItemCounts>>(); + //Logger.Info("GetItemValues {0}", string.Join(";", statementTexts.ToArray())); var statements = PrepareAllSafe(db, statementTexts); if (!isReturningZeroItems) @@ -5369,7 +5744,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type statement.TryBind("@SelectType", returnType); if (EnableJoinUserData(query)) { - statement.TryBind("@UserId", query.User.Id); + statement.TryBind("@UserId", query.User.InternalId); } if (typeSubQuery != null) @@ -5377,11 +5752,13 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type GetWhereClauses(typeSubQuery, null); } BindSimilarParams(query, statement); + BindSearchParams(query, statement); GetWhereClauses(innerQuery, statement); GetWhereClauses(outerQuery, statement); var hasEpisodeAttributes = HasEpisodeAttributes(query); var hasProgramAttributes = HasProgramAttributes(query); + var hasServiceName = HasServiceName(query); var hasStartDate = HasStartDate(query); var hasTrailerTypes = HasTrailerTypes(query); var hasArtistFields = HasArtistFields(query); @@ -5389,7 +5766,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type foreach (var row in statement.ExecuteQuery()) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); if (item != null) { var countStartColumn = columns.Count - 1; @@ -5404,7 +5781,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type if (query.EnableTotalRecordCount) { - commandText = "select count (distinct PresentationUniqueKey)" + GetFromText(); + commandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct PresentationUniqueKey)" })) + GetFromText(); commandText += GetJoinUserDataText(query); commandText += whereText; @@ -5414,7 +5791,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type statement.TryBind("@SelectType", returnType); if (EnableJoinUserData(query)) { - statement.TryBind("@UserId", query.User.Id); + statement.TryBind("@UserId", query.User.InternalId); } if (typeSubQuery != null) @@ -5422,6 +5799,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type GetWhereClauses(typeSubQuery, null); } BindSimilarParams(query, statement); + BindSearchParams(query, statement); GetWhereClauses(innerQuery, statement); GetWhereClauses(outerQuery, statement); @@ -5535,7 +5913,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type private void UpdateItemValues(Guid itemId, List<Tuple<int, string>> values, IDatabaseConnection db) { - if (itemId == Guid.Empty) + if (itemId.Equals(Guid.Empty)) { throw new ArgumentNullException("itemId"); } @@ -5552,41 +5930,66 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type // First delete db.Execute("delete from ItemValues where ItemId=@Id", guidBlob); - using (var statement = PrepareStatement(db, "insert into ItemValues (ItemId, Type, Value, CleanValue) values (@ItemId, @Type, @Value, @CleanValue)")) + InsertItemValues(guidBlob, values, db); + } + + private void InsertItemValues(byte[] idBlob, List<Tuple<int, string>> values, IDatabaseConnection db) + { + var startIndex = 0; + var limit = 100; + + while (startIndex < values.Count) { - foreach (var pair in values) - { - var itemValue = pair.Item2; + var insertText = new StringBuilder("insert into ItemValues (ItemId, Type, Value, CleanValue) values "); - // Don't save if invalid - if (string.IsNullOrWhiteSpace(itemValue)) + var endIndex = Math.Min(values.Count, startIndex + limit); + var isSubsequentRow = false; + + for (var i = startIndex; i < endIndex; i++) + { + if (isSubsequentRow) { - continue; + insertText.Append(","); } - statement.Reset(); + insertText.AppendFormat("(@ItemId, @Type{0}, @Value{0}, @CleanValue{0})", i.ToString(CultureInfo.InvariantCulture)); + isSubsequentRow = true; + } - statement.TryBind("@ItemId", guidBlob); - statement.TryBind("@Type", pair.Item1); - statement.TryBind("@Value", itemValue); + using (var statement = PrepareStatementSafe(db, insertText.ToString())) + { + statement.TryBind("@ItemId", idBlob); - if (pair.Item2 == null) + for (var i = startIndex; i < endIndex; i++) { - statement.TryBindNull("@CleanValue"); - } - else - { - statement.TryBind("@CleanValue", GetCleanValue(pair.Item2)); + var index = i.ToString(CultureInfo.InvariantCulture); + + var currentValueInfo = values[i]; + + var itemValue = currentValueInfo.Item2; + + // Don't save if invalid + if (string.IsNullOrWhiteSpace(itemValue)) + { + continue; + } + + statement.TryBind("@Type" + index, currentValueInfo.Item1); + statement.TryBind("@Value" + index, itemValue); + statement.TryBind("@CleanValue" + index, GetCleanValue(itemValue)); } + statement.Reset(); statement.MoveNext(); } + + startIndex += limit; } } public void UpdatePeople(Guid itemId, List<PersonInfo> people) { - if (itemId == Guid.Empty) + if (itemId.Equals(Guid.Empty)) { throw new ArgumentNullException("itemId"); } @@ -5602,34 +6005,69 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type { using (var connection = CreateConnection()) { - // First delete - // "delete from People where ItemId=?" - connection.Execute("delete from People where ItemId=?", itemId.ToGuidBlob()); + connection.RunInTransaction(db => + { + var itemIdBlob = itemId.ToGuidBlob(); + + // First delete chapters + db.Execute("delete from People where ItemId=@ItemId", itemIdBlob); + + InsertPeople(itemIdBlob, people, db); + + }, TransactionMode); + + } + } + } + + private void InsertPeople(byte[] idBlob, List<PersonInfo> people, IDatabaseConnection db) + { + var startIndex = 0; + var limit = 100; + var listIndex = 0; - var listIndex = 0; + while (startIndex < people.Count) + { + var insertText = new StringBuilder("insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values "); + + var endIndex = Math.Min(people.Count, startIndex + limit); + var isSubsequentRow = false; - using (var statement = PrepareStatement(connection, - "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values (@ItemId, @Name, @Role, @PersonType, @SortOrder, @ListOrder)")) + for (var i = startIndex; i < endIndex; i++) + { + if (isSubsequentRow) { - foreach (var person in people) - { - if (listIndex > 0) - { - statement.Reset(); - } + insertText.Append(","); + } - statement.TryBind("@ItemId", itemId.ToGuidBlob()); - statement.TryBind("@Name", person.Name); - statement.TryBind("@Role", person.Role); - statement.TryBind("@PersonType", person.Type); - statement.TryBind("@SortOrder", person.SortOrder); - statement.TryBind("@ListOrder", listIndex); + insertText.AppendFormat("(@ItemId, @Name{0}, @Role{0}, @PersonType{0}, @SortOrder{0}, @ListOrder{0})", i.ToString(CultureInfo.InvariantCulture)); + isSubsequentRow = true; + } - statement.MoveNext(); - listIndex++; - } + using (var statement = PrepareStatementSafe(db, insertText.ToString())) + { + statement.TryBind("@ItemId", idBlob); + + for (var i = startIndex; i < endIndex; i++) + { + var index = i.ToString(CultureInfo.InvariantCulture); + + var person = people[i]; + + statement.TryBind("@Name" + index, person.Name); + statement.TryBind("@Role" + index, person.Role); + statement.TryBind("@PersonType" + index, person.Type); + statement.TryBind("@SortOrder" + index, person.SortOrder); + statement.TryBind("@ListOrder" + index, listIndex); + + listIndex++; } + + statement.Reset(); + statement.MoveNext(); } + + startIndex += limit; } } @@ -5718,7 +6156,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type { CheckDisposed(); - if (id == Guid.Empty) + if (id.Equals(Guid.Empty)) { throw new ArgumentNullException("id"); } @@ -5734,62 +6172,111 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type { using (var connection = CreateConnection()) { - // First delete chapters - connection.Execute("delete from mediastreams where ItemId=@ItemId", id.ToGuidBlob()); + connection.RunInTransaction(db => + { + var itemIdBlob = id.ToGuidBlob(); + + // First delete chapters + db.Execute("delete from mediastreams where ItemId=@ItemId", itemIdBlob); + + InsertMediaStreams(itemIdBlob, streams, db); - using (var statement = PrepareStatement(connection, string.Format("replace into mediastreams ({0}) values ({1})", - string.Join(",", _mediaStreamSaveColumns), - string.Join(",", _mediaStreamSaveColumns.Select(i => "@" + i).ToArray())))) + }, TransactionMode); + } + } + } + + private void InsertMediaStreams(byte[] idBlob, List<MediaStream> streams, IDatabaseConnection db) + { + var startIndex = 0; + var limit = 10; + + while (startIndex < streams.Count) + { + var insertText = new StringBuilder(string.Format("insert into mediastreams ({0}) values ", string.Join(",", _mediaStreamSaveColumns))); + + var endIndex = Math.Min(streams.Count, startIndex + limit); + var isSubsequentRow = false; + + for (var i = startIndex; i < endIndex; i++) + { + if (isSubsequentRow) { - foreach (var stream in streams) - { - var paramList = new List<object>(); - - paramList.Add(id.ToGuidBlob()); - paramList.Add(stream.Index); - paramList.Add(stream.Type.ToString()); - paramList.Add(stream.Codec); - paramList.Add(stream.Language); - paramList.Add(stream.ChannelLayout); - paramList.Add(stream.Profile); - paramList.Add(stream.AspectRatio); - paramList.Add(stream.Path); - - paramList.Add(stream.IsInterlaced); - paramList.Add(stream.BitRate); - paramList.Add(stream.Channels); - paramList.Add(stream.SampleRate); - - paramList.Add(stream.IsDefault); - paramList.Add(stream.IsForced); - paramList.Add(stream.IsExternal); - - paramList.Add(stream.Width); - paramList.Add(stream.Height); - paramList.Add(stream.AverageFrameRate); - paramList.Add(stream.RealFrameRate); - paramList.Add(stream.Level); - paramList.Add(stream.PixelFormat); - paramList.Add(stream.BitDepth); - paramList.Add(stream.IsExternal); - paramList.Add(stream.RefFrames); - - paramList.Add(stream.CodecTag); - paramList.Add(stream.Comment); - paramList.Add(stream.NalLengthSize); - paramList.Add(stream.IsAVC); - paramList.Add(stream.Title); - - paramList.Add(stream.TimeBase); - paramList.Add(stream.CodecTimeBase); - - statement.Execute(paramList.ToArray()); - } + insertText.Append(","); + } + + var index = i.ToString(CultureInfo.InvariantCulture); + + var mediaStreamSaveColumns = string.Join(",", _mediaStreamSaveColumns.Skip(1).Select(m => "@" + m + index).ToArray()); + + insertText.AppendFormat("(@ItemId, {0})", mediaStreamSaveColumns); + isSubsequentRow = true; + } + + using (var statement = PrepareStatementSafe(db, insertText.ToString())) + { + statement.TryBind("@ItemId", idBlob); + + for (var i = startIndex; i < endIndex; i++) + { + var index = i.ToString(CultureInfo.InvariantCulture); + + var stream = streams[i]; + + statement.TryBind("@StreamIndex" + index, stream.Index); + statement.TryBind("@StreamType" + index, stream.Type.ToString()); + statement.TryBind("@Codec" + index, stream.Codec); + statement.TryBind("@Language" + index, stream.Language); + statement.TryBind("@ChannelLayout" + index, stream.ChannelLayout); + statement.TryBind("@Profile" + index, stream.Profile); + statement.TryBind("@AspectRatio" + index, stream.AspectRatio); + statement.TryBind("@Path" + index, GetPathToSave(stream.Path)); + + statement.TryBind("@IsInterlaced" + index, stream.IsInterlaced); + statement.TryBind("@BitRate" + index, stream.BitRate); + statement.TryBind("@Channels" + index, stream.Channels); + statement.TryBind("@SampleRate" + index, stream.SampleRate); + + statement.TryBind("@IsDefault" + index, stream.IsDefault); + statement.TryBind("@IsForced" + index, stream.IsForced); + statement.TryBind("@IsExternal" + index, stream.IsExternal); + + // Yes these are backwards due to a mistake + statement.TryBind("@Width" + index, stream.Height); + statement.TryBind("@Height" + index, stream.Width); + + statement.TryBind("@AverageFrameRate" + index, stream.AverageFrameRate); + statement.TryBind("@RealFrameRate" + index, stream.RealFrameRate); + statement.TryBind("@Level" + index, stream.Level); + + statement.TryBind("@PixelFormat" + index, stream.PixelFormat); + statement.TryBind("@BitDepth" + index, stream.BitDepth); + statement.TryBind("@IsExternal" + index, stream.IsExternal); + statement.TryBind("@RefFrames" + index, stream.RefFrames); + + statement.TryBind("@CodecTag" + index, stream.CodecTag); + statement.TryBind("@Comment" + index, stream.Comment); + statement.TryBind("@NalLengthSize" + index, stream.NalLengthSize); + statement.TryBind("@IsAvc" + index, stream.IsAVC); + statement.TryBind("@Title" + index, stream.Title); + + statement.TryBind("@TimeBase" + index, stream.TimeBase); + statement.TryBind("@CodecTimeBase" + index, stream.CodecTimeBase); + + statement.TryBind("@ColorPrimaries" + index, stream.ColorPrimaries); + statement.TryBind("@ColorSpace" + index, stream.ColorSpace); + statement.TryBind("@ColorTransfer" + index, stream.ColorTransfer); } + + statement.Reset(); + statement.MoveNext(); } + + startIndex += limit; } } + /// <summary> /// Gets the chapter. /// </summary> @@ -5831,7 +6318,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type if (reader[8].SQLiteType != SQLiteType.Null) { - item.Path = reader[8].ToString(); + item.Path = RestorePath(reader[8].ToString()); } item.IsInterlaced = reader.GetBoolean(9); @@ -5935,6 +6422,21 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type item.CodecTimeBase = reader[31].ToString(); } + if (reader[32].SQLiteType != SQLiteType.Null) + { + item.ColorPrimaries = reader[32].ToString(); + } + + if (reader[33].SQLiteType != SQLiteType.Null) + { + item.ColorSpace = reader[33].ToString(); + } + + if (reader[34].SQLiteType != SQLiteType.Null) + { + item.ColorTransfer = reader[34].ToString(); + } + return item; } diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index ad5c60ede..07d64a2b0 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -9,12 +9,12 @@ using MediaBrowser.Controller.Persistence; using MediaBrowser.Model.IO; using MediaBrowser.Model.Logging; using SQLitePCL.pretty; +using MediaBrowser.Controller.Library; namespace Emby.Server.Implementations.Data { public class SqliteUserDataRepository : BaseSqliteRepository, IUserDataRepository { - private readonly string _importFile; private readonly IFileSystem _fileSystem; public SqliteUserDataRepository(ILogger logger, IApplicationPaths appPaths, IFileSystem fileSystem) @@ -22,7 +22,6 @@ namespace Emby.Server.Implementations.Data { _fileSystem = fileSystem; DbFilePath = Path.Combine(appPaths.DataPath, "library.db"); - _importFile = Path.Combine(appPaths.DataPath, "userdata_v2.db"); } /// <summary> @@ -41,7 +40,7 @@ namespace Emby.Server.Implementations.Data /// Opens the connection to the database /// </summary> /// <returns>Task.</returns> - public void Initialize(ReaderWriterLockSlim writeLock, ManagedConnection managedConnection) + public void Initialize(ReaderWriterLockSlim writeLock, ManagedConnection managedConnection, IUserManager userManager) { _connection = managedConnection; @@ -50,138 +49,134 @@ namespace Emby.Server.Implementations.Data using (var connection = CreateConnection()) { - string[] queries = { + var userDatasTableExists = TableExists(connection, "UserDatas"); + var userDataTableExists = TableExists(connection, "userdata"); - "create table if not exists userdata (key nvarchar not null, userId GUID not null, rating float null, played bit not null, playCount int not null, isFavorite bit not null, playbackPositionTicks bigint not null, lastPlayedDate datetime null)", - - "create table if not exists DataSettings (IsUserDataImported bit)", - - "drop index if exists idx_userdata", - "drop index if exists idx_userdata1", - "drop index if exists idx_userdata2", - "drop index if exists userdataindex1", - - "create unique index if not exists userdataindex on userdata (key, userId)", - "create index if not exists userdataindex2 on userdata (key, userId, played)", - "create index if not exists userdataindex3 on userdata (key, userId, playbackPositionTicks)", - "create index if not exists userdataindex4 on userdata (key, userId, isFavorite)", - - "pragma shrink_memory" - }; - - connection.RunQueries(queries); + var users = userDatasTableExists ? null : userManager.Users.ToArray(); connection.RunInTransaction(db => { - var existingColumnNames = GetColumnNames(db, "userdata"); + db.ExecuteAll(string.Join(";", new[] { + + "create table if not exists UserDatas (key nvarchar not null, userId INT not null, rating float null, played bit not null, playCount int not null, isFavorite bit not null, playbackPositionTicks bigint not null, lastPlayedDate datetime null, AudioStreamIndex INT, SubtitleStreamIndex INT)", + + "drop index if exists idx_userdata", + "drop index if exists idx_userdata1", + "drop index if exists idx_userdata2", + "drop index if exists userdataindex1", + "drop index if exists userdataindex", + "drop index if exists userdataindex3", + "drop index if exists userdataindex4", + "create unique index if not exists UserDatasIndex1 on UserDatas (key, userId)", + "create index if not exists UserDatasIndex2 on UserDatas (key, userId, played)", + "create index if not exists UserDatasIndex3 on UserDatas (key, userId, playbackPositionTicks)", + "create index if not exists UserDatasIndex4 on UserDatas (key, userId, isFavorite)" + })); + + if (userDataTableExists) + { + var existingColumnNames = GetColumnNames(db, "userdata"); - AddColumn(db, "userdata", "AudioStreamIndex", "int", existingColumnNames); - AddColumn(db, "userdata", "SubtitleStreamIndex", "int", existingColumnNames); - }, TransactionMode); + AddColumn(db, "userdata", "InternalUserId", "int", existingColumnNames); + AddColumn(db, "userdata", "AudioStreamIndex", "int", existingColumnNames); + AddColumn(db, "userdata", "SubtitleStreamIndex", "int", existingColumnNames); - try - { - ImportUserDataIfNeeded(connection); - } - catch (Exception ex) - { - Logger.ErrorException("Error in ImportUserDataIfNeeded", ex); - } - } - } + if (!userDatasTableExists) + { + ImportUserIds(db, users); - protected override bool EnableTempStoreMemory - { - get - { - return true; + db.ExecuteAll("INSERT INTO UserDatas (key, userId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex) SELECT key, InternalUserId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex from userdata where InternalUserId not null"); + } + } + }, TransactionMode); } } - private void ImportUserDataIfNeeded(ManagedConnection connection) + private void ImportUserIds(IDatabaseConnection db, User[] users) { - if (!_fileSystem.FileExists(_importFile)) + var userIdsWithUserData = GetAllUserIdsWithUserData(db); + + using (var statement = db.PrepareStatement("update userdata set InternalUserId=@InternalUserId where UserId=@UserId")) { - return; - } + foreach (var user in users) + { + if (!userIdsWithUserData.Contains(user.Id)) + { + continue; + } - var fileToImport = _importFile; - var isImported = connection.Query("select IsUserDataImported from DataSettings").SelectScalarBool().FirstOrDefault(); + statement.TryBind("@UserId", user.Id.ToGuidBlob()); + statement.TryBind("@InternalUserId", user.InternalId); - if (isImported) - { - return; + statement.MoveNext(); + statement.Reset(); + } } + } - ImportUserData(connection, fileToImport); + private List<Guid> GetAllUserIdsWithUserData(IDatabaseConnection db) + { + List<Guid> list = new List<Guid>(); - connection.RunInTransaction(db => + using (var statement = PrepareStatement(db, "select DISTINCT UserId from UserData where UserId not null")) { - using (var statement = db.PrepareStatement("replace into DataSettings (IsUserDataImported) values (@IsUserDataImported)")) + foreach (var row in statement.ExecuteQuery()) { - statement.TryBind("@IsUserDataImported", true); - statement.MoveNext(); + try + { + list.Add(row[0].ReadGuidFromBlob()); + } + catch + { + + } } - }, TransactionMode); + } + + return list; } - private void ImportUserData(ManagedConnection connection, string file) + protected override bool EnableTempStoreMemory { - SqliteExtensions.Attach(connection, file, "UserDataBackup"); - - var columns = "key, userId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex"; - - connection.RunInTransaction(db => + get { - db.Execute("REPLACE INTO userdata(" + columns + ") SELECT " + columns + " FROM UserDataBackup.userdata;"); - }, TransactionMode); + return true; + } } /// <summary> /// Saves the user data. /// </summary> - /// <param name="userId">The user id.</param> - /// <param name="key">The key.</param> - /// <param name="userData">The user data.</param> - /// <param name="cancellationToken">The cancellation token.</param> - /// <returns>Task.</returns> - /// <exception cref="System.ArgumentNullException">userData - /// or - /// cancellationToken - /// or - /// userId - /// or - /// userDataId</exception> - public void SaveUserData(Guid userId, string key, UserItemData userData, CancellationToken cancellationToken) + public void SaveUserData(long internalUserId, string key, UserItemData userData, CancellationToken cancellationToken) { if (userData == null) { throw new ArgumentNullException("userData"); } - if (userId == Guid.Empty) + if (internalUserId <= 0) { - throw new ArgumentNullException("userId"); + throw new ArgumentNullException("internalUserId"); } if (string.IsNullOrEmpty(key)) { throw new ArgumentNullException("key"); } - PersistUserData(userId, key, userData, cancellationToken); + PersistUserData(internalUserId, key, userData, cancellationToken); } - public void SaveAllUserData(Guid userId, UserItemData[] userData, CancellationToken cancellationToken) + public void SaveAllUserData(long internalUserId, UserItemData[] userData, CancellationToken cancellationToken) { if (userData == null) { throw new ArgumentNullException("userData"); } - if (userId == Guid.Empty) + if (internalUserId <= 0) { - throw new ArgumentNullException("userId"); + throw new ArgumentNullException("internalUserId"); } - PersistAllUserData(userId, userData, cancellationToken); + PersistAllUserData(internalUserId, userData, cancellationToken); } /// <summary> @@ -192,7 +187,7 @@ namespace Emby.Server.Implementations.Data /// <param name="userData">The user data.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>Task.</returns> - public void PersistUserData(Guid userId, string key, UserItemData userData, CancellationToken cancellationToken) + public void PersistUserData(long internalUserId, string key, UserItemData userData, CancellationToken cancellationToken) { cancellationToken.ThrowIfCancellationRequested(); @@ -202,17 +197,17 @@ namespace Emby.Server.Implementations.Data { connection.RunInTransaction(db => { - SaveUserData(db, userId, key, userData); + SaveUserData(db, internalUserId, key, userData); }, TransactionMode); } } } - private void SaveUserData(IDatabaseConnection db, Guid userId, string key, UserItemData userData) + private void SaveUserData(IDatabaseConnection db, long internalUserId, string key, UserItemData userData) { - using (var statement = db.PrepareStatement("replace into userdata (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)")) + using (var statement = db.PrepareStatement("replace into UserDatas (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)")) { - statement.TryBind("@userId", userId.ToGuidBlob()); + statement.TryBind("@userId", internalUserId); statement.TryBind("@key", key); if (userData.Rating.HasValue) @@ -263,7 +258,7 @@ namespace Emby.Server.Implementations.Data /// <summary> /// Persist all user data for the specified user /// </summary> - private void PersistAllUserData(Guid userId, UserItemData[] userDataList, CancellationToken cancellationToken) + private void PersistAllUserData(long internalUserId, UserItemData[] userDataList, CancellationToken cancellationToken) { cancellationToken.ThrowIfCancellationRequested(); @@ -275,7 +270,7 @@ namespace Emby.Server.Implementations.Data { foreach (var userItemData in userDataList) { - SaveUserData(db, userId, userItemData.Key, userItemData); + SaveUserData(db, internalUserId, userItemData.Key, userItemData); } }, TransactionMode); } @@ -293,11 +288,11 @@ namespace Emby.Server.Implementations.Data /// or /// key /// </exception> - public UserItemData GetUserData(Guid userId, string key) + public UserItemData GetUserData(long internalUserId, string key) { - if (userId == Guid.Empty) + if (internalUserId <= 0) { - throw new ArgumentNullException("userId"); + throw new ArgumentNullException("internalUserId"); } if (string.IsNullOrEmpty(key)) { @@ -308,9 +303,9 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection(true)) { - using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where key =@Key and userId=@UserId")) + using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from UserDatas where key =@Key and userId=@UserId")) { - statement.TryBind("@UserId", userId.ToGuidBlob()); + statement.TryBind("@UserId", internalUserId); statement.TryBind("@Key", key); foreach (var row in statement.ExecuteQuery()) @@ -324,12 +319,8 @@ namespace Emby.Server.Implementations.Data } } - public UserItemData GetUserData(Guid userId, List<string> keys) + public UserItemData GetUserData(long internalUserId, List<string> keys) { - if (userId == Guid.Empty) - { - throw new ArgumentNullException("userId"); - } if (keys == null) { throw new ArgumentNullException("keys"); @@ -340,7 +331,7 @@ namespace Emby.Server.Implementations.Data return null; } - return GetUserData(userId, keys[0]); + return GetUserData(internalUserId, keys[0]); } /// <summary> @@ -348,11 +339,11 @@ namespace Emby.Server.Implementations.Data /// </summary> /// <param name="userId"></param> /// <returns></returns> - public List<UserItemData> GetAllUserData(Guid userId) + public List<UserItemData> GetAllUserData(long internalUserId) { - if (userId == Guid.Empty) + if (internalUserId <= 0) { - throw new ArgumentNullException("userId"); + throw new ArgumentNullException("internalUserId"); } var list = new List<UserItemData>(); @@ -361,9 +352,9 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection()) { - using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@UserId")) + using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from UserDatas where userId=@UserId")) { - statement.TryBind("@UserId", userId.ToGuidBlob()); + statement.TryBind("@UserId", internalUserId); foreach (var row in statement.ExecuteQuery()) { @@ -385,7 +376,7 @@ namespace Emby.Server.Implementations.Data var userData = new UserItemData(); userData.Key = reader[0].ToString(); - userData.UserId = reader[1].ReadGuidFromBlob(); + //userData.UserId = reader[1].ReadGuidFromBlob(); if (reader[2].SQLiteType != SQLiteType.Null) { @@ -399,7 +390,7 @@ namespace Emby.Server.Implementations.Data if (reader[7].SQLiteType != SQLiteType.Null) { - userData.LastPlayedDate = reader[7].ReadDateTime(); + userData.LastPlayedDate = reader[7].TryReadDateTime(); } if (reader[8].SQLiteType != SQLiteType.Null) diff --git a/Emby.Server.Implementations/Data/SqliteUserRepository.cs b/Emby.Server.Implementations/Data/SqliteUserRepository.cs index e89de11c6..da828aa11 100644 --- a/Emby.Server.Implementations/Data/SqliteUserRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserRepository.cs @@ -18,13 +18,11 @@ namespace Emby.Server.Implementations.Data public class SqliteUserRepository : BaseSqliteRepository, IUserRepository { private readonly IJsonSerializer _jsonSerializer; - private readonly IMemoryStreamFactory _memoryStreamProvider; - public SqliteUserRepository(ILogger logger, IServerApplicationPaths appPaths, IJsonSerializer jsonSerializer, IMemoryStreamFactory memoryStreamProvider) + public SqliteUserRepository(ILogger logger, IServerApplicationPaths appPaths, IJsonSerializer jsonSerializer) : base(logger) { _jsonSerializer = jsonSerializer; - _memoryStreamProvider = memoryStreamProvider; DbFilePath = Path.Combine(appPaths.DataPath, "users.db"); } @@ -51,37 +49,83 @@ namespace Emby.Server.Implementations.Data { RunDefaultInitialization(connection); - string[] queries = { + var localUsersTableExists = TableExists(connection, "LocalUsersv2"); - "create table if not exists users (guid GUID primary key NOT NULL, data BLOB NOT NULL)", - "create index if not exists idx_users on users(guid)", + connection.RunQueries(new[] { + "create table if not exists LocalUsersv2 (Id INTEGER PRIMARY KEY, guid GUID NOT NULL, data BLOB NOT NULL)", + "drop index if exists idx_users" + }); - "pragma shrink_memory" - }; + if (!localUsersTableExists && TableExists(connection, "Users")) + { + TryMigrateToLocalUsersTable(connection); + } + } + } - connection.RunQueries(queries); + private void TryMigrateToLocalUsersTable(ManagedConnection connection) + { + try + { + connection.RunQueries(new[] + { + "INSERT INTO LocalUsersv2 (guid, data) SELECT guid,data from users" + }); + } + catch (Exception ex) + { + Logger.ErrorException("Error migrating users database", ex); } } /// <summary> /// Save a user in the repo /// </summary> - /// <param name="user">The user.</param> - /// <param name="cancellationToken">The cancellation token.</param> - /// <returns>Task.</returns> - /// <exception cref="System.ArgumentNullException">user</exception> - public void SaveUser(User user, CancellationToken cancellationToken) + public void CreateUser(User user) { if (user == null) { throw new ArgumentNullException("user"); } - cancellationToken.ThrowIfCancellationRequested(); + var serialized = _jsonSerializer.SerializeToBytes(user); + + using (WriteLock.Write()) + { + using (var connection = CreateConnection()) + { + connection.RunInTransaction(db => + { + using (var statement = db.PrepareStatement("insert into LocalUsersv2 (guid, data) values (@guid, @data)")) + { + statement.TryBind("@guid", user.Id.ToGuidBlob()); + statement.TryBind("@data", serialized); + + statement.MoveNext(); + } - var serialized = _jsonSerializer.SerializeToBytes(user, _memoryStreamProvider); + var createdUser = GetUser(user.Id, false); - cancellationToken.ThrowIfCancellationRequested(); + if (createdUser == null) + { + throw new ApplicationException("created user should never be null"); + } + + user.InternalId = createdUser.InternalId; + + }, TransactionMode); + } + } + } + + public void UpdateUser(User user) + { + if (user == null) + { + throw new ArgumentNullException("user"); + } + + var serialized = _jsonSerializer.SerializeToBytes(user); using (WriteLock.Write()) { @@ -89,22 +133,59 @@ namespace Emby.Server.Implementations.Data { connection.RunInTransaction(db => { - using (var statement = db.PrepareStatement("replace into users (guid, data) values (@guid, @data)")) + using (var statement = db.PrepareStatement("update LocalUsersv2 set data=@data where Id=@InternalId")) { - statement.TryBind("@guid", user.Id.ToGuidBlob()); + statement.TryBind("@InternalId", user.InternalId); statement.TryBind("@data", serialized); statement.MoveNext(); } + }, TransactionMode); } } } + private User GetUser(Guid guid, bool openLock) + { + using (openLock ? WriteLock.Read() : null) + { + using (var connection = CreateConnection(true)) + { + using (var statement = connection.PrepareStatement("select id,guid,data from LocalUsersv2 where guid=@guid")) + { + statement.TryBind("@guid", guid); + + foreach (var row in statement.ExecuteQuery()) + { + return GetUser(row); + } + } + } + } + + return null; + } + + private User GetUser(IReadOnlyList<IResultSetValue> row) + { + var id = row[0].ToInt64(); + var guid = row[1].ReadGuidFromBlob(); + + using (var stream = new MemoryStream(row[2].ToBlob())) + { + stream.Position = 0; + var user = _jsonSerializer.DeserializeFromStream<User>(stream); + user.InternalId = id; + user.Id = guid; + return user; + } + } + /// <summary> /// Retrieve all users from the database /// </summary> /// <returns>IEnumerable{User}.</returns> - public IEnumerable<User> RetrieveAllUsers() + public List<User> RetrieveAllUsers() { var list = new List<User>(); @@ -112,17 +193,9 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection(true)) { - foreach (var row in connection.Query("select guid,data from users")) + foreach (var row in connection.Query("select id,guid,data from LocalUsersv2")) { - var id = row[0].ReadGuidFromBlob(); - - using (var stream = _memoryStreamProvider.CreateNew(row[1].ToBlob())) - { - stream.Position = 0; - var user = _jsonSerializer.DeserializeFromStream<User>(stream); - user.Id = id; - list.Add(user); - } + list.Add(GetUser(row)); } } } @@ -137,24 +210,22 @@ namespace Emby.Server.Implementations.Data /// <param name="cancellationToken">The cancellation token.</param> /// <returns>Task.</returns> /// <exception cref="System.ArgumentNullException">user</exception> - public void DeleteUser(User user, CancellationToken cancellationToken) + public void DeleteUser(User user) { if (user == null) { throw new ArgumentNullException("user"); } - cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) { using (var connection = CreateConnection()) { connection.RunInTransaction(db => { - using (var statement = db.PrepareStatement("delete from users where guid=@id")) + using (var statement = db.PrepareStatement("delete from LocalUsersv2 where Id=@id")) { - statement.TryBind("@id", user.Id.ToGuidBlob()); + statement.TryBind("@id", user.InternalId); statement.MoveNext(); } }, TransactionMode); |