Jasne i ciemne strony baz danych

… w szczególności MS SQL Server ;-)

Posts Tagged ‘konwencje nazewnicze’

SQL Server – Narzucamy konwencję nazewniczą obiektów

Posted by C3PO w dniu 27 lipca 2010


Dobrą praktyką stosowaną w wielu firmach jest narzucenie programistom i administratorom pracującym z bazami danych konwencji nazewniczych dotyczących nazw obiektów w bazach danych. Jedną z częściej stosowanych konwencji jest wyróżnianie obiektów według ich typów przez stosowanie w nazwach odpowiednich prefiksów. I tak, widoki często nazywamy z prefiksem „v”, a dla funkcji niektórzy stosują prefiks „ufn”. Jaka by nie była konwencja, trzeba pomyśleć, jak narzucić ją zapominalskim.

W SQL Server 2008 do tego zadania świetnie nadają się wyzwalacze DDL (DDL triggers). Przykładowy wyzwalacz narzucający pewną, dość ograniczoną, konwencję nazewniczą, może wyglądać tak:

IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'tr_DDL_NamingTemplateProcedural')
  DROP TRIGGER tr_DDL_NamingTemplateProcedural ON DATABASE;
GO
CREATE TRIGGER tr_DDL_NamingTemplateProcedural
ON DATABASE
FOR
  CREATE_PROCEDURE,
  CREATE_VIEW,
  CREATE_FUNCTION,
  CREATE_TRIGGER
AS
BEGIN
  DECLARE
    -- szablony nazw
    @ProcedureTemplate sysname = 'usp[_]%',
    @FunctionTemplate sysname = 'ufn[_]%',
    @TriggerTemplate sysname = 'tr[_]%',
    @ViewTemplate sysname = 'v[_]%',
    --
    @NamingTemplate sysname,
    @EventData xml,
    @ObjectType sysname,
    @ObjectName sysname,
    @Message nvarchar(1000);
  SET
    @EventData = EVENTDATA();
  SELECT
    @ObjectName =
      @EventData.value('(/EVENT_INSTANCE/ObjectName/text())[1]', 'sysname'),
    @ObjectType =
      @EventData.value('(/EVENT_INSTANCE/ObjectType/text())[1]', 'sysname');
  SET
    @NamingTemplate =
      CASE @ObjectType
        WHEN 'PROCEDURE' THEN @ProcedureTemplate
        WHEN 'FUNCTION' THEN @FunctionTemplate
        WHEN 'TRIGGER' THEN @TriggerTemplate
        WHEN 'VIEW' THEN @ViewTemplate
        ELSE '%'
      END;
  SET
    @Message = 'Nazwa obiektu niezgodna z narzuconą konwencją nazewniczą w bazie (' +
                @ObjectType + ' LIKE ''' + REPLACE(@NamingTemplate, '%', '%%') + ''')';

  IF @ObjectName NOT LIKE @NamingTemplate BEGIN
      RAISERROR(@Message, 16, 1);
      ROLLBACK;
  END;
END;
GO

Wyzwalacz ten narzuca w bieżącej bazie danych następującą konwencję: nazwy procedur muszą zaczynać się od prefiksu usp_, nazwy funkcji – od prefiksu ufn_, nazwy wyzwalaczy – od prefiksu tr_, zaś nazwy widoków – od prefiksu v_. Wyzwalacz jest uruchamiany w przypadku próby stworzenia któregokolwiek z wymienionych obiektów w bazie danych, wydobywa z pomocą funkcji EVENTDATA i metody value (metoda typu danych XML) niezbędne informacje  – nazwę obiektu oraz jego typ, sprawdza, czy nazwa jest zgodna z konwencją, a jeśli napotka niezgodność, wycofuje transakcję tworząca obiekt zwracając przy tym stosowny komunikat.

I tak, próba utworzenia przykładowej procedury:

CREATE PROC sp_SampleProc AS SELECT 1;

zakończy się zwróceniem następującego komunikatu błędu:

Msg 50000, Level 16, State 1, Procedure tr_DDL_NamingTemplateProcedural, Line 39
Nazwa obiektu niezgodna z narzuconą konwencją nazewniczą w bazie
(PROCEDURE LIKE 'usp[_]%')
Msg 3609, Level 16, State 2, Procedure sp_SampleProc, Line 1
The transaction ended in the trigger. The batch has been aborted.

Ktoś mógłby powiedzieć, że taki efekt można osiągnąć stosując mechanizm Policy-Based Management. Prawda. Ale wyzwalacz DDL ma kilka zalet w porównaniu ze wspomnianym mechanizmem:

  • wyzwalacza DDL można użyć już w SQL Server 2005 (wymaga odrobiny zmiany kodu przy deklaracjach zmiennych),
  • w przypadku wyzwalacza DDL mamy pełną kontrolę nad tym, co i jak jest sprawdzane oraz jakie zwracamy komunikaty (sami piszemy kod), jesteśmy też w stanie zaimplementować bardziej skomplikowaną logikę,
  • w przypadku Policy-Based Management trzeba dobrze poznać szablony (facets), bo niektóre z nich nie działają w trybie On Change – Prevent (spróbujcie założyć polisę na nazwę widoku z użyciem szablonu View, to zrozumiecie, o co chodzi).

I jeszcze mała uwaga. W SQL Server istnieje niestety możliwość zmiany nazwy obiektu przy użyciu procedury sp_rename. Dokonywanie takich zmian jest bardzo złym pomysłem. Jednak na SQL Server 2008 / 2008 R2 administrator może przed takimi pomysłami zabezpieczyć się kolejnym wyzwalaczem DDL:

IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'tr_DDL_DenyRename')
  DROP TRIGGER tr_DDL_DenyRename ON ALL SERVER;
GO
CREATE TRIGGER tr_DDL_DenyRename
ON ALL SERVER
FOR RENAME
AS
BEGIN
  RAISERROR('Zmiany nazw obiektów są zabronione.', 16, 1);
  ROLLBACK;
END;
GO
Reklamy

Posted in MS SQL Server | Otagowane: , , | Leave a Comment »