publish-mysql.yml

name: 'Publish: MySQL database'

on:
  schedule:
    # run every day at midnight
    - cron: '0 0 1-31 * *'

  workflow_dispatch:

env:
  DB_HOST: 127.0.0.1
  DB_USER: root
  DB_PWD: ${{ secrets.MYSQL_PASSWORD }}
  DB_DB: cfa_project_index

jobs:

  publish-mysql:
    runs-on: ubuntu-latest
    steps:

    - name: Cancel Previous Runs
      uses: styfle/cancel-workflow-action@0.7.0
      with:
        access_token: ${{ github.token }}

    - uses: actions/checkout@v2

    - name: Set up connection to Kubernetes cluster
      run: |
        test -e ~/.kube || mkdir ~/.kube
        echo "${{ secrets.MYSQL_KUBECONFIG_BASE64 }}" | base64 -d > ~/.kube/config

    - name: Open port forwarding to MySQL
      run: |
        MYSQL_POD=$(kubectl get pod -l service=mysql -o jsonpath='{.items[0].metadata.name}')
        kubectl port-forward "pods/$(kubectl get pod -l service=mysql -o jsonpath='{.items[0].metadata.name}')" 3306:3306 &

    - name: Set up virtual env
      run: |
        cd tools/mysql-loader
        python -m venv ".venv"
        source .venv/bin/activate
        pip install --upgrade pip
        pip install -r requirements.txt

    - name: Drop existing tables
      run: mysql -h "${DB_HOST}" -u "${DB_USER}" -p"${DB_PWD}" < tools/mysql-loader/drop-schema.sql

    - name: Create database tables
      run: mysql -h "${DB_HOST}" -u "${DB_USER}" -p"${DB_PWD}" < tools/mysql-loader/create-schema.sql

    - name: Load organizations
      run: |
        cd tools/mysql-loader
        source .venv/bin/activate
        python org-toml-2-mysql.py

    - name: Load projects
      run: |
        cd tools/mysql-loader
        source .venv/bin/activate
        python proj-toml-2-mysql.py

    # - uses: mxschmitt/action-tmate@v3
    #   if: failure()