add images table

3 files changed, 135 insertions, 47 deletions

diff --git a/MediaBrowser.Server.Implementations/FileOrganization/EpisodeFileOrganizer.cs b/MediaBrowser.Server.Implementations/FileOrganization/EpisodeFileOrganizer.cs
index 83801b3e76..2109f8d59e 100644
--- a/MediaBrowser.Server.Implementations/FileOrganization/EpisodeFileOrganizer.cs
+++ b/MediaBrowser.Server.Implementations/FileOrganization/EpisodeFileOrganizer.cs
@@ -562,9 +562,10 @@ namespace MediaBrowser.Server.Implementations.FileOrganization
                     series = _libraryManager.GetItemList(new Controller.Entities.InternalItemsQuery
                     {
                         IncludeItemTypes = new[] { typeof(Series).Name },
-                        Recursive = true
-                    }).Cast<Series>()
-                    .FirstOrDefault(i => string.Equals(i.Name, info.ItemName, StringComparison.OrdinalIgnoreCase));
+                        Recursive = true,
+                        Name = info.ItemName
+
+                    }).Cast<Series>().FirstOrDefault();
                 }
             }
 
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs
index b819fb9790..519731a5cc 100644
--- a/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs
+++ b/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs
@@ -66,7 +66,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
         }
     }
 
-    [SQLiteFunction(Name = "GetSimilarityScore", Arguments = 13, FuncType = FunctionType.Scalar)]
+    [SQLiteFunction(Name = "GetSimilarityScore", Arguments = 6, FuncType = FunctionType.Scalar)]
     public class SimiliarToFunction : SQLiteFunction
     {
         internal static ILogger Logger;
@@ -87,19 +87,17 @@ namespace MediaBrowser.Server.Implementations.Persistence
         {
             var score = 0;
 
-            var inputOfficialRating = args[0] as string;
-            var rowOfficialRating = args[1] as string;
-            if (!string.IsNullOrWhiteSpace(inputOfficialRating) && string.Equals(inputOfficialRating, rowOfficialRating))
+            // Official rating equals
+            if ((long)args[0] == 1)
             {
                 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)
+            // Year difference
+            long? yearDifference = args[1] == null ? (long?)null : (long)args[1];
+            if (yearDifference.HasValue)
             {
-                var diff = Math.Abs(inputYear.Value - rowYear.Value);
+                var diff = Math.Abs(yearDifference.Value);
 
                 // Add if they came out within the same decade
                 if (diff < 10)
@@ -115,25 +113,28 @@ namespace MediaBrowser.Server.Implementations.Persistence
             }
 
             // genres
-            score += GetListScore(args, 4, 5);
+            score += Convert.ToInt32((long)args[2]) * 10;
 
             // tags
-            score += GetListScore(args, 6, 7);
+            score += Convert.ToInt32((long)args[3]) * 10;
+
+            // # of common keywords
+            score += Convert.ToInt32((long)args[4]) *10;
 
-            // keywords
-            score += GetListScore(args, 8, 9);
+            // # of common studios
+            score += Convert.ToInt32((long)args[5]) * 3;
 
             // studios
-            score += GetListScore(args, 10, 11, 3);
+            //score += GetListScore(args, 7, 8, 3);
 
-            var rowPeopleNamesText = (args[12] as string) ?? string.Empty;
-            var rowPeopleNames = rowPeopleNamesText.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
+            //var rowPeopleNamesText = (args[12] as string) ?? string.Empty;
+            //var rowPeopleNames = rowPeopleNamesText.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
 
-            foreach (var name in rowPeopleNames)
-            {
-                // TODO: Send along person types
-                score += 3;
-            }
+            //foreach (var name in rowPeopleNames)
+            //{
+            //    // TODO: Send along person types
+            //    score += 3;
+            //}
 
             //Logger.Debug("Returning score {0}", score);
             return score;
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs
index bf50f9a0e4..9f827c553a 100644
--- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs
+++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs
@@ -88,10 +88,13 @@ namespace MediaBrowser.Server.Implementations.Persistence
         private IDbCommand _deleteProviderIdsCommand;
         private IDbCommand _saveProviderIdsCommand;
 
+        private IDbCommand _deleteImagesCommand;
+        private IDbCommand _saveImagesCommand;
+
         private IDbCommand _updateInheritedRatingCommand;
         private IDbCommand _updateInheritedTagsCommand;
 
-        public const int LatestSchemaVersion = 84;
+        public const int LatestSchemaVersion = 87;
 
         /// <summary>
         /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class.
@@ -144,10 +147,14 @@ namespace MediaBrowser.Server.Implementations.Persistence
 
                                 "create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT)",
                                 "create index if not exists idx_ItemValues on ItemValues(ItemId)",
+                                "create index if not exists idx_ItemValues2 on ItemValues(ItemId,Type)",
 
                                 "create table if not exists ProviderIds (ItemId GUID, Name TEXT, Value TEXT, PRIMARY KEY (ItemId, Name))",
                                 "create index if not exists Idx_ProviderIds on ProviderIds(ItemId)",
 
+                                "create table if not exists Images (ItemId GUID NOT NULL, Path TEXT NOT NULL, ImageType INT NOT NULL, DateModified DATETIME, IsPlaceHolder BIT NOT NULL, SortOrder INT)",
+                                "create index if not exists idx_Images on Images(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)",
@@ -564,6 +571,19 @@ namespace MediaBrowser.Server.Implementations.Persistence
             _saveProviderIdsCommand.Parameters.Add(_saveProviderIdsCommand, "@Name");
             _saveProviderIdsCommand.Parameters.Add(_saveProviderIdsCommand, "@Value");
 
+            // images
+            _deleteImagesCommand = _connection.CreateCommand();
+            _deleteImagesCommand.CommandText = "delete from Images where ItemId=@Id";
+            _deleteImagesCommand.Parameters.Add(_deleteImagesCommand, "@Id");
+
+            _saveImagesCommand = _connection.CreateCommand();
+            _saveImagesCommand.CommandText = "insert into Images (ItemId, ImageType, Path, DateModified, IsPlaceHolder, SortOrder) values (@ItemId, @ImageType, @Path, @DateModified, @IsPlaceHolder, @SortOrder)";
+            _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@ItemId");
+            _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@ImageType");
+            _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@Path");
+            _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@DateModified");
+            _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@IsPlaceHolder");
+            _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@SortOrder");
         }
 
         /// <summary>
@@ -878,6 +898,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
                     }
 
                     UpdateUserDataKeys(item.Id, item.GetUserDataKeys().Distinct(StringComparer.OrdinalIgnoreCase).ToList(), transaction);
+                    UpdateImages(item.Id, item.ImageInfos, transaction);
                     UpdateProviderIds(item.Id, item.ProviderIds, transaction);
                     UpdateItemValues(item.Id, GetItemValues(item), transaction);
                 }
@@ -1620,37 +1641,33 @@ namespace MediaBrowser.Server.Implementations.Persistence
                 var item = query.SimilarTo;
 
                 var builder = new StringBuilder();
-                builder.Append("GetSimilarityScore(");
+                builder.Append("(");
+
+                builder.Append("((OfficialRating=@ItemOfficialRating) * 10)");
+                //builder.Append("+ ((ProductionYear=@ItemProductionYear) * 10)");
 
-                builder.Append("@ItemOfficialRating,");
-                builder.Append("OfficialRating,");
+                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 )");
 
-                builder.Append("@ItemProductionYear,");
-                builder.Append("ProductionYear,");
+                //// genres
+                builder.Append("+ ((Select count(value) from ItemValues where ItemId=Guid and Type=2 and value in (select value from itemvalues where ItemId=@SimilarItemId and type=2)) * 10)");
 
-                builder.Append("@ItemGenres,");
-                builder.Append("Genres,");
+                //// tags
+                builder.Append("+ ((Select count(value) from ItemValues where ItemId=Guid and Type=4 and value in (select value from itemvalues where ItemId=@SimilarItemId and type=4)) * 10)");
 
-                builder.Append("@ItemTags,");
-                builder.Append("Tags,");
+                builder.Append("+ ((Select count(value) from ItemValues where ItemId=Guid and Type=5 and value in (select value from itemvalues where ItemId=@SimilarItemId and type=5)) * 10)");
 
-                builder.Append("@ItemKeywords,");
-                builder.Append("(select group_concat((Select Value from ItemValues where ItemId=Guid and Type=5), '|')),");
+                builder.Append("+ ((Select count(value) from ItemValues where ItemId=Guid and Type=3 and value in (select value from itemvalues where ItemId=@SimilarItemId and type=3)) * 3)");
 
-                builder.Append("@ItemStudios,");
-                builder.Append("Studios,");
+                //builder.Append("+ ((Select count(Name) from People where ItemId=Guid and Name in (select Name from People where ItemId=@SimilarItemId)) * 3)");
 
-                builder.Append("(select group_concat((Select Name from People where ItemId=Guid and Name in (Select Name from People where ItemId=@SimilarItemId)), '|'))");
+                ////builder.Append("(select group_concat((Select Name from People where ItemId=Guid and Name in (Select Name from People where ItemId=@SimilarItemId)), '|'))");
 
                 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());
+                cmd.Parameters.Add(cmd, "@ItemProductionYear", DbType.Int32).Value = item.ProductionYear ?? 0;
                 cmd.Parameters.Add(cmd, "@SimilarItemId", DbType.Guid).Value = item.Id;
 
                 var excludeIds = query.ExcludeItemIds.ToList();
@@ -1865,7 +1882,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
                 {
                     if (query.User != null)
                     {
-                        query.SortBy = new[] { "SimilarityScore", "IsUnplayed", "Random" };
+                        query.SortBy = new[] { "SimilarityScore", "IsPlayed", "Random" };
                     }
                     else
                     {
@@ -2478,6 +2495,19 @@ namespace MediaBrowser.Server.Implementations.Persistence
                 cmd.Parameters.Add(cmd, "@NameLessThan", DbType.String).Value = query.NameLessThan.ToLower();
             }
 
+            if (query.ImageTypes.Length > 0 && _config.Configuration.SchemaVersion >= 87)
+            {
+                var requiredImageIndex = 0;
+
+                foreach (var requiredImage in query.ImageTypes)
+                {
+                    var paramName = "@RequiredImageType" + requiredImageIndex;
+                    whereClauses.Add("(select path from images where ItemId=Guid and ImageType=" + paramName + " limit 1) not null");
+                    cmd.Parameters.Add(cmd, paramName, DbType.Int32).Value = (int)requiredImage;
+                    requiredImageIndex++;
+                }
+            }
+
             if (query.IsLiked.HasValue)
             {
                 if (query.IsLiked.Value)
@@ -2741,8 +2771,13 @@ namespace MediaBrowser.Server.Implementations.Persistence
                 var index = 0;
                 foreach (var pair in query.ExcludeProviderIds)
                 {
+                    if (string.Equals(pair.Key, MetadataProviders.TmdbCollection.ToString(), StringComparison.OrdinalIgnoreCase))
+                    {
+                        continue;
+                    }
+
                     var paramName = "@ExcludeProviderId" + index;
-                    excludeIds.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = 'Imdb'), '') <> " + paramName + ")");
+                    excludeIds.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = '" + pair.Key + "'), '') <> " + paramName + ")");
                     cmd.Parameters.Add(cmd, paramName, DbType.String).Value = pair.Value;
                     index++;
                 }
@@ -3183,6 +3218,11 @@ namespace MediaBrowser.Server.Implementations.Persistence
                 _deleteProviderIdsCommand.Transaction = transaction;
                 _deleteProviderIdsCommand.ExecuteNonQuery();
 
+                // Delete images
+                _deleteImagesCommand.GetParameter(0).Value = id;
+                _deleteImagesCommand.Transaction = transaction;
+                _deleteImagesCommand.ExecuteNonQuery();
+
                 // Delete the item
                 _deleteItemCommand.GetParameter(0).Value = id;
                 _deleteItemCommand.Transaction = transaction;
@@ -3399,6 +3439,52 @@ namespace MediaBrowser.Server.Implementations.Persistence
             return list;
         }
 
+        private void UpdateImages(Guid itemId, List<ItemImageInfo> images, IDbTransaction transaction)
+        {
+            if (itemId == Guid.Empty)
+            {
+                throw new ArgumentNullException("itemId");
+            }
+
+            if (images == null)
+            {
+                throw new ArgumentNullException("images");
+            }
+
+            CheckDisposed();
+
+            // First delete 
+            _deleteImagesCommand.GetParameter(0).Value = itemId;
+            _deleteImagesCommand.Transaction = transaction;
+
+            _deleteImagesCommand.ExecuteNonQuery();
+
+            var index = 0;
+            foreach (var image in images)
+            {
+                _saveImagesCommand.GetParameter(0).Value = itemId;
+                _saveImagesCommand.GetParameter(1).Value = image.Type;
+                _saveImagesCommand.GetParameter(2).Value = image.Path;
+
+                if (image.DateModified == default(DateTime))
+                {
+                    _saveImagesCommand.GetParameter(3).Value = null;
+                }
+                else
+                {
+                    _saveImagesCommand.GetParameter(3).Value = image.DateModified;
+                }
+
+                _saveImagesCommand.GetParameter(4).Value = image.IsPlaceholder;
+                _saveImagesCommand.GetParameter(5).Value = index;
+
+                _saveImagesCommand.Transaction = transaction;
+
+                _saveImagesCommand.ExecuteNonQuery();
+                index++;
+            }
+        }
+
         private void UpdateProviderIds(Guid itemId, Dictionary<string, string> values, IDbTransaction transaction)
         {
             if (itemId == Guid.Empty)
@@ -3408,7 +3494,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
 
             if (values == null)
             {
-                throw new ArgumentNullException("keys");
+                throw new ArgumentNullException("values");
             }
 
             CheckDisposed();