sqlmlutils/.github/workflows/SQL2022.yml

# This workflow uses actions that are not certified by GitHub.
# They are provided by a third-party and are governed by
# separate terms of service, privacy policy, and support
# documentation.
#
# See https://github.com/r-lib/actions/tree/master/examples#readme for
# additional example workflows available for the R community.

name: R 4.2 - Python 3.10

on:
  push:
    branches: [ master ]
  pull_request:
    branches: [ master ]
  workflow_dispatch:

jobs:
  SQLServer2022:
    runs-on: windows-2022
    
    env:
      # Define CI to skip some test case.
      CI: True
      r-version: "4.2.0"
      python-version: "3.10.2"
      sql-platform: "box"


    defaults:
      run:
        shell: cmd

    steps:
      # Set password in Github env but don't display output.
      # Note for github action you need to use double %, when testing in a local VM
      # %i would be the correct format.
      - name: Generate password
        run: |
          setlocal enabledelayedexpansion
          set password=%random%%random%%random%qaZ~@
          for /f "delims=" %%i in ('echo !password!') do echo dbPassword=%%i >> "%GITHUB_ENV%"          

      # /SAPWD=%dbPassword% /SECURITYMODE=SQL /TCPENABLED=1 allows connecting using localhost without windows account.
      # /FEATURES=SQLEngine,ADVANCEDANALYTICS are the machine learning services options.
      - name: Install SQL Server 2022
        run: | 
          set CurrentDir=%cd%
          choco install sql-server-2022 -y -params "'/SAPWD=%dbPassword% /SECURITYMODE=SQL /TCPENABLED=1 /INDICATEPROGRESS /ACTION=Install /FEATURES=SQLEngine,ADVANCEDANALYTICS /INSTANCENAME=MSSQLSERVER /IACCEPTSQLSERVERLICENSETERMS'"
        shell: cmd

      - name: Install sqlcmd
        run: choco install sqlcmd
        shell: cmd

      # Saves the chocolately logs which will likely hold the errors needed to debug a failed installation of SQL Server 2019.
      - name: Save Logs as artifact
        if: always()
        uses: actions/upload-artifact@v4
        with:
          name: Chocolatey Logs
          path: C:\ProgramData\chocolatey\logs\chocolatey2022.log
          retention-days: 20

      - name: Check Connectivity to SQL Database
        run: |
          sqlcmd -S localhost -U SA -P %dbPassword% -d Master -l 5 -Q "SELECT @@VERSION"          
        shell: cmd

      - name: Checkout Branch
        uses: actions/checkout@v4

      - name: Move AirlineTestDB.bak to correct file location for backup command
        run: copy "AirlineTestDB.bak" "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AirlineTestDB.bak"

      - name: Restore AirlineTestDB
        run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q "USE [master]; RESTORE DATABASE [AirlineTestDB] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AirlineTestDB.bak' WITH  FILE = 1,  MOVE N'AirlineTestDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AirlineTestDB_Primary.mdf',  MOVE N'AirlineTestDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AirlineTestDB_Primary.ldf',  NOUNLOAD,  STATS = 5;"

      - name: Create Logins for Test DBs
        run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q "
          CREATE LOGIN AirlineUser WITH PASSWORD = '%dbPassword%', CHECK_POLICY=OFF, CHECK_EXPIRATION = OFF, DEFAULT_DATABASE=AirlineTestDB;
          CREATE LOGIN AirlineUserdbowner WITH PASSWORD = '%dbPassword%', CHECK_POLICY=OFF, CHECK_EXPIRATION = OFF, DEFAULT_DATABASE=AirlineTestDB;"
      
      - name: Alter AirlineUser
        run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q "
          USE AirlineTestDB;
          ALTER USER [AirlineUser] WITH LOGIN=[AirlineUser]"
      
      - name: Alter AirlineUserdbowner
        run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q "
          USE AirlineTestDB;
          ALTER USER [AirlineUserdbowner] WITH LOGIN=[AirlineUserdbowner]"

      - name: Alter Authorization
        run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q "
          USE AirlineTestDB;
          ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [AirlineUserdbowner]"

      # https://learn.microsoft.com/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16#setup-r-support
      - name: Install R for MLS
        run: |
          curl -L -o R-4.2.0-win.exe https://cloud.r-project.org/bin/windows/base/old/4.2.0/R-4.2.0-win.exe
          R-4.2.0-win.exe /VERYSILENT /DIR=C:\MLS\R          

      - name: Install CompatibilityAPI and RevoScaleR after installing their dependencies (iterators, foreach, R6, and jsonlite).
        run: |
          C:\MLS\R\bin\Rscript.exe -e "install.packages(c('iterators', 'foreach', 'R6', 'jsonlite'), repos='https://cloud.r-project.org/', lib='C:\\MLS\\R\\library')"
          C:\MLS\R\bin\Rscript.exe -e "install.packages('https://aka.ms/sqlml/r4.2/windows/CompatibilityAPI_1.1.0.zip', repos=NULL, lib='C:\\MLS\\R\\library')"
          C:\MLS\R\bin\Rscript.exe -e "install.packages('https://aka.ms/sqlml/r4.2/windows/RevoScaleR_10.0.1.zip', repos=NULL, lib='C:\\MLS\\R\\library')"          

      - name: Configure the R runtime installed for MLS with SQL Server
        run: C:\MLS\R\library\RevoScaleR\rxLibs\x64\RegisterRext.exe /configure /rhome:"C:\MLS\R" /instance:"MSSQLSERVER"

      - name: Set up Python ${{ env.python-version }}
        uses: actions/setup-python@v5
        with:
          python-version: ${{ env.python-version }}

      - name: Install revoscalepy and dependencies
        working-directory: ${{ env.Python3_ROOT_DIR }}
        run: |
          python -m pip install -t "${{ env.Python3_ROOT_DIR }}\Lib\site-packages" dill numpy==1.22.0 pandas patsy python-dateutil
          python -m pip install -t "${{ env.Python3_ROOT_DIR }}\Lib\site-packages" https://aka.ms/sqlml/python3.10/windows/revoscalepy-10.0.1-py3-none-any.whl          

      - name: Grant READ/EXECUTE access to installed libraries
        run: |
          icacls "${{ env.Python3_ROOT_DIR }}\Lib\site-packages" /grant "NT Service\MSSQLLAUNCHPAD":(OI)(CI)RX /T
          icacls "${{ env.Python3_ROOT_DIR }}\Lib\site-packages" /grant *S-1-15-2-1:(OI)(CI)RX /T          

      - name: Configure the Python runtime installed for MLS with SQL Server
        working-directory: ${{ env.Python3_ROOT_DIR }}\Lib\site-packages\revoscalepy\rxLibs
        run: .\RegisterRext.exe /configure /pythonhome:"${{ env.Python3_ROOT_DIR }}" /instance:"MSSQLSERVER"

      - name: Enable External Scripts
        run: sqlcmd -S localhost -U SA -P %dbPassword% -Q "EXEC sp_configure  'external scripts enabled', 1;"

      - name: Reconfigure
        run: sqlcmd -S localhost -U SA -P %dbPassword% -Q "RECONFIGURE WITH OVERRIDE;"  

      - name: Restart the Service
        run: |
          sqlcmd -S localhost -U SA -P %dbPassword% -Q "SHUTDOWN WITH NOWAIT"
          timeout /t 5 /nobreak
          net start "MSSQLSERVER"          

      - name: Execute sp_execute_external_script for R
        run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q "
          EXEC sp_execute_external_script  @language =N'R',
          @script=N'
          OutputDataSet <- InputDataSet;
          ',
          @input_data_1 =N'SELECT 1 AS hello'
          WITH RESULT SETS (([hello] int not null));"

      - name: Execute sp_execute_external_script for Python
        run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q "
          EXEC sp_execute_external_script  @language =N'Python',
          @script=N'OutputDataSet = InputDataSet;',
          @input_data_1 =N'SELECT 1 AS hello' WITH RESULT SETS (([hello] int not null));"

      - name: Set up R ${{ env.r-version }} Runtime
        uses: r-lib/actions/setup-r@v2
        with:
          r-version: ${{ env.r-version }}

      - name: Install R Package Dependencies
        uses: r-lib/actions/setup-r-dependencies@v2
        with:
          cache-version: 2
          working-directory: ./R
          extra-packages:
            #Retrieves most recent odbc pkg from cran to avoid errors seen in older versions.
            #Current version needs R >=3.6 and is currently failing
            cran::odbc
            cran::xml2
            rcmdcheck

      - uses: r-lib/actions/check-r-package@v2
        with:
          working-directory: ./R
        env:
          PASSWORD_AIRLINE_USER: "${{ env.dbPassword }}"
          PASSWORD_AIRLINE_USER_DBOWNER: "${{ env.dbPassword }}"

      - name: Install Python dependencies
        working-directory: ./Python
        run: |
          python -m pip install --upgrade pip
          python -m pip install flake8 pytest
          pip install -r requirements.txt          

      - name: Build Python Package
        working-directory: ./Python
        run: ./buildandinstall.cmd

      - name: Run pytest
        working-directory: ./Python/tests
        run: |
          pytest          
        env:
          USER: "AirlineUserdbowner"
          PASSWORD: "${{ env.dbPassword }}"
          PASSWORD_AIRLINE_USER: "${{ env.dbPassword }}"
        continue-on-error: true