Wersja kontrolująca zawartość bazy danych

Pracuję nad projektem internetowym, który obejmuje treści edytowalne przez użytkownika, i chciałbym móc śledzić wersję rzeczywistej treści, która znajduje się w bazie danych. Zasadniczo chcę wdrożyć historie zmian w stylu wiki.

Przeprowadzając pewne badania w tle, widzę dużo dokumentacji na temat sposobu wersjonowania schematu bazy danych (moja faktycznie jest już kontrolowana), ale wszelkie istniejące strategie dotyczące śledzenia zmian zawartości bazy danych są zagubione przez lawinę elementów wersji schematu, przynajmniej w moich poszukiwaniach.

Mogę wymyślić kilka sposobów na wdrożenie własnego śledzenia zmian, ale wszystkie wydają się dość prymitywne:

• Zapisz cały wiersz przy każdej zmianie, odnieś wiersz z powrotem do identyfikatora źródłowego za pomocą klucza podstawowego (do czego obecnie się skłaniam, jest najprostszy). Wiele drobnych zmian może jednak powodować duże wzdęcia.
• zapisz przed / po / użytkownik / znacznik czasu dla każdej zmiany, z nazwą kolumny, aby odnieść zmianę z powrotem do odpowiedniej kolumny.
• zapisz przed / po / użytkownik / znacznik czasu z tabelą dla każdej kolumny (spowodowałoby to zbyt wiele tabel).
• zapisz diffs / user / timestamp dla każdej zmiany z kolumną (oznaczałoby to, że będziesz musiał przejść całą historię zmian, aby wrócić do określonej daty).

Jakie jest tutaj najlepsze podejście? Wydawanie własnych wydaje się, że prawdopodobnie odkrywam czyjąś (lepszą) bazę kodów.

Punkty bonusowe dla PostgreSQL.

Zwykle stosuję technikę zapisywania całego rekordu z polem end_timestamp. Istnieje reguła biznesowa, że ​​tylko jeden wiersz może mieć zerowy znacznik_czasu i jest to oczywiście aktualnie aktywna treść.

Jeśli zastosujesz ten system, zdecydowanie zalecamy dodanie indeksu lub ograniczenia w celu wymuszenia reguły. Jest to łatwe z Oracle, ponieważ unikalny indeks może zawierać jeden i tylko jeden null. Inne bazy danych mogą stanowić większy problem. Egzekwowanie reguły przez bazę danych sprawi, że Twój kod będzie uczciwy.

Masz całkowitą rację, że wiele drobnych zmian spowoduje wzdęcie, ale musisz to zamienić na kod i prostotę raportowania.

Pamiętaj, że jeśli używasz Microsoft SQL Server, istnieje już funkcja o nazwie Zmiana przechwytywania danych . Nadal będziesz musiał napisać kod, aby uzyskać dostęp do poprzednich wersji później (CDC tworzy dla niego określone widoki), ale przynajmniej nie musisz zmieniać schematu swoich tabel ani implementować samego śledzenia zmian.

Pod maską dzieje się tak:

• CDC tworzy dodatkową tabelę zawierającą poprawki,

• Oryginalna tabela jest używana tak jak wcześniej, to znaczy każda aktualizacja jest bezpośrednio odzwierciedlana w tej tabeli,

• Tabela CDC przechowuje tylko zmienione wartości, co oznacza, że ​​duplikacja danych jest ograniczona do minimum.

Fakt, że zmiany są przechowywane w innej tabeli, ma dwie główne konsekwencje:

• Wybory z oryginalnej tabeli są tak szybkie, jak bez CDC. Jeśli dobrze pamiętam, CDC dzieje się po aktualizacji, więc aktualizacje są równie szybkie (chociaż nie pamiętam dobrze, jak CDC zarządza spójnością danych).

• Niektóre zmiany w schemacie oryginalnej tabeli prowadzą do usunięcia CDC. Na przykład, jeśli dodasz kolumnę, CDC nie wie, jak sobie z tym poradzić. Z drugiej strony dodanie indeksu lub ograniczenia powinno być w porządku. Szybko staje się to problemem, jeśli włączysz CDC na stole, który podlega częstym zmianom. Może istnieć rozwiązanie pozwalające zmienić schemat bez utraty CDC, ale go nie szukałem.

Najpierw rozwiąż problem „filozoficznie” i w kodzie. Następnie „negocjuj” za pomocą kodu i bazy danych, aby tak się stało.

Na przykład , jeśli masz do czynienia z artykułami ogólnymi, wstępna koncepcja artykułu może wyglądać następująco:

class Article {
  public Int32 Id;
  public String Body;
}

I na kolejnym najbardziej podstawowym poziomie chcę zachować listę poprawek:

class Article {
  public Int32 Id;
  public String Body;
  public List<String> Revisions;
}

I może mi przyjść do głowy, że obecne ciało jest tylko najnowszą wersją. A to oznacza dwie rzeczy: potrzebuję każdej edycji, aby była datowana lub numerowana:

class Revision {
  public Int32 Id;
  public Article ParentArticle;
  public DateTime Created;
  public String Body;
}

I ... obecne ciało artykułu nie musi różnić się od najnowszej wersji:

class Article {
  public Int32 Id;
  public String Body {
    get {
      return (Revisions.OrderByDesc(r => r.Created))[0];
    }
    set {
      Revisions.Add(new Revision(value));
    }
  }
  public List<Revision> Revisions;
}

Brakuje kilku szczegółów; ale pokazuje, że prawdopodobnie chcesz dwóch bytów . Jeden reprezentuje artykuł (lub inny typ nagłówka), a drugi to listę poprawek (grupowanie dowolnych pól, które mają sens w „filozoficznym” sensie grupowania). Początkowo nie potrzebujesz specjalnych ograniczeń bazy danych, ponieważ twój kod nie dba o żadną z poprawek samych w sobie - są właściwościami artykułu, który wie o poprawkach.

Nie musisz się więc martwić o oznaczanie poprawek w żaden szczególny sposób lub poleganie na ograniczeniach bazy danych w celu oznaczenia „bieżącego” artykułu. Po prostu oznaczysz je datownikiem (nawet automatyczna identyfikacja będzie odpowiednia), powiązasz je z ich nadrzędnym artykułem i pozwolisz, aby artykuł wiedział, że „najnowszy” jest najbardziej odpowiedni.

I pozwalasz ORM zająć się mniej filozoficznymi szczegółami - lub ukrywasz je w niestandardowej klasie narzędzi, jeśli nie używasz gotowej ORM.

Znacznie później, po przeprowadzeniu testów warunków skrajnych, możesz pomyśleć o uczynieniu tej właściwości leniwym ładowaniem lub o przypisaniu leniwemu atrybutowi Body tylko najwyższej wersji. Ale struktura danych w tym przypadku nie powinna się zmieniać, aby uwzględnić te optymalizacje.

Istnieje strona wiki PostgreSQL dla wyzwalacza śledzenia inspekcji, która przeprowadzi cię przez proces konfigurowania dziennika kontroli, który zrobi to, czego potrzebujesz.

Śledzi pełne oryginalne dane zmiany, a także listę nowych wartości aktualizacji (w przypadku wstawiania i usuwania istnieje tylko jedna wartość). Jeśli chcesz przywrócić starą wersję, możesz pobrać kopię oryginalnych danych z rekordu kontroli. Należy pamiętać, że jeśli dane dotyczą kluczy obcych, rekordy te mogą wymagać wycofania w celu zachowania spójności.

Ogólnie rzecz biorąc, jeśli aplikacja bazy danych spędza większość czasu tylko na bieżących danych, myślę, że lepiej jest śledzić alternatywne wersje w oddzielnej tabeli od bieżących danych. Dzięki temu indeksy aktywnych tabel będą łatwiejsze do zarządzania.

Jeśli śledzone wiersze są bardzo duże, a przestrzeń stanowi poważny problem, możesz spróbować rozbić zmiany i przechowywać minimalne różnice / łaty, ale to zdecydowanie więcej pracy, aby objąć wszystkie rodzaje typów danych. Zrobiłem to wcześniej i bardzo trudno było odbudować stare wersje danych, przechodząc przez wszystkie zmiany wstecz, pojedynczo.

Cóż, skończyło się na tym, że wybrałem najprostszą opcję, wyzwalacz, który kopiuje starą wersję wiersza do dziennika historii poszczególnych tabel.

Jeśli skończę z nadmiarem bazy danych, mogę spojrzeć na ewentualne zawalenie niektórych drobnych zmian historii, jeśli zajdzie taka potrzeba.

Rozwiązanie okazało się dość niechlujne, ponieważ chciałem automatycznie wygenerować funkcje wyzwalacza. Jestem SQLAlchemy, więc byłem w stanie wygenerować tabelę historii, wykonując kilka przejmowań dziedziczenia, co było miłe, ale faktyczne funkcje wyzwalacza zakończyły się wymaganiem mungingu ciągu, aby poprawnie wygenerować funkcje PostgreSQL i odwzorować kolumny z jednej tabeli na inny poprawnie.

W każdym razie wszystko jest tutaj na github .