Dokumentacja tabel i kolumn w SQL Server
Temat kontrowersyjny. Dokumentować, czy nie dokumentować? Doświadczenie pokazuje, że czasami jest z tym duży problem. Trwa odwieczna walka pomiędzy limitem czasowym na napisanie nowych modułów, a korzystaniem z dobrych praktyk. Postawmy się w roli zarządzającego projektem i spytajmy co jest lepsze - dokumentowanie kodu, aby gdzieś, z punktu widzenia czasu życia projektu, w ekstremalnej przyszłości, ktoś miał łatwiej, czy zrobienie wcześniej nowej funkcji wyliczającej opłaty za rezerwację biletu? Tak się jakoś porobiło, że komentarz stał się tworem prawie wyłącznie podręcznikowym, legandarnym, wręcz baśniowym, nieuchwytną i niewidoczną cząstką kodu źródłowego. Niektórzy wręcz twierdzą, że komentarze wyginęły śmiercią naturalną niczym dinozaury, upadły jak cywilizacja Majów. Po uważnym przebadaniu starych źródeł pokażę, jak to historycznie wyglądało.
Komentarze ustandaryzowane
Gdybyśmy się przyjrzeli SQL Server Management Studio wyjątkowo dokładnie, zauważylibyśmy, że do każdej kolumny można w trybie projektowym (ang. design) dodać właściwość o nazwie Description, czyli po naszemu opis. Pojawia się pierwsze pytanie: jak to jest zapisywane? W czymś, co nazywamy właściwościami rozszerzonymi (ang. extended properties). Dla tych, którzy nie lubią czytać przejdę od razu do skryptu. Przyjrzyjmy się zatem skryptowi zaprezentowanemu poniżej:
(
Id int NOT NULL IDENTITY(1,1),
Nazwa nvarchar(32)
)
EXEC sp_addextendedproperty 'MS_Description',
N'Nazwa działu', 'SCHEMA', 'dbo', 'TABLE', 'Dzial', 'COLUMN', 'Nazwa'
Zanim przejdę do bardziej zaawansowanych przykładów wyjaśnijmy sobie sposób działania procedury sp_addextendedproperty. Jest ona na pierwszy rzut oka dość złożona. Swoją drogą, na drugi rzut też tak jest. Obiekty w bazie danych mają pewne relacje. Dobrze wiemy, że kolumny należą do tabeli. Tabela jest więc, korzystając ze słownictwa algorytmicznego, rodzicem. Kolumna jest dzieckiem. Mówiąc jeszcze inaczej, tabela jest na wyższym poziomie. Zastanówmy się, czy jest coś na jeszcze wyższym poziomie niż tabela...
A co ze schematem, czy jak kto woli przestrzenią? Może on mieć w sobie kilka tabel, które w strukturze drzewiastej nazywane byłyby dziećmi schematu. Schemat ma też wyższy poziom niż tabela.
Otrzymaliśmy zatem pewną hierarchię: schemat (poziom 0), tabela (poziom 1) oraz kolumna (poziom 2). Przyjrzyjmy się jeszcze raz samemu wywołaniu procedury sp_addextendedproperty:
'MS_Description', --1
N'Nazwa działu', --2
'SCHEMA', --3
'dbo', --4
'TABLE', --5
'Dzial', --6
'COLUMN', --7
'Nazwa'--8
Parametr oznaczony (1) to nazwa właściwości rozszerzonej, i w zasadzie nie ma znaczenia jak ją nazwiemy. Jest jednak pewien drobiazg. Jeżeli nazwiemy tę właściwość MS_Description, wtedy SQL Server Management Studio będzie ją traktował jako opis. Warto o tym wiedzieć i o ile to możliwe trzymać się konwencji. Podejścia standardowe mają to do siebie, że są powszechniejsze, a także łatwiej znaleźć w internecie rozwiązania problemów podobnych do naszych, być może nawet gotowe już aplikacje pomocnicze. Drugi parametr (2) to wartość tej właściwości. Zwykle jest to tekst, ale nie ma przeszkód, aby wstawić tam inną wartość. Sam parametr jest typu sql_variant.
Kolejne pary oznaczają opisywane wcześniej poziomy. Są to odpowiednio: rodzaj elementu na poziomie X i nazwa elementu na poziomie X.
Obiektów w bazie danych jest dużo, a opisanie ich wszystkich wykracza poza zakres tego wpisu. Dla potrzeb dokumentacji kolumn i tabel wystarczy wiedzieć, że obiektem zerowego poziomu jest schemat (przestrzeń), czyli najczęściej dbo, pierwszy poziom to tabela, a drugi to kolumna. Jażeli zamierzamy dodać opis samej tabeli, nie trzeba podawać parametrów drugiego poziomu.
UPDATE, czyli modyfikacja właściwości rozszerzonych
Modyfikacja właściwości rozszerzonych jest tak prosta, że nie wymaga żadnych szczegółowych wyjaśnień. Jeżeli opanowaliśmy dodawanie takich właściwości, to modyfikacja będzie dla nas naturalna. Zmienia się tylko nazwa procedury z sp_addextendedproperty na sp_updateextendedproperty. Przykładowy skrypt modyfikujący istniejący już wpis będzie wyglądał następująco:
'MS_Description', 'Nowy opis tabeli Table',
'SCHEMA', 'dbo',
'TABLE', 'Table'
Smutne jest to, że samemu trzeba sprawdzać, czy dany wpis już istnieje. Jeżeli spróbujemy dodać taki sam po raz drugi, otrzymamy następujący komunikat:
Property cannot be added. Property 'MS_Description' already exists for 'dbo.Table'.
Podobnie, jeżeli spróbujemy zmodyfokować właściwość rozszerzoną, także otrzymamy błąd. Będzie on podobny to zaprezentowanego poniżej:
Object is invalid. Extended properties are not permitted on 'dbo.Table.Column', or the object does not exist.
Aby sprawdzić, czy wpis związany z daną właściwością rozszerzoną istnieje trzeba wiedzieć, gdzie go szukać.
Pobieranie informacji o właściwościach rozszerzonych
Dodaliśmy już kilka właściwości rozszerzonych, niektóre może zmodyfikowaliśmy. Nadszedł czas, aby z nich skorzystać. Jak już wspomniałem, jeżeli właściwość rozszerzona ma nazwę MS_Description, SQL Server Management Studio będzie automatycznie wyświetlał wartość w polu Description, czyli po naszemu w polu opisu. Być może komuś to wystarczy, ale prawdziwe możliwości pojawiają się dopiero wtedy, gdy sami możemy zrobić coś z tymi danymi. Naturalnie nie będziemy tych wartości pobierać z SQL Server Management Studio, lecz z widoków systemowych. Niektórzy pewnie domyślają sie jak ten widok się nazywa. Ten widok to sys.extended_properties (ang. extended - rozszerzony, ang. properties - właściwości). Widok zawiera kilka kolumn. Najważniejsze z nich przedstawiono w tabeli poniżej:
| Kolumna | Opis |
| Class | Dla właściwości definiujących tabele i kolumny będzie tutaj zawsze wartość 1. |
| major_id | Identyfikator obiektu pierwszego rzędu. W rozważanym przypadku dokumentowania kolumn i tabel będzie to object_id, czyli identyfikator tabeli. |
| minor_id | Identyfikator drugiego rzędu. W naszym przykładzie będzie to identyfikator kolumny. Jeżeli opis dotyczy tabeli, w tym miejscu będzie 0. |
| name | Nazwa właściwości rozszerzonej. |
| value | Wartość właściwości rozszerzonej. |
Po dodaniu kilku właściwości rozszerzonych można spróbować podejrzeć wartości w tym widoku za pomocą następującej instrukcji:
Ułatwianie sobie zadania
Temat jest złożony. W większości przypadków będziemy dodawać komentarze tylko do tabel i kolumn. Co więcej, jeżeli wpis już istnieje, będziemy chcieli go zamienić. Można sobie przygotować odpowiednie procedury, które znacząco ułatwią całe zadanie. Przedstawione skrypty można traktować jako pewnego rodzaju wzorce i rozszerzać je o kolejne elementy. Przyjrzyjmy się, jak takie procedury mogłyby wyglądać:
--komentarzy do tabel
CREATE PROCEDURE dbo.AddTableComment
@schema nvarchar(128),
@table nvarchar(128),
@comment nvarchar(4000)
AS
--Jeżeli wpis istnieje - podmień
--Jeżeli nie istnieje - wstaw
IF EXISTS(SELECT * FROM
sys.extended_properties E
JOIN sys.tables T ON E.major_id=T.[object_id]
JOIN sys.schemas S ON S.[schema_id]=T.[schema_id]
WHERE T.name=@table AND S.name=@schema
AND E.name='MS_Description'
AND E.minor_id=0
AND E.class=1)
EXEC sp_updateextendedproperty
'MS_Description', @comment,
'SCHEMA', @schema,
'TABLE', @table
ELSE
EXEC sp_addextendedproperty
'MS_Description', @comment,
'SCHEMA', @schema,
'TABLE', @table
GO
--Przykładowe wywołanie procedury
EXEC dbo.AddTableComment 'dbo', 'Table', 'Nowy opis tabeli Table'
Poniżej podobny skrypt dla kolumn:
--komentarzy do kolumn
CREATE PROCEDURE dbo.AddColumnComment
@schema nvarchar(128),
@table nvarchar(128),
@column nvarchar(128),
@comment nvarchar(4000)
AS
IF EXISTS(SELECT * FROM
sys.extended_properties E
JOIN sys.tables T ON E.major_id=T.[object_id]
JOIN sys.schemas S ON S.[schema_id]=T.[schema_id]
JOIN sys.columns C ON C.[object_id]=T.[object_id]
WHERE T.name=@table AND S.name=@schema
AND C.name=@column
AND E.name='MS_Description'
AND E.minor_id=C.column_id
AND E.class=1)
EXEC sp_updateextendedproperty
'MS_Description', @comment,
'SCHEMA', @schema,
'TABLE', @table,
'COLUMN', @column
ELSE
EXEC sp_addextendedproperty
'MS_Description', @comment,
'SCHEMA', @schema,
'TABLE', @table,
'COLUMN', @column
GO
--Przykładowe wywołanie procedury
EXEC dbo.AddColumnComment 'dbo', 'Table', 'Name', 'Opis kolumny Name w tabeli Table'
Dobrym pomysłem wydaje się dalsze rozszerzenie zaprezentowanych procedur o przypadek, w którym przekazujemy pustą wartość. Obsługa takiego przypadku mogłaby polegać na usunięciu właściwości rozszerzonej lub zignorowaniu instrukcji, jeżeli wpis nie istnieje.
Podsumowanie
Zaprezentowane techniki pozwalają na dużo więcej. W jednym z kolejnych wpisów postaram się opisać, jak utworzyć prosty program ułatwiający dokumentowanie bazy, nawet bardzo mało wtajemniczonym użytkownikom. Pokażę też, jak wygenerować dokumentację takiej bazy w pliku HTML, dzięki czemu każdy będzie mógł sobie taką dokumentację przejrzeć. Możliwości dokumentowania tutaj się dopiero otwierają.
Kategoria:SQL Server
Brak komentarzy - bądź pierwszy