DarlingData/.github/workflows/sql-tests.yml at 71bac1a7856b99f23c3810c903e17d900a6cd3dc · ReeceGoding/DarlingData · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
name: SQL Tests

on:
  push:
    branches: [dev]
  pull_request:
    branches: [dev, main]

jobs:
  test:
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        include:
          - version: '2017'
            image: mcr.microsoft.com/mssql/server:2017-latest
          - version: '2019'
            image: mcr.microsoft.com/mssql/server:2019-latest
          - version: '2022'
            image: mcr.microsoft.com/mssql/server:2022-latest
          - version: '2025'
            image: mcr.microsoft.com/mssql/server:2025-latest

    name: SQL Server ${{ matrix.version }}

    services:
      sqlserver:
        image: ${{ matrix.image }}
        env:
          ACCEPT_EULA: Y
          MSSQL_SA_PASSWORD: CI_Test#2026!
        ports:
          - 1433:1433
        options: >-
          --health-cmd "grep -q 'SQL Server is now ready for client connections' /var/opt/mssql/log/errorlog || exit 1"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 15

    steps:
      - uses: actions/checkout@v4

      - name: Install sqlcmd
        run: |
          # Ubuntu 24.04 runners have Microsoft repo pre-configured; avoid Signed-By conflicts
          if ! grep -rql 'packages.microsoft.com' /etc/apt/sources.list.d/ 2>/dev/null; then
            curl -sSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg
            source /etc/os-release
            echo "deb [arch=amd64,signed-by=/usr/share/keyrings/microsoft-prod.gpg] https://packages.microsoft.com/ubuntu/${VERSION_ID}/prod ${VERSION_CODENAME} main" | sudo tee /etc/apt/sources.list.d/mssql-release.list
          fi
          sudo apt-get update
          sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18

      - name: Create test database
        env:
          SA_PASSWORD: CI_Test#2026!
        run: |
          /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -C -No -b -Q "CREATE DATABASE DarlingData_CI_Test;"
          /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -C -No -b -d DarlingData_CI_Test -Q "ALTER DATABASE DarlingData_CI_Test SET QUERY_STORE = ON;"

      - name: Regenerate DarlingData.sql from current branch
        shell: pwsh
        run: |
          Push-Location Install-All
          ./Merge-All.ps1
          Pop-Location

      - name: Install all procedures
        env:
          SA_PASSWORD: CI_Test#2026!
        run: |
          /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -C -No -b -d DarlingData_CI_Test -i "Install-All/DarlingData.sql"

      - name: Verify procedures exist
        env:
          SA_PASSWORD: CI_Test#2026!
        run: |
          /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -C -No -b -d DarlingData_CI_Test -Q "
            SET NOCOUNT ON;
            DECLARE @missing int = 0;
            IF OBJECT_ID(N'dbo.sp_HealthParser', N'P') IS NULL BEGIN SET @missing += 1; PRINT 'MISSING: sp_HealthParser'; END;
            IF OBJECT_ID(N'dbo.sp_HumanEvents', N'P') IS NULL BEGIN SET @missing += 1; PRINT 'MISSING: sp_HumanEvents'; END;
            IF OBJECT_ID(N'dbo.sp_HumanEventsBlockViewer', N'P') IS NULL BEGIN SET @missing += 1; PRINT 'MISSING: sp_HumanEventsBlockViewer'; END;
            IF OBJECT_ID(N'dbo.sp_IndexCleanup', N'P') IS NULL BEGIN SET @missing += 1; PRINT 'MISSING: sp_IndexCleanup'; END;
            IF OBJECT_ID(N'dbo.sp_LogHunter', N'P') IS NULL BEGIN SET @missing += 1; PRINT 'MISSING: sp_LogHunter'; END;
            IF OBJECT_ID(N'dbo.sp_PerfCheck', N'P') IS NULL BEGIN SET @missing += 1; PRINT 'MISSING: sp_PerfCheck'; END;
            IF OBJECT_ID(N'dbo.sp_PressureDetector', N'P') IS NULL BEGIN SET @missing += 1; PRINT 'MISSING: sp_PressureDetector'; END;
            IF OBJECT_ID(N'dbo.sp_QueryReproBuilder', N'P') IS NULL BEGIN SET @missing += 1; PRINT 'MISSING: sp_QueryReproBuilder'; END;
            IF OBJECT_ID(N'dbo.sp_QuickieStore', N'P') IS NULL BEGIN SET @missing += 1; PRINT 'MISSING: sp_QuickieStore'; END;
            IF @missing > 0
              RAISERROR('Missing %d procedure(s)', 16, 1, @missing);
            ELSE
              PRINT 'All 9 procedures installed successfully';
          "

      - name: Test help output
        env:
          SA_PASSWORD: CI_Test#2026!
        run: |
          /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -C -No -b -d DarlingData_CI_Test -i ".github/sql/test_help_output.sql"

      - name: Test basic execution
        env:
          SA_PASSWORD: CI_Test#2026!
        run: |
          /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -C -No -b -d DarlingData_CI_Test -i ".github/sql/test_basic_execution.sql"