From 93bf60c9e221564e5d0580174c9e45d135758fe7 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Mon, 23 May 2016 13:51:49 -0400 Subject: add db connect logging --- MediaBrowser.ServerApplication/Native/SqliteExtensions.cs | 13 +++++++++++-- 1 file changed, 11 insertions(+), 2 deletions(-) (limited to 'MediaBrowser.ServerApplication/Native/SqliteExtensions.cs') diff --git a/MediaBrowser.ServerApplication/Native/SqliteExtensions.cs b/MediaBrowser.ServerApplication/Native/SqliteExtensions.cs index 1cde2ea139..4e6c824959 100644 --- a/MediaBrowser.ServerApplication/Native/SqliteExtensions.cs +++ b/MediaBrowser.ServerApplication/Native/SqliteExtensions.cs @@ -54,9 +54,18 @@ namespace MediaBrowser.ServerApplication.Native _logger = logger; } - public Task Connect(string dbPath) + public async Task Connect(string dbPath) { - return SqliteExtensions.ConnectToDb(dbPath, _logger); + try + { + return await SqliteExtensions.ConnectToDb(dbPath, _logger).ConfigureAwait(false); + } + catch (Exception ex) + { + _logger.ErrorException("Error opening database {0}", ex, dbPath); + + throw; + } } } } \ No newline at end of file -- cgit v1.2.3 From 977f62336be3c54fc33b3476d80d3cce4c0b244c Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Tue, 31 May 2016 14:07:54 -0400 Subject: update storage of genres, studios, tags, & keywords --- MediaBrowser.Api/ItemUpdateService.cs | 6 +--- MediaBrowser.Api/SimilarItemsHelper.cs | 8 +----- MediaBrowser.Controller/Entities/BaseItem.cs | 3 ++ MediaBrowser.Controller/Entities/IHasKeywords.cs | 31 -------------------- .../Entities/InternalItemsQuery.cs | 2 ++ .../Entities/KeywordExtensions.cs | 21 ++++++++++++++ MediaBrowser.Controller/Entities/Movies/BoxSet.cs | 9 +----- MediaBrowser.Controller/Entities/Movies/Movie.cs | 4 +-- MediaBrowser.Controller/Entities/Trailer.cs | 4 +-- .../MediaBrowser.Controller.csproj | 2 +- .../Providers/BaseItemXmlParser.cs | 8 ++---- .../Savers/XmlSaverHelpers.cs | 18 +++++------- MediaBrowser.Providers/Manager/ProviderUtils.cs | 10 ++----- .../Movies/GenericMovieDbInfo.cs | 6 +--- .../Dto/DtoService.cs | 11 +------- .../Intros/DefaultIntroProvider.cs | 8 +----- .../Persistence/SqliteItemRepository.cs | 33 +++++++++++++++++----- .../Native/SqliteExtensions.cs | 2 +- .../Native/SqliteExtensions.cs | 2 +- MediaBrowser.XbmcMetadata/Parsers/BaseNfoParser.cs | 8 ++---- MediaBrowser.XbmcMetadata/Savers/BaseNfoSaver.cs | 8 ++---- 21 files changed, 78 insertions(+), 126 deletions(-) delete mode 100644 MediaBrowser.Controller/Entities/IHasKeywords.cs create mode 100644 MediaBrowser.Controller/Entities/KeywordExtensions.cs (limited to 'MediaBrowser.ServerApplication/Native/SqliteExtensions.cs') diff --git a/MediaBrowser.Api/ItemUpdateService.cs b/MediaBrowser.Api/ItemUpdateService.cs index 5bb4ed5f00..36e8a504c8 100644 --- a/MediaBrowser.Api/ItemUpdateService.cs +++ b/MediaBrowser.Api/ItemUpdateService.cs @@ -298,11 +298,7 @@ namespace MediaBrowser.Api hasShortOverview.ShortOverview = request.ShortOverview; } - var hasKeywords = item as IHasKeywords; - if (hasKeywords != null) - { - hasKeywords.Keywords = request.Keywords; - } + item.Keywords = request.Keywords; if (request.Studios != null) { diff --git a/MediaBrowser.Api/SimilarItemsHelper.cs b/MediaBrowser.Api/SimilarItemsHelper.cs index 277bba1dd1..76bc16a96f 100644 --- a/MediaBrowser.Api/SimilarItemsHelper.cs +++ b/MediaBrowser.Api/SimilarItemsHelper.cs @@ -127,13 +127,7 @@ namespace MediaBrowser.Api private static IEnumerable GetKeywords(BaseItem item) { - var hasTags = item as IHasKeywords; - if (hasTags != null) - { - return hasTags.Keywords; - } - - return new List(); + return item.Keywords; } /// diff --git a/MediaBrowser.Controller/Entities/BaseItem.cs b/MediaBrowser.Controller/Entities/BaseItem.cs index 2a00ce992e..78f1828eaf 100644 --- a/MediaBrowser.Controller/Entities/BaseItem.cs +++ b/MediaBrowser.Controller/Entities/BaseItem.cs @@ -37,6 +37,7 @@ namespace MediaBrowser.Controller.Entities { protected BaseItem() { + Keywords = new List(); Tags = new List(); Genres = new List(); Studios = new List(); @@ -811,6 +812,8 @@ namespace MediaBrowser.Controller.Entities [IgnoreDataMember] public List Tags { get; set; } + public List Keywords { get; set; } + /// /// Gets or sets the home page URL. /// diff --git a/MediaBrowser.Controller/Entities/IHasKeywords.cs b/MediaBrowser.Controller/Entities/IHasKeywords.cs deleted file mode 100644 index ab9eb4aeee..0000000000 --- a/MediaBrowser.Controller/Entities/IHasKeywords.cs +++ /dev/null @@ -1,31 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; - -namespace MediaBrowser.Controller.Entities -{ - public interface IHasKeywords - { - /// - /// Gets or sets the keywords. - /// - /// The keywords. - List Keywords { get; set; } - } - - public static class KeywordExtensions - { - public static void AddKeyword(this IHasKeywords item, string name) - { - if (string.IsNullOrWhiteSpace(name)) - { - throw new ArgumentNullException("name"); - } - - if (!item.Keywords.Contains(name, StringComparer.OrdinalIgnoreCase)) - { - item.Keywords.Add(name); - } - } - } -} diff --git a/MediaBrowser.Controller/Entities/InternalItemsQuery.cs b/MediaBrowser.Controller/Entities/InternalItemsQuery.cs index 823f4066c4..7e38d7ed9f 100644 --- a/MediaBrowser.Controller/Entities/InternalItemsQuery.cs +++ b/MediaBrowser.Controller/Entities/InternalItemsQuery.cs @@ -33,6 +33,7 @@ namespace MediaBrowser.Controller.Entities public string[] ExcludeTags { get; set; } public string[] ExcludeInheritedTags { get; set; } public string[] Genres { get; set; } + public string[] Keywords { get; set; } public bool? IsMissing { get; set; } public bool? IsUnaired { get; set; } @@ -151,6 +152,7 @@ namespace MediaBrowser.Controller.Entities OfficialRatings = new string[] { }; SortBy = new string[] { }; MediaTypes = new string[] { }; + Keywords = new string[] { }; IncludeItemTypes = new string[] { }; ExcludeItemTypes = new string[] { }; Genres = new string[] { }; diff --git a/MediaBrowser.Controller/Entities/KeywordExtensions.cs b/MediaBrowser.Controller/Entities/KeywordExtensions.cs new file mode 100644 index 0000000000..5c9afdf3d5 --- /dev/null +++ b/MediaBrowser.Controller/Entities/KeywordExtensions.cs @@ -0,0 +1,21 @@ +using System; +using System.Linq; + +namespace MediaBrowser.Controller.Entities +{ + public static class KeywordExtensions + { + public static void AddKeyword(this BaseItem item, string name) + { + if (string.IsNullOrWhiteSpace(name)) + { + throw new ArgumentNullException("name"); + } + + if (!item.Keywords.Contains(name, StringComparer.OrdinalIgnoreCase)) + { + item.Keywords.Add(name); + } + } + } +} diff --git a/MediaBrowser.Controller/Entities/Movies/BoxSet.cs b/MediaBrowser.Controller/Entities/Movies/BoxSet.cs index 09a9d97bcf..4effc162e4 100644 --- a/MediaBrowser.Controller/Entities/Movies/BoxSet.cs +++ b/MediaBrowser.Controller/Entities/Movies/BoxSet.cs @@ -15,7 +15,7 @@ namespace MediaBrowser.Controller.Entities.Movies /// /// Class BoxSet /// - public class BoxSet : Folder, IHasTrailers, IHasKeywords, IHasDisplayOrder, IHasLookupInfo, IHasShares + public class BoxSet : Folder, IHasTrailers, IHasDisplayOrder, IHasLookupInfo, IHasShares { public List Shares { get; set; } @@ -26,7 +26,6 @@ namespace MediaBrowser.Controller.Entities.Movies RemoteTrailerIds = new List(); DisplayOrder = ItemSortBy.PremiereDate; - Keywords = new List(); Shares = new List(); } @@ -47,12 +46,6 @@ namespace MediaBrowser.Controller.Entities.Movies /// The remote trailers. public List RemoteTrailers { get; set; } - /// - /// Gets or sets the tags. - /// - /// The tags. - public List Keywords { get; set; } - /// /// Gets or sets the display order. /// diff --git a/MediaBrowser.Controller/Entities/Movies/Movie.cs b/MediaBrowser.Controller/Entities/Movies/Movie.cs index 605221dcde..c7a833c58f 100644 --- a/MediaBrowser.Controller/Entities/Movies/Movie.cs +++ b/MediaBrowser.Controller/Entities/Movies/Movie.cs @@ -15,7 +15,7 @@ namespace MediaBrowser.Controller.Entities.Movies /// /// Class Movie /// - public class Movie : Video, IHasCriticRating, IHasSpecialFeatures, IHasProductionLocations, IHasBudget, IHasKeywords, IHasTrailers, IHasThemeMedia, IHasTaglines, IHasAwards, IHasMetascore, IHasLookupInfo, ISupportsBoxSetGrouping, IHasOriginalTitle + public class Movie : Video, IHasCriticRating, IHasSpecialFeatures, IHasProductionLocations, IHasBudget, IHasTrailers, IHasThemeMedia, IHasTaglines, IHasAwards, IHasMetascore, IHasLookupInfo, ISupportsBoxSetGrouping, IHasOriginalTitle { public List SpecialFeatureIds { get; set; } @@ -32,7 +32,6 @@ namespace MediaBrowser.Controller.Entities.Movies ThemeSongIds = new List(); ThemeVideoIds = new List(); Taglines = new List(); - Keywords = new List(); ProductionLocations = new List(); } @@ -42,7 +41,6 @@ namespace MediaBrowser.Controller.Entities.Movies public List LocalTrailerIds { get; set; } public List RemoteTrailerIds { get; set; } - public List Keywords { get; set; } public List RemoteTrailers { get; set; } diff --git a/MediaBrowser.Controller/Entities/Trailer.cs b/MediaBrowser.Controller/Entities/Trailer.cs index 74645bbe91..eab5ab6794 100644 --- a/MediaBrowser.Controller/Entities/Trailer.cs +++ b/MediaBrowser.Controller/Entities/Trailer.cs @@ -12,7 +12,7 @@ namespace MediaBrowser.Controller.Entities /// /// Class Trailer /// - public class Trailer : Video, IHasCriticRating, IHasProductionLocations, IHasBudget, IHasKeywords, IHasTaglines, IHasMetascore, IHasOriginalTitle, IHasLookupInfo + public class Trailer : Video, IHasCriticRating, IHasProductionLocations, IHasBudget, IHasTaglines, IHasMetascore, IHasOriginalTitle, IHasLookupInfo { public List ProductionLocations { get; set; } @@ -31,8 +31,6 @@ namespace MediaBrowser.Controller.Entities public List RemoteTrailers { get; set; } - public List Keywords { get; set; } - [IgnoreDataMember] public bool IsLocalTrailer { diff --git a/MediaBrowser.Controller/MediaBrowser.Controller.csproj b/MediaBrowser.Controller/MediaBrowser.Controller.csproj index 4d9999b37d..b15bb94c78 100644 --- a/MediaBrowser.Controller/MediaBrowser.Controller.csproj +++ b/MediaBrowser.Controller/MediaBrowser.Controller.csproj @@ -142,7 +142,7 @@ - + diff --git a/MediaBrowser.Controller/Providers/BaseItemXmlParser.cs b/MediaBrowser.Controller/Providers/BaseItemXmlParser.cs index 1014fc2ee9..aaa440060c 100644 --- a/MediaBrowser.Controller/Providers/BaseItemXmlParser.cs +++ b/MediaBrowser.Controller/Providers/BaseItemXmlParser.cs @@ -816,11 +816,7 @@ namespace MediaBrowser.Controller.Providers { using (var subtree = reader.ReadSubtree()) { - var hasTags = item as IHasKeywords; - if (hasTags != null) - { - FetchFromKeywordsNode(subtree, hasTags); - } + FetchFromKeywordsNode(subtree, item); } break; } @@ -1099,7 +1095,7 @@ namespace MediaBrowser.Controller.Providers } } - private void FetchFromKeywordsNode(XmlReader reader, IHasKeywords item) + private void FetchFromKeywordsNode(XmlReader reader, BaseItem item) { reader.MoveToContent(); diff --git a/MediaBrowser.LocalMetadata/Savers/XmlSaverHelpers.cs b/MediaBrowser.LocalMetadata/Savers/XmlSaverHelpers.cs index be81d21d20..ca19b403ab 100644 --- a/MediaBrowser.LocalMetadata/Savers/XmlSaverHelpers.cs +++ b/MediaBrowser.LocalMetadata/Savers/XmlSaverHelpers.cs @@ -609,20 +609,16 @@ namespace MediaBrowser.LocalMetadata.Savers } } - var hasKeywords = item as IHasKeywords; - if (hasKeywords != null) + if (item.Keywords.Count > 0) { - if (hasKeywords.Keywords.Count > 0) - { - builder.Append(""); - - foreach (var tag in hasKeywords.Keywords) - { - builder.Append("" + SecurityElement.Escape(tag) + ""); - } + builder.Append(""); - builder.Append(""); + foreach (var tag in item.Keywords) + { + builder.Append("" + SecurityElement.Escape(tag) + ""); } + + builder.Append(""); } var people = libraryManager.GetPeople(item); diff --git a/MediaBrowser.Providers/Manager/ProviderUtils.cs b/MediaBrowser.Providers/Manager/ProviderUtils.cs index 59a2da4603..a6f02f3f74 100644 --- a/MediaBrowser.Providers/Manager/ProviderUtils.cs +++ b/MediaBrowser.Providers/Manager/ProviderUtils.cs @@ -165,15 +165,9 @@ namespace MediaBrowser.Providers.Manager if (!lockedFields.Contains(MetadataFields.Keywords)) { - var sourceHasKeywords = source as IHasKeywords; - var targetHasKeywords = target as IHasKeywords; - - if (sourceHasKeywords != null && targetHasKeywords != null) + if (replaceData || target.Keywords.Count == 0) { - if (replaceData || targetHasKeywords.Keywords.Count == 0) - { - targetHasKeywords.Keywords = sourceHasKeywords.Keywords; - } + target.Keywords = source.Keywords; } } diff --git a/MediaBrowser.Providers/Movies/GenericMovieDbInfo.cs b/MediaBrowser.Providers/Movies/GenericMovieDbInfo.cs index d13716cba1..3b3065893c 100644 --- a/MediaBrowser.Providers/Movies/GenericMovieDbInfo.cs +++ b/MediaBrowser.Providers/Movies/GenericMovieDbInfo.cs @@ -314,11 +314,7 @@ namespace MediaBrowser.Providers.Movies if (movieData.keywords != null && movieData.keywords.keywords != null) { - var hasTags = movie as IHasKeywords; - if (hasTags != null) - { - hasTags.Keywords = movieData.keywords.keywords.Select(i => i.name).ToList(); - } + movie.Keywords = movieData.keywords.keywords.Select(i => i.name).ToList(); } if (movieData.trailers != null && movieData.trailers.youtube != null && diff --git a/MediaBrowser.Server.Implementations/Dto/DtoService.cs b/MediaBrowser.Server.Implementations/Dto/DtoService.cs index eb868d3630..bfcdb2a264 100644 --- a/MediaBrowser.Server.Implementations/Dto/DtoService.cs +++ b/MediaBrowser.Server.Implementations/Dto/DtoService.cs @@ -983,16 +983,7 @@ namespace MediaBrowser.Server.Implementations.Dto if (fields.Contains(ItemFields.Keywords)) { - var hasTags = item as IHasKeywords; - if (hasTags != null) - { - dto.Keywords = hasTags.Keywords; - } - - if (dto.Keywords == null) - { - dto.Keywords = new List(); - } + dto.Keywords = item.Keywords; } if (fields.Contains(ItemFields.ProductionLocations)) diff --git a/MediaBrowser.Server.Implementations/Intros/DefaultIntroProvider.cs b/MediaBrowser.Server.Implementations/Intros/DefaultIntroProvider.cs index 49012c65af..df128a90b0 100644 --- a/MediaBrowser.Server.Implementations/Intros/DefaultIntroProvider.cs +++ b/MediaBrowser.Server.Implementations/Intros/DefaultIntroProvider.cs @@ -433,13 +433,7 @@ namespace MediaBrowser.Server.Implementations.Intros private static IEnumerable GetKeywords(BaseItem item) { - var hasTags = item as IHasKeywords; - if (hasTags != null) - { - return hasTags.Keywords; - } - - return new List(); + return item.Keywords; } public IEnumerable GetAllIntroFiles() diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 1de0ea7108..a85bf1c81b 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -87,7 +87,7 @@ namespace MediaBrowser.Server.Implementations.Persistence private IDbCommand _updateInheritedRatingCommand; private IDbCommand _updateInheritedTagsCommand; - public const int LatestSchemaVersion = 80; + public const int LatestSchemaVersion = 82; /// /// Initializes a new instance of the class. @@ -2489,8 +2489,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Genres) { - clauses.Add("Genres like @Genres" + index); - cmd.Parameters.Add(cmd, "@Genres" + index, DbType.String).Value = "%" + item + "%"; + clauses.Add("@Genre" + index + " in (select value from itemvalues where ItemId=Guid and Type=2)"); + cmd.Parameters.Add(cmd, "@Genre" + index, DbType.String).Value = item; index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2503,8 +2503,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Tags) { - clauses.Add("Tags like @Tags" + index); - cmd.Parameters.Add(cmd, "@Tags" + index, DbType.String).Value = "%" + item + "%"; + clauses.Add("@Tag" + index + " in (select value from itemvalues where ItemId=Guid and Type=4)"); + cmd.Parameters.Add(cmd, "@Tag" + index, DbType.String).Value = item; index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2517,8 +2517,22 @@ namespace MediaBrowser.Server.Implementations.Persistence var index = 0; foreach (var item in query.Studios) { - clauses.Add("Studios like @Studios" + index); - cmd.Parameters.Add(cmd, "@Studios" + index, DbType.String).Value = "%" + item + "%"; + clauses.Add("@Studio" + index + " in (select value from itemvalues where ItemId=Guid and Type=3)"); + cmd.Parameters.Add(cmd, "@Studio" + index, DbType.String).Value = item; + index++; + } + var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); + } + + if (query.Keywords.Length > 0) + { + var clauses = new List(); + var index = 0; + foreach (var item in query.Keywords) + { + clauses.Add("@Keyword" + index + " in (select value from itemvalues where ItemId=Guid and Type=5)"); + cmd.Parameters.Add(cmd, "@Keyword" + index, DbType.String).Value = item; index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -3233,6 +3247,11 @@ namespace MediaBrowser.Server.Implementations.Persistence list.AddRange(hasAlbumArtist.AlbumArtists.Select(i => new Tuple(1, i))); } + list.AddRange(item.Genres.Select(i => new Tuple(2, i))); + list.AddRange(item.Studios.Select(i => new Tuple(3, i))); + list.AddRange(item.Tags.Select(i => new Tuple(4, i))); + list.AddRange(item.Keywords.Select(i => new Tuple(5, i))); + return list; } diff --git a/MediaBrowser.Server.Mono/Native/SqliteExtensions.cs b/MediaBrowser.Server.Mono/Native/SqliteExtensions.cs index 385a7d0c58..ca2327282e 100644 --- a/MediaBrowser.Server.Mono/Native/SqliteExtensions.cs +++ b/MediaBrowser.Server.Mono/Native/SqliteExtensions.cs @@ -32,7 +32,7 @@ namespace MediaBrowser.Server.Mono.Native { PageSize = 4096, CacheSize = 2000, - SyncMode = SynchronizationModes.Full, + SyncMode = SynchronizationModes.Normal, DataSource = dbPath, JournalMode = SQLiteJournalModeEnum.Wal }; diff --git a/MediaBrowser.ServerApplication/Native/SqliteExtensions.cs b/MediaBrowser.ServerApplication/Native/SqliteExtensions.cs index 4e6c824959..bdf5c33237 100644 --- a/MediaBrowser.ServerApplication/Native/SqliteExtensions.cs +++ b/MediaBrowser.ServerApplication/Native/SqliteExtensions.cs @@ -32,7 +32,7 @@ namespace MediaBrowser.ServerApplication.Native { PageSize = 4096, CacheSize = 2000, - SyncMode = SynchronizationModes.Full, + SyncMode = SynchronizationModes.Normal, DataSource = dbPath, JournalMode = SQLiteJournalModeEnum.Wal }; diff --git a/MediaBrowser.XbmcMetadata/Parsers/BaseNfoParser.cs b/MediaBrowser.XbmcMetadata/Parsers/BaseNfoParser.cs index d020a73fe0..ad1c6802d2 100644 --- a/MediaBrowser.XbmcMetadata/Parsers/BaseNfoParser.cs +++ b/MediaBrowser.XbmcMetadata/Parsers/BaseNfoParser.cs @@ -932,13 +932,9 @@ namespace MediaBrowser.XbmcMetadata.Parsers { var val = reader.ReadElementContentAsString(); - var hasKeywords = item as IHasKeywords; - if (hasKeywords != null) + if (!string.IsNullOrWhiteSpace(val)) { - if (!string.IsNullOrWhiteSpace(val)) - { - hasKeywords.AddKeyword(val); - } + item.AddKeyword(val); } break; } diff --git a/MediaBrowser.XbmcMetadata/Savers/BaseNfoSaver.cs b/MediaBrowser.XbmcMetadata/Savers/BaseNfoSaver.cs index d2e09d4ebf..5bb9577ff3 100644 --- a/MediaBrowser.XbmcMetadata/Savers/BaseNfoSaver.cs +++ b/MediaBrowser.XbmcMetadata/Savers/BaseNfoSaver.cs @@ -752,13 +752,9 @@ namespace MediaBrowser.XbmcMetadata.Savers } } - var hasKeywords = item as IHasKeywords; - if (hasKeywords != null) + foreach (var tag in item.Keywords) { - foreach (var tag in hasKeywords.Keywords) - { - writer.WriteElementString("plotkeyword", tag); - } + writer.WriteElementString("plotkeyword", tag); } var hasAwards = item as IHasAwards; -- cgit v1.2.3 From e1f562e16ff585b440a43029efe9db314b4de965 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Wed, 1 Jun 2016 01:50:00 -0400 Subject: calculate similarity at database level --- MediaBrowser.Api/GamesService.cs | 41 +++-- MediaBrowser.Api/Movies/MoviesService.cs | 114 +++++--------- MediaBrowser.Api/TvShowsService.cs | 41 +++-- .../Entities/InternalItemsQuery.cs | 2 + .../Persistence/IDbConnector.cs | 1 + .../Persistence/SqliteExtensions.cs | 175 +++++++++++++++++++++ .../Persistence/SqliteItemRepository.cs | 80 +++++++--- .../MediaBrowser.Server.Mono.csproj | 5 +- MediaBrowser.Server.Mono/Native/DbConnector.cs | 29 ++++ .../Native/SqliteExtensions.cs | 62 -------- .../MediaBrowser.ServerApplication.csproj | 5 +- .../Native/DbConnector.cs | 38 +++++ .../Native/SqliteExtensions.cs | 71 --------- 13 files changed, 409 insertions(+), 255 deletions(-) create mode 100644 MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs create mode 100644 MediaBrowser.Server.Mono/Native/DbConnector.cs delete mode 100644 MediaBrowser.Server.Mono/Native/SqliteExtensions.cs create mode 100644 MediaBrowser.ServerApplication/Native/DbConnector.cs delete mode 100644 MediaBrowser.ServerApplication/Native/SqliteExtensions.cs (limited to 'MediaBrowser.ServerApplication/Native/SqliteExtensions.cs') diff --git a/MediaBrowser.Api/GamesService.cs b/MediaBrowser.Api/GamesService.cs index 387771b6d5..cb77e62adf 100644 --- a/MediaBrowser.Api/GamesService.cs +++ b/MediaBrowser.Api/GamesService.cs @@ -10,6 +10,7 @@ using System.Collections.Generic; using System.Globalization; using System.IO; using System.Linq; +using MediaBrowser.Model.Querying; namespace MediaBrowser.Api { @@ -187,18 +188,40 @@ namespace MediaBrowser.Api /// System.Object. public object Get(GetSimilarGames request) { + var result = GetSimilarItemsResult(request); + + return ToOptimizedSerializedResultUsingCache(result); + } + + private QueryResult GetSimilarItemsResult(BaseGetSimilarItemsFromItem request) + { + var user = !string.IsNullOrWhiteSpace(request.UserId) ? _userManager.GetUserById(request.UserId) : null; + + var item = string.IsNullOrEmpty(request.Id) ? + (!string.IsNullOrWhiteSpace(request.UserId) ? user.RootFolder : + _libraryManager.RootFolder) : _libraryManager.GetItemById(request.Id); + + var itemsResult = _libraryManager.GetItemList(new InternalItemsQuery(user) + { + Limit = request.Limit, + IncludeItemTypes = new[] + { + typeof(Game).Name + }, + SimilarTo = item + + }).ToList(); + var dtoOptions = GetDtoOptions(request); - var result = SimilarItemsHelper.GetSimilarItemsResult(dtoOptions, _userManager, - _itemRepo, - _libraryManager, - _userDataRepository, - _dtoService, - Logger, - request, new[] { typeof(Game) }, - SimilarItemsHelper.GetSimiliarityScore); + var result = new QueryResult + { + Items = _dtoService.GetBaseItemDtos(itemsResult, dtoOptions, user).ToArray(), - return ToOptimizedSerializedResultUsingCache(result); + TotalRecordCount = itemsResult.Count + }; + + return result; } } } diff --git a/MediaBrowser.Api/Movies/MoviesService.cs b/MediaBrowser.Api/Movies/MoviesService.cs index ce36dd2ac9..ff18d440c1 100644 --- a/MediaBrowser.Api/Movies/MoviesService.cs +++ b/MediaBrowser.Api/Movies/MoviesService.cs @@ -111,18 +111,16 @@ namespace MediaBrowser.Api.Movies /// /// The request. /// System.Object. - public async Task Get(GetSimilarMovies request) + public object Get(GetSimilarMovies request) { - var result = await GetSimilarItemsResult( - request, SimilarItemsHelper.GetSimiliarityScore).ConfigureAwait(false); + var result = GetSimilarItemsResult(request); return ToOptimizedSerializedResultUsingCache(result); } - public async Task Get(GetSimilarTrailers request) + public object Get(GetSimilarTrailers request) { - var result = await GetSimilarItemsResult( - request, SimilarItemsHelper.GetSimiliarityScore).ConfigureAwait(false); + var result = GetSimilarItemsResult(request); return ToOptimizedSerializedResultUsingCache(result); } @@ -131,42 +129,16 @@ namespace MediaBrowser.Api.Movies { var user = _userManager.GetUserById(request.UserId); - var query = new InternalItemsQuery(user) - { - IncludeItemTypes = new[] - { - typeof(Movie).Name, - typeof(Trailer).Name, - //typeof(LiveTvProgram).Name - }, - // IsMovie = true - }; - - var parentIds = string.IsNullOrWhiteSpace(request.ParentId) ? new string[] { } : new[] { request.ParentId }; - var movies = _libraryManager.GetItemList(query, parentIds) - .OrderBy(i => (int)i.SourceType); - - var listEligibleForSuggestion = new List(); - - var list = movies.ToList(); - - listEligibleForSuggestion.AddRange(list); - - listEligibleForSuggestion = listEligibleForSuggestion - .DistinctBy(i => i.Name, StringComparer.OrdinalIgnoreCase) - .DistinctBy(i => i.GetProviderId(MetadataProviders.Imdb) ?? Guid.NewGuid().ToString(), StringComparer.OrdinalIgnoreCase) - .ToList(); - var dtoOptions = GetDtoOptions(request); dtoOptions.Fields = request.GetItemFields().ToList(); - var result = GetRecommendationCategories(user, request.ParentId, listEligibleForSuggestion, request.CategoryLimit, request.ItemLimit, dtoOptions); + var result = GetRecommendationCategories(user, request.ParentId, request.CategoryLimit, request.ItemLimit, dtoOptions); return ToOptimizedResult(result); } - private async Task GetSimilarItemsResult(BaseGetSimilarItemsFromItem request, Func, List, BaseItem, int> getSimilarityScore) + private QueryResult GetSimilarItemsResult(BaseGetSimilarItemsFromItem request) { var user = !string.IsNullOrWhiteSpace(request.UserId) ? _userManager.GetUserById(request.UserId) : null; @@ -174,57 +146,32 @@ namespace MediaBrowser.Api.Movies (!string.IsNullOrWhiteSpace(request.UserId) ? user.RootFolder : _libraryManager.RootFolder) : _libraryManager.GetItemById(request.Id); - var query = new InternalItemsQuery(user) + var itemsResult = _libraryManager.GetItemList(new InternalItemsQuery(user) { + Limit = request.Limit, IncludeItemTypes = new[] { - typeof(Movie).Name, - typeof(Trailer).Name, - //typeof(LiveTvProgram).Name + typeof(Movie).Name, + typeof(Trailer).Name, + typeof(LiveTvProgram).Name }, - //IsMovie = true - }; - - var list = _libraryManager.GetItemList(query) - .OrderBy(i => (int)i.SourceType) - .DistinctBy(i => i.GetProviderId(MetadataProviders.Imdb) ?? Guid.NewGuid().ToString("N")) - .ToList(); - - if (item is Video) - { - var imdbId = item.GetProviderId(MetadataProviders.Imdb); - - // Use imdb id to try to filter duplicates of the same item - if (!string.IsNullOrWhiteSpace(imdbId)) - { - list = list - .Where(i => !string.Equals(imdbId, i.GetProviderId(MetadataProviders.Imdb), StringComparison.OrdinalIgnoreCase)) - .ToList(); - } - } - - var items = SimilarItemsHelper.GetSimilaritems(item, _libraryManager, list, getSimilarityScore).ToList(); - - IEnumerable returnItems = items; - - if (request.Limit.HasValue) - { - returnItems = returnItems.Take(request.Limit.Value); - } + IsMovie = true, + SimilarTo = item + }).ToList(); var dtoOptions = GetDtoOptions(request); - var result = new ItemsResult + var result = new QueryResult { - Items = _dtoService.GetBaseItemDtos(returnItems, dtoOptions, user).ToArray(), + Items = _dtoService.GetBaseItemDtos(itemsResult, dtoOptions, user).ToArray(), - TotalRecordCount = items.Count + TotalRecordCount = itemsResult.Count }; return result; } - private IEnumerable GetRecommendationCategories(User user, string parentId, List allMovies, int categoryLimit, int itemLimit, DtoOptions dtoOptions) + private IEnumerable GetRecommendationCategories(User user, string parentId, int categoryLimit, int itemLimit, DtoOptions dtoOptions) { var categories = new List(); @@ -260,7 +207,7 @@ namespace MediaBrowser.Api.Movies IsFavoriteOrLiked = true, ExcludeItemIds = recentlyPlayedMovies.Select(i => i.Id.ToString("N")).ToArray() - }, parentIds); + }, parentIds).ToList(); var mostRecentMovies = recentlyPlayedMovies.Take(6).ToList(); // Get recently played directors @@ -273,8 +220,8 @@ namespace MediaBrowser.Api.Movies .OrderBy(i => Guid.NewGuid()) .ToList(); - var similarToRecentlyPlayed = GetSimilarTo(user, allMovies, recentlyPlayedMovies.Take(7).OrderBy(i => Guid.NewGuid()), itemLimit, dtoOptions, RecommendationType.SimilarToRecentlyPlayed).GetEnumerator(); - var similarToLiked = GetSimilarTo(user, allMovies, likedMovies, itemLimit, dtoOptions, RecommendationType.SimilarToLikedItem).GetEnumerator(); + var similarToRecentlyPlayed = GetSimilarTo(user, recentlyPlayedMovies, itemLimit, dtoOptions, RecommendationType.SimilarToRecentlyPlayed).GetEnumerator(); + var similarToLiked = GetSimilarTo(user, likedMovies, itemLimit, dtoOptions, RecommendationType.SimilarToLikedItem).GetEnumerator(); var hasDirectorFromRecentlyPlayed = GetWithDirector(user, recentDirectors, itemLimit, dtoOptions, RecommendationType.HasDirectorFromRecentlyPlayed).GetEnumerator(); var hasActorFromRecentlyPlayed = GetWithActor(user, recentActors, itemLimit, dtoOptions, RecommendationType.HasActorFromRecentlyPlayed).GetEnumerator(); @@ -389,14 +336,23 @@ namespace MediaBrowser.Api.Movies } } - private IEnumerable GetSimilarTo(User user, List allMovies, IEnumerable baselineItems, int itemLimit, DtoOptions dtoOptions, RecommendationType type) + private IEnumerable GetSimilarTo(User user, List baselineItems, int itemLimit, DtoOptions dtoOptions, RecommendationType type) { foreach (var item in baselineItems) { - var similar = SimilarItemsHelper - .GetSimilaritems(item, _libraryManager, allMovies, SimilarItemsHelper.GetSimiliarityScore) - .Take(itemLimit) - .ToList(); + var similar = _libraryManager.GetItemList(new InternalItemsQuery(user) + { + Limit = itemLimit, + IncludeItemTypes = new[] + { + typeof(Movie).Name, + typeof(Trailer).Name, + typeof(LiveTvProgram).Name + }, + IsMovie = true, + SimilarTo = item + + }).ToList(); if (similar.Count > 0) { diff --git a/MediaBrowser.Api/TvShowsService.cs b/MediaBrowser.Api/TvShowsService.cs index aa0485d57e..5ccfede1ef 100644 --- a/MediaBrowser.Api/TvShowsService.cs +++ b/MediaBrowser.Api/TvShowsService.cs @@ -12,6 +12,7 @@ using ServiceStack; using System; using System.Collections.Generic; using System.Linq; +using MediaBrowser.Model.Dto; namespace MediaBrowser.Api { @@ -273,18 +274,40 @@ namespace MediaBrowser.Api /// System.Object. public object Get(GetSimilarShows request) { + var result = GetSimilarItemsResult(request); + + return ToOptimizedSerializedResultUsingCache(result); + } + + private QueryResult GetSimilarItemsResult(BaseGetSimilarItemsFromItem request) + { + var user = !string.IsNullOrWhiteSpace(request.UserId) ? _userManager.GetUserById(request.UserId) : null; + + var item = string.IsNullOrEmpty(request.Id) ? + (!string.IsNullOrWhiteSpace(request.UserId) ? user.RootFolder : + _libraryManager.RootFolder) : _libraryManager.GetItemById(request.Id); + + var itemsResult = _libraryManager.GetItemList(new InternalItemsQuery(user) + { + Limit = request.Limit, + IncludeItemTypes = new[] + { + typeof(Series).Name + }, + SimilarTo = item + + }).ToList(); + var dtoOptions = GetDtoOptions(request); - var result = SimilarItemsHelper.GetSimilarItemsResult(dtoOptions, _userManager, - _itemRepo, - _libraryManager, - _userDataManager, - _dtoService, - Logger, - request, new[] { typeof(Series) }, - SimilarItemsHelper.GetSimiliarityScore); + var result = new QueryResult + { + Items = _dtoService.GetBaseItemDtos(itemsResult, dtoOptions, user).ToArray(), - return ToOptimizedSerializedResultUsingCache(result); + TotalRecordCount = itemsResult.Count + }; + + return result; } public object Get(GetUpcomingEpisodes request) diff --git a/MediaBrowser.Controller/Entities/InternalItemsQuery.cs b/MediaBrowser.Controller/Entities/InternalItemsQuery.cs index f3f05a08fa..0047a13b28 100644 --- a/MediaBrowser.Controller/Entities/InternalItemsQuery.cs +++ b/MediaBrowser.Controller/Entities/InternalItemsQuery.cs @@ -19,6 +19,8 @@ namespace MediaBrowser.Controller.Entities public User User { get; set; } + public BaseItem SimilarTo { get; set; } + public bool? IsFolder { get; set; } public bool? IsFavorite { get; set; } public bool? IsFavoriteOrLiked { get; set; } diff --git a/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs b/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs index cac9fe9835..985d79a0ac 100644 --- a/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs +++ b/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs @@ -6,5 +6,6 @@ namespace MediaBrowser.Server.Implementations.Persistence public interface IDbConnector { Task Connect(string dbPath); + void BindSimilarityScoreFunction(IDbConnection connection); } } diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs new file mode 100644 index 0000000000..5e07bac310 --- /dev/null +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs @@ -0,0 +1,175 @@ +using System; +using System.Collections.Generic; +using System.Data; +using System.Data.SQLite; +using System.Linq; +using System.Text; +using System.Threading.Tasks; +using MediaBrowser.Model.Logging; + +namespace MediaBrowser.Server.Implementations.Persistence +{ + /// + /// Class SQLiteExtensions + /// + public static class SqliteExtensions + { + /// + /// Connects to db. + /// + /// The db path. + /// The logger. + /// Task{IDbConnection}. + /// dbPath + public static async Task ConnectToDb(string dbPath, ILogger logger) + { + if (string.IsNullOrEmpty(dbPath)) + { + throw new ArgumentNullException("dbPath"); + } + + logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, dbPath); + + var connectionstr = new SQLiteConnectionStringBuilder + { + PageSize = 4096, + CacheSize = 2000, + SyncMode = SynchronizationModes.Normal, + DataSource = dbPath, + JournalMode = SQLiteJournalModeEnum.Wal + }; + + var connection = new SQLiteConnection(connectionstr.ConnectionString); + + await connection.OpenAsync().ConfigureAwait(false); + + return connection; + } + + public static void BindGetSimilarityScore(IDbConnection connection, ILogger logger) + { + var sqlConnection = (SQLiteConnection) connection; + SimiliarToFunction.Logger = logger; + sqlConnection.BindFunction(new SimiliarToFunction()); + } + + public static void BindFunction(this SQLiteConnection connection, SQLiteFunction function) + { + var attributes = function.GetType().GetCustomAttributes(typeof(SQLiteFunctionAttribute), true).Cast().ToArray(); + if (attributes.Length == 0) + { + throw new InvalidOperationException("SQLiteFunction doesn't have SQLiteFunctionAttribute"); + } + connection.BindFunction(attributes[0], function); + } + } + + [SQLiteFunction(Name = "GetSimilarityScore", Arguments = 12, FuncType = FunctionType.Scalar)] + public class SimiliarToFunction : SQLiteFunction + { + internal static ILogger Logger; + + public override object Invoke(object[] args) + { + var score = 0; + + var inputOfficialRating = args[0] as string; + var rowOfficialRating = args[1] as string; + if (!string.IsNullOrWhiteSpace(inputOfficialRating) && string.Equals(inputOfficialRating, rowOfficialRating)) + { + score += 10; + } + + long? inputYear = args[2] == null ? (long?)null : (long)args[2]; + long? rowYear = args[3] == null ? (long?)null : (long)args[3]; + + if (inputYear.HasValue && rowYear.HasValue) + { + var diff = Math.Abs(inputYear.Value - rowYear.Value); + + // Add if they came out within the same decade + if (diff < 10) + { + score += 2; + } + + // And more if within five years + if (diff < 5) + { + score += 2; + } + } + + // genres + score += GetListScore(args, 4, 5); + + // tags + score += GetListScore(args, 6, 7); + + // keywords + score += GetListScore(args, 8, 9); + + // studios + score += GetListScore(args, 10, 11, 3); + + + // TODO: People + // var item2PeopleNames = allPeople.Where(i => i.ItemId == item2.Id) + //.Select(i => i.Name) + //.Where(i => !string.IsNullOrWhiteSpace(i)) + //.DistinctNames() + //.ToDictionary(i => i, StringComparer.OrdinalIgnoreCase); + + // points += item1People.Where(i => item2PeopleNames.ContainsKey(i.Name)).Sum(i => + // { + // if (string.Equals(i.Type, PersonType.Director, StringComparison.OrdinalIgnoreCase) || string.Equals(i.Role, PersonType.Director, StringComparison.OrdinalIgnoreCase)) + // { + // return 5; + // } + // if (string.Equals(i.Type, PersonType.Actor, StringComparison.OrdinalIgnoreCase) || string.Equals(i.Role, PersonType.Actor, StringComparison.OrdinalIgnoreCase)) + // { + // return 3; + // } + // if (string.Equals(i.Type, PersonType.Composer, StringComparison.OrdinalIgnoreCase) || string.Equals(i.Role, PersonType.Composer, StringComparison.OrdinalIgnoreCase)) + // { + // return 3; + // } + // if (string.Equals(i.Type, PersonType.GuestStar, StringComparison.OrdinalIgnoreCase) || string.Equals(i.Role, PersonType.GuestStar, StringComparison.OrdinalIgnoreCase)) + // { + // return 3; + // } + // if (string.Equals(i.Type, PersonType.Writer, StringComparison.OrdinalIgnoreCase) || string.Equals(i.Role, PersonType.Writer, StringComparison.OrdinalIgnoreCase)) + // { + // return 2; + // } + + // return 1; + // }); + + // return points; + + //Logger.Debug("Returning score {0}", score); + return score; + } + + private int GetListScore(object[] args, int index1, int index2, int value = 10) + { + var score = 0; + + var inputGenres = args[index1] as string; + var rowGenres = args[index2] as string; + var inputGenreList = string.IsNullOrWhiteSpace(inputGenres) ? new string[] { } : inputGenres.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + var rowGenresList = string.IsNullOrWhiteSpace(rowGenres) ? new string[] { } : rowGenres.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries); + + foreach (var genre in inputGenreList) + { + if (rowGenresList.Contains(genre, StringComparer.OrdinalIgnoreCase)) + { + score += value; + } + } + + return score; + } + } +} diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 3149352a90..131158dd2d 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -15,6 +15,7 @@ using System.Globalization; using System.IO; using System.Linq; using System.Runtime.Serialization; +using System.Text; using System.Threading; using System.Threading.Tasks; using MediaBrowser.Common.Extensions; @@ -258,6 +259,8 @@ namespace MediaBrowser.Server.Implementations.Persistence new MediaStreamColumns(_connection, Logger).AddColumns(); DataExtensions.Attach(_connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb"); + + dbConnector.BindSimilarityScoreFunction(_connection); } private readonly string[] _retriveItemColumns = @@ -1575,7 +1578,7 @@ namespace MediaBrowser.Server.Implementations.Persistence return false; } - private string[] GetFinalColumnsToSelect(InternalItemsQuery query, string[] startColumns) + private string[] GetFinalColumnsToSelect(InternalItemsQuery query, string[] startColumns, IDbCommand cmd) { var list = startColumns.ToList(); @@ -1590,6 +1593,45 @@ namespace MediaBrowser.Server.Implementations.Persistence list.Add("UserDataDb.UserData.rating"); } + if (query.SimilarTo != null) + { + var item = query.SimilarTo; + + var builder = new StringBuilder(); + builder.Append("GetSimilarityScore("); + + builder.Append("@ItemOfficialRating,"); + builder.Append("OfficialRating,"); + + builder.Append("@ItemProductionYear,"); + builder.Append("ProductionYear,"); + + builder.Append("@ItemGenres,"); + builder.Append("Genres,"); + + builder.Append("@ItemTags,"); + builder.Append("Tags,"); + + builder.Append("@ItemKeywords,"); + builder.Append("(select group_concat((Select Value from ItemValues where ItemId=Guid and Type=5), '|')),"); + + builder.Append("@ItemStudios,"); + builder.Append("Studios"); + builder.Append(") as SimilarityScore"); + + list.Add(builder.ToString()); + cmd.Parameters.Add(cmd, "@ItemOfficialRating", DbType.String).Value = item.OfficialRating; + cmd.Parameters.Add(cmd, "@ItemProductionYear", DbType.Int32).Value = item.ProductionYear ?? -1; + cmd.Parameters.Add(cmd, "@ItemGenres", DbType.String).Value = string.Join("|", item.Genres.ToArray()); + cmd.Parameters.Add(cmd, "@ItemTags", DbType.String).Value = string.Join("|", item.Tags.ToArray()); + cmd.Parameters.Add(cmd, "@ItemKeywords", DbType.String).Value = string.Join("|", item.Keywords.ToArray()); + cmd.Parameters.Add(cmd, "@ItemStudios", DbType.String).Value = string.Join("|", item.Studios.ToArray()); + + var excludeIds = query.ExcludeItemIds.ToList(); + excludeIds.Add(item.Id.ToString("N")); + query.ExcludeItemIds = excludeIds.ToArray(); + } + return list.ToArray(); } @@ -1616,7 +1658,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns)) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems"; cmd.CommandText += GetJoinUserDataText(query); if (EnableJoinUserData(query)) @@ -1706,7 +1748,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns)) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems"; cmd.CommandText += GetJoinUserDataText(query); if (EnableJoinUserData(query)) @@ -1789,6 +1831,15 @@ namespace MediaBrowser.Server.Implementations.Persistence private string GetOrderByText(InternalItemsQuery query) { + if (query.SimilarTo != null) + { + if (query.SortBy == null || query.SortBy.Length == 0) + { + query.SortBy = new[] { "SimilarityScore", "Random" }; + query.SortOrder = SortOrder.Descending; + } + } + if (query.SortBy == null || query.SortBy.Length == 0) { return string.Empty; @@ -1879,7 +1930,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" })) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems"; cmd.CommandText += GetJoinUserDataText(query); if (EnableJoinUserData(query)) @@ -2022,7 +2073,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" })) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems"; var whereClauses = GetWhereClauses(query, cmd); cmd.CommandText += GetJoinUserDataText(query); @@ -2148,24 +2199,7 @@ namespace MediaBrowser.Server.Implementations.Persistence } else { - if (query.IsMovie.Value) - { - var typeClauses = new List(); - var typeIndex = 0; - foreach (var type in alternateTypes) - { - var paramName = "@AlternateType" + typeIndex.ToString(CultureInfo.InvariantCulture); - typeClauses.Add("Type=" + paramName); - cmd.Parameters.Add(cmd, paramName, DbType.String).Value = type; - typeIndex++; - } - - whereClauses.Add("(IsMovie=@IsMovie OR " + string.Join(" OR ", typeClauses.ToArray()) + ")"); - } - else - { - whereClauses.Add("(IsMovie is null OR IsMovie=@IsMovie)"); - } + whereClauses.Add("(IsMovie is null OR IsMovie=@IsMovie)"); } cmd.Parameters.Add(cmd, "@IsMovie", DbType.Boolean).Value = query.IsMovie; } diff --git a/MediaBrowser.Server.Mono/MediaBrowser.Server.Mono.csproj b/MediaBrowser.Server.Mono/MediaBrowser.Server.Mono.csproj index b71877e17a..45071c9d9e 100644 --- a/MediaBrowser.Server.Mono/MediaBrowser.Server.Mono.csproj +++ b/MediaBrowser.Server.Mono/MediaBrowser.Server.Mono.csproj @@ -82,11 +82,14 @@ + + Native\SqliteExtensions.cs + Properties\SharedVersion.cs - + diff --git a/MediaBrowser.Server.Mono/Native/DbConnector.cs b/MediaBrowser.Server.Mono/Native/DbConnector.cs new file mode 100644 index 0000000000..536cd73223 --- /dev/null +++ b/MediaBrowser.Server.Mono/Native/DbConnector.cs @@ -0,0 +1,29 @@ +using System; +using System.Data; +using System.Data.SQLite; +using System.Threading.Tasks; +using MediaBrowser.Model.Logging; +using MediaBrowser.Server.Implementations.Persistence; + +namespace MediaBrowser.Server.Mono.Native +{ + public class DbConnector : IDbConnector + { + private readonly ILogger _logger; + + public DbConnector(ILogger logger) + { + _logger = logger; + } + + public void BindSimilarityScoreFunction(IDbConnection connection) + { + SqliteExtensions.BindGetSimilarityScore(connection, _logger); + } + + public Task Connect(string dbPath) + { + return SqliteExtensions.ConnectToDb(dbPath, _logger); + } + } +} \ No newline at end of file diff --git a/MediaBrowser.Server.Mono/Native/SqliteExtensions.cs b/MediaBrowser.Server.Mono/Native/SqliteExtensions.cs deleted file mode 100644 index ca2327282e..0000000000 --- a/MediaBrowser.Server.Mono/Native/SqliteExtensions.cs +++ /dev/null @@ -1,62 +0,0 @@ -using System; -using System.Data; -using System.Data.SQLite; -using System.Threading.Tasks; -using MediaBrowser.Model.Logging; -using MediaBrowser.Server.Implementations.Persistence; - -namespace MediaBrowser.Server.Mono.Native -{ - /// - /// Class SQLiteExtensions - /// - static class SqliteExtensions - { - /// - /// Connects to db. - /// - /// The db path. - /// The logger. - /// Task{IDbConnection}. - /// dbPath - public static async Task ConnectToDb(string dbPath, ILogger logger) - { - if (string.IsNullOrEmpty(dbPath)) - { - throw new ArgumentNullException("dbPath"); - } - - logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, dbPath); - - var connectionstr = new SQLiteConnectionStringBuilder - { - PageSize = 4096, - CacheSize = 2000, - SyncMode = SynchronizationModes.Normal, - DataSource = dbPath, - JournalMode = SQLiteJournalModeEnum.Wal - }; - - var connection = new SQLiteConnection(connectionstr.ConnectionString); - - await connection.OpenAsync().ConfigureAwait(false); - - return connection; - } - } - - public class DbConnector : IDbConnector - { - private readonly ILogger _logger; - - public DbConnector(ILogger logger) - { - _logger = logger; - } - - public Task Connect(string dbPath) - { - return SqliteExtensions.ConnectToDb(dbPath, _logger); - } - } -} \ No newline at end of file diff --git a/MediaBrowser.ServerApplication/MediaBrowser.ServerApplication.csproj b/MediaBrowser.ServerApplication/MediaBrowser.ServerApplication.csproj index 366d4b6083..35660b2b1f 100644 --- a/MediaBrowser.ServerApplication/MediaBrowser.ServerApplication.csproj +++ b/MediaBrowser.ServerApplication/MediaBrowser.ServerApplication.csproj @@ -97,6 +97,9 @@ + + Native\SqliteExtensions.cs + Properties\SharedVersion.cs @@ -114,7 +117,7 @@ - + diff --git a/MediaBrowser.ServerApplication/Native/DbConnector.cs b/MediaBrowser.ServerApplication/Native/DbConnector.cs new file mode 100644 index 0000000000..f93cad62cb --- /dev/null +++ b/MediaBrowser.ServerApplication/Native/DbConnector.cs @@ -0,0 +1,38 @@ +using System; +using System.Data; +using System.Data.SQLite; +using System.Threading.Tasks; +using MediaBrowser.Model.Logging; +using MediaBrowser.Server.Implementations.Persistence; + +namespace MediaBrowser.ServerApplication.Native +{ + public class DbConnector : IDbConnector + { + private readonly ILogger _logger; + + public DbConnector(ILogger logger) + { + _logger = logger; + } + + public void BindSimilarityScoreFunction(IDbConnection connection) + { + SqliteExtensions.BindGetSimilarityScore(connection, _logger); + } + + public async Task Connect(string dbPath) + { + try + { + return await SqliteExtensions.ConnectToDb(dbPath, _logger).ConfigureAwait(false); + } + catch (Exception ex) + { + _logger.ErrorException("Error opening database {0}", ex, dbPath); + + throw; + } + } + } +} \ No newline at end of file diff --git a/MediaBrowser.ServerApplication/Native/SqliteExtensions.cs b/MediaBrowser.ServerApplication/Native/SqliteExtensions.cs deleted file mode 100644 index bdf5c33237..0000000000 --- a/MediaBrowser.ServerApplication/Native/SqliteExtensions.cs +++ /dev/null @@ -1,71 +0,0 @@ -using System; -using System.Data; -using System.Data.SQLite; -using System.Threading.Tasks; -using MediaBrowser.Model.Logging; -using MediaBrowser.Server.Implementations.Persistence; - -namespace MediaBrowser.ServerApplication.Native -{ - /// - /// Class SQLiteExtensions - /// - static class SqliteExtensions - { - /// - /// Connects to db. - /// - /// The db path. - /// The logger. - /// Task{IDbConnection}. - /// dbPath - public static async Task ConnectToDb(string dbPath, ILogger logger) - { - if (string.IsNullOrEmpty(dbPath)) - { - throw new ArgumentNullException("dbPath"); - } - - logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, dbPath); - - var connectionstr = new SQLiteConnectionStringBuilder - { - PageSize = 4096, - CacheSize = 2000, - SyncMode = SynchronizationModes.Normal, - DataSource = dbPath, - JournalMode = SQLiteJournalModeEnum.Wal - }; - - var connection = new SQLiteConnection(connectionstr.ConnectionString); - - await connection.OpenAsync().ConfigureAwait(false); - - return connection; - } - } - - public class DbConnector : IDbConnector - { - private readonly ILogger _logger; - - public DbConnector(ILogger logger) - { - _logger = logger; - } - - public async Task Connect(string dbPath) - { - try - { - return await SqliteExtensions.ConnectToDb(dbPath, _logger).ConfigureAwait(false); - } - catch (Exception ex) - { - _logger.ErrorException("Error opening database {0}", ex, dbPath); - - throw; - } - } - } -} \ No newline at end of file -- cgit v1.2.3