Backport pull request #17835 from jellyfin/release-12.z

Clean up invalid data before running migrations

Original-merge: 75d7b2010ea2d75fdb7554c700c4e6981331b484

Merged-by: crobibero <cody@robibe.ro>

Backported-by: Cody Robibero <cody@robibe.ro>

3 files changed, 70 insertions, 23 deletions

diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20250913211637_AddProperParentChildRelationBaseItemWithCascade.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20250913211637_AddProperParentChildRelationBaseItemWithCascade.cs
index a7f5e369ab..156f553fb3 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20250913211637_AddProperParentChildRelationBaseItemWithCascade.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20250913211637_AddProperParentChildRelationBaseItemWithCascade.cs
@@ -11,27 +11,19 @@ namespace Jellyfin.Server.Implementations.Migrations
         protected override void Up(MigrationBuilder migrationBuilder)
         {
             migrationBuilder.Sql("""
-DELETE FROM BaseItems
-        WHERE
-        ParentId IS NOT NULL
-        AND
-        NOT EXISTS(SELECT 1 FROM BaseItems parent WHERE parent.Id = BaseItems.ParentId);
-DELETE FROM BaseItems
-        WHERE
-        ParentId IS NOT NULL
-        AND
-        NOT EXISTS(SELECT 1 FROM BaseItems parent WHERE parent.Id = BaseItems.ParentId);
-DELETE FROM BaseItems
-        WHERE
-        ParentId IS NOT NULL
-        AND
-        NOT EXISTS(SELECT 1 FROM BaseItems parent WHERE parent.Id = BaseItems.ParentId);
-DELETE FROM BaseItems
-        WHERE
-        ParentId IS NOT NULL
-        AND
-        NOT EXISTS(SELECT 1 FROM BaseItems parent WHERE parent.Id = BaseItems.ParentId);
-""");
+                WITH RECURSIVE Orphan ("Id") AS (
+                    SELECT Child."Id"
+                    FROM "BaseItems" AS Child
+                    WHERE Child."ParentId" IS NOT NULL
+                      AND NOT EXISTS (SELECT 1 FROM "BaseItems" AS Parent WHERE Parent."Id" = Child."ParentId")
+                    UNION
+                    SELECT Descendant."Id"
+                    FROM "BaseItems" AS Descendant
+                    INNER JOIN Orphan ON Descendant."ParentId" = Orphan."Id"
+                )
+                DELETE FROM "BaseItems" WHERE "Id" IN (SELECT "Id" FROM Orphan);
+                """);
+
             migrationBuilder.AddForeignKey(
                 name: "FK_BaseItems_BaseItems_ParentId",
                 table: "BaseItems",
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260113203012_ChangeOwnerIdToGuid.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260113203012_ChangeOwnerIdToGuid.cs
index 4927b0e78d..379da0e9be 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260113203012_ChangeOwnerIdToGuid.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260113203012_ChangeOwnerIdToGuid.cs
@@ -11,6 +11,61 @@ namespace Jellyfin.Database.Providers.Sqlite.Migrations
         /// <inheritdoc />
         protected override void Up(MigrationBuilder migrationBuilder)
         {
+            migrationBuilder.Sql(
+                """
+                DROP TABLE IF EXISTS "OrphanedBaseItemIds";
+                CREATE TEMPORARY TABLE "OrphanedBaseItemIds" ("Id" TEXT NOT NULL PRIMARY KEY);
+
+                INSERT INTO "OrphanedBaseItemIds" ("Id")
+                WITH RECURSIVE Orphan ("Id") AS (
+                    SELECT Child."Id"
+                    FROM "BaseItems" AS Child
+                    WHERE Child."ParentId" IS NOT NULL
+                      AND NOT EXISTS (SELECT 1 FROM "BaseItems" AS Parent WHERE Parent."Id" = Child."ParentId")
+                    UNION
+                    SELECT Descendant."Id"
+                    FROM "BaseItems" AS Descendant
+                    INNER JOIN Orphan ON Descendant."ParentId" = Orphan."Id"
+                )
+                SELECT "Id" FROM Orphan;
+
+                -- Keep the play state of the doomed items the way ItemPersistenceService does when it
+                -- deletes an item: reattach it to the placeholder item instead of letting the
+                -- FK_UserData_BaseItems_ItemId cascade wipe it. The placeholder can only hold one row
+                -- per (UserId, CustomDataKey), so resolve collisions before repointing anything.
+                DELETE FROM "UserData"
+                WHERE "ItemId" = '00000000-0000-0000-0000-000000000001'
+                  AND EXISTS (
+                      SELECT 1
+                      FROM "UserData" AS Doomed
+                      INNER JOIN "OrphanedBaseItemIds" AS Orphan ON Orphan."Id" = Doomed."ItemId"
+                      WHERE Doomed."UserId" = "UserData"."UserId"
+                        AND Doomed."CustomDataKey" = "UserData"."CustomDataKey");
+
+                DELETE FROM "UserData"
+                WHERE "ItemId" IN (SELECT "Id" FROM "OrphanedBaseItemIds")
+                  AND "rowid" NOT IN (
+                      SELECT MIN("rowid")
+                      FROM "UserData"
+                      WHERE "ItemId" IN (SELECT "Id" FROM "OrphanedBaseItemIds")
+                      GROUP BY "UserId", "CustomDataKey");
+
+                UPDATE "UserData"
+                SET "ItemId" = '00000000-0000-0000-0000-000000000001',
+                    "RetentionDate" = datetime('now')
+                WHERE "ItemId" IN (SELECT "Id" FROM "OrphanedBaseItemIds");
+
+                -- FK_LinkedChildren_BaseItems_{ParentId,ChildId} are NO ACTION, so these rows have to
+                -- go by hand or the delete below fails on them.
+                DELETE FROM "LinkedChildren"
+                WHERE "ParentId" IN (SELECT "Id" FROM "OrphanedBaseItemIds")
+                   OR "ChildId" IN (SELECT "Id" FROM "OrphanedBaseItemIds");
+
+                DELETE FROM "BaseItems" WHERE "Id" IN (SELECT "Id" FROM "OrphanedBaseItemIds");
+
+                DROP TABLE "OrphanedBaseItemIds";
+                """);
+
             // Normalize OwnerId to uppercase GUID format
             migrationBuilder.Sql(
                 @"UPDATE BaseItems
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260815063607_RemoveOrphanedUserPermissionsAndPreferences.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260815063607_RemoveOrphanedUserPermissionsAndPreferences.cs
index 3d4cf90441..2530e84af6 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260815063607_RemoveOrphanedUserPermissionsAndPreferences.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260815063607_RemoveOrphanedUserPermissionsAndPreferences.cs
@@ -11,8 +11,8 @@ namespace Jellyfin.Database.Providers.Sqlite.Migrations
         /// <inheritdoc />
         protected override void Up(MigrationBuilder migrationBuilder)
         {
-            migrationBuilder.Sql("DELETE FROM Permissions WHERE UserId IS NULL;");
-            migrationBuilder.Sql("DELETE FROM Preferences WHERE UserId IS NULL;");
+            migrationBuilder.Sql("DELETE FROM Permissions WHERE UserId IS NULL OR UserId NOT IN (SELECT Id FROM Users);");
+            migrationBuilder.Sql("DELETE FROM Preferences WHERE UserId IS NULL OR UserId NOT IN (SELECT Id FROM Users);");
 
             migrationBuilder.DropIndex(
                 name: "IX_Preferences_UserId_Kind",