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
- 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.
- 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.
- 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.
- 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.