jellyfin-ha-src/tests/Jellyfin.Server.Tests/Migrations/PostgreSqlStartupMigrationTests.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Threading;
using System.Threading.Tasks;
using Emby.Server.Implementations;
using Emby.Server.Implementations.Branding;
using Emby.Server.Implementations.Configuration;
using Emby.Server.Implementations.Serialization;
using Jellyfin.Database.Implementations;
using Jellyfin.Database.Implementations.DbConfiguration;
using Jellyfin.Database.Implementations.Locking;
using Jellyfin.Database.Providers.PostgreSQL;
using Jellyfin.LiveTv.Configuration;
using Jellyfin.Server.Implementations.DatabaseConfiguration;
using Jellyfin.Server.Migrations;
using Jellyfin.Server.Migrations.Stages;
using Jellyfin.Server.ServerSetupApp;
using MediaBrowser.Common.Configuration;
using MediaBrowser.Common.Net;
using MediaBrowser.Controller;
using MediaBrowser.Controller.Configuration;
using MediaBrowser.Controller.Entities;
using MediaBrowser.Controller.IO;
using MediaBrowser.Controller.Library;
using MediaBrowser.Controller.Persistence;
using MediaBrowser.Controller.Playlists;
using MediaBrowser.Controller.Providers;
using MediaBrowser.Controller.Trickplay;
using MediaBrowser.MediaEncoding.Configuration;
using MediaBrowser.Model.Configuration;
using MediaBrowser.Model.Entities;
using MediaBrowser.Model.Globalization;
using MediaBrowser.Model.IO;
using MediaBrowser.Model.Serialization;
using MediaBrowser.XbmcMetadata.Configuration;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Abstractions;
using Moq;
using Npgsql;
using Xunit;

namespace Jellyfin.Server.Tests.Migrations;

/// <summary>
/// Runs the startup migration sequence that <c>Program</c> runs - both database stages, schema and code
/// migrations interleaved by <see cref="JellyfinMigrationService"/> - against a real PostgreSQL holding a
/// library representative of one being upgraded.
/// </summary>
[Trait("Category", "RequiresDocker")]
public sealed class PostgreSqlStartupMigrationTests : IAsyncLifetime
{
    private const string BaselineMigrationId = "20260305010333_InitialPostgreSql";

    private const string MovieItemId = "11111111-1111-1111-1111-111111111111";
    private const string UnratedMovieItemId = "22222222-2222-2222-2222-222222222222";
    private const string EmptyRatingMovieItemId = "33333333-3333-3333-3333-333333333333";
    private const string TrailerItemId = "44444444-4444-4444-4444-444444444444";
    private const string SeriesItemId = "55555555-5555-5555-5555-555555555555";
    private const string EpisodeItemId = "66666666-6666-6666-6666-666666666666";
    private const string ArtistItemId = "77777777-7777-7777-7777-777777777777";
    private const string DuplicateArtistItemId = "88888888-8888-8888-8888-888888888888";
    private const string AliceId = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa";
    private const string BobId = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb";

    /// <summary>
    /// The last code migration the build that wrote the database shipped. Everything before it is recorded as
    /// applied, so the pending set is the one a real upgrade faces; everything from here on reads and writes
    /// the seeded library. The routines before it import a SQLite library.db that a PostgreSQL install never
    /// had, so they only ever run on a database the previous build already marked them on.
    /// </summary>
    private static readonly DateTime _previousReleaseOrder = new(2026, 2, 7, 0, 0, 0, DateTimeKind.Unspecified);

    private PostgreSqlTestServer _server = null!;

    /// <inheritdoc/>
    public async ValueTask InitializeAsync()
    {
        _server = await PostgreSqlTestServer.StartAsync().ConfigureAwait(false);
    }

    /// <inheritdoc/>
    public async ValueTask DisposeAsync()
    {
        await _server.DisposeAsync().ConfigureAwait(false);
    }

    /// <summary>
    /// A library written by the previous build has to reach the current schema through the whole startup
    /// sequence, not through the schema migrations alone.
    /// </summary>
    /// <returns>A <see cref="Task"/> representing the asynchronous operation.</returns>
    [Fact]
    public async Task ExistingLibrary_CompletesEveryStage()
    {
        var cancellationToken = TestContext.Current.CancellationToken;
        var connectionString = await _server.CreateDatabaseAsync("startup_existing", cancellationToken);

        await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
        await MigrateToBaselineAsync(dataSource, cancellationToken);
        await RecordPreviousReleaseMigrationsAsync(dataSource, cancellationToken);
        await SeedLibraryAsync(dataSource, cancellationToken);

        await RunStartupMigrationsAsync(dataSource, cancellationToken);

        await using var context = CreateContext(dataSource);
        Assert.Empty(await context.Database.GetPendingMigrationsAsync(cancellationToken));
        Assert.False(context.Database.HasPendingModelChanges());

        // The rating recalculation is the code migration that reads every distinct rating and writes each
        // group, so its result proves the code migrations really ran against the seeded rows.
        Assert.Equal(13, await RatingValueAsync(dataSource, MovieItemId, cancellationToken));
        Assert.Null(await RatingValueAsync(dataSource, UnratedMovieItemId, cancellationToken));
        Assert.Null(await RatingValueAsync(dataSource, EmptyRatingMovieItemId, cancellationToken));

        // The username code migration has to have filled the column the unique index is built on.
        Assert.Equal("ALICE", await ScalarStringAsync(dataSource, $"""SELECT "NormalizedUsername" FROM "Users" WHERE "Id" = '{AliceId}'""", cancellationToken));
        Assert.Equal("BOB", await ScalarStringAsync(dataSource, $"""SELECT "NormalizedUsername" FROM "Users" WHERE "Id" = '{BobId}'""", cancellationToken));
    }

    /// <summary>
    /// A fresh install goes through the same sequence, starting from the seeding the startup path does on a
    /// database that has never been migrated.
    /// </summary>
    /// <returns>A <see cref="Task"/> representing the asynchronous operation.</returns>
    [Fact]
    public async Task FreshInstall_CompletesEveryStage()
    {
        var cancellationToken = TestContext.Current.CancellationToken;
        var connectionString = await _server.CreateDatabaseAsync("startup_fresh", cancellationToken);

        await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
        await RunStartupMigrationsAsync(dataSource, cancellationToken, seedFirstTimeRun: true);

        await using var context = CreateContext(dataSource);
        Assert.Empty(await context.Database.GetPendingMigrationsAsync(cancellationToken));
        Assert.False(context.Database.HasPendingModelChanges());
    }

    /// <summary>
    /// Runs both database migration stages in the order <c>Program</c> runs them, leaving the migration
    /// service to interleave the schema and code migrations.
    /// </summary>
    private static async Task RunStartupMigrationsAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken, bool seedFirstTimeRun = false)
    {
        var dataPath = Directory.CreateTempSubdirectory("jellyfin-migration-test").FullName;
        try
        {
            await using var serviceProvider = BuildServiceProvider(dataSource, dataPath);
            var migrationService = ActivatorUtilities.CreateInstance<JellyfinMigrationService>(serviceProvider);

            if (seedFirstTimeRun)
            {
                await migrationService
                    .CheckFirstTimeRunOrMigration(serviceProvider.GetRequiredService<IApplicationPaths>(), new StartupOptions())
                    .ConfigureAwait(false);
            }

            await migrationService.MigrateStepAsync(JellyfinMigrationStageTypes.CoreInitialisation, serviceProvider).ConfigureAwait(false);
            await migrationService.MigrateStepAsync(JellyfinMigrationStageTypes.AppInitialisation, serviceProvider).ConfigureAwait(false);
        }
        finally
        {
            Directory.Delete(dataPath, true);
        }

        cancellationToken.ThrowIfCancellationRequested();
    }

    /// <summary>
    /// Records every code migration the previous build shipped as applied, the way that build left the
    /// database behind.
    /// </summary>
    private static async Task RecordPreviousReleaseMigrationsAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
    {
#pragma warning disable CS0618 // Type or member is obsolete
        var applied = typeof(JellyfinMigrationService).Assembly.GetTypes()
            .Select(type => (Type: type, Metadata: type.GetCustomAttribute<JellyfinMigrationAttribute>()))
            .Where(candidate => candidate.Metadata is not null && candidate.Metadata.Order < _previousReleaseOrder)
            .Select(candidate => new CodeMigration(candidate.Type, candidate.Metadata!, null).BuildCodeMigrationId())
            .ToList();
#pragma warning restore CS0618 // Type or member is obsolete

        Assert.NotEmpty(applied);

        var values = string.Join(", ", applied.Select(id => $"('{id}', '12.0.0.0')"));
        await ExecuteAsync(
            dataSource,
            $"""INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion") VALUES {values}""",
            cancellationToken);
    }

    /// <summary>
    /// Builds the container the migration routines are resolved from. The services a routine reaches past the
    /// database are stubbed out; everything touching the database is real.
    /// </summary>
    private static ServiceProvider BuildServiceProvider(NpgsqlDataSource dataSource, string dataPath)
    {
        var loggerFactory = NullLoggerFactory.Instance;
        var applicationPaths = new ServerApplicationPaths(
            dataPath,
            Ensure(dataPath, "log"),
            Ensure(dataPath, "config"),
            Ensure(dataPath, "cache"),
            Ensure(dataPath, "web"));

        // The real configuration manager with the real stores: a routine that rewrites a configuration file
        // would otherwise never get past looking its store up.
        var configurationManager = new ServerConfigurationManager(applicationPaths, loggerFactory, new MyXmlSerializer());
        configurationManager.AddParts(
        [
            new DatabaseConfigurationFactory(),
            new EncodingConfigurationFactory(),
            new MetadataConfigurationStore(),
            new NetworkConfigurationFactory(),
            new BrandingConfigurationFactory(),
            new NfoConfigurationFactory(),
            new LiveTvConfigurationFactory()
        ]);

        var libraryManager = new Mock<ILibraryManager>();
        libraryManager.Setup(manager => manager.GetItemList(It.IsAny<InternalItemsQuery>())).Returns([]);
        libraryManager.Setup(manager => manager.GetVirtualFolders(It.IsAny<bool>())).Returns([]);

        var applicationHost = new Mock<IServerApplicationHost>();
        applicationHost.Setup(host => host.ExpandVirtualPath(It.IsAny<string>())).Returns<string>(path => path);
        applicationHost.Setup(host => host.ReverseVirtualPath(It.IsAny<string>())).Returns<string>(path => path);

        var fileSystem = new Mock<IFileSystem>();
        fileSystem.Setup(system => system.GetValidFilename(It.IsAny<string>())).Returns<string>(name => name);

        var trickplayManager = new Mock<ITrickplayManager>();
        trickplayManager
            .Setup(manager => manager.GetTrickplayItemsAsync(It.IsAny<int>(), It.IsAny<int>()))
            .ReturnsAsync([]);

        var localizationManager = new Mock<ILocalizationManager>();
        localizationManager.Setup(manager => manager.GetRatingScore("PG-13", null)).Returns(new ParentalRatingScore(13, 2));

        return new ServiceCollection()
            .AddLogging()
            .RegisterStartupLogger()
            .AddSingleton<IDbContextFactory<JellyfinDbContext>>(new DataSourceContextFactory(dataSource))
            .AddSingleton<IJellyfinDatabaseProvider>(new PostgreSqlDatabaseProvider(dataSource))
            .AddSingleton<ServerApplicationPaths>(applicationPaths)
            .AddSingleton<IServerApplicationPaths>(applicationPaths)
            .AddSingleton<IApplicationPaths>(applicationPaths)
            .AddSingleton<IServerConfigurationManager>(configurationManager)
            .AddSingleton<IConfigurationManager>(configurationManager)
            .AddSingleton<IXmlSerializer>(new MyXmlSerializer())
            .AddSingleton(libraryManager.Object)
            .AddSingleton(applicationHost.Object)
            .AddSingleton(fileSystem.Object)
            .AddSingleton(trickplayManager.Object)
            .AddSingleton(localizationManager.Object)
            .AddSingleton(Mock.Of<IProviderManager>())
            .AddSingleton(Mock.Of<IItemRepository>())
            .AddSingleton(Mock.Of<IItemCountService>())
            .AddSingleton(Mock.Of<IItemPersistenceService>())
            .AddSingleton(Mock.Of<IPathManager>())
            .AddSingleton(Mock.Of<IPlaylistManager>())
            .AddSingleton(Mock.Of<IUserManager>())
            .BuildServiceProvider();
    }

    private static string Ensure(string root, params string[] parts)
    {
        var path = Path.Combine([root, .. parts]);
        Directory.CreateDirectory(path);
        return path;
    }

    private static JellyfinDbContext CreateContext(NpgsqlDataSource dataSource)
    {
        var optionsBuilder = new DbContextOptionsBuilder<JellyfinDbContext>();
        var provider = new PostgreSqlDatabaseProvider(dataSource);
        provider.Initialise(optionsBuilder, new DatabaseConfigurationOptions { DatabaseType = "PostgreSQL" });
        return new JellyfinDbContext(
            optionsBuilder.Options,
            NullLogger<JellyfinDbContext>.Instance,
            provider,
            new NoLockBehavior(NullLogger<NoLockBehavior>.Instance));
    }

    /// <summary>
    /// Brings the database to the state the previous build left it in: the baseline schema migration and
    /// nothing else, so the startup sequence has the whole chain still ahead of it.
    /// </summary>
    private static async Task MigrateToBaselineAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
    {
        await using var context = CreateContext(dataSource);
        await context.GetService<IMigrator>()
            .MigrateAsync(BaselineMigrationId, cancellationToken)
            .ConfigureAwait(false);
    }

    /// <summary>
    /// Writes a library big enough for the code migrations to have work to do: rated, unrated and
    /// empty-rated items, an owned extra, a series with an episode, duplicate artists, users with
    /// permissions and preferences.
    /// </summary>
    private static async Task SeedLibraryAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
    {
        await ExecuteAsync(
            dataSource,
            $"""
            INSERT INTO "Users" ("Id", "Username", "MustUpdatePassword", "AuthenticationProviderId", "PasswordResetProviderId",
                "InvalidLoginAttemptCount", "MaxActiveSessions", "SubtitleMode", "PlayDefaultAudioTrack", "DisplayMissingEpisodes",
                "DisplayCollectionsView", "EnableLocalPassword", "HidePlayedInLatest", "RememberAudioSelections",
                "RememberSubtitleSelections", "EnableNextEpisodeAutoPlay", "EnableAutoLogin", "EnableUserPreferenceAccess",
                "InternalId", "SyncPlayAccess", "RowVersion")
            VALUES
                ('{AliceId}', 'alice', false, 'provider', 'provider', 0, 0, 0, true, false, true, false, false, true, true, true, false, true, 1, 0, 0),
                ('{BobId}', 'bob', false, 'provider', 'provider', 0, 0, 0, true, false, true, false, false, true, true, true, false, true, 2, 0, 0);

            INSERT INTO "Permissions" ("UserId", "Kind", "Value", "RowVersion")
            VALUES ('{AliceId}', 0, true, 0), ('{BobId}', 0, true, 0);

            INSERT INTO "Preferences" ("UserId", "Kind", "Value", "RowVersion")
            VALUES ('{AliceId}', 0, 'kept', 0), ('{BobId}', 0, 'kept', 0);
            """,
            cancellationToken);

        // NULL and empty OfficialRating are the rows that make the rating migration write while it is still
        // reading the distinct rating list.
        await InsertItemAsync(dataSource, MovieItemId, "MediaBrowser.Controller.Entities.Movies.Movie", "Rated Movie", "PG-13", null, null, cancellationToken);
        await InsertItemAsync(dataSource, UnratedMovieItemId, "MediaBrowser.Controller.Entities.Movies.Movie", "Unrated Movie", null, null, null, cancellationToken);
        await InsertItemAsync(dataSource, EmptyRatingMovieItemId, "MediaBrowser.Controller.Entities.Movies.Movie", "Empty Rating Movie", string.Empty, null, null, cancellationToken);
        await InsertItemAsync(dataSource, TrailerItemId, "MediaBrowser.Controller.Entities.Video", "Trailer", null, MovieItemId, null, cancellationToken);
        await InsertItemAsync(dataSource, SeriesItemId, "MediaBrowser.Controller.Entities.TV.Series", "Series", "TV-14", null, null, cancellationToken);
        await InsertItemAsync(dataSource, EpisodeItemId, "MediaBrowser.Controller.Entities.TV.Episode", "Episode", "TV-14", null, SeriesItemId, cancellationToken);

        // Two artists differing only by casing are what the merge migrations look for.
        await InsertItemAsync(dataSource, ArtistItemId, "MediaBrowser.Controller.Entities.Audio.MusicArtist", "The Band", null, null, null, cancellationToken);
        await InsertItemAsync(dataSource, DuplicateArtistItemId, "MediaBrowser.Controller.Entities.Audio.MusicArtist", "the band", null, null, null, cancellationToken);

        await ExecuteAsync(
            dataSource,
            $"""
            INSERT INTO "Peoples" ("Id", "Name", "PersonType")
            VALUES ('{Guid.NewGuid()}', 'Jane Doe', 'Actor'), ('{Guid.NewGuid()}', 'jane doe', 'Actor');
            """,
            cancellationToken);
    }

    private static Task InsertItemAsync(
        NpgsqlDataSource dataSource,
        string itemId,
        string type,
        string name,
        string? officialRating,
        string? ownerId,
        string? parentId,
        CancellationToken cancellationToken)
        => ExecuteAsync(
            dataSource,
            $"""
            INSERT INTO "BaseItems" ("Id", "Type", "IsMovie", "IsLocked", "IsSeries", "IsRepeat", "IsInMixedFolder",
                "IsFolder", "IsVirtualItem", "Name", "CleanName", "Path", "OfficialRating", "OwnerId", "ParentId",
                "InheritedParentalRatingValue", "InheritedParentalRatingSubValue")
            VALUES ('{itemId}', '{type}', false, false, false, false, false, false, false,
                '{name}', '{name.ToLowerInvariant()}', '/media/{itemId}.mkv', {Literal(officialRating)}, {Literal(ownerId)}, {Literal(parentId)},
                99, 98);
            """,
            cancellationToken);

    private static string Literal(string? value) => value is null ? "NULL" : $"'{value}'";

    private static async Task ExecuteAsync(NpgsqlDataSource dataSource, string sql, CancellationToken cancellationToken)
    {
        await using var command = dataSource.CreateCommand(sql);
        await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
    }

    private static async Task<int?> RatingValueAsync(NpgsqlDataSource dataSource, string itemId, CancellationToken cancellationToken)
    {
        await using var command = dataSource.CreateCommand($"""SELECT "InheritedParentalRatingValue" FROM "BaseItems" WHERE "Id" = '{itemId}'""");
        var value = await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);
        return value is null or DBNull ? null : (int?)value;
    }

    private static async Task<string?> ScalarStringAsync(NpgsqlDataSource dataSource, string sql, CancellationToken cancellationToken)
    {
        await using var command = dataSource.CreateCommand(sql);
        var value = await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);
        return value is null or DBNull ? null : (string)value;
    }

    private sealed class DataSourceContextFactory : IDbContextFactory<JellyfinDbContext>
    {
        private readonly NpgsqlDataSource _dataSource;

        public DataSourceContextFactory(NpgsqlDataSource dataSource)
        {
            _dataSource = dataSource;
        }

        public JellyfinDbContext CreateDbContext() => CreateContext(_dataSource);
    }
}