diff options
| author | Luke <luke.pulverenti@gmail.com> | 2016-06-11 11:57:28 -0400 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2016-06-11 11:57:28 -0400 |
| commit | cccc27483114dc9782cc76aa6ad4ce7a23891794 (patch) | |
| tree | 4a541169a784dd2f094211a4836eaa5b94bfb135 | |
| parent | ea3b099f7f57f5a5bcc824e95cbb375e2f561050 (diff) | |
| parent | dc5c15c60b598a58c924daa350dfaf9f6b7d1c17 (diff) | |
Merge pull request #1837 from MediaBrowser/dev
Dev
20 files changed, 1297 insertions, 1368 deletions
diff --git a/MediaBrowser.Dlna/PlayTo/PlayToManager.cs b/MediaBrowser.Dlna/PlayTo/PlayToManager.cs index bbb9bf6de..cd9a7b1f0 100644 --- a/MediaBrowser.Dlna/PlayTo/PlayToManager.cs +++ b/MediaBrowser.Dlna/PlayTo/PlayToManager.cs @@ -101,6 +101,7 @@ namespace MediaBrowser.Dlna.PlayTo } var uri = new Uri(location); + _logger.Debug("Attempting to create PlayToController from location {0}", location); var device = await Device.CreateuPnpDeviceAsync(uri, _httpClient, _config, _logger).ConfigureAwait(false); if (device.RendererCommands == null) @@ -112,6 +113,7 @@ namespace MediaBrowser.Dlna.PlayTo } } + _logger.Debug("Logging session activity from location {0}", location); var sessionInfo = await _sessionManager.LogSessionActivity(device.Properties.ClientType, _appHost.ApplicationVersion.ToString(), device.Properties.UUID, device.Properties.Name, uri.OriginalString, null) .ConfigureAwait(false); diff --git a/MediaBrowser.Server.Implementations/Activity/ActivityRepository.cs b/MediaBrowser.Server.Implementations/Activity/ActivityRepository.cs index b0e05a5bc..d6ae381e9 100644 --- a/MediaBrowser.Server.Implementations/Activity/ActivityRepository.cs +++ b/MediaBrowser.Server.Implementations/Activity/ActivityRepository.cs @@ -15,25 +15,19 @@ namespace MediaBrowser.Server.Implementations.Activity { public class ActivityRepository : BaseSqliteRepository, IActivityRepository { - private IDbConnection _connection; - private readonly IServerApplicationPaths _appPaths; private readonly CultureInfo _usCulture = new CultureInfo("en-US"); - private IDbCommand _saveActivityCommand; - - public ActivityRepository(ILogManager logManager, IServerApplicationPaths appPaths) - : base(logManager) + public ActivityRepository(ILogManager logManager, IServerApplicationPaths appPaths, IDbConnector connector) + : base(logManager, connector) { - _appPaths = appPaths; + DbFilePath = Path.Combine(appPaths.DataPath, "activitylog.db"); } - public async Task Initialize(IDbConnector dbConnector) + public async Task Initialize() { - var dbFile = Path.Combine(_appPaths.DataPath, "activitylog.db"); - - _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); - - string[] queries = { + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + string[] queries = { "create table if not exists ActivityLogEntries (Id GUID PRIMARY KEY, Name TEXT, Overview TEXT, ShortOverview TEXT, Type TEXT, ItemId TEXT, UserId TEXT, DateCreated DATETIME, LogSeverity TEXT)", "create index if not exists idx_ActivityLogEntries on ActivityLogEntries(Id)", @@ -44,25 +38,8 @@ namespace MediaBrowser.Server.Implementations.Activity "pragma shrink_memory" }; - _connection.RunQueries(queries, Logger); - - PrepareStatements(); - } - - private void PrepareStatements() - { - _saveActivityCommand = _connection.CreateCommand(); - _saveActivityCommand.CommandText = "replace into ActivityLogEntries (Id, Name, Overview, ShortOverview, Type, ItemId, UserId, DateCreated, LogSeverity) values (@Id, @Name, @Overview, @ShortOverview, @Type, @ItemId, @UserId, @DateCreated, @LogSeverity)"; - - _saveActivityCommand.Parameters.Add(_saveActivityCommand, "@Id"); - _saveActivityCommand.Parameters.Add(_saveActivityCommand, "@Name"); - _saveActivityCommand.Parameters.Add(_saveActivityCommand, "@Overview"); - _saveActivityCommand.Parameters.Add(_saveActivityCommand, "@ShortOverview"); - _saveActivityCommand.Parameters.Add(_saveActivityCommand, "@Type"); - _saveActivityCommand.Parameters.Add(_saveActivityCommand, "@ItemId"); - _saveActivityCommand.Parameters.Add(_saveActivityCommand, "@UserId"); - _saveActivityCommand.Parameters.Add(_saveActivityCommand, "@DateCreated"); - _saveActivityCommand.Parameters.Add(_saveActivityCommand, "@LogSeverity"); + connection.RunQueries(queries, Logger); + } } private const string BaseActivitySelectText = "select Id, Name, Overview, ShortOverview, Type, ItemId, UserId, DateCreated, LogSeverity from ActivityLogEntries"; @@ -79,128 +56,145 @@ namespace MediaBrowser.Server.Implementations.Activity throw new ArgumentNullException("entry"); } - await WriteLock.WaitAsync().ConfigureAwait(false); + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + using (var saveActivityCommand = connection.CreateCommand()) + { + saveActivityCommand.CommandText = "replace into ActivityLogEntries (Id, Name, Overview, ShortOverview, Type, ItemId, UserId, DateCreated, LogSeverity) values (@Id, @Name, @Overview, @ShortOverview, @Type, @ItemId, @UserId, @DateCreated, @LogSeverity)"; - IDbTransaction transaction = null; + saveActivityCommand.Parameters.Add(saveActivityCommand, "@Id"); + saveActivityCommand.Parameters.Add(saveActivityCommand, "@Name"); + saveActivityCommand.Parameters.Add(saveActivityCommand, "@Overview"); + saveActivityCommand.Parameters.Add(saveActivityCommand, "@ShortOverview"); + saveActivityCommand.Parameters.Add(saveActivityCommand, "@Type"); + saveActivityCommand.Parameters.Add(saveActivityCommand, "@ItemId"); + saveActivityCommand.Parameters.Add(saveActivityCommand, "@UserId"); + saveActivityCommand.Parameters.Add(saveActivityCommand, "@DateCreated"); + saveActivityCommand.Parameters.Add(saveActivityCommand, "@LogSeverity"); - try - { - transaction = _connection.BeginTransaction(); + IDbTransaction transaction = null; - var index = 0; + try + { + transaction = connection.BeginTransaction(); - _saveActivityCommand.GetParameter(index++).Value = new Guid(entry.Id); - _saveActivityCommand.GetParameter(index++).Value = entry.Name; - _saveActivityCommand.GetParameter(index++).Value = entry.Overview; - _saveActivityCommand.GetParameter(index++).Value = entry.ShortOverview; - _saveActivityCommand.GetParameter(index++).Value = entry.Type; - _saveActivityCommand.GetParameter(index++).Value = entry.ItemId; - _saveActivityCommand.GetParameter(index++).Value = entry.UserId; - _saveActivityCommand.GetParameter(index++).Value = entry.Date; - _saveActivityCommand.GetParameter(index++).Value = entry.Severity.ToString(); + var index = 0; - _saveActivityCommand.Transaction = transaction; + saveActivityCommand.GetParameter(index++).Value = new Guid(entry.Id); + saveActivityCommand.GetParameter(index++).Value = entry.Name; + saveActivityCommand.GetParameter(index++).Value = entry.Overview; + saveActivityCommand.GetParameter(index++).Value = entry.ShortOverview; + saveActivityCommand.GetParameter(index++).Value = entry.Type; + saveActivityCommand.GetParameter(index++).Value = entry.ItemId; + saveActivityCommand.GetParameter(index++).Value = entry.UserId; + saveActivityCommand.GetParameter(index++).Value = entry.Date; + saveActivityCommand.GetParameter(index++).Value = entry.Severity.ToString(); - _saveActivityCommand.ExecuteNonQuery(); + saveActivityCommand.Transaction = transaction; - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } + saveActivityCommand.ExecuteNonQuery(); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save record:", e); + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); - } + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to save record:", e); - throw; - } - finally - { - if (transaction != null) - { - transaction.Dispose(); - } + if (transaction != null) + { + transaction.Rollback(); + } - WriteLock.Release(); + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + } + } } } public QueryResult<ActivityLogEntry> GetActivityLogEntries(DateTime? minDate, int? startIndex, int? limit) { - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = BaseActivitySelectText; - - var whereClauses = new List<string>(); - - if (minDate.HasValue) + using (var cmd = connection.CreateCommand()) { - whereClauses.Add("DateCreated>=@DateCreated"); - cmd.Parameters.Add(cmd, "@DateCreated", DbType.Date).Value = minDate.Value; - } + cmd.CommandText = BaseActivitySelectText; - var whereTextWithoutPaging = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + var whereClauses = new List<string>(); - if (startIndex.HasValue && startIndex.Value > 0) - { - var pagingWhereText = whereClauses.Count == 0 ? + if (minDate.HasValue) + { + whereClauses.Add("DateCreated>=@DateCreated"); + cmd.Parameters.Add(cmd, "@DateCreated", DbType.Date).Value = minDate.Value; + } + + var whereTextWithoutPaging = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray()); - - whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM ActivityLogEntries {0} ORDER BY DateCreated DESC LIMIT {1})", - pagingWhereText, - startIndex.Value.ToString(_usCulture))); - } - - var whereText = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); - cmd.CommandText += whereText; + if (startIndex.HasValue && startIndex.Value > 0) + { + var pagingWhereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - cmd.CommandText += " ORDER BY DateCreated DESC"; + whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM ActivityLogEntries {0} ORDER BY DateCreated DESC LIMIT {1})", + pagingWhereText, + startIndex.Value.ToString(_usCulture))); + } - if (limit.HasValue) - { - cmd.CommandText += " LIMIT " + limit.Value.ToString(_usCulture); - } + var whereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - cmd.CommandText += "; select count (Id) from ActivityLogEntries" + whereTextWithoutPaging; + cmd.CommandText += whereText; - var list = new List<ActivityLogEntry>(); - var count = 0; + cmd.CommandText += " ORDER BY DateCreated DESC"; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) - { - while (reader.Read()) + if (limit.HasValue) { - list.Add(GetEntry(reader)); + cmd.CommandText += " LIMIT " + limit.Value.ToString(_usCulture); } - if (reader.NextResult() && reader.Read()) + cmd.CommandText += "; select count (Id) from ActivityLogEntries" + whereTextWithoutPaging; + + var list = new List<ActivityLogEntry>(); + var count = 0; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { - count = reader.GetInt32(0); + while (reader.Read()) + { + list.Add(GetEntry(reader)); + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } } - } - return new QueryResult<ActivityLogEntry>() - { - Items = list.ToArray(), - TotalRecordCount = count - }; + return new QueryResult<ActivityLogEntry>() + { + Items = list.ToArray(), + TotalRecordCount = count + }; + } } } @@ -260,19 +254,5 @@ namespace MediaBrowser.Server.Implementations.Activity return info; } - - protected override void CloseConnection() - { - if (_connection != null) - { - if (_connection.IsOpen()) - { - _connection.Close(); - } - - _connection.Dispose(); - _connection = null; - } - } } } diff --git a/MediaBrowser.Server.Implementations/EntryPoints/ExternalPortForwarding.cs b/MediaBrowser.Server.Implementations/EntryPoints/ExternalPortForwarding.cs index 5777a0af7..50ad3cfbc 100644 --- a/MediaBrowser.Server.Implementations/EntryPoints/ExternalPortForwarding.cs +++ b/MediaBrowser.Server.Implementations/EntryPoints/ExternalPortForwarding.cs @@ -93,7 +93,7 @@ namespace MediaBrowser.Server.Implementations.EntryPoints NatUtility.UnhandledException += NatUtility_UnhandledException; NatUtility.StartDiscovery(); - _timer = new PeriodicTimer(s => _createdRules = new List<string>(), null, TimeSpan.FromMinutes(5), TimeSpan.FromMinutes(5)); + _timer = new PeriodicTimer(ClearCreatedRules, null, TimeSpan.FromMinutes(5), TimeSpan.FromMinutes(5)); _ssdp.MessageReceived += _ssdp_MessageReceived; @@ -102,12 +102,43 @@ namespace MediaBrowser.Server.Implementations.EntryPoints _isStarted = true; } + private void ClearCreatedRules(object state) + { + _createdRules = new List<string>(); + _usnsHandled = new List<string>(); + } + void _ssdp_MessageReceived(object sender, SsdpMessageEventArgs e) { var endpoint = e.EndPoint as IPEndPoint; - if (endpoint != null && e.LocalEndPoint != null) + if (endpoint == null || e.LocalEndPoint == null) { + return; + } + + string usn; + if (!e.Headers.TryGetValue("USN", out usn)) usn = string.Empty; + + string nt; + if (!e.Headers.TryGetValue("NT", out nt)) nt = string.Empty; + + // Filter device type + if (usn.IndexOf("WANIPConnection:", StringComparison.OrdinalIgnoreCase) == -1 && + nt.IndexOf("WANIPConnection:", StringComparison.OrdinalIgnoreCase) == -1 && + usn.IndexOf("WANPPPConnection:", StringComparison.OrdinalIgnoreCase) == -1 && + nt.IndexOf("WANPPPConnection:", StringComparison.OrdinalIgnoreCase) == -1) + { + return; + } + + var identifier = string.IsNullOrWhiteSpace(usn) ? nt : usn; + + if (!_usnsHandled.Contains(identifier)) + { + _usnsHandled.Add(identifier); + + _logger.Debug("Calling Nat.Handle on " + identifier); NatUtility.Handle(e.LocalEndPoint.Address, e.Message, endpoint, NatProtocol.Upnp); } } @@ -151,6 +182,7 @@ namespace MediaBrowser.Server.Implementations.EntryPoints } private List<string> _createdRules = new List<string>(); + private List<string> _usnsHandled = new List<string>(); private void CreateRules(INatDevice device) { // On some systems the device discovered event seems to fire repeatedly diff --git a/MediaBrowser.Server.Implementations/IO/FileRefresher.cs b/MediaBrowser.Server.Implementations/IO/FileRefresher.cs index 18c52ab29..4bea6ad34 100644 --- a/MediaBrowser.Server.Implementations/IO/FileRefresher.cs +++ b/MediaBrowser.Server.Implementations/IO/FileRefresher.cs @@ -93,8 +93,15 @@ namespace MediaBrowser.Server.Implementations.IO private async void OnTimerCallback(object state) { + List<string> paths; + + lock (_timerLock) + { + paths = _affectedPaths.ToList(); + } + // Extend the timer as long as any of the paths are still being written to. - if (_affectedPaths.Any(IsFileLocked)) + if (paths.Any(IsFileLocked)) { Logger.Info("Timer extended."); RestartTimer(); @@ -108,7 +115,7 @@ namespace MediaBrowser.Server.Implementations.IO try { - await ProcessPathChanges(_affectedPaths.ToList()).ConfigureAwait(false); + await ProcessPathChanges(paths.ToList()).ConfigureAwait(false); } catch (Exception ex) { diff --git a/MediaBrowser.Server.Implementations/Notifications/SqliteNotificationsRepository.cs b/MediaBrowser.Server.Implementations/Notifications/SqliteNotificationsRepository.cs index 10e8c5699..6c3bc3050 100644 --- a/MediaBrowser.Server.Implementations/Notifications/SqliteNotificationsRepository.cs +++ b/MediaBrowser.Server.Implementations/Notifications/SqliteNotificationsRepository.cs @@ -15,30 +15,20 @@ namespace MediaBrowser.Server.Implementations.Notifications { public class SqliteNotificationsRepository : BaseSqliteRepository, INotificationsRepository { - private IDbConnection _connection; - private readonly IServerApplicationPaths _appPaths; + public SqliteNotificationsRepository(ILogManager logManager, IServerApplicationPaths appPaths, IDbConnector dbConnector) : base(logManager, dbConnector) + { + DbFilePath = Path.Combine(appPaths.DataPath, "notifications.db"); + } public event EventHandler<NotificationUpdateEventArgs> NotificationAdded; public event EventHandler<NotificationReadEventArgs> NotificationsMarkedRead; public event EventHandler<NotificationUpdateEventArgs> NotificationUpdated; - private IDbCommand _replaceNotificationCommand; - private IDbCommand _markReadCommand; - private IDbCommand _markAllReadCommand; - - public SqliteNotificationsRepository(ILogManager logManager, IServerApplicationPaths appPaths) - : base(logManager) + public async Task Initialize() { - _appPaths = appPaths; - } - - public async Task Initialize(IDbConnector dbConnector) - { - var dbFile = Path.Combine(_appPaths.DataPath, "notifications.db"); - - _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); - - string[] queries = { + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + string[] queries = { "create table if not exists Notifications (Id GUID NOT NULL, UserId GUID NOT NULL, Date DATETIME NOT NULL, Name TEXT NOT NULL, Description TEXT, Url TEXT, Level TEXT NOT NULL, IsRead BOOLEAN NOT NULL, Category TEXT NOT NULL, RelatedId TEXT, PRIMARY KEY (Id, UserId))", "create index if not exists idx_Notifications1 on Notifications(Id)", @@ -50,39 +40,8 @@ namespace MediaBrowser.Server.Implementations.Notifications "pragma shrink_memory" }; - _connection.RunQueries(queries, Logger); - - PrepareStatements(); - } - - private void PrepareStatements() - { - _replaceNotificationCommand = _connection.CreateCommand(); - _replaceNotificationCommand.CommandText = "replace into Notifications (Id, UserId, Date, Name, Description, Url, Level, IsRead, Category, RelatedId) values (@Id, @UserId, @Date, @Name, @Description, @Url, @Level, @IsRead, @Category, @RelatedId)"; - - _replaceNotificationCommand.Parameters.Add(_replaceNotificationCommand, "@Id"); - _replaceNotificationCommand.Parameters.Add(_replaceNotificationCommand, "@UserId"); - _replaceNotificationCommand.Parameters.Add(_replaceNotificationCommand, "@Date"); - _replaceNotificationCommand.Parameters.Add(_replaceNotificationCommand, "@Name"); - _replaceNotificationCommand.Parameters.Add(_replaceNotificationCommand, "@Description"); - _replaceNotificationCommand.Parameters.Add(_replaceNotificationCommand, "@Url"); - _replaceNotificationCommand.Parameters.Add(_replaceNotificationCommand, "@Level"); - _replaceNotificationCommand.Parameters.Add(_replaceNotificationCommand, "@IsRead"); - _replaceNotificationCommand.Parameters.Add(_replaceNotificationCommand, "@Category"); - _replaceNotificationCommand.Parameters.Add(_replaceNotificationCommand, "@RelatedId"); - - _markReadCommand = _connection.CreateCommand(); - _markReadCommand.CommandText = "update Notifications set IsRead=@IsRead where Id=@Id and UserId=@UserId"; - - _markReadCommand.Parameters.Add(_replaceNotificationCommand, "@UserId"); - _markReadCommand.Parameters.Add(_replaceNotificationCommand, "@IsRead"); - _markReadCommand.Parameters.Add(_replaceNotificationCommand, "@Id"); - - _markAllReadCommand = _connection.CreateCommand(); - _markAllReadCommand.CommandText = "update Notifications set IsRead=@IsRead where UserId=@UserId"; - - _markAllReadCommand.Parameters.Add(_replaceNotificationCommand, "@UserId"); - _markAllReadCommand.Parameters.Add(_replaceNotificationCommand, "@IsRead"); + connection.RunQueries(queries, Logger); + } } /// <summary> @@ -94,49 +53,52 @@ namespace MediaBrowser.Server.Implementations.Notifications { var result = new NotificationResult(); - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - var clauses = new List<string>(); - - if (query.IsRead.HasValue) + using (var cmd = connection.CreateCommand()) { - clauses.Add("IsRead=@IsRead"); - cmd.Parameters.Add(cmd, "@IsRead", DbType.Boolean).Value = query.IsRead.Value; - } + var clauses = new List<string>(); - clauses.Add("UserId=@UserId"); - cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = new Guid(query.UserId); + if (query.IsRead.HasValue) + { + clauses.Add("IsRead=@IsRead"); + cmd.Parameters.Add(cmd, "@IsRead", DbType.Boolean).Value = query.IsRead.Value; + } - var whereClause = " where " + string.Join(" And ", clauses.ToArray()); + clauses.Add("UserId=@UserId"); + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = new Guid(query.UserId); - cmd.CommandText = string.Format("select count(Id) from Notifications{0};select Id,UserId,Date,Name,Description,Url,Level,IsRead,Category,RelatedId from Notifications{0} order by IsRead asc, Date desc", whereClause); + var whereClause = " where " + string.Join(" And ", clauses.ToArray()); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) - { - if (reader.Read()) - { - result.TotalRecordCount = reader.GetInt32(0); - } + cmd.CommandText = string.Format("select count(Id) from Notifications{0};select Id,UserId,Date,Name,Description,Url,Level,IsRead,Category,RelatedId from Notifications{0} order by IsRead asc, Date desc", whereClause); - if (reader.NextResult()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { - var notifications = GetNotifications(reader); - - if (query.StartIndex.HasValue) + if (reader.Read()) { - notifications = notifications.Skip(query.StartIndex.Value); + result.TotalRecordCount = reader.GetInt32(0); } - if (query.Limit.HasValue) + if (reader.NextResult()) { - notifications = notifications.Take(query.Limit.Value); - } + var notifications = GetNotifications(reader); - result.Notifications = notifications.ToArray(); + if (query.StartIndex.HasValue) + { + notifications = notifications.Skip(query.StartIndex.Value); + } + + if (query.Limit.HasValue) + { + notifications = notifications.Take(query.Limit.Value); + } + + result.Notifications = notifications.ToArray(); + } } - } - return result; + return result; + } } } @@ -144,31 +106,34 @@ namespace MediaBrowser.Server.Implementations.Notifications { var result = new NotificationsSummary(); - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = "select Level from Notifications where UserId=@UserId and IsRead=@IsRead"; - - cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = new Guid(userId); - cmd.Parameters.Add(cmd, "@IsRead", DbType.Boolean).Value = false; - - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) + using (var cmd = connection.CreateCommand()) { - var levels = new List<NotificationLevel>(); + cmd.CommandText = "select Level from Notifications where UserId=@UserId and IsRead=@IsRead"; - while (reader.Read()) + cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = new Guid(userId); + cmd.Parameters.Add(cmd, "@IsRead", DbType.Boolean).Value = false; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { - levels.Add(GetLevel(reader, 0)); - } + var levels = new List<NotificationLevel>(); - result.UnreadCount = levels.Count; + while (reader.Read()) + { + levels.Add(GetLevel(reader, 0)); + } - if (levels.Count > 0) - { - result.MaxUnreadNotificationLevel = levels.Max(); + result.UnreadCount = levels.Count; + + if (levels.Count > 0) + { + result.MaxUnreadNotificationLevel = levels.Max(); + } } - } - return result; + return result; + } } } @@ -179,10 +144,14 @@ namespace MediaBrowser.Server.Implementations.Notifications /// <returns>IEnumerable{Notification}.</returns> private IEnumerable<Notification> GetNotifications(IDataReader reader) { + var list = new List<Notification>(); + while (reader.Read()) { - yield return GetNotification(reader); + list.Add(GetNotification(reader)); } + + return list; } private Notification GetNotification(IDataReader reader) @@ -273,59 +242,74 @@ namespace MediaBrowser.Server.Implementations.Notifications cancellationToken.ThrowIfCancellationRequested(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); + using (var replaceNotificationCommand = connection.CreateCommand()) + { + replaceNotificationCommand.CommandText = "replace into Notifications (Id, UserId, Date, Name, Description, Url, Level, IsRead, Category, RelatedId) values (@Id, @UserId, @Date, @Name, @Description, @Url, @Level, @IsRead, @Category, @RelatedId)"; + + replaceNotificationCommand.Parameters.Add(replaceNotificationCommand, "@Id"); + replaceNotificationCommand.Parameters.Add(replaceNotificationCommand, "@UserId"); + replaceNotificationCommand.Parameters.Add(replaceNotificationCommand, "@Date"); + replaceNotificationCommand.Parameters.Add(replaceNotificationCommand, "@Name"); + replaceNotificationCommand.Parameters.Add(replaceNotificationCommand, "@Description"); + replaceNotificationCommand.Parameters.Add(replaceNotificationCommand, "@Url"); + replaceNotificationCommand.Parameters.Add(replaceNotificationCommand, "@Level"); + replaceNotificationCommand.Parameters.Add(replaceNotificationCommand, "@IsRead"); + replaceNotificationCommand.Parameters.Add(replaceNotificationCommand, "@Category"); + replaceNotificationCommand.Parameters.Add(replaceNotificationCommand, "@RelatedId"); + + IDbTransaction transaction = null; + + try + { + transaction = connection.BeginTransaction(); - _replaceNotificationCommand.GetParameter(0).Value = new Guid(notification.Id); - _replaceNotificationCommand.GetParameter(1).Value = new Guid(notification.UserId); - _replaceNotificationCommand.GetParameter(2).Value = notification.Date.ToUniversalTime(); - _replaceNotificationCommand.GetParameter(3).Value = notification.Name; - _replaceNotificationCommand.GetParameter(4).Value = notification.Description; - _replaceNotificationCommand.GetParameter(5).Value = notification.Url; - _replaceNotificationCommand.GetParameter(6).Value = notification.Level.ToString(); - _replaceNotificationCommand.GetParameter(7).Value = notification.IsRead; - _replaceNotificationCommand.GetParameter(8).Value = string.Empty; - _replaceNotificationCommand.GetParameter(9).Value = string.Empty; + replaceNotificationCommand.GetParameter(0).Value = new Guid(notification.Id); + replaceNotificationCommand.GetParameter(1).Value = new Guid(notification.UserId); + replaceNotificationCommand.GetParameter(2).Value = notification.Date.ToUniversalTime(); + replaceNotificationCommand.GetParameter(3).Value = notification.Name; + replaceNotificationCommand.GetParameter(4).Value = notification.Description; + replaceNotificationCommand.GetParameter(5).Value = notification.Url; + replaceNotificationCommand.GetParameter(6).Value = notification.Level.ToString(); + replaceNotificationCommand.GetParameter(7).Value = notification.IsRead; + replaceNotificationCommand.GetParameter(8).Value = string.Empty; + replaceNotificationCommand.GetParameter(9).Value = string.Empty; - _replaceNotificationCommand.Transaction = transaction; + replaceNotificationCommand.Transaction = transaction; - _replaceNotificationCommand.ExecuteNonQuery(); + replaceNotificationCommand.ExecuteNonQuery(); - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save notification:", e); + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to save notification:", 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(); } } @@ -366,51 +350,58 @@ namespace MediaBrowser.Server.Implementations.Notifications { cancellationToken.ThrowIfCancellationRequested(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + using (var markAllReadCommand = connection.CreateCommand()) + { + markAllReadCommand.CommandText = "update Notifications set IsRead=@IsRead where UserId=@UserId"; - IDbTransaction transaction = null; + markAllReadCommand.Parameters.Add(markAllReadCommand, "@UserId"); + markAllReadCommand.Parameters.Add(markAllReadCommand, "@IsRead"); - try - { - cancellationToken.ThrowIfCancellationRequested(); + IDbTransaction transaction = null; - transaction = _connection.BeginTransaction(); + try + { + cancellationToken.ThrowIfCancellationRequested(); - _markAllReadCommand.GetParameter(0).Value = new Guid(userId); - _markAllReadCommand.GetParameter(1).Value = isRead; + transaction = connection.BeginTransaction(); - _markAllReadCommand.ExecuteNonQuery(); + markAllReadCommand.GetParameter(0).Value = new Guid(userId); + markAllReadCommand.GetParameter(1).Value = isRead; - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } + markAllReadCommand.ExecuteNonQuery(); - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save notification:", e); + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); - } + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to save notification:", e); - throw; - } - finally - { - if (transaction != null) - { - transaction.Dispose(); - } + if (transaction != null) + { + transaction.Rollback(); + } - WriteLock.Release(); + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + } + } } } @@ -418,72 +409,66 @@ namespace MediaBrowser.Server.Implementations.Notifications { cancellationToken.ThrowIfCancellationRequested(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - cancellationToken.ThrowIfCancellationRequested(); + using (var markReadCommand = connection.CreateCommand()) + { + markReadCommand.CommandText = "update Notifications set IsRead=@IsRead where Id=@Id and UserId=@UserId"; - transaction = _connection.BeginTransaction(); + markReadCommand.Parameters.Add(markReadCommand, "@UserId"); + markReadCommand.Parameters.Add(markReadCommand, "@IsRead"); + markReadCommand.Parameters.Add(markReadCommand, "@Id"); - _markReadCommand.GetParameter(0).Value = new Guid(userId); - _markReadCommand.GetParameter(1).Value = isRead; + IDbTransaction transaction = null; - foreach (var id in notificationIdList) - { - _markReadCommand.GetParameter(2).Value = id; + try + { + cancellationToken.ThrowIfCancellationRequested(); - _markReadCommand.Transaction = transaction; + transaction = connection.BeginTransaction(); - _markReadCommand.ExecuteNonQuery(); - } + markReadCommand.GetParameter(0).Value = new Guid(userId); + markReadCommand.GetParameter(1).Value = isRead; - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } + foreach (var id in notificationIdList) + { + markReadCommand.GetParameter(2).Value = id; - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save notification:", e); + markReadCommand.Transaction = transaction; - if (transaction != null) - { - transaction.Rollback(); - } + markReadCommand.ExecuteNonQuery(); + } - throw; - } - finally - { - if (transaction != null) - { - transaction.Dispose(); - } + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } - WriteLock.Release(); - } - } + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to save notification:", e); - protected override void CloseConnection() - { - if (_connection != null) - { - if (_connection.IsOpen()) - { - _connection.Close(); - } + if (transaction != null) + { + transaction.Rollback(); + } - _connection.Dispose(); - _connection = null; + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + } + } } } } diff --git a/MediaBrowser.Server.Implementations/Persistence/BaseSqliteRepository.cs b/MediaBrowser.Server.Implementations/Persistence/BaseSqliteRepository.cs index 395907844..f9c892b76 100644 --- a/MediaBrowser.Server.Implementations/Persistence/BaseSqliteRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/BaseSqliteRepository.cs @@ -9,13 +9,22 @@ namespace MediaBrowser.Server.Implementations.Persistence public abstract class BaseSqliteRepository : IDisposable { protected readonly 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 Task<IDbConnection> CreateConnection(bool isReadOnly = false) + { + return DbConnector.Connect(DbFilePath, false, true); + } + private bool _disposed; protected void CheckDisposed() { @@ -84,6 +93,9 @@ namespace MediaBrowser.Server.Implementations.Persistence } } - protected abstract void CloseConnection(); + protected virtual void CloseConnection() + { + + } } } diff --git a/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs b/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs index 2092f830a..596cf8407 100644 --- a/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs +++ b/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs @@ -5,6 +5,6 @@ namespace MediaBrowser.Server.Implementations.Persistence { public interface IDbConnector { - Task<IDbConnection> Connect(string dbPath, int? cacheSize = null); + Task<IDbConnection> Connect(string dbPath, bool isReadOnly, bool enablePooling = false, int? cacheSize = null); } } diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs index 6077cfdba..1a6a88ebe 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,21 +43,14 @@ 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(IDbConnector dbConnector) + public async Task Initialize() { - var dbFile = Path.Combine(_appPaths.DataPath, "displaypreferences.db"); - - _connection = await dbConnector.Connect(dbFile).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)", @@ -69,7 +61,8 @@ namespace MediaBrowser.Server.Implementations.Persistence "pragma shrink_memory" }; - _connection.RunQueries(queries, Logger); + connection.RunQueries(queries, Logger); + } } /// <summary> @@ -96,58 +89,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(); + + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "replace into userdisplaypreferences (id, userid, client, data) values (@1, @2, @3, @4)"; - 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.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.Transaction = transaction; + cmd.Transaction = transaction; - cmd.ExecuteNonQuery(); - } + 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(); } } @@ -168,64 +160,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 +237,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 +274,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 2f3f34aa4..cc9e3ebcc 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs @@ -19,11 +19,7 @@ namespace MediaBrowser.Server.Implementations.Persistence /// <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, int? cacheSize, ILogger logger) + public static async Task<IDbConnection> ConnectToDb(string dbPath, bool isReadOnly, bool enablePooling, int? cacheSize, ILogger logger) { if (string.IsNullOrEmpty(dbPath)) { @@ -38,7 +34,9 @@ namespace MediaBrowser.Server.Implementations.Persistence CacheSize = cacheSize ?? 2000, SyncMode = SynchronizationModes.Normal, DataSource = dbPath, - JournalMode = SQLiteJournalModeEnum.Wal + JournalMode = SQLiteJournalModeEnum.Wal, + Pooling = enablePooling, + ReadOnly = isReadOnly }; var connection = new SQLiteConnection(connectionstr.ConnectionString); @@ -47,15 +45,5 @@ namespace MediaBrowser.Server.Implementations.Persistence return connection; } - - public static void BindFunction(this SQLiteConnection connection, SQLiteFunction function) - { - var attributes = function.GetType().GetCustomAttributes(typeof(SQLiteFunctionAttribute), true).Cast<SQLiteFunctionAttribute>().ToArray(); - if (attributes.Length == 0) - { - throw new InvalidOperationException("SQLiteFunction doesn't have SQLiteFunctionAttribute"); - } - connection.BindFunction(attributes[0], function); - } } } diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs index 037776997..8a4ba6460 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs @@ -26,7 +26,7 @@ namespace MediaBrowser.Server.Implementations.Persistence 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; } @@ -39,7 +39,7 @@ namespace MediaBrowser.Server.Implementations.Persistence { var dbFile = Path.Combine(_appPaths.DataPath, "fileorganization.db"); - _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); + _connection = await dbConnector.Connect(dbFile, false).ConfigureAwait(false); string[] queries = { diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 071951018..9bfdd879d 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -99,8 +99,8 @@ namespace MediaBrowser.Server.Implementations.Persistence /// <summary> /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. /// </summary> - public SqliteItemRepository(IServerConfigurationManager config, IJsonSerializer jsonSerializer, ILogManager logManager) - : base(logManager) + public SqliteItemRepository(IServerConfigurationManager config, IJsonSerializer jsonSerializer, ILogManager logManager, IDbConnector connector) + : base(logManager, connector) { if (config == null) { @@ -127,7 +127,7 @@ namespace MediaBrowser.Server.Implementations.Persistence { var dbFile = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); - _connection = await dbConnector.Connect(dbFile, 6000).ConfigureAwait(false); + _connection = await dbConnector.Connect(dbFile, false, false, 6000).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, 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))"; diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs index bfdb9e0c7..d81f4ad37 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs @@ -18,7 +18,7 @@ namespace MediaBrowser.Server.Implementations.Persistence 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; } @@ -43,7 +43,7 @@ namespace MediaBrowser.Server.Implementations.Persistence { var dbFile = Path.Combine(_appPaths.DataPath, "userdata_v2.db"); - _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); + _connection = await dbConnector.Connect(dbFile, false).ConfigureAwait(false); string[] queries = { diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs index f7ca39a54..4c07a6018 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> @@ -43,13 +42,11 @@ namespace MediaBrowser.Server.Implementations.Persistence /// Opens the connection to the database /// </summary> /// <returns>Task.</returns> - public async Task Initialize(IDbConnector dbConnector) + public async Task Initialize() { - var dbFile = Path.Combine(_appPaths.DataPath, "users.db"); - - _connection = await dbConnector.Connect(dbFile).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)", @@ -61,7 +58,8 @@ namespace MediaBrowser.Server.Implementations.Persistence "pragma shrink_memory" }; - _connection.RunQueries(queries, Logger); + connection.RunQueries(queries, Logger); + } } /// <summary> @@ -84,55 +82,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 +139,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 +183,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; } } } diff --git a/MediaBrowser.Server.Implementations/Security/AuthenticationRepository.cs b/MediaBrowser.Server.Implementations/Security/AuthenticationRepository.cs index e8d9814ec..3013510de 100644 --- a/MediaBrowser.Server.Implementations/Security/AuthenticationRepository.cs +++ b/MediaBrowser.Server.Implementations/Security/AuthenticationRepository.cs @@ -15,25 +15,21 @@ namespace MediaBrowser.Server.Implementations.Security { public class AuthenticationRepository : BaseSqliteRepository, IAuthenticationRepository { - private IDbConnection _connection; private readonly IServerApplicationPaths _appPaths; private readonly CultureInfo _usCulture = new CultureInfo("en-US"); - private IDbCommand _saveInfoCommand; - - public AuthenticationRepository(ILogManager logManager, IServerApplicationPaths appPaths) - : base(logManager) + public AuthenticationRepository(ILogManager logManager, IServerApplicationPaths appPaths, IDbConnector connector) + : base(logManager, connector) { _appPaths = appPaths; + DbFilePath = Path.Combine(appPaths.DataPath, "authentication.db"); } - public async Task Initialize(IDbConnector dbConnector) + public async Task Initialize() { - var dbFile = Path.Combine(_appPaths.DataPath, "authentication.db"); - - _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); - - string[] queries = { + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + string[] queries = { "create table if not exists AccessTokens (Id GUID PRIMARY KEY, AccessToken TEXT NOT NULL, DeviceId TEXT, AppName TEXT, AppVersion TEXT, DeviceName TEXT, UserId TEXT, IsActive BIT, DateCreated DATETIME NOT NULL, DateRevoked DATETIME)", "create index if not exists idx_AccessTokens on AccessTokens(Id)", @@ -44,28 +40,10 @@ namespace MediaBrowser.Server.Implementations.Security "pragma shrink_memory" }; - _connection.RunQueries(queries, Logger); - - _connection.AddColumn(Logger, "AccessTokens", "AppVersion", "TEXT"); + connection.RunQueries(queries, Logger); - PrepareStatements(); - } - - private void PrepareStatements() - { - _saveInfoCommand = _connection.CreateCommand(); - _saveInfoCommand.CommandText = "replace into AccessTokens (Id, AccessToken, DeviceId, AppName, AppVersion, DeviceName, UserId, IsActive, DateCreated, DateRevoked) values (@Id, @AccessToken, @DeviceId, @AppName, @AppVersion, @DeviceName, @UserId, @IsActive, @DateCreated, @DateRevoked)"; - - _saveInfoCommand.Parameters.Add(_saveInfoCommand, "@Id"); - _saveInfoCommand.Parameters.Add(_saveInfoCommand, "@AccessToken"); - _saveInfoCommand.Parameters.Add(_saveInfoCommand, "@DeviceId"); - _saveInfoCommand.Parameters.Add(_saveInfoCommand, "@AppName"); - _saveInfoCommand.Parameters.Add(_saveInfoCommand, "@AppVersion"); - _saveInfoCommand.Parameters.Add(_saveInfoCommand, "@DeviceName"); - _saveInfoCommand.Parameters.Add(_saveInfoCommand, "@UserId"); - _saveInfoCommand.Parameters.Add(_saveInfoCommand, "@IsActive"); - _saveInfoCommand.Parameters.Add(_saveInfoCommand, "@DateCreated"); - _saveInfoCommand.Parameters.Add(_saveInfoCommand, "@DateRevoked"); + connection.AddColumn(Logger, "AccessTokens", "AppVersion", "TEXT"); + } } public Task Create(AuthenticationInfo info, CancellationToken cancellationToken) @@ -84,61 +62,76 @@ namespace MediaBrowser.Server.Implementations.Security cancellationToken.ThrowIfCancellationRequested(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); + using (var saveInfoCommand = connection.CreateCommand()) + { + saveInfoCommand.CommandText = "replace into AccessTokens (Id, AccessToken, DeviceId, AppName, AppVersion, DeviceName, UserId, IsActive, DateCreated, DateRevoked) values (@Id, @AccessToken, @DeviceId, @AppName, @AppVersion, @DeviceName, @UserId, @IsActive, @DateCreated, @DateRevoked)"; + + saveInfoCommand.Parameters.Add(saveInfoCommand, "@Id"); + saveInfoCommand.Parameters.Add(saveInfoCommand, "@AccessToken"); + saveInfoCommand.Parameters.Add(saveInfoCommand, "@DeviceId"); + saveInfoCommand.Parameters.Add(saveInfoCommand, "@AppName"); + saveInfoCommand.Parameters.Add(saveInfoCommand, "@AppVersion"); + saveInfoCommand.Parameters.Add(saveInfoCommand, "@DeviceName"); + saveInfoCommand.Parameters.Add(saveInfoCommand, "@UserId"); + saveInfoCommand.Parameters.Add(saveInfoCommand, "@IsActive"); + saveInfoCommand.Parameters.Add(saveInfoCommand, "@DateCreated"); + saveInfoCommand.Parameters.Add(saveInfoCommand, "@DateRevoked"); + + IDbTransaction transaction = null; + + try + { + transaction = connection.BeginTransaction(); - var index = 0; + var index = 0; - _saveInfoCommand.GetParameter(index++).Value = new Guid(info.Id); - _saveInfoCommand.GetParameter(index++).Value = info.AccessToken; - _saveInfoCommand.GetParameter(index++).Value = info.DeviceId; - _saveInfoCommand.GetParameter(index++).Value = info.AppName; - _saveInfoCommand.GetParameter(index++).Value = info.AppVersion; - _saveInfoCommand.GetParameter(index++).Value = info.DeviceName; - _saveInfoCommand.GetParameter(index++).Value = info.UserId; - _saveInfoCommand.GetParameter(index++).Value = info.IsActive; - _saveInfoCommand.GetParameter(index++).Value = info.DateCreated; - _saveInfoCommand.GetParameter(index++).Value = info.DateRevoked; + saveInfoCommand.GetParameter(index++).Value = new Guid(info.Id); + saveInfoCommand.GetParameter(index++).Value = info.AccessToken; + saveInfoCommand.GetParameter(index++).Value = info.DeviceId; + saveInfoCommand.GetParameter(index++).Value = info.AppName; + saveInfoCommand.GetParameter(index++).Value = info.AppVersion; + saveInfoCommand.GetParameter(index++).Value = info.DeviceName; + saveInfoCommand.GetParameter(index++).Value = info.UserId; + saveInfoCommand.GetParameter(index++).Value = info.IsActive; + saveInfoCommand.GetParameter(index++).Value = info.DateCreated; + saveInfoCommand.GetParameter(index++).Value = info.DateRevoked; - _saveInfoCommand.Transaction = transaction; + saveInfoCommand.Transaction = transaction; - _saveInfoCommand.ExecuteNonQuery(); + saveInfoCommand.ExecuteNonQuery(); - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save record:", e); + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to save record:", 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(); } } @@ -151,101 +144,104 @@ namespace MediaBrowser.Server.Implementations.Security throw new ArgumentNullException("query"); } - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = BaseSelectText; - - var whereClauses = new List<string>(); - - var startIndex = query.StartIndex ?? 0; - - if (!string.IsNullOrWhiteSpace(query.AccessToken)) + using (var cmd = connection.CreateCommand()) { - whereClauses.Add("AccessToken=@AccessToken"); - cmd.Parameters.Add(cmd, "@AccessToken", DbType.String).Value = query.AccessToken; - } + cmd.CommandText = BaseSelectText; - if (!string.IsNullOrWhiteSpace(query.UserId)) - { - whereClauses.Add("UserId=@UserId"); - cmd.Parameters.Add(cmd, "@UserId", DbType.String).Value = query.UserId; - } + var whereClauses = new List<string>(); - if (!string.IsNullOrWhiteSpace(query.DeviceId)) - { - whereClauses.Add("DeviceId=@DeviceId"); - cmd.Parameters.Add(cmd, "@DeviceId", DbType.String).Value = query.DeviceId; - } + var startIndex = query.StartIndex ?? 0; - if (query.IsActive.HasValue) - { - whereClauses.Add("IsActive=@IsActive"); - cmd.Parameters.Add(cmd, "@IsActive", DbType.Boolean).Value = query.IsActive.Value; - } + if (!string.IsNullOrWhiteSpace(query.AccessToken)) + { + whereClauses.Add("AccessToken=@AccessToken"); + cmd.Parameters.Add(cmd, "@AccessToken", DbType.String).Value = query.AccessToken; + } - if (query.HasUser.HasValue) - { - if (query.HasUser.Value) + if (!string.IsNullOrWhiteSpace(query.UserId)) { - whereClauses.Add("UserId not null"); + whereClauses.Add("UserId=@UserId"); + cmd.Parameters.Add(cmd, "@UserId", DbType.String).Value = query.UserId; } - else + + if (!string.IsNullOrWhiteSpace(query.DeviceId)) { - whereClauses.Add("UserId is null"); + whereClauses.Add("DeviceId=@DeviceId"); + cmd.Parameters.Add(cmd, "@DeviceId", DbType.String).Value = query.DeviceId; } - } - var whereTextWithoutPaging = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + if (query.IsActive.HasValue) + { + whereClauses.Add("IsActive=@IsActive"); + cmd.Parameters.Add(cmd, "@IsActive", DbType.Boolean).Value = query.IsActive.Value; + } - if (startIndex > 0) - { - var pagingWhereText = whereClauses.Count == 0 ? + if (query.HasUser.HasValue) + { + if (query.HasUser.Value) + { + whereClauses.Add("UserId not null"); + } + else + { + whereClauses.Add("UserId is null"); + } + } + + var whereTextWithoutPaging = whereClauses.Count == 0 ? string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray()); - whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM AccessTokens {0} ORDER BY DateCreated LIMIT {1})", - pagingWhereText, - startIndex.ToString(_usCulture))); - } + if (startIndex > 0) + { + var pagingWhereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); + + whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM AccessTokens {0} ORDER BY DateCreated LIMIT {1})", + pagingWhereText, + startIndex.ToString(_usCulture))); + } - var whereText = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + var whereText = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - cmd.CommandText += whereText; + cmd.CommandText += whereText; - cmd.CommandText += " ORDER BY DateCreated"; + cmd.CommandText += " ORDER BY DateCreated"; - if (query.Limit.HasValue) - { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); - } + if (query.Limit.HasValue) + { + cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); + } - cmd.CommandText += "; select count (Id) from AccessTokens" + whereTextWithoutPaging; + cmd.CommandText += "; select count (Id) from AccessTokens" + whereTextWithoutPaging; - var list = new List<AuthenticationInfo>(); - var count = 0; + var list = new List<AuthenticationInfo>(); + var count = 0; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) - { - while (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { - list.Add(Get(reader)); + while (reader.Read()) + { + list.Add(Get(reader)); + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } } - if (reader.NextResult() && reader.Read()) + return new QueryResult<AuthenticationInfo>() { - count = reader.GetInt32(0); - } + Items = list.ToArray(), + TotalRecordCount = count + }; } - - return new QueryResult<AuthenticationInfo>() - { - Items = list.ToArray(), - TotalRecordCount = count - }; } } @@ -256,24 +252,27 @@ namespace MediaBrowser.Server.Implementations.Security throw new ArgumentNullException("id"); } - var guid = new Guid(id); - - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = BaseSelectText + " where Id=@Id"; - - cmd.Parameters.Add(cmd, "@Id", DbType.Guid).Value = guid; + var guid = new Guid(id); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) + using (var cmd = connection.CreateCommand()) { - if (reader.Read()) + cmd.CommandText = BaseSelectText + " where Id=@Id"; + + cmd.Parameters.Add(cmd, "@Id", DbType.Guid).Value = guid; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) { - return Get(reader); + if (reader.Read()) + { + return Get(reader); + } } } - } - return null; + return null; + } } private AuthenticationInfo Get(IDataReader reader) @@ -319,19 +318,5 @@ namespace MediaBrowser.Server.Implementations.Security return info; } - - protected override void CloseConnection() - { - if (_connection != null) - { - if (_connection.IsOpen()) - { - _connection.Close(); - } - - _connection.Dispose(); - _connection = null; - } - } } } diff --git a/MediaBrowser.Server.Implementations/Social/SharingRepository.cs b/MediaBrowser.Server.Implementations/Social/SharingRepository.cs index 317743eb1..8a895037e 100644 --- a/MediaBrowser.Server.Implementations/Social/SharingRepository.cs +++ b/MediaBrowser.Server.Implementations/Social/SharingRepository.cs @@ -12,27 +12,21 @@ namespace MediaBrowser.Server.Implementations.Social { public class SharingRepository : BaseSqliteRepository { - private IDbConnection _connection; - private IDbCommand _saveShareCommand; - private readonly IApplicationPaths _appPaths; - - public SharingRepository(ILogManager logManager, IApplicationPaths appPaths) - : base(logManager) + public SharingRepository(ILogManager logManager, IApplicationPaths appPaths, IDbConnector dbConnector) + : base(logManager, dbConnector) { - _appPaths = appPaths; + DbFilePath = Path.Combine(appPaths.DataPath, "shares.db"); } /// <summary> /// Opens the connection to the database /// </summary> /// <returns>Task.</returns> - public async Task Initialize(IDbConnector dbConnector) + public async Task Initialize() { - var dbFile = Path.Combine(_appPaths.DataPath, "shares.db"); - - _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); - - string[] queries = { + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + string[] queries = { "create table if not exists Shares (Id GUID, ItemId TEXT, UserId TEXT, ExpirationDate DateTime, PRIMARY KEY (Id))", "create index if not exists idx_Shares on Shares(Id)", @@ -43,23 +37,8 @@ namespace MediaBrowser.Server.Implementations.Social "pragma shrink_memory" }; - _connection.RunQueries(queries, Logger); - - PrepareStatements(); - } - - /// <summary> - /// Prepares the statements. - /// </summary> - private void PrepareStatements() - { - _saveShareCommand = _connection.CreateCommand(); - _saveShareCommand.CommandText = "replace into Shares (Id, ItemId, UserId, ExpirationDate) values (@Id, @ItemId, @UserId, @ExpirationDate)"; - - _saveShareCommand.Parameters.Add(_saveShareCommand, "@Id"); - _saveShareCommand.Parameters.Add(_saveShareCommand, "@ItemId"); - _saveShareCommand.Parameters.Add(_saveShareCommand, "@UserId"); - _saveShareCommand.Parameters.Add(_saveShareCommand, "@ExpirationDate"); + connection.RunQueries(queries, Logger); + } } public async Task CreateShare(SocialShareInfo info) @@ -77,53 +56,62 @@ namespace MediaBrowser.Server.Implementations.Social cancellationToken.ThrowIfCancellationRequested(); - await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); - - _saveShareCommand.GetParameter(0).Value = new Guid(info.Id); - _saveShareCommand.GetParameter(1).Value = info.ItemId; - _saveShareCommand.GetParameter(2).Value = info.UserId; - _saveShareCommand.GetParameter(3).Value = info.ExpirationDate; - - _saveShareCommand.Transaction = transaction; - - _saveShareCommand.ExecuteNonQuery(); - - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) + using (var saveShareCommand = connection.CreateCommand()) { - transaction.Rollback(); + saveShareCommand.CommandText = "replace into Shares (Id, ItemId, UserId, ExpirationDate) values (@Id, @ItemId, @UserId, @ExpirationDate)"; + + saveShareCommand.Parameters.Add(saveShareCommand, "@Id"); + saveShareCommand.Parameters.Add(saveShareCommand, "@ItemId"); + saveShareCommand.Parameters.Add(saveShareCommand, "@UserId"); + saveShareCommand.Parameters.Add(saveShareCommand, "@ExpirationDate"); + + IDbTransaction transaction = null; + + try + { + transaction = connection.BeginTransaction(); + + saveShareCommand.GetParameter(0).Value = new Guid(info.Id); + saveShareCommand.GetParameter(1).Value = info.ItemId; + saveShareCommand.GetParameter(2).Value = info.UserId; + saveShareCommand.GetParameter(3).Value = info.ExpirationDate; + + saveShareCommand.Transaction = transaction; + + saveShareCommand.ExecuteNonQuery(); + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to save share:", e); + + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + } } - - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save share:", e); - - if (transaction != null) - { - transaction.Rollback(); - } - - throw; - } - finally - { - if (transaction != null) - { - transaction.Dispose(); - } - - WriteLock.Release(); } } @@ -134,20 +122,23 @@ namespace MediaBrowser.Server.Implementations.Social throw new ArgumentNullException("id"); } - var cmd = _connection.CreateCommand(); - cmd.CommandText = "select Id, ItemId, UserId, ExpirationDate from Shares where id = @id"; + using (var connection = CreateConnection(true).Result) + { + var cmd = connection.CreateCommand(); + cmd.CommandText = "select Id, ItemId, UserId, ExpirationDate from Shares where id = @id"; - cmd.Parameters.Add(cmd, "@id", DbType.Guid).Value = new Guid(id); + cmd.Parameters.Add(cmd, "@id", DbType.Guid).Value = new Guid(id); - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) - { - if (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) { - return GetSocialShareInfo(reader); + if (reader.Read()) + { + return GetSocialShareInfo(reader); + } } - } - return null; + return null; + } } private SocialShareInfo GetSocialShareInfo(IDataReader reader) @@ -164,21 +155,7 @@ namespace MediaBrowser.Server.Implementations.Social public async Task DeleteShare(string id) { - - } - - protected override void CloseConnection() - { - if (_connection != null) - { - if (_connection.IsOpen()) - { - _connection.Close(); - } - _connection.Dispose(); - _connection = null; - } } } } diff --git a/MediaBrowser.Server.Implementations/Sync/SyncRepository.cs b/MediaBrowser.Server.Implementations/Sync/SyncRepository.cs index 739d1ab6e..5b623cf2a 100644 --- a/MediaBrowser.Server.Implementations/Sync/SyncRepository.cs +++ b/MediaBrowser.Server.Implementations/Sync/SyncRepository.cs @@ -18,34 +18,22 @@ namespace MediaBrowser.Server.Implementations.Sync { public class SyncRepository : BaseSqliteRepository, ISyncRepository { - private IDbConnection _connection; private readonly CultureInfo _usCulture = new CultureInfo("en-US"); - private IDbCommand _insertJobCommand; - private IDbCommand _updateJobCommand; - private IDbCommand _deleteJobCommand; - - private IDbCommand _deleteJobItemsCommand; - private IDbCommand _insertJobItemCommand; - private IDbCommand _updateJobItemCommand; - private readonly IJsonSerializer _json; - private readonly IServerApplicationPaths _appPaths; - public SyncRepository(ILogManager logManager, IJsonSerializer json, IServerApplicationPaths appPaths) - : base(logManager) + public SyncRepository(ILogManager logManager, IJsonSerializer json, IServerApplicationPaths appPaths, IDbConnector connector) + : base(logManager, connector) { _json = json; - _appPaths = appPaths; + DbFilePath = Path.Combine(appPaths.DataPath, "sync14.db"); } - public async Task Initialize(IDbConnector dbConnector) + public async Task Initialize() { - var dbFile = Path.Combine(_appPaths.DataPath, "sync14.db"); - - _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); - - string[] queries = { + using (var connection = await CreateConnection().ConfigureAwait(false)) + { + string[] queries = { "create table if not exists SyncJobs (Id GUID PRIMARY KEY, TargetId TEXT NOT NULL, Name TEXT NOT NULL, Profile TEXT, Quality TEXT, Bitrate INT, Status TEXT NOT NULL, Progress FLOAT, UserId TEXT NOT NULL, ItemIds TEXT NOT NULL, Category TEXT, ParentId TEXT, UnwatchedOnly BIT, ItemLimit INT, SyncNewContent BIT, DateCreated DateTime, DateLastModified DateTime, ItemCount int)", "create index if not exists idx_SyncJobs on SyncJobs(Id)", @@ -61,114 +49,12 @@ namespace MediaBrowser.Server.Implementations.Sync "pragma shrink_memory" }; - _connection.RunQueries(queries, Logger); - - _connection.AddColumn(Logger, "SyncJobs", "Profile", "TEXT"); - _connection.AddColumn(Logger, "SyncJobs", "Bitrate", "INT"); - _connection.AddColumn(Logger, "SyncJobItems", "ItemDateModifiedTicks", "BIGINT"); + connection.RunQueries(queries, Logger); - PrepareStatements(); - } - - private void PrepareStatements() - { - // _deleteJobCommand - _deleteJobCommand = _connection.CreateCommand(); - _deleteJobCommand.CommandText = "delete from SyncJobs where Id=@Id"; - _deleteJobCommand.Parameters.Add(_deleteJobCommand, "@Id"); - - // _deleteJobItemsCommand - _deleteJobItemsCommand = _connection.CreateCommand(); - _deleteJobItemsCommand.CommandText = "delete from SyncJobItems where JobId=@JobId"; - _deleteJobItemsCommand.Parameters.Add(_deleteJobItemsCommand, "@JobId"); - - // _insertJobCommand - _insertJobCommand = _connection.CreateCommand(); - _insertJobCommand.CommandText = "insert into SyncJobs (Id, TargetId, Name, Profile, Quality, Bitrate, Status, Progress, UserId, ItemIds, Category, ParentId, UnwatchedOnly, ItemLimit, SyncNewContent, DateCreated, DateLastModified, ItemCount) values (@Id, @TargetId, @Name, @Profile, @Quality, @Bitrate, @Status, @Progress, @UserId, @ItemIds, @Category, @ParentId, @UnwatchedOnly, @ItemLimit, @SyncNewContent, @DateCreated, @DateLastModified, @ItemCount)"; - - _insertJobCommand.Parameters.Add(_insertJobCommand, "@Id"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@TargetId"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@Name"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@Profile"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@Quality"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@Bitrate"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@Status"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@Progress"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@UserId"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@ItemIds"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@Category"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@ParentId"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@UnwatchedOnly"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@ItemLimit"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@SyncNewContent"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@DateCreated"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@DateLastModified"); - _insertJobCommand.Parameters.Add(_insertJobCommand, "@ItemCount"); - - // _updateJobCommand - _updateJobCommand = _connection.CreateCommand(); - _updateJobCommand.CommandText = "update SyncJobs set TargetId=@TargetId,Name=@Name,Profile=@Profile,Quality=@Quality,Bitrate=@Bitrate,Status=@Status,Progress=@Progress,UserId=@UserId,ItemIds=@ItemIds,Category=@Category,ParentId=@ParentId,UnwatchedOnly=@UnwatchedOnly,ItemLimit=@ItemLimit,SyncNewContent=@SyncNewContent,DateCreated=@DateCreated,DateLastModified=@DateLastModified,ItemCount=@ItemCount where Id=@Id"; - - _updateJobCommand.Parameters.Add(_updateJobCommand, "@Id"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@TargetId"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@Name"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@Profile"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@Quality"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@Bitrate"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@Status"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@Progress"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@UserId"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@ItemIds"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@Category"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@ParentId"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@UnwatchedOnly"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@ItemLimit"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@SyncNewContent"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@DateCreated"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@DateLastModified"); - _updateJobCommand.Parameters.Add(_updateJobCommand, "@ItemCount"); - - // _insertJobItemCommand - _insertJobItemCommand = _connection.CreateCommand(); - _insertJobItemCommand.CommandText = "insert into SyncJobItems (Id, ItemId, ItemName, MediaSourceId, JobId, TemporaryPath, OutputPath, Status, TargetId, DateCreated, Progress, AdditionalFiles, MediaSource, IsMarkedForRemoval, JobItemIndex, ItemDateModifiedTicks) values (@Id, @ItemId, @ItemName, @MediaSourceId, @JobId, @TemporaryPath, @OutputPath, @Status, @TargetId, @DateCreated, @Progress, @AdditionalFiles, @MediaSource, @IsMarkedForRemoval, @JobItemIndex, @ItemDateModifiedTicks)"; - - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@Id"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@ItemId"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@ItemName"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@MediaSourceId"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@JobId"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@TemporaryPath"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@OutputPath"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@Status"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@TargetId"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@DateCreated"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@Progress"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@AdditionalFiles"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@MediaSource"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@IsMarkedForRemoval"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@JobItemIndex"); - _insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@ItemDateModifiedTicks"); - - // _updateJobItemCommand - _updateJobItemCommand = _connection.CreateCommand(); - _updateJobItemCommand.CommandText = "update SyncJobItems set ItemId=@ItemId,ItemName=@ItemName,MediaSourceId=@MediaSourceId,JobId=@JobId,TemporaryPath=@TemporaryPath,OutputPath=@OutputPath,Status=@Status,TargetId=@TargetId,DateCreated=@DateCreated,Progress=@Progress,AdditionalFiles=@AdditionalFiles,MediaSource=@MediaSource,IsMarkedForRemoval=@IsMarkedForRemoval,JobItemIndex=@JobItemIndex,ItemDateModifiedTicks=@ItemDateModifiedTicks where Id=@Id"; - - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@Id"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@ItemId"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@ItemName"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@MediaSourceId"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@JobId"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@TemporaryPath"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@OutputPath"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@Status"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@TargetId"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@DateCreated"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@Progress"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@AdditionalFiles"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@MediaSource"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@IsMarkedForRemoval"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@JobItemIndex"); - _updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@ItemDateModifiedTicks"); + connection.AddColumn(Logger, "SyncJobs", "Profile", "TEXT"); + connection.AddColumn(Logger, "SyncJobs", "Bitrate", "INT"); + connection.AddColumn(Logger, "SyncJobItems", "ItemDateModifiedTicks", "BIGINT"); + } } private const string BaseJobSelectText = "select Id, TargetId, Name, Profile, Quality, Bitrate, Status, Progress, UserId, ItemIds, Category, ParentId, UnwatchedOnly, ItemLimit, SyncNewContent, DateCreated, DateLastModified, ItemCount from SyncJobs"; @@ -182,7 +68,7 @@ namespace MediaBrowser.Server.Implementations.Sync } CheckDisposed(); - + var guid = new Guid(id); if (guid == Guid.Empty) @@ -190,22 +76,25 @@ namespace MediaBrowser.Server.Implementations.Sync throw new ArgumentNullException("id"); } - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = BaseJobSelectText + " where Id=@Id"; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = BaseJobSelectText + " where Id=@Id"; - cmd.Parameters.Add(cmd, "@Id", DbType.Guid).Value = guid; + cmd.Parameters.Add(cmd, "@Id", DbType.Guid).Value = guid; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) - { - if (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) { - return GetJob(reader); + if (reader.Read()) + { + return GetJob(reader); + } } } - } - return null; + return null; + } } private SyncJob GetJob(IDataReader reader) @@ -283,15 +172,15 @@ namespace MediaBrowser.Server.Implementations.Sync public Task Create(SyncJob job) { - return InsertOrUpdate(job, _insertJobCommand); + return InsertOrUpdate(job, true); } public Task Update(SyncJob job) { - return InsertOrUpdate(job, _updateJobCommand); + return InsertOrUpdate(job, false); } - private async Task InsertOrUpdate(SyncJob job, IDbCommand cmd) + private async Task InsertOrUpdate(SyncJob job, bool insert) { if (job == null) { @@ -299,70 +188,119 @@ namespace MediaBrowser.Server.Implementations.Sync } CheckDisposed(); - - await WriteLock.WaitAsync().ConfigureAwait(false); - - IDbTransaction transaction = null; - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); - - var index = 0; - - cmd.GetParameter(index++).Value = new Guid(job.Id); - cmd.GetParameter(index++).Value = job.TargetId; - cmd.GetParameter(index++).Value = job.Name; - cmd.GetParameter(index++).Value = job.Profile; - cmd.GetParameter(index++).Value = job.Quality; - cmd.GetParameter(index++).Value = job.Bitrate; - cmd.GetParameter(index++).Value = job.Status.ToString(); - cmd.GetParameter(index++).Value = job.Progress; - cmd.GetParameter(index++).Value = job.UserId; - cmd.GetParameter(index++).Value = string.Join(",", job.RequestedItemIds.ToArray()); - cmd.GetParameter(index++).Value = job.Category; - cmd.GetParameter(index++).Value = job.ParentId; - cmd.GetParameter(index++).Value = job.UnwatchedOnly; - cmd.GetParameter(index++).Value = job.ItemLimit; - cmd.GetParameter(index++).Value = job.SyncNewContent; - cmd.GetParameter(index++).Value = job.DateCreated; - cmd.GetParameter(index++).Value = job.DateLastModified; - cmd.GetParameter(index++).Value = job.ItemCount; - - cmd.Transaction = transaction; + using (var cmd = connection.CreateCommand()) + { + if (insert) + { + cmd.CommandText = "insert into SyncJobs (Id, TargetId, Name, Profile, Quality, Bitrate, Status, Progress, UserId, ItemIds, Category, ParentId, UnwatchedOnly, ItemLimit, SyncNewContent, DateCreated, DateLastModified, ItemCount) values (@Id, @TargetId, @Name, @Profile, @Quality, @Bitrate, @Status, @Progress, @UserId, @ItemIds, @Category, @ParentId, @UnwatchedOnly, @ItemLimit, @SyncNewContent, @DateCreated, @DateLastModified, @ItemCount)"; + + cmd.Parameters.Add(cmd, "@Id"); + cmd.Parameters.Add(cmd, "@TargetId"); + cmd.Parameters.Add(cmd, "@Name"); + cmd.Parameters.Add(cmd, "@Profile"); + cmd.Parameters.Add(cmd, "@Quality"); + cmd.Parameters.Add(cmd, "@Bitrate"); + cmd.Parameters.Add(cmd, "@Status"); + cmd.Parameters.Add(cmd, "@Progress"); + cmd.Parameters.Add(cmd, "@UserId"); + cmd.Parameters.Add(cmd, "@ItemIds"); + cmd.Parameters.Add(cmd, "@Category"); + cmd.Parameters.Add(cmd, "@ParentId"); + cmd.Parameters.Add(cmd, "@UnwatchedOnly"); + cmd.Parameters.Add(cmd, "@ItemLimit"); + cmd.Parameters.Add(cmd, "@SyncNewContent"); + cmd.Parameters.Add(cmd, "@DateCreated"); + cmd.Parameters.Add(cmd, "@DateLastModified"); + cmd.Parameters.Add(cmd, "@ItemCount"); + } + else + { + cmd.CommandText = "update SyncJobs set TargetId=@TargetId,Name=@Name,Profile=@Profile,Quality=@Quality,Bitrate=@Bitrate,Status=@Status,Progress=@Progress,UserId=@UserId,ItemIds=@ItemIds,Category=@Category,ParentId=@ParentId,UnwatchedOnly=@UnwatchedOnly,ItemLimit=@ItemLimit,SyncNewContent=@SyncNewContent,DateCreated=@DateCreated,DateLastModified=@DateLastModified,ItemCount=@ItemCount where Id=@Id"; + + cmd.Parameters.Add(cmd, "@Id"); + cmd.Parameters.Add(cmd, "@TargetId"); + cmd.Parameters.Add(cmd, "@Name"); + cmd.Parameters.Add(cmd, "@Profile"); + cmd.Parameters.Add(cmd, "@Quality"); + cmd.Parameters.Add(cmd, "@Bitrate"); + cmd.Parameters.Add(cmd, "@Status"); + cmd.Parameters.Add(cmd, "@Progress"); + cmd.Parameters.Add(cmd, "@UserId"); + cmd.Parameters.Add(cmd, "@ItemIds"); + cmd.Parameters.Add(cmd, "@Category"); + cmd.Parameters.Add(cmd, "@ParentId"); + cmd.Parameters.Add(cmd, "@UnwatchedOnly"); + cmd.Parameters.Add(cmd, "@ItemLimit"); + cmd.Parameters.Add(cmd, "@SyncNewContent"); + cmd.Parameters.Add(cmd, "@DateCreated"); + cmd.Parameters.Add(cmd, "@DateLastModified"); + cmd.Parameters.Add(cmd, "@ItemCount"); + } - cmd.ExecuteNonQuery(); + IDbTransaction transaction = null; - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } + try + { + transaction = connection.BeginTransaction(); + + var index = 0; + + cmd.GetParameter(index++).Value = new Guid(job.Id); + cmd.GetParameter(index++).Value = job.TargetId; + cmd.GetParameter(index++).Value = job.Name; + cmd.GetParameter(index++).Value = job.Profile; + cmd.GetParameter(index++).Value = job.Quality; + cmd.GetParameter(index++).Value = job.Bitrate; + cmd.GetParameter(index++).Value = job.Status.ToString(); + cmd.GetParameter(index++).Value = job.Progress; + cmd.GetParameter(index++).Value = job.UserId; + cmd.GetParameter(index++).Value = string.Join(",", job.RequestedItemIds.ToArray()); + cmd.GetParameter(index++).Value = job.Category; + cmd.GetParameter(index++).Value = job.ParentId; + cmd.GetParameter(index++).Value = job.UnwatchedOnly; + cmd.GetParameter(index++).Value = job.ItemLimit; + cmd.GetParameter(index++).Value = job.SyncNewContent; + cmd.GetParameter(index++).Value = job.DateCreated; + cmd.GetParameter(index++).Value = job.DateLastModified; + cmd.GetParameter(index++).Value = job.ItemCount; + + cmd.Transaction = transaction; + + cmd.ExecuteNonQuery(); + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save record:", e); + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to save record:", 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(); } } @@ -374,56 +312,66 @@ namespace MediaBrowser.Server.Implementations.Sync } CheckDisposed(); - - await WriteLock.WaitAsync().ConfigureAwait(false); - IDbTransaction transaction = null; - - try - { - transaction = _connection.BeginTransaction(); - - var index = 0; - - _deleteJobCommand.GetParameter(index++).Value = new Guid(id); - _deleteJobCommand.Transaction = transaction; - _deleteJobCommand.ExecuteNonQuery(); - - index = 0; - _deleteJobItemsCommand.GetParameter(index++).Value = id; - _deleteJobItemsCommand.Transaction = transaction; - _deleteJobItemsCommand.ExecuteNonQuery(); - - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } - - throw; - } - catch (Exception e) + using (var connection = await CreateConnection().ConfigureAwait(false)) { - Logger.ErrorException("Failed to save record:", e); - - if (transaction != null) + using (var deleteJobCommand = connection.CreateCommand()) { - transaction.Rollback(); - } - - throw; - } - finally - { - if (transaction != null) - { - transaction.Dispose(); + using (var deleteJobItemsCommand = connection.CreateCommand()) + { + IDbTransaction transaction = null; + + try + { + // _deleteJobCommand + deleteJobCommand.CommandText = "delete from SyncJobs where Id=@Id"; + deleteJobCommand.Parameters.Add(deleteJobCommand, "@Id"); + + transaction = connection.BeginTransaction(); + + deleteJobCommand.GetParameter(0).Value = new Guid(id); + deleteJobCommand.Transaction = transaction; + deleteJobCommand.ExecuteNonQuery(); + + // _deleteJobItemsCommand + deleteJobItemsCommand.CommandText = "delete from SyncJobItems where JobId=@JobId"; + deleteJobItemsCommand.Parameters.Add(deleteJobItemsCommand, "@JobId"); + + deleteJobItemsCommand.GetParameter(0).Value = id; + deleteJobItemsCommand.Transaction = transaction; + deleteJobItemsCommand.ExecuteNonQuery(); + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to save record:", e); + + if (transaction != null) + { + transaction.Rollback(); + } + + throw; + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + } + } } - - WriteLock.Release(); } } @@ -435,83 +383,86 @@ namespace MediaBrowser.Server.Implementations.Sync } CheckDisposed(); - - using (var cmd = _connection.CreateCommand()) + + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = BaseJobSelectText; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = BaseJobSelectText; - var whereClauses = new List<string>(); + var whereClauses = new List<string>(); - if (query.Statuses.Length > 0) - { - var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray()); + if (query.Statuses.Length > 0) + { + var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray()); - whereClauses.Add(string.Format("Status in ({0})", statuses)); - } - if (!string.IsNullOrWhiteSpace(query.TargetId)) - { - whereClauses.Add("TargetId=@TargetId"); - cmd.Parameters.Add(cmd, "@TargetId", DbType.String).Value = query.TargetId; - } - if (!string.IsNullOrWhiteSpace(query.UserId)) - { - whereClauses.Add("UserId=@UserId"); - cmd.Parameters.Add(cmd, "@UserId", DbType.String).Value = query.UserId; - } - if (query.SyncNewContent.HasValue) - { - whereClauses.Add("SyncNewContent=@SyncNewContent"); - cmd.Parameters.Add(cmd, "@SyncNewContent", DbType.Boolean).Value = query.SyncNewContent.Value; - } + whereClauses.Add(string.Format("Status in ({0})", statuses)); + } + if (!string.IsNullOrWhiteSpace(query.TargetId)) + { + whereClauses.Add("TargetId=@TargetId"); + cmd.Parameters.Add(cmd, "@TargetId", DbType.String).Value = query.TargetId; + } + if (!string.IsNullOrWhiteSpace(query.UserId)) + { + whereClauses.Add("UserId=@UserId"); + cmd.Parameters.Add(cmd, "@UserId", DbType.String).Value = query.UserId; + } + if (query.SyncNewContent.HasValue) + { + whereClauses.Add("SyncNewContent=@SyncNewContent"); + cmd.Parameters.Add(cmd, "@SyncNewContent", DbType.Boolean).Value = query.SyncNewContent.Value; + } - cmd.CommandText += " mainTable"; + cmd.CommandText += " mainTable"; - var whereTextWithoutPaging = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + var whereTextWithoutPaging = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - var startIndex = query.StartIndex ?? 0; - if (startIndex > 0) - { - whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM SyncJobs ORDER BY (Select Max(DateLastModified) from SyncJobs where TargetId=mainTable.TargetId) DESC, DateLastModified DESC LIMIT {0})", - startIndex.ToString(_usCulture))); - } + var startIndex = query.StartIndex ?? 0; + if (startIndex > 0) + { + whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM SyncJobs ORDER BY (Select Max(DateLastModified) from SyncJobs where TargetId=mainTable.TargetId) DESC, DateLastModified DESC LIMIT {0})", + startIndex.ToString(_usCulture))); + } - if (whereClauses.Count > 0) - { - cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray()); - } + if (whereClauses.Count > 0) + { + cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray()); + } - cmd.CommandText += " ORDER BY (Select Max(DateLastModified) from SyncJobs where TargetId=mainTable.TargetId) DESC, DateLastModified DESC"; + cmd.CommandText += " ORDER BY (Select Max(DateLastModified) from SyncJobs where TargetId=mainTable.TargetId) DESC, DateLastModified DESC"; - if (query.Limit.HasValue) - { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); - } + if (query.Limit.HasValue) + { + cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); + } - cmd.CommandText += "; select count (Id) from SyncJobs" + whereTextWithoutPaging; + cmd.CommandText += "; select count (Id) from SyncJobs" + whereTextWithoutPaging; - var list = new List<SyncJob>(); - var count = 0; + var list = new List<SyncJob>(); + var count = 0; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) - { - while (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { - list.Add(GetJob(reader)); + while (reader.Read()) + { + list.Add(GetJob(reader)); + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } } - if (reader.NextResult() && reader.Read()) + return new QueryResult<SyncJob>() { - count = reader.GetInt32(0); - } + Items = list.ToArray(), + TotalRecordCount = count + }; } - - return new QueryResult<SyncJob>() - { - Items = list.ToArray(), - TotalRecordCount = count - }; } } @@ -523,25 +474,28 @@ namespace MediaBrowser.Server.Implementations.Sync } CheckDisposed(); - + var guid = new Guid(id); - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = BaseJobItemSelectText + " where Id=@Id"; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = BaseJobItemSelectText + " where Id=@Id"; - cmd.Parameters.Add(cmd, "@Id", DbType.Guid).Value = guid; + cmd.Parameters.Add(cmd, "@Id", DbType.Guid).Value = guid; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) - { - if (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) { - return GetJobItem(reader); + if (reader.Read()) + { + return GetJobItem(reader); + } } } - } - return null; + return null; + } } private QueryResult<T> GetJobItemReader<T>(SyncJobItemQuery query, string baseSelectText, Func<IDataReader, T> itemFactory) @@ -551,81 +505,84 @@ namespace MediaBrowser.Server.Implementations.Sync throw new ArgumentNullException("query"); } - using (var cmd = _connection.CreateCommand()) + using (var connection = CreateConnection(true).Result) { - cmd.CommandText = baseSelectText; + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = baseSelectText; - var whereClauses = new List<string>(); + var whereClauses = new List<string>(); - if (!string.IsNullOrWhiteSpace(query.JobId)) - { - whereClauses.Add("JobId=@JobId"); - cmd.Parameters.Add(cmd, "@JobId", DbType.String).Value = query.JobId; - } - if (!string.IsNullOrWhiteSpace(query.ItemId)) - { - whereClauses.Add("ItemId=@ItemId"); - cmd.Parameters.Add(cmd, "@ItemId", DbType.String).Value = query.ItemId; - } - if (!string.IsNullOrWhiteSpace(query.TargetId)) - { - whereClauses.Add("TargetId=@TargetId"); - cmd.Parameters.Add(cmd, "@TargetId", DbType.String).Value = query.TargetId; - } + if (!string.IsNullOrWhiteSpace(query.JobId)) + { + whereClauses.Add("JobId=@JobId"); + cmd.Parameters.Add(cmd, "@JobId", DbType.String).Value = query.JobId; + } + if (!string.IsNullOrWhiteSpace(query.ItemId)) + { + whereClauses.Add("ItemId=@ItemId"); + cmd.Parameters.Add(cmd, "@ItemId", DbType.String).Value = query.ItemId; + } + if (!string.IsNullOrWhiteSpace(query.TargetId)) + { + whereClauses.Add("TargetId=@TargetId"); + cmd.Parameters.Add(cmd, "@TargetId", DbType.String).Value = query.TargetId; + } - if (query.Statuses.Length > 0) - { - var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray()); + if (query.Statuses.Length > 0) + { + var statuses = string.Join(",", query.Statuses.Select(i => "'" + i.ToString() + "'").ToArray()); - whereClauses.Add(string.Format("Status in ({0})", statuses)); - } + whereClauses.Add(string.Format("Status in ({0})", statuses)); + } - var whereTextWithoutPaging = whereClauses.Count == 0 ? - string.Empty : - " where " + string.Join(" AND ", whereClauses.ToArray()); + var whereTextWithoutPaging = whereClauses.Count == 0 ? + string.Empty : + " where " + string.Join(" AND ", whereClauses.ToArray()); - var startIndex = query.StartIndex ?? 0; - if (startIndex > 0) - { - whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM SyncJobItems ORDER BY JobItemIndex, DateCreated LIMIT {0})", - startIndex.ToString(_usCulture))); - } + var startIndex = query.StartIndex ?? 0; + if (startIndex > 0) + { + whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM SyncJobItems ORDER BY JobItemIndex, DateCreated LIMIT {0})", + startIndex.ToString(_usCulture))); + } - if (whereClauses.Count > 0) - { - cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray()); - } + if (whereClauses.Count > 0) + { + cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray()); + } - cmd.CommandText += " ORDER BY JobItemIndex, DateCreated"; + cmd.CommandText += " ORDER BY JobItemIndex, DateCreated"; - if (query.Limit.HasValue) - { - cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); - } + if (query.Limit.HasValue) + { + cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(_usCulture); + } - cmd.CommandText += "; select count (Id) from SyncJobItems" + whereTextWithoutPaging; + cmd.CommandText += "; select count (Id) from SyncJobItems" + whereTextWithoutPaging; - var list = new List<T>(); - var count = 0; + var list = new List<T>(); + var count = 0; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) - { - while (reader.Read()) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { - list.Add(itemFactory(reader)); + while (reader.Read()) + { + list.Add(itemFactory(reader)); + } + + if (reader.NextResult() && reader.Read()) + { + count = reader.GetInt32(0); + } } - if (reader.NextResult() && reader.Read()) + return new QueryResult<T>() { - count = reader.GetInt32(0); - } + Items = list.ToArray(), + TotalRecordCount = count + }; } - - return new QueryResult<T>() - { - Items = list.ToArray(), - TotalRecordCount = count - }; } } @@ -641,15 +598,15 @@ namespace MediaBrowser.Server.Implementations.Sync public Task Create(SyncJobItem jobItem) { - return InsertOrUpdate(jobItem, _insertJobItemCommand); + return InsertOrUpdate(jobItem, true); } public Task Update(SyncJobItem jobItem) { - return InsertOrUpdate(jobItem, _updateJobItemCommand); + return InsertOrUpdate(jobItem, false); } - private async Task InsertOrUpdate(SyncJobItem jobItem, IDbCommand cmd) + private async Task InsertOrUpdate(SyncJobItem jobItem, bool insert) { if (jobItem == null) { @@ -657,68 +614,114 @@ namespace MediaBrowser.Server.Implementations.Sync } CheckDisposed(); - - await WriteLock.WaitAsync().ConfigureAwait(false); - IDbTransaction transaction = null; - - try + using (var connection = await CreateConnection().ConfigureAwait(false)) { - transaction = _connection.BeginTransaction(); - - var index = 0; - - cmd.GetParameter(index++).Value = new Guid(jobItem.Id); - cmd.GetParameter(index++).Value = jobItem.ItemId; - cmd.GetParameter(index++).Value = jobItem.ItemName; - cmd.GetParameter(index++).Value = jobItem.MediaSourceId; - cmd.GetParameter(index++).Value = jobItem.JobId; - cmd.GetParameter(index++).Value = jobItem.TemporaryPath; - cmd.GetParameter(index++).Value = jobItem.OutputPath; - cmd.GetParameter(index++).Value = jobItem.Status.ToString(); - cmd.GetParameter(index++).Value = jobItem.TargetId; - cmd.GetParameter(index++).Value = jobItem.DateCreated; - cmd.GetParameter(index++).Value = jobItem.Progress; - cmd.GetParameter(index++).Value = _json.SerializeToString(jobItem.AdditionalFiles); - cmd.GetParameter(index++).Value = jobItem.MediaSource == null ? null : _json.SerializeToString(jobItem.MediaSource); - cmd.GetParameter(index++).Value = jobItem.IsMarkedForRemoval; - cmd.GetParameter(index++).Value = jobItem.JobItemIndex; - cmd.GetParameter(index++).Value = jobItem.ItemDateModifiedTicks; - - cmd.Transaction = transaction; + using (var cmd = connection.CreateCommand()) + { + if (insert) + { + cmd.CommandText = "insert into SyncJobItems (Id, ItemId, ItemName, MediaSourceId, JobId, TemporaryPath, OutputPath, Status, TargetId, DateCreated, Progress, AdditionalFiles, MediaSource, IsMarkedForRemoval, JobItemIndex, ItemDateModifiedTicks) values (@Id, @ItemId, @ItemName, @MediaSourceId, @JobId, @TemporaryPath, @OutputPath, @Status, @TargetId, @DateCreated, @Progress, @AdditionalFiles, @MediaSource, @IsMarkedForRemoval, @JobItemIndex, @ItemDateModifiedTicks)"; + + cmd.Parameters.Add(cmd, "@Id"); + cmd.Parameters.Add(cmd, "@ItemId"); + cmd.Parameters.Add(cmd, "@ItemName"); + cmd.Parameters.Add(cmd, "@MediaSourceId"); + cmd.Parameters.Add(cmd, "@JobId"); + cmd.Parameters.Add(cmd, "@TemporaryPath"); + cmd.Parameters.Add(cmd, "@OutputPath"); + cmd.Parameters.Add(cmd, "@Status"); + cmd.Parameters.Add(cmd, "@TargetId"); + cmd.Parameters.Add(cmd, "@DateCreated"); + cmd.Parameters.Add(cmd, "@Progress"); + cmd.Parameters.Add(cmd, "@AdditionalFiles"); + cmd.Parameters.Add(cmd, "@MediaSource"); + cmd.Parameters.Add(cmd, "@IsMarkedForRemoval"); + cmd.Parameters.Add(cmd, "@JobItemIndex"); + cmd.Parameters.Add(cmd, "@ItemDateModifiedTicks"); + } + else + { + // cmd + cmd.CommandText = "update SyncJobItems set ItemId=@ItemId,ItemName=@ItemName,MediaSourceId=@MediaSourceId,JobId=@JobId,TemporaryPath=@TemporaryPath,OutputPath=@OutputPath,Status=@Status,TargetId=@TargetId,DateCreated=@DateCreated,Progress=@Progress,AdditionalFiles=@AdditionalFiles,MediaSource=@MediaSource,IsMarkedForRemoval=@IsMarkedForRemoval,JobItemIndex=@JobItemIndex,ItemDateModifiedTicks=@ItemDateModifiedTicks where Id=@Id"; + + cmd.Parameters.Add(cmd, "@Id"); + cmd.Parameters.Add(cmd, "@ItemId"); + cmd.Parameters.Add(cmd, "@ItemName"); + cmd.Parameters.Add(cmd, "@MediaSourceId"); + cmd.Parameters.Add(cmd, "@JobId"); + cmd.Parameters.Add(cmd, "@TemporaryPath"); + cmd.Parameters.Add(cmd, "@OutputPath"); + cmd.Parameters.Add(cmd, "@Status"); + cmd.Parameters.Add(cmd, "@TargetId"); + cmd.Parameters.Add(cmd, "@DateCreated"); + cmd.Parameters.Add(cmd, "@Progress"); + cmd.Parameters.Add(cmd, "@AdditionalFiles"); + cmd.Parameters.Add(cmd, "@MediaSource"); + cmd.Parameters.Add(cmd, "@IsMarkedForRemoval"); + cmd.Parameters.Add(cmd, "@JobItemIndex"); + cmd.Parameters.Add(cmd, "@ItemDateModifiedTicks"); + } - cmd.ExecuteNonQuery(); + IDbTransaction transaction = null; - transaction.Commit(); - } - catch (OperationCanceledException) - { - if (transaction != null) - { - transaction.Rollback(); - } + try + { + transaction = connection.BeginTransaction(); + + var index = 0; + + cmd.GetParameter(index++).Value = new Guid(jobItem.Id); + cmd.GetParameter(index++).Value = jobItem.ItemId; + cmd.GetParameter(index++).Value = jobItem.ItemName; + cmd.GetParameter(index++).Value = jobItem.MediaSourceId; + cmd.GetParameter(index++).Value = jobItem.JobId; + cmd.GetParameter(index++).Value = jobItem.TemporaryPath; + cmd.GetParameter(index++).Value = jobItem.OutputPath; + cmd.GetParameter(index++).Value = jobItem.Status.ToString(); + cmd.GetParameter(index++).Value = jobItem.TargetId; + cmd.GetParameter(index++).Value = jobItem.DateCreated; + cmd.GetParameter(index++).Value = jobItem.Progress; + cmd.GetParameter(index++).Value = _json.SerializeToString(jobItem.AdditionalFiles); + cmd.GetParameter(index++).Value = jobItem.MediaSource == null ? null : _json.SerializeToString(jobItem.MediaSource); + cmd.GetParameter(index++).Value = jobItem.IsMarkedForRemoval; + cmd.GetParameter(index++).Value = jobItem.JobItemIndex; + cmd.GetParameter(index++).Value = jobItem.ItemDateModifiedTicks; + + cmd.Transaction = transaction; + + cmd.ExecuteNonQuery(); + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } - throw; - } - catch (Exception e) - { - Logger.ErrorException("Failed to save record:", e); + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to save record:", 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(); } } @@ -809,19 +812,5 @@ namespace MediaBrowser.Server.Implementations.Sync return item; } - - protected override void CloseConnection() - { - if (_connection != null) - { - if (_connection.IsOpen()) - { - _connection.Close(); - } - - _connection.Dispose(); - _connection = null; - } - } } } diff --git a/MediaBrowser.Server.Mono/Native/DbConnector.cs b/MediaBrowser.Server.Mono/Native/DbConnector.cs index 7553dbe1a..5ad3ecfef 100644 --- a/MediaBrowser.Server.Mono/Native/DbConnector.cs +++ b/MediaBrowser.Server.Mono/Native/DbConnector.cs @@ -16,9 +16,9 @@ namespace MediaBrowser.Server.Mono.Native _logger = logger; } - public Task<IDbConnection> Connect(string dbPath, int? cacheSize = null) + public Task<IDbConnection> Connect(string dbPath, bool isReadOnly, bool enablePooling = false, int? cacheSize = null) { - return SqliteExtensions.ConnectToDb(dbPath, cacheSize, _logger); + return SqliteExtensions.ConnectToDb(dbPath, isReadOnly, enablePooling, cacheSize, _logger); } } }
\ No newline at end of file diff --git a/MediaBrowser.Server.Startup.Common/ApplicationHost.cs b/MediaBrowser.Server.Startup.Common/ApplicationHost.cs index f344e6f1c..9e869478c 100644 --- a/MediaBrowser.Server.Startup.Common/ApplicationHost.cs +++ b/MediaBrowser.Server.Startup.Common/ApplicationHost.cs @@ -424,11 +424,11 @@ namespace MediaBrowser.Server.Startup.Common UserRepository = await GetUserRepository().ConfigureAwait(false); RegisterSingleInstance(UserRepository); - var displayPreferencesRepo = new SqliteDisplayPreferencesRepository(LogManager, JsonSerializer, ApplicationPaths); + var displayPreferencesRepo = new SqliteDisplayPreferencesRepository(LogManager, JsonSerializer, ApplicationPaths, NativeApp.GetDbConnector()); DisplayPreferencesRepository = displayPreferencesRepo; RegisterSingleInstance(DisplayPreferencesRepository); - var itemRepo = new SqliteItemRepository(ServerConfigurationManager, JsonSerializer, LogManager); + var itemRepo = new SqliteItemRepository(ServerConfigurationManager, JsonSerializer, LogManager, NativeApp.GetDbConnector()); ItemRepository = itemRepo; RegisterSingleInstance(ItemRepository); @@ -553,8 +553,8 @@ namespace MediaBrowser.Server.Startup.Common RegisterSingleInstance(NativeApp.GetPowerManagement()); - var sharingRepo = new SharingRepository(LogManager, ApplicationPaths); - await sharingRepo.Initialize(NativeApp.GetDbConnector()).ConfigureAwait(false); + var sharingRepo = new SharingRepository(LogManager, ApplicationPaths, NativeApp.GetDbConnector()); + await sharingRepo.Initialize().ConfigureAwait(false); RegisterSingleInstance<ISharingManager>(new SharingManager(sharingRepo, ServerConfigurationManager, LibraryManager, this)); RegisterSingleInstance<ISsdpHandler>(new SsdpHandler(LogManager.GetLogger("SsdpHandler"), ServerConfigurationManager, this)); @@ -571,7 +571,7 @@ namespace MediaBrowser.Server.Startup.Common SubtitleEncoder = new SubtitleEncoder(LibraryManager, LogManager.GetLogger("SubtitleEncoder"), ApplicationPaths, FileSystemManager, MediaEncoder, JsonSerializer, HttpClient, MediaSourceManager); RegisterSingleInstance(SubtitleEncoder); - await displayPreferencesRepo.Initialize(NativeApp.GetDbConnector()).ConfigureAwait(false); + await displayPreferencesRepo.Initialize().ConfigureAwait(false); await ConfigureUserDataRepositories().ConfigureAwait(false); await itemRepo.Initialize(NativeApp.GetDbConnector()).ConfigureAwait(false); ((LibraryManager)LibraryManager).ItemRepository = ItemRepository; @@ -670,9 +670,9 @@ namespace MediaBrowser.Server.Startup.Common { try { - var repo = new SqliteUserRepository(LogManager, ApplicationPaths, JsonSerializer); + var repo = new SqliteUserRepository(LogManager, ApplicationPaths, JsonSerializer, NativeApp.GetDbConnector()); - await repo.Initialize(NativeApp.GetDbConnector()).ConfigureAwait(false); + await repo.Initialize().ConfigureAwait(false); return repo; } @@ -689,7 +689,7 @@ namespace MediaBrowser.Server.Startup.Common /// <returns>Task{IUserRepository}.</returns> private async Task<IFileOrganizationRepository> GetFileOrganizationRepository() { - var repo = new SqliteFileOrganizationRepository(LogManager, ServerConfigurationManager.ApplicationPaths); + var repo = new SqliteFileOrganizationRepository(LogManager, ServerConfigurationManager.ApplicationPaths, NativeApp.GetDbConnector()); await repo.Initialize(NativeApp.GetDbConnector()).ConfigureAwait(false); @@ -698,27 +698,27 @@ namespace MediaBrowser.Server.Startup.Common private async Task<IAuthenticationRepository> GetAuthenticationRepository() { - var repo = new AuthenticationRepository(LogManager, ServerConfigurationManager.ApplicationPaths); + var repo = new AuthenticationRepository(LogManager, ServerConfigurationManager.ApplicationPaths, NativeApp.GetDbConnector()); - await repo.Initialize(NativeApp.GetDbConnector()).ConfigureAwait(false); + await repo.Initialize().ConfigureAwait(false); return repo; } private async Task<IActivityRepository> GetActivityLogRepository() { - var repo = new ActivityRepository(LogManager, ServerConfigurationManager.ApplicationPaths); + var repo = new ActivityRepository(LogManager, ServerConfigurationManager.ApplicationPaths, NativeApp.GetDbConnector()); - await repo.Initialize(NativeApp.GetDbConnector()).ConfigureAwait(false); + await repo.Initialize().ConfigureAwait(false); return repo; } private async Task<ISyncRepository> GetSyncRepository() { - var repo = new SyncRepository(LogManager, JsonSerializer, ServerConfigurationManager.ApplicationPaths); + var repo = new SyncRepository(LogManager, JsonSerializer, ServerConfigurationManager.ApplicationPaths, NativeApp.GetDbConnector()); - await repo.Initialize(NativeApp.GetDbConnector()).ConfigureAwait(false); + await repo.Initialize().ConfigureAwait(false); return repo; } @@ -729,9 +729,9 @@ namespace MediaBrowser.Server.Startup.Common /// <returns>Task.</returns> private async Task ConfigureNotificationsRepository() { - var repo = new SqliteNotificationsRepository(LogManager, ApplicationPaths); + var repo = new SqliteNotificationsRepository(LogManager, ApplicationPaths, NativeApp.GetDbConnector()); - await repo.Initialize(NativeApp.GetDbConnector()).ConfigureAwait(false); + await repo.Initialize().ConfigureAwait(false); NotificationsRepository = repo; @@ -744,7 +744,7 @@ namespace MediaBrowser.Server.Startup.Common /// <returns>Task.</returns> private async Task ConfigureUserDataRepositories() { - var repo = new SqliteUserDataRepository(LogManager, ApplicationPaths); + var repo = new SqliteUserDataRepository(LogManager, ApplicationPaths, NativeApp.GetDbConnector()); await repo.Initialize(NativeApp.GetDbConnector()).ConfigureAwait(false); diff --git a/MediaBrowser.ServerApplication/Native/DbConnector.cs b/MediaBrowser.ServerApplication/Native/DbConnector.cs index 6001ac3c0..9aaa96a80 100644 --- a/MediaBrowser.ServerApplication/Native/DbConnector.cs +++ b/MediaBrowser.ServerApplication/Native/DbConnector.cs @@ -16,18 +16,9 @@ namespace MediaBrowser.ServerApplication.Native _logger = logger; } - public async Task<IDbConnection> Connect(string dbPath, int? cacheSize = null) + public Task<IDbConnection> Connect(string dbPath, bool isReadOnly, bool enablePooling = false, int? cacheSize = null) { - try - { - return await SqliteExtensions.ConnectToDb(dbPath, cacheSize, _logger).ConfigureAwait(false); - } - catch (Exception ex) - { - _logger.ErrorException("Error opening database {0}", ex, dbPath); - - throw; - } + return SqliteExtensions.ConnectToDb(dbPath, isReadOnly, enablePooling, cacheSize, _logger); } } }
\ No newline at end of file diff --git a/MediaBrowser.WebDashboard/Api/DashboardService.cs b/MediaBrowser.WebDashboard/Api/DashboardService.cs index 904953cfc..809c3f1a5 100644 --- a/MediaBrowser.WebDashboard/Api/DashboardService.cs +++ b/MediaBrowser.WebDashboard/Api/DashboardService.cs @@ -342,7 +342,9 @@ namespace MediaBrowser.WebDashboard.Api if (string.Equals(mode, "cordova", StringComparison.OrdinalIgnoreCase)) { - DeleteFoldersByName(Path.Combine(bowerPath, "emby-webcomponents"), "fonts"); + DeleteFoldersByName(Path.Combine(bowerPath, "emby-webcomponents", "fonts"), "montserrat"); + DeleteFoldersByName(Path.Combine(bowerPath, "emby-webcomponents", "fonts"), "opensans"); + DeleteFoldersByName(Path.Combine(bowerPath, "emby-webcomponents", "fonts"), "roboto"); } _fileSystem.DeleteDirectory(Path.Combine(bowerPath, "jquery", "src"), true); |