update artist queries

4 files changed, 121 insertions, 12 deletions

diff --git a/MediaBrowser.Server.Implementations/Library/LibraryManager.cs b/MediaBrowser.Server.Implementations/Library/LibraryManager.cs
index 1407cdce3..f61bac42d 100644
--- a/MediaBrowser.Server.Implementations/Library/LibraryManager.cs
+++ b/MediaBrowser.Server.Implementations/Library/LibraryManager.cs
@@ -1448,8 +1448,12 @@ namespace MediaBrowser.Server.Implementations.Library
                 // Handle grouping
                 if (user != null && !string.IsNullOrWhiteSpace(view.ViewType) && UserView.IsEligibleForGrouping(view.ViewType))
                 {
-                    var collectionFolders = user.RootFolder.GetChildren(user, true).OfType<CollectionFolder>().Where(i => string.IsNullOrWhiteSpace(i.CollectionType) || string.Equals(i.CollectionType, view.ViewType, StringComparison.OrdinalIgnoreCase));
-                    return collectionFolders.SelectMany(i => GetTopParentsForQuery(i, user));
+                    return user.RootFolder
+                        .GetChildren(user, true)
+                        .OfType<CollectionFolder>()
+                        .Where(i => string.IsNullOrWhiteSpace(i.CollectionType) || string.Equals(i.CollectionType, view.ViewType, StringComparison.OrdinalIgnoreCase))
+                        .Where(i => user.Configuration.GroupedFolders.Contains(i.Id.ToString("N"), StringComparer.OrdinalIgnoreCase))
+                        .SelectMany(i => GetTopParentsForQuery(i, user));
                 }
                 return new BaseItem[] { };
             }
diff --git a/MediaBrowser.Server.Implementations/Library/MusicManager.cs b/MediaBrowser.Server.Implementations/Library/MusicManager.cs
index c82c4cdf7..ef13ba996 100644
--- a/MediaBrowser.Server.Implementations/Library/MusicManager.cs
+++ b/MediaBrowser.Server.Implementations/Library/MusicManager.cs
@@ -30,7 +30,10 @@ namespace MediaBrowser.Server.Implementations.Library
         public IEnumerable<Audio> GetInstantMixFromArtist(MusicArtist artist, User user)
         {
             var genres = user.RootFolder
-                .GetRecursiveChildren(user, i => i is Audio)
+                .GetRecursiveChildren(user, new InternalItemsQuery(user)
+                {
+                    IncludeItemTypes = new[] { typeof(Audio).Name }
+                })
                 .Cast<Audio>()
                 .Where(i => i.HasAnyArtist(artist.Name))
                 .SelectMany(i => i.Genres)
@@ -43,7 +46,10 @@ namespace MediaBrowser.Server.Implementations.Library
         public IEnumerable<Audio> GetInstantMixFromAlbum(MusicAlbum item, User user)
         {
             var genres = item
-                .GetRecursiveChildren(user, i => i is Audio)
+                .GetRecursiveChildren(user, new InternalItemsQuery(user)
+                {
+                    IncludeItemTypes = new[] { typeof(Audio).Name }
+                })
                .Cast<Audio>()
                .SelectMany(i => i.Genres)
                .Concat(item.Genres)
@@ -55,7 +61,10 @@ namespace MediaBrowser.Server.Implementations.Library
         public IEnumerable<Audio> GetInstantMixFromFolder(Folder item, User user)
         {
             var genres = item
-               .GetRecursiveChildren(user, i => i is Audio)
+               .GetRecursiveChildren(user, new InternalItemsQuery(user)
+               {
+                   IncludeItemTypes = new[] {typeof(Audio).Name}
+               })
                .Cast<Audio>()
                .SelectMany(i => i.Genres)
                .Concat(item.Genres)
@@ -67,7 +76,10 @@ namespace MediaBrowser.Server.Implementations.Library
         public IEnumerable<Audio> GetInstantMixFromPlaylist(Playlist item, User user)
         {
             var genres = item
-               .GetRecursiveChildren(user, i => i is Audio)
+               .GetRecursiveChildren(user, new InternalItemsQuery(user)
+               {
+                   IncludeItemTypes = new[] { typeof(Audio).Name }
+               })
                .Cast<Audio>()
                .SelectMany(i => i.Genres)
                .Concat(item.Genres)
diff --git a/MediaBrowser.Server.Implementations/LiveTv/LiveTvManager.cs b/MediaBrowser.Server.Implementations/LiveTv/LiveTvManager.cs
index dc347c1dc..23bf50072 100644
--- a/MediaBrowser.Server.Implementations/LiveTv/LiveTvManager.cs
+++ b/MediaBrowser.Server.Implementations/LiveTv/LiveTvManager.cs
@@ -1410,7 +1410,7 @@ namespace MediaBrowser.Server.Implementations.LiveTv
                 .Where(i => i.IsVisibleStandalone(user))
                 .ToList();
 
-            var items = _libraryManager.GetItemsResult(new InternalItemsQuery(user)
+            return _libraryManager.GetItemsResult(new InternalItemsQuery(user)
             {
                 MediaTypes = new[] { MediaType.Video },
                 Recursive = true,
@@ -1418,8 +1418,6 @@ namespace MediaBrowser.Server.Implementations.LiveTv
                 ExcludeLocationTypes = new[] { LocationType.Virtual },
                 Limit = Math.Min(10, query.Limit ?? int.MaxValue)
             });
-
-            return items;
         }
 
         public async Task<QueryResult<BaseItem>> GetInternalRecordings(RecordingQuery query, CancellationToken cancellationToken)
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs
index 78e0bce17..7ab8b9151 100644
--- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs
+++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs
@@ -81,10 +81,13 @@ namespace MediaBrowser.Server.Implementations.Persistence
         private IDbCommand _deleteUserDataKeysCommand;
         private IDbCommand _saveUserDataKeysCommand;
 
+        private IDbCommand _deleteItemValuesCommand;
+        private IDbCommand _saveItemValuesCommand;
+
         private IDbCommand _updateInheritedRatingCommand;
         private IDbCommand _updateInheritedTagsCommand;
 
-        public const int LatestSchemaVersion = 78;
+        public const int LatestSchemaVersion = 79;
 
         /// <summary>
         /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class.
@@ -136,6 +139,9 @@ namespace MediaBrowser.Server.Implementations.Persistence
                                 "create table if not exists UserDataKeys (ItemId GUID, UserDataKey TEXT, PRIMARY KEY (ItemId, UserDataKey))",
                                 "create index if not exists idx_UserDataKeys1 on UserDataKeys(ItemId)",
 
+                                "create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT)",
+                                "create index if not exists idx_ItemValues on ItemValues(ItemId)",
+
                                 "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)",
                                 "create index if not exists idxPeopleItemId on People(ItemId)",
                                 "create index if not exists idxPeopleName on People(Name)",
@@ -565,6 +571,17 @@ namespace MediaBrowser.Server.Implementations.Persistence
             _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@UserDataKey");
             _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@Priority");
 
+            // item values
+            _deleteItemValuesCommand = _connection.CreateCommand();
+            _deleteItemValuesCommand.CommandText = "delete from ItemValues where ItemId=@Id";
+            _deleteItemValuesCommand.Parameters.Add(_deleteItemValuesCommand, "@Id");
+
+            _saveItemValuesCommand = _connection.CreateCommand();
+            _saveItemValuesCommand.CommandText = "insert into ItemValues (ItemId, Type, Value) values (@ItemId, @Type, @Value)";
+            _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@ItemId");
+            _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Type");
+            _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Value");
+
         }
 
         /// <summary>
@@ -851,6 +868,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
                     }
 
                     UpdateUserDataKeys(item.Id, item.GetUserDataKeys().Distinct(StringComparer.OrdinalIgnoreCase).ToList(), transaction);
+                    UpdateItemValues(item.Id, GetItemValues(item), transaction);
                 }
 
                 transaction.Commit();
@@ -1661,7 +1679,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
         {
             var elapsed = (DateTime.UtcNow - startDate).TotalMilliseconds;
 
-            if (elapsed >= 500)
+            if (elapsed >= 400)
             {
                 Logger.Debug("{2} query time (slow): {0}ms. Query: {1}",
                     Convert.ToInt32(elapsed),
@@ -1795,7 +1813,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
             }).ToArray());
         }
 
-        private Tuple<string,bool> MapOrderByField(string name)
+        private Tuple<string, bool> MapOrderByField(string name)
         {
             if (string.Equals(name, ItemSortBy.AirTime, StringComparison.OrdinalIgnoreCase))
             {
@@ -1838,6 +1856,14 @@ namespace MediaBrowser.Server.Implementations.Persistence
             {
                 return new Tuple<string, bool>("DateLastMediaAdded", false);
             }
+            if (string.Equals(name, ItemSortBy.Artist, StringComparison.OrdinalIgnoreCase))
+            {
+                return new Tuple<string, bool>("(select value 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 value from itemvalues where ItemId=Guid and Type=1 LIMIT 1)", false);
+            }
 
             return new Tuple<string, bool>(name, false);
         }
@@ -2433,6 +2459,20 @@ namespace MediaBrowser.Server.Implementations.Persistence
                 }
             }
 
+            if (query.ArtistNames.Length > 0)
+            {
+                var clauses = new List<string>();
+                var index = 0;
+                foreach (var artist in query.ArtistNames)
+                {
+                    clauses.Add("@ArtistName" + index + " in (select value from itemvalues where ItemId=Guid and Type <= 1)");
+                    cmd.Parameters.Add(cmd, "@ArtistName" + index, DbType.String).Value = artist;
+                    index++;
+                }
+                var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
+                whereClauses.Add(clause);
+            }
+
             if (query.Genres.Length > 0)
             {
                 var clauses = new List<string>();
@@ -2970,6 +3010,11 @@ namespace MediaBrowser.Server.Implementations.Persistence
                 _deleteUserDataKeysCommand.Transaction = transaction;
                 _deleteUserDataKeysCommand.ExecuteNonQuery();
 
+                // Delete item values
+                _deleteItemValuesCommand.GetParameter(0).Value = id;
+                _deleteItemValuesCommand.Transaction = transaction;
+                _deleteItemValuesCommand.ExecuteNonQuery();
+
                 // Delete the item
                 _deleteItemCommand.GetParameter(0).Value = id;
                 _deleteItemCommand.Transaction = transaction;
@@ -3162,6 +3207,56 @@ namespace MediaBrowser.Server.Implementations.Persistence
             }
         }
 
+        private List<Tuple<int, string>> GetItemValues(BaseItem item)
+        {
+            var list = new List<Tuple<int, string>>();
+
+            var hasArtist = item as IHasArtist;
+            if (hasArtist != null)
+            {
+                list.AddRange(hasArtist.Artists.Select(i => new Tuple<int, string>(0, i)));
+            }
+
+            var hasAlbumArtist = item as IHasAlbumArtist;
+            if (hasAlbumArtist != null)
+            {
+                list.AddRange(hasAlbumArtist.AlbumArtists.Select(i => new Tuple<int, string>(1, i)));
+            }
+
+            return list;
+        }
+
+        private void UpdateItemValues(Guid itemId, List<Tuple<int, string>> values, IDbTransaction transaction)
+        {
+            if (itemId == Guid.Empty)
+            {
+                throw new ArgumentNullException("itemId");
+            }
+
+            if (values == null)
+            {
+                throw new ArgumentNullException("keys");
+            }
+
+            CheckDisposed();
+
+            // First delete 
+            _deleteItemValuesCommand.GetParameter(0).Value = itemId;
+            _deleteItemValuesCommand.Transaction = transaction;
+
+            _deleteItemValuesCommand.ExecuteNonQuery();
+
+            foreach (var pair in values)
+            {
+                _saveItemValuesCommand.GetParameter(0).Value = itemId;
+                _saveItemValuesCommand.GetParameter(1).Value = pair.Item1;
+                _saveItemValuesCommand.GetParameter(2).Value = pair.Item2;
+                _saveItemValuesCommand.Transaction = transaction;
+
+                _saveItemValuesCommand.ExecuteNonQuery();
+            }
+        }
+
         private void UpdateUserDataKeys(Guid itemId, List<string> keys, IDbTransaction transaction)
         {
             if (itemId == Guid.Empty)