Am 09.05.2022 wurde ein Artikel von Microsoft...

Read more

Neben den altbekannten Logic Apps (Consumption),...

Read more

Im Jahr 2022 fallen eine Reihe von .Net Versionen...

Read more

SAP in die Power Platform integrieren – In einem...

Read more

Bicep Templates benutzen eine deklarative Syntax...

Read more

In BizTalk gibt es einige Alternativen, wie...

Read more

Wir sind auf ein seltsames Phänomen bei einem...

Read more

Nach der Migration konnten in VS 2019 „normale“...

Read more

Warum wir uns als Sponsor engagieren, warum und...

Read more

Für einen Kunden sollen Produktkataloge im XML...

Read more

How-to: UnitTesting für den SQL-Server (Teil2)

Der Schnelleinstieg in die Welt der Tests für den SQL-Server.

1. Funktionsisolation

Funktionen und Prozeduren (im Weiteren nur „Funktionen“ genannt) im SQL-Server sind in den wenigsten Fälle unabhängig von Tabelleninhalten oder Abfrageergebnissen.

Es ist daher erforderlich, die zu testende Funktionen für die Laufzeit des Tests von den Datentabellen der Datenbank zu „entkoppeln“ und die Tests mit definierten Tabelleninhalten durchzuführen. Nur so kann geprüft werden, ob die Funktionen ihre Aufgaben korrekt ausführen oder nicht.

Daher stellt tSQLt einige Funktionen bereit, die diese Isolation ermöglichen:

  • FakeTable
    temporär angepasster Tabelleninhalt, unabhängig von Realdaten
  • ApplyConstraints, Constraints für FakeTables
  • ApplyTrigger, Trigger für FakeTables
  • FakeFunction, temporär ausgetauschte Funktion mit losgelöster Logik
  • SpyProcedure, entkoppelt aufgerufene (sub-)-Prozeduren und zeichnet die Übergabeparameter an diese Prozedur(en) auf

 

2. Komponenten der Isolation

2.1. FakeTable

In unserer Datenbank wird eine Abfrage-Funktion eingesetzt, um in einer Tabelle nach Artikel zu suchen, in deren Namen ein gegebener Suchbegriff vorkommt.

Hier die Funktion:

-- Ermitteln von Artikeln, mit geg. Suchbegriff im Namen
-- bei Mehrfachvorkommen, die neueste Version (höchste ArtikelID) verwenden

CREATEORALTERFUNCTION article.GetArticlesByName(
   @NamePart nvarchar(32))

RETURNS @returntable TABLE (
  ArticleCode nvarchar(13),
  ArticleFullName nvarchar(255)
)

AS
BEGIN

  INSERT @returntable(ArticleCode,ArticleFullName)
  SELECT
    ArticleCode,
    ArticleFullName
  FROM
  ( 
    SELECT
      a.ArticleCode,
      a.ArticleID,
      a.ArticleFullName,
      row_number()OVER(PARTITIONBY a.ArticleCode ORDERBY a.ArticleID desc)AS rownum
FROM article.Article a
WHERE ArticleFullName like '%'+@NamePart+'%'
  ) art
  WHERE art.rownum = 1
  RETURN
END

Für einen isolierten Test müssen die Inhalte der Tabelle article.Article simuliert werden, um beim Test immer die gleiche Ausgangssituation bereitzustellen.

Im vorliegenden Fall hat die Tabelle article.Article 80 Datenfelder!!

Wenn wir die Tabelle vollständig simulieren wollten, müssten wir in unserer Testfunktion diese 80 Tabellenfelder für ein paar Testartikel mit Daten befüllen. Welch ein Aufwand! Wie wir hier aber sehen, benutzt unsere zu testende Funktion aber nur 3 der 80 Felder:

Daher reicht es hier aus, bei der Simulation genau die 3 Felder bereitzustellen, die hier benötigt werden. Die restlichen Felder können einfach unterschlagen werden. Die simulierte Tabelle lässt dies zu, da bei FakeTable „per default“ keine Constraints und Trigger auf die simulierte Tabelle übertragen werden.

Sollte das Übernehmen von Eigenschaften auf die Fake-Tabelle erwünscht sein, kann dies beim Befehl FaleTable als Parameter definiert werden. (@Identity, @ComputedColumns und @Default)

Die Testfunktion:

CREATEORALTERPROCEDURE [testGetArticleByName].[Test_GetArticleByName_Test]
AS
BEGIN

  -- Arrange
  -- expected Result - table
  CREATETABLE expectedResult(
    ArticleCode nvarchar(13),
    ArticleFullName nvarchar(255)
  ) 
    -- expected Result - content
  INSERTINTO expectedResult(ArticleCode, ArticleFullName)VALUES
  ('123456','Test-Bier 0,25l (Mehrweg)'),-- Art. 123456 mit höchster ID
  ('567890','Corona-Test-Set')

  -- Fake table
  EXEC tSQLt.FakeTable'article.Article'; 

  INSERTINTO article.Article
    ( ArticleId
     ,ArticleCode
     ,ArticleFullName)
     VALUES
           (1,'123456','Test-Bier 0,25l'),
           (2,'234567','hmm-Joghurt '),
           (3,'345678','Razupaltuff-Schnitte'),
           (4,'456789','Blumenkohl'),
           (5,'567890','Corona-Test-Set'),
           (6,'123456','Test-Bier 0,25l (Mehrweg)')
  -- Act
  SELECT ArticleCode, ArticleFullName
  INTO actualComputedResult -- Result of Test-call
  FROM article.GetArticlesByName('Test')

    -- Assert
  EXEC tSQLt.AssertEqualsTable'actualComputedResult','expectedResult' 

END

2.2. ApplyConstraint

Für den isolierten Test von Constraints kann unter tSQLt mit ApplyConstraint einer FakeTable (die ja keinerlei Keys oder Constraints von ihrer Originaltabelle erbt) hinzugefügt werden. tSQLt unterstützt dabei folgende Constraint-Typen: CHECK, FOREIGN KEY , UNIQUE und PRIMARY KEY -Constraints.

In tSQLt können die Constraints nicht erstellt werden, hier werden die in der Datenbank vorhanden Constraints auf die Fake-Tabellen angewendet.

Daher sieht der Aufruf dann (nach FakeTable) folgendermaßen aus:

EXEC tSQLt.ApplyConstraint'article.Article','PK_ArticleID'
(erstellt einen Primary Key auf die Spalte ID)

Beim Versuch, ungültige Werte (hier: doppelte Verwendung der ID=1) in die Fake-Tabelle einzutragen, kommt es dann zum Fehler, der ausgewertet werden kann:

  DECLARE @ErrorMessage NVARCHAR(MAX)=''
  -- Fake table
  EXEC tSQLt.FakeTable'article.Article';

  -- Add Constraint
  EXEC tSQLt.ApplyConstraint'article.Article','PK_ArticleID'

  -- insert valid data
  INSERTINTO article.Article(ArticleID,ArticleCode,ArticleFullName)
  VALUES      (1,'123456','Test-Bier 0,25l'),
                      (2,'234567','hmm-Joghurt '),
                      (3,'345678','Razupaltuff-Schnitte'),
                      (4,'456789','Blumenkohl'),
                      (5,'567890','Corona-Test-Set'),
                      (6,'123456','Test-Bier 0,25l (Mehrweg)')

  -- insert invalid data
  BEGINTRY
    INSERTINTO article.Article(ArticleID,ArticleCode,ArticleFullName)
    VALUES    (1,'777777','Käsewürfel')
  ENDTRY
  BEGINCATCH
    SET @ErrorMessage =ERROR_MESSAGE()
  ENDCATCH

  -- Auswertung
  IF @ErrorMessage NOTLIKE'%PK_ArticleID%'
  BEGIN
    EXEC tSQLt.Fail'Expected error message containing ''PK_ArticleID'' but got: ''',@ErrorMessage,'''!';
  END

 

2.3. ApplyTrigger

Die Anwendung von ApplyTrigger ist mit ApplyConstraint vergleichbar. Auch die Trigger werden nicht in tSQLt erstellt, sondern wir weisen nur vorhandene Trigger der FakeTable zu.
Beispiel für die Anwendung des Triggers „LogInserts“ auf die Tabelle (gefakte) dbo.Article:

  EXEC tSQLt.FakeTable'dbo.Article';
  EXEC tSQLt.FakeTable'dbo.Log_Article';
  EXEC tSQLt.ApplyTrigger'dbo.Article','LogInserts';

  INSERTINTO dbo.Article(...)VALUES (...);

  SELECT LogMessage
  INTO #Actual
  FROM dbo.Log_Article;

  SELECTTOP(0)*
  INTO #Expected
  FROM #Actual;

  INSERTINTO #Expected
  VALUES('Log-Nachricht des Triggers...');

    EXEC tSQLt.AssertEqualsTable'#Expected','#Actual';

 

2.4. ​​​​​​​FakeFunction

Wird in einer Procedure oder einer View eine Function verwenden, die ihrerseits von Realdaten abhängig ist, so kann diese mit FakeFunction simuliert werden. Dazu muss lediglich eine Funktion mit demselben Typ (scalar/table) und denselben Parametern bereitgestellt werden, die isoliert definierte Werte zurückgibt.

EXEC tSQLt.FakeFunction'dbo.GetArticleInfo','dbo.Fake_GetArticleInfo';

So kann nun eine Procedure, die die simulierte Function verwendet, isoliert getestet werden.

​​​​​​​​​​​​​​2.5. ​​​​​​​SpyProcedure

Große, zusammenhängende Prozeduren sind unübersichtlich und schlecht wart- und testbar. Daher wird versucht, die Prozeduren klein und nach Aufgaben getrennt zu halten. Für dem Test bietet nun tSQLt eine Möglichkeit, diese „Fragmente“ einzeln durch eine geeignete Isolation zu testen,

SypProcedure bietet nun die Möglichkeit, Prozeduraufrufe (innerhalb einer Procedure) so zu isolieren, dass

  1. Die aufzurufende Prozedur nicht aufgerufen wird, sondern lediglich die Parameter (sofern es welche gibt) die beim Procedureaufruf übergeben wurden, in einer Tabelle zur späteren, separaten Auswertung, zu sammeln.
  2. Der Rückgabewert, den wir von der aufgerufenen (abgekoppelten) Procedure erwarten und für den weiteren Verlauf unserer zu testenden Procedure benötigen, einfach beim Aufruf von SpyProcedure mitgegeben werden kann.

Beispiel: hier eine Procedure (dbo.IsDriveSpaceTooLow), die ihrerseits eine weitere Procedure (dbo.GetDiskSpace) aufruft, die für den Test abgekoppelt werden soll:

CREATEORALTERPROCEDURE dbo.IsDriveSpaceTooLow
AS
BEGIN

    DECLARE @DiskSpace INT = 400;
    EXEC dbo.GetDiskSpace@Drive ='C', @DiskSpace = @DiskSpace OUT;

    IF @DiskSpace < 512
        RETURN-1;
    ELSE
        RETURN 0;
END;
GO

CREATEORALTERPROCEDURE [dbo].[GetDiskSpace] @Drive varchar(2), @DiskSpace INTOUT
AS
BEGIN

  DECLARE @MBfree int

  -- available Drives
  DROPTABLEIFEXISTS #tbl_xp_fixeddrives
  CREATETABLE #tbl_xp_fixeddrives 
    (Drive varchar(2)NOTNULL,  [MB free] intNOTNULL) 

  -- Get free Diskspace
  INSERTINTO #tbl_xp_fixeddrives(Drive, [MB free])
  EXECmaster.sys.xp_fixeddrives

  --Select Drive
  SELECT @MBfree = [MB free] FROM #tbl_xp_fixeddrives WHERE Drive = @Drive 

  SET @DiskSpace = @MBFree 
  RETURN 1
END

Anmerkungen dazu:

1. Hier wird der Procedure dbo.GetDiskSpace ein Wert von 400 als Parameter mitgegeben. Der Parameter wird innerhalb der procedure nicht verwendet bzw. einfach überschrieben und soll hier nur als Beispiel dienen.

Die Testprocedure:

CREATEORALTERPROCEDURE testDiskUtil.[test IsDriveSpaceTooLow returns -1 if drive space is less than 512 MB]
AS
BEGIN

    EXEC tSQLt.SpyProcedure'dbo.GetDiskSpace','SET @DiskSpace = 200';

    DECLARE @ReturnValue INT;
    EXEC @ReturnValue = dbo.IsDriveSpaceTooLow;

    EXEC tSQLt.AssertEquals-1, @ReturnValue;
    SELECT*FROM dbo.GetDiskSpace_SpyProcedureLog
END
GO

Anmerkungen:

1. Abkoppeln der procedure dbo.GetDiskSpace, die von der proc GetDiscPaceTooLow verwendet wird. Durch SET @DiskSpace = 200 wird der Rückgabewert simuliert.

2. Das Select-Statement ist für den Test nicht erforderlich, hier wird der Inhalt der Aufruf- und Parametertabelle ausgegeben.

Die Ausgabe(n):

Wir simulieren einen Rückgabewert von 200 (führt erwartungsgemäß zu Ergebnis „-1“) -> daher Test erfolgreich.

Die Ausgabetabelle zeigt nun den Aufrufparameter 400:

 

​​​​​​​​​​​​​​2.6. ​​​​​​​RemoveObject

Für manche Test-Szenarien ist es erforderlich, weitere Datenbankobjekte durch einen Mock zu ersetzen. Hierzu stellt tSQLt die beiden Funktionen „RemoveObject“ und „RemoveObjectIfExists“ bereit.

In vielen Tutorials werden mit RemoveObject Funktionen oder Prozeduren entfernt und mit anderen Komponenten ersetzt. Hierzu stehen allerdings (s.o.) FakeFunction und SypProcedure bereit, die in den meisten Fällen diese Aufgabe einfacher und eleganter erledigen.

RemoveObject kann aber auch andere Datenbankobjekte entfernen: Hier z.B. wird ein Synonym für eine Tabelle in einer benachbarten Datenbank ersetzt:

CREATEORALTERPROCEDURE testDiskUtil.[test_ReplaceObjects]
as
BEGIN
  DECLARE @ErrorMessage nvarchar(max)
  --Synonym entfernen/simulieren
  EXEC tSQLt.RemoveObject'[dbo].[syn_CRMSystem_Artikel]'
  CREATESYNONYM [dbo].[syn_CRMSystem_Artikel] FOR [myTool].[Artikel]

  BEGINTRY
    SELECTTop 1 *FROM dbo.syn_CRMSystem_Artikel
  ENDTRY
  BEGINCATCH
    SET @ErrorMessage =ERROR_MESSAGE()
  ENDCATCH

  -- Auswertung
  IFNOT @ErrorMessage ISNULL
  BEGIN
    EXEC tSQLt.Fail'Error message: ''',@ErrorMessage,'''!';
  END
END

Anmerkung:

Die Ausgabe SELECT-Anweisung zeigt, dass hier nun die Werte aus der Datenbank [myTool].[Artikel] und nicht wie ursprünglich aus der Datenbank [CRMSystem].[Artikel] gelesen werden.

  1. Zusammenfassung

Wie die aufgeführten Beispiele zeigen, bietet tSQLt eine Reihe von Möglichkeiten der Funktionsisolation. Für automatisierte Test, die ggfs. auch über Build-Server ausgeführt werden, ist diese Isolation unumgänglich, da diese Systeme in der Regel keinen Zugriff auf produktive Datenbanksysteme haben.

  1. Ausblicke

Daher wollen wir uns im nächsten Schritt mit der automatisierten Ausführung von TEST über DevOps auseinandersetzen. Wie können die Tests durchgeführt werden und wie werden die Testergebnisse weiterverarbeitet.

Dieser Beitrag kommt von Thomas Amann, QUIBIQ Stuttgart. 

 

© QUIBIQ GmbH · Imprint · Data protection