msvc2022-qt6.yml

# MySQL
# ---
# Forces TLS connections with the caching_sha2_password and certificate validation, also validates
# certificate's CN for issuer and subject.

# PostgreSQL
# ---
# Forces TLS connections with the certificate authentication and full certificate validation
# using the clientname=DN map=dn in the pg_hba.conf, so the username is matched against
# the entire Distinguished Name (DN) of the client certificate and sslmode=verify-full
# on the libpq client side, so the the server host name is matched against the CN (Common Name)
# stored in the server certificate.

name: MSVC2022 Qt6.7

on:
  workflow_dispatch:
  push:
    branches:
      - main
      - gh-actions

jobs:

  build:
    name: cmake build / ctest

    runs-on: windows-2022

    env:
      # Settings (constant variables)
      TINY_QT_VERSION: 6.7.2
      TINY_QT_SPEC: win64_msvc2019_64
      # The following was computed for the Debug build only, I have added also the Release build but
      # 700M is still enough, currently init. builds are: Debug - 0.3G, Release - 0.1G; because of
      # this increasing ~50M
      # First value is a compressed data size
      # ~ 190 * 3 + 100
      TINY_CCACHE_MAX_SIZE: 750M
      TINY_MYSQL_SERVICE: MySQL
      TINY_POSTGRES_SERVICE: postgresql-x64-14

    strategy:
      matrix:
        build-type:
          - key: debug
            name: Debug

          - key: release
            name: Release

    steps:
    - uses: actions/checkout@v4

    - name: TinyORM prepare environment
      run: |
        $runnerWorkPath = Resolve-Path -Path "$env:RUNNER_WORKSPACE/.."
        "TinyRunnerWorkPath=$runnerWorkPath" >> $env:GITHUB_ENV

        $sqlitePath = Join-Path -Path $runnerWorkPath -ChildPath "SQLite/$env:DB_SQLITE_DATABASE"
        "TinySQLitePath=$sqlitePath" >> $env:GITHUB_ENV

        $mysqlExePath = (Get-Command -Name mysql.exe).Source
        $mysqlInstallationPath = Split-Path -Parent -Path (Split-Path -Parent -Path $mysqlExePath)
        "TinyMySQLInstallationPath=$mysqlInstallationPath" >> $env:GITHUB_ENV

        $mysqlDataPath = Join-Path -Path $runnerWorkPath -ChildPath 'mysql/data'
        "TinyMySQLDataPath=$mysqlDataPath" >> $env:GITHUB_ENV

        $qtSpecSplitted = $env:TINY_QT_SPEC.Split('_')
        $qtSpecPlain = $qtSpecSplitted[1..($qtSpecSplitted.Count - 1)] -join '_'
        "TinyQtSpecPlain=$qtSpecPlain" >> $env:GITHUB_ENV

        "TinyParallel=$env:NUMBER_OF_PROCESSORS" >> $env:GITHUB_ENV

        $tinyormBuildName = 'msvc-cmake-${{ matrix.build-type.key }}'
        "TinyORMBuildName=$tinyormBuildName" >> $env:GITHUB_ENV

        $tinyormBuildTree = Join-Path -Path $env:RUNNER_WORKSPACE TinyORM-builds-cmake `
        "build-$tinyormBuildName"
        "TinyORMBuildTree=$tinyormBuildTree" >> $env:GITHUB_ENV
      env:
        DB_SQLITE_DATABASE: ${{ secrets.DB_SQLITE_DATABASE }}

    - name: Hosts add PostgreSQL server hostname
      run: >-
        "127.0.0.1`t$env:DB_PGSQL_HOST" |
        Add-Content -Path "$env:windir/System32/drivers/etc/hosts" -Force
      env:
        DB_PGSQL_HOST: ${{ secrets.DB_PGSQL_HOST_SSL }}

    - name: PostgreSQL initialize configuration
      working-directory: .github/resources/postgresql
      run: |
        Copy-Item -Path ./conf.d -Destination $env:PGDATA -Recurse -Force

        "include_dir = 'conf.d'" >> (Join-Path -Path $env:PGDATA -ChildPath 'postgresql.conf')

    - name: PostgreSQL SSL certificates initialize
      id: openssl-initialize-postgresql-certificates
      run: |
        $folderPath = Join-Path -Path $env:TinyRunnerWorkPath `
        -ChildPath 'tiny-postgresql-certificates'

        # Create an empty folder for generating certificates
        New-Item -Type Directory $folderPath

        "FolderPath=$folderPath" >> $env:GITHUB_OUTPUT

        # This hash invalidates the PostgreSQL certificates cache every month
        $hash = Get-Date -Format 'yyyyMM'
        "Hash=$hash" >> $env:GITHUB_OUTPUT

    - name: PostgreSQL SSL certificates restore cache
      uses: actions/cache/restore@v4
      id: openssl-cache-postgresql-certificates
      with:
        path: |
          ${{ env.folder_path }}/*.crt
          ${{ env.folder_path }}/*.key
        key: ${{ runner.os }}-openssl-${{ env.cache_name }}-${{ env.cache_hash }}
      env:
        # This hash invalidates this certificates cache every month
        cache_hash: ${{ steps.openssl-initialize-postgresql-certificates.outputs.Hash }}
        cache_name: postgresql-certificates
        folder_path: ${{ steps.openssl-initialize-postgresql-certificates.outputs.FolderPath }}

    - name: PostgreSQL SSL certificates generate
      if: steps.openssl-cache-postgresql-certificates.outputs.cache-hit != 'true'
      working-directory: ${{ steps.openssl-initialize-postgresql-certificates.outputs.FolderPath }}
      run: |
        Write-Output '::group::Print openssl version'
        openssl.exe version -a
        Write-Output '::endgroup::'

        Write-Output '::group::Create .rnd file'
        openssl.exe rand -out ./.rnd -writerand ./.rnd
        Write-Output '::endgroup::'

        Write-Output '::group::CA certificate'
        # -days 32 is important, -days 30 is not enough
        openssl.exe req -new -x509 -nodes -subj $env:DB_PGSQL_SSL_SUBJECT_CA -days 32 `
        -keyout ./root.key -out ./root.crt
        Write-Output '::endgroup::'

        Write-Output '::group::Server certificate'
        openssl.exe req -new -nodes -subj $env:DB_PGSQL_SSL_SUBJECT_SERVER -keyout ./server.key `
        -out ./server.csr
        $env:OPENSSL_SAN = "DNS:${env:DB_PGSQL_HOST}"
        openssl.exe x509 -req -CA ./root.crt -CAkey ./root.key -days 32 -set_serial 01 `
        -extfile $env:extfile -in ./server.csr -out ./server.crt
        Write-Output '::endgroup::'

        Write-Output '::group::Client certificate'
        openssl.exe req -new -nodes -subj $env:DB_PGSQL_SSL_SUBJECT_CLIENT `
        -keyout ./postgresql.key -out ./postgresql.csr
        $env:OPENSSL_SAN = "DNS:${env:DB_PGSQL_USERNAME}"
        openssl.exe x509 -req -CA ./root.crt -CAkey ./root.key -days 32 -set_serial 02 `
        -extfile $env:extfile -in ./postgresql.csr -out ./postgresql.crt
        Write-Output '::endgroup::'
      env:
        extfile: ${{ github.workspace }}/.github/resources/openssl/usr_cert.cnf
        DB_PGSQL_HOST: ${{ secrets.DB_PGSQL_HOST_SSL }}
        DB_PGSQL_USERNAME: ${{ secrets.DB_PGSQL_USERNAME }}
        DB_PGSQL_SSL_SUBJECT_CA: ${{ secrets.DB_PGSQL_SSL_SUBJECT_CA }}
        DB_PGSQL_SSL_SUBJECT_CLIENT: ${{ secrets.DB_PGSQL_SSL_SUBJECT_CLIENT }}
        DB_PGSQL_SSL_SUBJECT_SERVER: ${{ secrets.DB_PGSQL_SSL_SUBJECT_SERVER }}

    # Always verify, regardless if certificates were newly generated or restored from the cache
    - name: PostgreSQL SSL certificates verify
      working-directory: ${{ steps.openssl-initialize-postgresql-certificates.outputs.FolderPath }}
      run: |
        openssl.exe verify -CAfile ./root.crt ./server.crt ./postgresql.crt

    # Save the cache only if certificates were newly generated
    # The actions/cache/save allows to use the Move-Item during the install step
    - name: PostgreSQL SSL certificates save cache
      if: steps.openssl-cache-postgresql-certificates.outputs.cache-hit != 'true'
      uses: actions/cache/save@v4
      with:
        path: |
          ${{ env.folder_path }}/*.crt
          ${{ env.folder_path }}/*.key
        key: ${{ steps.openssl-cache-postgresql-certificates.outputs.cache-primary-key }}
      env:
        folder_path: ${{ steps.openssl-initialize-postgresql-certificates.outputs.FolderPath }}

    - name: PostgreSQL SSL certificates install
      working-directory: ${{ steps.openssl-initialize-postgresql-certificates.outputs.FolderPath }}
      run: |
        Write-Output '::group::Install server certificates'
        Copy-Item -Path ./root.crt -Destination $env:PGDATA
        Move-Item -Path ./server.crt, ./server.key -Destination $env:PGDATA
        Write-Output '::endgroup::'

        Write-Output '::group::Install client certificates'
        $folderPath = Join-Path -Path $env:APPDATA -ChildPath 'postgresql'
        New-Item -Type Directory $folderPath
        Move-Item -Path ./postgresql.crt, ./postgresql.key, ./root.crt -Destination $folderPath
        Write-Output '::endgroup::'

    - name: PostgreSQL add PGBIN on the $env:Path
      run: |
        $env:PGBIN >> $env:GITHUB_PATH

    - name: PostgreSQL service start
      run: |
        Set-Service -Name $env:TINY_POSTGRES_SERVICE -StartupType Manual
        Start-Service $env:TINY_POSTGRES_SERVICE

    - name: PostgreSQL change ${{ secrets.DB_PGSQL_ROOT_USERNAME }} password
      run: >-
        "alter user `"$env:DB_PGSQL_ROOT_USERNAME`"
        with password '$env:DB_PGSQL_ROOT_PASSWORD';" | psql.exe
      env:
        DB_PGSQL_ROOT_PASSWORD: ${{ secrets.DB_PGSQL_ROOT_PASSWORD }}
        DB_PGSQL_ROOT_USERNAME: ${{ secrets.DB_PGSQL_ROOT_USERNAME }}

    - name: PostgreSQL create TinyORM user
      run: >-
        "create user `"$env:DB_PGSQL_USERNAME`"
        with createdb password '$env:DB_PGSQL_PASSWORD';" | psql.exe
      env:
        DB_PGSQL_PASSWORD: ${{ secrets.DB_PGSQL_PASSWORD }}
        DB_PGSQL_USERNAME: ${{ secrets.DB_PGSQL_USERNAME }}
        PGPASSWORD: ${{ secrets.DB_PGSQL_ROOT_PASSWORD }}

    - name: PostgreSQL create TinyORM database
      run: |
        createdb.exe --owner=$env:DB_PGSQL_USERNAME $env:DB_PGSQL_DATABASE
      env:
        DB_PGSQL_DATABASE: ${{ secrets.DB_PGSQL_DATABASE }}
        DB_PGSQL_USERNAME: ${{ secrets.DB_PGSQL_USERNAME }}
        PGPASSWORD: ${{ secrets.DB_PGSQL_ROOT_PASSWORD }}

    - name: PostgreSQL initialize client authentication
      working-directory: .github/resources/postgresql
      run: >-
        $env:DB_PGSQL_DATABASE, $env:TINYORM_DATABASE_TESTS_SCHEMABUILDER |
        Set-Content -Path (Join-Path -Path $env:PGDATA -ChildPath 'tinyorm_dbs')

        $pgHbaConf = Join-Path -Path $env:PGDATA -ChildPath 'pg_hba.conf'

        $pgHbaTmplTmpl = "`n`n" +
        "# crystal`n" +
        'hostssl @tinyorm_dbs    {0}         127.0.0.1/32            cert  clientname=DN map=dn'

        $pgHbaTmpl = $pgHbaTmplTmpl -f $env:DB_PGSQL_USERNAME

        (Get-Content -Path $pgHbaConf) -creplace
        '(# +TYPE +DATABASE +USER +ADDRESS +METHOD)', "`$1$pgHbaTmpl" |
        Set-Content -Path $pgHbaConf

        $pgIdentConf = Join-Path -Path $env:PGDATA -ChildPath 'pg_ident.conf'

        'dn "/^{0}$" {1}' -f $env:DB_PGSQL_SSL_DN, $env:DB_PGSQL_USERNAME >> $pgIdentConf
      env:
        DB_PGSQL_DATABASE: ${{ secrets.DB_PGSQL_DATABASE }}
        DB_PGSQL_SSL_DN: ${{ secrets.DB_PGSQL_SSL_DN }}
        DB_PGSQL_USERNAME: ${{ secrets.DB_PGSQL_USERNAME }}
        TINYORM_DATABASE_TESTS_SCHEMABUILDER: ${{ secrets.TINYORM_DATABASE_TESTS_SCHEMABUILDER }}

    - name: PostgreSQL service restart
      run: |
        Restart-Service $env:TINY_POSTGRES_SERVICE

    - name: PostgreSQL service check status
      run: |
        Write-Output '::group::Get-Service'
        $pgsqlService = Get-Service $env:TINY_POSTGRES_SERVICE
        Write-Output $pgsqlService
        Write-Output '::endgroup::'

        Write-Output '::group::Service running check'
        $pgsqlService.status.ToString() -ceq 'Running' -or `
        $(throw "$env:TINY_POSTGRES_SERVICE service is not running") > $null
        Write-Output '::endgroup::'

        Write-Output '::group::pg_isready'
        pg_isready.exe
        Write-Output '::endgroup::'

    # Adding the DB_MYSQL_HOST_CLIENT isn't strictly needed (works without it too)
    - name: Hosts add MySQL server hostname
      run: >-
        "127.0.0.1`t$env:DB_MYSQL_HOST $env:DB_MYSQL_HOST_CLIENT" |
        Add-Content -Path "$env:windir/System32/drivers/etc/hosts" -Force
      env:
        DB_MYSQL_HOST: ${{ secrets.DB_MYSQL_HOST_SSL }}
        DB_MYSQL_HOST_CLIENT: ${{ secrets.DB_MYSQL_HOST_CLIENT_SSL }}

    - name: MySQL create data folder
      run: |
        New-Item -Type Directory $env:TinyMySQLDataPath

    - name: MySQL initialize my.ini configuration
      working-directory: .github/resources/windows
      run: >-
        (Get-Content -Path ./my_8_ssl.template.ini) -creplace
        '\{MYSQL_DATADIR\}', $env:TinyMySQLDataPath -creplace
        '\{MYSQL_HOST\}', $env:DB_MYSQL_HOST |
        Set-Content -Path "$env:TinyMySQLInstallationPath/my.ini"
      env:
        DB_MYSQL_HOST: ${{ secrets.DB_MYSQL_HOST_SSL }}

    # The ConvertFrom-SecureString must be called on the [SecureString] instance to be able to
    # store it in the environment or output variable
    - name: MySQL initialize data directory
      id: initializes-initialize-mysql-data-folder
      run: >-
        $regEx = '(?:\[MY-010454\].*temporary.*: )(?<password>.+)'

        $securedPassword = (mysqld.exe --initialize --console 2>&1 |
        Select-String -Pattern $regEx).Matches[0].Groups['password'].Value |
        ConvertTo-SecureString -AsPlainText | ConvertFrom-SecureString

        "SecuredPassword=$securedPassword" >> $env:GITHUB_OUTPUT

    # We can't generate certificates first and then initialize MySQL data folder, MySQL throws
    # error, it also generates all keys and certificates so we have remove them to generate are own
    - name: MySQL SSL certificates remove
      working-directory: ${{ env.TinyMySQLDataPath }}
      run: >-
        Remove-Item ./ca.pem, ./ca-key.pem, ./server-cert.pem, ./server-key.pem,
        ./client-cert.pem, ./client-key.pem

    - name: MySQL SSL certificates initialize
      id: openssl-initialize-mysql-certificates
      run: |
        $folderPath = Join-Path -Path $env:TinyRunnerWorkPath -ChildPath 'tiny-mysql-certificates'

        # Create an empty folder for generating certificates
        New-Item -Type Directory $folderPath

        "FolderPath=$folderPath" >> $env:GITHUB_OUTPUT

        # This hash invalidates the MySQL certificates cache every month
        $hash = Get-Date -Format 'yyyyMM'
        "Hash=$hash" >> $env:GITHUB_OUTPUT

    - name: MySQL SSL certificates restore cache
      uses: actions/cache/restore@v4
      id: openssl-cache-mysql-certificates
      with:
        path: |
          ${{ env.folder_path }}/*.pem
        key: ${{ runner.os }}-openssl-${{ env.cache_name }}-${{ env.cache_hash }}
      env:
        # This hash invalidates this certificates cache every month
        cache_hash: ${{ steps.openssl-initialize-mysql-certificates.outputs.Hash }}
        cache_name: mysql-certificates
        folder_path: ${{ steps.openssl-initialize-mysql-certificates.outputs.FolderPath }}

    - name: MySQL SSL certificates generate
      if: steps.openssl-cache-mysql-certificates.outputs.cache-hit != 'true'
      working-directory: ${{ steps.openssl-initialize-mysql-certificates.outputs.FolderPath }}
      run: |
        Write-Output '::group::Print openssl version'
        openssl.exe version -a
        Write-Output '::endgroup::'

        Write-Output '::group::Create .rnd file'
        openssl.exe rand -out ./.rnd -writerand ./.rnd
        Write-Output '::endgroup::'

        Write-Output '::group::CA certificate'
        # -days 32 is important, -days 30 is not enough
        openssl.exe req -new -x509 -nodes -subj $env:DB_MYSQL_SSL_SUBJECT_CA -days 32 `
        -keyout ./ca-key.pem -out ./ca.pem
        Write-Output '::endgroup::'

        Write-Output '::group::Server certificate'
        openssl.exe req -new -nodes -subj $env:DB_MYSQL_SSL_SUBJECT_SERVER -keyout ./server-key.pem `
        -out ./server-req.pem
        $env:OPENSSL_SAN = "DNS:${env:DB_MYSQL_HOST}"
        openssl.exe x509 -req -CA ./ca.pem -CAkey ./ca-key.pem -days 32 -set_serial 01 `
        -extfile $env:extfile -in ./server-req.pem -out ./server-cert.pem
        Write-Output '::endgroup::'

        Write-Output '::group::Client certificate'
        openssl.exe req -new -nodes -subj $env:DB_MYSQL_SSL_SUBJECT_CLIENT `
        -keyout ./client-key.pem -out client-req.pem
        $env:OPENSSL_SAN = "DNS:${env:DB_MYSQL_HOST_CLIENT}"
        openssl.exe x509 -req -CA ./ca.pem -CAkey ./ca-key.pem -days 32 -set_serial 02 `
        -extfile $env:extfile -in ./client-req.pem -out ./client-cert.pem
        Write-Output '::endgroup::'
      env:
        extfile: ${{ github.workspace }}/.github/resources/openssl/usr_cert.cnf
        DB_MYSQL_HOST: ${{ secrets.DB_MYSQL_HOST_SSL }}
        DB_MYSQL_HOST_CLIENT: ${{ secrets.DB_MYSQL_HOST_CLIENT_SSL }}
        DB_MYSQL_SSL_SUBJECT_CA: ${{ secrets.DB_MYSQL_SSL_SUBJECT_CA }}
        DB_MYSQL_SSL_SUBJECT_CLIENT: ${{ secrets.DB_MYSQL_SSL_SUBJECT_CLIENT }}
        DB_MYSQL_SSL_SUBJECT_SERVER: ${{ secrets.DB_MYSQL_SSL_SUBJECT_SERVER }}

    # Always verify, regardless if certificates were newly generated or restored from the cache
    - name: MySQL SSL certificates verify
      working-directory: ${{ steps.openssl-initialize-mysql-certificates.outputs.FolderPath }}
      run: |
        openssl.exe verify -CAfile ./ca.pem ./server-cert.pem ./client-cert.pem

    # Save the cache only if certificates were newly generated
    # The actions/cache/save allows to use the Move-Item during the install step
    - name: MySQL SSL certificates save cache
      if: steps.openssl-cache-mysql-certificates.outputs.cache-hit != 'true'
      uses: actions/cache/save@v4
      with:
        path: |
          ${{ env.folder_path }}/*.pem
        key: ${{ steps.openssl-cache-mysql-certificates.outputs.cache-primary-key }}
      env:
        folder_path: ${{ steps.openssl-initialize-mysql-certificates.outputs.FolderPath }}

    - name: MySQL SSL certificates install
      working-directory: ${{ steps.openssl-initialize-mysql-certificates.outputs.FolderPath }}
      run: |
        Write-Output '::group::Install CA certificate'
        Move-Item -Path ./ca.pem -Destination $env:TinyMySQLDataPath
        Write-Output '::endgroup::'

        Write-Output '::group::Install server certificates'
        Move-Item -Path ./server-cert.pem, ./server-key.pem -Destination $env:TinyMySQLDataPath
        Write-Output '::endgroup::'

        Write-Output '::group::Install client certificates'
        Move-Item -Path ./client-cert.pem, ./client-key.pem $env:TinyMySQLDataPath
        Write-Output '::endgroup::'

    - name: MySQL service install/start
      run: |
        mysqld.exe --install $env:TINY_MYSQL_SERVICE
        Start-Service $env:TINY_MYSQL_SERVICE

    # Securing the root account even on localhost is for testing to make sure that everything
    # works as expected
    # The secured_password is store in the string form so we have to re-create the [SecureString]
    # from this encrypted string and then it can be decrypted as normally would 😬
    - name: MySQL change ${{ secrets.DB_MYSQL_ROOT_USERNAME }} password
      run: >-
        "alter user '$env:DB_MYSQL_ROOT_USERNAME'@'localhost'
        identified with caching_sha2_password by '$env:DB_MYSQL_ROOT_PASSWORD'
        require issuer '$env:DB_MYSQL_SSL_SUBJECT_CA' and
        subject '$env:DB_MYSQL_SSL_SUBJECT_CLIENT';" |
        mysql.exe --user=$env:DB_MYSQL_ROOT_USERNAME
        --password=$($env:secured_password | ConvertTo-SecureString |
        ConvertFrom-SecureString -AsPlainText)
        --connect-expired-password
      env:
        DB_MYSQL_ROOT_PASSWORD: ${{ secrets.DB_MYSQL_ROOT_PASSWORD }}
        DB_MYSQL_ROOT_USERNAME: ${{ secrets.DB_MYSQL_ROOT_USERNAME }}
        DB_MYSQL_SSL_SUBJECT_CA: ${{ secrets.DB_MYSQL_SSL_SUBJECT_CA }}
        DB_MYSQL_SSL_SUBJECT_CLIENT: ${{ secrets.DB_MYSQL_SSL_SUBJECT_CLIENT }}
        secured_password: ${{ steps.initializes-initialize-mysql-data-folder.outputs.SecuredPassword }}

    - name: MySQL time zone POSIX tables initialize download
      id: downloads-initialize-mysql-timezone-tables
      run: |
        $filename = (Split-Path -Path $env:URL_MYSQL_TIMEZONE_TABLES -Leaf)
        "Filename=$filename" >> $env:GITHUB_OUTPUT

        $filepath = Join-Path -Path $env:RUNNER_TEMP -ChildPath $filename
        "Filepath=$filepath" >> $env:GITHUB_OUTPUT

        $basename = Split-Path -Path $filename -LeafBase

        $extractedFolder = Join-Path -Path $env:RUNNER_TEMP -ChildPath $basename
        "ExtractedFolder=$extractedFolder" >> $env:GITHUB_OUTPUT

        "Hash=$basename" >> $env:GITHUB_OUTPUT
      env:
        URL_MYSQL_TIMEZONE_TABLES: ${{ secrets.URL_MYSQL_TIMEZONE_TABLES }}

    - name: MySQL time zone POSIX tables restore cache (download)
      uses: actions/cache@v4
      id: downloads-cache-mysql-timezone-tables
      with:
        path: ${{ env.extracted_folder }}
        key: ${{ runner.os }}-databases-${{ env.cache_name }}-${{ env.cache_hash }}
      env:
        cache_hash: ${{ steps.downloads-initialize-mysql-timezone-tables.outputs.Hash }}
        cache_name: mysql-timezone-tables
        extracted_folder: ${{ steps.downloads-initialize-mysql-timezone-tables.outputs.ExtractedFolder }}

    - name: MySQL time zone POSIX tables download
      if: steps.downloads-cache-mysql-timezone-tables.outputs.cache-hit != 'true'
      run: >-
        Invoke-WebRequest -Uri $env:URL_MYSQL_TIMEZONE_TABLES -HttpVersion 2.0
        -MaximumRetryCount 3 -RetryIntervalSec 10 |
        Select-Object -ExpandProperty Content |
        Set-Content -Path $env:archive_filepath -AsByteStream

        7z.exe x -y -o"$env:RUNNER_TEMP" "$env:archive_filepath"
      env:
        archive_filepath: ${{ steps.downloads-initialize-mysql-timezone-tables.outputs.Filepath }}
        URL_MYSQL_TIMEZONE_TABLES: ${{ secrets.URL_MYSQL_TIMEZONE_TABLES }}

    - name: MySQL populate time zone tables 👌
      run: >-
        $filepath = Join-Path -Path $env:extracted_folder -ChildPath 'timezone_posix.sql'

        # source path can't be quoted, it works correctly even with spaces

        "source $filepath" |
        mysql.exe --user=$env:DB_MYSQL_ROOT_USERNAME --password=$env:DB_MYSQL_ROOT_PASSWORD mysql

        Restart-Service MySQL
      env:
        extracted_folder: ${{ steps.downloads-initialize-mysql-timezone-tables.outputs.ExtractedFolder }}
        DB_MYSQL_ROOT_PASSWORD: ${{ secrets.DB_MYSQL_ROOT_PASSWORD }}
        DB_MYSQL_ROOT_USERNAME: ${{ secrets.DB_MYSQL_ROOT_USERNAME }}

    - name: MySQL service check status
      run: |
        Write-Output '::group::Get-Service'
        $mysqlService = Get-Service $env:TINY_MYSQL_SERVICE
        Write-Output $mysqlService
        Write-Output '::endgroup::'

        Write-Output '::group::Service running check'
        $mysqlService.status.ToString() -ceq 'Running' -or `
        $(throw "$env:TINY_MYSQL_SERVICE service is not running") > $null
        Write-Output '::endgroup::'

        Write-Output '::group::Ping'
        mysqladmin.exe --user=$env:DB_MYSQL_ROOT_USERNAME `
        --password=$env:DB_MYSQL_ROOT_PASSWORD ping
        Write-Output '::endgroup::'
      env:
        DB_MYSQL_ROOT_PASSWORD: ${{ secrets.DB_MYSQL_ROOT_PASSWORD }}
        DB_MYSQL_ROOT_USERNAME: ${{ secrets.DB_MYSQL_ROOT_USERNAME }}

    - name: MySQL create TinyORM database
      run: >-
        "create database if not exists ``$env:DB_MYSQL_DATABASE``
        default character set $env:DB_MYSQL_CHARSET
        default collate $env:DB_MYSQL_COLLATION;" |
        mysql.exe --user=$env:DB_MYSQL_ROOT_USERNAME --password=$env:DB_MYSQL_ROOT_PASSWORD
      env:
        DB_MYSQL_CHARSET: ${{ secrets.DB_MYSQL_CHARSET }}
        DB_MYSQL_COLLATION: ${{ secrets.DB_MYSQL_COLLATION }}
        DB_MYSQL_DATABASE: ${{ secrets.DB_MYSQL_DATABASE }}
        DB_MYSQL_ROOT_PASSWORD: ${{ secrets.DB_MYSQL_ROOT_PASSWORD }}
        DB_MYSQL_ROOT_USERNAME: ${{ secrets.DB_MYSQL_ROOT_USERNAME }}

    - name: MySQL create TinyORM user
      run: >-
        "create user '$env:DB_MYSQL_USERNAME'@'%'
        identified with caching_sha2_password by '$env:DB_MYSQL_PASSWORD'
        require issuer '$env:DB_MYSQL_SSL_SUBJECT_CA' and
        subject '$env:DB_MYSQL_SSL_SUBJECT_CLIENT';
        grant all privileges on ``tinyorm\_%``.* to '$env:DB_MYSQL_USERNAME'@'%';
        grant select on ``mysql``.``time_zone_name`` to '$env:DB_MYSQL_USERNAME'@'%';
        flush privileges;" |
        mysql.exe --user=$env:DB_MYSQL_ROOT_USERNAME --password=$env:DB_MYSQL_ROOT_PASSWORD
      env:
        DB_MYSQL_PASSWORD: ${{ secrets.DB_MYSQL_PASSWORD }}
        DB_MYSQL_ROOT_PASSWORD: ${{ secrets.DB_MYSQL_ROOT_PASSWORD }}
        DB_MYSQL_ROOT_USERNAME: ${{ secrets.DB_MYSQL_ROOT_USERNAME }}
        DB_MYSQL_SSL_SUBJECT_CA: ${{ secrets.DB_MYSQL_SSL_SUBJECT_CA }}
        DB_MYSQL_SSL_SUBJECT_CLIENT: ${{ secrets.DB_MYSQL_SSL_SUBJECT_CLIENT }}
        DB_MYSQL_USERNAME: ${{ secrets.DB_MYSQL_USERNAME }}

    - name: MySQL add libmysql.dll on the $env:Path, INCLUDE, and LIB
      run: |
        "$env:TinyMySQLInstallationPath\lib" >> $env:GITHUB_PATH
        # Needed by the lastest FindMySQL.cmake module, it stopped working without this
        "INCLUDE=$env:TinyMySQLInstallationPath\include" >> $env:GITHUB_ENV
        "LIB=$env:TinyMySQLInstallationPath\lib" >> $env:GITHUB_ENV

    - name: SQLite create TinyORM database
      run: |
        New-Item -Type Directory (Split-Path -Path $env:TinySQLitePath -Parent)

        New-Item -Type File -Path $env:TinySQLitePath

    # Windows github hosted runners don't contain sqlite3 executable
    - name: Print MySQL and PostgreSQL database versions
      run: |
        Write-Output '::group::MySQL version'
        mysql.exe --version
        Write-Output '::endgroup::'

        Write-Output '::group::PostgreSQL version'
        postgres.exe --version
        Write-Output '::endgroup::'

    - name: Visual Studio 2022 pwsh shell setup
      uses: ilammy/msvc-dev-cmd@v1
      with:
        arch: x64

    - name: Qt ${{ env.TINY_QT_VERSION }} install base components
      uses: jurplel/install-qt-action@v4
      with:
        archives: qtbase
        version: ${{ env.TINY_QT_VERSION }}
        arch: ${{ env.TINY_QT_SPEC }}
        source: true
        src-archives: qtbase
        cache: true
        setup-python: false
        extra: --external 7z.exe
        dir: ${{ env.TinyRunnerWorkPath }}

    - name: QMYSQL driver DLLs initialize
      id: compiles-initialize-qmysql-dlls
      run: |
        $qtVersion = $env:TINY_QT_VERSION.Replace('.', '')
        "QtVersion=$qtVersion" >> $env:GITHUB_OUTPUT

        [System.Version] $mysqlVersionRaw = ('select version();' |
        mysql --user=$env:DB_MYSQL_USERNAME --password=$env:DB_MYSQL_PASSWORD)[1]
        $mysqlVersion = '{0}{1}' -f $mysqlVersionRaw.Major, $mysqlVersionRaw.Minor
        "MySQLVersion=$mysqlVersion" >> $env:GITHUB_OUTPUT
      env:
        DB_MYSQL_PASSWORD: ${{ secrets.DB_MYSQL_PASSWORD }}
        DB_MYSQL_USERNAME: ${{ secrets.DB_MYSQL_USERNAME }}

    - name: QMYSQL driver DLLs restore cache
      uses: actions/cache@v4
      id: compiles-cache-qmysql-dlls
      with:
        path: |
          ${{ env.QT_PLUGIN_PATH }}/sqldrivers/qsqlmysql*.dll
          ${{ env.QT_PLUGIN_PATH }}/sqldrivers/qsqlmysql*.pdb
        key: ${{ runner.os }}-drivers-${{ env.cache_name }}-${{ env.qt_version }}-${{ env.mysql_version }}
      env:
        cache_name: qmysql-dlls-qt
        qt_version: ${{ steps.compiles-initialize-qmysql-dlls.outputs.QtVersion }}
        mysql_version: ${{ steps.compiles-initialize-qmysql-dlls.outputs.MySQLVersion }}

    - name: QMYSQL driver DLLs build and install 🚀 (Qt ${{ env.TINY_QT_VERSION }})
      if: steps.compiles-cache-qmysql-dlls.outputs.cache-hit != 'true'
      run: >-
        ./tools/qtbuild-qmysql-driver.ps1 -SkipInitializeBuildEnvironment
        -QtRootPath (Get-Item -Path $env:QT_PLUGIN_PATH).Parent.Parent.Parent
        -QtVersion $env:TINY_QT_VERSION -QtSpec $env:TinyQtSpecPlain
        -MySQLServerPath $env:TinyMySQLInstallationPath
        -BuildPath $env:RUNNER_WORKSPACE

    # Always install the latest CMake and Ninja
    - name: CMake and Ninja install latest versions
      uses: lukka/get-cmake@latest

    - name: Ccache initialize
      id: ccache-initialize-cache
      run: |
        Write-Output '::group::Install'
        choco.exe install ccache --yes
        Write-Output '::endgroup::'

        Write-Output '::group::get-config cache_dir'
        $cachePath = ccache.exe --get-config cache_dir
        "CachePath=$cachePath" >> $env:GITHUB_OUTPUT

        "ImageOS=$env:ImageOS" >> $env:GITHUB_OUTPUT
        Write-Output '::endgroup::'

    - name: Ccache restore cache 🕺
      uses: actions/cache@v4
      with:
        path: ${{ env.cache_path }}
        key: ${{ runner.os }}-${{ env.image_os }}-ccache-${{ env.cache_name }}-${{ github.run_id }}
        restore-keys: |
          ${{ runner.os }}-${{ env.image_os }}-ccache-${{ env.cache_name }}-
      env:
        cache_name: msvc2022-qt6-${{ matrix.build-type.key }}
        cache_path: ${{ steps.ccache-initialize-cache.outputs.CachePath }}
        image_os: ${{ steps.ccache-initialize-cache.outputs.ImageOS }}

    - name: Ccache prepare configuration 🥳
      run: |
        ccache.exe --set-config max_size=$env:TINY_CCACHE_MAX_SIZE
        ccache.exe --set-config sloppiness=pch_defines,time_macros
        ccache.exe --set-config depend_mode=true
        ccache.exe --set-config inode_cache=true

    - name: Ccache print version and configuration
      run: |
        Write-Output '::group::Print version'
        ccache.exe --version
        Write-Output '::endgroup::'

        Write-Output '::group::Print ccache config'
        ccache.exe --show-config
        Write-Output '::endgroup::'

    # Must be after the ilammy/msvc-dev-cmd@v1 because vcvars64 overrides the VCPKG_ROOT
    - name: vcpkg prepare environment
      run: |
        "VCPKG_ROOT=$env:VCPKG_INSTALLATION_ROOT" >> $env:GITHUB_ENV
        'VCPKG_DEFAULT_TRIPLET=x64-windows' >> $env:GITHUB_ENV
        "VCPKG_MAX_CONCURRENCY=$env:TinyParallel" >> $env:GITHUB_ENV

    - name: Compiler print version (cl.exe)
      run: |
        cl.exe

    - name: Linker print version (link.exe)
      run: |
        link.exe | Select-Object -First 1

    - name: CMake print version
      run: |
        cmake.exe --version

    - name: Ninja print version
      run: |
        ninja.exe --version

    - name: vcpkg print version
      run: |
        vcpkg.exe --version

    - name: Qt print version
      run: |
        qmake.exe -query QT_VERSION

    - name: Ccache clear statistics
      run: |
        ccache.exe --zero-stats

    - name: TinyORM cmake configure (${{ env.TinyORMBuildName }})
      run: >-
        cmake.exe --log-level=DEBUG --log-context
        -S .
        -B $env:TinyORMBuildTree
        -G Ninja
        -D CMAKE_CXX_COMPILER_LAUNCHER:FILEPATH=ccache.exe
        -D CMAKE_TOOLCHAIN_FILE:FILEPATH="$env:VCPKG_INSTALLATION_ROOT/scripts/buildsystems/vcpkg.cmake"
        -D CMAKE_DISABLE_PRECOMPILE_HEADERS:BOOL=ON
        -D CMAKE_EXPORT_PACKAGE_REGISTRY:BOOL=OFF
        -D CMAKE_BUILD_TYPE:STRING=${{ matrix.build-type.name }}
        -D CMAKE_CXX_SCAN_FOR_MODULES:BOOL=OFF
        -D VCPKG_APPLOCAL_DEPS:BOOL=OFF
        -D VERBOSE_CONFIGURE:BOOL=ON
        -D BUILD_TREE_DEPLOY:BOOL=OFF
        -D STRICT_MODE:BOOL=OFF
        -D MYSQL_PING:BOOL=ON
        -D BUILD_TESTS:BOOL=ON
        -D ORM:BOOL=ON
        -D TOM:BOOL=ON
        -D TOM_EXAMPLE:BOOL=ON
        -D BUILD_DRIVERS:BOOL=OFF

    - name: TinyORM cmake build ✨ (${{ env.TinyORMBuildName }})
      working-directory: ${{ env.TinyORMBuildTree }}
      run: |
        cmake.exe --build . --target all --parallel $env:TinyParallel

    - name: Ccache print statistics
      run: |
        ccache.exe --show-stats --verbose

    - name: TinyOrm print linker version
      working-directory: ${{ env.TinyORMBuildTree }}
      run: |
        $tinyTinyOrm = '${{ matrix.build-type.key }}' -ceq 'debug' ? 'TinyOrm0d.dll' : 'TinyOrm0.dll'

        dumpbin.exe /HEADERS .\$tinyTinyOrm | Select-String -SimpleMatch 'linker version'

    - name: TinyORM add on the $env:Path
      run: |
        $env:TinyORMBuildTree >> $env:GITHUB_PATH

    - name: Create and Seed tables for unit tests 🎉
      working-directory: ${{ env.TinyORMBuildTree }}/tests/testdata_tom
      run: >-
        .\tom_testdata.exe migrate
        --database=tinyorm_testdata_tom_mysql,tinyorm_testdata_tom_postgres,tinyorm_testdata_tom_sqlite
        --seed --no-ansi
      env:
        DB_MYSQL_CHARSET: ${{ secrets.DB_MYSQL_CHARSET }}
        DB_MYSQL_COLLATION: ${{ secrets.DB_MYSQL_COLLATION }}
        DB_MYSQL_DATABASE: ${{ secrets.DB_MYSQL_DATABASE }}
        DB_MYSQL_HOST: ${{ secrets.DB_MYSQL_HOST_SSL }}
        DB_MYSQL_PASSWORD: ${{ secrets.DB_MYSQL_PASSWORD }}
        DB_MYSQL_SSL_CA: ${{ env.TinyMySQLDataPath }}/ca.pem
        DB_MYSQL_SSL_CERT: ${{ env.TinyMySQLDataPath }}/client-cert.pem
        DB_MYSQL_SSL_KEY: ${{ env.TinyMySQLDataPath }}/client-key.pem
        DB_MYSQL_SSL_MODE: ${{ secrets.DB_MYSQL_SSL_MODE }}
        DB_MYSQL_USERNAME: ${{ secrets.DB_MYSQL_USERNAME }}
        DB_PGSQL_CHARSET: ${{ secrets.DB_PGSQL_CHARSET }}
        DB_PGSQL_DATABASE: ${{ secrets.DB_PGSQL_DATABASE }}
        DB_PGSQL_HOST: ${{ secrets.DB_PGSQL_HOST_SSL }}
        DB_PGSQL_SEARCHPATH: ${{ secrets.DB_PGSQL_SEARCHPATH }}
        DB_PGSQL_SSLMODE: ${{ secrets.DB_PGSQL_SSLMODE }}
        DB_PGSQL_USERNAME: ${{ secrets.DB_PGSQL_USERNAME }}
        DB_SQLITE_DATABASE: ${{ env.TinySQLitePath }}
        TOM_TESTDATA_ENV: ${{ vars.TOM_TESTDATA_ENV }}

    # $env:NUMBER_OF_PROCESSORS / 2 : rounds down
    - name: TinyORM execute ctest 🔥
      working-directory: ${{ env.TinyORMBuildTree }}
      run: >-
        ctest.exe --output-on-failure
        --parallel ([int] $env:TinyParallel + $env:NUMBER_OF_PROCESSORS / 2)
      env:
        DB_MYSQL_CHARSET: ${{ secrets.DB_MYSQL_CHARSET }}
        DB_MYSQL_COLLATION: ${{ secrets.DB_MYSQL_COLLATION }}
        DB_MYSQL_DATABASE: ${{ secrets.DB_MYSQL_DATABASE }}
        DB_MYSQL_HOST: ${{ secrets.DB_MYSQL_HOST_SSL }}
        DB_MYSQL_PASSWORD: ${{ secrets.DB_MYSQL_PASSWORD }}
        DB_MYSQL_SSL_CA: ${{ env.TinyMySQLDataPath }}/ca.pem
        DB_MYSQL_SSL_CERT: ${{ env.TinyMySQLDataPath }}/client-cert.pem
        DB_MYSQL_SSL_KEY: ${{ env.TinyMySQLDataPath }}/client-key.pem
        DB_MYSQL_SSL_MODE: ${{ secrets.DB_MYSQL_SSL_MODE }}
        DB_MYSQL_USERNAME: ${{ secrets.DB_MYSQL_USERNAME }}
        DB_PGSQL_CHARSET: ${{ secrets.DB_PGSQL_CHARSET }}
        DB_PGSQL_DATABASE: ${{ secrets.DB_PGSQL_DATABASE }}
        DB_PGSQL_HOST: ${{ secrets.DB_PGSQL_HOST_SSL }}
        DB_PGSQL_SEARCHPATH: ${{ secrets.DB_PGSQL_SEARCHPATH }}
        DB_PGSQL_SSLMODE: ${{ secrets.DB_PGSQL_SSLMODE }}
        DB_PGSQL_USERNAME: ${{ secrets.DB_PGSQL_USERNAME }}
        DB_SQLITE_DATABASE: ${{ env.TinySQLitePath }}
        TOM_TESTS_ENV: ${{ vars.TOM_TESTS_ENV }}

    - name: Tom example test some commands (MySQL) 🚀
      working-directory: ${{ env.TinyORMBuildTree }}/examples/tom
      run: |
        .\tom.exe migrate:fresh --database=tinyorm_tom_mysql --no-ansi
        .\tom.exe migrate:uninstall --reset --database=tinyorm_tom_mysql --no-ansi
        .\tom.exe migrate:install --database=tinyorm_tom_mysql --no-ansi
        .\tom.exe migrate --database=tinyorm_tom_mysql --seed --no-ansi
        .\tom.exe migrate:status --database=tinyorm_tom_mysql --no-ansi
        .\tom.exe migrate:refresh --database=tinyorm_tom_mysql --seed --no-ansi
        .\tom.exe migrate:reset --database=tinyorm_tom_mysql --no-ansi
        .\tom.exe migrate:uninstall --database=tinyorm_tom_mysql --no-ansi
      env:
        DB_MYSQL_CHARSET: ${{ secrets.DB_MYSQL_CHARSET }}
        DB_MYSQL_COLLATION: ${{ secrets.DB_MYSQL_COLLATION }}
        DB_MYSQL_DATABASE: ${{ secrets.DB_MYSQL_DATABASE }}
        DB_MYSQL_HOST: ${{ secrets.DB_MYSQL_HOST_SSL }}
        DB_MYSQL_PASSWORD: ${{ secrets.DB_MYSQL_PASSWORD }}
        DB_MYSQL_SSL_CA: ${{ env.TinyMySQLDataPath }}/ca.pem
        DB_MYSQL_SSL_CERT: ${{ env.TinyMySQLDataPath }}/client-cert.pem
        DB_MYSQL_SSL_KEY: ${{ env.TinyMySQLDataPath }}/client-key.pem
        DB_MYSQL_SSL_MODE: ${{ secrets.DB_MYSQL_SSL_MODE }}
        DB_MYSQL_USERNAME: ${{ secrets.DB_MYSQL_USERNAME }}
        TOM_EXAMPLE_ENV: ${{ vars.TOM_EXAMPLE_ENV }}

    - name: Tom example test some commands (PostgreSQL) 🙌
      working-directory: ${{ env.TinyORMBuildTree }}/examples/tom
      run: |
        .\tom.exe migrate:fresh --database=tinyorm_tom_postgres --no-ansi
        .\tom.exe migrate:uninstall --reset --database=tinyorm_tom_postgres --no-ansi
        .\tom.exe migrate:install --database=tinyorm_tom_postgres --no-ansi
        .\tom.exe migrate --database=tinyorm_tom_postgres --seed --no-ansi
        .\tom.exe migrate:status --database=tinyorm_tom_postgres --no-ansi
        .\tom.exe migrate:refresh --database=tinyorm_tom_postgres --seed --no-ansi
        .\tom.exe migrate:reset --database=tinyorm_tom_postgres --no-ansi
        .\tom.exe migrate:uninstall --database=tinyorm_tom_postgres --no-ansi
      env:
        DB_PGSQL_CHARSET: ${{ secrets.DB_PGSQL_CHARSET }}
        DB_PGSQL_DATABASE: ${{ secrets.DB_PGSQL_DATABASE }}
        DB_PGSQL_HOST: ${{ secrets.DB_PGSQL_HOST_SSL }}
        DB_PGSQL_SEARCHPATH: ${{ secrets.DB_PGSQL_SEARCHPATH }}
        DB_PGSQL_SSLMODE: ${{ secrets.DB_PGSQL_SSLMODE }}
        DB_PGSQL_USERNAME: ${{ secrets.DB_PGSQL_USERNAME }}
        TOM_EXAMPLE_ENV: ${{ vars.TOM_EXAMPLE_ENV }}

    - name: Tom example test some commands (SQLite) 🏁
      working-directory: ${{ env.TinyORMBuildTree }}/examples/tom
      run: |
        .\tom.exe migrate:fresh --database=tinyorm_tom_sqlite --no-ansi
        .\tom.exe migrate:uninstall --reset --database=tinyorm_tom_sqlite --no-ansi
        .\tom.exe migrate:install --database=tinyorm_tom_sqlite --no-ansi
        .\tom.exe migrate --database=tinyorm_tom_sqlite --seed --no-ansi
        .\tom.exe migrate:status --database=tinyorm_tom_sqlite --no-ansi
        .\tom.exe migrate:refresh --database=tinyorm_tom_sqlite --seed --no-ansi
        .\tom.exe migrate:reset --database=tinyorm_tom_sqlite --no-ansi
        .\tom.exe migrate:uninstall --database=tinyorm_tom_sqlite --no-ansi
      env:
        DB_SQLITE_DATABASE: ${{ env.TinySQLitePath }}
        TOM_EXAMPLE_ENV: ${{ vars.TOM_EXAMPLE_ENV }}