From 3be6ef65f4ecdfc564231b9e613b8a307dfdbe64 Mon Sep 17 00:00:00 2001 From: Jake Mannens Date: Mon, 28 Aug 2023 15:45:21 +1000 Subject: Gallery media is now filtered by tag using Object IDs rather than Guids, signficantly reducing DB overhead --- HBContext.cs | 7 +- Media.cs | 4 +- .../20230828053948_TagDefinitionId.Designer.cs | 258 +++++++++++++++++++++ Migrations/20230828053948_TagDefinitionId.cs | 62 +++++ Migrations/HBContextModelSnapshot.cs | 6 +- Pages/Gallery.razor | 5 +- Tag.cs | 8 +- 7 files changed, 337 insertions(+), 13 deletions(-) create mode 100644 Migrations/20230828053948_TagDefinitionId.Designer.cs create mode 100644 Migrations/20230828053948_TagDefinitionId.cs diff --git a/HBContext.cs b/HBContext.cs index d0cd26f..f6bc15c 100644 --- a/HBContext.cs +++ b/HBContext.cs @@ -4,6 +4,9 @@ using HyperBooru.Services; namespace HyperBooru; public class HBContext : DbContext { + public const int NsfwTagId = -1; + public const int IngestTagId = -2; + public static readonly Guid NsfwTag = new("EBDAD4F8-455A-4351-8017-1D4854D6FA38"); public static readonly Guid IngestTag = new("EA212801-5BCC-4C0E-814F-FB9D30DB58BC"); @@ -38,13 +41,13 @@ public class HBContext : DbContext { // These should NEVER change modelBuilder.Entity().HasData(new TagDefinition[] { new() { - ObjectId = -1, + ObjectId = NsfwTagId, Guid = NsfwTag, Source = TagSource.Internal, Name = "nsfw" }, new() { - ObjectId = -2, + ObjectId = IngestTagId, Guid = IngestTag, Source = TagSource.Internal, Name = "ingest" diff --git a/Media.cs b/Media.cs index 750aeb4..47aa73b 100644 --- a/Media.cs +++ b/Media.cs @@ -14,8 +14,8 @@ public class Media : HBObject { public virtual List UploadedFiles { get; set; } = new(); public bool IsIngest => Tags - .Select(t => t.TagDefinition.Guid) - .Contains(HBContext.IngestTag); + .Select(t => t.TagDefinitionId) + .Contains(HBContext.IngestTagId); public string? DisplayName { get { diff --git a/Migrations/20230828053948_TagDefinitionId.Designer.cs b/Migrations/20230828053948_TagDefinitionId.Designer.cs new file mode 100644 index 0000000..0e67836 --- /dev/null +++ b/Migrations/20230828053948_TagDefinitionId.Designer.cs @@ -0,0 +1,258 @@ +// +using System; +using HyperBooru; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Infrastructure; +using Microsoft.EntityFrameworkCore.Migrations; +using Microsoft.EntityFrameworkCore.Storage.ValueConversion; +using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; + +#nullable disable + +namespace HyperBooru.Migrations +{ + [DbContext(typeof(HBContext))] + [Migration("20230828053948_TagDefinitionId")] + partial class TagDefinitionId + { + /// + protected override void BuildTargetModel(ModelBuilder modelBuilder) + { +#pragma warning disable 612, 618 + modelBuilder + .HasAnnotation("ProductVersion", "7.0.10") + .HasAnnotation("Relational:MaxIdentifierLength", 63); + + NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder); + + modelBuilder.Entity("HyperBooru.HBObject", b => + { + b.Property("ObjectId") + .ValueGeneratedOnAdd() + .HasColumnType("integer"); + + NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("ObjectId")); + + b.Property("Guid") + .HasColumnType("uuid"); + + b.HasKey("ObjectId"); + + b.HasIndex("Guid"); + + b.ToTable("Objects", (string)null); + + b.UseTptMappingStrategy(); + }); + + modelBuilder.Entity("HyperBooru.UploadedFile", b => + { + b.Property("UploadedFileId") + .ValueGeneratedOnAdd() + .HasColumnType("integer"); + + NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("UploadedFileId")); + + b.Property("CreateTime") + .HasColumnType("timestamp with time zone"); + + b.Property("Filename") + .HasColumnType("text"); + + b.Property("LastAccessTime") + .HasColumnType("timestamp with time zone"); + + b.Property("LastWriteTime") + .HasColumnType("timestamp with time zone"); + + b.Property("MediaObjectId") + .HasColumnType("integer"); + + b.Property("OriginalChecksum") + .IsRequired() + .HasColumnType("text"); + + b.Property("UploadTime") + .HasColumnType("timestamp with time zone"); + + b.HasKey("UploadedFileId"); + + b.HasIndex("MediaObjectId"); + + b.ToTable("UploadedFiles", (string)null); + }); + + modelBuilder.Entity("TagDefinitionTagDefinition", b => + { + b.Property("ImplicitTagsObjectId") + .HasColumnType("integer"); + + b.Property("TagDefinitionObjectId") + .HasColumnType("integer"); + + b.HasKey("ImplicitTagsObjectId", "TagDefinitionObjectId"); + + b.HasIndex("TagDefinitionObjectId"); + + b.ToTable("TagDefinitionTagDefinition"); + }); + + modelBuilder.Entity("HyperBooru.Media", b => + { + b.HasBaseType("HyperBooru.HBObject"); + + b.Property("Checksum") + .IsRequired() + .HasColumnType("text"); + + b.Property("LongDescription") + .HasColumnType("text"); + + b.Property("MimeType") + .IsRequired() + .HasColumnType("text"); + + b.Property("ShortDescription") + .HasColumnType("text"); + + b.ToTable("Media", (string)null); + }); + + modelBuilder.Entity("HyperBooru.Tag", b => + { + b.HasBaseType("HyperBooru.HBObject"); + + b.Property("CreateTime") + .HasColumnType("timestamp with time zone"); + + b.Property("TagDefinitionId") + .HasColumnType("integer"); + + b.Property("TargetObjectId") + .HasColumnType("integer"); + + b.HasIndex("TagDefinitionId"); + + b.HasIndex("TargetObjectId"); + + b.ToTable("Tags", (string)null); + }); + + modelBuilder.Entity("HyperBooru.TagDefinition", b => + { + b.HasBaseType("HyperBooru.HBObject"); + + b.Property("Alias") + .HasColumnType("text"); + + b.Property("Name") + .IsRequired() + .HasColumnType("text"); + + b.Property("Namespace") + .HasColumnType("text"); + + b.Property("Source") + .HasColumnType("integer"); + + b.ToTable("TagDefinitions", (string)null); + + b.HasData( + new + { + ObjectId = -1, + Guid = new Guid("ebdad4f8-455a-4351-8017-1d4854d6fa38"), + Name = "nsfw", + Source = 0 + }, + new + { + ObjectId = -2, + Guid = new Guid("ea212801-5bcc-4c0e-814f-fb9d30db58bc"), + Name = "ingest", + Source = 0 + }); + }); + + modelBuilder.Entity("HyperBooru.UploadedFile", b => + { + b.HasOne("HyperBooru.Media", "Media") + .WithMany("UploadedFiles") + .HasForeignKey("MediaObjectId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Media"); + }); + + modelBuilder.Entity("TagDefinitionTagDefinition", b => + { + b.HasOne("HyperBooru.TagDefinition", null) + .WithMany() + .HasForeignKey("ImplicitTagsObjectId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("HyperBooru.TagDefinition", null) + .WithMany() + .HasForeignKey("TagDefinitionObjectId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("HyperBooru.Media", b => + { + b.HasOne("HyperBooru.HBObject", null) + .WithOne() + .HasForeignKey("HyperBooru.Media", "ObjectId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("HyperBooru.Tag", b => + { + b.HasOne("HyperBooru.HBObject", null) + .WithOne() + .HasForeignKey("HyperBooru.Tag", "ObjectId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("HyperBooru.TagDefinition", "TagDefinition") + .WithMany() + .HasForeignKey("TagDefinitionId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("HyperBooru.HBObject", "Target") + .WithMany("Tags") + .HasForeignKey("TargetObjectId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("TagDefinition"); + + b.Navigation("Target"); + }); + + modelBuilder.Entity("HyperBooru.TagDefinition", b => + { + b.HasOne("HyperBooru.HBObject", null) + .WithOne() + .HasForeignKey("HyperBooru.TagDefinition", "ObjectId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("HyperBooru.HBObject", b => + { + b.Navigation("Tags"); + }); + + modelBuilder.Entity("HyperBooru.Media", b => + { + b.Navigation("UploadedFiles"); + }); +#pragma warning restore 612, 618 + } + } +} diff --git a/Migrations/20230828053948_TagDefinitionId.cs b/Migrations/20230828053948_TagDefinitionId.cs new file mode 100644 index 0000000..b0d16b5 --- /dev/null +++ b/Migrations/20230828053948_TagDefinitionId.cs @@ -0,0 +1,62 @@ +using Microsoft.EntityFrameworkCore.Migrations; + +#nullable disable + +namespace HyperBooru.Migrations +{ + /// + public partial class TagDefinitionId : Migration + { + /// + protected override void Up(MigrationBuilder migrationBuilder) + { + migrationBuilder.DropForeignKey( + name: "FK_Tags_TagDefinitions_TagDefinitionObjectId", + table: "Tags"); + + migrationBuilder.RenameColumn( + name: "TagDefinitionObjectId", + table: "Tags", + newName: "TagDefinitionId"); + + migrationBuilder.RenameIndex( + name: "IX_Tags_TagDefinitionObjectId", + table: "Tags", + newName: "IX_Tags_TagDefinitionId"); + + migrationBuilder.AddForeignKey( + name: "FK_Tags_TagDefinitions_TagDefinitionId", + table: "Tags", + column: "TagDefinitionId", + principalTable: "TagDefinitions", + principalColumn: "ObjectId", + onDelete: ReferentialAction.Cascade); + } + + /// + protected override void Down(MigrationBuilder migrationBuilder) + { + migrationBuilder.DropForeignKey( + name: "FK_Tags_TagDefinitions_TagDefinitionId", + table: "Tags"); + + migrationBuilder.RenameColumn( + name: "TagDefinitionId", + table: "Tags", + newName: "TagDefinitionObjectId"); + + migrationBuilder.RenameIndex( + name: "IX_Tags_TagDefinitionId", + table: "Tags", + newName: "IX_Tags_TagDefinitionObjectId"); + + migrationBuilder.AddForeignKey( + name: "FK_Tags_TagDefinitions_TagDefinitionObjectId", + table: "Tags", + column: "TagDefinitionObjectId", + principalTable: "TagDefinitions", + principalColumn: "ObjectId", + onDelete: ReferentialAction.Cascade); + } + } +} diff --git a/Migrations/HBContextModelSnapshot.cs b/Migrations/HBContextModelSnapshot.cs index f0e4b1f..e00f2d8 100644 --- a/Migrations/HBContextModelSnapshot.cs +++ b/Migrations/HBContextModelSnapshot.cs @@ -122,13 +122,13 @@ namespace HyperBooru.Migrations b.Property("CreateTime") .HasColumnType("timestamp with time zone"); - b.Property("TagDefinitionObjectId") + b.Property("TagDefinitionId") .HasColumnType("integer"); b.Property("TargetObjectId") .HasColumnType("integer"); - b.HasIndex("TagDefinitionObjectId"); + b.HasIndex("TagDefinitionId"); b.HasIndex("TargetObjectId"); @@ -216,7 +216,7 @@ namespace HyperBooru.Migrations b.HasOne("HyperBooru.TagDefinition", "TagDefinition") .WithMany() - .HasForeignKey("TagDefinitionObjectId") + .HasForeignKey("TagDefinitionId") .OnDelete(DeleteBehavior.Cascade) .IsRequired(); diff --git a/Pages/Gallery.razor b/Pages/Gallery.razor index d00d49b..d89fd3d 100644 --- a/Pages/Gallery.razor +++ b/Pages/Gallery.razor @@ -36,7 +36,6 @@ IEnumerable media = db.Media .Include(m => m.Tags) - .ThenInclude(t => t.TagDefinition) .OrderByDescending(m => m.ObjectId) .ToArray(); @@ -52,11 +51,11 @@ // Filter both NSFW AND ingest images if we're not showing NSFW if(!userService.ShowNsfw) { var nsfwTags = tagService.TagsThatImply(HBContext.NsfwTag) - .Select(td => td.Guid) + .Select(td => td.ObjectId) .ToArray(); media = media .AsEnumerable() - .Where(m => !m.Tags.Select(t => t.TagDefinition.Guid).Intersect(nsfwTags).Any()) + .Where(m => !m.Tags.Select(t => t.TagDefinitionId).Intersect(nsfwTags).Any()) .Where(m => !m.IsIngest); } diff --git a/Tag.cs b/Tag.cs index 81c5f67..0150b39 100644 --- a/Tag.cs +++ b/Tag.cs @@ -18,9 +18,11 @@ public class TagDefinition : HBObject { } public class Tag : HBObject { - public virtual TagDefinition TagDefinition { get; set; } - public DateTime CreateTime { get; set; } = DateTime.UtcNow; - public virtual HBObject Target { get; set; } + [ForeignKey("ObjectId")] + public int TagDefinitionId { get; set; } + public virtual TagDefinition TagDefinition { get; set; } + public DateTime CreateTime { get; set; } = DateTime.UtcNow; + public virtual HBObject Target { get; set; } public Tag() {} -- cgit v1.3