Spis treści:

Kategoria:SQL Server


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:

CREATE TABLE dbo.Dzial
(
  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:

EXEC 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:

EXEC sp_updateextendedproperty
    '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:

Msg 15233, Level 16, State 1, Procedure sp_addextendedproperty, Line 37
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:

Msg 15135, Level 16, State 8, Procedure sp_updateextendedproperty, Line 36
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:

KolumnaOpis
ClassDla właściwości definiujących tabele i kolumny będzie tutaj zawsze wartość 1.
major_idIdentyfikator obiektu pierwszego rzędu. W rozważanym przypadku dokumentowania kolumn i tabel będzie to object_id, czyli identyfikator tabeli.
minor_idIdentyfikator drugiego rzędu. W naszym przykładzie będzie to identyfikator kolumny. Jeżeli opis dotyczy tabeli, w tym miejscu będzie 0.
nameNazwa właściwości rozszerzonej.
valueWartość 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:

SELECTFROM sys.extended_properties

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ć:

--Utworzenie procedury ułatwiającej dodawanie
--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(SELECTFROM
sys.extended_properties E
JOIN sys.tablesON E.major_id=T.[object_id]
JOIN sys.schemasON 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:

--Utworzenie procedury ułatwiającej dodawanie
--komentarzy do kolumn
CREATE PROCEDURE dbo.AddColumnComment
@schema nvarchar(128),
@table nvarchar(128),
@column nvarchar(128),
@comment nvarchar(4000)
AS
IF EXISTS(SELECTFROM
sys.extended_properties E
JOIN sys.tablesON E.major_id=T.[object_id]
JOIN sys.schemasON S.[schema_id]=T.[schema_id]
JOIN sys.columnsON 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

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !
a z innej strony - co gdybym ciąg znaków chciał mieć rozbity nie na wiersze a na kolumny? Czyli ciąg ABCD: 1. kolumna: A, 2. kolumna: B, 3. kolumna: C, 4 kolumna: D?
Ciekawy artykuł.
Czy można za pomocą EF wysłać swoje zapytanie?
Czy lepiej do tego użyć ADO.net i DataTable?