1 files changed, 89 insertions, 20 deletions

diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs
index 7f3b32e06..62d9e7634 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;
 
@@ -14,11 +16,15 @@ namespace MediaBrowser.Server.Implementations.Persistence
     public class SqliteUserDataRepository : BaseSqliteRepository, IUserDataRepository
     {
         private IDbConnection _connection;
-        private readonly IApplicationPaths _appPaths;
 
-        public SqliteUserDataRepository(ILogManager logManager, IApplicationPaths appPaths) : base(logManager)
+        public SqliteUserDataRepository(ILogManager logManager, IApplicationPaths appPaths, IDbConnector connector) : base(logManager, connector)
         {
-            _appPaths = appPaths;
+            DbFilePath = Path.Combine(appPaths.DataPath, "userdata_v2.db");
+        }
+
+        protected override bool EnableConnectionPooling
+        {
+            get { return false; }
         }
 
         /// <summary>
@@ -33,22 +39,42 @@ namespace MediaBrowser.Server.Implementations.Persistence
             }
         }
 
+        protected override async Task<IDbConnection> CreateConnection(bool isReadOnly = false)
+        {
+            var connection = await DbConnector.Connect(DbFilePath, false, false, 10000).ConfigureAwait(false);
+
+            connection.RunQueries(new[]
+            {
+                "pragma temp_store = memory"
+
+            }, Logger);
+
+            return connection;
+        }
+
         /// <summary>
         /// Opens the connection to the database
         /// </summary>
         /// <returns>Task.</returns>
-        public async Task Initialize(IDbConnector dbConnector)
+        public async Task Initialize(IDbConnection connection, SemaphoreSlim writeLock)
         {
-            var dbFile = Path.Combine(_appPaths.DataPath, "userdata_v2.db");
-
-            _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false);
+            WriteLock.Dispose();
+            WriteLock = writeLock;
+            _connection = connection;
 
             string[] queries = {
 
-                                "create table if not exists userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null)",
+                                "create table if not exists UserDataDb.userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null)",
+
+                                "drop index if exists UserDataDb.idx_userdata",
+                                "drop index if exists UserDataDb.idx_userdata1",
+                                "drop index if exists UserDataDb.idx_userdata2",
+                                "drop index if exists UserDataDb.userdataindex1",
 
-                                "create index if not exists idx_userdata on userdata(key)",
-                                "create unique index if not exists userdataindex on userdata (key, userId)",
+                                "create unique index if not exists UserDataDb.userdataindex on userdata (key, userId)",
+                                "create index if not exists UserDataDb.userdataindex2 on userdata (key, userId, played)",
+                                "create index if not exists UserDataDb.userdataindex3 on userdata (key, userId, playbackPositionTicks)",
+                                "create index if not exists UserDataDb.userdataindex4 on userdata (key, userId, isFavorite)",
 
                                 //pragmas
                                 "pragma temp_store = memory",
@@ -300,6 +326,53 @@ 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 userdataKeys = new List<string>();
+                var builder = new StringBuilder();
+                foreach (var key in keys)
+                {
+                    var paramName = "@Key" + index;
+                    userdataKeys.Add("Key =" + paramName);
+                    cmd.Parameters.Add(cmd, paramName, DbType.String).Value = key;
+                    builder.Append(" WHEN Key=" + paramName + " THEN " + index);
+                    index++;
+                    break;
+                }
+
+                var keyText = string.Join(" OR ", userdataKeys.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>
@@ -367,18 +440,14 @@ namespace MediaBrowser.Server.Implementations.Persistence
             return userData;
         }
 
-        protected override void CloseConnection()
+        protected override void Dispose(bool dispose)
         {
-            if (_connection != null)
-            {
-                if (_connection.IsOpen())
-                {
-                    _connection.Close();
-                }
+            // handled by library database
+        }
 
-                _connection.Dispose();
-                _connection = null;
-            }
+        protected override void CloseConnection()
+        {
+            // handled by library database
         }
     }
 }
\ No newline at end of file