Jest na blogu artykuł przedstawiający prototypowania relacyjnej bazy danych z użyciem Entity Framework. Skupia się on na technicznej stronie pracy pokazując szybki sposób uruchomienia bazy.

Dziś trochę bardziej skomplikowany przykład (chociaż wciąż prosty), w którym skupię się na modelowaniu danych. Pokażę też, jak wykorzystać Entity Framework do odwzorowania modelu w aplikacji .NET, utworzenia bazy na serwerze i wdrażania zmian w potoku CI/CD.

Zadanie

Przykład, jak zawsze, będzie praktyczny. Teorię ograniczamy do minimum. Biblioteki są pełne podręczników z teorią. Praktyka developera polega na tym, że rozwiązuje zadania.

Zadanie brzmi:
Zaprojektuj struktury danych, które obsłużą system ankietowy. Ankieta może składać się z wielu pytań, a odpowiedzi mogą być tekstowe

Dość ogólne i pole do interpretacji jest duże. Powiedzmy, że po sesji QA powstała tabelka z wymaganiami.

#Wymaganie
1system ankietowy jest aplikacją webową
2odwiedzający stronę anonimowo nie widzi ankiet
3użytkownik odwiedzający stronę ponownie automatycznie wchodzi w swoją rolę użytkownika,
4odwiedzający stronę może utworzyć użytkownika
5odwiedzający stronę widzi listę użytkowników
6można wybrać “wejdź w rolę” użytkownika
7użytkownik możne przejrzeć listę wszystkich użytkowników
8użytkownik może przejrzeć listę ankiet w których uczestniczy
9użytkownik może otworzyć ankietę w której uczestniczy i edytować swoje odpowiedzi
10użytkownik może tworzyć ankiety
11ankieta utworzona przez użytkownika otrzymuje status “należy do” tego użytkownika
12właściciel ankiety może dodawać i edytować pytania
13właściciel ankiety może usunąć ankietę
14właściciel ankiety może przejrzeć wyniki ankiety (odpowiedzi na pytania)
15właściciel ankiety może dodać innych użytkowników (wtedy oni widzą ankietę i mogą dodać/edytować odpowiedzi)
16ankieta ma pytania
17pytanie może mieć wiele odpowiedzi
18pytanie ma indeks kolejności wyświetlania na liście pytań w danej ankiecie (opcja)
19odpowiedź ma datę edycji (opcja)

Projekt struktury danych

Mamy tu wymagania funkcjonalne (będą przydatne później) oraz wymagania co do struktury danych (#16, #17, #18, #19). Jest też jedno wymagania techniczne (#1). Po odfiltrowaniu tego, co decyduje o strukturze danych rysują się następujące encje i relacje (bez opcjonalnych #19 i #19):

  • użytkownik
  • ankieta
    • użytkownik tworzy ankietę
    • może zaprosić innych użytkowników do jej wypełnienia, czyli należy do użytkowników przy czym jeden jest “właścicielem”
  • pytanie
    • należy do ankiety
    • ankieta może mieć wiele pytań (>=0)
  • odpowiedź na pytanie
    • należy do pytania
    • każdy zaproszony użytkownik może udzielić innej odpowiedzi

Zapisując to jako schemat tabel mamy:

┌─────────────────────────────┐
│ users (użytkownicy) │
├─────────────────────────────┤
┌─1│ PK id int identity │
│ │ UQ name nvarchar │
│ └─────────────────────────────┘
│ ┌─────────────────────────────┐ ┌────────────────────────────────┐
│ │ user_poll (tabela pośrednia)│ │ polls (ankiety) │
│ ├─────────────────────────────┤ ├────────────────────────────────┤
│ │ PK,FK id_poll int │*───1│ PK id int identity │1─┐
├─*│ PK,FK id_user int │ │ title nvarchar │ │
│ │ is_owner bit │ │ description nvarchar NULL │ │
│ └─────────────────────────────┘ └────────────────────────────────┘ │
│ ┌─────────────────────────────┐ ┌────────────────────────────────┐ │
│ │ answers (odpowiedzi) │ │ quetions (pytania) │ │
│ ├─────────────────────────────┤ ├────────────────────────────────┤ │
│ │ PK id int identity │ ┌─1│ PK id int identity │ │
│ │ FK id_question int │*─┘ │ FK id_poll int │*─┘
└─*│ FK id_user int │ │ question nvarchar(1000) │
│ answer nvarchar │ └────────────────────────────────┘
└─────────────────────────────┘

Każda tabela jest w 3 postaci normalnej (3NF) a właściwie nawet spełnia wymagania postaci normalnej Boyce’a-Codda (BCNF).

Wymagania dodatkowe

Struktura danych będzie wykorzystana w aplikacji webowej (wymaganie #1) a backend wykorzystuje Entity Framework. Wiąże się z tym potrzeba dodatkowych tabel przechowujących, np:

  • historię migracji
  • klucze szyfrujące dane cookie, które odpowiada za spełnienie wymagania #3
  • dodatkowa tabela przechowująca ostrzeżenia administracyjne (o tym później)

Wprawdzie nie ma tego w tabeli z wymaganiami, ale analiza UX wskazała, że przyda się kilka dodatkowych funkcji

  • możliwość zmiany kolejności pytań w ankiecie,
  • czas udzielenia odpowiedzi,
  • czasy aktualizacji innych encji,
  • soft delete,
  • i kilka innych.

Zostały one uwzględnione i ostateczny diagram tabel wygląda następująco:

diagram tabel
Rysunek 1. Diagram tabel

Definicja w EF

Następnym krokiem jest odwzorowanie projektku w EF Core. Dla małych kontekstów można umieścić definicje encji w nadpisanej metodzie OnModelCreating(). Pokazałem to w innym moim artykule na tym blogu.
Tu mamy więcej encji i przejrzyściej będzie umieścić definicje w oddzielnych plikach dla każdej encji.

DbContext

W pliku określamy potrzebne DbSety tak, jak zawsze i dodajemy:

PollsDbContext.cs
protected override void OnModelCreating(ModelBuilder modelBuilder) =>
modelBuilder.ApplyConfigurationsFromAssembly(typeof(PollsDbContext).Assembly);

W Infrastructure umieszczamy pliki:

AnswerConfiguration.cs
internal sealed class AnswerConfiguration : IEntityTypeConfiguration<Answer>
{
public void Configure(EntityTypeBuilder<Answer> builder)
{
builder.ToTable("answers", t => t.HasCheckConstraint("ck_answers_answer_not_empty", "LEN([answer]) > 0"));
builder.HasKey(a => a.Id).HasName("pk_answers");
builder.Property(a => a.Id).HasColumnName("id").UseIdentityColumn();
builder.Property(a => a.QuestionId).HasColumnName("id_question");
builder.Property(a => a.UserId).HasColumnName("id_user");
builder.Property(a => a.Text).HasColumnName("answer").HasMaxLength(PollLimits.AnswerMaxLength).IsRequired();
builder.Property(a => a.ModifiedAt).HasColumnName("modified_at").HasDefaultValueSql(DbDefaults.UtcNow);
builder.HasOne(a => a.Question).WithMany(q => q.Answers)
.HasForeignKey(a => a.QuestionId).HasConstraintName("fk_answers_questions")
.OnDelete(DeleteBehavior.NoAction);
builder.HasOne(a => a.User).WithMany()
.HasForeignKey(a => a.UserId).HasConstraintName("fk_answers_users")
.OnDelete(DeleteBehavior.NoAction);
builder.HasIndex(a => new { a.QuestionId, a.UserId }).IsUnique().HasDatabaseName("ux_answers_question_user");
builder.HasIndex(a => a.UserId).HasDatabaseName("ix_answers_user");
builder.HasQueryFilter(a => a.Question.DeletedAt == null && a.Question.Poll.DeletedAt == null);
}
}

A dotnet przy starcie aplikacji zrobi za nas robotę. Odnajdzie klasy implementujące IEntityTypeConfiguration i utworzy modele encji.

Baza danych jako strażnik reguł biznesowych

Aplikacja zakłada, że ankieta ma tylko jednego właściciela. Przypisanie kolejnego użytkownika jako właściciela danej ankiety jest naruszeniem unikalności indeksu. Baza sama pilnuje unikalności, zwalniając z tego obowiązku aplikację.

ParticipantConfiguration.cs
// ParticipantConfiguration odpowiada za tabelę user_poll
builder.HasIndex(p => p.PollId)
.IsUnique()
.HasFilter("[is_owner] = 1")
.HasDatabaseName("ux_user_poll_owner");

Gdybyśmy chcieli zmienić tę regułę, to od strony bazy wystarczy usunąć ten indeks. Przyda się też kilka zmian w aplikacji, ale najważniejsze jest to, że nie zmieniamy schemy.

Utworzenie bazy na serwerze

API nie powinno być zaangażowane w proces wdrożenia samej bazy danych. Dokładanie tam akcji typu MigrateUp() jest antywzorcem. Najzdrowiej jest po prostu wykonać polecenie:

Terminal
CREATE DATABASE polls;

Można to obudować skryptem sprawdzającym, czy baza istnieje.

Migracje

Do wykonania migracji w CI/CD w tym przypadku (database first z EF) mamy dwie podstawowe opcje:

  1. CLI i dotnet ef migrations add <name> oraz dotnet ef database update <name>
  2. Podobnie jak powyżej dodajemy migrację, ale tworzymy skrypt sql komendą dotnet ef migrations script --idempotent

Ta druga opcja jest przeze mnie zalecana. Wygenerowany skrypt zawiera wszystkie migracje od “czystej” bazy do ostatniej migracji i jest idempotentny. Razem z narzędziem sqlcmd tworzą świetny tandem do oskryptowania migracji w CI/CD.

Pierwsza opcja wymaga po prostu zbudowania aplikacji w kontenerze i uruchomienia polecenia dotnet ef database update <name>. Tylko po co tworzyć kontener z sdk tylko w tym celu?

Kubernetes

Front-end i API są przygotowane do uruchomienia na klastrze. W kodzie na Githubie jest folder deploy z manifestami. Jest wersja ogólna, którą można dostosować do każdego klastra oraz wersja pod Docker-desktop. Z README dowiesz się, jak w kilku krokach wdrożyć całość na klaster Kubernetes. Deployment dla Docker-Desktop jest gotowy “z pudełka”. Inny klaster może wymagać modyfikacji manifestów, które są na tę okoliczność przygotowane.

W repozytorium Github są gotowe do pubrania obrazy obu aplikacji.

Aplikacja demo

Cała ta robota nie może zostać w szufladzie. Do zilustrowania, jak zaprojektowana baza działa w rzeczywistości, wykonałem backend jako .NET API. Jak się powiedziało B jak Backend, to trzeba też powiedzieć A jak Angular 😉 Front-end utylizujący API wykonany jest w Angular. Zapraszam do zapoznania się z implementacją: Ankieta demo - kliknij

API

Oprócz głównego adresu aplikacji frontendowej, jest też dostępne api pod selektorem /api. Dokumentacja API prezentowana przez Scalar

Nie, baza się nie wyłoży. Są limity 😉

Podsumowanie