diff options
| author | hatharry <hatharry@hotmail.com> | 2016-07-25 23:29:52 +1200 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2016-07-25 23:29:52 +1200 |
| commit | f21f9923de6291aaf985f32dbbbaddbb26d07fb1 (patch) | |
| tree | 1a313e9a1c6790a755926bcef221c5f680537eae /MediaBrowser.Server.Implementations/Persistence | |
| parent | 6332d0b9436c511a59e2abd67ea8c24ce3d82ace (diff) | |
| parent | 8328f39834f042e1808fd8506bbc7c48151703ab (diff) | |
Merge pull request #15 from MediaBrowser/dev
Dev
Diffstat (limited to 'MediaBrowser.Server.Implementations/Persistence')
12 files changed, 2991 insertions, 1220 deletions
diff --git a/MediaBrowser.Server.Implementations/Persistence/BaseSqliteRepository.cs b/MediaBrowser.Server.Implementations/Persistence/BaseSqliteRepository.cs index 395907844..233ab56fe 100644 --- a/MediaBrowser.Server.Implementations/Persistence/BaseSqliteRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/BaseSqliteRepository.cs @@ -8,14 +8,36 @@ namespace MediaBrowser.Server.Implementations.Persistence { public abstract class BaseSqliteRepository : IDisposable { - protected readonly SemaphoreSlim WriteLock = new SemaphoreSlim(1, 1); + protected SemaphoreSlim WriteLock = new SemaphoreSlim(1, 1); + protected readonly IDbConnector DbConnector; protected ILogger Logger; - protected BaseSqliteRepository(ILogManager logManager) + protected string DbFilePath { get; set; } + + protected BaseSqliteRepository(ILogManager logManager, IDbConnector dbConnector) { + DbConnector = dbConnector; Logger = logManager.GetLogger(GetType().Name); } + protected virtual bool EnableConnectionPooling + { + get { return true; } + } + + protected virtual async Task<IDbConnection> CreateConnection(bool isReadOnly = false) + { + var connection = await DbConnector.Connect(DbFilePath, false, true).ConfigureAwait(false); + + connection.RunQueries(new[] + { + "pragma temp_store = memory" + + }, Logger); + + return connection; + } + private bool _disposed; protected void CheckDisposed() { @@ -84,6 +106,9 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - protected abstract void CloseConnection(); + protected virtual void CloseConnection() + { + + } } -} +}
\ No newline at end of file diff --git a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs index 3c8a0ffeb..bf2afb5ac 100644 --- a/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs +++ b/MediaBrowser.Server.Implementations/Persistence/CleanDatabaseScheduledTask.cs @@ -15,6 +15,7 @@ using System.Threading.Tasks; using CommonIO; using MediaBrowser.Controller.Channels; using MediaBrowser.Controller.Entities.Audio; +using MediaBrowser.Controller.LiveTv; using MediaBrowser.Controller.Localization; using MediaBrowser.Controller.Net; using MediaBrowser.Server.Implementations.ScheduledTasks; @@ -32,7 +33,7 @@ namespace MediaBrowser.Server.Implementations.Persistence private readonly ILocalizationManager _localization; private readonly ITaskManager _taskManager; - public const int MigrationVersion = 20; + public const int MigrationVersion = 23; public static bool EnableUnavailableMessage = false; public CleanDatabaseScheduledTask(ILibraryManager libraryManager, IItemRepository itemRepo, ILogger logger, IServerConfigurationManager config, IFileSystem fileSystem, IHttpServer httpServer, ILocalizationManager localization, ITaskManager taskManager) @@ -110,6 +111,12 @@ namespace MediaBrowser.Server.Implementations.Persistence _config.SaveConfiguration(); } + if (_config.Configuration.SchemaVersion < SqliteItemRepository.LatestSchemaVersion) + { + _config.Configuration.SchemaVersion = SqliteItemRepository.LatestSchemaVersion; + _config.SaveConfiguration(); + } + if (EnableUnavailableMessage) { EnableUnavailableMessage = false; @@ -139,7 +146,8 @@ namespace MediaBrowser.Server.Implementations.Persistence { var itemIds = _libraryManager.GetItemIds(new InternalItemsQuery { - IsCurrentSchema = false + IsCurrentSchema = false, + ExcludeItemTypes = new[] { typeof(LiveTvProgram).Name } }); var numComplete = 0; @@ -147,6 +155,8 @@ namespace MediaBrowser.Server.Implementations.Persistence _logger.Debug("Upgrading schema for {0} items", numItems); + var list = new List<BaseItem>(); + foreach (var itemId in itemIds) { cancellationToken.ThrowIfCancellationRequested(); @@ -158,27 +168,50 @@ namespace MediaBrowser.Server.Implementations.Persistence if (item != null) { - try - { - await _itemRepo.SaveItem(item, cancellationToken).ConfigureAwait(false); - } - catch (OperationCanceledException) - { - throw; - } - catch (Exception ex) - { - _logger.ErrorException("Error saving item", ex); - } + list.Add(item); } } + if (list.Count >= 1000) + { + try + { + await _itemRepo.SaveItems(list, cancellationToken).ConfigureAwait(false); + } + catch (OperationCanceledException) + { + throw; + } + catch (Exception ex) + { + _logger.ErrorException("Error saving item", ex); + } + + list.Clear(); + } + numComplete++; double percent = numComplete; percent /= numItems; progress.Report(percent * 100); } + if (list.Count > 0) + { + try + { + await _itemRepo.SaveItems(list, cancellationToken).ConfigureAwait(false); + } + catch (OperationCanceledException) + { + throw; + } + catch (Exception ex) + { + _logger.ErrorException("Error saving item", ex); + } + } + progress.Report(100); } @@ -230,14 +263,14 @@ namespace MediaBrowser.Server.Implementations.Persistence // These have their own cleanup routines ExcludeItemTypes = new[] { - typeof(Person).Name, - typeof(Genre).Name, - typeof(MusicGenre).Name, - typeof(GameGenre).Name, - typeof(Studio).Name, - typeof(Year).Name, - typeof(Channel).Name, - typeof(AggregateFolder).Name, + typeof(Person).Name, + typeof(Genre).Name, + typeof(MusicGenre).Name, + typeof(GameGenre).Name, + typeof(Studio).Name, + typeof(Year).Name, + typeof(Channel).Name, + typeof(AggregateFolder).Name, typeof(CollectionFolder).Name } }); @@ -307,8 +340,8 @@ namespace MediaBrowser.Server.Implementations.Persistence public IEnumerable<ITaskTrigger> GetDefaultTriggers() { - return new ITaskTrigger[] - { + return new ITaskTrigger[] + { new IntervalTrigger{ Interval = TimeSpan.FromHours(24)} }; } diff --git a/MediaBrowser.Server.Implementations/Persistence/DataExtensions.cs b/MediaBrowser.Server.Implementations/Persistence/DataExtensions.cs new file mode 100644 index 000000000..61ce6e351 --- /dev/null +++ b/MediaBrowser.Server.Implementations/Persistence/DataExtensions.cs @@ -0,0 +1,185 @@ +using System.Text; +using MediaBrowser.Model.Logging; +using MediaBrowser.Model.Serialization; +using System; +using System.Data; +using System.IO; +using System.Threading.Tasks; + +namespace MediaBrowser.Server.Implementations.Persistence +{ + static class DataExtensions + { + /// <summary> + /// Determines whether the specified conn is open. + /// </summary> + /// <param name="conn">The conn.</param> + /// <returns><c>true</c> if the specified conn is open; otherwise, <c>false</c>.</returns> + public static bool IsOpen(this IDbConnection conn) + { + return conn.State == ConnectionState.Open; + } + + public static IDataParameter GetParameter(this IDbCommand cmd, int index) + { + return (IDataParameter)cmd.Parameters[index]; + } + + public static IDataParameter Add(this IDataParameterCollection paramCollection, IDbCommand cmd, string name, DbType type) + { + var param = cmd.CreateParameter(); + + param.ParameterName = name; + param.DbType = type; + + paramCollection.Add(param); + + return param; + } + + public static IDataParameter Add(this IDataParameterCollection paramCollection, IDbCommand cmd, string name) + { + var param = cmd.CreateParameter(); + + param.ParameterName = name; + + paramCollection.Add(param); + + return param; + } + + + /// <summary> + /// Gets a stream from a DataReader at a given ordinal + /// </summary> + /// <param name="reader">The reader.</param> + /// <param name="ordinal">The ordinal.</param> + /// <returns>Stream.</returns> + /// <exception cref="System.ArgumentNullException">reader</exception> + public static Stream GetMemoryStream(this IDataReader reader, int ordinal) + { + if (reader == null) + { + throw new ArgumentNullException("reader"); + } + + var memoryStream = new MemoryStream(); + var num = 0L; + var array = new byte[4096]; + long bytes; + do + { + bytes = reader.GetBytes(ordinal, num, array, 0, array.Length); + memoryStream.Write(array, 0, (int)bytes); + num += bytes; + } + while (bytes > 0L); + memoryStream.Position = 0; + return memoryStream; + } + + /// <summary> + /// Runs the queries. + /// </summary> + /// <param name="connection">The connection.</param> + /// <param name="queries">The queries.</param> + /// <param name="logger">The logger.</param> + /// <returns><c>true</c> if XXXX, <c>false</c> otherwise</returns> + /// <exception cref="System.ArgumentNullException">queries</exception> + public static void RunQueries(this IDbConnection connection, string[] queries, ILogger logger) + { + if (queries == null) + { + throw new ArgumentNullException("queries"); + } + + using (var tran = connection.BeginTransaction()) + { + try + { + using (var cmd = connection.CreateCommand()) + { + foreach (var query in queries) + { + cmd.Transaction = tran; + cmd.CommandText = query; + cmd.ExecuteNonQuery(); + } + } + + tran.Commit(); + } + catch (Exception e) + { + logger.ErrorException("Error running queries", e); + tran.Rollback(); + throw; + } + } + } + + public static void Attach(IDbConnection db, string path, string alias) + { + using (var cmd = db.CreateCommand()) + { + cmd.CommandText = string.Format("attach @dbPath as {0};", alias); + cmd.Parameters.Add(cmd, "@dbPath", DbType.String); + cmd.GetParameter(0).Value = path; + + cmd.ExecuteNonQuery(); + } + } + + /// <summary> + /// Serializes to bytes. + /// </summary> + /// <param name="json">The json.</param> + /// <param name="obj">The obj.</param> + /// <returns>System.Byte[][].</returns> + /// <exception cref="System.ArgumentNullException">obj</exception> + public static byte[] SerializeToBytes(this IJsonSerializer json, object obj) + { + if (obj == null) + { + throw new ArgumentNullException("obj"); + } + + using (var stream = new MemoryStream()) + { + json.SerializeToStream(obj, stream); + return stream.ToArray(); + } + } + + public static void AddColumn(this IDbConnection connection, ILogger logger, string table, string columnName, string type) + { + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "PRAGMA table_info(" + table + ")"; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + if (!reader.IsDBNull(1)) + { + var name = reader.GetString(1); + + if (string.Equals(name, columnName, StringComparison.OrdinalIgnoreCase)) + { + return; + } + } + } + } + } + + var builder = new StringBuilder(); + + builder.AppendLine("alter table " + table); + builder.AppendLine("add column " + columnName + " " + type); + + connection.RunQueries(new[] { builder.ToString() }, logger); + } + } +}
\ No newline at end of file diff --git a/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs b/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs new file mode 100644 index 000000000..596cf8407 --- /dev/null +++ b/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs @@ -0,0 +1,10 @@ +using System.Data; +using System.Threading.Tasks; + +namespace MediaBrowser.Server.Implementations.Persistence +{ + public interface IDbConnector + { + Task<IDbConnection> Connect(string dbPath, bool isReadOnly, bool enablePooling = false, int? cacheSize = null); + } +} diff --git a/MediaBrowser.Server.Implementations/Persistence/MediaStreamColumns.cs b/MediaBrowser.Server.Implementations/Persistence/MediaStreamColumns.cs index 211c77107..1d9be2e0d 100644 --- a/MediaBrowser.Server.Implementations/Persistence/MediaStreamColumns.cs +++ b/MediaBrowser.Server.Implementations/Persistence/MediaStreamColumns.cs @@ -21,14 +21,18 @@ namespace MediaBrowser.Server.Implementations.Persistence AddPixelFormatColumnCommand(); AddBitDepthCommand(); AddIsAnamorphicColumn(); - AddIsCabacColumn(); AddKeyFramesColumn(); AddRefFramesCommand(); AddCodecTagColumn(); AddCommentColumn(); + AddNalColumn(); + AddIsAvcColumn(); + AddTitleColumn(); + AddTimeBaseColumn(); + AddCodecTimeBaseColumn(); } - private void AddCommentColumn() + private void AddIsAvcColumn() { using (var cmd = _connection.CreateCommand()) { @@ -42,7 +46,7 @@ namespace MediaBrowser.Server.Implementations.Persistence { var name = reader.GetString(1); - if (string.Equals(name, "Comment", StringComparison.OrdinalIgnoreCase)) + if (string.Equals(name, "IsAvc", StringComparison.OrdinalIgnoreCase)) { return; } @@ -54,12 +58,12 @@ namespace MediaBrowser.Server.Implementations.Persistence var builder = new StringBuilder(); builder.AppendLine("alter table mediastreams"); - builder.AppendLine("add column Comment TEXT"); + builder.AppendLine("add column IsAvc BIT NULL"); _connection.RunQueries(new[] { builder.ToString() }, _logger); } - private void AddCodecTagColumn() + private void AddTimeBaseColumn() { using (var cmd = _connection.CreateCommand()) { @@ -73,7 +77,7 @@ namespace MediaBrowser.Server.Implementations.Persistence { var name = reader.GetString(1); - if (string.Equals(name, "CodecTag", StringComparison.OrdinalIgnoreCase)) + if (string.Equals(name, "TimeBase", StringComparison.OrdinalIgnoreCase)) { return; } @@ -85,12 +89,12 @@ namespace MediaBrowser.Server.Implementations.Persistence var builder = new StringBuilder(); builder.AppendLine("alter table mediastreams"); - builder.AppendLine("add column CodecTag TEXT"); + builder.AppendLine("add column TimeBase TEXT"); _connection.RunQueries(new[] { builder.ToString() }, _logger); } - private void AddPixelFormatColumnCommand() + private void AddCodecTimeBaseColumn() { using (var cmd = _connection.CreateCommand()) { @@ -104,7 +108,7 @@ namespace MediaBrowser.Server.Implementations.Persistence { var name = reader.GetString(1); - if (string.Equals(name, "PixelFormat", StringComparison.OrdinalIgnoreCase)) + if (string.Equals(name, "CodecTimeBase", StringComparison.OrdinalIgnoreCase)) { return; } @@ -116,12 +120,12 @@ namespace MediaBrowser.Server.Implementations.Persistence var builder = new StringBuilder(); builder.AppendLine("alter table mediastreams"); - builder.AppendLine("add column PixelFormat TEXT"); + builder.AppendLine("add column CodecTimeBase TEXT"); _connection.RunQueries(new[] { builder.ToString() }, _logger); } - private void AddBitDepthCommand() + private void AddTitleColumn() { using (var cmd = _connection.CreateCommand()) { @@ -135,7 +139,7 @@ namespace MediaBrowser.Server.Implementations.Persistence { var name = reader.GetString(1); - if (string.Equals(name, "BitDepth", StringComparison.OrdinalIgnoreCase)) + if (string.Equals(name, "Title", StringComparison.OrdinalIgnoreCase)) { return; } @@ -147,12 +151,12 @@ namespace MediaBrowser.Server.Implementations.Persistence var builder = new StringBuilder(); builder.AppendLine("alter table mediastreams"); - builder.AppendLine("add column BitDepth INT NULL"); + builder.AppendLine("add column Title TEXT"); _connection.RunQueries(new[] { builder.ToString() }, _logger); } - private void AddRefFramesCommand() + private void AddNalColumn() { using (var cmd = _connection.CreateCommand()) { @@ -166,7 +170,7 @@ namespace MediaBrowser.Server.Implementations.Persistence { var name = reader.GetString(1); - if (string.Equals(name, "RefFrames", StringComparison.OrdinalIgnoreCase)) + if (string.Equals(name, "NalLengthSize", StringComparison.OrdinalIgnoreCase)) { return; } @@ -178,12 +182,74 @@ namespace MediaBrowser.Server.Implementations.Persistence var builder = new StringBuilder(); builder.AppendLine("alter table mediastreams"); - builder.AppendLine("add column RefFrames INT NULL"); + builder.AppendLine("add column NalLengthSize TEXT"); + + _connection.RunQueries(new[] { builder.ToString() }, _logger); + } + + private void AddCommentColumn() + { + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "PRAGMA table_info(mediastreams)"; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + if (!reader.IsDBNull(1)) + { + var name = reader.GetString(1); + + if (string.Equals(name, "Comment", StringComparison.OrdinalIgnoreCase)) + { + return; + } + } + } + } + } + + var builder = new StringBuilder(); + + builder.AppendLine("alter table mediastreams"); + builder.AppendLine("add column Comment TEXT"); + + _connection.RunQueries(new[] { builder.ToString() }, _logger); + } + + private void AddCodecTagColumn() + { + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "PRAGMA table_info(mediastreams)"; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + if (!reader.IsDBNull(1)) + { + var name = reader.GetString(1); + + if (string.Equals(name, "CodecTag", StringComparison.OrdinalIgnoreCase)) + { + return; + } + } + } + } + } + + var builder = new StringBuilder(); + + builder.AppendLine("alter table mediastreams"); + builder.AppendLine("add column CodecTag TEXT"); _connection.RunQueries(new[] { builder.ToString() }, _logger); } - private void AddIsCabacColumn() + private void AddPixelFormatColumnCommand() { using (var cmd = _connection.CreateCommand()) { @@ -197,7 +263,7 @@ namespace MediaBrowser.Server.Implementations.Persistence { var name = reader.GetString(1); - if (string.Equals(name, "IsCabac", StringComparison.OrdinalIgnoreCase)) + if (string.Equals(name, "PixelFormat", StringComparison.OrdinalIgnoreCase)) { return; } @@ -209,7 +275,69 @@ namespace MediaBrowser.Server.Implementations.Persistence var builder = new StringBuilder(); builder.AppendLine("alter table mediastreams"); - builder.AppendLine("add column IsCabac BIT NULL"); + builder.AppendLine("add column PixelFormat TEXT"); + + _connection.RunQueries(new[] { builder.ToString() }, _logger); + } + + private void AddBitDepthCommand() + { + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "PRAGMA table_info(mediastreams)"; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + if (!reader.IsDBNull(1)) + { + var name = reader.GetString(1); + + if (string.Equals(name, "BitDepth", StringComparison.OrdinalIgnoreCase)) + { + return; + } + } + } + } + } + + var builder = new StringBuilder(); + + builder.AppendLine("alter table mediastreams"); + builder.AppendLine("add column BitDepth INT NULL"); + + _connection.RunQueries(new[] { builder.ToString() }, _logger); + } + + private void AddRefFramesCommand() + { + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "PRAGMA table_info(mediastreams)"; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + if (!reader.IsDBNull(1)) + { + var name = reader.GetString(1); + + if (string.Equals(name, "RefFrames", StringComparison.OrdinalIgnoreCase)) + { + return; + } + } + } + } + } + + var builder = new StringBuilder(); + + builder.AppendLine("alter table mediastreams"); + builder.AppendLine("add column RefFrames INT NULL"); _connection.RunQueries(new[] { builder.ToString() }, _logger); } diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs index 45e0304c1..40970dbe4 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs @@ -18,12 +18,11 @@ namespace MediaBrowser.Server.Implementations.Persistence /// </summary> public class SqliteDisplayPreferencesRepository : BaseSqliteRepository, IDisplayPreferencesRepository { - private IDbConnection _connection; - - public SqliteDisplayPreferencesRepository(ILogManager logManager, IJsonSerializer jsonSerializer, IApplicationPaths appPaths) : base(logManager) + public SqliteDisplayPreferencesRepository(ILogManager logManager, IJsonSerializer jsonSerializer, IApplicationPaths appPaths, IDbConnector dbConnector) + : base(logManager, dbConnector) { _jsonSerializer = jsonSerializer; - _appPaths = appPaths; + DbFilePath = Path.Combine(appPaths.DataPath, "displaypreferences.db"); } /// <summary> @@ -44,32 +43,21 @@ namespace MediaBrowser.Server.Implementations.Persistence private readonly IJsonSerializer _jsonSerializer; /// <summary> - /// The _app paths - /// </summary> - private readonly IApplicationPaths _appPaths; - - /// <summary> /// Opens the connection to the database /// </summary> /// <returns>Task.</returns> public async Task Initialize() { - var dbFile = Path.Combine(_appPaths.DataPath, "displaypreferences.db"); - - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); - - string[] queries = { + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + string[] queries = { "create table if not exists userdisplaypreferences (id GUID, userId GUID, client text, data BLOB)", - "create unique index if not exists userdisplaypreferencesindex on userdisplaypreferences (id, userId, client)", - - //pragmas - "pragma temp_store = memory", - - "pragma shrink_memory" + "create unique index if not exists userdisplaypreferencesindex on userdisplaypreferences (id, userId, client)" }; - _connection.RunQueries(queries, Logger); + connection.RunQueries(queries, Logger); + } } /// <summary> @@ -96,58 +84,57 @@ namespace MediaBrowser.Server.Implementations.Persistence var serialized = _jsonSerializer.SerializeToBytes(displayPreferences); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); + IDbTransaction transaction = null; - using (var cmd = _connection.CreateCommand()) + try { - cmd.CommandText = "replace into userdisplaypreferences (id, userid, client, data) values (@1, @2, @3, @4)"; + transaction = connection.BeginTransaction(); - cmd.Parameters.Add(cmd, "@1", DbType.Guid).Value = new Guid(displayPreferences.Id); - cmd.Parameters.Add(cmd, "@2", DbType.Guid).Value = userId; - cmd.Parameters.Add(cmd, "@3", DbType.String).Value = client; - cmd.Parameters.Add(cmd, "@4", DbType.Binary).Value = serialized; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "replace into userdisplaypreferences (id, userid, client, data) values (@1, @2, @3, @4)"; - cmd.Transaction = transaction; + cmd.Parameters.Add(cmd, "@1", DbType.Guid).Value = new Guid(displayPreferences.Id); + cmd.Parameters.Add(cmd, "@2", DbType.Guid).Value = userId; + cmd.Parameters.Add(cmd, "@3", DbType.String).Value = client; + cmd.Parameters.Add(cmd, "@4", DbType.Binary).Value = serialized; - cmd.ExecuteNonQuery(); - } + cmd.Transaction = transaction; - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) + cmd.ExecuteNonQuery(); + } + + transaction.Commit(); + } + catch (OperationCanceledException) { - transaction.Rollback(); + if (transaction != null) + { + transaction.Rollback(); + } + + throw; } + catch (Exception e) + { + Logger.ErrorException("Failed to save display preferences:", e); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save display preferences:", e); + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); + throw; } - - throw; - } - finally - { - if (transaction != null) + finally { - transaction.Dispose(); + if (transaction != null) + { + transaction.Dispose(); + } } - - WriteLock.Release(); } } @@ -168,64 +155,63 @@ namespace MediaBrowser.Server.Implementations.Persistence cancellationToken.ThrowIfCancellationRequested(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); + IDbTransaction transaction = null; - foreach (var displayPreference in displayPreferences) + try { + transaction = connection.BeginTransaction(); - var serialized = _jsonSerializer.SerializeToBytes(displayPreference); - - using (var cmd = _connection.CreateCommand()) + foreach (var displayPreference in displayPreferences) { - cmd.CommandText = "replace into userdisplaypreferences (id, userid, client, data) values (@1, @2, @3, @4)"; - cmd.Parameters.Add(cmd, "@1", DbType.Guid).Value = new Guid(displayPreference.Id); - cmd.Parameters.Add(cmd, "@2", DbType.Guid).Value = userId; - cmd.Parameters.Add(cmd, "@3", DbType.String).Value = displayPreference.Client; - cmd.Parameters.Add(cmd, "@4", DbType.Binary).Value = serialized; + var serialized = _jsonSerializer.SerializeToBytes(displayPreference); - cmd.Transaction = transaction; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "replace into userdisplaypreferences (id, userid, client, data) values (@1, @2, @3, @4)"; - cmd.ExecuteNonQuery(); + cmd.Parameters.Add(cmd, "@1", DbType.Guid).Value = new Guid(displayPreference.Id); + cmd.Parameters.Add(cmd, "@2", DbType.Guid).Value = userId; + cmd.Parameters.Add(cmd, "@3", DbType.String).Value = displayPreference.Client; + cmd.Parameters.Add(cmd, "@4", DbType.Binary).Value = serialized; + + cmd.Transaction = transaction; + + cmd.ExecuteNonQuery(); + } } - } - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) + transaction.Commit(); + } + catch (OperationCanceledException) { - transaction.Rollback(); + if (transaction != null) + { + transaction.Rollback(); + } + + throw; } + catch (Exception e) + { + Logger.ErrorException("Failed to save display preferences:", e); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save display preferences:", e); + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); + throw; } - - throw; - } - finally - { - if (transaction != null) + finally { - transaction.Dispose(); + if (transaction != null) + { + transaction.Dispose(); + } } - - WriteLock.Release(); } } @@ -246,28 +232,33 @@ namespace MediaBrowser.Server.Implementations.Persistence var guidId = displayPreferencesId.GetMD5(); - var cmd = _connection.CreateCommand(); - cmd.CommandText = "select data from userdisplaypreferences where id = @id and userId=@userId and client=@client"; - - cmd.Parameters.Add(cmd, "@id", DbType.Guid).Value = guidId; - cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; - cmd.Parameters.Add(cmd, "@client", DbType.String).Value = client; - - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) + using (var connection = CreateConnection(true).Result) { - if (reader.Read()) + using (var cmd = connection.CreateCommand()) { - using (var stream = reader.GetMemoryStream(0)) + cmd.CommandText = "select data from userdisplaypreferences where id = @id and userId=@userId and client=@client"; + + cmd.Parameters.Add(cmd, "@id", DbType.Guid).Value = guidId; + cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; + cmd.Parameters.Add(cmd, "@client", DbType.String).Value = client; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) { - return _jsonSerializer.DeserializeFromStream<DisplayPreferences>(stream); + if (reader.Read()) + { + using (var stream = reader.GetMemoryStream(0)) + { + return _jsonSerializer.DeserializeFromStream<DisplayPreferences>(stream); + } + } } + + return new DisplayPreferences + { + Id = guidId.ToString("N") + }; } } - - return new DisplayPreferences - { - Id = guidId.ToString("N") - }; } /// <summary> @@ -278,36 +269,30 @@ namespace MediaBrowser.Server.Implementations.Persistence /// <exception cref="System.ArgumentNullException">item</exception> public IEnumerable<DisplayPreferences> GetAllDisplayPreferences(Guid userId) { + var list = new List<DisplayPreferences>(); - var cmd = _connection.CreateCommand(); - cmd.CommandText = "select data from userdisplaypreferences where userId=@userId"; - - cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; - - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + using (var connection = CreateConnection(true).Result) { - while (reader.Read()) + using (var cmd = connection.CreateCommand()) { - using (var stream = reader.GetMemoryStream(0)) + cmd.CommandText = "select data from userdisplaypreferences where userId=@userId"; + + cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { - yield return _jsonSerializer.DeserializeFromStream<DisplayPreferences>(stream); + while (reader.Read()) + { + using (var stream = reader.GetMemoryStream(0)) + { + list.Add(_jsonSerializer.DeserializeFromStream<DisplayPreferences>(stream)); + } + } } } } - } - - protected override void CloseConnection() - { - if (_connection != null) - { - if (_connection.IsOpen()) - { - _connection.Close(); - } - _connection.Dispose(); - _connection = null; - } + return list; } public Task SaveDisplayPreferences(DisplayPreferences displayPreferences, string userId, string client, CancellationToken cancellationToken) diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs index 4fb1e07dd..d5b582da5 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs @@ -1,218 +1,58 @@ -using System.Text; -using MediaBrowser.Model.Logging; -using MediaBrowser.Model.Serialization; -using System; +using System; +using System.Collections.Generic; using System.Data; using System.Data.SQLite; -using System.IO; +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 { /// <summary> /// Class SQLiteExtensions /// </summary> - static class SqliteExtensions + public static class SqliteExtensions { /// <summary> - /// Determines whether the specified conn is open. - /// </summary> - /// <param name="conn">The conn.</param> - /// <returns><c>true</c> if the specified conn is open; otherwise, <c>false</c>.</returns> - public static bool IsOpen(this IDbConnection conn) - { - return conn.State == ConnectionState.Open; - } - - public static IDataParameter GetParameter(this IDbCommand cmd, int index) - { - return (IDataParameter)cmd.Parameters[index]; - } - - public static IDataParameter Add(this IDataParameterCollection paramCollection, IDbCommand cmd, string name, DbType type) - { - var param = cmd.CreateParameter(); - - param.ParameterName = name; - param.DbType = type; - - paramCollection.Add(param); - - return param; - } - - public static IDataParameter Add(this IDataParameterCollection paramCollection, IDbCommand cmd, string name) - { - var param = cmd.CreateParameter(); - - param.ParameterName = name; - - paramCollection.Add(param); - - return param; - } - - - /// <summary> - /// Gets a stream from a DataReader at a given ordinal - /// </summary> - /// <param name="reader">The reader.</param> - /// <param name="ordinal">The ordinal.</param> - /// <returns>Stream.</returns> - /// <exception cref="System.ArgumentNullException">reader</exception> - public static Stream GetMemoryStream(this IDataReader reader, int ordinal) - { - if (reader == null) - { - throw new ArgumentNullException("reader"); - } - - var memoryStream = new MemoryStream(); - var num = 0L; - var array = new byte[4096]; - long bytes; - do - { - bytes = reader.GetBytes(ordinal, num, array, 0, array.Length); - memoryStream.Write(array, 0, (int)bytes); - num += bytes; - } - while (bytes > 0L); - memoryStream.Position = 0; - return memoryStream; - } - - /// <summary> - /// Runs the queries. - /// </summary> - /// <param name="connection">The connection.</param> - /// <param name="queries">The queries.</param> - /// <param name="logger">The logger.</param> - /// <returns><c>true</c> if XXXX, <c>false</c> otherwise</returns> - /// <exception cref="System.ArgumentNullException">queries</exception> - public static void RunQueries(this IDbConnection connection, string[] queries, ILogger logger) - { - if (queries == null) - { - throw new ArgumentNullException("queries"); - } - - using (var tran = connection.BeginTransaction()) - { - try - { - using (var cmd = connection.CreateCommand()) - { - foreach (var query in queries) - { - cmd.Transaction = tran; - cmd.CommandText = query; - cmd.ExecuteNonQuery(); - } - } - - tran.Commit(); - } - catch (Exception e) - { - logger.ErrorException("Error running queries", e); - tran.Rollback(); - throw; - } - } - } - - /// <summary> /// Connects to db. /// </summary> - /// <param name="dbPath">The db path.</param> - /// <param name="logger">The logger.</param> - /// <returns>Task{IDbConnection}.</returns> - /// <exception cref="System.ArgumentNullException">dbPath</exception> - public static async Task<IDbConnection> ConnectToDb(string dbPath, ILogger logger) + public static async Task<IDbConnection> ConnectToDb(string dbPath, bool isReadOnly, bool enablePooling, int? cacheSize, ILogger logger) { if (string.IsNullOrEmpty(dbPath)) { throw new ArgumentNullException("dbPath"); } - logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, dbPath); + SQLiteConnection.SetMemoryStatus(false); var connectionstr = new SQLiteConnectionStringBuilder { PageSize = 4096, - CacheSize = 2000, - SyncMode = SynchronizationModes.Full, + CacheSize = cacheSize ?? 2000, + SyncMode = SynchronizationModes.Normal, DataSource = dbPath, - JournalMode = SQLiteJournalModeEnum.Wal - }; - - var connection = new SQLiteConnection(connectionstr.ConnectionString); + JournalMode = SQLiteJournalModeEnum.Wal, - await connection.OpenAsync().ConfigureAwait(false); - - return connection; - } - - public static void Attach(IDbConnection db, string path, string alias) - { - using (var cmd = db.CreateCommand()) - { - cmd.CommandText = string.Format("attach '{0}' as {1};", path, alias); - cmd.ExecuteNonQuery(); - } - } - - /// <summary> - /// Serializes to bytes. - /// </summary> - /// <param name="json">The json.</param> - /// <param name="obj">The obj.</param> - /// <returns>System.Byte[][].</returns> - /// <exception cref="System.ArgumentNullException">obj</exception> - public static byte[] SerializeToBytes(this IJsonSerializer json, object obj) - { - if (obj == null) - { - throw new ArgumentNullException("obj"); - } + // This is causing crashing under linux + Pooling = enablePooling && Environment.OSVersion.Platform == PlatformID.Win32NT, + ReadOnly = isReadOnly + }; - using (var stream = new MemoryStream()) - { - json.SerializeToStream(obj, stream); - return stream.ToArray(); - } - } + var connectionString = connectionstr.ConnectionString; - public static void AddColumn(this IDbConnection connection, ILogger logger, string table, string columnName, string type) - { - using (var cmd = connection.CreateCommand()) + if (!enablePooling) { - cmd.CommandText = "PRAGMA table_info(" + table + ")"; - - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) - { - while (reader.Read()) - { - if (!reader.IsDBNull(1)) - { - var name = reader.GetString(1); - - if (string.Equals(name, columnName, StringComparison.OrdinalIgnoreCase)) - { - return; - } - } - } - } + logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, connectionString); } - var builder = new StringBuilder(); + var connection = new SQLiteConnection(connectionString); - builder.AppendLine("alter table " + table); - builder.AppendLine("add column " + columnName + " " + type); + await connection.OpenAsync().ConfigureAwait(false); - connection.RunQueries(new[] { builder.ToString() }, logger); + return connection; } } -}
\ No newline at end of file +} diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs index 2d5aad04d..7a5e00090 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs @@ -16,19 +16,11 @@ namespace MediaBrowser.Server.Implementations.Persistence { public class SqliteFileOrganizationRepository : BaseSqliteRepository, IFileOrganizationRepository, IDisposable { - private IDbConnection _connection; - - private readonly IServerApplicationPaths _appPaths; - private readonly CultureInfo _usCulture = new CultureInfo("en-US"); - private IDbCommand _saveResultCommand; - private IDbCommand _deleteResultCommand; - private IDbCommand _deleteAllCommand; - - public SqliteFileOrganizationRepository(ILogManager logManager, IServerApplicationPaths appPaths) : base(logManager) + public SqliteFileOrganizationRepository(ILogManager logManager, IServerApplicationPaths appPaths, IDbConnector connector) : base(logManager, connector) { - _appPaths = appPaths; + DbFilePath = Path.Combine(appPaths.DataPath, "fileorganization.db"); } /// <summary> @@ -37,53 +29,16 @@ namespace MediaBrowser.Server.Implementations.Persistence /// <returns>Task.</returns> public async Task Initialize() { - var dbFile = Path.Combine(_appPaths.DataPath, "fileorganization.db"); - - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); - - string[] queries = { + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + string[] queries = { "create table if not exists FileOrganizerResults (ResultId GUID PRIMARY KEY, OriginalPath TEXT, TargetPath TEXT, FileLength INT, OrganizationDate datetime, Status TEXT, OrganizationType TEXT, StatusMessage TEXT, ExtractedName TEXT, ExtractedYear int null, ExtractedSeasonNumber int null, ExtractedEpisodeNumber int null, ExtractedEndingEpisodeNumber, DuplicatePaths TEXT int null)", - "create index if not exists idx_FileOrganizerResults on FileOrganizerResults(ResultId)", - - //pragmas - "pragma temp_store = memory", - - "pragma shrink_memory" + "create index if not exists idx_FileOrganizerResults on FileOrganizerResults(ResultId)" }; - _connection.RunQueries(queries, Logger); - - PrepareStatements(); - } - - private void PrepareStatements() - { - _saveResultCommand = _connection.CreateCommand(); - _saveResultCommand.CommandText = "replace into FileOrganizerResults (ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths) values (@ResultId, @OriginalPath, @TargetPath, @FileLength, @OrganizationDate, @Status, @OrganizationType, @StatusMessage, @ExtractedName, @ExtractedYear, @ExtractedSeasonNumber, @ExtractedEpisodeNumber, @ExtractedEndingEpisodeNumber, @DuplicatePaths)"; - - _saveResultCommand.Parameters.Add(_saveResultCommand, "@ResultId"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@OriginalPath"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@TargetPath"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@FileLength"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@OrganizationDate"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@Status"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@OrganizationType"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@StatusMessage"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@ExtractedName"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@ExtractedYear"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@ExtractedSeasonNumber"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@ExtractedEpisodeNumber"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@ExtractedEndingEpisodeNumber"); - _saveResultCommand.Parameters.Add(_saveResultCommand, "@DuplicatePaths"); - - _deleteResultCommand = _connection.CreateCommand(); - _deleteResultCommand.CommandText = "delete from FileOrganizerResults where ResultId = @ResultId"; - - _deleteResultCommand.Parameters.Add(_saveResultCommand, "@ResultId"); - - _deleteAllCommand = _connection.CreateCommand(); - _deleteAllCommand.CommandText = "delete from FileOrganizerResults"; + connection.RunQueries(queries, Logger); + } } public async Task SaveResult(FileOrganizationResult result, CancellationToken cancellationToken) @@ -95,65 +50,84 @@ namespace MediaBrowser.Server.Implementations.Persistence cancellationToken.ThrowIfCancellationRequested(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try - { - transaction = _connection.BeginTransaction(); - - var index = 0; - - _saveResultCommand.GetParameter(index++).Value = new Guid(result.Id); - _saveResultCommand.GetParameter(index++).Value = result.OriginalPath; - _saveResultCommand.GetParameter(index++).Value = result.TargetPath; - _saveResultCommand.GetParameter(index++).Value = result.FileSize; - _saveResultCommand.GetParameter(index++).Value = result.Date; - _saveResultCommand.GetParameter(index++).Value = result.Status.ToString(); - _saveResultCommand.GetParameter(index++).Value = result.Type.ToString(); - _saveResultCommand.GetParameter(index++).Value = result.StatusMessage; - _saveResultCommand.GetParameter(index++).Value = result.ExtractedName; - _saveResultCommand.GetParameter(index++).Value = result.ExtractedYear; - _saveResultCommand.GetParameter(index++).Value = result.ExtractedSeasonNumber; - _saveResultCommand.GetParameter(index++).Value = result.ExtractedEpisodeNumber; - _saveResultCommand.GetParameter(index++).Value = result.ExtractedEndingEpisodeNumber; - _saveResultCommand.GetParameter(index).Value = string.Join("|", result.DuplicatePaths.ToArray()); - - _saveResultCommand.Transaction = transaction; - - _saveResultCommand.ExecuteNonQuery(); - - transaction.Commit(); - } - catch (OperationCanceledException) + using (var connection = await CreateConnection().ConfigureAwait(false)) { - if (transaction != null) + using (var saveResultCommand = connection.CreateCommand()) { - transaction.Rollback(); - } + saveResultCommand.CommandText = "replace into FileOrganizerResults (ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths) values (@ResultId, @OriginalPath, @TargetPath, @FileLength, @OrganizationDate, @Status, @OrganizationType, @StatusMessage, @ExtractedName, @ExtractedYear, @ExtractedSeasonNumber, @ExtractedEpisodeNumber, @ExtractedEndingEpisodeNumber, @DuplicatePaths)"; + + saveResultCommand.Parameters.Add(saveResultCommand, "@ResultId"); + saveResultCommand.Parameters.Add(saveResultCommand, "@OriginalPath"); + saveResultCommand.Parameters.Add(saveResultCommand, "@TargetPath"); + saveResultCommand.Parameters.Add(saveResultCommand, "@FileLength"); + saveResultCommand.Parameters.Add(saveResultCommand, "@OrganizationDate"); + saveResultCommand.Parameters.Add(saveResultCommand, "@Status"); + saveResultCommand.Parameters.Add(saveResultCommand, "@OrganizationType"); + saveResultCommand.Parameters.Add(saveResultCommand, "@StatusMessage"); + saveResultCommand.Parameters.Add(saveResultCommand, "@ExtractedName"); + saveResultCommand.Parameters.Add(saveResultCommand, "@ExtractedYear"); + saveResultCommand.Parameters.Add(saveResultCommand, "@ExtractedSeasonNumber"); + saveResultCommand.Parameters.Add(saveResultCommand, "@ExtractedEpisodeNumber"); + saveResultCommand.Parameters.Add(saveResultCommand, "@ExtractedEndingEpisodeNumber"); + saveResultCommand.Parameters.Add(saveResultCommand, "@DuplicatePaths"); + + IDbTransaction transaction = null; + + try + { + transaction = connection.BeginTransaction(); + + var index = 0; + + saveResultCommand.GetParameter(index++).Value = new Guid(result.Id); + saveResultCommand.GetParameter(index++).Value = result.OriginalPath; + saveResultCommand.GetParameter(index++).Value = result.TargetPath; + saveResultCommand.GetParameter(index++).Value = result.FileSize; + saveResultCommand.GetParameter(index++).Value = result.Date; + saveResultCommand.GetParameter(index++).Value = result.Status.ToString(); + saveResultCommand.GetParameter(index++).Value = result.Type.ToString(); + saveResultCommand.GetParameter(index++).Value = result.StatusMessage; + saveResultCommand.GetParameter(index++).Value = result.ExtractedName; + saveResultCommand.GetParameter(index++).Value = result.ExtractedYear; + saveResultCommand.GetParameter(index++).Value = result.ExtractedSeasonNumber; + saveResultCommand.GetParameter(index++).Value = result.ExtractedEpisodeNumber; + saveResultCommand.GetParameter(index++).Value = result.ExtractedEndingEpisodeNumber; + saveResultCommand.GetParameter(index).Value = string.Join("|", result.DuplicatePaths.ToArray()); + + saveResultCommand.Transaction = transaction; + + saveResultCommand.ExecuteNonQuery(); + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save FileOrganizationResult:", e); + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to save FileOrganizationResult:", e); - if (transaction != null) - { - transaction.Rollback(); - } + if (transaction != null) + { + transaction.Rollback(); + } - throw; - } - finally - { - if (transaction != null) - { - transaction.Dispose(); + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + } } - - WriteLock.Release(); } } @@ -164,100 +138,110 @@ namespace MediaBrowser.Server.Implementations.Persistence throw new ArgumentNullException("id"); } - await WriteLock.WaitAsync().ConfigureAwait(false); + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + using (var deleteResultCommand = connection.CreateCommand()) + { + deleteResultCommand.CommandText = "delete from FileOrganizerResults where ResultId = @ResultId"; - IDbTransaction transaction = null; + deleteResultCommand.Parameters.Add(deleteResultCommand, "@ResultId"); - try - { - transaction = _connection.BeginTransaction(); + IDbTransaction transaction = null; - _deleteResultCommand.GetParameter(0).Value = new Guid(id); + try + { + transaction = connection.BeginTransaction(); - _deleteResultCommand.Transaction = transaction; + deleteResultCommand.GetParameter(0).Value = new Guid(id); - _deleteResultCommand.ExecuteNonQuery(); + deleteResultCommand.Transaction = transaction; - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } + deleteResultCommand.ExecuteNonQuery(); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to delete FileOrganizationResult:", e); + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); - } + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to delete FileOrganizationResult:", e); - throw; - } - finally - { - if (transaction != null) - { - transaction.Dispose(); - } + if (transaction != null) + { + transaction.Rollback(); + } - WriteLock.Release(); + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + } + } } } public async Task DeleteAll() { - await WriteLock.WaitAsync().ConfigureAwait(false); + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "delete from FileOrganizerResults"; - IDbTransaction transaction = null; + IDbTransaction transaction = null; - try - { - transaction = _connection.BeginTransaction(); - - _deleteAllCommand.Transaction = transaction; + try + { + transaction = connection.BeginTransaction(); - _deleteAllCommand.ExecuteNonQuery(); + cmd.Transaction = transaction; - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } + cmd.ExecuteNonQuery(); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to delete results", e); + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); - } + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to delete results", e); - throw; - } - finally - { - if (transaction != null) - { - transaction.Dispose(); - } + if (transaction != null) + { + transaction.Rollback(); + } - WriteLock.Release(); + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + } + } } } - + public QueryResult<FileOrganizationResult> GetResults(FileOrganizationResultQuery query) { if (query == null) @@ -265,46 +249,49 @@ namespace MediaBrowser.Server.Implementations.Persistence throw new ArgumentNullException("query"); } - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "SELECT ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults"; - - if (query.StartIndex.HasValue && query.StartIndex.Value > 0) + using (var cmd = connection.CreateCommand()) { - cmd.CommandText += string.Format(" WHERE ResultId NOT IN (SELECT ResultId FROM FileOrganizerResults ORDER BY OrganizationDate desc LIMIT {0})", - query.StartIndex.Value.ToString(_usCulture)); - } + cmd.CommandText = "SELECT ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults"; - cmd.CommandText += " ORDER BY OrganizationDate desc"; + if (query.StartIndex.HasValue && query.StartIndex.Value > 0) + { + cmd.CommandText += string.Format(" WHERE ResultId NOT IN (SELECT ResultId FROM FileOrganizerResults ORDER BY OrganizationDate desc LIMIT {0})", + query.StartIndex.Value.ToString(_usCulture)); + } - if (query.Limit.HasValue) - { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); - } + cmd.CommandText += " ORDER BY OrganizationDate desc"; + + if (query.Limit.HasValue) + { + cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); + } - cmd.CommandText += "; select count (ResultId) from FileOrganizerResults"; + cmd.CommandText += "; select count (ResultId) from FileOrganizerResults"; - var list = new List<FileOrganizationResult>(); - var count = 0; + var list = new List<FileOrganizationResult>(); + var count = 0; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) - { - while (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { - list.Add(GetResult(reader)); + while (reader.Read()) + { + list.Add(GetResult(reader)); + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } } - if (reader.NextResult() && reader.Read()) + return new QueryResult<FileOrganizationResult>() { - count = reader.GetInt32(0); - } + Items = list.ToArray(), + TotalRecordCount = count + }; } - - return new QueryResult<FileOrganizationResult>() - { - Items = list.ToArray(), - TotalRecordCount = count - }; } } @@ -315,24 +302,27 @@ namespace MediaBrowser.Server.Implementations.Persistence throw new ArgumentNullException("id"); } - var guid = new Guid(id); - - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "select ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults where ResultId=@Id"; + var guid = new Guid(id); - cmd.Parameters.Add(cmd, "@Id", DbType.Guid).Value = guid; - - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) + using (var cmd = connection.CreateCommand()) { - if (reader.Read()) + cmd.CommandText = "select ResultId, OriginalPath, TargetPath, FileLength, OrganizationDate, Status, OrganizationType, StatusMessage, ExtractedName, ExtractedYear, ExtractedSeasonNumber, ExtractedEpisodeNumber, ExtractedEndingEpisodeNumber, DuplicatePaths from FileOrganizerResults where ResultId=@Id"; + + cmd.Parameters.Add(cmd, "@Id", DbType.Guid).Value = guid; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) { - return GetResult(reader); + if (reader.Read()) + { + return GetResult(reader); + } } } - } - return null; + return null; + } } public FileOrganizationResult GetResult(IDataReader reader) @@ -414,19 +404,5 @@ namespace MediaBrowser.Server.Implementations.Persistence return result; } - - protected override void CloseConnection() - { - if (_connection != null) - { - if (_connection.IsOpen()) - { - _connection.Close(); - } - - _connection.Dispose(); - _connection = null; - } - } } } diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index af275faee..63dd29e0d 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -1,4 +1,3 @@ -using MediaBrowser.Common.Configuration; using MediaBrowser.Controller.Entities; using MediaBrowser.Controller.Entities.Audio; using MediaBrowser.Controller.Entities.Movies; @@ -16,10 +15,14 @@ using System.Globalization; using System.IO; using System.Linq; using System.Runtime.Serialization; +using System.Text; using System.Threading; using System.Threading.Tasks; +using MediaBrowser.Common.Extensions; using MediaBrowser.Controller.Channels; +using MediaBrowser.Controller.Configuration; using MediaBrowser.Controller.Playlists; +using MediaBrowser.Model.Dto; using MediaBrowser.Model.LiveTv; namespace MediaBrowser.Server.Implementations.Persistence @@ -54,7 +57,7 @@ namespace MediaBrowser.Server.Implementations.Persistence /// <summary> /// The _app paths /// </summary> - private readonly IApplicationPaths _appPaths; + private readonly IServerConfigurationManager _config; /// <summary> /// The _save item command @@ -77,79 +80,110 @@ namespace MediaBrowser.Server.Implementations.Persistence private IDbCommand _deleteAncestorsCommand; private IDbCommand _saveAncestorCommand; + private IDbCommand _deleteUserDataKeysCommand; + private IDbCommand _saveUserDataKeysCommand; + + private IDbCommand _deleteItemValuesCommand; + private IDbCommand _saveItemValuesCommand; + + private IDbCommand _deleteProviderIdsCommand; + private IDbCommand _saveProviderIdsCommand; + + private IDbCommand _deleteImagesCommand; + private IDbCommand _saveImagesCommand; + private IDbCommand _updateInheritedRatingCommand; + private IDbCommand _updateInheritedTagsCommand; - private const int LatestSchemaVersion = 53; + public const int LatestSchemaVersion = 108; /// <summary> /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. /// </summary> - /// <param name="appPaths">The app paths.</param> - /// <param name="jsonSerializer">The json serializer.</param> - /// <param name="logManager">The log manager.</param> - /// <exception cref="System.ArgumentNullException"> - /// appPaths - /// or - /// jsonSerializer - /// </exception> - public SqliteItemRepository(IApplicationPaths appPaths, IJsonSerializer jsonSerializer, ILogManager logManager) - : base(logManager) + public SqliteItemRepository(IServerConfigurationManager config, IJsonSerializer jsonSerializer, ILogManager logManager, IDbConnector connector) + : base(logManager, connector) { - if (appPaths == null) + if (config == null) { - throw new ArgumentNullException("appPaths"); + throw new ArgumentNullException("config"); } if (jsonSerializer == null) { throw new ArgumentNullException("jsonSerializer"); } - _appPaths = appPaths; + _config = config; _jsonSerializer = jsonSerializer; - _criticReviewsPath = Path.Combine(_appPaths.DataPath, "critic-reviews"); + _criticReviewsPath = Path.Combine(_config.ApplicationPaths.DataPath, "critic-reviews"); + DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); } private const string ChaptersTableName = "Chapters2"; + protected override async Task<IDbConnection> CreateConnection(bool isReadOnly = false) + { + var cacheSize = _config.Configuration.SqliteCacheSize; + if (cacheSize <= 0) + { + cacheSize = Math.Min(Environment.ProcessorCount * 50000, 200000); + } + + var connection = await DbConnector.Connect(DbFilePath, false, false, 0 - cacheSize).ConfigureAwait(false); + + connection.RunQueries(new[] + { + "pragma temp_store = memory", + "pragma default_temp_store = memory", + "PRAGMA locking_mode=EXCLUSIVE" + + }, Logger); + + return connection; + } + /// <summary> /// Opens the connection to the database /// </summary> /// <returns>Task.</returns> - public async Task Initialize() + public async Task Initialize(SqliteUserDataRepository userDataRepo) { - var dbFile = Path.Combine(_appPaths.DataPath, "library.db"); - - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); + _connection = await CreateConnection(false).ConfigureAwait(false); var createMediaStreamsTableCommand - = "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, IsCabac BIT NULL, CodecTag TEXT NULL, Comment TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))"; + = "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, TimeBase TEXT NULL, CodecTimeBase TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))"; 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 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)", "create index if not exists idx_AncestorIds2 on AncestorIds(AncestorIdText)", - + + "create table if not exists UserDataKeys (ItemId GUID, UserDataKey TEXT Priority INT, PRIMARY KEY (ItemId, UserDataKey))", + + "create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT, CleanValue TEXT)", + + "create table if not exists ProviderIds (ItemId GUID, Name TEXT, Value TEXT, PRIMARY KEY (ItemId, Name))", + // covering index + "create index if not exists Idx_ProviderIds1 on ProviderIds(ItemId,Name,Value)", + + "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)", + + "drop index if exists idxPeopleItemId", + "create index if not exists idxPeopleItemId1 on People(ItemId,ListOrder)", "create index if not exists idxPeopleName on People(Name)", "create table if not exists "+ChaptersTableName+" (ItemId GUID, ChapterIndex INT, StartPositionTicks BIGINT, Name TEXT, ImagePath TEXT, PRIMARY KEY (ItemId, ChapterIndex))", - "create index if not exists idx_"+ChaptersTableName+" on "+ChaptersTableName+"(ItemId, ChapterIndex)", createMediaStreamsTableCommand, - "create index if not exists idx_mediastreams on mediastreams(ItemId, StreamIndex)", - //pragmas - "pragma temp_store = memory", + "create index if not exists idx_mediastreams1 on mediastreams(ItemId)", - "pragma shrink_memory" }; _connection.RunQueries(queries, Logger); @@ -223,84 +257,95 @@ namespace MediaBrowser.Server.Implementations.Persistence _connection.AddColumn(Logger, "TypedBaseItems", "TrailerTypes", "Text"); _connection.AddColumn(Logger, "TypedBaseItems", "CriticRating", "Float"); _connection.AddColumn(Logger, "TypedBaseItems", "CriticRatingSummary", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "InheritedTags", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "CleanName", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "PresentationUniqueKey", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "SlugName", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "OriginalTitle", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "PrimaryVersionId", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "DateLastMediaAdded", "DATETIME"); + _connection.AddColumn(Logger, "TypedBaseItems", "Album", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "IsVirtualItem", "BIT"); + _connection.AddColumn(Logger, "TypedBaseItems", "SeriesName", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "UserDataKey", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "SeasonName", "Text"); + _connection.AddColumn(Logger, "TypedBaseItems", "SeasonId", "GUID"); + _connection.AddColumn(Logger, "TypedBaseItems", "SeriesId", "GUID"); + _connection.AddColumn(Logger, "TypedBaseItems", "SeriesSortName", "Text"); + + _connection.AddColumn(Logger, "UserDataKeys", "Priority", "INT"); + _connection.AddColumn(Logger, "ItemValues", "CleanValue", "Text"); + + _connection.AddColumn(Logger, ChaptersTableName, "ImageDateModified", "DATETIME"); + + string[] postQueries = + + { + // obsolete + "drop index if exists idx_TypedBaseItems", + "drop index if exists idx_mediastreams", + "drop index if exists idx_"+ChaptersTableName, + "drop index if exists idx_UserDataKeys1", + "drop index if exists idx_UserDataKeys2", + "drop index if exists idx_TypeTopParentId3", + "drop index if exists idx_TypeTopParentId2", + "drop index if exists idx_TypeTopParentId4", + "drop index if exists idx_Type", + "drop index if exists idx_TypeTopParentId", + "drop index if exists idx_GuidType", + "drop index if exists idx_TopParentId", + "drop index if exists idx_TypeTopParentId6", + "drop index if exists idx_ItemValues2", + "drop index if exists Idx_ProviderIds", + "drop index if exists idx_ItemValues3", + "drop index if exists idx_ItemValues4", + "drop index if exists idx_ItemValues5", + + "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_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)", + "create index if not exists idx_GuidTypeIsFolderIsVirtualItem on TypedBaseItems(Guid,Type,IsFolder,IsVirtualItem)", + //"create index if not exists idx_GuidMediaTypeIsFolderIsVirtualItem on TypedBaseItems(Guid,MediaType,IsFolder,IsVirtualItem)", + "create index if not exists idx_CleanNameType on TypedBaseItems(CleanName,Type)", + + // covering index + "create index if not exists idx_TopParentIdGuid on TypedBaseItems(TopParentId,Guid)", + + // live tv programs + "create index if not exists idx_TypeTopParentIdStartDate on TypedBaseItems(Type,TopParentId,StartDate)", + + // covering index for getitemvalues + "create index if not exists idx_TypeTopParentIdGuid on TypedBaseItems(Type,TopParentId,Guid)", + + // used by movie suggestions + "create index if not exists idx_TypeTopParentIdGroup on TypedBaseItems(Type,TopParentId,PresentationUniqueKey)", + "create index if not exists idx_TypeTopParentId5 on TypedBaseItems(TopParentId,IsVirtualItem)", + + // latest items + "create index if not exists idx_TypeTopParentId9 on TypedBaseItems(TopParentId,Type,IsVirtualItem,PresentationUniqueKey,DateCreated)", + "create index if not exists idx_TypeTopParentId8 on TypedBaseItems(TopParentId,IsFolder,IsVirtualItem,PresentationUniqueKey,DateCreated)", + + // resume + "create index if not exists idx_TypeTopParentId7 on TypedBaseItems(TopParentId,MediaType,IsVirtualItem,PresentationUniqueKey)", + + // items by name + "create index if not exists idx_ItemValues6 on ItemValues(ItemId,Type,CleanValue)", + "create index if not exists idx_ItemValues7 on ItemValues(Type,CleanValue,ItemId)", + + // covering index + "create index if not exists idx_UserDataKeys3 on UserDataKeys(ItemId,Priority,UserDataKey)" + }; + + _connection.RunQueries(postQueries, Logger); PrepareStatements(); new MediaStreamColumns(_connection, Logger).AddColumns(); - var chapterDbFile = Path.Combine(_appPaths.DataPath, "chapters.db"); - if (File.Exists(chapterDbFile)) - { - MigrateChapters(chapterDbFile); - } - - var mediaStreamsDbFile = Path.Combine(_appPaths.DataPath, "mediainfo.db"); - if (File.Exists(mediaStreamsDbFile)) - { - MigrateMediaStreams(mediaStreamsDbFile); - } - } - - private void MigrateMediaStreams(string file) - { - try - { - var backupFile = file + ".bak"; - File.Copy(file, backupFile, true); - SqliteExtensions.Attach(_connection, backupFile, "MediaInfoOld"); - - var columns = string.Join(",", _mediaStreamSaveColumns); - - string[] queries = { - "REPLACE INTO mediastreams("+columns+") SELECT "+columns+" FROM MediaInfoOld.mediastreams;" - }; - - _connection.RunQueries(queries, Logger); - } - catch (Exception ex) - { - Logger.ErrorException("Error migrating media info database", ex); - } - finally - { - TryDeleteFile(file); - } - } - - private void MigrateChapters(string file) - { - try - { - var backupFile = file + ".bak"; - File.Copy(file, backupFile, true); - SqliteExtensions.Attach(_connection, backupFile, "ChaptersOld"); - - string[] queries = { - "REPLACE INTO "+ChaptersTableName+"(ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath) SELECT ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath FROM ChaptersOld.Chapters;" - }; - - _connection.RunQueries(queries, Logger); - } - catch (Exception ex) - { - Logger.ErrorException("Error migrating chapter database", ex); - } - finally - { - TryDeleteFile(file); - } - } - - private void TryDeleteFile(string file) - { - try - { - File.Delete(file); - } - catch (Exception ex) - { - Logger.ErrorException("Error deleting file {0}", ex, file); - } + DataExtensions.Attach(_connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb"); + await userDataRepo.Initialize(_connection, WriteLock).ConfigureAwait(false); + //await Vacuum(_connection).ConfigureAwait(false); } private readonly string[] _retriveItemColumns = @@ -355,7 +400,19 @@ namespace MediaBrowser.Server.Implementations.Persistence "Studios", "Tags", "SourceType", - "TrailerTypes" + "TrailerTypes", + "OriginalTitle", + "PrimaryVersionId", + "DateLastMediaAdded", + "Album", + "CriticRating", + "CriticRatingSummary", + "IsVirtualItem", + "SeriesName", + "SeasonName", + "SeasonId", + "SeriesId", + "SeriesSortName" }; private readonly string[] _mediaStreamSaveColumns = @@ -385,9 +442,13 @@ namespace MediaBrowser.Server.Implementations.Persistence "BitDepth", "IsAnamorphic", "RefFrames", - "IsCabac", "CodecTag", - "Comment" + "Comment", + "NalLengthSize", + "IsAvc", + "Title", + "TimeBase", + "CodecTimeBase" }; /// <summary> @@ -400,7 +461,7 @@ namespace MediaBrowser.Server.Implementations.Persistence "guid", "type", "data", - "Path", + "Path", "StartDate", "EndDate", "ChannelId", @@ -459,7 +520,22 @@ namespace MediaBrowser.Server.Implementations.Persistence "SourceType", "TrailerTypes", "CriticRating", - "CriticRatingSummary" + "CriticRatingSummary", + "InheritedTags", + "CleanName", + "PresentationUniqueKey", + "SlugName", + "OriginalTitle", + "PrimaryVersionId", + "DateLastMediaAdded", + "Album", + "IsVirtualItem", + "SeriesName", + "UserDataKey", + "SeasonName", + "SeasonId", + "SeriesId", + "SeriesSortName" }; _saveItemCommand = _connection.CreateCommand(); _saveItemCommand.CommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values ("; @@ -518,6 +594,7 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@StartPositionTicks"); _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@Name"); _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ImagePath"); + _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ImageDateModified"); // MediaStreams _deleteStreamsCommand = _connection.CreateCommand(); @@ -537,8 +614,61 @@ namespace MediaBrowser.Server.Implementations.Persistence _updateInheritedRatingCommand = _connection.CreateCommand(); _updateInheritedRatingCommand.CommandText = "Update TypedBaseItems set InheritedParentalRatingValue=@InheritedParentalRatingValue where Guid=@Guid"; - _updateInheritedRatingCommand.Parameters.Add(_updateInheritedRatingCommand, "@InheritedParentalRatingValue"); _updateInheritedRatingCommand.Parameters.Add(_updateInheritedRatingCommand, "@Guid"); + _updateInheritedRatingCommand.Parameters.Add(_updateInheritedRatingCommand, "@InheritedParentalRatingValue"); + + _updateInheritedTagsCommand = _connection.CreateCommand(); + _updateInheritedTagsCommand.CommandText = "Update TypedBaseItems set InheritedTags=@InheritedTags where Guid=@Guid"; + _updateInheritedTagsCommand.Parameters.Add(_updateInheritedTagsCommand, "@Guid"); + _updateInheritedTagsCommand.Parameters.Add(_updateInheritedTagsCommand, "@InheritedTags"); + + // user data + _deleteUserDataKeysCommand = _connection.CreateCommand(); + _deleteUserDataKeysCommand.CommandText = "delete from UserDataKeys where ItemId=@Id"; + _deleteUserDataKeysCommand.Parameters.Add(_deleteUserDataKeysCommand, "@Id"); + + _saveUserDataKeysCommand = _connection.CreateCommand(); + _saveUserDataKeysCommand.CommandText = "insert into UserDataKeys (ItemId, UserDataKey, Priority) values (@ItemId, @UserDataKey, @Priority)"; + _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@ItemId"); + _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, CleanValue) values (@ItemId, @Type, @Value, @CleanValue)"; + _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@ItemId"); + _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Type"); + _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Value"); + _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@CleanValue"); + + // provider ids + _deleteProviderIdsCommand = _connection.CreateCommand(); + _deleteProviderIdsCommand.CommandText = "delete from ProviderIds where ItemId=@Id"; + _deleteProviderIdsCommand.Parameters.Add(_deleteProviderIdsCommand, "@Id"); + + _saveProviderIdsCommand = _connection.CreateCommand(); + _saveProviderIdsCommand.CommandText = "insert into ProviderIds (ItemId, Name, Value) values (@ItemId, @Name, @Value)"; + _saveProviderIdsCommand.Parameters.Add(_saveProviderIdsCommand, "@ItemId"); + _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> @@ -696,7 +826,15 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = item.DateLastRefreshed; } - _saveItemCommand.GetParameter(index++).Value = item.DateLastSaved; + if (item.DateLastSaved == default(DateTime)) + { + _saveItemCommand.GetParameter(index++).Value = null; + } + else + { + _saveItemCommand.GetParameter(index++).Value = item.DateLastSaved; + } + _saveItemCommand.GetParameter(index++).Value = item.IsInMixedFolder; _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.LockedFields.Select(i => i.ToString()).ToArray()); _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Studios.ToArray()); @@ -712,7 +850,15 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = item.ServiceName; - _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Tags.ToArray()); + if (item.Tags.Count > 0) + { + _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Tags.ToArray()); + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + } + _saveItemCommand.GetParameter(index++).Value = item.IsFolder; _saveItemCommand.GetParameter(index++).Value = item.GetBlockUnratedType().ToString(); @@ -741,7 +887,7 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = item.SourceType.ToString(); var trailer = item as Trailer; - if (trailer != null) + if (trailer != null && trailer.TrailerTypes.Count > 0) { _saveItemCommand.GetParameter(index++).Value = string.Join("|", trailer.TrailerTypes.Select(i => i.ToString()).ToArray()); } @@ -752,7 +898,89 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = item.CriticRating; _saveItemCommand.GetParameter(index++).Value = item.CriticRatingSummary; - + + var inheritedTags = item.GetInheritedTags(); + if (inheritedTags.Count > 0) + { + _saveItemCommand.GetParameter(index++).Value = string.Join("|", inheritedTags.ToArray()); + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + } + + if (string.IsNullOrWhiteSpace(item.Name)) + { + _saveItemCommand.GetParameter(index++).Value = null; + } + else + { + _saveItemCommand.GetParameter(index++).Value = item.Name.RemoveDiacritics(); + } + + _saveItemCommand.GetParameter(index++).Value = item.PresentationUniqueKey; + _saveItemCommand.GetParameter(index++).Value = item.SlugName; + _saveItemCommand.GetParameter(index++).Value = item.OriginalTitle; + + var video = item as Video; + if (video != null) + { + _saveItemCommand.GetParameter(index++).Value = video.PrimaryVersionId; + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + } + + var folder = item as Folder; + if (folder != null && folder.DateLastMediaAdded.HasValue) + { + _saveItemCommand.GetParameter(index++).Value = folder.DateLastMediaAdded.Value; + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + } + + _saveItemCommand.GetParameter(index++).Value = item.Album; + + _saveItemCommand.GetParameter(index++).Value = item.IsVirtualItem || (!item.IsFolder && item.LocationType == LocationType.Virtual); + + var hasSeries = item as IHasSeries; + if (hasSeries != null) + { + _saveItemCommand.GetParameter(index++).Value = hasSeries.FindSeriesName(); + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + } + + _saveItemCommand.GetParameter(index++).Value = item.GetUserDataKeys().FirstOrDefault(); + + var episode = item as Episode; + if (episode != null) + { + _saveItemCommand.GetParameter(index++).Value = episode.FindSeasonName(); + _saveItemCommand.GetParameter(index++).Value = episode.FindSeasonId(); + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + _saveItemCommand.GetParameter(index++).Value = null; + } + + if (hasSeries != null) + { + _saveItemCommand.GetParameter(index++).Value = hasSeries.FindSeriesId(); + _saveItemCommand.GetParameter(index++).Value = hasSeries.FindSeriesSortName(); + } + else + { + _saveItemCommand.GetParameter(index++).Value = null; + _saveItemCommand.GetParameter(index++).Value = null; + } + _saveItemCommand.Transaction = transaction; _saveItemCommand.ExecuteNonQuery(); @@ -761,6 +989,11 @@ namespace MediaBrowser.Server.Implementations.Persistence { UpdateAncestors(item.Id, item.GetAncestorIds().Distinct().ToList(), transaction); } + + 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, GetItemValuesToSave(item), transaction); } transaction.Commit(); @@ -836,7 +1069,7 @@ namespace MediaBrowser.Server.Implementations.Persistence if (type == null) { - Logger.Debug("Unknown type {0}", typeString); + //Logger.Debug("Unknown type {0}", typeString); return null; } @@ -1125,6 +1358,102 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + var index = 51; + + if (!reader.IsDBNull(index)) + { + item.OriginalTitle = reader.GetString(index); + } + index++; + + var video = item as Video; + if (video != null) + { + if (!reader.IsDBNull(index)) + { + video.PrimaryVersionId = reader.GetString(index); + } + } + index++; + + var folder = item as Folder; + if (folder != null && !reader.IsDBNull(index)) + { + folder.DateLastMediaAdded = reader.GetDateTime(index).ToUniversalTime(); + } + index++; + + if (!reader.IsDBNull(index)) + { + item.Album = reader.GetString(index); + } + index++; + + if (!reader.IsDBNull(index)) + { + item.CriticRating = reader.GetFloat(index); + } + index++; + + if (!reader.IsDBNull(index)) + { + item.CriticRatingSummary = reader.GetString(index); + } + index++; + + if (!reader.IsDBNull(index)) + { + item.IsVirtualItem = reader.GetBoolean(index); + } + index++; + + var hasSeries = item as IHasSeries; + if (hasSeries != null) + { + if (!reader.IsDBNull(index)) + { + hasSeries.SeriesName = reader.GetString(index); + } + } + index++; + + var episode = item as Episode; + if (episode != null) + { + if (!reader.IsDBNull(index)) + { + episode.SeasonName = reader.GetString(index); + } + index++; + if (!reader.IsDBNull(index)) + { + episode.SeasonId = reader.GetGuid(index); + } + } + else + { + index++; + } + index++; + + if (hasSeries != null) + { + if (!reader.IsDBNull(index)) + { + hasSeries.SeriesId = reader.GetGuid(index); + } + } + index++; + + if (hasSeries != null) + { + if (!reader.IsDBNull(index)) + { + hasSeries.SeriesSortName = reader.GetString(index); + } + } + index++; + return item; } @@ -1182,10 +1511,11 @@ namespace MediaBrowser.Server.Implementations.Persistence { throw new ArgumentNullException("id"); } + var list = new List<ChapterInfo>(); using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"; + cmd.CommandText = "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"; cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = id; @@ -1193,10 +1523,12 @@ namespace MediaBrowser.Server.Implementations.Persistence { while (reader.Read()) { - yield return GetChapter(reader); + list.Add(GetChapter(reader)); } } } + + return list; } /// <summary> @@ -1216,7 +1548,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex"; + cmd.CommandText = "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex"; cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = id; cmd.Parameters.Add(cmd, "@ChapterIndex", DbType.Int32).Value = index; @@ -1254,6 +1586,11 @@ namespace MediaBrowser.Server.Implementations.Persistence chapter.ImagePath = reader.GetString(2); } + if (!reader.IsDBNull(3)) + { + chapter.ImageDateModified = reader.GetDateTime(3).ToUniversalTime(); + } + return chapter; } @@ -1271,7 +1608,7 @@ namespace MediaBrowser.Server.Implementations.Persistence /// or /// cancellationToken /// </exception> - public async Task SaveChapters(Guid id, IEnumerable<ChapterInfo> chapters, CancellationToken cancellationToken) + public async Task SaveChapters(Guid id, List<ChapterInfo> chapters, CancellationToken cancellationToken) { CheckDisposed(); @@ -1313,6 +1650,7 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveChapterCommand.GetParameter(2).Value = chapter.StartPositionTicks; _saveChapterCommand.GetParameter(3).Value = chapter.Name; _saveChapterCommand.GetParameter(4).Value = chapter.ImagePath; + _saveChapterCommand.GetParameter(5).Value = chapter.ImageDateModified; _saveChapterCommand.Transaction = transaction; @@ -1368,37 +1706,167 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - public IEnumerable<BaseItem> GetItemsOfType(Type type) + private bool EnableJoinUserData(InternalItemsQuery query) { - if (type == null) + if (query.User == null) { - throw new ArgumentNullException("type"); + return false; } - CheckDisposed(); + if (query.SimilarTo != null && query.User != null) + { + return true; + } - using (var cmd = _connection.CreateCommand()) + if (query.SortBy != null && query.SortBy.Length > 0) + { + if (query.SortBy.Contains(ItemSortBy.IsFavoriteOrLiked, StringComparer.OrdinalIgnoreCase)) + { + return true; + } + if (query.SortBy.Contains(ItemSortBy.IsPlayed, StringComparer.OrdinalIgnoreCase)) + { + return true; + } + if (query.SortBy.Contains(ItemSortBy.IsUnplayed, StringComparer.OrdinalIgnoreCase)) + { + return true; + } + if (query.SortBy.Contains(ItemSortBy.PlayCount, StringComparer.OrdinalIgnoreCase)) + { + return true; + } + if (query.SortBy.Contains(ItemSortBy.DatePlayed, StringComparer.OrdinalIgnoreCase)) + { + return true; + } + } + + if (query.IsFavoriteOrLiked.HasValue) { - cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where type = @type"; + return true; + } - cmd.Parameters.Add(cmd, "@type", DbType.String).Value = type.FullName; + if (query.IsFavorite.HasValue) + { + return true; + } - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) - { - while (reader.Read()) - { - var item = GetItem(reader); + if (query.IsResumable.HasValue) + { + return true; + } - if (item != null) - { - yield return item; - } - } - } + if (query.IsPlayed.HasValue) + { + return true; + } + + if (query.IsLiked.HasValue) + { + return true; } + + return false; } - public IEnumerable<BaseItem> GetItemList(InternalItemsQuery query) + private string[] GetFinalColumnsToSelect(InternalItemsQuery query, string[] startColumns, IDbCommand cmd) + { + var list = startColumns.ToList(); + + if (EnableJoinUserData(query)) + { + list.Add("UserDataDb.UserData.UserId"); + list.Add("UserDataDb.UserData.lastPlayedDate"); + list.Add("UserDataDb.UserData.playbackPositionTicks"); + list.Add("UserDataDb.UserData.playcount"); + list.Add("UserDataDb.UserData.isFavorite"); + list.Add("UserDataDb.UserData.played"); + list.Add("UserDataDb.UserData.rating"); + } + + if (query.SimilarTo != null) + { + var item = query.SimilarTo; + + var builder = new StringBuilder(); + 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(CleanValue) from ItemValues where ItemId=Guid and Type=2 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=2)) * 10)"); + + //// tags + builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=4 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=4)) * 10)"); + + builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=5 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=5)) * 10)"); + + builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=3 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=3)) * 3)"); + + //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(") 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 ?? 0; + cmd.Parameters.Add(cmd, "@SimilarItemId", DbType.Guid).Value = item.Id; + + var excludeIds = query.ExcludeItemIds.ToList(); + excludeIds.Add(item.Id.ToString("N")); + query.ExcludeItemIds = excludeIds.ToArray(); + + query.ExcludeProviderIds = item.ProviderIds; + } + + return list.ToArray(); + } + + private string GetJoinUserDataText(InternalItemsQuery query) + { + if (!EnableJoinUserData(query)) + { + return string.Empty; + } + + if (_config.Configuration.SchemaVersion >= 96) + { + return " left join UserDataDb.UserData on UserDataKey=UserDataDb.UserData.Key And (UserId=@UserId)"; + } + + return " left join UserDataDb.UserData on (select UserDataKey from UserDataKeys where ItemId=Guid order by Priority LIMIT 1)=UserDataDb.UserData.Key And (UserId=@UserId)"; + } + + private string GetGroupBy(InternalItemsQuery query) + { + var groups = new List<string>(); + + if (EnableGroupByPresentationUniqueKey(query)) + { + groups.Add("PresentationUniqueKey"); + } + + if (groups.Count > 0) + { + return " Group by " + string.Join(",", groups.ToArray()); + } + + return string.Empty; + } + + private string GetFromText(string alias = "A") + { + return " from TypedBaseItems " + alias; + } + + public List<BaseItem> GetItemList(InternalItemsQuery query) { if (query == null) { @@ -1407,11 +1875,27 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); + var now = DateTime.UtcNow; + + var list = new List<BaseItem>(); + + // Hack for right now since we currently don't support filtering out these duplicates within a query + if (query.Limit.HasValue && query.EnableGroupByMetadataKey) + { + query.Limit = query.Limit.Value + 4; + } + using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + GetFromText(); + cmd.CommandText += GetJoinUserDataText(query); + + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } - var whereClauses = GetWhereClauses(query, cmd, true); + var whereClauses = GetWhereClauses(query, cmd); var whereText = whereClauses.Count == 0 ? string.Empty : @@ -1419,27 +1903,115 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; + cmd.CommandText += GetGroupBy(query); + cmd.CommandText += GetOrderByText(query); - if (query.Limit.HasValue) + if (query.Limit.HasValue || query.StartIndex.HasValue) { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); - } + var offset = query.StartIndex ?? 0; - //Logger.Debug(cmd.CommandText); + if (query.Limit.HasValue || offset > 0) + { + cmd.CommandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + } + + if (offset > 0) + { + cmd.CommandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); + } + } using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { + LogQueryTime("GetItemList", cmd, now); + while (reader.Read()) { var item = GetItem(reader); if (item != null) { - yield return item; + list.Add(item); } } } } + + // Hack for right now since we currently don't support filtering out these duplicates within a query + if (query.EnableGroupByMetadataKey) + { + var limit = query.Limit ?? int.MaxValue; + limit -= 4; + var newList = new List<BaseItem>(); + + foreach (var item in list) + { + AddItem(newList, item); + + if (newList.Count >= limit) + { + break; + } + } + + list = newList; + } + + return list; + } + + private void AddItem(List<BaseItem> items, BaseItem newItem) + { + var providerIds = newItem.ProviderIds.ToList(); + + for (var i = 0; i < items.Count; i++) + { + var item = items[i]; + + foreach (var providerId in providerIds) + { + if (providerId.Key == MetadataProviders.TmdbCollection.ToString()) + { + continue; + } + if (item.GetProviderId(providerId.Key) == providerId.Value) + { + if (newItem.SourceType == SourceType.Library) + { + items[i] = newItem; + } + return; + } + } + } + + items.Add(newItem); + } + + private void LogQueryTime(string methodName, IDbCommand cmd, DateTime startDate) + { + var elapsed = (DateTime.UtcNow - startDate).TotalMilliseconds; + + var slowThreshold = 1000; + +#if DEBUG + slowThreshold = 50; +#endif + + if (elapsed >= slowThreshold) + { + Logger.Debug("{2} query time (slow): {0}ms. Query: {1}", + Convert.ToInt32(elapsed), + cmd.CommandText, + methodName); + } + else + { + //Logger.Debug("{2} query time: {0}ms. Query: {1}", + // Convert.ToInt32(elapsed), + // cmd.CommandText, + // methodName); + } } public QueryResult<BaseItem> GetItems(InternalItemsQuery query) @@ -1451,52 +2023,109 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); + if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0)) + { + var list = GetItemList(query); + return new QueryResult<BaseItem> + { + Items = list.ToArray(), + TotalRecordCount = list.Count + }; + } + + var now = DateTime.UtcNow; + using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + GetFromText(); + cmd.CommandText += GetJoinUserDataText(query); - var whereClauses = GetWhereClauses(query, cmd, false); + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } + + var whereClauses = GetWhereClauses(query, cmd); var whereTextWithoutPaging = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray()); - whereClauses = GetWhereClauses(query, cmd, true); - var whereText = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray()); cmd.CommandText += whereText; + cmd.CommandText += GetGroupBy(query); + cmd.CommandText += GetOrderByText(query); - if (query.Limit.HasValue) + if (query.Limit.HasValue || query.StartIndex.HasValue) { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); + var offset = query.StartIndex ?? 0; + + if (query.Limit.HasValue || offset > 0) + { + cmd.CommandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + } + + if (offset > 0) + { + cmd.CommandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); + } } - cmd.CommandText += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging; + cmd.CommandText += ";"; - //Logger.Debug(cmd.CommandText); + var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; + + if (isReturningZeroItems) + { + cmd.CommandText = ""; + } + + if (EnableGroupByPresentationUniqueKey(query)) + { + cmd.CommandText += " select count (distinct PresentationUniqueKey)" + GetFromText(); + } + else + { + cmd.CommandText += " select count (guid)" + GetFromText(); + } + + cmd.CommandText += GetJoinUserDataText(query); + cmd.CommandText += whereTextWithoutPaging; var list = new List<BaseItem>(); var count = 0; using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { - while (reader.Read()) + LogQueryTime("GetItems", cmd, now); + + if (isReturningZeroItems) { - var item = GetItem(reader); - if (item != null) + if (reader.Read()) { - list.Add(item); + count = reader.GetInt32(0); } } - - if (reader.NextResult() && reader.Read()) + else { - count = reader.GetInt32(0); + while (reader.Read()) + { + var item = GetItem(reader); + if (item != null) + { + list.Add(item); + } + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } } } @@ -1510,33 +2139,109 @@ namespace MediaBrowser.Server.Implementations.Persistence private string GetOrderByText(InternalItemsQuery query) { + if (query.SimilarTo != null) + { + if (query.SortBy == null || query.SortBy.Length == 0) + { + if (query.User != null) + { + query.SortBy = new[] { ItemSortBy.IsPlayed, "SimilarityScore", ItemSortBy.Random }; + } + else + { + query.SortBy = new[] { "SimilarityScore", ItemSortBy.Random }; + } + query.SortOrder = SortOrder.Descending; + } + } + if (query.SortBy == null || query.SortBy.Length == 0) { return string.Empty; } - var sortOrder = query.SortOrder == SortOrder.Descending ? "DESC" : "ASC"; + var isAscending = query.SortOrder != SortOrder.Descending; - return " ORDER BY " + string.Join(",", query.SortBy.Select(i => MapOrderByField(i) + " " + sortOrder).ToArray()); + return " ORDER BY " + string.Join(",", query.SortBy.Select(i => + { + var columnMap = MapOrderByField(i, query); + var columnAscending = isAscending; + if (columnMap.Item2) + { + columnAscending = !columnAscending; + } + + var sortOrder = columnAscending ? "ASC" : "DESC"; + + return columnMap.Item1 + " " + sortOrder; + }).ToArray()); } - private string MapOrderByField(string name) + private Tuple<string, bool> MapOrderByField(string name, InternalItemsQuery query) { if (string.Equals(name, ItemSortBy.AirTime, StringComparison.OrdinalIgnoreCase)) { // TODO - return "SortName"; + return new Tuple<string, bool>("SortName", false); } if (string.Equals(name, ItemSortBy.Runtime, StringComparison.OrdinalIgnoreCase)) { - return "RuntimeTicks"; + return new Tuple<string, bool>("RuntimeTicks", false); } if (string.Equals(name, ItemSortBy.Random, StringComparison.OrdinalIgnoreCase)) { - return "RANDOM()"; + return new Tuple<string, bool>("RANDOM()", false); + } + if (string.Equals(name, ItemSortBy.DatePlayed, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("LastPlayedDate", false); + } + if (string.Equals(name, ItemSortBy.PlayCount, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("PlayCount", false); + } + if (string.Equals(name, ItemSortBy.IsFavoriteOrLiked, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("IsFavorite", true); + } + if (string.Equals(name, ItemSortBy.IsFolder, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("IsFolder", true); + } + if (string.Equals(name, ItemSortBy.IsPlayed, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("played", true); + } + if (string.Equals(name, ItemSortBy.IsUnplayed, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("played", false); + } + if (string.Equals(name, ItemSortBy.DateLastContentAdded, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("DateLastMediaAdded", false); + } + if (string.Equals(name, ItemSortBy.Artist, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("(select CleanValue 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 CleanValue from itemvalues where ItemId=Guid and Type=1 LIMIT 1)", false); + } + if (string.Equals(name, ItemSortBy.OfficialRating, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("ParentalRatingValue", false); + } + if (string.Equals(name, ItemSortBy.Studio, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("(select CleanValue from itemvalues where ItemId=Guid and Type=3 LIMIT 1)", false); + } + if (string.Equals(name, ItemSortBy.SeriesDatePlayed, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText(query) + " where B.Guid in (Select ItemId from AncestorIds where AncestorId in (select guid from typedbaseitems c where C.Type = 'MediaBrowser.Controller.Entities.TV.Series' And C.Guid in (Select AncestorId from AncestorIds where ItemId=A.Guid))))", false); } - return name; + return new Tuple<string, bool>(name, false); } public List<Guid> GetItemIdsList(InternalItemsQuery query) @@ -1548,11 +2253,19 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); + var now = DateTime.UtcNow; + using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select guid from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + GetFromText(); + cmd.CommandText += GetJoinUserDataText(query); + + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } - var whereClauses = GetWhereClauses(query, cmd, true); + var whereClauses = GetWhereClauses(query, cmd); var whereText = whereClauses.Count == 0 ? string.Empty : @@ -1560,19 +2273,31 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; + cmd.CommandText += GetGroupBy(query); + cmd.CommandText += GetOrderByText(query); - if (query.Limit.HasValue) + if (query.Limit.HasValue || query.StartIndex.HasValue) { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); + var offset = query.StartIndex ?? 0; + + if (query.Limit.HasValue || offset > 0) + { + cmd.CommandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + } + + if (offset > 0) + { + cmd.CommandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); + } } var list = new List<Guid>(); - //Logger.Debug(cmd.CommandText); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) { + LogQueryTime("GetItemIdsList", cmd, now); + while (reader.Read()) { list.Add(reader.GetGuid(0)); @@ -1596,25 +2321,35 @@ namespace MediaBrowser.Server.Implementations.Persistence { cmd.CommandText = "select guid,path from TypedBaseItems"; - var whereClauses = GetWhereClauses(query, cmd, false); + var whereClauses = GetWhereClauses(query, cmd); var whereTextWithoutPaging = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray()); - whereClauses = GetWhereClauses(query, cmd, true); - var whereText = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray()); cmd.CommandText += whereText; + cmd.CommandText += GetGroupBy(query); + cmd.CommandText += GetOrderByText(query); - if (query.Limit.HasValue) + if (query.Limit.HasValue || query.StartIndex.HasValue) { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); + var offset = query.StartIndex ?? 0; + + if (query.Limit.HasValue || offset > 0) + { + cmd.CommandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + } + + if (offset > 0) + { + cmd.CommandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); + } } cmd.CommandText += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging; @@ -1661,17 +2396,29 @@ namespace MediaBrowser.Server.Implementations.Persistence CheckDisposed(); - using (var cmd = _connection.CreateCommand()) + if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0)) { - cmd.CommandText = "select guid from TypedBaseItems"; + var list = GetItemIdsList(query); + return new QueryResult<Guid> + { + Items = list.ToArray(), + TotalRecordCount = list.Count + }; + } - var whereClauses = GetWhereClauses(query, cmd, false); + var now = DateTime.UtcNow; - var whereTextWithoutPaging = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + GetFromText(); + + var whereClauses = GetWhereClauses(query, cmd); + cmd.CommandText += GetJoinUserDataText(query); - whereClauses = GetWhereClauses(query, cmd, true); + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } var whereText = whereClauses.Count == 0 ? string.Empty : @@ -1679,22 +2426,44 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; + cmd.CommandText += GetGroupBy(query); + cmd.CommandText += GetOrderByText(query); - if (query.Limit.HasValue) + if (query.Limit.HasValue || query.StartIndex.HasValue) { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); + var offset = query.StartIndex ?? 0; + + if (query.Limit.HasValue || offset > 0) + { + cmd.CommandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + } + + if (offset > 0) + { + cmd.CommandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); + } } - cmd.CommandText += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging; + if (EnableGroupByPresentationUniqueKey(query)) + { + cmd.CommandText += "; select count (distinct PresentationUniqueKey)" + GetFromText(); + } + else + { + cmd.CommandText += "; select count (guid)" + GetFromText(); + } + + cmd.CommandText += GetJoinUserDataText(query); + cmd.CommandText += whereText; var list = new List<Guid>(); var count = 0; - //Logger.Debug(cmd.CommandText); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { + LogQueryTime("GetItemIds", cmd, now); + while (reader.Read()) { list.Add(reader.GetGuid(0)); @@ -1714,10 +2483,14 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - private List<string> GetWhereClauses(InternalItemsQuery query, IDbCommand cmd, bool addPaging) + private List<string> GetWhereClauses(InternalItemsQuery query, IDbCommand cmd, string paramSuffix = "") { var whereClauses = new List<string>(); + if (EnableJoinUserData(query)) + { + //whereClauses.Add("(UserId is null or UserId=@UserId)"); + } if (query.IsCurrentSchema.HasValue) { if (query.IsCurrentSchema.Value) @@ -1747,7 +2520,24 @@ namespace MediaBrowser.Server.Implementations.Persistence } if (query.IsMovie.HasValue) { - whereClauses.Add("IsMovie=@IsMovie"); + var alternateTypes = new List<string>(); + if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Movie).Name)) + { + alternateTypes.Add(typeof(Movie).FullName); + } + if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Trailer).Name)) + { + alternateTypes.Add(typeof(Trailer).FullName); + } + + if (alternateTypes.Count == 0) + { + whereClauses.Add("IsMovie=@IsMovie"); + } + else + { + whereClauses.Add("(IsMovie is null OR IsMovie=@IsMovie)"); + } cmd.Parameters.Add(cmd, "@IsMovie", DbType.Boolean).Value = query.IsMovie; } if (query.IsKids.HasValue) @@ -1769,8 +2559,8 @@ namespace MediaBrowser.Server.Implementations.Persistence var includeTypes = query.IncludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray(); if (includeTypes.Length == 1) { - whereClauses.Add("type=@type"); - cmd.Parameters.Add(cmd, "@type", DbType.String).Value = includeTypes[0]; + whereClauses.Add("type=@type" + paramSuffix); + cmd.Parameters.Add(cmd, "@type" + paramSuffix, DbType.String).Value = includeTypes[0]; } else if (includeTypes.Length > 1) { @@ -1813,6 +2603,12 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.Parameters.Add(cmd, "@Path", DbType.String).Value = query.Path; } + if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey)) + { + whereClauses.Add("PresentationUniqueKey=@PresentationUniqueKey"); + cmd.Parameters.Add(cmd, "@PresentationUniqueKey", DbType.String).Value = query.PresentationUniqueKey; + } + if (query.MinCommunityRating.HasValue) { whereClauses.Add("CommunityRating>=@MinCommunityRating"); @@ -1837,11 +2633,21 @@ namespace MediaBrowser.Server.Implementations.Persistence // cmd.Parameters.Add(cmd, "@MaxPlayers", DbType.Int32).Value = query.MaxPlayers.Value; //} + if (query.IndexNumber.HasValue) + { + whereClauses.Add("IndexNumber=@IndexNumber"); + cmd.Parameters.Add(cmd, "@IndexNumber", DbType.Int32).Value = query.IndexNumber.Value; + } if (query.ParentIndexNumber.HasValue) { - whereClauses.Add("ParentIndexNumber=@MinEndDate"); + whereClauses.Add("ParentIndexNumber=@ParentIndexNumber"); cmd.Parameters.Add(cmd, "@ParentIndexNumber", DbType.Int32).Value = query.ParentIndexNumber.Value; } + if (query.ParentIndexNumberNotEquals.HasValue) + { + whereClauses.Add("(ParentIndexNumber<>@ParentIndexNumberNotEquals or ParentIndexNumber is null)"); + cmd.Parameters.Add(cmd, "@ParentIndexNumberNotEquals", DbType.Int32).Value = query.ParentIndexNumberNotEquals.Value; + } if (query.MinEndDate.HasValue) { whereClauses.Add("EndDate>=@MinEndDate"); @@ -1913,20 +2719,6 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add(clause); } - if (query.ExcludeTrailerTypes.Length > 0) - { - var clauses = new List<string>(); - var index = 0; - foreach (var type in query.ExcludeTrailerTypes) - { - clauses.Add("TrailerTypes not like @TrailerTypes" + index); - cmd.Parameters.Add(cmd, "@TrailerTypes" + index, DbType.String).Value = "%" + type + "%"; - index++; - } - var clause = "(" + string.Join(" AND ", clauses.ToArray()) + ")"; - whereClauses.Add(clause); - } - if (query.IsAiring.HasValue) { if (query.IsAiring.Value) @@ -1944,16 +2736,176 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + if (query.PersonIds.Length > 0) + { + // Todo: improve without having to do this + query.Person = query.PersonIds.Select(i => RetrieveItem(new Guid(i))).Where(i => i != null).Select(i => i.Name).FirstOrDefault(); + } + if (!string.IsNullOrWhiteSpace(query.Person)) { whereClauses.Add("Guid in (select ItemId from People where Name=@PersonName)"); cmd.Parameters.Add(cmd, "@PersonName", DbType.String).Value = query.Person; } + if (!string.IsNullOrWhiteSpace(query.SlugName)) + { + whereClauses.Add("SlugName=@SlugName"); + cmd.Parameters.Add(cmd, "@SlugName", DbType.String).Value = query.SlugName; + } + + if (!string.IsNullOrWhiteSpace(query.MinSortName)) + { + whereClauses.Add("SortName>=@MinSortName"); + cmd.Parameters.Add(cmd, "@MinSortName", DbType.String).Value = query.MinSortName; + } + + if (!string.IsNullOrWhiteSpace(query.Name)) + { + whereClauses.Add("CleanName=@Name"); + cmd.Parameters.Add(cmd, "@Name", DbType.String).Value = query.Name.RemoveDiacritics(); + } + if (!string.IsNullOrWhiteSpace(query.NameContains)) { - whereClauses.Add("Name like @NameContains"); - cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%" + query.NameContains + "%"; + whereClauses.Add("CleanName like @NameContains"); + cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%" + query.NameContains.RemoveDiacritics() + "%"; + } + if (!string.IsNullOrWhiteSpace(query.NameStartsWith)) + { + whereClauses.Add("SortName like @NameStartsWith"); + cmd.Parameters.Add(cmd, "@NameStartsWith", DbType.String).Value = query.NameStartsWith + "%"; + } + if (!string.IsNullOrWhiteSpace(query.NameStartsWithOrGreater)) + { + whereClauses.Add("SortName >= @NameStartsWithOrGreater"); + // lowercase this because SortName is stored as lowercase + cmd.Parameters.Add(cmd, "@NameStartsWithOrGreater", DbType.String).Value = query.NameStartsWithOrGreater.ToLower(); + } + if (!string.IsNullOrWhiteSpace(query.NameLessThan)) + { + whereClauses.Add("SortName < @NameLessThan"); + // lowercase this because SortName is stored as lowercase + 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) + { + whereClauses.Add("rating>=@UserRating"); + cmd.Parameters.Add(cmd, "@UserRating", DbType.Double).Value = UserItemData.MinLikeValue; + } + else + { + whereClauses.Add("(rating is null or rating<@UserRating)"); + cmd.Parameters.Add(cmd, "@UserRating", DbType.Double).Value = UserItemData.MinLikeValue; + } + } + + if (query.IsFavoriteOrLiked.HasValue) + { + if (query.IsFavoriteOrLiked.Value) + { + whereClauses.Add("IsFavorite=@IsFavoriteOrLiked"); + } + else + { + whereClauses.Add("(IsFavorite is null or IsFavorite=@IsFavoriteOrLiked)"); + } + cmd.Parameters.Add(cmd, "@IsFavoriteOrLiked", DbType.Boolean).Value = query.IsFavoriteOrLiked.Value; + } + + if (query.IsFavorite.HasValue) + { + if (query.IsFavorite.Value) + { + whereClauses.Add("IsFavorite=@IsFavorite"); + } + else + { + whereClauses.Add("(IsFavorite is null or IsFavorite=@IsFavorite)"); + } + cmd.Parameters.Add(cmd, "@IsFavorite", DbType.Boolean).Value = query.IsFavorite.Value; + } + + if (EnableJoinUserData(query)) + { + if (query.IsPlayed.HasValue) + { + if (query.IsPlayed.Value) + { + whereClauses.Add("(played=@IsPlayed)"); + } + else + { + whereClauses.Add("(played is null or played=@IsPlayed)"); + } + cmd.Parameters.Add(cmd, "@IsPlayed", DbType.Boolean).Value = query.IsPlayed.Value; + } + } + + if (query.IsResumable.HasValue) + { + if (query.IsResumable.Value) + { + whereClauses.Add("playbackPositionTicks > 0"); + } + else + { + whereClauses.Add("(playbackPositionTicks is null or playbackPositionTicks = 0)"); + } + } + + 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 CleanValue from itemvalues where ItemId=Guid and Type <= 1)"); + cmd.Parameters.Add(cmd, "@ArtistName" + index, DbType.String).Value = artist.RemoveDiacritics(); + index++; + } + var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); + } + + if (query.ExcludeArtistIds.Length > 0) + { + var clauses = new List<string>(); + var index = 0; + foreach (var artistId in query.ExcludeArtistIds) + { + var artistItem = RetrieveItem(new Guid(artistId)); + if (artistItem != null) + { + clauses.Add("@ExcludeArtistName" + index + " not in (select CleanValue from itemvalues where ItemId=Guid and Type <= 1)"); + cmd.Parameters.Add(cmd, "@ExcludeArtistName" + index, DbType.String).Value = artistItem.Name.RemoveDiacritics(); + index++; + } + } + var clause = "(" + string.Join(" AND ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); + } + + if (query.GenreIds.Length > 0) + { + // Todo: improve without having to do this + query.Genres = query.GenreIds.Select(i => RetrieveItem(new Guid(i))).Where(i => i != null).Select(i => i.Name).ToArray(); } if (query.Genres.Length > 0) @@ -1962,8 +2914,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 CleanValue from itemvalues where ItemId=Guid and Type=2)"); + cmd.Parameters.Add(cmd, "@Genre" + index, DbType.String).Value = item.RemoveDiacritics(); index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -1976,22 +2928,56 @@ 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 CleanValue from itemvalues where ItemId=Guid and Type=4)"); + cmd.Parameters.Add(cmd, "@Tag" + index, DbType.String).Value = item.RemoveDiacritics(); index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; whereClauses.Add(clause); } + if (query.StudioIds.Length > 0) + { + // Todo: improve without having to do this + query.Studios = query.StudioIds.Select(i => RetrieveItem(new Guid(i))).Where(i => i != null).Select(i => i.Name).ToArray(); + } + if (query.Studios.Length > 0) { var clauses = new List<string>(); 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 CleanValue from itemvalues where ItemId=Guid and Type=3)"); + cmd.Parameters.Add(cmd, "@Studio" + index, DbType.String).Value = item.RemoveDiacritics(); + 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 CleanValue from itemvalues where ItemId=Guid and Type=5)"); + cmd.Parameters.Add(cmd, "@Keyword" + index, DbType.String).Value = item.RemoveDiacritics(); + index++; + } + var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); + } + + if (query.OfficialRatings.Length > 0) + { + var clauses = new List<string>(); + var index = 0; + foreach (var item in query.OfficialRatings) + { + clauses.Add("OfficialRating=@OfficialRating" + index); + cmd.Parameters.Add(cmd, "@OfficialRating" + index, DbType.String).Value = item; index++; } var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; @@ -2056,8 +3042,15 @@ namespace MediaBrowser.Server.Implementations.Persistence if (query.LocationTypes.Length == 1) { - whereClauses.Add("LocationType=@LocationType"); - cmd.Parameters.Add(cmd, "@LocationType", DbType.String).Value = query.LocationTypes[0].ToString(); + if (query.LocationTypes[0] == LocationType.Virtual && _config.Configuration.SchemaVersion >= 90) + { + query.IsVirtualItem = true; + } + else + { + whereClauses.Add("LocationType=@LocationType"); + cmd.Parameters.Add(cmd, "@LocationType", DbType.String).Value = query.LocationTypes[0].ToString(); + } } else if (query.LocationTypes.Length > 1) { @@ -2067,8 +3060,15 @@ namespace MediaBrowser.Server.Implementations.Persistence } if (query.ExcludeLocationTypes.Length == 1) { - whereClauses.Add("LocationType<>@ExcludeLocationTypes"); - cmd.Parameters.Add(cmd, "@ExcludeLocationTypes", DbType.String).Value = query.ExcludeLocationTypes[0].ToString(); + if (query.ExcludeLocationTypes[0] == LocationType.Virtual && _config.Configuration.SchemaVersion >= 90) + { + query.IsVirtualItem = false; + } + else + { + whereClauses.Add("LocationType<>@ExcludeLocationTypes"); + cmd.Parameters.Add(cmd, "@ExcludeLocationTypes", DbType.String).Value = query.ExcludeLocationTypes[0].ToString(); + } } else if (query.ExcludeLocationTypes.Length > 1) { @@ -2076,10 +3076,55 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add("LocationType not in (" + val + ")"); } + if (query.IsVirtualItem.HasValue) + { + if (_config.Configuration.SchemaVersion >= 90) + { + whereClauses.Add("IsVirtualItem=@IsVirtualItem"); + cmd.Parameters.Add(cmd, "@IsVirtualItem", DbType.Boolean).Value = query.IsVirtualItem.Value; + } + else if (!query.IsVirtualItem.Value) + { + whereClauses.Add("LocationType<>'Virtual'"); + } + } + if (query.IsUnaired.HasValue) + { + if (query.IsUnaired.Value) + { + whereClauses.Add("PremiereDate >= DATETIME('now')"); + } + else + { + whereClauses.Add("PremiereDate < DATETIME('now')"); + } + } + if (query.IsMissing.HasValue && _config.Configuration.SchemaVersion >= 90) + { + if (query.IsMissing.Value) + { + whereClauses.Add("(IsVirtualItem=1 AND PremiereDate < DATETIME('now'))"); + } + else + { + whereClauses.Add("(IsVirtualItem=0 OR PremiereDate >= DATETIME('now'))"); + } + } + if (query.IsVirtualUnaired.HasValue && _config.Configuration.SchemaVersion >= 90) + { + if (query.IsVirtualUnaired.Value) + { + whereClauses.Add("(IsVirtualItem=1 AND PremiereDate >= DATETIME('now'))"); + } + else + { + whereClauses.Add("(IsVirtualItem=0 OR PremiereDate < DATETIME('now'))"); + } + } if (query.MediaTypes.Length == 1) { whereClauses.Add("MediaType=@MediaTypes"); - cmd.Parameters.Add(cmd, "@MediaTypes", DbType.String).Value = query.MediaTypes[0].ToString(); + cmd.Parameters.Add(cmd, "@MediaTypes", DbType.String).Value = query.MediaTypes[0]; } if (query.MediaTypes.Length > 1) { @@ -2087,8 +3132,96 @@ namespace MediaBrowser.Server.Implementations.Persistence whereClauses.Add("MediaType in (" + val + ")"); } + if (query.ItemIds.Length > 0) + { + var excludeIds = new List<string>(); - var enableItemsByName = query.IncludeItemsByName ?? query.IncludeItemTypes.Length > 0; + var index = 0; + foreach (var id in query.ItemIds) + { + excludeIds.Add("Guid = @IncludeId" + index); + cmd.Parameters.Add(cmd, "@IncludeId" + index, DbType.Guid).Value = new Guid(id); + index++; + } + + whereClauses.Add(string.Join(" OR ", excludeIds.ToArray())); + } + if (query.ExcludeItemIds.Length > 0) + { + var excludeIds = new List<string>(); + + var index = 0; + foreach (var id in query.ExcludeItemIds) + { + excludeIds.Add("Guid <> @ExcludeId" + index); + cmd.Parameters.Add(cmd, "@ExcludeId" + index, DbType.Guid).Value = new Guid(id); + index++; + } + + whereClauses.Add(string.Join(" AND ", excludeIds.ToArray())); + } + + if (query.ExcludeProviderIds.Count > 0) + { + var excludeIds = new List<string>(); + + 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 = '" + pair.Key + "'), '') <> " + paramName + ")"); + cmd.Parameters.Add(cmd, paramName, DbType.String).Value = pair.Value; + index++; + } + + whereClauses.Add(string.Join(" AND ", excludeIds.ToArray())); + } + + if (query.HasImdbId.HasValue) + { + var fn = query.HasImdbId.Value ? "<>" : "="; + whereClauses.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = 'Imdb'), '') " + fn + " '')"); + } + + if (query.HasTmdbId.HasValue) + { + var fn = query.HasTmdbId.Value ? "<>" : "="; + whereClauses.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = 'Tmdb'), '') " + fn + " '')"); + } + + if (query.HasTvdbId.HasValue) + { + var fn = query.HasTvdbId.Value ? "<>" : "="; + whereClauses.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = 'Tvdb'), '') " + fn + " '')"); + } + + if (query.AlbumNames.Length > 0) + { + var clause = "("; + + var index = 0; + foreach (var name in query.AlbumNames) + { + if (index > 0) + { + clause += " OR "; + } + clause += "Album=@AlbumName" + index; + cmd.Parameters.Add(cmd, "@AlbumName" + index, DbType.String).Value = name; + index++; + } + + clause += ")"; + whereClauses.Add(clause); + } + + //var enableItemsByName = query.IncludeItemsByName ?? query.IncludeItemTypes.Length > 0; + var enableItemsByName = query.IncludeItemsByName ?? false; if (query.TopParentIds.Length == 1) { @@ -2128,6 +3261,12 @@ namespace MediaBrowser.Server.Implementations.Persistence var inClause = string.Join(",", query.AncestorIds.Select(i => "'" + new Guid(i).ToString("N") + "'").ToArray()); whereClauses.Add(string.Format("Guid in (select itemId from AncestorIds where AncestorIdText in ({0}))", inClause)); } + if (!string.IsNullOrWhiteSpace(query.AncestorWithPresentationUniqueKey)) + { + var inClause = "select guid from TypedBaseItems where PresentationUniqueKey=@AncestorWithPresentationUniqueKey"; + whereClauses.Add(string.Format("Guid in (select itemId from AncestorIds where AncestorId in ({0}))", inClause)); + cmd.Parameters.Add(cmd, "@AncestorWithPresentationUniqueKey", DbType.String).Value = query.AncestorWithPresentationUniqueKey; + } if (query.BlockUnratedItems.Length == 1) { @@ -2143,28 +3282,58 @@ namespace MediaBrowser.Server.Implementations.Persistence var excludeTagIndex = 0; foreach (var excludeTag in query.ExcludeTags) { - whereClauses.Add("Tags not like @excludeTag" + excludeTagIndex); + whereClauses.Add("(Tags is null OR Tags not like @excludeTag" + excludeTagIndex + ")"); cmd.Parameters.Add(cmd, "@excludeTag" + excludeTagIndex, DbType.String).Value = "%" + excludeTag + "%"; excludeTagIndex++; } - if (addPaging) + excludeTagIndex = 0; + foreach (var excludeTag in query.ExcludeInheritedTags) { - if (query.StartIndex.HasValue && query.StartIndex.Value > 0) - { - var pagingWhereText = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + whereClauses.Add("(InheritedTags is null OR InheritedTags not like @excludeInheritedTag" + excludeTagIndex + ")"); + cmd.Parameters.Add(cmd, "@excludeInheritedTag" + excludeTagIndex, DbType.String).Value = "%" + excludeTag + "%"; + excludeTagIndex++; + } - var orderBy = GetOrderByText(query); + return whereClauses; + } - whereClauses.Add(string.Format("guid NOT IN (SELECT guid FROM TypedBaseItems {0}" + orderBy + " LIMIT {1})", - pagingWhereText, - query.StartIndex.Value.ToString(CultureInfo.InvariantCulture))); - } + private bool EnableGroupByPresentationUniqueKey(InternalItemsQuery query) + { + if (!query.GroupByPresentationUniqueKey) + { + return false; } - return whereClauses; + if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey)) + { + return false; + } + + if (query.User == null) + { + return false; + } + + if (query.IncludeItemTypes.Length == 0) + { + return true; + } + + var types = new[] { + typeof(Episode).Name, + typeof(Video).Name , + typeof(Movie).Name , + typeof(MusicVideo).Name , + typeof(Series).Name , + typeof(Season).Name }; + + if (types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase))) + { + return true; + } + + return false; } private static readonly Type[] KnownTypes = @@ -2208,6 +3377,88 @@ namespace MediaBrowser.Server.Implementations.Persistence public async Task UpdateInheritedValues(CancellationToken cancellationToken) { + await UpdateInheritedParentalRating(cancellationToken).ConfigureAwait(false); + await UpdateInheritedTags(cancellationToken).ConfigureAwait(false); + } + + private async Task UpdateInheritedTags(CancellationToken cancellationToken) + { + var newValues = new List<Tuple<Guid, string>>(); + + using (var cmd = _connection.CreateCommand()) + { + cmd.CommandText = "select Guid,InheritedTags,(select group_concat(Tags, '|') from TypedBaseItems where (guid=outer.guid) OR (guid in (Select AncestorId from AncestorIds where ItemId=Outer.guid))) as NewInheritedTags from typedbaseitems as Outer where NewInheritedTags <> InheritedTags"; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + var id = reader.GetGuid(0); + string value = reader.IsDBNull(2) ? null : reader.GetString(2); + + newValues.Add(new Tuple<Guid, string>(id, value)); + } + } + } + + Logger.Debug("UpdateInheritedTags - {0} rows", newValues.Count); + if (newValues.Count == 0) + { + return; + } + + await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); + + IDbTransaction transaction = null; + + try + { + transaction = _connection.BeginTransaction(); + + foreach (var item in newValues) + { + _updateInheritedTagsCommand.GetParameter(0).Value = item.Item1; + _updateInheritedTagsCommand.GetParameter(1).Value = item.Item2; + + _updateInheritedTagsCommand.Transaction = transaction; + _updateInheritedTagsCommand.ExecuteNonQuery(); + } + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + catch (Exception e) + { + Logger.ErrorException("Error running query:", e); + + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + + WriteLock.Release(); + } + } + + private async Task UpdateInheritedParentalRating(CancellationToken cancellationToken) + { var newValues = new List<Tuple<Guid, int>>(); using (var cmd = _connection.CreateCommand()) @@ -2226,6 +3477,7 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + Logger.Debug("UpdateInheritedParentalRatings - {0} rows", newValues.Count); if (newValues.Count == 0) { return; @@ -2283,7 +3535,7 @@ namespace MediaBrowser.Server.Implementations.Persistence private static Dictionary<string, string[]> GetTypeMapDictionary() { - var dict = new Dictionary<string, string[]>(); + var dict = new Dictionary<string, string[]>(StringComparer.OrdinalIgnoreCase); foreach (var t in KnownTypes) { @@ -2348,6 +3600,26 @@ namespace MediaBrowser.Server.Implementations.Persistence _deleteAncestorsCommand.Transaction = transaction; _deleteAncestorsCommand.ExecuteNonQuery(); + // Delete user data keys + _deleteUserDataKeysCommand.GetParameter(0).Value = id; + _deleteUserDataKeysCommand.Transaction = transaction; + _deleteUserDataKeysCommand.ExecuteNonQuery(); + + // Delete item values + _deleteItemValuesCommand.GetParameter(0).Value = id; + _deleteItemValuesCommand.Transaction = transaction; + _deleteItemValuesCommand.ExecuteNonQuery(); + + // Delete provider ids + _deleteProviderIdsCommand.GetParameter(0).Value = id; + _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; @@ -2540,6 +3812,490 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + public QueryResult<Tuple<BaseItem, ItemCounts>> GetArtists(InternalItemsQuery query) + { + return GetItemValues(query, 0, typeof(MusicArtist).FullName); + } + + public QueryResult<Tuple<BaseItem, ItemCounts>> GetAlbumArtists(InternalItemsQuery query) + { + return GetItemValues(query, 1, typeof(MusicArtist).FullName); + } + + public QueryResult<Tuple<BaseItem, ItemCounts>> GetStudios(InternalItemsQuery query) + { + return GetItemValues(query, 3, typeof(Studio).FullName); + } + + public QueryResult<Tuple<BaseItem, ItemCounts>> GetGenres(InternalItemsQuery query) + { + return GetItemValues(query, 2, typeof(Genre).FullName); + } + + public QueryResult<Tuple<BaseItem, ItemCounts>> GetGameGenres(InternalItemsQuery query) + { + return GetItemValues(query, 2, typeof(GameGenre).FullName); + } + + public QueryResult<Tuple<BaseItem, ItemCounts>> GetMusicGenres(InternalItemsQuery query) + { + return GetItemValues(query, 2, typeof(MusicGenre).FullName); + } + + private QueryResult<Tuple<BaseItem, ItemCounts>> GetItemValues(InternalItemsQuery query, int itemValueType, string returnType) + { + if (query == null) + { + throw new ArgumentNullException("query"); + } + + if (!query.Limit.HasValue) + { + query.EnableTotalRecordCount = false; + } + + CheckDisposed(); + + var now = DateTime.UtcNow; + + using (var cmd = _connection.CreateCommand()) + { + var itemCountColumns = new List<Tuple<string, string>>(); + + var typesToCount = query.IncludeItemTypes.ToList(); + + if (typesToCount.Count > 0) + { + var itemCountColumnQuery = "select group_concat(type, '|')" + GetFromText("B"); + + var typeSubQuery = new InternalItemsQuery(query.User) + { + ExcludeItemTypes = query.ExcludeItemTypes, + IncludeItemTypes = query.IncludeItemTypes, + MediaTypes = query.MediaTypes, + AncestorIds = query.AncestorIds, + ExcludeItemIds = query.ExcludeItemIds, + ItemIds = query.ItemIds, + TopParentIds = query.TopParentIds, + ParentId = query.ParentId, + IsPlayed = query.IsPlayed + }; + var whereClauses = GetWhereClauses(typeSubQuery, cmd, "itemTypes"); + + whereClauses.Add("guid in (select ItemId from ItemValues where ItemValues.CleanValue=A.CleanName AND Type=@ItemValueType)"); + + var typeWhereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); + + itemCountColumnQuery += typeWhereText; + + //itemCountColumnQuery += ")"; + + itemCountColumns.Add(new Tuple<string, string>("itemTypes", "(" + itemCountColumnQuery + ") as itemTypes")); + } + + var columns = _retriveItemColumns.ToList(); + columns.AddRange(itemCountColumns.Select(i => i.Item2).ToArray()); + + cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, columns.ToArray(), cmd)) + GetFromText(); + cmd.CommandText += GetJoinUserDataText(query); + + var innerQuery = new InternalItemsQuery(query.User) + { + ExcludeItemTypes = query.ExcludeItemTypes, + IncludeItemTypes = query.IncludeItemTypes, + MediaTypes = query.MediaTypes, + AncestorIds = query.AncestorIds, + ExcludeItemIds = query.ExcludeItemIds, + ItemIds = query.ItemIds, + TopParentIds = query.TopParentIds, + ParentId = query.ParentId, + IsPlayed = query.IsPlayed + }; + + var innerWhereClauses = GetWhereClauses(innerQuery, cmd); + + var innerWhereText = innerWhereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", innerWhereClauses.ToArray()); + + var whereText = " where Type=@SelectType"; + + if (typesToCount.Count == 0) + { + whereText += " And CleanName In (Select CleanValue from ItemValues where Type=@ItemValueType AND ItemId in (select guid from TypedBaseItems" + innerWhereText + "))"; + } + else + { + //whereText += " And itemTypes not null"; + whereText += " And CleanName In (Select CleanValue from ItemValues where Type=@ItemValueType AND ItemId in (select guid from TypedBaseItems" + innerWhereText + "))"; + } + + var outerQuery = new InternalItemsQuery(query.User) + { + IsFavorite = query.IsFavorite, + IsFavoriteOrLiked = query.IsFavoriteOrLiked, + IsLiked = query.IsLiked, + IsLocked = query.IsLocked, + NameLessThan = query.NameLessThan, + NameStartsWith = query.NameStartsWith, + NameStartsWithOrGreater = query.NameStartsWithOrGreater, + AlbumArtistStartsWithOrGreater = query.AlbumArtistStartsWithOrGreater, + Tags = query.Tags, + OfficialRatings = query.OfficialRatings, + Genres = query.GenreIds, + Years = query.Years + }; + + var outerWhereClauses = GetWhereClauses(outerQuery, cmd); + + whereText += outerWhereClauses.Count == 0 ? + string.Empty : + " AND " + string.Join(" AND ", outerWhereClauses.ToArray()); + //cmd.CommandText += GetGroupBy(query); + + cmd.CommandText += whereText; + cmd.CommandText += " group by PresentationUniqueKey"; + + cmd.Parameters.Add(cmd, "@SelectType", DbType.String).Value = returnType; + cmd.Parameters.Add(cmd, "@ItemValueType", DbType.Int32).Value = itemValueType; + + if (EnableJoinUserData(query)) + { + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id; + } + + cmd.CommandText += " order by SortName"; + + if (query.Limit.HasValue || query.StartIndex.HasValue) + { + var offset = query.StartIndex ?? 0; + + if (query.Limit.HasValue || offset > 0) + { + cmd.CommandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture); + } + + if (offset > 0) + { + cmd.CommandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture); + } + } + + cmd.CommandText += ";"; + + var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0; + + if (isReturningZeroItems) + { + cmd.CommandText = ""; + } + + if (query.EnableTotalRecordCount) + { + cmd.CommandText += "select count (distinct PresentationUniqueKey)" + GetFromText(); + + cmd.CommandText += GetJoinUserDataText(query); + cmd.CommandText += whereText; + } + else + { + cmd.CommandText = cmd.CommandText.TrimEnd(';'); + } + + var list = new List<Tuple<BaseItem, ItemCounts>>(); + var count = 0; + + var commandBehavior = isReturningZeroItems || !query.EnableTotalRecordCount + ? (CommandBehavior.SequentialAccess | CommandBehavior.SingleResult) + : CommandBehavior.SequentialAccess; + + //Logger.Debug("GetItemValues: " + cmd.CommandText); + + using (var reader = cmd.ExecuteReader(commandBehavior)) + { + LogQueryTime("GetItemValues", cmd, now); + + if (isReturningZeroItems) + { + if (reader.Read()) + { + count = reader.GetInt32(0); + } + } + else + { + while (reader.Read()) + { + var item = GetItem(reader); + if (item != null) + { + var countStartColumn = columns.Count - 1; + + list.Add(new Tuple<BaseItem, ItemCounts>(item, GetItemCounts(reader, countStartColumn, typesToCount))); + } + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } + } + } + + if (count == 0) + { + count = list.Count; + } + + return new QueryResult<Tuple<BaseItem, ItemCounts>> + { + Items = list.ToArray(), + TotalRecordCount = count + }; + + } + } + + private ItemCounts GetItemCounts(IDataReader reader, int countStartColumn, List<string> typesToCount) + { + var counts = new ItemCounts(); + + if (typesToCount.Count == 0) + { + return counts; + } + + var typeString = reader.IsDBNull(countStartColumn) ? null : reader.GetString(countStartColumn); + + if (string.IsNullOrWhiteSpace(typeString)) + { + return counts; + } + + var allTypes = typeString.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries) + .ToLookup(i => i).ToList(); + + foreach (var type in allTypes) + { + var value = type.ToList().Count; + var typeName = type.Key; + + if (string.Equals(typeName, typeof(Series).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.SeriesCount = value; + } + else if (string.Equals(typeName, typeof(Episode).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.EpisodeCount = value; + } + else if (string.Equals(typeName, typeof(Movie).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.MovieCount = value; + } + else if (string.Equals(typeName, typeof(MusicAlbum).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.AlbumCount = value; + } + else if (string.Equals(typeName, typeof(Audio).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.SongCount = value; + } + else if (string.Equals(typeName, typeof(Game).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.GameCount = value; + } + else if (string.Equals(typeName, typeof(Trailer).FullName, StringComparison.OrdinalIgnoreCase)) + { + counts.TrailerCount = value; + } + counts.ItemCount += value; + } + + return counts; + } + + private List<Tuple<int, string>> GetItemValuesToSave(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))); + } + + 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; + } + + 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) + { + throw new ArgumentNullException("itemId"); + } + + if (values == null) + { + throw new ArgumentNullException("values"); + } + + // Just in case there might be case-insensitive duplicates, strip them out now + var newValues = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase); + foreach (var pair in values) + { + newValues[pair.Key] = pair.Value; + } + + CheckDisposed(); + + // First delete + _deleteProviderIdsCommand.GetParameter(0).Value = itemId; + _deleteProviderIdsCommand.Transaction = transaction; + + _deleteProviderIdsCommand.ExecuteNonQuery(); + + foreach (var pair in newValues) + { + _saveProviderIdsCommand.GetParameter(0).Value = itemId; + _saveProviderIdsCommand.GetParameter(1).Value = pair.Key; + _saveProviderIdsCommand.GetParameter(2).Value = pair.Value; + _saveProviderIdsCommand.Transaction = transaction; + + _saveProviderIdsCommand.ExecuteNonQuery(); + } + } + + 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; + if (pair.Item2 == null) + { + _saveItemValuesCommand.GetParameter(3).Value = null; + } + else + { + _saveItemValuesCommand.GetParameter(3).Value = pair.Item2.RemoveDiacritics(); + } + _saveItemValuesCommand.Transaction = transaction; + + _saveItemValuesCommand.ExecuteNonQuery(); + } + } + + private void UpdateUserDataKeys(Guid itemId, List<string> keys, IDbTransaction transaction) + { + if (itemId == Guid.Empty) + { + throw new ArgumentNullException("itemId"); + } + + if (keys == null) + { + throw new ArgumentNullException("keys"); + } + + CheckDisposed(); + + // First delete + _deleteUserDataKeysCommand.GetParameter(0).Value = itemId; + _deleteUserDataKeysCommand.Transaction = transaction; + + _deleteUserDataKeysCommand.ExecuteNonQuery(); + var index = 0; + + foreach (var key in keys) + { + _saveUserDataKeysCommand.GetParameter(0).Value = itemId; + _saveUserDataKeysCommand.GetParameter(1).Value = key; + _saveUserDataKeysCommand.GetParameter(2).Value = index; + index++; + _saveUserDataKeysCommand.Transaction = transaction; + + _saveUserDataKeysCommand.ExecuteNonQuery(); + } + } + public async Task UpdatePeople(Guid itemId, List<PersonInfo> people) { if (itemId == Guid.Empty) @@ -2656,6 +4412,8 @@ namespace MediaBrowser.Server.Implementations.Persistence throw new ArgumentNullException("query"); } + var list = new List<MediaStream>(); + using (var cmd = _connection.CreateCommand()) { var cmdText = "select " + string.Join(",", _mediaStreamSaveColumns) + " from mediastreams where"; @@ -2683,13 +4441,15 @@ namespace MediaBrowser.Server.Implementations.Persistence { while (reader.Read()) { - yield return GetMediaStream(reader); + list.Add(GetMediaStream(reader)); } } } + + return list; } - public async Task SaveMediaStreams(Guid id, IEnumerable<MediaStream> streams, CancellationToken cancellationToken) + public async Task SaveMediaStreams(Guid id, List<MediaStream> streams, CancellationToken cancellationToken) { CheckDisposed(); @@ -2755,10 +4515,15 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveStreamCommand.GetParameter(index++).Value = stream.BitDepth; _saveStreamCommand.GetParameter(index++).Value = stream.IsAnamorphic; _saveStreamCommand.GetParameter(index++).Value = stream.RefFrames; - _saveStreamCommand.GetParameter(index++).Value = stream.IsCabac; _saveStreamCommand.GetParameter(index++).Value = stream.CodecTag; _saveStreamCommand.GetParameter(index++).Value = stream.Comment; + _saveStreamCommand.GetParameter(index++).Value = stream.NalLengthSize; + _saveStreamCommand.GetParameter(index++).Value = stream.IsAVC; + _saveStreamCommand.GetParameter(index++).Value = stream.Title; + + _saveStreamCommand.GetParameter(index++).Value = stream.TimeBase; + _saveStreamCommand.GetParameter(index++).Value = stream.CodecTimeBase; _saveStreamCommand.Transaction = transaction; _saveStreamCommand.ExecuteNonQuery(); @@ -2909,17 +4674,37 @@ namespace MediaBrowser.Server.Implementations.Persistence if (!reader.IsDBNull(25)) { - item.IsCabac = reader.GetBoolean(25); + item.CodecTag = reader.GetString(25); } if (!reader.IsDBNull(26)) { - item.CodecTag = reader.GetString(26); + item.Comment = reader.GetString(26); } if (!reader.IsDBNull(27)) { - item.Comment = reader.GetString(27); + item.NalLengthSize = reader.GetString(27); + } + + if (!reader.IsDBNull(28)) + { + item.IsAVC = reader.GetBoolean(28); + } + + if (!reader.IsDBNull(29)) + { + item.Title = reader.GetString(29); + } + + if (!reader.IsDBNull(30)) + { + item.TimeBase = reader.GetString(30); + } + + if (!reader.IsDBNull(31)) + { + item.CodecTimeBase = reader.GetString(31); } return item; diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteProviderInfoRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteProviderInfoRepository.cs deleted file mode 100644 index dbceda727..000000000 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteProviderInfoRepository.cs +++ /dev/null @@ -1,248 +0,0 @@ -using MediaBrowser.Common.Configuration; -using MediaBrowser.Controller.Providers; -using MediaBrowser.Model.Logging; -using System; -using System.Data; -using System.IO; -using System.Linq; -using System.Text; -using System.Threading; -using System.Threading.Tasks; - -namespace MediaBrowser.Server.Implementations.Persistence -{ - public class SqliteProviderInfoRepository : BaseSqliteRepository, IProviderRepository - { - private IDbConnection _connection; - - private IDbCommand _saveStatusCommand; - private readonly IApplicationPaths _appPaths; - - public SqliteProviderInfoRepository(ILogManager logManager, IApplicationPaths appPaths) : base(logManager) - { - _appPaths = appPaths; - } - - /// <summary> - /// Gets the name of the repository - /// </summary> - /// <value>The name.</value> - public string Name - { - get - { - return "SQLite"; - } - } - - /// <summary> - /// Opens the connection to the database - /// </summary> - /// <returns>Task.</returns> - public async Task Initialize() - { - var dbFile = Path.Combine(_appPaths.DataPath, "refreshinfo.db"); - - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); - - string[] queries = { - - "create table if not exists MetadataStatus (ItemId GUID PRIMARY KEY, DateLastMetadataRefresh datetime, DateLastImagesRefresh datetime, ItemDateModified DateTimeNull)", - "create index if not exists idx_MetadataStatus on MetadataStatus(ItemId)", - - //pragmas - "pragma temp_store = memory", - - "pragma shrink_memory" - }; - - _connection.RunQueries(queries, Logger); - - AddItemDateModifiedCommand(); - - PrepareStatements(); - } - - private static readonly string[] StatusColumns = - { - "ItemId", - "DateLastMetadataRefresh", - "DateLastImagesRefresh", - "ItemDateModified" - }; - - private void AddItemDateModifiedCommand() - { - using (var cmd = _connection.CreateCommand()) - { - cmd.CommandText = "PRAGMA table_info(MetadataStatus)"; - - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) - { - while (reader.Read()) - { - if (!reader.IsDBNull(1)) - { - var name = reader.GetString(1); - - if (string.Equals(name, "ItemDateModified", StringComparison.OrdinalIgnoreCase)) - { - return; - } - } - } - } - } - - var builder = new StringBuilder(); - - builder.AppendLine("alter table MetadataStatus"); - builder.AppendLine("add column ItemDateModified DateTime NULL"); - - _connection.RunQueries(new[] { builder.ToString() }, Logger); - } - - /// <summary> - /// Prepares the statements. - /// </summary> - private void PrepareStatements() - { - _saveStatusCommand = _connection.CreateCommand(); - - _saveStatusCommand.CommandText = string.Format("replace into MetadataStatus ({0}) values ({1})", - string.Join(",", StatusColumns), - string.Join(",", StatusColumns.Select(i => "@" + i).ToArray())); - - foreach (var col in StatusColumns) - { - _saveStatusCommand.Parameters.Add(_saveStatusCommand, "@" + col); - } - } - - public MetadataStatus GetMetadataStatus(Guid itemId) - { - if (itemId == Guid.Empty) - { - throw new ArgumentNullException("itemId"); - } - - using (var cmd = _connection.CreateCommand()) - { - var cmdText = "select " + string.Join(",", StatusColumns) + " from MetadataStatus where"; - - cmdText += " ItemId=@ItemId"; - cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = itemId; - - cmd.CommandText = cmdText; - - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) - { - while (reader.Read()) - { - return GetStatus(reader); - } - - return null; - } - } - } - - private MetadataStatus GetStatus(IDataReader reader) - { - var result = new MetadataStatus - { - ItemId = reader.GetGuid(0) - }; - - if (!reader.IsDBNull(1)) - { - result.DateLastMetadataRefresh = reader.GetDateTime(1).ToUniversalTime(); - } - - if (!reader.IsDBNull(2)) - { - result.DateLastImagesRefresh = reader.GetDateTime(2).ToUniversalTime(); - } - - if (!reader.IsDBNull(3)) - { - result.ItemDateModified = reader.GetDateTime(3).ToUniversalTime(); - } - - return result; - } - - public async Task SaveMetadataStatus(MetadataStatus status, CancellationToken cancellationToken) - { - if (status == null) - { - throw new ArgumentNullException("status"); - } - - cancellationToken.ThrowIfCancellationRequested(); - - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try - { - transaction = _connection.BeginTransaction(); - - _saveStatusCommand.GetParameter(0).Value = status.ItemId; - _saveStatusCommand.GetParameter(1).Value = status.DateLastMetadataRefresh; - _saveStatusCommand.GetParameter(2).Value = status.DateLastImagesRefresh; - _saveStatusCommand.GetParameter(3).Value = status.ItemDateModified; - - _saveStatusCommand.Transaction = transaction; - - _saveStatusCommand.ExecuteNonQuery(); - - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } - - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save provider info:", e); - - if (transaction != null) - { - transaction.Rollback(); - } - - throw; - } - finally - { - if (transaction != null) - { - transaction.Dispose(); - } - - WriteLock.Release(); - } - } - - protected override void CloseConnection() - { - if (_connection != null) - { - if (_connection.IsOpen()) - { - _connection.Close(); - } - - _connection.Dispose(); - _connection = null; - } - } - } -} diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs index 63c41c71f..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,21 +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() + public async Task Initialize(IDbConnection connection, SemaphoreSlim writeLock) { - var dbFile = Path.Combine(_appPaths.DataPath, "userdata_v2.db"); - - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).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 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", @@ -295,11 +322,54 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - return new UserItemData + return null; + } + } + + 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)) { - UserId = userId, - Key = key - }; + if (reader.Read()) + { + return ReadRow(reader); + } + } + + return null; } } @@ -370,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 diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs index 9bd7e47f3..25ab60ca5 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs @@ -17,14 +17,13 @@ namespace MediaBrowser.Server.Implementations.Persistence /// </summary> public class SqliteUserRepository : BaseSqliteRepository, IUserRepository { - private IDbConnection _connection; - private readonly IServerApplicationPaths _appPaths; private readonly IJsonSerializer _jsonSerializer; - public SqliteUserRepository(ILogManager logManager, IServerApplicationPaths appPaths, IJsonSerializer jsonSerializer) : base(logManager) + public SqliteUserRepository(ILogManager logManager, IServerApplicationPaths appPaths, IJsonSerializer jsonSerializer, IDbConnector dbConnector) : base(logManager, dbConnector) { - _appPaths = appPaths; _jsonSerializer = jsonSerializer; + + DbFilePath = Path.Combine(appPaths.DataPath, "users.db"); } /// <summary> @@ -45,23 +44,19 @@ namespace MediaBrowser.Server.Implementations.Persistence /// <returns>Task.</returns> public async Task Initialize() { - var dbFile = Path.Combine(_appPaths.DataPath, "users.db"); - - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); - - string[] queries = { + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + string[] queries = { "create table if not exists users (guid GUID primary key, data BLOB)", "create index if not exists idx_users on users(guid)", "create table if not exists schema_version (table_name primary key, version)", - //pragmas - "pragma temp_store = memory", - "pragma shrink_memory" }; - _connection.RunQueries(queries, Logger); + connection.RunQueries(queries, Logger); + } } /// <summary> @@ -84,55 +79,54 @@ namespace MediaBrowser.Server.Implementations.Persistence cancellationToken.ThrowIfCancellationRequested(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); + IDbTransaction transaction = null; - using (var cmd = _connection.CreateCommand()) + try { - cmd.CommandText = "replace into users (guid, data) values (@1, @2)"; - cmd.Parameters.Add(cmd, "@1", DbType.Guid).Value = user.Id; - cmd.Parameters.Add(cmd, "@2", DbType.Binary).Value = serialized; + transaction = connection.BeginTransaction(); - cmd.Transaction = transaction; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "replace into users (guid, data) values (@1, @2)"; + cmd.Parameters.Add(cmd, "@1", DbType.Guid).Value = user.Id; + cmd.Parameters.Add(cmd, "@2", DbType.Binary).Value = serialized; - cmd.ExecuteNonQuery(); - } + cmd.Transaction = transaction; - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) + cmd.ExecuteNonQuery(); + } + + transaction.Commit(); + } + catch (OperationCanceledException) { - transaction.Rollback(); + if (transaction != null) + { + transaction.Rollback(); + } + + throw; } + catch (Exception e) + { + Logger.ErrorException("Failed to save user:", e); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save user:", e); + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); + throw; } - - throw; - } - finally - { - if (transaction != null) + finally { - transaction.Dispose(); + if (transaction != null) + { + transaction.Dispose(); + } } - - WriteLock.Release(); } } @@ -142,25 +136,32 @@ namespace MediaBrowser.Server.Implementations.Persistence /// <returns>IEnumerable{User}.</returns> public IEnumerable<User> RetrieveAllUsers() { - using (var cmd = _connection.CreateCommand()) - { - cmd.CommandText = "select guid,data from users"; + var list = new List<User>(); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + using (var connection = CreateConnection(true).Result) + { + using (var cmd = connection.CreateCommand()) { - while (reader.Read()) - { - var id = reader.GetGuid(0); + cmd.CommandText = "select guid,data from users"; - using (var stream = reader.GetMemoryStream(1)) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) { - var user = _jsonSerializer.DeserializeFromStream<User>(stream); - user.Id = id; - yield return user; + var id = reader.GetGuid(0); + + using (var stream = reader.GetMemoryStream(1)) + { + var user = _jsonSerializer.DeserializeFromStream<User>(stream); + user.Id = id; + list.Add(user); + } } } } } + + return list; } /// <summary> @@ -179,69 +180,54 @@ namespace MediaBrowser.Server.Implementations.Persistence cancellationToken.ThrowIfCancellationRequested(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); + IDbTransaction transaction = null; - using (var cmd = _connection.CreateCommand()) + try { - cmd.CommandText = "delete from users where guid=@guid"; - - cmd.Parameters.Add(cmd, "@guid", DbType.Guid).Value = user.Id; + transaction = connection.BeginTransaction(); - cmd.Transaction = transaction; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "delete from users where guid=@guid"; - cmd.ExecuteNonQuery(); - } + cmd.Parameters.Add(cmd, "@guid", DbType.Guid).Value = user.Id; - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } + cmd.Transaction = transaction; - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to delete user:", e); + cmd.ExecuteNonQuery(); + } - if (transaction != null) - { - transaction.Rollback(); + transaction.Commit(); } - - throw; - } - finally - { - if (transaction != null) + catch (OperationCanceledException) { - transaction.Dispose(); + if (transaction != null) + { + transaction.Rollback(); + } + + throw; } + catch (Exception e) + { + Logger.ErrorException("Failed to delete user:", e); - WriteLock.Release(); - } - } + if (transaction != null) + { + transaction.Rollback(); + } - protected override void CloseConnection() - { - if (_connection != null) - { - if (_connection.IsOpen()) + throw; + } + finally { - _connection.Close(); + if (transaction != null) + { + transaction.Dispose(); + } } - - _connection.Dispose(); - _connection = null; } } } |