Czasami stosowany model danych wymaga, by nieaktualne rekordy nie zostawały usuwane z bazy danych, a zostały zachowane do późniejszej analizy. Zazwyczaj rozwiązuje się ten problem poprzez wersjonowanie rekordów. Są różne metody zachowywanie informacji o wersji, ja przedstawię tutaj metodę polegającą na rozszerzeniu tabeli o dwa pola określające okres ważności rekordu:
ValidFrom i
ValidTo.
Uwaga: w całym tekście stosuję założenie, że wszystkie operacje na danych odbywają się po stronie klienta. Oznacza to, że nie mogę stosować ani procedur ani triggerów. Ot takie dodatkowe utrudnienie, ale nic na to nie poradzę... Wszystkie klucze są elementami IDENTITY i są generowane automatycznie po stronie serwera.
Dla tak zdefiniowanej tabeli opreacje SELECT, INSERT, UPDATE i DELETE są zdefiniowane nieco inaczej niż w przypadku standardowym, poniżej przedstawiam odpowiedni kod:
private SqlDataAdapter GetAdapterTable1(DateTime version)
{
SqlDataAdapter ad = new SqlDataAdapter();
SqlCommand cmdSelect = new SqlCommand(
@"SELECT Id, Name, ValidFrom, ValidTo
FROM Table1
WHERE @versionTime BETWEEN ValidFrom AND ValidTo", cnn);
cmdSelect.Parameters.AddWithValue("@versionTime", version);
SqlCommand cmdInsert = new SqlCommand(
@"INSERT INTO Table1(Name, ValidFrom, ValidTo)
VALUES (@Name, @ValidFrom)
SELECT @Id=@@IDENTITY", cnn);
cmdInsert.Parameters.Add("@Id", SqlDbType.Int, 4, "Id");
cmdInsert.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name");
cmdInsert.Parameters.AddWithValue("@ValidFrom", version);
SqlCommand cmdDelete = new SqlCommand(
@"UPDATE Table1 SET ValidTo=@ValidTo WHERE Id=@Id", cnn);
cmdDelete.Parameters.Add("@Id", SqlDbType.Int, 4, "Id");
cmdDelete.Parameters.AddWithValue("@ValidTo", version);
SqlCommand cmdUpdate = new SqlCommand(
@"UPDATE Table1 SET ValidTo=@ValidTo WHERE Id=@Id
INSERT INTO Table1(Name, ValidFrom)
VALUES (@Name, @ValidFrom)
SELECT Id, Name, ValidFrom, ValidTo FROM Table1
WHERE Id=SCOPE_IDENTITY()", cnn);
cmdUpdate.Parameters.Add("@Id", SqlDbType.Int, 4, "Id");
cmdUpdate.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name");
cmdUpdate.Parameters.AddWithValue("@ValidFrom", version);
cmdUpdate.Parameters.AddWithValue("@ValidTo", version);
ad.SelectCommand = cmdSelect;
ad.InsertCommand = cmdInsert;
ad.UpdateCommand = cmdUpdate;
ad.DeleteCommand = cmdDelete;
return ad;
}SELECT i INSERT chyba nie wymagają komentarza, DELETE polega tylko na zmianie daty ważności rekordu, UPDATE to skombinowane DELETE/INSERT/SELECT. W kodzie korzystam z tego, że w tabeli ustawione są predefiniowane wartości dla pól określających ważność rekordu.
Tak mimochodem wspomnę jeszcze (a’ propos
tekstu Pawła), że użycie metody
AddWithValue nie jest w tym miejscu „niebezpieczne”, jako że parametr version zostanie przez SQL Server przy kompilacji planu wykonania przedstawiony zawsze jako @version datetime.
Ale miało być o relacjach, prawda? No więc co się dzieje, jeśli nasz schemat nieco skomplikujemy:
Pierwsza sprawa: właściwości relacji należy tak skonfigurować, by oprócz relacji definiowany był również klucz obcy, a to dlatego, że tylko dla klucza można wymusić kaskadową aktualizację danych – dla reguły Update. Kiedy skorzystamy z kaskadowej aktualizacji? Przy wstawianiu nowego wiersza z tabeli Table1 bądź Table2 – po wstawieniu zawartość kolumny ID odpowiedniego wiersza zostanie zaktualizowana i rozpropagowana do odpowiednich pozycji w tabeli Relation.
Pozostaje przypadek najtrudniejszy do zrealizowania – mianowicie UPDATE. Zakładając, że aktualizowane są tylko dane w tabeli, zachowane pomiędzy wersjami muszą zostać informacje o strukturze relacji. Innymi słowy update Table1 lub Table2 wiąże się ze zduplikowaniem odpowiednich pozycji w tabeli Relation i wstawieniem tam odpowiednich wartości ID. Zadanie wydawałoby się nietrywialne ale wykonywane praktycznie automatycznie.
Po operacji UPDATE zdefiniowanej jak wyżej stary rekord „znika” z tabeli a pojawia się nowy, z poprawionym ID. Co ciekawe, dla wiersza jest to traktowane jako operacja UPDATE, więc odpowiedni wiersz w tabeli relacji zostaje także automatycznie zaktualizowany. I tu kryje się cały trick aktualizacji danych: dla synchronizacji tabeli relacji definiujemy tylko dwie komendy (nie definiujemy DELETE bo z definicji nie chcemy tracić informacji), ale obie składniowo są identyczne, tj. zawierają komendę INSERT:
SqlCommand cmdUpdate = new SqlCommand(
@"INSERT INTO Relation([OldId], [NewId])
VALUES (@OldId, @NewId)
SELECT [Id], [OldId], [NewId] FROM Relation
WHERE Id=@@IDENTITY", cnn);W ten sposób stare wiersze określające strukturę relacji w bazie nie zostaną nadpisane i będzie do nich dostęp kiedy przyjdzie taka potrzeba.
Nie opisuję tutaj jakiegoś konkretnego systemu, ale mam nadzieję, że powyższe rozważania mogą okazać się pomocne dla kogoś, kto będzie potrzebował stworzyć własny system wersjonowania danych w bazie.