CONNECTED Conference 2023 - Aufzeichnungen jetzt hier verfügbar +++                     

Suche

über alle News und Events

 

Alle News

 

Für Entwickler, Architekten, Projektleiter und...

Read more

In der Welt der Softwareentwicklung ist die...

Read more

QUIBIQ spendet für den guten Zweck – und für...

Read more

Eine bestimmte Antwort auf einen HTTP Request zu...

Read more

In einer Welt, die von stetigem Wandel geprägt...

Read more

In einem unserer Kundenprojekte, war das Ziel eine...

Read more

QUIBIQ Hamburg wird mit dem Hamburger...

Read more

Zwei Tage lang wurde vom 14.-15.11 wieder das...

Read more

Was ist ein Excel-Plugin – und wann ist es...

Read more

Wir expandieren, bringen Kunden und Talente besser...

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. 

 

Ihre Kontaktmöglichkeiten

Sie haben eine konkrete Frage an uns


 

Bleiben Sie immer auf dem Laufenden


 

Mit meinem "Ja" erkläre ich mich mit der Verarbeitung meiner Daten zur Zusendung von Informationen einverstanden. Ich weiß, dass ich diese Erklärung jederzeit durch einfache Mitteilung widerrufen kann. Bei einem Nein an dieser Stelle erhalte ich zukünftig keine Informationen mehr.

© QUIBIQ GmbH · Impressum · Datenschutz