diff options
Diffstat (limited to 'src/Jellyfin.Database')
12 files changed, 2246 insertions, 81 deletions
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs index b821476390..2ba3faf5f7 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs @@ -22,6 +22,65 @@ public static class DescendantQueryHelper b => !b.IsFolder && !b.IsVirtualItem; /// <summary> + /// Gets the predicate identifying the items that stand on their own in a library. An alternate + /// version is a second file for the item that links it rather than an item beside it, and an owned + /// item belongs to its owner unless it is an extra (a trailer and the like, which carries both an + /// owner and an extra type). Nothing here turns on who is asking, so a count that applies it + /// answers the same with a user and without one. + /// </summary> + public static Expression<Func<BaseItemEntity, bool>> IsDistinctLibraryItem { get; } = + b => !b.PrimaryVersionId.HasValue && (!b.OwnerId.HasValue || b.ExtraType != null); + + /// <summary> + /// Builds the predicate identifying the items a user has played, counting a multi-version item as + /// played when any of its alternate versions is. Mirrors the aggregation + /// <c>VersionResumeData.ApplyTo</c> performs on the played flag a single item reports, so that a + /// folder's unplayed count cannot disagree with the watched state its members render with. + /// </summary> + /// <param name="userId">The id of the user whose played state to test.</param> + /// <returns>The predicate matching the items that user has played.</returns> + public static Expression<Func<BaseItemEntity, bool>> IsPlayedBy(Guid userId) => + b => b.UserData!.Any(u => u.UserId.Equals(userId) && u.Played) + || b.LinkedChildEntities!.Any(lc => + (lc.ChildType == LinkedChildType.LocalAlternateVersion || lc.ChildType == LinkedChildType.LinkedAlternateVersion) + && lc.Child!.UserData!.Any(u => u.UserId.Equals(userId) && u.Played)); + + /// <summary> + /// Builds the projection pairing an item's id with <see cref="IsPlayedBy"/> evaluated on that same + /// row. A caller that needs the flag alongside the id composes it rather than testing membership of + /// the played set: as a sub-select the set is unbounded by whatever the caller joins it to, so the + /// database builds it from the whole table once per place it appears. + /// </summary> + /// <param name="userId">The id of the user whose played state to test.</param> + /// <returns>The projection of each item onto its id and that user's played state.</returns> + public static Expression<Func<BaseItemEntity, LeafPlayedState>> PlayedStateBy(Guid userId) + { + var played = IsPlayedBy(userId); + var item = played.Parameters[0]; + + // Named members, as the compiler emits for an anonymous type: without them the query provider + // cannot read a later `x.Id` back to the column it was built from and gives up translating. + return Expression.Lambda<Func<BaseItemEntity, LeafPlayedState>>( + Expression.New( + typeof(LeafPlayedState).GetConstructor([typeof(Guid), typeof(bool)])!, + [Expression.Property(item, nameof(BaseItemEntity.Id)), played.Body], + [typeof(LeafPlayedState).GetProperty(nameof(LeafPlayedState.Id))!, typeof(LeafPlayedState).GetProperty(nameof(LeafPlayedState.Played))!]), + item); + } + + /// <summary> + /// Builds the negation of <see cref="IsPlayedBy"/>, so a caller filtering for unplayed items reads + /// the same definition of played as one filtering for played items. + /// </summary> + /// <param name="userId">The id of the user whose played state to test.</param> + /// <returns>The predicate matching the items that user has not played.</returns> + public static Expression<Func<BaseItemEntity, bool>> IsUnplayedBy(Guid userId) + { + var played = IsPlayedBy(userId); + return Expression.Lambda<Func<BaseItemEntity, bool>>(Expression.Not(played.Body), played.Parameters); + } + + /// <summary> /// Gets a queryable of all descendant IDs for a parent item. /// Traverses AncestorIds and LinkedChildren to find all descendants. /// </summary> diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs index 77abb45f2a..6efced6556 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs @@ -45,8 +45,19 @@ public interface IJellyfinDatabaseProvider Task RunScheduledOptimisation(CancellationToken cancellationToken); /// <summary> - /// If supported this should perform any actions that are required on stopping the jellyfin server, including the - /// same maintenance as <see cref="RunScheduledOptimisation(CancellationToken)"/>. + /// If supported this should refresh the query planner statistics, e.g. after a library scan changed the data. + /// Unlike <see cref="RunScheduledOptimisation(CancellationToken)"/> it should not reclaim space, so that it stays + /// cheap enough to run after every scan. + /// </summary> + /// <param name="cancellationToken">The token to abort the operation.</param> + /// <returns>A <see cref="Task"/> representing the asynchronous operation.</returns> + Task RefreshStatistics(CancellationToken cancellationToken) => Task.CompletedTask; + + /// <summary> + /// If supported this should perform any actions that are required on stopping the jellyfin server. This runs + /// against a deadline imposed by the service manager, so unlike + /// <see cref="RunScheduledOptimisation(CancellationToken)"/> it should only do work whose cost does not grow with + /// the size of the database. /// </summary> /// <param name="cancellationToken">The token that will be used to abort the operation.</param> /// <returns>A <see cref="Task"/> representing the asynchronous operation.</returns> diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/LeafPlayedState.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/LeafPlayedState.cs new file mode 100644 index 0000000000..0846013c45 --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/LeafPlayedState.cs @@ -0,0 +1,10 @@ +using System; + +namespace Jellyfin.Database.Implementations; + +/// <summary> +/// An item's id paired with whether a given user has played it. +/// </summary> +/// <param name="Id">The id of the item.</param> +/// <param name="Played">Whether the user has played the item.</param> +public readonly record struct LeafPlayedState(Guid Id, bool Played); diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Locking/OptimisticLockBehavior.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Locking/OptimisticLockBehavior.cs index 29a073ff74..585ee82854 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Locking/OptimisticLockBehavior.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Locking/OptimisticLockBehavior.cs @@ -46,9 +46,9 @@ public class OptimisticLockBehavior : IEntityFrameworkCoreLockingBehavior TimeSpan.FromSeconds(3) ]; - Func<int, Context, TimeSpan> backoffProvider = (index, context) => + Func<int, Context, TimeSpan> backoffProvider = (retryNo, context) => { - var backoff = sleepDurations[index]; + var backoff = sleepDurations[retryNo - 1]; return backoff + TimeSpan.FromMilliseconds(RandomNumberGenerator.GetInt32(0, (int)(backoff.TotalMilliseconds * .5))); }; diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs index ee36be035b..9e846056ac 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs @@ -35,9 +35,11 @@ public class BaseItemConfiguration : IEntityTypeConfiguration<BaseItemEntity> builder.HasIndex(e => e.Path); builder.HasIndex(e => e.ParentId); - builder.HasIndex(e => e.OwnerId); + builder.HasIndex(e => e.OwnerId) + .HasFilter("\"OwnerId\" IS NOT NULL"); builder.HasIndex(e => e.Name); - builder.HasIndex(e => new { e.ExtraType, e.OwnerId }); + builder.HasIndex(e => new { e.ExtraType, e.OwnerId }) + .HasFilter("\"ExtraType\" IS NOT NULL"); builder.HasIndex(e => e.PresentationUniqueKey); // covering index builder.HasIndex(e => new { e.TopParentId, e.Id }); 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..8c03347736 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,74 @@ 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. + DROP TABLE IF EXISTS "DoomedUserDataKeys"; + CREATE TEMPORARY TABLE "DoomedUserDataKeys" ( + "UserId" TEXT NOT NULL, + "CustomDataKey" TEXT NOT NULL, + PRIMARY KEY ("UserId", "CustomDataKey")); + + -- Collect the colliding keys up front: correlating against "UserData" directly makes + -- the delete below re-scan every row of that user once per placeholder row. + INSERT OR IGNORE INTO "DoomedUserDataKeys" ("UserId", "CustomDataKey") + SELECT Doomed."UserId", Doomed."CustomDataKey" + FROM "UserData" AS Doomed + INNER JOIN "OrphanedBaseItemIds" AS Orphan ON Orphan."Id" = Doomed."ItemId"; + + DELETE FROM "UserData" + WHERE "ItemId" = '00000000-0000-0000-0000-000000000001' + AND EXISTS ( + SELECT 1 + FROM "DoomedUserDataKeys" AS Doomed + 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 "DoomedUserDataKeys"; + DROP TABLE "OrphanedBaseItemIds"; + """); + // Normalize OwnerId to uppercase GUID format migrationBuilder.Sql( @"UPDATE BaseItems @@ -51,41 +119,9 @@ namespace Jellyfin.Database.Providers.Sqlite.Migrations columns: new[] { "BaseItemEntityId", "Name", "OwnerId" }, values: new object[] { null, "This is a placeholder item for UserData that has been detached from its original item", null }); - migrationBuilder.CreateIndex( - name: "IX_BaseItems_BaseItemEntityId", - table: "BaseItems", - column: "BaseItemEntityId"); - - migrationBuilder.CreateIndex( - name: "IX_BaseItems_ExtraType", - table: "BaseItems", - column: "ExtraType"); - - migrationBuilder.CreateIndex( - name: "IX_BaseItems_ExtraType_OwnerId", - table: "BaseItems", - columns: new[] { "ExtraType", "OwnerId" }); - - migrationBuilder.CreateIndex( - name: "IX_BaseItems_OwnerId", - table: "BaseItems", - column: "OwnerId"); - - migrationBuilder.CreateIndex( - name: "IX_BaseItems_TopParentId_IsFolder_IsVirtualItem_DateCreated", - table: "BaseItems", - columns: new[] { "TopParentId", "IsFolder", "IsVirtualItem", "DateCreated" }); - - migrationBuilder.CreateIndex( - name: "IX_BaseItems_TopParentId_MediaType_IsVirtualItem_DateCreated", - table: "BaseItems", - columns: new[] { "TopParentId", "MediaType", "IsVirtualItem", "DateCreated" }); - - migrationBuilder.CreateIndex( - name: "IX_BaseItems_TopParentId_Type_IsVirtualItem_DateCreated", - table: "BaseItems", - columns: new[] { "TopParentId", "Type", "IsVirtualItem", "DateCreated" }); - + // No CreateIndex calls here on purpose: AddForeignKey rebuilds BaseItems on SQLite and + // recreates every index of the target model afterwards, so building them first only + // pays for a full index pass that the rebuild immediately throws away. migrationBuilder.AddForeignKey( name: "FK_BaseItems_BaseItems_BaseItemEntityId", table: "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", diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20261002210711_MakeOwnerIdIndexesPartial.Designer.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20261002210711_MakeOwnerIdIndexesPartial.Designer.cs new file mode 100644 index 0000000000..4407809410 --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20261002210711_MakeOwnerIdIndexesPartial.Designer.cs @@ -0,0 +1,1809 @@ +// <auto-generated /> +using System; +using Jellyfin.Database.Implementations; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Infrastructure; +using Microsoft.EntityFrameworkCore.Migrations; +using Microsoft.EntityFrameworkCore.Storage.ValueConversion; + +#nullable disable + +namespace Jellyfin.Database.Providers.Sqlite.Migrations +{ + [DbContext(typeof(JellyfinDbContext))] + [Migration("20261002210711_MakeOwnerIdIndexesPartial")] + partial class MakeOwnerIdIndexesPartial + { + /// <inheritdoc /> + protected override void BuildTargetModel(ModelBuilder modelBuilder) + { +#pragma warning disable 612, 618 + modelBuilder.HasAnnotation("ProductVersion", "10.0.11"); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AccessSchedule", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<int>("DayOfWeek") + .HasColumnType("INTEGER"); + + b.Property<double>("EndHour") + .HasColumnType("REAL"); + + b.Property<double>("StartHour") + .HasColumnType("REAL"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId"); + + b.ToTable("AccessSchedules"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ActivityLog", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<DateTime>("DateCreated") + .HasColumnType("TEXT"); + + b.Property<string>("ItemId") + .HasMaxLength(256) + .HasColumnType("TEXT"); + + b.Property<int>("LogSeverity") + .HasColumnType("INTEGER"); + + b.Property<string>("Name") + .IsRequired() + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property<string>("Overview") + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property<uint>("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property<string>("ShortOverview") + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property<string>("Type") + .IsRequired() + .HasMaxLength(256) + .HasColumnType("TEXT"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("DateCreated"); + + b.ToTable("ActivityLogs"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AncestorId", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<Guid>("ParentItemId") + .HasColumnType("TEXT"); + + b.HasKey("ItemId", "ParentItemId"); + + b.HasIndex("ParentItemId"); + + b.ToTable("AncestorIds"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AttachmentStreamInfo", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<int>("Index") + .HasColumnType("INTEGER"); + + b.Property<string>("Codec") + .HasColumnType("TEXT"); + + b.Property<string>("CodecTag") + .HasColumnType("TEXT"); + + b.Property<string>("Comment") + .HasColumnType("TEXT"); + + b.Property<string>("Filename") + .HasColumnType("TEXT"); + + b.Property<string>("MimeType") + .HasColumnType("TEXT"); + + b.HasKey("ItemId", "Index"); + + b.ToTable("AttachmentStreamInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemEntity", b => + { + b.Property<Guid>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property<string>("Album") + .HasColumnType("TEXT"); + + b.Property<string>("AlbumArtists") + .HasColumnType("TEXT"); + + b.Property<string>("Artists") + .HasColumnType("TEXT"); + + b.Property<int?>("Audio") + .HasColumnType("INTEGER"); + + b.Property<Guid?>("ChannelId") + .HasColumnType("TEXT"); + + b.Property<string>("CleanName") + .HasColumnType("TEXT"); + + b.Property<float?>("CommunityRating") + .HasColumnType("REAL"); + + b.Property<float?>("CriticRating") + .HasColumnType("REAL"); + + b.Property<string>("CustomRating") + .HasColumnType("TEXT"); + + b.Property<string>("Data") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("DateCreated") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("DateLastMediaAdded") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("DateLastRefreshed") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("DateLastSaved") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("DateModified") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("EndDate") + .HasColumnType("TEXT"); + + b.Property<string>("EpisodeTitle") + .HasColumnType("TEXT"); + + b.Property<string>("ExternalId") + .HasColumnType("TEXT"); + + b.Property<string>("ExternalSeriesId") + .HasColumnType("TEXT"); + + b.Property<string>("ExternalServiceId") + .HasColumnType("TEXT"); + + b.Property<int?>("ExtraType") + .HasColumnType("INTEGER"); + + b.Property<string>("ForcedSortName") + .HasColumnType("TEXT"); + + b.Property<string>("Genres") + .HasColumnType("TEXT"); + + b.Property<int?>("Height") + .HasColumnType("INTEGER"); + + b.Property<int?>("IndexNumber") + .HasColumnType("INTEGER"); + + b.Property<int?>("InheritedParentalRatingSubValue") + .HasColumnType("INTEGER"); + + b.Property<int?>("InheritedParentalRatingValue") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsFolder") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsInMixedFolder") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsLocked") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsMovie") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsRepeat") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsSeries") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsVirtualItem") + .HasColumnType("INTEGER"); + + b.Property<float?>("LUFS") + .HasColumnType("REAL"); + + b.Property<string>("MediaType") + .HasColumnType("TEXT"); + + b.Property<string>("Name") + .HasColumnType("TEXT"); + + b.Property<float?>("NormalizationGain") + .HasColumnType("REAL"); + + b.Property<string>("OfficialRating") + .HasColumnType("TEXT"); + + b.Property<string>("OriginalLanguage") + .HasColumnType("TEXT"); + + b.Property<string>("OriginalTitle") + .HasColumnType("TEXT"); + + b.Property<string>("Overview") + .HasColumnType("TEXT"); + + b.Property<Guid?>("OwnerId") + .HasColumnType("TEXT"); + + b.Property<Guid?>("ParentId") + .HasColumnType("TEXT"); + + b.Property<int?>("ParentIndexNumber") + .HasColumnType("INTEGER"); + + b.Property<string>("Path") + .HasColumnType("TEXT"); + + b.Property<string>("PreferredMetadataCountryCode") + .HasColumnType("TEXT"); + + b.Property<string>("PreferredMetadataLanguage") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("PremiereDate") + .HasColumnType("TEXT"); + + b.Property<string>("PresentationUniqueKey") + .HasColumnType("TEXT"); + + b.Property<Guid?>("PrimaryVersionId") + .HasColumnType("TEXT"); + + b.Property<string>("ProductionLocations") + .HasColumnType("TEXT"); + + b.Property<int?>("ProductionYear") + .HasColumnType("INTEGER"); + + b.Property<long?>("RunTimeTicks") + .HasColumnType("INTEGER"); + + b.Property<Guid?>("SeasonId") + .HasColumnType("TEXT"); + + b.Property<string>("SeasonName") + .HasColumnType("TEXT"); + + b.Property<Guid?>("SeriesId") + .HasColumnType("TEXT"); + + b.Property<string>("SeriesName") + .HasColumnType("TEXT"); + + b.Property<string>("SeriesPresentationUniqueKey") + .HasColumnType("TEXT"); + + b.Property<string>("ShowId") + .HasColumnType("TEXT"); + + b.Property<long?>("Size") + .HasColumnType("INTEGER"); + + b.Property<string>("SortName") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("StartDate") + .HasColumnType("TEXT"); + + b.Property<string>("Studios") + .HasColumnType("TEXT"); + + b.Property<string>("Tagline") + .HasColumnType("TEXT"); + + b.Property<string>("Tags") + .HasColumnType("TEXT"); + + b.Property<Guid?>("TopParentId") + .HasColumnType("TEXT"); + + b.Property<int?>("TotalBitrate") + .HasColumnType("INTEGER"); + + b.Property<string>("Type") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<string>("UnratedType") + .HasColumnType("TEXT"); + + b.Property<int?>("Width") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("Name"); + + b.HasIndex("OwnerId") + .HasFilter("\"OwnerId\" IS NOT NULL"); + + b.HasIndex("ParentId"); + + b.HasIndex("Path"); + + b.HasIndex("PresentationUniqueKey"); + + b.HasIndex("PrimaryVersionId") + .HasFilter("\"PrimaryVersionId\" IS NOT NULL"); + + b.HasIndex("SeasonId"); + + b.HasIndex("SeriesId"); + + b.HasIndex("SeriesName"); + + b.HasIndex("ExtraType", "OwnerId") + .HasFilter("\"ExtraType\" IS NOT NULL"); + + b.HasIndex("TopParentId", "Id"); + + b.HasIndex("Type", "CleanName"); + + b.HasIndex("TopParentId", "Type", "IsVirtualItem") + .HasFilter("\"PrimaryVersionId\" IS NULL AND (\"OwnerId\" IS NULL OR \"ExtraType\" IS NOT NULL)"); + + b.HasIndex("Type", "TopParentId", "Id"); + + b.HasIndex("Type", "TopParentId", "PresentationUniqueKey"); + + b.HasIndex("Type", "TopParentId", "SortName"); + + b.HasIndex("Type", "TopParentId", "StartDate"); + + b.HasIndex("MediaType", "TopParentId", "IsVirtualItem", "PresentationUniqueKey"); + + b.HasIndex("TopParentId", "IsFolder", "IsVirtualItem", "DateCreated"); + + b.HasIndex("TopParentId", "MediaType", "IsVirtualItem", "DateCreated"); + + b.HasIndex("TopParentId", "Type", "IsVirtualItem", "DateCreated"); + + b.HasIndex("Type", "SeriesPresentationUniqueKey", "IsFolder", "IsVirtualItem"); + + b.HasIndex("Type", "SeriesPresentationUniqueKey", "ParentIndexNumber", "IndexNumber"); + + b.HasIndex("Type", "SeriesPresentationUniqueKey", "PresentationUniqueKey", "SortName"); + + b.HasIndex("IsFolder", "TopParentId", "IsVirtualItem", "PresentationUniqueKey", "DateCreated"); + + b.HasIndex("Type", "TopParentId", "IsVirtualItem", "PresentationUniqueKey", "DateCreated"); + + b.ToTable("BaseItems"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + + b.HasData( + new + { + Id = new Guid("00000000-0000-0000-0000-000000000001"), + IsFolder = false, + IsInMixedFolder = false, + IsLocked = false, + IsMovie = false, + IsRepeat = false, + IsSeries = false, + IsVirtualItem = false, + Name = "This is a placeholder item for UserData that has been detached from its original item", + Type = "PLACEHOLDER" + }); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemImageInfo", b => + { + b.Property<Guid>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property<byte[]>("Blurhash") + .HasColumnType("BLOB"); + + b.Property<DateTime?>("DateModified") + .HasColumnType("TEXT"); + + b.Property<int>("Height") + .HasColumnType("INTEGER"); + + b.Property<int>("ImageType") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<string>("Path") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<int>("Width") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("ItemId", "ImageType"); + + b.ToTable("BaseItemImageInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemMetadataField", b => + { + b.Property<int>("Id") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.HasKey("Id", "ItemId"); + + b.HasIndex("ItemId"); + + b.ToTable("BaseItemMetadataFields"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemProvider", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<string>("ProviderId") + .HasColumnType("TEXT"); + + b.Property<string>("ProviderValue") + .IsRequired() + .HasColumnType("TEXT"); + + b.HasKey("ItemId", "ProviderId"); + + b.HasIndex("ProviderId", "ItemId", "ProviderValue"); + + b.ToTable("BaseItemProviders"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemTrailerType", b => + { + b.Property<int>("Id") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.HasKey("Id", "ItemId"); + + b.HasIndex("ItemId"); + + b.ToTable("BaseItemTrailerTypes"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Chapter", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<int>("ChapterIndex") + .HasColumnType("INTEGER"); + + b.Property<DateTime?>("ImageDateModified") + .HasColumnType("TEXT"); + + b.Property<string>("ImagePath") + .HasColumnType("TEXT"); + + b.Property<string>("Name") + .HasColumnType("TEXT"); + + b.Property<long>("StartPositionTicks") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "ChapterIndex"); + + b.ToTable("Chapters"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.CustomItemDisplayPreferences", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<string>("Client") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<string>("Key") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.Property<string>("Value") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId", "ItemId", "Client", "Key") + .IsUnique(); + + b.ToTable("CustomItemDisplayPreferences"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.DisplayPreferences", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<int>("ChromecastVersion") + .HasColumnType("INTEGER"); + + b.Property<string>("Client") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<string>("DashboardTheme") + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<bool>("EnableNextVideoInfoOverlay") + .HasColumnType("INTEGER"); + + b.Property<int?>("IndexBy") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<int>("ScrollDirection") + .HasColumnType("INTEGER"); + + b.Property<bool>("ShowBackdrop") + .HasColumnType("INTEGER"); + + b.Property<bool>("ShowSidebar") + .HasColumnType("INTEGER"); + + b.Property<int>("SkipBackwardLength") + .HasColumnType("INTEGER"); + + b.Property<int>("SkipForwardLength") + .HasColumnType("INTEGER"); + + b.Property<string>("TvHome") + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId", "ItemId", "Client") + .IsUnique(); + + b.ToTable("DisplayPreferences"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.HomeSection", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<int>("DisplayPreferencesId") + .HasColumnType("INTEGER"); + + b.Property<int>("Order") + .HasColumnType("INTEGER"); + + b.Property<int>("Type") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("DisplayPreferencesId"); + + b.ToTable("HomeSection"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ImageInfo", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<DateTime>("LastModified") + .HasColumnType("TEXT"); + + b.Property<string>("Path") + .IsRequired() + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property<Guid?>("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId") + .IsUnique(); + + b.ToTable("ImageInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemDisplayPreferences", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<string>("Client") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<int?>("IndexBy") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<bool>("RememberIndexing") + .HasColumnType("INTEGER"); + + b.Property<bool>("RememberSorting") + .HasColumnType("INTEGER"); + + b.Property<string>("SortBy") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.Property<int>("SortOrder") + .HasColumnType("INTEGER"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.Property<int>("ViewType") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("UserId"); + + b.ToTable("ItemDisplayPreferences"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemValue", b => + { + b.Property<Guid>("ItemValueId") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property<string>("CleanValue") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<int>("Type") + .HasColumnType("INTEGER"); + + b.Property<string>("Value") + .IsRequired() + .HasColumnType("TEXT"); + + b.HasKey("ItemValueId"); + + b.HasIndex("Type", "CleanValue"); + + b.HasIndex("Type", "Value") + .IsUnique(); + + b.ToTable("ItemValues"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemValueMap", b => + { + b.Property<Guid>("ItemValueId") + .HasColumnType("TEXT"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.HasKey("ItemValueId", "ItemId"); + + b.HasIndex("ItemId"); + + b.ToTable("ItemValuesMap"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.KeyframeData", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.PrimitiveCollection<string>("KeyframeTicks") + .HasColumnType("TEXT"); + + b.Property<long>("TotalDuration") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId"); + + b.ToTable("KeyframeData"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.LinkedChildEntity", b => + { + b.Property<Guid>("ParentId") + .HasColumnType("TEXT"); + + b.Property<int>("SortOrder") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ChildId") + .HasColumnType("TEXT"); + + b.Property<int>("ChildType") + .HasColumnType("INTEGER"); + + b.HasKey("ParentId", "SortOrder"); + + b.HasIndex("ChildId", "ChildType"); + + b.HasIndex("ParentId", "ChildType"); + + b.ToTable("LinkedChildren", (string)null); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.MediaSegment", b => + { + b.Property<Guid>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property<long>("EndTicks") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<string>("SegmentProviderId") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<long>("StartTicks") + .HasColumnType("INTEGER"); + + b.Property<int>("Type") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.ToTable("MediaSegments"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.MediaStreamInfo", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<int>("StreamIndex") + .HasColumnType("INTEGER"); + + b.Property<string>("AspectRatio") + .HasColumnType("TEXT"); + + b.Property<float?>("AverageFrameRate") + .HasColumnType("REAL"); + + b.Property<int?>("BitDepth") + .HasColumnType("INTEGER"); + + b.Property<int?>("BitRate") + .HasColumnType("INTEGER"); + + b.Property<int?>("BlPresentFlag") + .HasColumnType("INTEGER"); + + b.Property<string>("ChannelLayout") + .HasColumnType("TEXT"); + + b.Property<int?>("Channels") + .HasColumnType("INTEGER"); + + b.Property<string>("Codec") + .HasColumnType("TEXT"); + + b.Property<string>("CodecTag") + .HasColumnType("TEXT"); + + b.Property<string>("CodecTimeBase") + .HasColumnType("TEXT"); + + b.Property<string>("ColorPrimaries") + .HasColumnType("TEXT"); + + b.Property<string>("ColorSpace") + .HasColumnType("TEXT"); + + b.Property<string>("ColorTransfer") + .HasColumnType("TEXT"); + + b.Property<string>("Comment") + .HasColumnType("TEXT"); + + b.Property<int?>("DvBlSignalCompatibilityId") + .HasColumnType("INTEGER"); + + b.Property<int?>("DvLevel") + .HasColumnType("INTEGER"); + + b.Property<int?>("DvProfile") + .HasColumnType("INTEGER"); + + b.Property<int?>("DvVersionMajor") + .HasColumnType("INTEGER"); + + b.Property<int?>("DvVersionMinor") + .HasColumnType("INTEGER"); + + b.Property<int?>("ElPresentFlag") + .HasColumnType("INTEGER"); + + b.Property<bool?>("Hdr10PlusPresentFlag") + .HasColumnType("INTEGER"); + + b.Property<int?>("Height") + .HasColumnType("INTEGER"); + + b.Property<bool?>("IsAnamorphic") + .HasColumnType("INTEGER"); + + b.Property<bool?>("IsAvc") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsDefault") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsExternal") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsForced") + .HasColumnType("INTEGER"); + + b.Property<bool?>("IsHearingImpaired") + .HasColumnType("INTEGER"); + + b.Property<bool?>("IsInterlaced") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsOriginal") + .HasColumnType("INTEGER"); + + b.Property<string>("KeyFrames") + .HasColumnType("TEXT"); + + b.Property<string>("Language") + .HasColumnType("TEXT"); + + b.Property<float?>("Level") + .HasColumnType("REAL"); + + b.Property<string>("NalLengthSize") + .HasColumnType("TEXT"); + + b.Property<string>("Path") + .HasColumnType("TEXT"); + + b.Property<string>("PixelFormat") + .HasColumnType("TEXT"); + + b.Property<string>("Profile") + .HasColumnType("TEXT"); + + b.Property<float?>("RealFrameRate") + .HasColumnType("REAL"); + + b.Property<int?>("RefFrames") + .HasColumnType("INTEGER"); + + b.Property<int?>("Rotation") + .HasColumnType("INTEGER"); + + b.Property<int?>("RpuPresentFlag") + .HasColumnType("INTEGER"); + + b.Property<int?>("SampleRate") + .HasColumnType("INTEGER"); + + b.Property<int>("StreamType") + .HasColumnType("INTEGER"); + + b.Property<string>("TimeBase") + .HasColumnType("TEXT"); + + b.Property<string>("Title") + .HasColumnType("TEXT"); + + b.Property<int?>("Width") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "StreamIndex"); + + b.HasIndex("StreamType", "ItemId", "Language", "IsExternal"); + + b.ToTable("MediaStreamInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.People", b => + { + b.Property<Guid>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property<string>("Name") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<string>("PersonType") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("Name"); + + b.ToTable("Peoples"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.PeopleBaseItemMap", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<Guid>("PeopleId") + .HasColumnType("TEXT"); + + b.Property<string>("Role") + .HasColumnType("TEXT"); + + b.Property<int?>("ListOrder") + .HasColumnType("INTEGER"); + + b.Property<int?>("SortOrder") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "PeopleId", "Role"); + + b.HasIndex("ItemId", "ListOrder"); + + b.HasIndex("ItemId", "SortOrder"); + + b.HasIndex("PeopleId", "ItemId"); + + b.ToTable("PeopleBaseItemMap"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Permission", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<int>("Kind") + .HasColumnType("INTEGER"); + + b.Property<uint>("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.Property<bool>("Value") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("UserId", "Kind") + .IsUnique(); + + b.ToTable("Permissions"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Preference", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<int>("Kind") + .HasColumnType("INTEGER"); + + b.Property<uint>("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.Property<string>("Value") + .IsRequired() + .HasMaxLength(65535) + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId", "Kind") + .IsUnique(); + + b.ToTable("Preferences"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Security.ApiKey", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<string>("AccessToken") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<DateTime>("DateCreated") + .HasColumnType("TEXT"); + + b.Property<DateTime>("DateLastActivity") + .HasColumnType("TEXT"); + + b.Property<string>("Name") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("AccessToken") + .IsUnique(); + + b.ToTable("ApiKeys"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Security.Device", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<string>("AccessToken") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<string>("AppName") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.Property<string>("AppVersion") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<DateTime>("DateCreated") + .HasColumnType("TEXT"); + + b.Property<DateTime>("DateLastActivity") + .HasColumnType("TEXT"); + + b.Property<DateTime>("DateModified") + .HasColumnType("TEXT"); + + b.Property<string>("DeviceId") + .IsRequired() + .HasMaxLength(256) + .HasColumnType("TEXT"); + + b.Property<string>("DeviceName") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.Property<bool>("IsActive") + .HasColumnType("INTEGER"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("AccessToken", "DateLastActivity"); + + b.HasIndex("DeviceId", "DateLastActivity"); + + b.HasIndex("UserId", "DeviceId"); + + b.ToTable("Devices"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Security.DeviceOptions", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<string>("CustomName") + .HasColumnType("TEXT"); + + b.Property<string>("DeviceId") + .IsRequired() + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("DeviceId") + .IsUnique(); + + b.ToTable("DeviceOptions"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.TrickplayInfo", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<int>("Width") + .HasColumnType("INTEGER"); + + b.Property<int>("Bandwidth") + .HasColumnType("INTEGER"); + + b.Property<int>("Height") + .HasColumnType("INTEGER"); + + b.Property<int>("Interval") + .HasColumnType("INTEGER"); + + b.Property<int>("ThumbnailCount") + .HasColumnType("INTEGER"); + + b.Property<int>("TileHeight") + .HasColumnType("INTEGER"); + + b.Property<int>("TileWidth") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "Width"); + + b.ToTable("TrickplayInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.User", b => + { + b.Property<Guid>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property<string>("AudioLanguagePreference") + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property<string>("AuthenticationProviderId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property<string>("CastReceiverId") + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<bool>("DisplayCollectionsView") + .HasColumnType("INTEGER"); + + b.Property<bool>("DisplayMissingEpisodes") + .HasColumnType("INTEGER"); + + b.Property<bool>("EnableAutoLogin") + .HasColumnType("INTEGER"); + + b.Property<bool>("EnableLocalPassword") + .HasColumnType("INTEGER"); + + b.Property<bool>("EnableNextEpisodeAutoPlay") + .HasColumnType("INTEGER"); + + b.Property<bool>("EnableUserPreferenceAccess") + .HasColumnType("INTEGER"); + + b.Property<bool>("HidePlayedInLatest") + .HasColumnType("INTEGER"); + + b.Property<long>("InternalId") + .HasColumnType("INTEGER"); + + b.Property<int>("InvalidLoginAttemptCount") + .HasColumnType("INTEGER"); + + b.Property<DateTime?>("LastActivityDate") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("LastLoginDate") + .HasColumnType("TEXT"); + + b.Property<int?>("LoginAttemptsBeforeLockout") + .HasColumnType("INTEGER"); + + b.Property<int>("MaxActiveSessions") + .HasColumnType("INTEGER"); + + b.Property<int?>("MaxParentalRatingScore") + .HasColumnType("INTEGER"); + + b.Property<int?>("MaxParentalRatingSubScore") + .HasColumnType("INTEGER"); + + b.Property<bool>("MustUpdatePassword") + .HasColumnType("INTEGER"); + + b.Property<string>("NormalizedUsername") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property<string>("Password") + .HasMaxLength(65535) + .HasColumnType("TEXT"); + + b.Property<string>("PasswordResetProviderId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property<bool>("PlayDefaultAudioTrack") + .HasColumnType("INTEGER"); + + b.Property<bool>("RememberAudioSelections") + .HasColumnType("INTEGER"); + + b.Property<bool>("RememberSubtitleSelections") + .HasColumnType("INTEGER"); + + b.Property<int?>("RemoteClientBitrateLimit") + .HasColumnType("INTEGER"); + + b.Property<uint>("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property<string>("SubtitleLanguagePreference") + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property<int>("SubtitleMode") + .HasColumnType("INTEGER"); + + b.Property<int>("SyncPlayAccess") + .HasColumnType("INTEGER"); + + b.Property<string>("Username") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("NormalizedUsername") + .IsUnique(); + + b.HasIndex("Username") + .IsUnique(); + + b.ToTable("Users"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.UserData", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.Property<string>("CustomDataKey") + .HasColumnType("TEXT"); + + b.Property<int?>("AudioStreamIndex") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsFavorite") + .HasColumnType("INTEGER"); + + b.Property<DateTime?>("LastPlayedDate") + .HasColumnType("TEXT"); + + b.Property<bool?>("Likes") + .HasColumnType("INTEGER"); + + b.Property<int>("PlayCount") + .HasColumnType("INTEGER"); + + b.Property<long>("PlaybackPositionTicks") + .HasColumnType("INTEGER"); + + b.Property<bool>("Played") + .HasColumnType("INTEGER"); + + b.Property<double?>("Rating") + .HasColumnType("REAL"); + + b.Property<DateTime?>("RetentionDate") + .HasColumnType("TEXT"); + + b.Property<int?>("SubtitleStreamIndex") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "UserId", "CustomDataKey"); + + b.HasIndex("ItemId", "UserId", "IsFavorite"); + + b.HasIndex("ItemId", "UserId", "LastPlayedDate"); + + b.HasIndex("ItemId", "UserId", "PlaybackPositionTicks"); + + b.HasIndex("ItemId", "UserId", "Played"); + + b.HasIndex("UserId", "IsFavorite", "ItemId"); + + b.HasIndex("UserId", "ItemId", "LastPlayedDate"); + + b.HasIndex("UserId", "Played", "ItemId"); + + b.ToTable("UserData"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AccessSchedule", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("AccessSchedules") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AncestorId", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Parents") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "ParentItem") + .WithMany("Children") + .HasForeignKey("ParentItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + + b.Navigation("ParentItem"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AttachmentStreamInfo", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany() + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemEntity", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Owner") + .WithMany("Extras") + .HasForeignKey("OwnerId") + .OnDelete(DeleteBehavior.NoAction); + + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "DirectParent") + .WithMany("DirectChildren") + .HasForeignKey("ParentId") + .OnDelete(DeleteBehavior.Cascade); + + b.Navigation("DirectParent"); + + b.Navigation("Owner"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemImageInfo", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Images") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemMetadataField", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("LockedFields") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemProvider", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Provider") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemTrailerType", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("TrailerTypes") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Chapter", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Chapters") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.DisplayPreferences", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("DisplayPreferences") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.HomeSection", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.DisplayPreferences", null) + .WithMany("HomeSections") + .HasForeignKey("DisplayPreferencesId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ImageInfo", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithOne("ProfileImage") + .HasForeignKey("Jellyfin.Database.Implementations.Entities.ImageInfo", "UserId") + .OnDelete(DeleteBehavior.Cascade); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemDisplayPreferences", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("ItemDisplayPreferences") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemValueMap", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("ItemValues") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.ItemValue", "ItemValue") + .WithMany("BaseItemsMap") + .HasForeignKey("ItemValueId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + + b.Navigation("ItemValue"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.KeyframeData", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany() + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.LinkedChildEntity", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Child") + .WithMany("LinkedChildOfEntities") + .HasForeignKey("ChildId") + .OnDelete(DeleteBehavior.NoAction) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Parent") + .WithMany("LinkedChildEntities") + .HasForeignKey("ParentId") + .OnDelete(DeleteBehavior.NoAction) + .IsRequired(); + + b.Navigation("Child"); + + b.Navigation("Parent"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.MediaStreamInfo", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("MediaStreams") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.PeopleBaseItemMap", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Peoples") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.People", "People") + .WithMany("BaseItems") + .HasForeignKey("PeopleId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + + b.Navigation("People"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Permission", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("Permissions") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Preference", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("Preferences") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Security.Device", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", "User") + .WithMany() + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("User"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.UserData", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("UserData") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.User", "User") + .WithMany() + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + + b.Navigation("User"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemEntity", b => + { + b.Navigation("Chapters"); + + b.Navigation("Children"); + + b.Navigation("DirectChildren"); + + b.Navigation("Extras"); + + b.Navigation("Images"); + + b.Navigation("ItemValues"); + + b.Navigation("LinkedChildEntities"); + + b.Navigation("LinkedChildOfEntities"); + + b.Navigation("LockedFields"); + + b.Navigation("MediaStreams"); + + b.Navigation("Parents"); + + b.Navigation("Peoples"); + + b.Navigation("Provider"); + + b.Navigation("TrailerTypes"); + + b.Navigation("UserData"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.DisplayPreferences", b => + { + b.Navigation("HomeSections"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemValue", b => + { + b.Navigation("BaseItemsMap"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.People", b => + { + b.Navigation("BaseItems"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.User", b => + { + b.Navigation("AccessSchedules"); + + b.Navigation("DisplayPreferences"); + + b.Navigation("ItemDisplayPreferences"); + + b.Navigation("Permissions"); + + b.Navigation("Preferences"); + + b.Navigation("ProfileImage"); + }); +#pragma warning restore 612, 618 + } + } +} diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20261002210711_MakeOwnerIdIndexesPartial.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20261002210711_MakeOwnerIdIndexesPartial.cs new file mode 100644 index 0000000000..96aab1778c --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20261002210711_MakeOwnerIdIndexesPartial.cs @@ -0,0 +1,56 @@ +using Microsoft.EntityFrameworkCore.Migrations; + +#nullable disable + +namespace Jellyfin.Database.Providers.Sqlite.Migrations +{ + /// <inheritdoc /> + public partial class MakeOwnerIdIndexesPartial : Migration + { + /// <inheritdoc /> + protected override void Up(MigrationBuilder migrationBuilder) + { + migrationBuilder.DropIndex( + name: "IX_BaseItems_ExtraType_OwnerId", + table: "BaseItems"); + + migrationBuilder.DropIndex( + name: "IX_BaseItems_OwnerId", + table: "BaseItems"); + + migrationBuilder.CreateIndex( + name: "IX_BaseItems_ExtraType_OwnerId", + table: "BaseItems", + columns: new[] { "ExtraType", "OwnerId" }, + filter: "\"ExtraType\" IS NOT NULL"); + + migrationBuilder.CreateIndex( + name: "IX_BaseItems_OwnerId", + table: "BaseItems", + column: "OwnerId", + filter: "\"OwnerId\" IS NOT NULL"); + } + + /// <inheritdoc /> + protected override void Down(MigrationBuilder migrationBuilder) + { + migrationBuilder.DropIndex( + name: "IX_BaseItems_ExtraType_OwnerId", + table: "BaseItems"); + + migrationBuilder.DropIndex( + name: "IX_BaseItems_OwnerId", + table: "BaseItems"); + + migrationBuilder.CreateIndex( + name: "IX_BaseItems_ExtraType_OwnerId", + table: "BaseItems", + columns: new[] { "ExtraType", "OwnerId" }); + + migrationBuilder.CreateIndex( + name: "IX_BaseItems_OwnerId", + table: "BaseItems", + column: "OwnerId"); + } + } +} diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs index 0c35ccc992..d3df73d2f6 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs @@ -365,7 +365,8 @@ namespace Jellyfin.Server.Implementations.Migrations b.HasIndex("Name"); - b.HasIndex("OwnerId"); + b.HasIndex("OwnerId") + .HasFilter("\"OwnerId\" IS NOT NULL"); b.HasIndex("ParentId"); @@ -382,7 +383,8 @@ namespace Jellyfin.Server.Implementations.Migrations b.HasIndex("SeriesName"); - b.HasIndex("ExtraType", "OwnerId"); + b.HasIndex("ExtraType", "OwnerId") + .HasFilter("\"ExtraType\" IS NOT NULL"); b.HasIndex("TopParentId", "Id"); diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs index f11cde7e48..a3fbe42e02 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs @@ -1,5 +1,6 @@ using System; using System.Collections.Generic; +using System.Data.Common; using System.Globalization; using System.IO; using System.Linq; @@ -109,6 +110,35 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider } /// <inheritdoc/> + public async Task RefreshStatistics(CancellationToken cancellationToken) + { + if (DbContextFactory is null) + { + return; + } + + var context = await DbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false); + await using (context.ConfigureAwait(false)) + { + await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false); + try + { + if (!await HasLibraryItemsAsync(context, cancellationToken).ConfigureAwait(false)) + { + return; + } + + _logger.LogInformation("Analyzing jellyfin.db"); + await AnalyzeAsync(context, cancellationToken).ConfigureAwait(false); + } + finally + { + await context.Database.CloseConnectionAsync().ConfigureAwait(false); + } + } + } + + /// <inheritdoc/> public void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.SetDefaultDateTimeKind(DateTimeKind.Utc); @@ -117,17 +147,32 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider /// <inheritdoc/> public async Task RunShutdownTask(CancellationToken cancellationToken) { - // Run before disposing the application + // Run before disposing the application. Only a checkpoint: stopping is on a deadline. + + // Empty the pool first. Anything still parked in it can start reading again between here and the + // checkpoint, and a reader that holds the write-ahead log open is exactly what makes the truncation + // fail. Connections handed out already cannot be taken away, but they get disposed on return. + SqliteConnection.ClearAllPools(); + try { - await OptimizeAsync(cancellationToken).ConfigureAwait(false); + if (DbContextFactory is not null) + { + var context = await DbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false); + await using (context.ConfigureAwait(false)) + { + await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false); + } + } } catch (Exception ex) { - // A missed optimization only costs performance, so never fail the shutdown over this. - _logger.LogError(ex, "Error while optimizing jellyfin.db"); + // A missed checkpoint only leaves a write-ahead log for the next start to replay, so never fail the + // shutdown over this. + _logger.LogError(ex, "Error while checkpointing jellyfin.db"); } + // The checkpointing connection went back into the pool, so retire that one as well. SqliteConnection.ClearAllPools(); } @@ -141,13 +186,96 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider var context = await DbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false); await using (context.ConfigureAwait(false)) { - await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false); - await context.Database.ExecuteSqlRawAsync("VACUUM", cancellationToken).ConfigureAwait(false); - await context.Database.ExecuteSqlRawAsync("PRAGMA analysis_limit=0", cancellationToken).ConfigureAwait(false); + await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false); + try + { + long? tempStore; + var pragmaCommand = context.Database.GetDbConnection().CreateCommand(); + await using (pragmaCommand.ConfigureAwait(false)) + { + pragmaCommand.CommandText = "PRAGMA temp_store"; + tempStore = await ReadPragmaValueAsync(pragmaCommand, cancellationToken).ConfigureAwait(false); + } + + await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false); + + _logger.LogDebug( + "Rebuilding jellyfin.db on disk, scratch space goes to {TempDirectory}", + Environment.GetEnvironmentVariable("SQLITE_TMPDIR") ?? "SQLite's default temporary directory"); + await context.Database.ExecuteSqlRawAsync("PRAGMA temp_store=1", cancellationToken).ConfigureAwait(false); + try + { + await context.Database.ExecuteSqlRawAsync("VACUUM", cancellationToken).ConfigureAwait(false); + } + finally + { + // The connection goes back to the pool, so hand it over the way it was handed to us. + if (tempStore is not null) + { + await context.Database.ExecuteSqlRawAsync( + FormattableString.Invariant($"PRAGMA temp_store={tempStore.Value}"), + CancellationToken.None).ConfigureAwait(false); + } + } + + // Statistics taken while the library is empty make the planner treat every table as one row and + // pick full scans once it fills up; no statistics at all plan far better until there is data. + if (await HasLibraryItemsAsync(context, cancellationToken).ConfigureAwait(false)) + { + await AnalyzeAsync(context, cancellationToken).ConfigureAwait(false); + } + else + { + _logger.LogInformation("Not analyzing jellyfin.db, the library holds no items yet"); + } + + await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false); + _logger.LogInformation("jellyfin.db optimized successfully!"); + } + finally + { + await context.Database.CloseConnectionAsync().ConfigureAwait(false); + } + } + } + + private static Task<bool> HasLibraryItemsAsync(JellyfinDbContext context, CancellationToken cancellationToken) + { + // Folders and the seeded placeholder exist before any library has been scanned. + return context.BaseItems.AnyAsync(e => !e.IsFolder && e.Type != "PLACEHOLDER", cancellationToken); + } + + private static async Task AnalyzeAsync(JellyfinDbContext context, CancellationToken cancellationToken) + { + long? analysisLimit; + var pragmaCommand = context.Database.GetDbConnection().CreateCommand(); + await using (pragmaCommand.ConfigureAwait(false)) + { + pragmaCommand.CommandText = "PRAGMA analysis_limit"; + analysisLimit = await ReadPragmaValueAsync(pragmaCommand, cancellationToken).ConfigureAwait(false); + } + + await context.Database.ExecuteSqlRawAsync("PRAGMA analysis_limit=0", cancellationToken).ConfigureAwait(false); + try + { await context.Database.ExecuteSqlRawAsync("ANALYZE", cancellationToken).ConfigureAwait(false); - await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false); - _logger.LogInformation("jellyfin.db optimized successfully!"); } + finally + { + // The connection goes back to the pool, so hand it over the way it was handed to us. + if (analysisLimit is not null) + { + await context.Database.ExecuteSqlRawAsync( + FormattableString.Invariant($"PRAGMA analysis_limit={analysisLimit.Value}"), + CancellationToken.None).ConfigureAwait(false); + } + } + } + + private static async Task<long?> ReadPragmaValueAsync(DbCommand command, CancellationToken cancellationToken) + { + var value = await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false); + return value is null or DBNull ? null : Convert.ToInt64(value, CultureInfo.InvariantCulture); } /// <inheritdoc/> @@ -157,16 +285,31 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider } /// <inheritdoc /> - public Task<string> MigrationBackupFast(CancellationToken cancellationToken) + public async Task<string> MigrationBackupFast(CancellationToken cancellationToken) { - var key = DateTime.UtcNow.ToString("yyyyMMddhhmmss", CultureInfo.InvariantCulture); var path = Path.Combine(_applicationPaths.DataPath, "jellyfin.db"); - var backupFile = Path.Combine(_applicationPaths.DataPath, BackupFolderName); - Directory.CreateDirectory(backupFile); + var backupFolder = Path.Combine(_applicationPaths.DataPath, BackupFolderName); + Directory.CreateDirectory(backupFolder); + + if (DbContextFactory is not null && File.Exists(path)) + { + var context = await DbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false); + await using (context.ConfigureAwait(false)) + { + await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false); + } + } + + var key = DateTime.UtcNow.ToString("yyyyMMddHHmmss", CultureInfo.InvariantCulture); + var backupFile = Path.Combine(backupFolder, $"{key}_jellyfin.db"); + for (var attempt = 1; File.Exists(backupFile); attempt++) + { + key = string.Create(CultureInfo.InvariantCulture, $"{DateTime.UtcNow:yyyyMMddHHmmss}_{attempt}"); + backupFile = Path.Combine(backupFolder, $"{key}_jellyfin.db"); + } - backupFile = Path.Combine(backupFile, $"{key}_jellyfin.db"); File.Copy(path, backupFile); - return Task.FromResult(key); + return key; } /// <inheritdoc /> @@ -183,10 +326,55 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider return Task.CompletedTask; } + if (!TryRetireWriteAheadLog(path)) + { + _logger.LogCritical( + "Refusing to restore jellyfin.db: the write-ahead log at {WriteAheadLog} could not be retired, which " + + "means the database is still open and replacing it now would silently bring back the data this " + + "rollback is undoing. Stop the server and copy {Backup} over {Path} by hand.", + path + "-wal", + backupFile, + path); + return Task.CompletedTask; + } + File.Copy(backupFile, path, true); + return Task.CompletedTask; } + private bool TryRetireWriteAheadLog(string path) + { + var writeAheadLogPath = path + "-wal"; + if (!File.Exists(path) || !File.Exists(writeAheadLogPath)) + { + return true; + } + + try + { + var connectionString = new SqliteConnectionStringBuilder + { + DataSource = path, + Mode = SqliteOpenMode.ReadWrite, + Pooling = false + }.ToString(); + + using var connection = new SqliteConnection(connectionString); + connection.Open(); + using var command = connection.CreateCommand(); + command.CommandText = "PRAGMA wal_checkpoint(TRUNCATE)"; + command.ExecuteNonQuery(); + } + catch (SqliteException ex) + { + // Either something else holds the database or it is too damaged to open. The check below covers both. + _logger.LogError(ex, "Could not open jellyfin.db to retire its write-ahead log"); + } + + return !File.Exists(writeAheadLogPath); + } + /// <inheritdoc /> public Task DeleteBackup(string key) { |