Index: ChapterX.API/appsettings.json =================================================================== --- ChapterX.API/appsettings.json (revision dc383dd3199c7db4b47fd42b458a73717189db88) +++ ChapterX.API/appsettings.json (revision d300631ac3354730c3b03cb7b160974af50e7894) @@ -11,4 +11,12 @@ "Issuer": "ChapterX", "Audience": "ChapterX" + }, + "ConnectionPool": { + "MinPoolSize": 1, + "MaxPoolSize": 20, + "ConnectionIdleLifetime": 300, + "ConnectionPruningInterval": 10, + "CommandTimeout": 30, + "Timeout": 15 } } Index: ChapterX.Application/Abstractions/IApplicationDbContext.cs =================================================================== --- ChapterX.Application/Abstractions/IApplicationDbContext.cs (revision dc383dd3199c7db4b47fd42b458a73717189db88) +++ ChapterX.Application/Abstractions/IApplicationDbContext.cs (revision d300631ac3354730c3b03cb7b160974af50e7894) @@ -1,3 +1,4 @@ using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Storage; using UserEntity = ChapterX.Domain.Entities.User; using StoryEntity = ChapterX.Domain.Entities.Story; @@ -40,4 +41,5 @@ DbSet NeedApprovals { get; } Task SaveChangesAsync(CancellationToken cancellationToken = default); + Task BeginTransactionAsync(CancellationToken cancellationToken = default); } } Index: ChapterX.Application/Auth/RegisterHandler.cs =================================================================== --- ChapterX.Application/Auth/RegisterHandler.cs (revision dc383dd3199c7db4b47fd42b458a73717189db88) +++ ChapterX.Application/Auth/RegisterHandler.cs (revision d300631ac3354730c3b03cb7b160974af50e7894) @@ -1,2 +1,3 @@ +using ChapterX.Application.Abstractions; using ChapterX.Domain.Repositories; using MediatR; @@ -8,9 +9,11 @@ private readonly IUserRepository _userRepository; private readonly IWriterRepository _writerRepository; + private readonly IApplicationDbContext _context; - public RegisterHandler(IUserRepository userRepository, IWriterRepository writerRepository) + public RegisterHandler(IUserRepository userRepository, IWriterRepository writerRepository, IApplicationDbContext context) { _userRepository = userRepository; _writerRepository = writerRepository; + _context = context; } @@ -21,21 +24,31 @@ throw new InvalidOperationException("Email already in use."); - var user = new Domain.Entities.User + await using var transaction = await _context.BeginTransactionAsync(cancellationToken); + try { - Username = request.Username, - Email = request.Email, - Name = request.Name, - Surname = request.Surname, - Password = BCrypt.Net.BCrypt.HashPassword(request.Password), - CreatedAt = DateTime.UtcNow, - UpdatedAt = DateTime.UtcNow - }; + var user = new Domain.Entities.User + { + Username = request.Username, + Email = request.Email, + Name = request.Name, + Surname = request.Surname, + Password = BCrypt.Net.BCrypt.HashPassword(request.Password), + CreatedAt = DateTime.UtcNow, + UpdatedAt = DateTime.UtcNow + }; - await _userRepository.AddAsync(user, cancellationToken); + await _userRepository.AddAsync(user, cancellationToken); - var writer = new Domain.Entities.Writer { Id = user.Id }; - await _writerRepository.AddAsync(writer, cancellationToken); + var writer = new Domain.Entities.Writer { Id = user.Id }; + await _writerRepository.AddAsync(writer, cancellationToken); - return new RegisterResponse(user.Id, user.Username, user.Email); + await transaction.CommitAsync(cancellationToken); + return new RegisterResponse(user.Id, user.Username, user.Email); + } + catch + { + await transaction.RollbackAsync(cancellationToken); + throw; + } } } Index: ChapterX.Application/Story/Commands/AddHandler.cs =================================================================== --- ChapterX.Application/Story/Commands/AddHandler.cs (revision dc383dd3199c7db4b47fd42b458a73717189db88) +++ ChapterX.Application/Story/Commands/AddHandler.cs (revision d300631ac3354730c3b03cb7b160974af50e7894) @@ -1,2 +1,3 @@ +using ChapterX.Application.Abstractions; using ChapterX.Domain.Repositories; using MediatR; @@ -10,11 +11,13 @@ private readonly IGenreRepository _genreRepository; private readonly IHasGenreRepository _hasGenreRepository; + private readonly IApplicationDbContext _context; private readonly ILogger _logger; - public AddHandler(IStoryRepository storyRepository, IGenreRepository genreRepository, IHasGenreRepository hasGenreRepository, ILogger logger) + public AddHandler(IStoryRepository storyRepository, IGenreRepository genreRepository, IHasGenreRepository hasGenreRepository, IApplicationDbContext context, ILogger logger) { _storyRepository = storyRepository; _genreRepository = genreRepository; _hasGenreRepository = hasGenreRepository; + _context = context; _logger = logger; } @@ -22,25 +25,35 @@ public async Task Handle(AddRequest request, CancellationToken cancellationToken) { - var story = new Domain.Entities.Story + await using var transaction = await _context.BeginTransactionAsync(cancellationToken); + try { - MatureContent = request.MatureContent, - ShortDescription = request.ShortDescription, - Image = request.Image, - Content = request.Content, - UserId = request.UserId, - CreatedAt = DateTime.UtcNow, - UpdatedAt = DateTime.UtcNow - }; + var story = new Domain.Entities.Story + { + MatureContent = request.MatureContent, + ShortDescription = request.ShortDescription, + Image = request.Image, + Content = request.Content, + UserId = request.UserId, + CreatedAt = DateTime.UtcNow, + UpdatedAt = DateTime.UtcNow + }; - await _storyRepository.AddAsync(story, cancellationToken); + await _storyRepository.AddAsync(story, cancellationToken); - foreach (var genreName in request.Genres ?? []) + foreach (var genreName in request.Genres ?? []) + { + var genre = await _genreRepository.GetByNameAsync(genreName, cancellationToken); + if (genre == null) continue; + await _hasGenreRepository.AddAsync(new Domain.Entities.HasGenre { StoryId = story.Id, GenreId = genre.Id }, cancellationToken); + } + + await transaction.CommitAsync(cancellationToken); + return new AddResponse(story.Id); + } + catch { - var genre = await _genreRepository.GetByNameAsync(genreName, cancellationToken); - if (genre == null) continue; - await _hasGenreRepository.AddAsync(new Domain.Entities.HasGenre { StoryId = story.Id, GenreId = genre.Id }, cancellationToken); + await transaction.RollbackAsync(cancellationToken); + throw; } - - return new AddResponse(story.Id); } } Index: ChapterX.Infrastructure/Data/DataContext/ApplicationDbContext.cs =================================================================== --- ChapterX.Infrastructure/Data/DataContext/ApplicationDbContext.cs (revision dc383dd3199c7db4b47fd42b458a73717189db88) +++ ChapterX.Infrastructure/Data/DataContext/ApplicationDbContext.cs (revision d300631ac3354730c3b03cb7b160974af50e7894) @@ -2,4 +2,5 @@ using ChapterX.Domain.Entities; using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Storage; namespace ChapterX.Infrastructure.Data.DataContext @@ -31,4 +32,7 @@ public DbSet PermissionLevels { get; init; } + public Task BeginTransactionAsync(CancellationToken cancellationToken = default) + => Database.BeginTransactionAsync(cancellationToken); + protected override void OnModelCreating(ModelBuilder modelBuilder) { @@ -43,9 +47,9 @@ e.Property(x => x.Username).HasColumnName("username"); e.Property(x => x.Email).HasColumnName("email"); - e.Property(x => x.Name).HasColumnName("name"); + e.Property(x => x.Name).HasColumnName("user_name"); e.Property(x => x.Surname).HasColumnName("surname"); e.Property(x => x.Password).HasColumnName("password"); - e.Property(x => x.CreatedAt).HasColumnName("created_at"); - e.Property(x => x.UpdatedAt).HasColumnName("updated_at"); + e.Property(x => x.CreatedAt).HasColumnName("user_created_at"); + e.Property(x => x.UpdatedAt).HasColumnName("user_updated_at"); }); @@ -86,8 +90,8 @@ e.Property(x => x.ShortDescription).HasColumnName("short_description"); e.Property(x => x.Image).HasColumnName("image"); - e.Property(x => x.Content).HasColumnName("content"); - e.Property(x => x.UserId).HasColumnName("user_id"); - e.Property(x => x.CreatedAt).HasColumnName("created_at"); - e.Property(x => x.UpdatedAt).HasColumnName("updated_at"); + e.Property(x => x.Content).HasColumnName("story_content"); + e.Property(x => x.UserId).HasColumnName("user_id"); + e.Property(x => x.CreatedAt).HasColumnName("story_created_at"); + e.Property(x => x.UpdatedAt).HasColumnName("story_updated_at"); e.HasOne(x => x.Writer).WithMany(w => w.Stories).HasForeignKey(x => x.UserId); }); @@ -113,5 +117,5 @@ e.Property(x => x.Name).HasColumnName("chapter_name"); e.Property(x => x.Title).HasColumnName("title"); - e.Property(x => x.Content).HasColumnName("content"); + e.Property(x => x.Content).HasColumnName("chapter_content"); e.Property(x => x.WordCount).HasColumnName("word_count"); e.Property(x => x.Rating).HasColumnName("rating"); @@ -119,6 +123,6 @@ e.Property(x => x.ViewCount).HasColumnName("view_count"); e.Property(x => x.StoryId).HasColumnName("story_id"); - e.Property(x => x.CreatedAt).HasColumnName("created_at"); - e.Property(x => x.UpdatedAt).HasColumnName("updated_at"); + e.Property(x => x.CreatedAt).HasColumnName("chapter_created_at"); + e.Property(x => x.UpdatedAt).HasColumnName("chapter_updated_at"); e.HasOne(x => x.Story).WithMany(s => s.Chapters).HasForeignKey(x => x.StoryId); }); @@ -130,5 +134,5 @@ e.HasKey(x => x.Id); e.Property(x => x.Id).HasColumnName("genre_id"); - e.Property(x => x.Name).HasColumnName("name"); + e.Property(x => x.Name).HasColumnName("genre_name"); }); @@ -153,5 +157,5 @@ e.Property(x => x.UserId).HasColumnName("user_id"); e.Property(x => x.StoryId).HasColumnName("story_id"); - e.Property(x => x.CreatedAt).HasColumnName("created_at"); + e.Property(x => x.CreatedAt).HasColumnName("like_created_at"); e.HasOne(x => x.User).WithMany(u => u.Likes).HasForeignKey(x => x.UserId); e.HasOne(x => x.Story).WithMany(s => s.Likes).HasForeignKey(x => x.StoryId); @@ -164,9 +168,9 @@ e.HasKey(x => x.Id); e.Property(x => x.Id).HasColumnName("comment_id"); - e.Property(x => x.Content).HasColumnName("content"); - e.Property(x => x.UserId).HasColumnName("user_id"); - e.Property(x => x.StoryId).HasColumnName("story_id"); - e.Property(x => x.CreatedAt).HasColumnName("created_at"); - e.Property(x => x.UpdatedAt).HasColumnName("updated_at"); + e.Property(x => x.Content).HasColumnName("comment_content"); + e.Property(x => x.UserId).HasColumnName("user_id"); + e.Property(x => x.StoryId).HasColumnName("story_id"); + e.Property(x => x.CreatedAt).HasColumnName("comment_created_at"); + e.Property(x => x.UpdatedAt).HasColumnName("comment_updated_at"); e.HasOne(x => x.User).WithMany(u => u.Comments).HasForeignKey(x => x.UserId); e.HasOne(x => x.Story).WithMany(s => s.Comments).HasForeignKey(x => x.StoryId); @@ -179,6 +183,6 @@ e.HasKey(x => x.Id); e.Property(x => x.Id).HasColumnName("list_id"); - e.Property(x => x.Name).HasColumnName("name"); - e.Property(x => x.Content).HasColumnName("content"); + e.Property(x => x.Name).HasColumnName("list_name"); + e.Property(x => x.Content).HasColumnName("list_content"); e.Property(x => x.IsPublic).HasColumnName("is_public"); e.Property(x => x.UserId).HasColumnName("user_id"); @@ -207,7 +211,7 @@ e.HasKey(x => x.Id); e.Property(x => x.Id).HasColumnName("notification_id"); - e.Property(x => x.Content).HasColumnName("content"); + e.Property(x => x.Content).HasColumnName("notification_content"); e.Property(x => x.IsRead).HasColumnName("is_read"); - e.Property(x => x.CreatedAt).HasColumnName("created_at"); + e.Property(x => x.CreatedAt).HasColumnName("notification_created_at"); e.Property(x => x.RecipientUserId).HasColumnName("recipient_user_id"); e.Property(x => x.Type).HasColumnName("type"); @@ -249,5 +253,5 @@ e.Property(x => x.SuggestedText).HasColumnName("suggested_text"); e.Property(x => x.Accepted).HasColumnName("accepted"); - e.Property(x => x.CreatedAt).HasColumnName("created_at"); + e.Property(x => x.CreatedAt).HasColumnName("suggestion_created_at"); e.Property(x => x.AppliedAt).HasColumnName("applied_at"); e.Property(x => x.StoryId).HasColumnName("story_id"); @@ -288,5 +292,5 @@ e.Property(x => x.UserId).HasColumnName("user_id"); e.Property(x => x.StoryId).HasColumnName("story_id"); - e.Property(x => x.CreatedAt).HasColumnName("created_at"); + e.Property(x => x.CreatedAt).HasColumnName("collab_created_at"); e.HasOne(x => x.User).WithMany(u => u.Collaborations).HasForeignKey(x => x.UserId); e.HasOne(x => x.Story).WithMany(s => s.Collaborations).HasForeignKey(x => x.StoryId); Index: ChapterX.Infrastructure/DependencyInjection.cs =================================================================== --- ChapterX.Infrastructure/DependencyInjection.cs (revision dc383dd3199c7db4b47fd42b458a73717189db88) +++ ChapterX.Infrastructure/DependencyInjection.cs (revision d300631ac3354730c3b03cb7b160974af50e7894) @@ -15,6 +15,20 @@ public static IServiceCollection AddInfrastructure(this IServiceCollection services, IConfiguration configuration) { - services.AddDbContext(options => - options.UseNpgsql(configuration.GetConnectionString("Database"))); + var poolSection = configuration.GetSection("ConnectionPool"); + var minPoolSize = poolSection.GetValue("MinPoolSize", 1); + var maxPoolSize = poolSection.GetValue("MaxPoolSize", 20); + var idleLifetime = poolSection.GetValue("ConnectionIdleLifetime", 300); + var pruningInterval = poolSection.GetValue("ConnectionPruningInterval", 10); + var commandTimeout = poolSection.GetValue("CommandTimeout", 30); + var connectionTimeout = poolSection.GetValue("Timeout", 15); + + var baseConnectionString = configuration.GetConnectionString("Database")!; + var connectionString = + $"{baseConnectionString};Minimum Pool Size={minPoolSize};Maximum Pool Size={maxPoolSize};" + + $"Connection Idle Lifetime={idleLifetime};Connection Pruning Interval={pruningInterval};" + + $"Command Timeout={commandTimeout};Timeout={connectionTimeout}"; + + services.AddDbContextPool(options => + options.UseNpgsql(connectionString)); services.AddScoped(sp => sp.GetRequiredService());