Rework parental ratings (#12615)

3 files changed, 37 insertions, 56 deletions

diff --git a/Jellyfin.Server/Migrations/MigrationRunner.cs b/Jellyfin.Server/Migrations/MigrationRunner.cs
index 49568b087..68a3491b5 100644
--- a/Jellyfin.Server/Migrations/MigrationRunner.cs
+++ b/Jellyfin.Server/Migrations/MigrationRunner.cs
@@ -49,12 +49,12 @@ namespace Jellyfin.Server.Migrations
             typeof(Routines.RemoveDownloadImagesInAdvance),
             typeof(Routines.MigrateAuthenticationDb),
             typeof(Routines.FixPlaylistOwner),
-            typeof(Routines.MigrateRatingLevels),
             typeof(Routines.AddDefaultCastReceivers),
             typeof(Routines.UpdateDefaultPluginRepository),
             typeof(Routines.FixAudioData),
             typeof(Routines.RemoveDuplicatePlaylistChildren),
             typeof(Routines.MigrateLibraryDb),
+            typeof(Routines.MigrateRatingLevels),
             typeof(Routines.MoveTrickplayFiles),
         };
 
diff --git a/Jellyfin.Server/Migrations/Routines/MigrateRatingLevels.cs b/Jellyfin.Server/Migrations/Routines/MigrateRatingLevels.cs
index 9c2184029..c38beb723 100644
--- a/Jellyfin.Server/Migrations/Routines/MigrateRatingLevels.cs
+++ b/Jellyfin.Server/Migrations/Routines/MigrateRatingLevels.cs
@@ -1,36 +1,33 @@
 using System;
-using System.Globalization;
-using System.IO;
-using Emby.Server.Implementations.Data;
-using MediaBrowser.Controller;
+using System.Linq;
+using Jellyfin.Database.Implementations;
 using MediaBrowser.Model.Globalization;
-using Microsoft.Data.Sqlite;
+using Microsoft.EntityFrameworkCore;
 using Microsoft.Extensions.Logging;
 
 namespace Jellyfin.Server.Migrations.Routines
 {
     /// <summary>
-    /// Migrate rating levels to new rating level system.
+    /// Migrate rating levels.
     /// </summary>
-    internal class MigrateRatingLevels : IMigrationRoutine
+    internal class MigrateRatingLevels : IDatabaseMigrationRoutine
     {
-        private const string DbFilename = "library.db";
         private readonly ILogger<MigrateRatingLevels> _logger;
-        private readonly IServerApplicationPaths _applicationPaths;
+        private readonly IDbContextFactory<JellyfinDbContext> _provider;
         private readonly ILocalizationManager _localizationManager;
 
         public MigrateRatingLevels(
-            IServerApplicationPaths applicationPaths,
+            IDbContextFactory<JellyfinDbContext> provider,
             ILoggerFactory loggerFactory,
             ILocalizationManager localizationManager)
         {
-            _applicationPaths = applicationPaths;
+            _provider = provider;
             _localizationManager = localizationManager;
             _logger = loggerFactory.CreateLogger<MigrateRatingLevels>();
         }
 
         /// <inheritdoc/>
-        public Guid Id => Guid.Parse("{73DAB92A-178B-48CD-B05B-FE18733ACDC8}");
+        public Guid Id => Guid.Parse("{98724538-EB11-40E3-931A-252C55BDDE7A}");
 
         /// <inheritdoc/>
         public string Name => "MigrateRatingLevels";
@@ -41,54 +38,37 @@ namespace Jellyfin.Server.Migrations.Routines
         /// <inheritdoc/>
         public void Perform()
         {
-            var dbPath = Path.Combine(_applicationPaths.DataPath, DbFilename);
-
-            // Back up the database before modifying any entries
-            for (int i = 1; ; i++)
+            _logger.LogInformation("Recalculating parental rating levels based on rating string.");
+            using var context = _provider.CreateDbContext();
+            using var transaction = context.Database.BeginTransaction();
+            var ratings = context.BaseItems.AsNoTracking().Select(e => e.OfficialRating).Distinct();
+            foreach (var rating in ratings)
             {
-                var bakPath = string.Format(CultureInfo.InvariantCulture, "{0}.bak{1}", dbPath, i);
-                if (!File.Exists(bakPath))
+                if (string.IsNullOrEmpty(rating))
                 {
-                    try
-                    {
-                        File.Copy(dbPath, bakPath);
-                        _logger.LogInformation("Library database backed up to {BackupPath}", bakPath);
-                        break;
-                    }
-                    catch (Exception ex)
-                    {
-                        _logger.LogError(ex, "Cannot make a backup of {Library} at path {BackupPath}", DbFilename, bakPath);
-                        throw;
-                    }
+                    int? value = null;
+                    context.BaseItems
+                        .Where(e => e.OfficialRating == null || e.OfficialRating == string.Empty)
+                        .ExecuteUpdate(f => f.SetProperty(e => e.InheritedParentalRatingValue, value));
+                    context.BaseItems
+                        .Where(e => e.OfficialRating == null || e.OfficialRating == string.Empty)
+                        .ExecuteUpdate(f => f.SetProperty(e => e.InheritedParentalRatingSubValue, value));
                 }
-            }
-
-            // Migrate parental rating strings to new levels
-            _logger.LogInformation("Recalculating parental rating levels based on rating string.");
-            using var connection = new SqliteConnection($"Filename={dbPath}");
-            connection.Open();
-            using (var transaction = connection.BeginTransaction())
-            {
-                var queryResult = connection.Query("SELECT DISTINCT OfficialRating FROM TypedBaseItems");
-                foreach (var entry in queryResult)
+                else
                 {
-                    if (!entry.TryGetString(0, out var ratingString) || string.IsNullOrEmpty(ratingString))
-                    {
-                        connection.Execute("UPDATE TypedBaseItems SET InheritedParentalRatingValue = NULL WHERE OfficialRating IS NULL OR OfficialRating='';");
-                    }
-                    else
-                    {
-                        var ratingValue = _localizationManager.GetRatingLevel(ratingString)?.ToString(CultureInfo.InvariantCulture) ?? "NULL";
-
-                        using var statement = connection.PrepareStatement("UPDATE TypedBaseItems SET InheritedParentalRatingValue = @Value WHERE OfficialRating = @Rating;");
-                        statement.TryBind("@Value", ratingValue);
-                        statement.TryBind("@Rating", ratingString);
-                        statement.ExecuteNonQuery();
-                    }
+                    var ratingValue = _localizationManager.GetRatingScore(rating);
+                    var score = ratingValue?.Score;
+                    var subScore = ratingValue?.SubScore;
+                    context.BaseItems
+                        .Where(e => e.OfficialRating == rating)
+                        .ExecuteUpdate(f => f.SetProperty(e => e.InheritedParentalRatingValue, score));
+                    context.BaseItems
+                        .Where(e => e.OfficialRating == rating)
+                        .ExecuteUpdate(f => f.SetProperty(e => e.InheritedParentalRatingSubValue, subScore));
                 }
-
-                transaction.Commit();
             }
+
+            transaction.Commit();
         }
     }
 }
diff --git a/Jellyfin.Server/Migrations/Routines/MigrateUserDb.cs b/Jellyfin.Server/Migrations/Routines/MigrateUserDb.cs
index c40560660..1b5fab7a8 100644
--- a/Jellyfin.Server/Migrations/Routines/MigrateUserDb.cs
+++ b/Jellyfin.Server/Migrations/Routines/MigrateUserDb.cs
@@ -112,7 +112,8 @@ namespace Jellyfin.Server.Migrations.Routines
                     {
                         Id = entry.GetGuid(1),
                         InternalId = entry.GetInt64(0),
-                        MaxParentalAgeRating = policy.MaxParentalRating,
+                        MaxParentalRatingScore = policy.MaxParentalRating,
+                        MaxParentalRatingSubScore = null,
                         EnableUserPreferenceAccess = policy.EnableUserPreferenceAccess,
                         RemoteClientBitrateLimit = policy.RemoteClientBitrateLimit,
                         InvalidLoginAttemptCount = policy.InvalidLoginAttemptCount,