Ostatnio napisałem kilka słów o swoim podejściu do różnego rodzaju ORM-ów. Jeśli więc w Twojej głowie pojawiła się myśl, że być może warto zainteresować się tymi nudnymi SQL-ami, to dziś opowiem Ci nieco więcej o warstwie, którą możesz zbudować między swoją aplikacją a tabelami w bazce.
W telegraficznym skrócie mogę Ci powiedzieć, że będzie o widokach, funkcjach i procedurach, a jeżeli używasz ORM-a, to także o korzyściach, jakie daje mapowanie ich na model obiektowy zamiast operowania bezpośrednio na tabelach. Wszystko dotyczy SQL Servera i Entity Frameworka, bo z nimi mam do czynienia najczęściej, ale całość wygląda bardzo podobnie w większości systemów baz relacyjnych i ORM-ów, jakie widziałem.
Widoki
Zacznijmy od początku, czyli od opisania wspomnianych konstrukcji. Na pierwszy ogień weźmy widoki. Definicja podana w dokumentacji jest bardzo prosta.
A view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data.
Możemy więc napisać mniej lub bardziej skomplikowane zapytanie i zapakować je w widok, by nie klepać kilku(nastu/dziesięciu/set) linii SQL-a za każdym razem, gdy będziemy się odwoływać do jakiegoś zbioru ładnie uporządkowanych danych. Widoki oczywiście możemy nazywać, daje nam to więc dosyć ciekawe możliwości porządkowania danych bez ich denormalizacji, bo zupełnie inaczej będziemy rozumieć klienta sklepu w kontekście rozliczeń, wysyłki produktu czy zebrania statystyk na potrzeby marketingowe.
Stwórz więc jakąkolwiek bazę i dodaj do niej kilka tabel. Pozwoliłem sobie uprościć ten model na potrzeby przykładu.
CREATE TABLE [dbo].[Customer]
(
[Id] int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[FirstName] nvarchar(100) NOT NULL,
[LastName] nvarchar(400) NOT NULL,
[Email] nvarchar(254) NOT NULL,
[Gender] tinyint NOT NULL,
[ShippingAddress] nvarchar(max) NULL,
[Phone] varchar(20) NULL
)
INSERT INTO [dbo].[Customer]
([FirstName], [LastName], [Email], [Gender], [ShippingAddress], [Phone])
VALUES
('Test',
'Testowy',
'Test@test.com',
1,
N'Testowa ulica w testowym mieście', '0048555555555'),
(N'Przykład',
N'Przykładowy',
'przyklad@przyklad.com',
1,
N'Przykładowa ulica w przykładowym mieście',
'0048666666666')
CREATE TABLE [dbo].[CreditCard]
(
[Id] int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Number] varchar(19) NOT NULL,
[ValidityDate] date NOT NULL,
[OwnerId] int NOT NULL
CONSTRAINT [Fk_CreditCard_Owner]
FOREIGN KEY REFERENCES [dbo].[Customer] ([Id])
)
INSERT INTO [dbo].[CreditCard] ([Number], [ValidityDate], [OwnerId])
SELECT '1234-5678-9012-1234',
'2018-01-31',
[Id]
FROM [dbo].[Customer]
Mamy więc nieco informacji o kliencie i jego kartach płatniczych. Gdybyśmy przesyłali dane do wysyłki paczki, potrzebowalibyśmy imienia, nazwiska, telefonu, e-maila i adresu. Można powiedzieć, że jest to praktycznie cała tabela, więc walimy SELECT * lub odpowiednik w ORM-ie (czyli podejście zwane czasem fachowo „encja na twarz i pchasz” polegające na całkowitym ignorowaniu metody Select) i lecimy.
Należy jednak pamiętać, że z biegiem czasu nasza tabela może spuchnąć i ulec fundamentalnym zmianom (które pokażę Ci za chwilę), w związku z tym będziemy wyciągać coraz więcej i więcej kolumn, a nasze potrzeby dotyczące przekazania danych do wysyłki nie ulegną zmianie. Sugeruję zatem napisać kawałek Selecta (czy to SQL-owego, czy czegoś, co Twój ORM przetłumaczy na projekcję).
SELECT [FirstName] + ' ' + [LastName] AS [FullName], [Phone], [ShippingAddress], [Email] FROM [dbo].[Customer] WHERE [Id] = 1
Aby to uprościć, uporządkować i nazwać, by ktokolwiek wiedział, co podmiot liryczny ma na myśli, możemy wszystko zapakować w ładny i zgrabny widok, który z góry określa, że mamy do czynienia z danymi do wysyłki. Przy okazji nasz widok umieśćmy w nowo utworzonej schemie shipping – to dosyć duży skrót myślowy, ale o schemie można myśleć jak o namespace’ie.
CREATE SCHEMA [shipping] GO CREATE VIEW [shipping].[CustomerShippingData] AS SELECT [Id] AS [CustomerId], [FirstName] + ' ' + [LastName] AS [FullName], [Phone], [ShippingAddress], [Email] FROM [dbo].[Customer]
A wywołanie go później to już poezja.
SELECT * FROM [shipping].[CustomerShippingData] WHERE [CustomerId] = 1
Większość ORM-ów pozwala na mapowanie widoków i oprócz samej czytelności oraz niezmienności daje nam to jeszcze jedną korzyść – bardzo dużą kontrolę nad zapytaniem, które faktycznie jest wysyłane do bazy. W przypadku bardziej skomplikowanych przypadków może to mieć bardzo duże znaczenie dla wydajności, a w takiej chmurze – bezpośrednio na ilość zasobów, jakich potrzebujemy i za jakie zapłacimy.
Funkcje i procedury
Odrobinę innym przypadkiem są funkcje i procedury. Choć widoki służą na ogół jako stosunkowo proste „nakładki” na tabele, to w nich możemy zawrzeć już całkiem sporo logiki i całkiem dużą część pracy zrzucić na silnik bazodanowy, co pozwala nam na pisanie chociażby rekurencyjnego gromadzenia danych, pracę z tymczasowymi tabelami czy zmiennymi tabelarycznymi.
O tym wszystkim w szczegółach pewnie napiszę w przyszłości, bo zarówno same funkcje, jak i procedury składowane to tematy na oddzielne posty. W tej chwili musisz wiedzieć, że tutaj zaczyna się prawdziwe programowanie w SQL-u i można tu zawrzeć naprawdę bardzo rozbudowane rzeczy, a w odróżnieniu od widoków możemy tu przekazać własne parametry.
Podstawowa różnica między obydwoma jest taka, że funkcje służą do przetworzenia jakichś danych i zwrócenia wyniku – funkcja nie może mieć żadnych skutków ubocznych dla stanu bazy. Procedury mają z kolei wykonać jakieś operacje, często związane właśnie ze zmianą stanu, po czym zwrócić kod oznaczający status (int). Przyjęło się, że „0” oznacza poprawne wykonanie procedury. Możliwe jest zwrócenie wielu innych informacji, ale o tym innym razem.
Przejdźmy zatem do tego, co to oznacza dla Ciebie. Zanim dotrzemy do sedna przyczyny, dla której warto umieszczać takie informacje po stronie bazy danych, napiszmy prostą procedurę dodającą klienta do naszej bazy.
CREATE SCHEMA [customers] GO CREATE PROCEDURE [customers].[AddCustomer] @firstName nvarchar(100), @lastName nvarchar(400), @email nvarchar(254), @gender tinyint, @shippingAddress nvarchar(max), @phone varchar(20) AS BEGIN INSERT INTO [dbo].[Customer] ([FirstName], [LastName], [Email], [Gender], [ShippingAddress], [Phone]) VALUES (@firstName, @lastName, @email, @gender, @shippingAddress, @phone) END
I po raz kolejny – wywołanie takiej procedury wygląda o wiele czytelniej niż zwykły insert.
EXEC [customers].[AddCustomer] @firstName = N'Rafał', @lastName = 'Hryniewski', @email = 'rafal@hryniewski.net', @gender = 1, @shippingAddress = 'Adres', @phone = '0048111111111'
Te przykłady są banalne, ale musisz pamiętać, że spora część operacji nie jest taka prosta i raczej nie składa się z jednego etapu. Nawet samo dodanie klienta może wymagać nieco więcej operacji niż zwykły insert. W funkcjach i procedurach możemy zrobić naprawdę sporo – ważne jest to, że pracuje wtedy baza danych, nie nasza aplikacja. A jakie z tego korzyści? Czytaj dalej.
Co z logiką biznesową?
Jeśli zdarzyło Ci się kiedyś nad tym zastanawiać, to w międzyczasie mógł Cię ogarnąć niepokój – przecież jesteśmy o krok od wrzucenia logiki biznesowej do bazy. Toć to herezja!
I tutaj się z Tobą całkowicie zgodzę. Wrzucenie zbyt wiele logiki do bazy będzie bardzo trudne w utrzymaniu. SQL-a można debugować, można go też testować, ale raczej nie powinniśmy tam wrzucać wszystkich reguł walidacji czy zmieniać stanu połowy aplikacji, bo wrzuciliśmy platynowego klienta albo inne dziwne mechanizmy.
Moim zdaniem na bazę można zrzucić całkiem sporo, ale jednak lepiej, żeby dotyczyło to pobierania danych niż ich zapisywania – zarządzanie stanem aplikacji mimo wszystko pozostawmy poza bazą. Proste operacje w stylu dodania klienta są jak najbardziej w porządku, ale jeśli wiążą się z tym operacje np. dodania punktów pracownikowi, który wspomnianego klienta pozyskał, to powinny one zostać obsłużone już w samej aplikacji.
Korzyści
Wróćmy jednak do naszej abstrakcji. Co nam daje załadowanie prostych operacji w funkcje i procedury, a wyników niektórych zapytań w widoki?
Jak już wspomniałem, często jest to kwestia kontroli nad tym, co się w bazie dzieje, co w wielu przypadkach może przyczynić się do znacznych wzrostów wydajności. Ale nie jest to jedyna zaleta takiego podejścia.
Produkty, nad którymi pracujemy, mogą być dosyć duże. Jeśli cały system korzysta z jednej bazy, to możemy dość szybko zorientować się, że np. z tabeli User korzysta w ten lub inny sposób pół aplikacji. Owszem, są architektury, które tego problemu nie mają. Owszem, doprowadzenie do tej sytuacji nie jest pożądane. Ale nie zawsze mamy do czynienia z greenfieldem i mnóstwem czasu na najlepsze praktyki.
Modyfikacje w locie
I tak docieramy do momentu, w którym ktoś informuje Cię, że klient prosi, byśmy zmienili kolejność wyświetlania imienia i nazwiska, gdy zwracamy dane do wysyłki.
Niby pierdoła, ale ktoś się zorientował, że w kraju naszego odbiorcy może to być uznawane za wielki nietakt. Potrzebne będzie wdrożenie. Oczywiście masz pecha i w Twoim projekcie CI/CD nie istnieje. Na dodatek w kraju klienta zbliżają się święta i trzeba to ogarnąć na wczoraj. Cóż, życie. To jak wyglądały Twoje plany na piątkowy wieczór?
Nic się nie martw. Przecież wszystko, co potrzebne do zmiany, masz zrobione po stronie bazy danych. Wystarczy, że zmodyfikujesz uprzednio utworzony widok.
ALTER VIEW [shipping].[CustomerShippingData] AS SELECT [Id] AS [CustomerId], [LastName] + ' ' + [FirstName] AS [FullName], [Phone], [ShippingAddress], [Email] FROM [dbo].[Customer]
Aplikacja nie przestanie działać nawet na chwilę, a cała operacja zajmie mniej niż sekundę. Problem rozwiązany, nie zmieniliśmy nic w kodzie samej aplikacji i ominął nas smutny deploy w piątkowy wieczór.
Schemabinding
Wszystko wygląda fajnie. Ale jak się w tym połapać i dopilnować, by aktualizować warstwy nad tabelami, gdy te ulegają zmianom?
Słowo klucz – schemabinding. Zmodyfikujmy nasz widok jeszcze raz, tym razem dodając opcję WITH SCHEMABINDING.
ALTER VIEW [shipping].[CustomerShippingData] WITH SCHEMABINDING AS SELECT [Id] AS [CustomerId], [LastName] + ' ' + [FirstName] AS [FullName], [Phone], [ShippingAddress], [Email] FROM [dbo].[Customer]
Nasz widok jest teraz powiązany z tabelą, z której korzysta, i baza nie pozwoli nam na modyfikację obiektów, od których jesteśmy w jakiś sposób zależni.
I tak możemy dodać kolumnę, której nasz widok nie używa.
ALTER TABLE [dbo].[Customer] ADD [Something] nvarchar(100)
Możemy też ją usunąć. Podobnie jak kolumnę Gender, z której nie korzystamy.
ALTER TABLE [dbo].[Customer] DROP COLUMN [Something] ALTER TABLE [dbo].[Customer] DROP COLUMN [Gender]
Ale jeśli spróbujemy chociaż tknąć kolumnę zawierającą imię, zostaniemy potraktowani błędem. Spróbuj tylko zrobić coś takiego.
ALTER TABLE [dbo].[Customer] ALTER COLUMN [FirstName] nvarchar(200)
A w odpowiedzi MS SQL zwróci Ci komunikat:
Msg 5074, Level 16, State 1, Line 1
The object ‘CustomerShippingData’ is dependent on column ‘FirstName’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN FirstName failed because one or more objects access this column.
Aby w jakikolwiek sposób zmodyfikować kolumnę, od której zależne są Twój widok i Twoja procedura, musisz je usunąć i po zamierzonych modyfikacjach utworzyć jeszcze raz, nawet jeśli ich kod nie zostanie zmodyfikowany.
Ciąg dalszy nastąpi?
I na tym dziś skończmy. Istnieje możliwość, że nigdy nie zajdzie konieczność, by w Twoim projekcie używać tego typu technik. Jednak jeśli pewnego dnia okaże się, że będzie Ci to potrzebne, to mam nadzieję, że będziesz o tych narzędziach pamiętać i pomyślisz o mnie ciepło.
A jeżeli takie modelowanie danych Ci się podoba i zastanawiasz się, jak wygląda pociągnięte naprawdę bardzo daleko, to rzuć okiem na pojęcie Anchor Modeling w Wikipedii. Już od jakiegoś czasu noszę się z zamiarem stworzenia na ten temat prezentacji i napisania kilku postów – wyraź zainteresowanie tym zagadnieniem w komentarzu, a może nieco się sprężę. ;)
Do przeczytania!
