update storage of genres, studios, tags, & keywords

20 files changed, 58 insertions, 106 deletions

diff --git a/MediaBrowser.Api/ItemUpdateService.cs b/MediaBrowser.Api/ItemUpdateService.cs
index 5bb4ed5f0..36e8a504c 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 277bba1dd..76bc16a96 100644
--- a/MediaBrowser.Api/SimilarItemsHelper.cs
+++ b/MediaBrowser.Api/SimilarItemsHelper.cs
@@ -127,13 +127,7 @@ namespace MediaBrowser.Api
 
         private static IEnumerable<string> GetKeywords(BaseItem item)
         {
-            var hasTags = item as IHasKeywords;
-            if (hasTags != null)
-            {
-                return hasTags.Keywords;
-            }
-
-            return new List<string>();
+            return item.Keywords;
         }
 
         /// <summary>
diff --git a/MediaBrowser.Controller/Entities/BaseItem.cs b/MediaBrowser.Controller/Entities/BaseItem.cs
index 2a00ce992..78f1828ea 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<string>();
             Tags = new List<string>();
             Genres = new List<string>();
             Studios = new List<string>();
@@ -811,6 +812,8 @@ namespace MediaBrowser.Controller.Entities
         [IgnoreDataMember]
         public List<string> Tags { get; set; }
 
+        public List<string> Keywords { get; set; }
+
         /// <summary>
         /// Gets or sets the home page URL.
         /// </summary>
diff --git a/MediaBrowser.Controller/Entities/InternalItemsQuery.cs b/MediaBrowser.Controller/Entities/InternalItemsQuery.cs
index 823f4066c..7e38d7ed9 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/IHasKeywords.cs b/MediaBrowser.Controller/Entities/KeywordExtensions.cs
index ab9eb4aee..5c9afdf3d 100644
--- a/MediaBrowser.Controller/Entities/IHasKeywords.cs
+++ b/MediaBrowser.Controller/Entities/KeywordExtensions.cs
@@ -1,21 +1,11 @@
 using System;
-using System.Collections.Generic;
 using System.Linq;
 
 namespace MediaBrowser.Controller.Entities
 {
-    public interface IHasKeywords
-    {
-        /// <summary>
-        /// Gets or sets the keywords.
-        /// </summary>
-        /// <value>The keywords.</value>
-        List<string> Keywords { get; set; }
-    }
-
     public static class KeywordExtensions
     {
-        public static void AddKeyword(this IHasKeywords item, string name)
+        public static void AddKeyword(this BaseItem item, string name)
         {
             if (string.IsNullOrWhiteSpace(name))
             {
diff --git a/MediaBrowser.Controller/Entities/Movies/BoxSet.cs b/MediaBrowser.Controller/Entities/Movies/BoxSet.cs
index 09a9d97bc..4effc162e 100644
--- a/MediaBrowser.Controller/Entities/Movies/BoxSet.cs
+++ b/MediaBrowser.Controller/Entities/Movies/BoxSet.cs
@@ -15,7 +15,7 @@ namespace MediaBrowser.Controller.Entities.Movies
     /// <summary>
     /// Class BoxSet
     /// </summary>
-    public class BoxSet : Folder, IHasTrailers, IHasKeywords, IHasDisplayOrder, IHasLookupInfo<BoxSetInfo>, IHasShares
+    public class BoxSet : Folder, IHasTrailers, IHasDisplayOrder, IHasLookupInfo<BoxSetInfo>, IHasShares
     {
         public List<Share> Shares { get; set; }
 
@@ -26,7 +26,6 @@ namespace MediaBrowser.Controller.Entities.Movies
             RemoteTrailerIds = new List<Guid>();
 
             DisplayOrder = ItemSortBy.PremiereDate;
-            Keywords = new List<string>();
             Shares = new List<Share>();
         }
 
@@ -48,12 +47,6 @@ namespace MediaBrowser.Controller.Entities.Movies
         public List<MediaUrl> RemoteTrailers { get; set; }
 
         /// <summary>
-        /// Gets or sets the tags.
-        /// </summary>
-        /// <value>The tags.</value>
-        public List<string> Keywords { get; set; }
-
-        /// <summary>
         /// Gets or sets the display order.
         /// </summary>
         /// <value>The display order.</value>
diff --git a/MediaBrowser.Controller/Entities/Movies/Movie.cs b/MediaBrowser.Controller/Entities/Movies/Movie.cs
index 605221dcd..c7a833c58 100644
--- a/MediaBrowser.Controller/Entities/Movies/Movie.cs
+++ b/MediaBrowser.Controller/Entities/Movies/Movie.cs
@@ -15,7 +15,7 @@ namespace MediaBrowser.Controller.Entities.Movies
     /// <summary>
     /// Class Movie
     /// </summary>
-    public class Movie : Video, IHasCriticRating, IHasSpecialFeatures, IHasProductionLocations, IHasBudget, IHasKeywords, IHasTrailers, IHasThemeMedia, IHasTaglines, IHasAwards, IHasMetascore, IHasLookupInfo<MovieInfo>, ISupportsBoxSetGrouping, IHasOriginalTitle
+    public class Movie : Video, IHasCriticRating, IHasSpecialFeatures, IHasProductionLocations, IHasBudget, IHasTrailers, IHasThemeMedia, IHasTaglines, IHasAwards, IHasMetascore, IHasLookupInfo<MovieInfo>, ISupportsBoxSetGrouping, IHasOriginalTitle
     {
         public List<Guid> SpecialFeatureIds { get; set; }
 
@@ -32,7 +32,6 @@ namespace MediaBrowser.Controller.Entities.Movies
             ThemeSongIds = new List<Guid>();
             ThemeVideoIds = new List<Guid>();
             Taglines = new List<string>();
-            Keywords = new List<string>();
             ProductionLocations = new List<string>();
         }
 
@@ -42,7 +41,6 @@ namespace MediaBrowser.Controller.Entities.Movies
 
         public List<Guid> LocalTrailerIds { get; set; }
         public List<Guid> RemoteTrailerIds { get; set; }
-        public List<string> Keywords { get; set; }
 
         public List<MediaUrl> RemoteTrailers { get; set; }
 
diff --git a/MediaBrowser.Controller/Entities/Trailer.cs b/MediaBrowser.Controller/Entities/Trailer.cs
index 74645bbe9..eab5ab679 100644
--- a/MediaBrowser.Controller/Entities/Trailer.cs
+++ b/MediaBrowser.Controller/Entities/Trailer.cs
@@ -12,7 +12,7 @@ namespace MediaBrowser.Controller.Entities
     /// <summary>
     /// Class Trailer
     /// </summary>
-    public class Trailer : Video, IHasCriticRating, IHasProductionLocations, IHasBudget, IHasKeywords, IHasTaglines, IHasMetascore, IHasOriginalTitle, IHasLookupInfo<TrailerInfo>
+    public class Trailer : Video, IHasCriticRating, IHasProductionLocations, IHasBudget, IHasTaglines, IHasMetascore, IHasOriginalTitle, IHasLookupInfo<TrailerInfo>
     {
         public List<string> ProductionLocations { get; set; }
 
@@ -31,8 +31,6 @@ namespace MediaBrowser.Controller.Entities
 
         public List<MediaUrl> RemoteTrailers { get; set; }
 
-        public List<string> Keywords { get; set; }
-
         [IgnoreDataMember]
         public bool IsLocalTrailer
         {
diff --git a/MediaBrowser.Controller/MediaBrowser.Controller.csproj b/MediaBrowser.Controller/MediaBrowser.Controller.csproj
index 4d9999b37..b15bb94c7 100644
--- a/MediaBrowser.Controller/MediaBrowser.Controller.csproj
+++ b/MediaBrowser.Controller/MediaBrowser.Controller.csproj
@@ -142,7 +142,7 @@
     <Compile Include="Entities\IHasDisplayOrder.cs" />
     <Compile Include="Entities\IHasId.cs" />
     <Compile Include="Entities\IHasImages.cs" />
-    <Compile Include="Entities\IHasKeywords.cs" />
+    <Compile Include="Entities\KeywordExtensions.cs" />
     <Compile Include="Entities\IHasMediaSources.cs" />
     <Compile Include="Entities\IHasMetascore.cs" />
     <Compile Include="Entities\IHasOriginalTitle.cs" />
diff --git a/MediaBrowser.Controller/Providers/BaseItemXmlParser.cs b/MediaBrowser.Controller/Providers/BaseItemXmlParser.cs
index 1014fc2ee..aaa440060 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 be81d21d2..ca19b403a 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("<PlotKeywords>");
-
-                    foreach (var tag in hasKeywords.Keywords)
-                    {
-                        builder.Append("<PlotKeyword>" + SecurityElement.Escape(tag) + "</PlotKeyword>");
-                    }
+                builder.Append("<PlotKeywords>");
 
-                    builder.Append("</PlotKeywords>");
+                foreach (var tag in item.Keywords)
+                {
+                    builder.Append("<PlotKeyword>" + SecurityElement.Escape(tag) + "</PlotKeyword>");
                 }
+
+                builder.Append("</PlotKeywords>");
             }
 
             var people = libraryManager.GetPeople(item);
diff --git a/MediaBrowser.Providers/Manager/ProviderUtils.cs b/MediaBrowser.Providers/Manager/ProviderUtils.cs
index 59a2da460..a6f02f3f7 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 d13716cba..3b3065893 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 eb868d363..bfcdb2a26 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<string>();
-                }
+                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 49012c65a..df128a90b 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<string> GetKeywords(BaseItem item)
         {
-            var hasTags = item as IHasKeywords;
-            if (hasTags != null)
-            {
-                return hasTags.Keywords;
-            }
-
-            return new List<string>();
+            return item.Keywords;
         }
 
         public IEnumerable<string> GetAllIntroFiles()
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs
index 1de0ea710..a85bf1c81 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;
 
         /// <summary>
         /// Initializes a new instance of the <see cref="SqliteItemRepository"/> 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<string>();
+                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<int, string>(1, i)));
             }
 
+            list.AddRange(item.Genres.Select(i => new Tuple<int, string>(2, i)));
+            list.AddRange(item.Studios.Select(i => new Tuple<int, string>(3, i)));
+            list.AddRange(item.Tags.Select(i => new Tuple<int, string>(4, i)));
+            list.AddRange(item.Keywords.Select(i => new Tuple<int, string>(5, i)));
+
             return list;
         }
 
diff --git a/MediaBrowser.Server.Mono/Native/SqliteExtensions.cs b/MediaBrowser.Server.Mono/Native/SqliteExtensions.cs
index 385a7d0c5..ca2327282 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 4e6c82495..bdf5c3323 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 d020a73fe..ad1c6802d 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 d2e09d4eb..5bb9577ff 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;