From 192cbe34aa23fe3aa4f75500cc34c6cec2a30965 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Mon, 20 Jun 2016 21:59:26 -0400 Subject: fix user data queries --- .../Persistence/SqliteItemRepository.cs | 15 ++++++++------- .../Persistence/SqliteUserDataRepository.cs | 1 + 2 files changed, 9 insertions(+), 7 deletions(-) (limited to 'MediaBrowser.Server.Implementations') diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index e98bb49d6..152781aff 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -156,8 +156,6 @@ namespace MediaBrowser.Server.Implementations.Persistence "create index if not exists idx_AncestorIds2 on AncestorIds(AncestorIdText)", "create table if not exists UserDataKeys (ItemId GUID, UserDataKey TEXT Priority INT, PRIMARY KEY (ItemId, UserDataKey))", - //"create index if not exists idx_UserDataKeys1 on UserDataKeys(ItemId)", - "create index if not exists idx_UserDataKeys2 on UserDataKeys(ItemId,Priority)", "create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT, CleanValue TEXT)", //"create index if not exists idx_ItemValues on ItemValues(ItemId)", @@ -294,7 +292,10 @@ namespace MediaBrowser.Server.Implementations.Persistence "create index if not exists idx_TypeTopParentId7 on TypedBaseItems(TopParentId,MediaType,IsVirtualItem,PresentationUniqueKey)", // items by name - "create index if not exists idx_ItemValues3 on ItemValues(ItemId,Type,CleanValue)" + "create index if not exists idx_ItemValues3 on ItemValues(ItemId,Type,CleanValue)", + + //"create index if not exists idx_UserDataKeys1 on UserDataKeys(ItemId)", + "create index if not exists idx_UserDataKeys2 on UserDataKeys(ItemId,Priority)" }; _connection.RunQueries(postQueries, Logger); @@ -1712,7 +1713,7 @@ namespace MediaBrowser.Server.Implementations.Persistence return string.Empty; } - return " left join UserDataDb.UserData on (select UserDataKey from UserDataKeys where ItemId=Guid order by Priority LIMIT 1)=UserDataDb.UserData.Key"; + return " left join UserDataDb.UserData on (select UserDataKey from UserDataKeys where ItemId=Guid order by Priority LIMIT 1)=UserDataDb.UserData.Key And (UserId=@UserId)"; } private string GetGroupBy(InternalItemsQuery query) @@ -1961,11 +1962,11 @@ namespace MediaBrowser.Server.Implementations.Persistence { if (query.User != null) { - query.SortBy = new[] { "SimilarityScore", "IsPlayed", "Random" }; + query.SortBy = new[] { "SimilarityScore", ItemSortBy.IsPlayed, ItemSortBy.Random }; } else { - query.SortBy = new[] { "SimilarityScore", "Random" }; + query.SortBy = new[] { "SimilarityScore", ItemSortBy.Random }; } query.SortOrder = SortOrder.Descending; } @@ -2296,7 +2297,7 @@ namespace MediaBrowser.Server.Implementations.Persistence if (EnableJoinUserData(query)) { - whereClauses.Add("(UserId is null or UserId=@UserId)"); + //whereClauses.Add("(UserId is null or UserId=@UserId)"); } if (query.IsCurrentSchema.HasValue) { diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs index f40006b44..ced43a3e3 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs @@ -66,6 +66,7 @@ namespace MediaBrowser.Server.Implementations.Persistence "create index if not exists idx_userdata on userdata(key)", "create unique index if not exists userdataindex on userdata (key, userId)", + "create index if not exists userdataindex1 on userdata (key, userId)", //pragmas "pragma temp_store = memory", -- cgit v1.2.3