unkin-agent
b501ba9620
Every PostgreSQL test carries Category=RequiresDocker and the pipeline filters that category out, so the provider production runs on was never exercised and two upgrade-path bugs reached it in a row. Nothing covered the whole startup sequence either - code and schema migrations interleaved the way the migration service orders them. - run both database stages through JellyfinMigrationService against a seeded library and against a fresh install - take the server from JELLYFIN_TEST_POSTGRES when it is set, else start a container - add a pipeline step that runs the PostgreSQL tests on every push and pull request, with the server inside the step: the kubernetes backend has no docker daemon, and a service container deadlocks on the workspace volume
634 lines
34 KiB
C#
634 lines
34 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Globalization;
|
|
using System.Linq;
|
|
using System.Threading;
|
|
using System.Threading.Tasks;
|
|
using Jellyfin.Database.Implementations;
|
|
using Jellyfin.Database.Implementations.DbConfiguration;
|
|
using Jellyfin.Database.Implementations.Entities;
|
|
using Jellyfin.Database.Implementations.Locking;
|
|
using Jellyfin.Database.Providers.PostgreSQL;
|
|
using Jellyfin.Server.Migrations.Routines;
|
|
using Jellyfin.Server.ServerSetupApp;
|
|
using MediaBrowser.Controller.Entities;
|
|
using MediaBrowser.Controller.Library;
|
|
using MediaBrowser.Model.Entities;
|
|
using MediaBrowser.Model.Globalization;
|
|
using Microsoft.EntityFrameworkCore;
|
|
using Microsoft.EntityFrameworkCore.Infrastructure;
|
|
using Microsoft.EntityFrameworkCore.Migrations;
|
|
using Microsoft.Extensions.Logging;
|
|
using Microsoft.Extensions.Logging.Abstractions;
|
|
using Moq;
|
|
using Npgsql;
|
|
using Xunit;
|
|
|
|
namespace Jellyfin.Server.Tests.Migrations;
|
|
|
|
/// <summary>
|
|
/// Verifies that a PostgreSQL database created by an earlier build reaches the current schema, and that a fresh
|
|
/// install ends up with exactly the same schema.
|
|
/// </summary>
|
|
[Trait("Category", "RequiresDocker")]
|
|
public sealed class PostgreSqlUpgradeTests : IAsyncLifetime
|
|
{
|
|
private const string BaselineMigrationId = "20260305010333_InitialPostgreSql";
|
|
private const string SchemaUpgradeMigrationId = "20260306000000_UpgradeToServer12Schema";
|
|
private const string UsernameIndexMigrationId = "20260524120336_AddUniqueNormalizedUsernameIndex";
|
|
|
|
private const string PlaceholderItemId = "00000000-0000-0000-0000-000000000001";
|
|
private const string OwnerItemId = "11111111-1111-1111-1111-111111111111";
|
|
private const string OwnedItemId = "22222222-2222-2222-2222-222222222222";
|
|
private const string UnparseableOwnerItemId = "33333333-3333-3333-3333-333333333333";
|
|
private const string DanglingOwnerItemId = "44444444-4444-4444-4444-444444444444";
|
|
private const string MalformedOwnerItemId = "55555555-5555-5555-5555-555555555555";
|
|
private const string PlaceholderOwnedItemId = "66666666-6666-6666-6666-666666666666";
|
|
private const string UnratedItemId = "77777777-7777-7777-7777-777777777777";
|
|
private const string EmptyRatingItemId = "88888888-8888-8888-8888-888888888888";
|
|
private const string RatedItemId = "99999999-9999-9999-9999-999999999999";
|
|
private const string AliceId = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa";
|
|
private const string BobId = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb";
|
|
|
|
// Thirty-two hexadecimal digits with hyphens in the wrong places: a digit count alone accepts it, the uuid cast
|
|
// does not.
|
|
private const string MalformedGuid = "1111111-11111-1111-1111-111111111111";
|
|
|
|
private PostgreSqlTestServer _server = null!;
|
|
|
|
public async ValueTask InitializeAsync()
|
|
{
|
|
_server = await PostgreSqlTestServer.StartAsync().ConfigureAwait(false);
|
|
}
|
|
|
|
public async ValueTask DisposeAsync()
|
|
{
|
|
await _server.DisposeAsync().ConfigureAwait(false);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task FreshInstall_AppliesTheWholeChain()
|
|
{
|
|
var cancellationToken = TestContext.Current.CancellationToken;
|
|
var connectionString = await _server.CreateDatabaseAsync("fresh_install", cancellationToken);
|
|
|
|
await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
|
|
await MigrateAsync(dataSource, null, cancellationToken);
|
|
|
|
Assert.Equal(
|
|
new[] { BaselineMigrationId, SchemaUpgradeMigrationId, UsernameIndexMigrationId },
|
|
await AppliedMigrationsAsync(dataSource, cancellationToken));
|
|
await AssertServer12SchemaAsync(dataSource, cancellationToken);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task LegacyDatabase_UpgradesToServer12Schema()
|
|
{
|
|
var cancellationToken = TestContext.Current.CancellationToken;
|
|
var connectionString = await _server.CreateDatabaseAsync("legacy_upgrade", cancellationToken);
|
|
|
|
await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
|
|
await CreateLegacyDatabaseAsync(dataSource, cancellationToken);
|
|
|
|
// The database knows only the migration the previous build shipped, and the two that carry it forward are the
|
|
// ones the startup sequence has to pick up.
|
|
Assert.Equal(new[] { BaselineMigrationId }, await AppliedMigrationsAsync(dataSource, cancellationToken));
|
|
Assert.Equal(
|
|
new[] { SchemaUpgradeMigrationId, UsernameIndexMigrationId },
|
|
await PendingMigrationsAsync(dataSource, cancellationToken));
|
|
|
|
await RunUpgradeInStartupOrderAsync(dataSource, cancellationToken);
|
|
|
|
Assert.Empty(await PendingMigrationsAsync(dataSource, cancellationToken));
|
|
await AssertServer12SchemaAsync(dataSource, cancellationToken);
|
|
|
|
// The code migration that the startup sequence runs between the two schema migrations has to have filled the
|
|
// new column in, otherwise the unique index could not have been created.
|
|
Assert.Equal("ALICE", await ScalarAsync<string>(dataSource, $"""SELECT "NormalizedUsername" FROM "Users" WHERE "Id" = '{AliceId}'""", cancellationToken));
|
|
Assert.Equal("BOB", await ScalarAsync<string>(dataSource, $"""SELECT "NormalizedUsername" FROM "Users" WHERE "Id" = '{BobId}'""", cancellationToken));
|
|
|
|
// Owner and version ids were text and are now uuid: parseable values survive, unparseable ones are cleared
|
|
// and owners that point at nothing are repointed at the placeholder so the new foreign key holds.
|
|
Assert.Equal(new Guid(OwnerItemId), await ScalarAsync<Guid>(dataSource, $"""SELECT "OwnerId" FROM "BaseItems" WHERE "Id" = '{OwnedItemId}'""", cancellationToken));
|
|
Assert.Equal(new Guid(OwnerItemId), await ScalarAsync<Guid>(dataSource, $"""SELECT "PrimaryVersionId" FROM "BaseItems" WHERE "Id" = '{OwnedItemId}'""", cancellationToken));
|
|
Assert.Null(await NullableScalarAsync(dataSource, $"""SELECT "OwnerId" FROM "BaseItems" WHERE "Id" = '{UnparseableOwnerItemId}'""", cancellationToken));
|
|
Assert.Null(await NullableScalarAsync(dataSource, $"""SELECT "PrimaryVersionId" FROM "BaseItems" WHERE "Id" = '{UnparseableOwnerItemId}'""", cancellationToken));
|
|
Assert.Equal(new Guid(PlaceholderItemId), await ScalarAsync<Guid>(dataSource, $"""SELECT "OwnerId" FROM "BaseItems" WHERE "Id" = '{DanglingOwnerItemId}'""", cancellationToken));
|
|
|
|
// ExtraIds and OriginalLanguage are unrelated columns, so the extra ids must not have been carried over.
|
|
Assert.Null(await NullableScalarAsync(dataSource, $"""SELECT "OriginalLanguage" FROM "BaseItems" WHERE "Id" = '{UnparseableOwnerItemId}'""", cancellationToken));
|
|
|
|
// UserId is no longer nullable, so the rows that never had an owner had to be removed.
|
|
Assert.Equal(1L, await ScalarAsync<long>(dataSource, """SELECT count(*) FROM "Permissions" """, cancellationToken));
|
|
Assert.Equal(1L, await ScalarAsync<long>(dataSource, """SELECT count(*) FROM "Preferences" """, cancellationToken));
|
|
|
|
await AssertContextMatchesSchemaAsync(dataSource, cancellationToken);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task LegacyDatabaseAndFreshInstall_EndUpWithTheModelSchema()
|
|
{
|
|
var cancellationToken = TestContext.Current.CancellationToken;
|
|
var modelConnectionString = await _server.CreateDatabaseAsync("schema_model", cancellationToken);
|
|
var freshConnectionString = await _server.CreateDatabaseAsync("schema_fresh", cancellationToken);
|
|
var upgradedConnectionString = await _server.CreateDatabaseAsync("schema_upgraded", cancellationToken);
|
|
|
|
// The model built straight from the context is the reference: comparing the two migrated databases against
|
|
// each other only proves they drifted together.
|
|
await using var modelDataSource = new NpgsqlDataSourceBuilder(modelConnectionString).Build();
|
|
await CreateFromModelAsync(modelDataSource, cancellationToken);
|
|
|
|
await using var freshDataSource = new NpgsqlDataSourceBuilder(freshConnectionString).Build();
|
|
await MigrateAsync(freshDataSource, null, cancellationToken);
|
|
|
|
await using var upgradedDataSource = new NpgsqlDataSourceBuilder(upgradedConnectionString).Build();
|
|
await CreateLegacyDatabaseAsync(upgradedDataSource, cancellationToken);
|
|
await RunUpgradeInStartupOrderAsync(upgradedDataSource, cancellationToken);
|
|
|
|
var modelColumns = await DescribeColumnsAsync(modelDataSource, cancellationToken);
|
|
var modelIndexes = await DescribeIndexesAsync(modelDataSource, cancellationToken);
|
|
var modelConstraints = await DescribeConstraintsAsync(modelDataSource, cancellationToken);
|
|
|
|
Assert.Equal(modelColumns, await DescribeColumnsAsync(freshDataSource, cancellationToken));
|
|
Assert.Equal(modelIndexes, await DescribeIndexesAsync(freshDataSource, cancellationToken));
|
|
Assert.Equal(modelConstraints, await DescribeConstraintsAsync(freshDataSource, cancellationToken));
|
|
|
|
Assert.Equal(modelColumns, await DescribeColumnsAsync(upgradedDataSource, cancellationToken));
|
|
Assert.Equal(modelIndexes, await DescribeIndexesAsync(upgradedDataSource, cancellationToken));
|
|
Assert.Equal(modelConstraints, await DescribeConstraintsAsync(upgradedDataSource, cancellationToken));
|
|
}
|
|
|
|
[Fact]
|
|
public async Task LegacyDatabase_MalformedGuidIds_AreCleared()
|
|
{
|
|
var cancellationToken = TestContext.Current.CancellationToken;
|
|
var connectionString = await _server.CreateDatabaseAsync("malformed_guid", cancellationToken);
|
|
|
|
await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
|
|
await CreateLegacyDatabaseAsync(dataSource, cancellationToken);
|
|
await InsertLegacyItemAsync(dataSource, MalformedOwnerItemId, "Malformed", MalformedGuid, MalformedGuid, cancellationToken);
|
|
|
|
await RunUpgradeInStartupOrderAsync(dataSource, cancellationToken);
|
|
|
|
Assert.Null(await NullableScalarAsync(dataSource, $"""SELECT "OwnerId" FROM "BaseItems" WHERE "Id" = '{MalformedOwnerItemId}'""", cancellationToken));
|
|
Assert.Null(await NullableScalarAsync(dataSource, $"""SELECT "PrimaryVersionId" FROM "BaseItems" WHERE "Id" = '{MalformedOwnerItemId}'""", cancellationToken));
|
|
await AssertServer12SchemaAsync(dataSource, cancellationToken);
|
|
}
|
|
|
|
[Theory]
|
|
[InlineData(true)]
|
|
[InlineData(false)]
|
|
public async Task LegacyDatabase_UpgradesWhetherOrNotThePlaceholderItemExists(bool placeholderExists)
|
|
{
|
|
var cancellationToken = TestContext.Current.CancellationToken;
|
|
var connectionString = await _server.CreateDatabaseAsync(placeholderExists ? "placeholder_present" : "placeholder_missing", cancellationToken);
|
|
|
|
await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
|
|
await CreateLegacyDatabaseAsync(dataSource, cancellationToken);
|
|
if (!placeholderExists)
|
|
{
|
|
await ExecuteAsync(dataSource, $"""DELETE FROM "BaseItems" WHERE "Id" = '{PlaceholderItemId}'""", cancellationToken);
|
|
}
|
|
|
|
await RunUpgradeInStartupOrderAsync(dataSource, cancellationToken);
|
|
|
|
// The dangling owner is repointed at the placeholder, so the foreign key only holds if the placeholder is there.
|
|
Assert.Equal(1L, await ScalarAsync<long>(dataSource, $"""SELECT count(*) FROM "BaseItems" WHERE "Id" = '{PlaceholderItemId}'""", cancellationToken));
|
|
Assert.Equal(new Guid(PlaceholderItemId), await ScalarAsync<Guid>(dataSource, $"""SELECT "OwnerId" FROM "BaseItems" WHERE "Id" = '{DanglingOwnerItemId}'""", cancellationToken));
|
|
await AssertServer12SchemaAsync(dataSource, cancellationToken);
|
|
}
|
|
|
|
[Fact]
|
|
public async Task LegacyDatabase_ItemOwnedByThePlaceholder_SurvivesCleanupOrphanedExtras()
|
|
{
|
|
var cancellationToken = TestContext.Current.CancellationToken;
|
|
var connectionString = await _server.CreateDatabaseAsync("placeholder_owner", cancellationToken);
|
|
|
|
await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
|
|
await CreateLegacyDatabaseAsync(dataSource, cancellationToken);
|
|
await InsertLegacyItemAsync(dataSource, PlaceholderOwnedItemId, "Owned by the placeholder", PlaceholderItemId, null, cancellationToken);
|
|
|
|
await RunUpgradeInStartupOrderAsync(dataSource, cancellationToken);
|
|
|
|
// The SQLite chain clears an owner that already is the placeholder, so the item is not mistaken for an orphan.
|
|
Assert.Null(await NullableScalarAsync(dataSource, $"""SELECT "OwnerId" FROM "BaseItems" WHERE "Id" = '{PlaceholderOwnedItemId}'""", cancellationToken));
|
|
|
|
var deletedItemIds = new List<Guid>();
|
|
var libraryManager = new Mock<ILibraryManager>();
|
|
libraryManager
|
|
.Setup(manager => manager.DeleteItemsUnsafeFast(It.IsAny<IReadOnlyCollection<BaseItem>>(), It.IsAny<bool>()))
|
|
.Callback<IReadOnlyCollection<BaseItem>, bool>((items, _) => deletedItemIds.AddRange(items.Select(item => item.Id)));
|
|
|
|
await new CleanupOrphanedExtras(
|
|
Mock.Of<IStartupLogger<CleanupOrphanedExtras>>(),
|
|
new SingleContextFactory(dataSource),
|
|
libraryManager.Object).PerformAsync(cancellationToken);
|
|
|
|
Assert.Contains(new Guid(DanglingOwnerItemId), deletedItemIds);
|
|
Assert.DoesNotContain(new Guid(PlaceholderOwnedItemId), deletedItemIds);
|
|
Assert.Equal(1L, await ScalarAsync<long>(dataSource, $"""SELECT count(*) FROM "BaseItems" WHERE "Id" = '{PlaceholderOwnedItemId}'""", cancellationToken));
|
|
}
|
|
|
|
[Fact]
|
|
public async Task MigrateRatingLevels_RecalculatesEveryRating()
|
|
{
|
|
var cancellationToken = TestContext.Current.CancellationToken;
|
|
var connectionString = await _server.CreateDatabaseAsync("rating_levels", cancellationToken);
|
|
|
|
await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
|
|
await MigrateAsync(dataSource, null, cancellationToken);
|
|
|
|
// A NULL and an empty rating are what make the migration issue an update while it is still
|
|
// walking the distinct rating list, which a connection that cannot multiplex commands rejects.
|
|
await InsertRatedItemAsync(dataSource, UnratedItemId, null, cancellationToken);
|
|
await InsertRatedItemAsync(dataSource, EmptyRatingItemId, string.Empty, cancellationToken);
|
|
await InsertRatedItemAsync(dataSource, RatedItemId, "PG-13", cancellationToken);
|
|
|
|
var localizationManager = new Mock<ILocalizationManager>();
|
|
localizationManager
|
|
.Setup(manager => manager.GetRatingScore("PG-13", null))
|
|
.Returns(new ParentalRatingScore(13, 2));
|
|
|
|
new MigrateRatingLevels(
|
|
new SingleContextFactory(dataSource),
|
|
new NullStartupLogger<MigrateRatingLevels>(),
|
|
localizationManager.Object).Perform();
|
|
|
|
Assert.Null(await InheritedRatingAsync(dataSource, UnratedItemId, "InheritedParentalRatingValue", cancellationToken));
|
|
Assert.Null(await InheritedRatingAsync(dataSource, UnratedItemId, "InheritedParentalRatingSubValue", cancellationToken));
|
|
Assert.Null(await InheritedRatingAsync(dataSource, EmptyRatingItemId, "InheritedParentalRatingValue", cancellationToken));
|
|
Assert.Null(await InheritedRatingAsync(dataSource, EmptyRatingItemId, "InheritedParentalRatingSubValue", cancellationToken));
|
|
Assert.Equal(13, await InheritedRatingAsync(dataSource, RatedItemId, "InheritedParentalRatingValue", cancellationToken));
|
|
Assert.Equal(2, await InheritedRatingAsync(dataSource, RatedItemId, "InheritedParentalRatingSubValue", cancellationToken));
|
|
}
|
|
|
|
[Fact]
|
|
public async Task UniqueUsernameIndex_CannotBeAppliedBeforeTheUsernameCodeMigration()
|
|
{
|
|
var cancellationToken = TestContext.Current.CancellationToken;
|
|
var connectionString = await _server.CreateDatabaseAsync("username_index_order", cancellationToken);
|
|
|
|
await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
|
|
await CreateLegacyDatabaseAsync(dataSource, cancellationToken);
|
|
|
|
// Every existing user starts out with the same empty normalized username, so the index can only be built once
|
|
// UpdateNormalizedUsername has filled the column in. That is why the index is a migration of its own.
|
|
await Assert.ThrowsAsync<PostgresException>(() => MigrateAsync(dataSource, null, cancellationToken));
|
|
}
|
|
|
|
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));
|
|
}
|
|
|
|
private static async Task MigrateAsync(NpgsqlDataSource dataSource, string? targetMigration, CancellationToken cancellationToken)
|
|
{
|
|
var context = CreateContext(dataSource);
|
|
await using (context.ConfigureAwait(false))
|
|
{
|
|
await context.GetService<IMigrator>().MigrateAsync(targetMigration, cancellationToken).ConfigureAwait(false);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Creates the schema straight from the current model, without going through any migration.
|
|
/// </summary>
|
|
private static async Task CreateFromModelAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
|
|
{
|
|
var context = CreateContext(dataSource);
|
|
await using (context.ConfigureAwait(false))
|
|
{
|
|
await context.Database.EnsureCreatedAsync(cancellationToken).ConfigureAwait(false);
|
|
}
|
|
}
|
|
|
|
private static async Task<IReadOnlyList<string>> AppliedMigrationsAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
|
|
{
|
|
var context = CreateContext(dataSource);
|
|
await using (context.ConfigureAwait(false))
|
|
{
|
|
return [.. await context.Database.GetAppliedMigrationsAsync(cancellationToken).ConfigureAwait(false)];
|
|
}
|
|
}
|
|
|
|
private static async Task<IReadOnlyList<string>> PendingMigrationsAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
|
|
{
|
|
var context = CreateContext(dataSource);
|
|
await using (context.ConfigureAwait(false))
|
|
{
|
|
return [.. await context.Database.GetPendingMigrationsAsync(cancellationToken).ConfigureAwait(false)];
|
|
}
|
|
}
|
|
|
|
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<T> ScalarAsync<T>(NpgsqlDataSource dataSource, string sql, CancellationToken cancellationToken)
|
|
{
|
|
var value = await NullableScalarAsync(dataSource, sql, cancellationToken).ConfigureAwait(false);
|
|
Assert.NotNull(value);
|
|
return (T)value;
|
|
}
|
|
|
|
private static async Task<object?> NullableScalarAsync(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 : value;
|
|
}
|
|
|
|
private static async Task<IReadOnlyList<string>> QueryLinesAsync(NpgsqlDataSource dataSource, string sql, CancellationToken cancellationToken)
|
|
{
|
|
var lines = new List<string>();
|
|
await using var command = dataSource.CreateCommand(sql);
|
|
await using var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
|
|
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
|
|
{
|
|
var values = new string[reader.FieldCount];
|
|
for (var i = 0; i < reader.FieldCount; i++)
|
|
{
|
|
values[i] = await reader.IsDBNullAsync(i, cancellationToken).ConfigureAwait(false)
|
|
? "<null>"
|
|
: Convert.ToString(reader.GetValue(i), CultureInfo.InvariantCulture) ?? "<null>";
|
|
}
|
|
|
|
lines.Add(string.Join('|', values));
|
|
}
|
|
|
|
return lines;
|
|
}
|
|
|
|
private static Task<IReadOnlyList<string>> DescribeColumnsAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
|
|
=> QueryLinesAsync(
|
|
dataSource,
|
|
"""
|
|
SELECT table_name, column_name, data_type, is_nullable, coalesce(character_maximum_length, -1), coalesce(column_default, '')
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'public' AND table_name <> '__EFMigrationsHistory'
|
|
ORDER BY table_name, column_name
|
|
""",
|
|
cancellationToken);
|
|
|
|
private static Task<IReadOnlyList<string>> DescribeIndexesAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
|
|
=> QueryLinesAsync(
|
|
dataSource,
|
|
"""
|
|
SELECT tablename, indexname, indexdef
|
|
FROM pg_indexes
|
|
WHERE schemaname = 'public' AND tablename <> '__EFMigrationsHistory'
|
|
ORDER BY tablename, indexname
|
|
""",
|
|
cancellationToken);
|
|
|
|
private static Task<IReadOnlyList<string>> DescribeConstraintsAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
|
|
=> QueryLinesAsync(
|
|
dataSource,
|
|
"""
|
|
SELECT relation.relname, constraint_.conname, pg_get_constraintdef(constraint_.oid)
|
|
FROM pg_constraint AS constraint_
|
|
JOIN pg_class AS relation ON relation.oid = constraint_.conrelid
|
|
JOIN pg_namespace AS namespace_ ON namespace_.oid = relation.relnamespace
|
|
WHERE namespace_.nspname = 'public' AND relation.relname <> '__EFMigrationsHistory'
|
|
ORDER BY relation.relname, constraint_.conname
|
|
""",
|
|
cancellationToken);
|
|
|
|
/// <summary>
|
|
/// Adds one more item to a legacy database, with the owner and version ids still held as text.
|
|
/// </summary>
|
|
private static Task InsertLegacyItemAsync(
|
|
NpgsqlDataSource dataSource,
|
|
string itemId,
|
|
string name,
|
|
string? ownerId,
|
|
string? primaryVersionId,
|
|
CancellationToken cancellationToken)
|
|
=> ExecuteAsync(
|
|
dataSource,
|
|
$"""
|
|
INSERT INTO "BaseItems" ("Id", "Type", "IsMovie", "IsLocked", "IsSeries", "IsRepeat", "IsInMixedFolder",
|
|
"IsFolder", "IsVirtualItem", "Name", "Path", "OwnerId", "PrimaryVersionId")
|
|
VALUES ('{itemId}', 'MediaBrowser.Controller.Entities.Video', false, false, false, false, false, false, false,
|
|
'{name}', '/media/{itemId}.mkv', {Literal(ownerId)}, {Literal(primaryVersionId)});
|
|
""",
|
|
cancellationToken);
|
|
|
|
private static string Literal(string? value) => value is null ? "NULL" : $"'{value}'";
|
|
|
|
/// <summary>
|
|
/// Adds an item carrying a rating and an already populated inherited rating, so that clearing it is visible.
|
|
/// </summary>
|
|
private static Task InsertRatedItemAsync(
|
|
NpgsqlDataSource dataSource,
|
|
string itemId,
|
|
string? officialRating,
|
|
CancellationToken cancellationToken)
|
|
=> ExecuteAsync(
|
|
dataSource,
|
|
$"""
|
|
INSERT INTO "BaseItems" ("Id", "Type", "IsMovie", "IsLocked", "IsSeries", "IsRepeat", "IsInMixedFolder",
|
|
"IsFolder", "IsVirtualItem", "Name", "Path", "OfficialRating",
|
|
"InheritedParentalRatingValue", "InheritedParentalRatingSubValue")
|
|
VALUES ('{itemId}', 'MediaBrowser.Controller.Entities.Movies.Movie', true, false, false, false, false, false, false,
|
|
'Rated {itemId}', '/media/{itemId}.mkv', {Literal(officialRating)}, 99, 98);
|
|
""",
|
|
cancellationToken);
|
|
|
|
private static async Task<int?> InheritedRatingAsync(NpgsqlDataSource dataSource, string itemId, string column, CancellationToken cancellationToken)
|
|
{
|
|
var value = await NullableScalarAsync(
|
|
dataSource,
|
|
$"""SELECT "{column}" FROM "BaseItems" WHERE "Id" = '{itemId}'""",
|
|
cancellationToken).ConfigureAwait(false);
|
|
return (int?)value;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Builds a database that looks like one written by the previous build: only the baseline migration applied, and
|
|
/// data that only the old column types could hold.
|
|
/// </summary>
|
|
private static async Task CreateLegacyDatabaseAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
|
|
{
|
|
await MigrateAsync(dataSource, BaselineMigrationId, cancellationToken).ConfigureAwait(false);
|
|
|
|
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 "BaseItems" ("Id", "Type", "IsMovie", "IsLocked", "IsSeries", "IsRepeat", "IsInMixedFolder",
|
|
"IsFolder", "IsVirtualItem", "Name", "Path", "OwnerId", "PrimaryVersionId", "ExtraIds")
|
|
VALUES
|
|
('{OwnerItemId}', 'MediaBrowser.Controller.Entities.Movies.Movie', true, false, false, false, false, false, false, 'Owner', '/media/owner.mkv', NULL, NULL, NULL),
|
|
('{OwnedItemId}', 'MediaBrowser.Controller.Entities.Video', false, false, false, false, false, false, false, 'Trailer', '/media/owner-trailer.mkv', '11111111111111111111111111111111', '11111111-1111-1111-1111-111111111111', NULL),
|
|
('{UnparseableOwnerItemId}', 'MediaBrowser.Controller.Entities.Video', false, false, false, false, false, false, false, 'Junk', '/media/junk.mkv', '', '00000000-0000-0000-0000-000000000000', 'aaaa|bbbb'),
|
|
('{DanglingOwnerItemId}', 'MediaBrowser.Controller.Entities.Video', false, false, false, false, false, false, false, 'Dangling', '/media/dangling.mkv', '99999999-9999-9999-9999-999999999999', NULL, NULL);
|
|
|
|
INSERT INTO "Permissions" ("UserId", "Kind", "Value", "RowVersion")
|
|
VALUES ('{AliceId}', 0, true, 0), (NULL, 0, true, 0);
|
|
|
|
INSERT INTO "Preferences" ("UserId", "Kind", "Value", "RowVersion")
|
|
VALUES ('{BobId}', 0, 'kept', 0), (NULL, 0, 'orphaned', 0);
|
|
""",
|
|
cancellationToken).ConfigureAwait(false);
|
|
}
|
|
|
|
/// <summary>
|
|
/// Runs the migrations in the order the startup migration service picks them: schema and code migration ids are
|
|
/// sorted into one list, which puts the username code migration between the two schema migrations.
|
|
/// </summary>
|
|
private static async Task RunUpgradeInStartupOrderAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
|
|
{
|
|
await MigrateAsync(dataSource, SchemaUpgradeMigrationId, cancellationToken).ConfigureAwait(false);
|
|
await new UpdateNormalizedUsername(new SingleContextFactory(dataSource)).PerformAsync(cancellationToken).ConfigureAwait(false);
|
|
await MigrateAsync(dataSource, UsernameIndexMigrationId, cancellationToken).ConfigureAwait(false);
|
|
}
|
|
|
|
private static async Task AssertServer12SchemaAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
|
|
{
|
|
Assert.Equal("character varying", await ColumnTypeAsync(dataSource, "Users", "NormalizedUsername", cancellationToken).ConfigureAwait(false));
|
|
Assert.Equal("uuid", await ColumnTypeAsync(dataSource, "BaseItems", "OwnerId", cancellationToken).ConfigureAwait(false));
|
|
Assert.Equal("uuid", await ColumnTypeAsync(dataSource, "BaseItems", "PrimaryVersionId", cancellationToken).ConfigureAwait(false));
|
|
Assert.Equal("text", await ColumnTypeAsync(dataSource, "BaseItems", "OriginalLanguage", cancellationToken).ConfigureAwait(false));
|
|
Assert.Equal("boolean", await ColumnTypeAsync(dataSource, "MediaStreamInfos", "IsOriginal", cancellationToken).ConfigureAwait(false));
|
|
Assert.Null(await ColumnTypeAsync(dataSource, "BaseItems", "ExtraIds", cancellationToken).ConfigureAwait(false));
|
|
Assert.Null(await ColumnTypeAsync(dataSource, "Permissions", "Permission_Permissions_Guid", cancellationToken).ConfigureAwait(false));
|
|
Assert.Null(await ColumnTypeAsync(dataSource, "Preferences", "Preference_Preferences_Guid", cancellationToken).ConfigureAwait(false));
|
|
|
|
Assert.Equal("NO", await ScalarAsync<string>(
|
|
dataSource,
|
|
"""SELECT is_nullable FROM information_schema.columns WHERE table_name = 'Permissions' AND column_name = 'UserId'""",
|
|
cancellationToken).ConfigureAwait(false));
|
|
Assert.Equal("NO", await ScalarAsync<string>(
|
|
dataSource,
|
|
"""SELECT is_nullable FROM information_schema.columns WHERE table_name = 'Preferences' AND column_name = 'UserId'""",
|
|
cancellationToken).ConfigureAwait(false));
|
|
|
|
Assert.True(await ScalarAsync<bool>(
|
|
dataSource,
|
|
"""SELECT indisunique FROM pg_index WHERE indexrelid = 'public."IX_Users_NormalizedUsername"'::regclass""",
|
|
cancellationToken).ConfigureAwait(false));
|
|
|
|
// The model declares no database defaults, so none may be left over from filling the new columns in.
|
|
Assert.Equal(0L, await ScalarAsync<long>(
|
|
dataSource,
|
|
"""
|
|
SELECT count(*) FROM information_schema.columns
|
|
WHERE table_schema = 'public' AND column_default IS NOT NULL
|
|
AND (table_name, column_name) IN (
|
|
('Users', 'NormalizedUsername'), ('Permissions', 'UserId'),
|
|
('Preferences', 'UserId'), ('MediaStreamInfos', 'IsOriginal'))
|
|
""",
|
|
cancellationToken).ConfigureAwait(false));
|
|
|
|
// Playlists may hold the same child more than once, which the primary key has to allow for.
|
|
Assert.Equal(
|
|
"ParentId,SortOrder",
|
|
await ScalarAsync<string>(
|
|
dataSource,
|
|
"""
|
|
SELECT string_agg(attribute.attname, ',' ORDER BY key.ordinality)
|
|
FROM pg_constraint AS constraint_
|
|
CROSS JOIN LATERAL unnest(constraint_.conkey) WITH ORDINALITY AS key(attnum, ordinality)
|
|
JOIN pg_attribute AS attribute
|
|
ON attribute.attrelid = constraint_.conrelid AND attribute.attnum = key.attnum
|
|
WHERE constraint_.conname = 'PK_LinkedChildren'
|
|
""",
|
|
cancellationToken).ConfigureAwait(false));
|
|
|
|
Assert.Equal(1L, await ScalarAsync<long>(
|
|
dataSource,
|
|
"""SELECT count(*) FROM pg_constraint WHERE conname = 'FK_BaseItems_BaseItems_OwnerId'""",
|
|
cancellationToken).ConfigureAwait(false));
|
|
}
|
|
|
|
private static async Task<string?> ColumnTypeAsync(NpgsqlDataSource dataSource, string table, string column, CancellationToken cancellationToken)
|
|
{
|
|
var value = await NullableScalarAsync(
|
|
dataSource,
|
|
$"""SELECT data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '{table}' AND column_name = '{column}'""",
|
|
cancellationToken).ConfigureAwait(false);
|
|
return (string?)value;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Writes and reads an entity through the context to prove the migrated schema really matches the current model.
|
|
/// </summary>
|
|
private static async Task AssertContextMatchesSchemaAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
|
|
{
|
|
var context = CreateContext(dataSource);
|
|
await using (context.ConfigureAwait(false))
|
|
{
|
|
Assert.False(context.Database.HasPendingModelChanges());
|
|
|
|
var user = new User("carol", "provider", "provider");
|
|
context.Users.Add(user);
|
|
await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
|
|
|
|
var reloaded = await context.Users.AsNoTracking()
|
|
.FirstAsync(u => u.Id.Equals(user.Id), cancellationToken)
|
|
.ConfigureAwait(false);
|
|
Assert.Equal("CAROL", reloaded.NormalizedUsername);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Stands in for the startup logger. Moq cannot proxy a logger whose category is an internal migration.
|
|
/// </summary>
|
|
private sealed class NullStartupLogger<TCategory> : IStartupLogger<TCategory>
|
|
{
|
|
public StartupLogTopic? Topic => null;
|
|
|
|
public IDisposable? BeginScope<TState>(TState state)
|
|
where TState : notnull
|
|
=> NullLogger.Instance.BeginScope(state);
|
|
|
|
public bool IsEnabled(LogLevel logLevel) => false;
|
|
|
|
public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception? exception, Func<TState, Exception?, string> formatter)
|
|
{
|
|
}
|
|
|
|
IStartupLogger IStartupLogger.BeginGroup(FormattableString logEntry) => this;
|
|
|
|
IStartupLogger<TOther> IStartupLogger.BeginGroup<TOther>(FormattableString logEntry) => new NullStartupLogger<TOther>();
|
|
|
|
IStartupLogger<TCategory> IStartupLogger<TCategory>.BeginGroup(FormattableString logEntry) => this;
|
|
|
|
IStartupLogger IStartupLogger.With(Microsoft.Extensions.Logging.ILogger logger) => this;
|
|
|
|
IStartupLogger<TOther> IStartupLogger.With<TOther>(Microsoft.Extensions.Logging.ILogger logger) => new NullStartupLogger<TOther>();
|
|
|
|
IStartupLogger<TCategory> IStartupLogger<TCategory>.With(Microsoft.Extensions.Logging.ILogger logger) => this;
|
|
}
|
|
|
|
private sealed class SingleContextFactory : IDbContextFactory<JellyfinDbContext>
|
|
{
|
|
private readonly NpgsqlDataSource _dataSource;
|
|
|
|
public SingleContextFactory(NpgsqlDataSource dataSource)
|
|
{
|
|
_dataSource = dataSource;
|
|
}
|
|
|
|
public JellyfinDbContext CreateDbContext() => CreateContext(_dataSource);
|
|
}
|
|
}
|