Snippety (wstawki kodu) w SQL Server

Zwiększ swoją produktywność

Wstawki kodu, zwane również z angielskiego snippetami, to nie jest wymysł SQL Server Management Studio. To tak jak z biblijnym stworzeniem świata. Widział Pan, że to, co zrobił, było dobre. Snippety są obecne w Visual Studio od dłuższego czasu, a wiedza o nich jest, w moim odczuciu, powszechna. Wstawki kodu w SQL Server już tak powszechne wśród społeczności programistów i administratorów baz danych nie są. Mam na to kilka teorii. Po pierwsze, integracja snippetów z Visual Studio jest lepsza - wstawki wbudowane są w mechanizm podpowiadania składni. Po drugie, kodu języków proceduralnych i obiektowych pisze się znacznie więcej niż instrukcji SQL. Zysk ze snippetów w językach C# i VB jest zatem znacznie większy. A im większy potencjalny zysk, tym większa potrzeba i nakłady. Taka jest ogólnie znana zasada ekonomii.

Dobrą praktyką jest dbanie o swój czas. Jeżeli coś da się zautomatyzować, należy to robić. Jeżeli coś wykonuje się po raz drugi, warto się zastanowić nad sposobem ułatwienia sobie tego zadania. Jeżeli coś wykonuje się po raz trzeci, warto przenieść uzyskane w poprzednich próbach doświadczenia i jeszcze bardziej udoskonalić cały proces. Jeżeli coś wykonujemy jeszcze częściej, wtedy już trzeba podjąć próbę automatyzacji. Nawet dla siebie. Nie ma nic gorszego niż monotonne powtarzanie tych samych czynności. Początkowy nakład może i jest kosztowny, bo trzeba coś przeanalizować, zaprojektować i wykonać, ale na dłuższym dystansie tylko zyskujemy.

Wstawianie snippetów w SQL Server Managment Studio

Snippety mogą być wstawiane na kilka sposobów. Najwygodniej posługiwać się skrótem klawiszowym, ale warto wspomnieć o pozostałych - gdybyśmy zapomnieli skrótu. Wszystkie sposoby zestawione są poniżej:

• z menu Edit wybieramy Intellisense, a następnie Insert Snippet,
• klikamy prawym przyciskiem myszy e oknie edycji i z menu podręcznego wybieramy Insert Snippet,
• wciskamy kombinację klawiszy Ctrl+K, Ctrl+X.

Warto w tym momencie wspomnieć o pewnym podziale wstawek kodu na dwie kategorie: snippety wstawiane w miejsce kursora lub zaznaczenia oraz snippety otaczające zaznaczony fragment. Wstawki otaczające to na przykład sekcja BEGIN ... END otaczająca już napisany fragment kodu lub sekcja IF (warunek) BEGIN ... END obejmująca kilka instrukcji. Z informacyjnego obowiązku należy wspomnieć także o tych drugich snippetach, taczających, i sposobach ich wstawiania:

• z menu Edit wybieramy Intellisense, a następnie Surround With,
• klikamy prawym przyciskiem myszy e oknie edycji i z menu podręcznego wybieramy Surround With,
• wciskamy kombinację klawiszy Ctrl+K, Ctrl+S.

Warto obejrzeć sobie zestaw snippetów, które dostarczane są razem z SQL Server Management Studio. Być może są już one napisane i nie trzeba ich pisać samemu. Dla snippetów otaczających są to między innymi Begin, If i While. Snippetów zwykłych jest znacznie więcej. Z tych najczęściej wykorzystywanych warto wspomnieć o schematach do tworzenia funkcji (skalarnych, rozwijanych w miejscu wywołania, z wieloma instrukcjami), procedur, indeksów, widoków, tabel i uprawnień.

Prawdziwa moc snippetów ukazuje się dopiero wtedy, gdy dodamy możliwość pisania własnych.

Tworzenie własnych snippetów w SQL Server

Wbudowanych wstawek kodu jest wiele, ale w moim odczuciu specyfika pracy każdego programisty lub administratora jest nieco inna. Każdy wykonuje nieco inne zadania i każdy ma swój zestaw często powtarzających się czynności. Przypuśćmy, że musimy sprawdzić, ile rekordów jest w tabeli A i B. Zapisalibyśmy to w następujący sposób:

SELECT COUNT(*) FROM Tabela

Wystarczyłoby tylko zamienić słowo Tabela na odpowiednią nazwę. Całą reszta jest stała.

Budowa wstawki kodu

Wstawki kodu definiuje się w plikach XML. Nie mam zamiaru opisywać całej struktury plików, bo nie będzie nam tutaj potrzebna. Przyjmę technikę analizowania przykładów. Przypadek instrukcji COUNT(*) doprowadza nas do następującego, prostego szablonu:

<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>COUNT *</Title>
      <Description>Count records in a table</Description>
      <Author>Paweł Dyl</Author>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Code Language="SQL">
        SELECT COUNT(*) Total FROM Tabela
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

Plik XML ze wstawką osadzony jest w głównym węźle CodeSnippets, ten z kolei zawiera element CodeSnippet z dwoma podstawowymi sekcjami: Header oraz Snippet. Ta pierwsza sekcja to nagłówek z podstawowymi informacjami o wstawce:

• Title - tytuł wstawki,
• Description - opis wstawki,
• Author - autor wstawki,
• SnippetTypes - sekcja z informacją o typie wstawki, może przyjmować wartości SurroundsWith (snippet otaczający) oraz Expansion (snippet zwykły).

Nagłówek najwygodniej skopiować i pozmieniać poszczególne wartości. To co najważniejsze mieści się w sekcji Snippet i elemencie Code. Snippety mogą być pisane dla wielu języków programowania. Te, które są pisane dla SQL Server będą miały w atrybucie Language wartość SQL. Tam wstawiamy to, co pewnie każdy ma na myśli mówiąc o snippecie.

Instalacja snippetu

Po utworzeniu pliku XML należy zmienić jego rozszerzenie na *.snippet. Takie rozszerzenie mają wszystkie wbudowane snippety. Teraz należy ten snippet zainstalować. Wydaje się, że instalowanie to zbyt odważne słowo jak na cały proces pokazany poniżej. Takie jednak nazewnictwo zostało przyjęte i postaram się je utrzymać. Aby korzystać ze snippetu w SQL Server Management Studio należy:

• z menu Tools wybrać Code Snippets Manager,
• w oknie dialogowym nacisnąć przycisk Add...,
• wybrać katalog, w którym umieszczony jest plik *.snippet.

Powyższy algorytm pozwala dodać nowy katalog z własnymi wstawkami i takie podejście jest przeze mnie preferowane, bo nie modyfikujemy ustawień domyślnych. Zawsze łatwiej odnaleźć się w obcym środowisku. Gdybyśmy zechcieli dodać wstawki do już zdefiniowanych katalogów możemy nieco zmodyfikować algorytm:

• z menu Tools wybrać Code Snippets Manager,
• w oknie dialogowym nacisnąć przycisk Import...,
• wybrać plik *.snippet.

To, jak pogrupujemy wstawki w katalogach, zależy tylko od nas.

Użycie własnych snippetów

Wstawki kodu zainstalowane w poprzednim podpunkcie mogą już być wykorzystywane. Zamiast pisać SELECT COUNT(*) FROM Tabela wystarczy teraz nacisnąć kombinację klawiszy Ctrl+K, Ctrl+X i wybrać utworzoną wstawkę z listy:

Wstawki kodu ze zdefiniowanymi sekcjami

Snippety to nie tylko taki rozszerzony schowek na fragmenty kodu. To coś więcej. Wewnątrz snippeta można zdefiniować pola, które mogą być modyfikowane tuż po wstawieniu. To sposób na parametryzację takiej wstawki. Popatrzmy na kolejny przykład:

<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>COUNT *</Title>
      <Description>Count records in a table</Description>
      <Author>Paweł Dyl</Author>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Declarations>
        <Literal>
          <ID>Table</ID>
          <ToolTip>Table to select from</ToolTip>
          <Default>sys.tables</Default>
        </Literal>
      </Declarations>
      <Code Language="SQL">
        SELECT COUNT(*) Total FROM $Table$
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

W pokazanej definicji pojawiła się sekcja Declarations, a w niej, w tym przypadku, jeden element Literal. To właśnie parametr, który może wystąpić we wstawce. Po wstawieniu snippeta SQL Server Management Studiu zaznaczy fragment i pozwoli go zmienić. Co więcej, jeżeli fragment ten występuje wielokrotnie, zostanie zamieniony w każdym miejscu. W sekcji Literal znajdują się trzy elementy i są to:

• ID - identyfikator zmiennej we wstawce,
• ToolTip - tekst podpowiedzi (element opcjonalny),
• Default - wartość domyślna.

Sama definicja zmiennej nie jest tak ważna jak ważne jest powiązanie identyfikatora tej zmiennej z kodem wstawki - zmienne w kodzie wstawki umieszcza się pomiędzy parą znaków $. Gdy zainstalujemy snippeta zdefiniowaną powyżej, nazwa tabeli będzie mogła być modyfikowana tuż po wstawieniu kodu.

Bardziej zaawansowany przykład - wyświetlanie drzewa

Wyszukiwanie i wyświetlanie danych z pojedynczej tabeli jest dość łatwe - o ile tabela nie reprezentuje jakiegoś drzewa lub grafu. Właściwe wyświetlenie takich niekonwencjonalnych struktur wymaga użycia wyrażeń CTE. Trzeba też napisać nieco więcej instrukcji niż w przypadku zwykłego zapytania. Cały proces można sobie nieco uprościć tworząc szablon - wstawkę. Popatrzmy na poniższy przykład snippeta:

<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>Tree</Title>
      <Description>Enumerates tree elements</Description>
      <Author>Paweł Dyl</Author>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Declarations>
        <Literal>
          <ID>Table</ID>
          <ToolTip>Table to select from</ToolTip>
          <Default>TreeTable</Default>
        </Literal>
        <Literal>
          <ID>ID</ID>
          <ToolTip>Identifier</ToolTip>
          <Default>ID</Default>
        </Literal>
        <Literal>
          <ID>ParentID</ID>
          <ToolTip>Foreign Key</ToolTip>
          <Default>ParentID</Default>
        </Literal>
        <Literal>
          <ID>OrderColumn</ID>
          <ToolTip>Order column</ToolTip>
          <Default>ID</Default>
        </Literal>
      </Declarations>
      <Code Language="SQL">
        <![CDATA[WITH Tree AS
(
  SELECT $ID$, $ParentID$, CAST(ROW_NUMBER() OVER (Order BY $OrderColumn$) AS varbinary(MAX)) ItemPath
  FROM $Table$
  WHERE $ParentID$ IS NULL
UNION ALL
  SELECT T.$ID$, T.$ParentID$, Tree.ItemPath + CAST(ROW_NUMBER() OVER (Order BY T.$OrderColumn$) AS varbinary(MAX)) ItemPath
  FROM Tree
  JOIN $Table$ T ON Tree.$ID$=T.$ParentID$
)
SELECT *
FROM Tree
ORDER BY ItemPath]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

Oprócz samozłączeń w tabeli pozwoliłem sobie dodać kolumnę definiującą ścieżkę rekordu. Kolumna ta pozwala w łatwy sposób posortować rekordy w postaci drzewa - rekordy podrzędne będą wyświetlane bezpośrednio pod elementami nadrzędnymi. W łatwy sposób można również określić poziom zagłębienia elementu i ścieżkę rodzica. Ta druga wartość powstaje przez obcięcie 8 ostatnich bajtów ścieżki. Ta pierwsza może być wyliczona według następującej formuły:

DATALENGTH(ItemPath)/8

Bardzo wygodne jest też wyświetlanie drzewa w postaci nazw z odpowiednimi wcięciami. Możemy zatem zapisać:

REPLICATE(' ', (DATALENGTH(ItemPath)-8)/2) + NazwaWłaściwa

Rozbudowę wstawki pozostawiam czytelnikom.

Wstawka otaczająca kod

Wspomniałem nieco wcześniej o innym rodzaju wstawki - wstawce otaczającej. Podejrzewam, że będzie to znacznie rzadziej wykorzystywana forma wstawki. Popatrzmy na listing pokazany poniżej:

<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>Number records</Title>
      <Description>Numbers records in a table</Description>
      <Author>Paweł Dyl</Author>
      <SnippetTypes>
        <SnippetType>SurroundsWith</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Code Language="SQL">
        <![CDATA[
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) RowNumber, T.* FROM
(
  $selected$ $end$
) T
]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

Wyjaśnienia wymagają dwa nowe elementy:

• $selected$ - Słowo kluczowe oznaczające zaznaczony fragment. Teoretycznie jest to fragment, który chcemy otoczyć. Nie ma jednak takiego wymogu. Fragment może być wstawiony na początku, może też występować kilka razy.
• $end$ - Miejsce, w którym zostanie umieszczony kursor po wstawieniu snippeta.

Zadaniem wstawki jest dodanie kolumny z numerem porządkowym rekordu przed pozostałymi kolumnami. W celu użycia wstawki należy zaznaczyć jakieś zapytanie, skorzystać ze skrótu klawiszowego Ctrl+K, Ctrl+S, wybrać z kolekcji właściwy snippet i cieszyć się efektem. Aby skrypt po otoczeniu mógł się wykonać, wszystkie kolumny otaczanego zapytania muszą mieć nazwy. Podobnie jak w przypadku snippetów nieotaczających, także w tym typie można dodawać parametry. Moglibyśmy na przykład zmodyfikować kolejność numerowania rekordów pozwalając na własną wartość po słowach kluczowych ORDER BY. Wszystko zależy od naszych chęci.

Podsumowanie

Podejrzewam, że każdy znajdzie miejsce na swoją własną wstawkę. Każdy ma jakiś zakres obowiązków i zestaw instrukcji, które wykonuje wyjątkowo często. Jeżeli snippety pozwolą przyspieszyć pracę, warto je stosować. Nie będę zatem się rozpisywał tylko zachęcę do dzielenia się pomysłami na wstawki lub gotowymi wstawkami w komentarzach.

Kategoria:SQL Server