[New App Request] Microsoft ODBC Driver for SQL Server

[AScott-WWF]

Similar in behaviour to the [New App Request] Microsoft OLE DB Driver for SQL Server
This new app request is to replace the Evergreen version - as Evergreen also 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" (v17.x & v18.x)
This reads the Release Notes for Microsoft ODBC Driver for SQL Server on Windows, 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 v17.x builds

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

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

# Get-MicrosoftODBCDriverforSQLServer.ps1

$AppName = "Microsoft ODBC Driver for SQL Server"

# Define the URL for the release notes and Installation Instructions
$ReleaseURL = "https://learn.microsoft.com/en-us/sql/connect/odbc/windows/release-notes-odbc-sql-server-windows?view=sql-server-ver16"
$InstallInstructionsUrl = "https://learn.microsoft.com/en-us/sql/connect/odbc/windows/system-requirements-installation-and-driver-files?view=sql-server-ver16#installing-microsoft-odbc-driver-for-sql-server"

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Fetch the HTML content from the URL
$htmlContent = (Invoke-WebRequest -Uri $ReleaseURL).RawContent

# Define the regex pattern for build versions
$regexBuildVersions = '<h2 id="(?<id>.*?)">(?<buildversion>17\.\d+(\.\d+){0,2}|18\.\d+(\.\d+){0,2})</h2>'

# Find all build versions
$releases = [regex]::Matches($htmlContent, $regexBuildVersions)

#Write-Verbose "Releases: $($releases)"
# Initialize variables to store the latest releases
$latestRelease18x = $null
$latestRelease17x = $null

# Loop through each match to determine the latest releases
foreach ($match in $releases) {
    $id = $match.Groups["id"].Value
    $buildversion = $match.Groups["buildversion"].Value
    
	#Write-Verbose "Build Version: $($buildversion)"
	
    # Update the latest release for each version
    if ($buildversion -match '^18\.') {
        if (-not $latestRelease18x -or [version]$buildversion -gt [version]$latestRelease18x.BuildVersion) {
            $latestRelease18x = [PSCustomObject]@{ Id = $id; BuildVersion = $buildversion }
        }
    } elseif ($buildversion -match '^17\.') {
        if (-not $latestRelease17x -or [version]$buildversion -gt [version]$latestRelease17x.BuildVersion) {
            $latestRelease17x = [PSCustomObject]@{ Id = $id; BuildVersion = $buildversion }
        }
    }
}

Write-Verbose ""
Write-Verbose "Latest 18.x release: $($latestRelease18x.BuildVersion)"
Write-Verbose "Latest 17.x release: $($latestRelease17x.BuildVersion)`n"

# Define the different regex patterns for each release - as text is in different order on webpage for v17 vs v18 releases
$regexDetails18x = '(?<buildversion>18\.\d+(\.\d+){0,2})</h2>\s*<p>\s*Version number:\s*(?<version>[\d\.]+)<br>\s*Released:\s*(?<released>.*?)</p>\s*<p>\s*<img src=".*?" role="presentation" data-linktype="relative-path">\s*<strong><a href="(?<x64url>https:\/\/go\.microsoft\.com\/fwlink\/\?linkid=\d+)" data-linktype="external">Download x64 installer<\/a></strong><br>\s*<img src=".*?" role="presentation" data-linktype="relative-path">\s*<strong><a href="(?<x86url>https:\/\/go\.microsoft\.com\/fwlink\/\?linkid=\d+)" data-linktype="external">Download x86 installer<\/a></strong><br>\s*<img src=".*?" role="presentation" data-linktype="relative-path">\s*<strong><a href="(?<arm64url>https:\/\/go\.microsoft\.com\/fwlink\/\?linkid=\d+)" data-linktype="external">Download ARM64 installer<\/a></strong><\/p>'
$regexDetails17x = '(?<buildversion>17\.\d+(\.\d+){0,2})</h2>\s*<p>\s*<img src=".*?" role="presentation" data-linktype="relative-path">\s*<strong><a href="(?<x64url>https:\/\/go\.microsoft\.com\/fwlink\/\?linkid=\d+)" data-linktype="external">Download x64 installer<\/a></strong><br>\s*<img src=".*?" role="presentation" data-linktype="relative-path">\s*<strong><a href="(?<x86url>https:\/\/go\.microsoft\.com\/fwlink\/\?linkid=\d+)" data-linktype="external">Download x86 installer<\/a></strong><\/p>\s*<p>\s*Version number:\s*(?<version>[\d\.]+)<br>\s*Released:\s*(?<released>.*?)</p>'

# Initialize array to store the latest release details
$latestReleases = @()

# Function to get release details based on the build version
function Get-ReleaseDetails($buildVersionObject, $regexDetails) {
    $id = $buildVersionObject.Id
    $detailsMatch = [regex]::Match($htmlContent, "<h2 id=`"$id`">($regexDetails).*?<p>If you need to download the installer", [System.Text.RegularExpressions.RegexOptions]::Singleline)
    
    if ($detailsMatch.Success) {
        return [PSCustomObject]@{
            BuildVersion = [version]$detailsMatch.Groups["buildversion"].Value
            Version = [version]$detailsMatch.Groups["version"].Value
            Released = $detailsMatch.Groups["released"].Value
            x64URL = $detailsMatch.Groups["x64url"].Value
            x86URL = $detailsMatch.Groups["x86url"].Value
            ARM64URL = $detailsMatch.Groups["arm64url"].Value
        }
    }
    return $null
}

# Combine the two latest releases into a single table '$latestReleases'
# Get the details for the latest v17.x release
if ($latestRelease17x) {
    $releaseDetails = Get-ReleaseDetails $latestRelease17x $regexDetails17x
    if ($releaseDetails) {
        $latestReleases += $releaseDetails
    }
}

# Get the details for the latest v18.x release
if ($latestRelease18x) {
    $releaseDetails = Get-ReleaseDetails $latestRelease18x $regexDetails18x
    if ($releaseDetails) {
        $latestReleases += $releaseDetails
    }
}

foreach ($release in $latestReleases) {
    # Define $AppVersions differently, to handle v17 releases not available in ARM64
    if ($release.version.Major -eq '17') {
        $AppVersions = @(
            @{Version = $release.Version; Ring = $($release.Version.Major); ReleaseDate = $release.Released; Type = 'msi'; Architecture = 'x86'; Uri = $release.x86URL}
            @{Version = $release.Version; Ring = $($release.Version.Major); ReleaseDate = $release.Released; Type = 'msi'; Architecture = 'x64'; Uri = $release.x64URL}
        )
    } elseif ($release.version.Major -eq '18') {
        $AppVersions = @(
            @{Version = $release.Version; Ring = $($release.Version.Major); ReleaseDate = $release.Released; Type = 'msi'; Architecture = 'x86'; Uri = $release.x86URL}
            @{Version = $release.Version; Ring = $($release.Version.Major); ReleaseDate = $release.Released; Type = 'msi'; Architecture = 'x64'; Uri = $release.x64URL}
            @{Version = $release.Version; Ring = $($release.Version.Major); ReleaseDate = $release.Released; Type = 'msi'; Architecture = 'ARM64'; Uri = $release.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) {
		Write-Verbose "`nRetrieving Language specific URLs for Version $($release.Version) for $($AppVersion.Architecture) Architecture...`n"
        foreach ($AppLanguage in $AppLanguages) {
            $SearchCount = 1 # This may need increasing as future versions are released
            $Version = $AppVersion.Version
            $Uri = $AppVersion.Uri # default Uri before language specific pattern has been appended
            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 $AppVersion.ReleaseDate
                        break
                    }

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

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

$ReleaseNotesUrl = $ReleaseURL
Write-Verbose "$($AppName) release notes are available here: $($ReleaseNotesUrl)"
Write-Verbose "$($AppName) Install instructions are available here:`n         $($InstallInstructionsUrl)"

[DanGough]

Thanks! My time has been taken up with other projects as of late but I will be picking this up again soon. I have some ideas to make some functions for these common patterns so that we won't need such lengthy scripts in future!

[AScott-WWF]

FYI: I have just updated the script due to an issue discovered when the new version 18.4 was released yesterday - this update means it now works for 2, 3 or 4 part version numbers

[AScott-WWF]

@DanGough Microsoft appeared to have updated the web page that this script reads, so this code was no longer working, I have updated the code above, so this now works again.
Same issue as #74