Merge remote-tracking branch 'upstream/dev' into dev

4 files changed, 165 insertions, 144 deletions

diff --git a/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs b/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs
index 985d79a0a..cac9fe983 100644
--- a/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs
+++ b/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs
@@ -6,6 +6,5 @@ namespace MediaBrowser.Server.Implementations.Persistence
     public interface IDbConnector
     {
         Task<IDbConnection> Connect(string dbPath);
-        void BindSimilarityScoreFunction(IDbConnection connection);
     }
 }
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs
index 5e07bac31..dd2f15cfd 100644
--- a/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs
+++ b/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs
@@ -5,6 +5,8 @@ using System.Data.SQLite;
 using System.Linq;
 using System.Text;
 using System.Threading.Tasks;
+using MediaBrowser.Controller.Entities;
+using MediaBrowser.Model.Entities;
 using MediaBrowser.Model.Logging;
 
 namespace MediaBrowser.Server.Implementations.Persistence
@@ -46,13 +48,6 @@ namespace MediaBrowser.Server.Implementations.Persistence
             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<SQLiteFunctionAttribute>().ToArray();
@@ -63,113 +58,4 @@ namespace MediaBrowser.Server.Implementations.Persistence
             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 13f7a0d37..460a67ca7 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 = 83;
+        public const int LatestSchemaVersion = 89;
 
         /// <summary>
         /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class.
@@ -132,9 +135,9 @@ namespace MediaBrowser.Server.Implementations.Persistence
             string[] queries = {
 
                                 "create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB, ParentId GUID, Path TEXT)",
-                                "create index if not exists idx_TypedBaseItems on TypedBaseItems(guid)",
                                 "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)",
                                 "create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)",
+                                "create index if not exists idx_TypedBaseItems2 on TypedBaseItems(Type,Guid)",
 
                                 "create table if not exists AncestorIds (ItemId GUID, AncestorId GUID, AncestorIdText TEXT, PRIMARY KEY (ItemId, AncestorId))",
                                 "create index if not exists idx_AncestorIds1 on AncestorIds(AncestorId)",
@@ -145,10 +148,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)",
+                                "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)",
@@ -265,8 +272,6 @@ 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 =
@@ -565,6 +570,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>
@@ -879,6 +897,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);
                 }
@@ -956,7 +975,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
 
             if (type == null)
             {
-                Logger.Debug("Unknown type {0}", typeString);
+                //Logger.Debug("Unknown type {0}", typeString);
 
                 return null;
             }
@@ -1621,34 +1640,34 @@ 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("+(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 )");
+
+                //// 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("@ItemOfficialRating,");
-                builder.Append("OfficialRating,");
+                //// 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("@ItemProductionYear,");
-                builder.Append("ProductionYear,");
+                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("@ItemGenres,");
-                builder.Append("Genres,");
+                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("@ItemTags,");
-                builder.Append("Tags,");
+                //builder.Append("+ ((Select count(Name) from People where ItemId=Guid and Name in (select Name from People where ItemId=@SimilarItemId)) * 3)");
 
-                builder.Append("@ItemKeywords,");
-                builder.Append("(select group_concat((Select Value from ItemValues where ItemId=Guid and Type=5), '|')),");
+                ////builder.Append("(select group_concat((Select Name from People where ItemId=Guid and Name in (Select Name from People where ItemId=@SimilarItemId)), '|'))");
 
-                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());
+                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();
                 excludeIds.Add(item.Id.ToString("N"));
@@ -1862,7 +1881,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
                 {
                     if (query.User != null)
                     {
-                        query.SortBy = new[] { "SimilarityScore", "IsUnplayed", "Random" };
+                        query.SortBy = new[] { "SimilarityScore", "IsPlayed", "Random" };
                     }
                     else
                     {
@@ -2475,6 +2494,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)
@@ -2738,8 +2770,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++;
                 }
@@ -3180,6 +3217,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;
@@ -3396,6 +3438,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)
@@ -3405,7 +3493,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
 
             if (values == null)
             {
-                throw new ArgumentNullException("keys");
+                throw new ArgumentNullException("values");
             }
 
             CheckDisposed();
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs
index 7f3b32e06..bfdb9e0c7 100644
--- a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs
+++ b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs
@@ -5,7 +5,9 @@ using MediaBrowser.Model.Logging;
 using System;
 using System.Collections.Generic;
 using System.Data;
+using System.Globalization;
 using System.IO;
+using System.Text;
 using System.Threading;
 using System.Threading.Tasks;
 
@@ -300,6 +302,52 @@ namespace MediaBrowser.Server.Implementations.Persistence
             }
         }
 
+        public UserItemData GetUserData(Guid userId, List<string> keys)
+        {
+            if (userId == Guid.Empty)
+            {
+                throw new ArgumentNullException("userId");
+            }
+            if (keys == null)
+            {
+                throw new ArgumentNullException("keys");
+            }
+
+            using (var cmd = _connection.CreateCommand())
+            {
+                var index = 0;
+                var excludeIds = new List<string>();
+                var builder = new StringBuilder();
+                foreach (var key in keys)
+                {
+                    var paramName = "@Key" + index;
+                    excludeIds.Add("Key =" + paramName);
+                    cmd.Parameters.Add(cmd, paramName, DbType.String).Value = key;
+                    builder.Append(" WHEN Key=" + paramName + " THEN " + index);
+                    index++;
+                }
+
+                var keyText = string.Join(" OR ", excludeIds.ToArray());
+
+                cmd.CommandText = "select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@userId AND (" + keyText + ") ";
+
+                cmd.CommandText += " ORDER BY (Case " + builder + " Else " + keys.Count.ToString(CultureInfo.InvariantCulture) + " End )";
+                cmd.CommandText += " LIMIT 1";
+
+                cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId;
+
+                using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow))
+                {
+                    if (reader.Read())
+                    {
+                        return ReadRow(reader);
+                    }
+                }
+
+                return null;
+            }
+        }
+
         /// <summary>
         /// Return all user-data associated with the given user
         /// </summary>