From cd70f54266d708867a1eb35870bc755bc5b2df32 Mon Sep 17 00:00:00 2001 From: ivar Date: Wed, 3 Dec 2025 21:49:20 +0100 Subject: Refactor db --- api/WhatApi/Database/AppDatabase.cs | 113 +++++++++++++++++++++ api/WhatApi/Database/Tables/AuditTrail.cs | 39 +++++++ api/WhatApi/Database/Tables/BaseAuditableEntity.cs | 18 ++++ api/WhatApi/Database/Tables/Content.cs | 20 ++++ api/WhatApi/Database/Tables/IAuditableEntity.cs | 9 ++ api/WhatApi/Database/Tables/Place.cs | 21 ++++ api/WhatApi/Database/Tables/User.cs | 27 +++++ 7 files changed, 247 insertions(+) create mode 100644 api/WhatApi/Database/AppDatabase.cs create mode 100644 api/WhatApi/Database/Tables/AuditTrail.cs create mode 100644 api/WhatApi/Database/Tables/BaseAuditableEntity.cs create mode 100644 api/WhatApi/Database/Tables/Content.cs create mode 100644 api/WhatApi/Database/Tables/IAuditableEntity.cs create mode 100644 api/WhatApi/Database/Tables/Place.cs create mode 100644 api/WhatApi/Database/Tables/User.cs (limited to 'api/WhatApi/Database') diff --git a/api/WhatApi/Database/AppDatabase.cs b/api/WhatApi/Database/AppDatabase.cs new file mode 100644 index 0000000..64c138f --- /dev/null +++ b/api/WhatApi/Database/AppDatabase.cs @@ -0,0 +1,113 @@ +using System.Security.Claims; + +namespace WhatApi.Database; + +public class AppDatabase(DbContextOptions options, IHttpContextAccessor httpContextAccessor, IConfiguration configuration) : DbContext(options) +{ + public DbSet Content => Set(); + public DbSet Places => Set(); + public DbSet Users => Set(); + public DbSet AuditTrails => Set(); + + protected override void OnModelCreating(ModelBuilder b) { + b.HasPostgresExtension("postgis"); + b.ApplyConfiguration(new AuditTrailConfiguration()); + b.ApplyConfiguration(new PlaceConfiguration()); + b.ApplyConfiguration(new UserConfiguration()); + b.ApplyConfiguration(new ContentConfiguration()); + base.OnModelCreating(b); + } + + public override int SaveChanges() { + if (configuration.GetValue("DISABLE_AUDIT_TRAILS")) return base.SaveChanges(); + SetAuditableProperties(); + var auditEntries = GetActiveAuditTrails(); + if (auditEntries.Count != 0) + AuditTrails.AddRange(auditEntries); + return base.SaveChanges(); + } + + public override async Task SaveChangesAsync(CancellationToken cancellationToken = default) { + if (configuration.GetValue("DISABLE_AUDIT_TRAILS")) return await base.SaveChangesAsync(cancellationToken); + SetAuditableProperties(); + var auditEntries = GetActiveAuditTrails(); + if (auditEntries.Count != 0) + await AuditTrails.AddRangeAsync(auditEntries, cancellationToken); + return await base.SaveChangesAsync(cancellationToken); + } + + private List GetActiveAuditTrails() { + var userId = GetUserId(); + var entries = ChangeTracker.Entries() + .Where(e => e.State is EntityState.Added or EntityState.Modified or EntityState.Deleted); + + var auditTrails = new List(); + + foreach (var entry in entries) { + var audit = new AuditTrail { + Id = Guid.NewGuid(), + UserId = userId, + EntityName = entry.Entity.GetType().Name, + DateUtc = DateTimeOffset.UtcNow + }; + + foreach (var prop in entry.Properties) { + if (prop.Metadata.IsPrimaryKey()) { + audit.PrimaryKey = prop.CurrentValue?.ToString(); + continue; + } + + if (prop.Metadata.PropertyInfo?.CustomAttributes.FirstOrDefault(c => c.AttributeType == typeof(AuditTrailIgnoreAttribute)) != null) + continue; + + var name = prop.Metadata.Name; + + switch (entry.State) { + case EntityState.Added: + audit.TrailType = TrailType.Create; + audit.NewValues[name] = prop.CurrentValue; + break; + case EntityState.Deleted: + audit.TrailType = TrailType.Delete; + audit.OldValues[name] = prop.OriginalValue; + break; + case EntityState.Modified: + if (!Equals(prop.OriginalValue, prop.CurrentValue)) { + audit.TrailType = TrailType.Update; + audit.ChangedColumns.Add(name); + audit.OldValues[name] = prop.OriginalValue; + audit.NewValues[name] = prop.CurrentValue; + } + break; + } + } + + if (audit.TrailType != TrailType.None) + auditTrails.Add(audit); + } + + return auditTrails; + } + + private Guid GetUserId() { + var system = new Guid("e87ab078-55bc-4655-86d9-c5b2ecad7162"); + var userIdString = httpContextAccessor.HttpContext?.User.FindFirstValue(ClaimTypes.NameIdentifier); + return string.IsNullOrWhiteSpace(userIdString) ? system : new Guid(userIdString); + } + + private void SetAuditableProperties() { + var actor = GetUserId(); + foreach (var entry in ChangeTracker.Entries()) { + switch (entry.State) { + case EntityState.Added: + entry.Entity.CreatedAtUtc = DateTimeOffset.UtcNow; + entry.Entity.CreatedBy = actor; + break; + case EntityState.Modified: + entry.Entity.UpdatedAtUtc = DateTimeOffset.UtcNow; + entry.Entity.UpdatedBy = actor; + break; + } + } + } +} \ No newline at end of file diff --git a/api/WhatApi/Database/Tables/AuditTrail.cs b/api/WhatApi/Database/Tables/AuditTrail.cs new file mode 100644 index 0000000..4ded46c --- /dev/null +++ b/api/WhatApi/Database/Tables/AuditTrail.cs @@ -0,0 +1,39 @@ +namespace WhatApi.Database.Tables; + +public class AuditTrail +{ + public Guid Id { get; init; } + public Guid? UserId { get; init; } + public required string EntityName { get; init; } + public string? PrimaryKey { get; set; } + public TrailType TrailType { get; set; } + public DateTimeOffset DateUtc { get; init; } + + public Dictionary OldValues { get; init; } = []; + public Dictionary NewValues { get; init; } = []; + public List ChangedColumns { get; init; } = []; +} + +public class AuditTrailConfiguration : IEntityTypeConfiguration +{ + public void Configure(EntityTypeBuilder builder) + { + builder.ToTable("audit_trails"); + builder.HasIndex(e => e.EntityName); + builder.Property(e => e.EntityName).HasMaxLength(100).IsRequired(); + builder.Property(e => e.PrimaryKey).HasMaxLength(100); + builder.Property(e => e.DateUtc).IsRequired(); + builder.Property(e => e.TrailType).HasConversion(); + builder.Property(e => e.OldValues).HasColumnType("jsonb"); + builder.Property(e => e.NewValues).HasColumnType("jsonb"); + builder.Property(e => e.ChangedColumns).HasColumnType("jsonb"); + } +} + +public enum TrailType : byte +{ + None = 0, + Create = 1, + Update = 2, + Delete = 3 +} \ No newline at end of file diff --git a/api/WhatApi/Database/Tables/BaseAuditableEntity.cs b/api/WhatApi/Database/Tables/BaseAuditableEntity.cs new file mode 100644 index 0000000..25fb3fa --- /dev/null +++ b/api/WhatApi/Database/Tables/BaseAuditableEntity.cs @@ -0,0 +1,18 @@ +namespace WhatApi.Database.Tables; + +public class BaseAuditableEntity : IAuditableEntity +{ + public DateTimeOffset CreatedAtUtc { get; set; } + public DateTimeOffset? UpdatedAtUtc { get; set; } + public Guid CreatedBy { get; set; } + public Guid? UpdatedBy { get; set; } + + public void SetCreated(Guid createdBy) { + CreatedBy = createdBy; + CreatedAtUtc = DateTimeOffset.UtcNow; + } + public void SetUpdated(Guid updatedBy) { + UpdatedBy = updatedBy; + UpdatedAtUtc = DateTimeOffset.UtcNow; + } +} \ No newline at end of file diff --git a/api/WhatApi/Database/Tables/Content.cs b/api/WhatApi/Database/Tables/Content.cs new file mode 100644 index 0000000..8148f81 --- /dev/null +++ b/api/WhatApi/Database/Tables/Content.cs @@ -0,0 +1,20 @@ +using System.Net; + +namespace WhatApi.Database.Tables; + +public class Content : BaseAuditableEntity +{ + public Guid Id { get; set; } + public required string Mime { get; set; } + public Guid BlobId { get; set; } + public required IPAddress Ip { get; set; } +} + +public class ContentConfiguration : IEntityTypeConfiguration +{ + public void Configure(EntityTypeBuilder builder) { + builder.HasKey(x => x.Id); + builder.Property(x => x.Mime).HasMaxLength(100).IsRequired(); + builder.ToTable("content"); + } +} \ No newline at end of file diff --git a/api/WhatApi/Database/Tables/IAuditableEntity.cs b/api/WhatApi/Database/Tables/IAuditableEntity.cs new file mode 100644 index 0000000..61d64fd --- /dev/null +++ b/api/WhatApi/Database/Tables/IAuditableEntity.cs @@ -0,0 +1,9 @@ +namespace WhatApi.Database.Tables; + +public interface IAuditableEntity +{ + public DateTimeOffset CreatedAtUtc { get; set; } + public DateTimeOffset? UpdatedAtUtc { get; set; } + public Guid CreatedBy { get; set; } + public Guid? UpdatedBy { get; set; } +} \ No newline at end of file diff --git a/api/WhatApi/Database/Tables/Place.cs b/api/WhatApi/Database/Tables/Place.cs new file mode 100644 index 0000000..2914aa7 --- /dev/null +++ b/api/WhatApi/Database/Tables/Place.cs @@ -0,0 +1,21 @@ +namespace WhatApi.Database.Tables; + +public class Place : BaseAuditableEntity +{ + public Guid Id { get; set; } + public Guid ContentId { get; set; } + public Content Content { get; set; } = null!; + public required Point Location { get; set; } +} + +public class PlaceConfiguration : IEntityTypeConfiguration +{ + public void Configure(EntityTypeBuilder builder) { + builder.ToTable("place"); + builder.HasKey(x => x.Id); + builder.Property(x => x.Location).IsRequired(); + builder.HasOne(x => x.Content); + builder.Property(x => x.Location).HasColumnType($"geometry(point,{Constants.Wgs84SpatialReferenceId})"); + builder.HasIndex(x => x.Location).HasMethod("gist"); + } +} diff --git a/api/WhatApi/Database/Tables/User.cs b/api/WhatApi/Database/Tables/User.cs new file mode 100644 index 0000000..bfcdb50 --- /dev/null +++ b/api/WhatApi/Database/Tables/User.cs @@ -0,0 +1,27 @@ +namespace WhatApi.Database.Tables; + +public class User : BaseAuditableEntity +{ + public Guid Id { get; set; } + public required string Name { get; set; } + public required string Email { get; set; } + public required string PasswordHash { get; set; } + public DateTimeOffset? LastSeen { get; set; } + public IEnumerable Places { get; set; } = null!; + + public void SetLastSeen() { + LastSeen = DateTimeOffset.UtcNow; + } +} + +public class UserConfiguration : IEntityTypeConfiguration +{ + public void Configure(EntityTypeBuilder builder) { + builder.HasKey(x => x.Id); + builder.Property(x => x.Name).HasMaxLength(50); + builder.Property(x => x.Email).HasMaxLength(100); + builder.Property(x => x.PasswordHash).HasMaxLength(100); + builder.HasMany(x => x.Places); + builder.ToTable("user"); + } +} \ No newline at end of file -- cgit v1.3