Wprowadzenie
W dwóch ostatnich wpisach na blogu omawiałem zapis historii zmian danych w aplikacji z wykorzystaniem mechanizmu Audit z Entity Framework Plus. Mechanizm tamten działał w momencie, gdy korzystaliśmy z Entity Framework. Jednak nie zawsze chcemy lub możemy korzystać z tego ORMa. Dlatego w dzisiejszym wpisie chcę Ci pokazać mechanizm, który działa zupełnie inaczej i jest dostępny w samym silniku SQL Server. Mechanizm ten nazywa się Temporal Tables i jest dostępny od wersji 2016.
Temporal Tables
Microsoft w wersji 2016 dodał mechanizm Temporal Tables, który umożliwia nam zapis historii zmian danych w dodatkowej tabeli. Dodatkowa tabela ma taką samą strukturę, jak tabele z aktualnymi danymi. Natomiast SQL Server automatycznie dodaje do niej informacje w momencie zmiany danych w pierwotnej tabeli.
Aby ten mechanizm działał, musi zostać spełnione kilka warunków. Przede wszystkim we właściwej tabeli musimy mieć dodatkowe dwie kolumny, które określają, w jakim czasie (UTC) dane są aktualne. Na ogół możemy spotkać takie nazwy kolumn, jak ValidFrom/ValidTo lub SysStartTime/SysEndTime.
W momencie dodawania nowego rekordu do tabeli SQL Server automatycznie ustawia początek okresu na aktualny czas w strefie UTC. Natomiast koniec ustawiany jest na wartość 9999-12-31 23:59:59.99, która w praktyce określa, że wartość rekordu jest cały czas aktualna.
W momencie jakiejś operacji na rekordzie (np. aktualizacji danych) SQL Server automatycznie przekopiowuje starą wersję rekordu do tabeli z historią. W kopii ustawia koniec okresu ważności danych na aktualny czas i ten sam czas ustawia jako początek ważności rekordu w głównej tabeli.
Dodatkowo SQL Server udostępnia nam składnię, za pomocą której możemy wyciągnąć wartość rekordu z określonego momentu w czasie. Takie zapytanie jest wykonywane automatycznie na obu tabelach i nie musimy robić tego ręcznie.
Skoro wiemy, jak działa mechanizm Temporal Tables, zobaczmy, jak z niego skorzystać.
Dodanie tabeli z historią
Z mechanizmu Temporal Tables możemy skorzystać na dwa sposoby: włączyć go od razu podczas tworzenia tabeli lub dodać go do już istniejącej.
W przykładzie będziemy bazowali na tabeli Products, które będzie zawierała trzy kolumny: Id, Name oraz Description. Plus kolumny wymagane przez Temporal Tables.
Stworzenie nowej tabeli z włączonym mechanizmem historii wygląda tak:
| CREATE TABLE dbo.Products | |
| ( | |
| [Id] int IDENTITY(1,1) PRIMARY KEY, | |
| [Name] nvarchar(250) NOT NULL, | |
| [Description] varchar(max) NULL, | |
| [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START, | |
| [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END, | |
| PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) | |
| ) | |
| WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductsHistory)); |
Początek instrukcji CREATE TABLE jest taki sam jak zwykle. Zmiany pojawiają się w momencie dodania dodatkowych kolumn. W przykładzie użyłem nazw ValidFrom oraz ValidTo dla początku oraz końca ważności rekordu. Wartość kolumn jest generowana automatycznie przez serwer bazy danych i oznaczona jako początek i koniec ważności rekordu.
Na samym końcu za pomocą klauzy WITH mówimy, że tabele mają mieć włączone wersjonowanie oraz podajemy nazwę tabeli z historią. Warto tutaj podać swoją nazwę, ponieważ wygenerowana nie jest zbyt fajna (np. MSSQL_TemporalHistoryFor_565577053 – domyśl się później, że jest to tabela z historią dla tabeli Products).
Drugim sposobem jest dodanie historii do istniejącej tabeli z wykorzystaniem ALTER TABLE:
| CREATE TABLE dbo.Products | |
| ( | |
| [Id] int IDENTITY(1,1) PRIMARY KEY, | |
| [Name] nvarchar(250) NOT NULL, | |
| [Description] varchar(max) NULL | |
| ); | |
| ALTER TABLE dbo.Products | |
| ADD ValidFrom datetime2(0) | |
| GENERATED ALWAYS AS ROW START NOT NULL | |
| DEFAULT SYSUTCDATETIME(), | |
| ValidTo datetime2(0) | |
| GENERATED ALWAYS AS ROW END NOT NULL | |
| DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2), | |
| PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) | |
| ALTER TABLE dbo.Products | |
| SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductsHistory)) |
Zapis danych w historii
Mając już przygotowaną tabelę z historią, możemy zobaczyć, w jaki sposób SQL Server zapisuje dane w historii.
Zaczniemy od prostego inserta:
| INSERT INTO dbo.Products (Name, Description) VALUES ('Product Name', 'Product Description'); |
W efekcie w tabeli właściwej oraz w historii otrzymamy coś takiego:
Tabela z danymi zawiera jeden wpis aktualny, natomiast historia jest pusta.
W przypadku update pojawi się już wpis w historii:
| UPDATE dbo.Products SET Name = 'Updated Product Name' WHERE Id = 1; |
Warto zwrócić uwagę, że ValidTo rekordu w historii ma taką samą wartość jak ValidFrom w normalnej tabeli.
Ciekawa sytuacja pojawia się w przypadku usuwania rekordu. W tabeli z danymi rekord znika, natomiast w historii dane pozostają. Plus pojawia się informacja o ostatnim ważnym stanie obiektu z datą, do której obowiązywał:
| DELETE FROM dbo.Products WHERE Id = 1; |
Jak widać, informacje ładnie zapisują się w tabeli z historią.
Pobieranie danych
Poza samym zapisaniem historii SQL Server udostępnia również składnię, za pomocą której możemy pobierać dane.
Dzięki FOR SYSTEM_TIME AS OF możemy pobrać wartość danych z określonego momentu w czasie, co widać poniżej:
| SELECT * From dbo.Products | |
| FOR SYSTEM_TIME AS OF '2019-02-25 07:59:00.00' | |
| WHERE Id = 1 | |
| SELECT * From dbo.Products | |
| FOR SYSTEM_TIME AS OF '2019-02-25 08:01:00.00' | |
| WHERE Id = 1 |
Można również wyciągnąć historię dla jakiegoś okresu:
| SELECT * From dbo.Products | |
| FOR SYSTEM_TIME FROM '2019-02-25 07:59:00.00' TO '2019-02-25 08:01:00.00' | |
| WHERE Id = 1 |
Czy wręcz całą historię rekordu:
| SELECT * From dbo.Products | |
| FOR SYSTEM_TIME ALL | |
| WHERE Id = 1 |
Podsumowanie
Temporal Tables z SQL Server jest bardzo ciekawym mechanizmem. Za jego pomocą możemy zapisywać historię rekordów w sposób automatyczny. Podejście to jest o tyle fajne, że jest zaimplementowane w samym silniku, czyli niezależnie od tego, w jaki sposób pracujemy na danych, historia się zapisze. W przypadku Audit z Entity Framework Plus dane zapisane poza aplikacją (np. bezpośrednio w Management Studio) nie zostaną w historii. Z drugiej strony za każdym razem zapisywane są wszystkie dane. Nie mamy za bardzo możliwości konfigurować tego, że na przykład zapisujemy tylko dane z określonej kolumny, jak jest to możliwe w Audit.
Dodatkowo warto pamiętać o tym, że z czasem liczba zapisanych danych może być duża, przez co wielkość bazy może wzrosnąć. W takim przypadku możemy się postarać i zapisywać tylko np. ostatnie trzy wersje. Ale to już temat na oddzielny wpis.
W kolejnym wpisie pokażę, w jaki sposób wykorzystać Temporal Tables w aplikacji .NET.
Szkolenie Entity Framework Core
Zainteresował Ciebie ten temat? A może chcesz więcej? Jak tak to zapraszam na moje autorskie szkolenie z Entity Framework Core.
Czy sama zmiana struktury tabeli nie jest wtedy problematyczna? Czy dane w histori pozostaną po takiej operacji?Czy używałeś tego mechanizmu w komercyjnym projekcie?
Czekam z niecierpliwością na kolejny wpis, ponieważ jak dla mnie włączenie tego powodowało problemy z aktualizają struktury.
Hej Damian!
Używam to w jednym projekcie i na razie spisuje się dobrze (sam mechanizm, gorzej już z użyciem tego w Entity Framework, aby za jego pomocą wyciągać dane). Jeden kolega z biura również używa to w swoim projekcie (ale tylko zapis, bez używania tego w samej aplikacji) i też na razie na to nie narzeka.
A w czym dokładnie miałeś problem? Myślę, że część odpowiedzi może pojawić się w kolejnym wpisie 🙂