sql-load.yml

# This workflow will Load SQL scripts to DB
name: Load SQL scripts to DB

env:
  APP_NAME: petcliaca
  LOCATION: francecentral
  RG_APP: rg-iac-aca-petclinic-mic-srv # RG where to deploy the other Azure services: ASA, MySQL, etc.
  
  MYSQL_SERVER_NAME: petcliaca
  MYSQL_DB_NAME: petclinic
  MYSQL_ADM_USR: mys_adm
  MYSQL_TIME_ZONE: Europe/Paris
  MYSQL_CHARACTER_SET: utf8
  MYSQL_PORT: 3306

  # ==== Secrets ====

  MYSQL_ADM_PWD: ${{ secrets.SPRING_DATASOURCE_PASSWORD }}
  AZURE_CLIENT_ID: ${{ secrets.AZURE_CLIENT_ID }}
  AZURE_TENANT_ID: ${{ secrets.AZURE_TENANT_ID }}
  AZURE_SUBSCRIPTION_ID: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

  # ==== Versions ====
  AZ_CLI_VERSION: 2.45.0

on:
  workflow_dispatch:
  workflow_call:

# required for https://learn.microsoft.com/en-us/azure/developer/github/connect-from-azure?tabs=azure-cli%2Clinux#set-up-azure-login-with-openid-connect-authentication
permissions:
  id-token: write
  contents: read
    
jobs:

  sql-load:
    runs-on: ubuntu-latest
            
    steps:
    - name: Login with GHA Runner SP
      uses: azure/login@v1 # fails https://github.com/marketplace/actions/azure-login
      with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

    - name: Checkout
      uses: actions/checkout@v3 # https://github.com/actions/checkout

    - name: Configure DB
      run: |

            az mysql flexible-server parameter set --name time_zone --value ${{ env.MYSQL_TIME_ZONE }} \
            -s ${{ env.MYSQL_SERVER_NAME }} -g ${{ env.RG_APP }}

            # https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html
            az mysql flexible-server parameter set --name character_set_server --value ${{ env.MYSQL_CHARACTER_SET }} \
            -g ${{ env.RG_APP }} -s ${{ env.MYSQL_SERVER_NAME }}

            LOCAL_IP=$(curl whatismyip.akamai.com)
            az mysql flexible-server firewall-rule create -g ${{ env.RG_APP }} -n ${{ env.MYSQL_SERVER_NAME }} --rule-name gha \
            --start-ip-address $LOCAL_IP --end-ip-address $LOCAL_IP

            # https://github.com/actions/runner-images/blob/main/images/linux/Ubuntu2204-Readme.md
            #sudo apt install mysql-client-core-8.0

            # https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-connection-using-parameters.html

            mysql -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} --execute 'CREATE DATABASE IF NOT EXISTS ${{ env.MYSQL_DB_NAME }};'

            echo Init Vets
            mysql ${{ env.MYSQL_DB_NAME }} -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} < spring-petclinic-vets-service/src/main/resources/db/mysql/schema.sql
            mysql ${{ env.MYSQL_DB_NAME }} -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} < spring-petclinic-vets-service/src/main/resources/db/mysql/data.sql

            echo Init Customers
            mysql ${{ env.MYSQL_DB_NAME }} -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} < spring-petclinic-customers-service/src/main/resources/db/mysql/schema.sql
            mysql ${{ env.MYSQL_DB_NAME }} -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} < spring-petclinic-customers-service/src/main/resources/db/mysql/data.sql

            echo Init visits
            mysql ${{ env.MYSQL_DB_NAME }} -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} < spring-petclinic-visits-service/src/main/resources/db/mysql/schema.sql
            mysql ${{ env.MYSQL_DB_NAME }} -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} < spring-petclinic-visits-service/src/main/resources/db/mysql/data.sql

      shell: bash

    - name: Disable local IP access to the DB
      if: ${{ always() }}
      run: |
            az mysql flexible-server firewall-rule delete -g ${{ env.RG_APP }} -n ${{ env.MYSQL_SERVER_NAME }} --rule-name gha --yes
      shell: bash

    # security hardening for self-hosted agents: https://github.com/marketplace/actions/azure-login
    # https://docs.github.com/en/actions/security-guides/security-hardening-for-github-actions#hardening-for-self-hosted-runners
    # if the runner is self-hosted which is not github provided it is recommended to manually logout at the end of the workflow as shown below.
    - name: Azure Logout security hardening
      run: |
          az logout
          az cache purge
          az account clear
      shell: bash