[New App Request] Microsoft OLE DB Driver for SQL Server

[AScott-WWF]

New app request to replace the Evergreen version - Evergreen requires the Microsoft fwlink addresses updating in the JSON each time Microsoft release a new version (not really the point of EverGreen), so with NeverGreen we can scrape the webpage and always have the latest version of each "ring" (v18.x & v19.x) This reads the Release notes for the Microsoft OLE DB Driver for SQL Server, returns all versions, then refines this list to the latest for each ring before creating language specific URLs for each architecture. Note: ARM64 msi is not available for earlier v18.x builds

N.B. This also makes use of the ReleaseDate Parameter introduced in the [Enhancement] Microsoft SSMS

This is probably the most complex NeverGreen script to date 😉:

# Get-MicrosoftOLEDBDriverforSQLServer.ps1

$AppName = "Microsoft OLE DB Driver for SQL Server"
$ReleaseUrl = "https://learn.microsoft.com/en-us/sql/connect/oledb/release-notes-for-oledb-driver-for-sql-server?view=sql-server-ver16"

$RawContent = (Invoke-WebRequest -Uri $ReleaseUrl).RawContent
Write-Verbose "Obtaining $($AppName) Release Versions..."

$pattern = [regex]::new('<h2 id="\d+">(?<version>[^<]+)</h2>\s*<p>.*?<a href="(?<x64>[^"]+)"[^>]*>Download x64 installer</a><br>.*?<a href="(?<x86>[^"]+)"[^>]*>Download x86 installer</a>(<br>.*?<a href="(?<arm64>[^"]+)"[^>]*>Download Arm64 installer</a>)?</p>\s*<p>Released:\s*(?<date>[^<]+)</p>', [System.Text.RegularExpressions.RegexOptions]::Singleline)
$releases = $pattern.Matches($RawContent)

# Dictionary to store the latest version for each major version
$latestVersions = @{}

# Process matches
foreach ($release in $releases) {
    $ThisVersion = [version]$release.Groups['version'].Value
    $majorVersion = $ThisVersion.Major
    $x64Url = $release.Groups['x64'].Value
    $x86Url = $release.Groups['x86'].Value
    $arm64Url = $release.Groups['arm64'].Value
    $releaseDate = $release.Groups['date'].Value

    # Update the latest version for the major version if needed
    if (-not $latestVersions.ContainsKey($majorVersion) -or $ThisVersion -gt $latestVersions[$majorVersion].Version) {
        $latestVersions[$majorVersion] = @{
            Version = $ThisVersion
            x64Url = $x64Url
            x86Url = $x86Url
            arm64Url = $arm64Url
            ReleaseDate = $releaseDate
        }
    }
}

# Filter to keep only v19.x and v18.x major versions
$majorVersionsToKeep = @(18, 19)

# Process the latest versions
foreach ($majorVersion in $majorVersionsToKeep) {
    if ($latestVersions.ContainsKey($majorVersion)) {
        $latestVersion = $latestVersions[$majorVersion]
        $ThisVersion = $latestVersion.Version
        $x64Url = $latestVersion.x64Url
        $x86Url = $latestVersion.x86Url
        $arm64Url = $latestVersion.arm64Url
        $releaseDate = $latestVersion.ReleaseDate

        $AppVersions = @(
            @{Version = $ThisVersion; Ring = $ThisVersion.Major; Type = 'msi'; Architecture = 'x86'; Uri = $x86Url}
            @{Version = $ThisVersion; Ring = $ThisVersion.Major; Type = 'msi'; Architecture = 'x64'; Uri = $x64Url}
            @{Version = $ThisVersion; Ring = $ThisVersion.Major; Type = 'msi'; Architecture = 'ARM64'; Uri = $arm64Url}
        )

        $AppLanguages = @(
            @{Language = 'Chinese (Simplified)'; Pattern = '&clcid=0x804'}
            @{Language = 'Chinese (Traditional)'; Pattern = '&clcid=0x404'}
            @{Language = 'English'; Pattern = '&clcid=0x409'} # N.B this is 'English  (United States)'
            @{Language = 'French'; Pattern = '&clcid=0x40c'}
            @{Language = 'German'; Pattern = '&clcid=0x407'}
            @{Language = 'Italian'; Pattern = '&clcid=0x410'}
            @{Language = 'Japanese'; Pattern = '&clcid=0x411'}
            @{Language = 'Korean'; Pattern = '&clcid=0x412'}
            @{Language = 'Portuguese (Brazil)'; Pattern = '&clcid=0x416'}
            @{Language = 'Russian'; Pattern = '&clcid=0x419'}
            @{Language = 'Spanish'; Pattern = '&clcid=0x40a'}
        )

        foreach ($AppVersion in $AppVersions) {
            foreach ($AppLanguage in $AppLanguages) {
                $SearchCount = 2 # This may need increasing as future versions are released
                $Version = $AppVersion.Version
                $Uri = $AppVersion.Uri
                if ($NULL -ne $Uri) {
                    do {
                        if ($Uri) {
                            $Uri = (Resolve-Uri -Uri "$($AppVersion.Uri)$($AppLanguage.Pattern)").Uri
                            Write-Verbose "New $($AppLanguage.Language) URI : $Uri"
                            # Build each link with Platform specific versions
                            New-NevergreenApp -Name $AppName -Version $Version -Ring $AppVersion.Ring -Uri $Uri -Architecture $AppVersion.Architecture -Type $AppVersion.Type -Language $AppLanguage.Language -ReleaseDate $releaseDate
                            break
                        }

                        $SearchCount--
                    } until ($SearchCount -eq 0)

                    if ($SearchCount -eq 0) {
                        Write-Warning "Could not find $($AppLanguage.Language) release for $($AppName) $($Version) ($($AppVersion.Architecture))"
                    }
                }
            }
        }
    }
}