Use PowerShell for database deployment smoke test

PowerShell is a command-line shell and scripting environment that allows command-line users and script writers to take advantage of the powerful features of the .NET Framework and also call Azure. With PowerShell V5 included in Win10, PowerShell can also be easily integrated into Jenkins.

The problem to be solved in this article:

After the database is deployed, perform a smoke test to ensure that the corresponding objects, such as tables, stored procedures, views, specific data, etc., have been deployed in place to avoid mixing with other errors during subsequent integration tests and reduce the time of Root Cause.

solution:

  1. Using the Pester testing framework in PowerShell, Pester provides a framework for executing and verifying PowerShell commands through unit tests within PowerShell. Pester follows a file naming convention: named tests can be automatically discovered during testing, and a set of simple functions. Pester is equivalent to PowerShell's xUnit.
  2. Users only need to focus on writing SQL statements to verify the database. Pay attention to 2 points:
    I. Use semicolons; as a separation of a batch of Sql statements
    II. Sql statement execution returns have records, which is correct

For example as follows


-- check newly deployed
SELECT * from sys.all_objects where name = 'td_searchbyname' and create_date > dateadd(MI, -10, getdate());
-- check deployed and no current modification
SELECT * from sys.all_objects where name = 'td_searchbyemail' and modify_date < dateadd(MI, -10, getdate());
SELECT * FROM TABLE123 WHERE FIELDA = 'EXPECTEDVALUE' AND FIELDB = 'FFFF';

-- check output of query procedure is the expected value, with several lines
create table #TempTable1(name VARCHAR(50) PRIMARY KEY, quantity INTEGER)
insert into #TempTable1
EXEC po_searchbyname 'Mike'
SELECT * from #TempTable1 where quantity = 234;

Set the suffix of the file to .sqlt, and leave the rest of the execution, reports, etc. to PowerShell.

$FileList = Get-ChildItem -Path $SqltPath -Include "*.sqlt"  -Recurse

$SqlPath is the root directory of the .sqlt file, or it may be the root directory of other Sql files. The above statement finds all .sqlt files and puts them in F i l e L i s t. Then, traverse the FileList. Then, traverse F. I L E L I S T when the . Then later , all over calendarFileList, generate a Pester Test Context according to each .sqlt file, as follows

  foreach ($SqltFile in $FileList) {
        $SqlList = Get-ZmSqlTestCaseList($SqltFile)
        Context "File:$($SqltFile.Name)" {
            It "SQL:" -TestCases $SqlList {
                $sqlcmd = $sqlConn.CreateCommand()
                $sqlcmd.CommandText = $Sql
                $result = $sqlcmd.ExecuteScalar()
                $result | Should -Not -BeNullOrEmpty
            }
        }
    }

The above core function is Get-ZmSqlTestCaseList($SqltFile)

function Get-ZmSqlTestCaseList($SqltFile) {
    $RawSqlList = Get-Content $SqltFile -Delimiter ";"
    $SqlList = [System.Collections.ArrayList]::new()
    foreach ($RawSql in $RawSqlList) {
        $Sql = ConvertTo-ZmValidSql $RawSql
        if ($Sql) {
            [void]$SqlList.Add(@{Sql = $Sql })
        }
    }
    return $SqlList
}

$RawSqlList = Get-Content $SqltFile -Delimiter “;”

According to ";", we get a preliminary list of Sql statements, and then check one by one and convert to valid Sql statements. The key function is ConvertTo-ZmValidSql

function ConvertTo-ZmValidSql([string]$RawSql) {
    if ($null -eq $RawSql) {
        return $null
    }
    $Sql = $RawSql.Trim()
    if ($Sql.Length -lt 8) {
        return $null
    }
    $BatchLineList = $Sql.Split("`n")
    $NotCommentLineCount = 0
    foreach ($item in $BatchLineList) {
        $item = $item.Trim()
        if ($item.Length -ge 2) {
            $NotCommentLineCount = $NotCommentLineCount - $item.SubString(0, 2).IndexOf("--")
        }
    }
    if ($NotCommentLineCount -eq 0) {
        return $null
    }
    return $Sql
}

The above sentence is judged by semicolon; whether the divided Sql sentence is valid or not, slightly converted.


$FileList = Get-ChildItem -Path $SqltPath -Include "*.sqlt"  -Recurse
Describe "DB-Smoke-Testing" {
    BeforeAll {
        $SqlConn.Open()
    }
    AfterAll {
        $SqlConn.Close()
    }
    foreach ($SqltFile in $FileList) {
        $SqlList = Get-ZmSqlTestCaseList($SqltFile)
        Context "File:$($SqltFile.Name)" {
            It "SQL:" -TestCases $SqlList {
                $Sqlcmd = $SqlConn.CreateCommand()
                $Sqlcmd.CommandText = $Sql
                $result = $Sqlcmd.ExecuteScalar()
                $result | Should -Not -BeNullOrEmpty
            }
        }
    }
}

Among them, $SqlConn is System.Data.SqlClient.SqlConnection, which is initialized in front.

The above test results are the same as other Junit results, which can be recorded by Jenkins for easy query.

Therefore, for DBO, you only need to write sql query statements in the .sqlt file.


summary:

This article explains the specific problems of a database smoke test, which is a bit different from the blog posts in the past. For most old readers, it is not used. Therefore, the last message to be delivered is worth trying to improve the convenience of testing and strengthen continuous integration and continuous testing.