UGISS User Group Italiano SQL Server
Nell’articolo precedente, il terzo di questa serie, abbiamo descritto come scrivere ed eseguire manualmente una unit di test per un trigger! In questo articolo verrà descritto come automatizzare il test di uno o più oggetti SQL Server utilizzando tSQLt, Docker e GitHub Actions!
Tecnologie e framework utilizzati
Le potenzialità del framework tSQLt sono state descritte nell’articolo “Il framework tSQLt e l’esecuzione di un test”:
Docker è uno dei più diffusi sistemi per l’esecuzione di applicazioni in ambienti isolabili, minimali e facilmente distribuibili chiamati container. SQL Server 2017, e le successive versioni, possono essere eseguite in un container Docker, un tipico scenario di utilizzo di container è proprio quello che riguarda l’automazione dei test.
GitHub Actions è una piattaforma di Continuous Integration e Continuous Delivery (CI/CD) che consente di automatizzare pipeline di compilazione, test e distribuzione. È possibile creare workflows in grado di compilare e testare una soluzione software ad ogni pull request o commit sul repository. Attraverso le GitHub Actions, GitHub fornisce macchine virtuali Linux, Windows e macOS per eseguire i workflows in alternativa all’utilizzo di risorse ospitate in cloud (per esempio Azure VM).
Case history
Il database AdventureWorks2017 contiene la tabella Production.Product che rappresenta l’anagrafica dei prodotti gestiti e commercializzati dall’azienda immaginaria Adventure Works LTD 😊 il cui database è disponibile per il download su questo repository GitHub. L’azienda ha commissionato lo sviluppo di un trigger per impedire l’inserimento di nuovi prodotti aventi come scorta di sicurezza valori minori di 10. L’azienda desidera quindi avere sempre una scorta di magazzino pari a 10 unità. La scorta di sicurezza è un dato molto importante per le procedure automatiche di riordino dei materiali che ne tengono conto per l’emissione degli ordini a fornitore o degli ordini di produzione. Per semplificare l’esempio, il trigger risponderà soltanto all’evento OnInsert.
Gli script TSQL per la creazione del trigger e delle relative unit di test sono disponibili all’interno del repository GitHub sql-server-demos-ci-cd, la stored procedure usp_Raiserror_SafetyStockLevel gestisce gli errori in modo centralizzato.
Automazione dello unit testing con tSQLt, Docker e GitHub Action
L’implementazione del trigger e delle relative unit di test è stata completata, la prossima sfida consiste nel rendere automatica l’esecuzione dei test ad ogni commit sul repository. Per raggiungere l’obiettivo è necessario individuare una piattaforma di Continuous Integration e Continuous Delivery in grado di supportare l’utilizzo di container.
GitHub Actions sarà la nostra piattaforma di CI/CD, supporta l’utilizzo di container Docker ed è intimamente integrata in GitHub, il source control che gestisce il versioning delle modifiche al codice sorgente. L’utilizzo di GitHub Actions non è l’unica possibilità ma per questo progetto di esempio è sicuramente la più adatta.
Procediamo con la creazione di un workflow rappresentato da un processo automatizzato e configurabile che eseguirà uno o più job. I workflow vengono definiti con un file YAML archiviato nello stesso repository che detiene il codice sorgente. I workflow verranno attivati al verificarsi di un evento nel repository (per esempio un commit).
Un workflow può anche essere attivato manualmente o in base a una pianificazione definita. Il file YAML che implementa il workflow di automazione dei test è disponibile qui, gli step fondamentali sono:
- Definizione degli eventi di attivazione
- Creazione di un container Docker da una immagine di SQL Server su Linux
- Ripristino del database AdventureWorks2017
- Installazione del framework tSQLt
- Creazione degli oggetti database da testare (SUT)
- Creazione ed esecuzione delle unit di test
Definizione degli eventi di attivazione
La definizione degli eventi di attivazione avviene tipicamente all’inizio dello script YAML con un frammento di codice simile a quello riportato di seguito. Il workflow si attiva al verificarsi di eventi push o pull request sul ramo “master”. La specifica workflow_dispatch consente di eseguire il workflow manualmente dalla scheda azioni.
# Controls when the workflow will run
on:
# Triggers the workflow on push or pull request events but only for the "master" branch
push:
branches: [ "master" ]
pull_request:
branches: [ "master" ]
# Allows you to run this workflow manually from the Actions tab
workflow_dispatch:
Creazione di un container Docker da una immagine di SQL Server su Linux
La creazione di un container Docker da una immagine di SQL Server su Linux può essere eseguita richiedendo il servizio sqlserver accompagnato dal path dell’immagine Docker che si desidera utilizzare. Le immagini ufficiali, fornite da Microsoft, per SQL Server su Linux sono disponibili qui. Noi non useremo una immagine ufficiale scaricata dal registry Microsoft. Useremo l’immagine Docker, pubblicata da chriseaton, di SQL Server con il database AdventureWorks installato, la trovate a questo link. Il seguente frammento di codice YAML predispone il servizio SQL Server.
jobs:
windows-auth-tsqlt:
name: Installting tSQLt with SQL Auth
# The type of runner that the job will run on
runs-on: ubuntu-latest
services:
sqlserver:
image: chriseaton/adventureworks:latest
ports:
- 1433:1433
env:
ACCEPT_EULA: Y
SA_PASSWORD: 3uuiCaKxfbForrKPer poter referenziare il container Docker appena creato è importante salvare il suo identificativo in una variabile di ambiente. Il seguente frammento di codice YAML imposta la variabile ENV_CONTAINER_ID con l’ID del container creato.
- name: Set environment variable ENV_CONTAINER_ID
run: echo "ENV_CONTAINER_ID=$(docker ps --all --filter status=running --no-trunc --format "{{.ID}}")" >> $GITHUB_ENVRipristino del database AdventureWorks2017
Il ripristino del database AdventureWorks2017 viene eseguito con il seguente comando docker exec.
- name: Restore AdventureWorks2017
run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -Q "RESTORE DATABASE [AdventureWorks2017] FROM DISK = '/adventureworks.bak' WITH MOVE 'AdventureWorks2017' TO '/var/opt/mssql/data/AdventureWorks.mdf', MOVE 'AdventureWorks2017_log' TO '/var/opt/mssql/data/AdventureWorks_log.ldf'"Installazione del framework tSQLt
L’installazione dell’ultima versione del framework tSQLt nel database AdventureWorks2017 viene effettuata utilizzando la GitHub Actions tSQLt Installer pubblicata da lowlydba, trovate tutti i dettagli qui e sul marketplace GitHub Actions. Il frammento di codice YAML utilizzato per l’installazione del framework tSQLt nel database AdventureWorks2017 è il seguente.
steps:
- uses: actions/checkout@v2
- name: Install tSQLt with SQL auth on AdventureWorks2017
uses: lowlydba/tsqlt-installer@v1
with:
sql-instance: localhost
database: AdventureWorks2017
version: latest
user: sa
password: 3uuiCaKxfbForrKCreazione degli oggetti database da testare (SUT)
L’ambiente di test è pronto, abbiamo una istanza SQL Server su Linux dentro un container Docker, il database AdventureWorks2017 è stato ripristinato ed è pronto all’uso. Procediamo con la creazione del trigger e della stored procedure (che gestisce gli errori), rappresentano il nostro System Under Test (SUT).
Lo script di creazione del trigger TR_Product_SafetyStockLevel e quello di creazione della stored procedure usp_Raiserror_SafetyStockLevel sono salvati nella directory source del repository sql-server-demos-ci-cd.
Trigger e stored procedure vengono creati nel database AdventureWorks2017 agganciato all’istanza SQL Server, il frammento di codice YAML che effettua questa operazione è il seguente.
- name: Create sp usp_Raiserror_SafetyStockLevel
run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./source/usp-raiserror-safetystocklevel.sql
- name: Create TR_Product_SafetyStockLevel
run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./source/tr_product_safetystocklevel.sqlCreazione ed esecuzione delle unit di test
L’ultima fase di questo workflow è rappresentata dalla creazione ed esecuzione delle unit di test. Gli script di creazione della classe di test e delle singole unit sono contenuti all’interno della directory unit-test del repository sql-server-demos-ci-cd. Procediamo quindi con la creazione della classe di test dedicata al trigger TR_Product_SafetyStockLevel, l’abbiamo chiamata UnitTestTRProductSafetyStockLevel. Il seguente comando docker exec, attraverso sqlcmd, manda in esecuzione i comandi TSQL contenuti nello script test-class-trproductsafetystocklevel.sql.
- name: Create test class UnitTestTRProductSafetyStockLevel
run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./unit-test/test-class-trproductsafetystocklevel.sqlProcediamo quindi con la creazione e l’esecuzione delle unit di test. Ogni file .sql della famiglia “test case” contenuto nella directory unit-test contiene i comandi TSQL per la creazione e l’esecuzione di una unit di test. Ogni unit di test verifica un solo test case. Per il trigger TR_Product_SafetyStockLevel abbiamo previsto quattro test case. Il seguente frammento di codice YAML crea ed esegue le unit di test previste.
- name: Create and run test case try to insert one wrong row
run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./unit-test/test-case-try-to-insert-one-wrong-row.sql
- name: Create and run test case try to insert one right row
run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./unit-test/test-case-try-to-insert-one-right-row.sql
- name: Create and run test case try to insert multiple rows
run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./unit-test/test-case-try-to-insert-multiple-rows.sql
- name: Create and run test case try to insert multiple rows ordered
run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./unit-test/test-case-try-to-insert-multiple-rows-ordered.sqlLo script YAML relativo al workflow di esecuzione delle unit di test è completo, lo trovate qui, non ci resta che verificarlo eseguendolo manualmente dalla scheda azioni. Il workflow verrà completato con successo se tutte le operazioni eseguite avranno dato esito positivo.
Conclusioni
Le unit di test sviluppate per una soluzione SQL Server non hanno il solo scopo di verificare che siano stati soddisfatti i requisiti una volta, prima del rilascio; il vero game changer è rappresentato dalla possibilità di ripetere le verifiche durante lo sviluppo di nuovo codice e durante la correzione di bug. La ripetibilità dei test fornisce la possibilità di automatizzarli, condizione essenziale per integrare test automatici all’interno di uno strumento di Continuous Integration.
