run_db_trigger.yml

name: Reusable workflow to build MySQL database images 

on:
  workflow_call:
    inputs:
      be_url:
        description: 'Backend URL'
        required: false
        type: string
      fe_url:
        description: 'Frontend URL'
        required: false
        type: string
      project:
        description: 'Project'
        required: false
        type: string
      s3_uri:
        description: 'S3 URI'
        required: true
        type: string
      runner:
        type: string
        required: false
        default: ubuntu-latest

    secrets:
      AWS_ACCESS_KEY_ID:
        required: true
      AWS_SECRET_ACCESS_KEY:
        required: true
      REGISTRY_TOKEN:
        required: true
      REGISTRY_USER:
        required: true
      SLACK_WEBHOOK_URL:
        required: true
      DB_IMAGE_MARIADB_ROOT_PASSWORD:
        required: true

env:
  REGISTRY: ghcr.io
    
jobs:
  export-db:
    runs-on: ${{ inputs.runner }}
    permissions:
      contents: read
      packages: write
      id-token: write
    env:
      REGISTRY: ghcr.io
      IMAGE_NAME: ${{ github.repository }}/mariadb
      TMP_DATA_DIR: data
      DB_FILE: db.sql
    steps:
      - name: Start the database container
        id: container
        run: |
          CONTAINER_ID=$(docker run -d ghcr.io/dpc-sdp/bay/mariadb:6.x)
          timeout 60s docker exec --user 1000 -i "${CONTAINER_ID}" sh -c "until nc -z localhost 3306; do sleep 1; echo -n .; done; echo"
          echo "id=$CONTAINER_ID" >> $GITHUB_OUTPUT

      - name: Configure AWS actions
        uses: aws-actions/configure-aws-credentials@v4
        with:
          aws-access-key-id: ${{ secrets.AWS_ACCESS_KEY_ID }}
          aws-secret-access-key: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
          aws-region: ap-southeast-2

      - name: Download the database
        run: aws s3 cp ${{ inputs.s3_uri }} "${{ env.DB_FILE }}"

      - name: Import the databse into the running container
        run: cat "${{ env.DB_FILE }}" | docker exec -i "${{ steps.container.outputs.id }}" /usr/bin/mysql

      - name: Verify database is available
        run: |
          if docker exec --user 1000 "${{ steps.container.outputs.id }}" /usr/bin/mysql -e "show tables;" | grep -q users; then
            echo "Database import successfully"
          else
            echo "Database was not imported successfully"
            exit 1
          fi

      - name: Update permissions of database files
        run: docker exec "${{ steps.container.outputs.id }}" bash -c "chown -R mysql /var/lib/mysql && /bin/fix-permissions /var/lib/mysql" || true
        continue-on-error: true

      - name: Copy database files out of the intemediary container
        run: |
          mkdir -p "${{ env.TMP_DATA_DIR }}"
          docker cp "${{ steps.container.outputs.id }}":/var/lib/mysql/. "${{ env.TMP_DATA_DIR }}/"
          ls -la "${{ env.TMP_DATA_DIR }}"

      - name: Verify the data directory
        run: |
          if [ -d "${{ env.TMP_DATA_DIR }}/mysql" ]; then
            echo "Files exist on the host"
          else
            ls -la "${{ env.TMP_DATA_DIR }}"
            exit 1
          fi

      - name: Gracefully shutdown the intermediary container
        run: docker exec -i "${{ steps.container.outputs.id }}" mysqladmin -u root -p${{ secrets.DB_IMAGE_MARIADB_ROOT_PASSWORD }} shutdown
        continue-on-error: true

      - name: Download the DB image dockerfile
        run: curl -o Dockerfile.seed https://raw.githubusercontent.com/dpc-sdp/github-actions/main/.docker/Dockerfile.seed

      # Login against a Docker registry except on PR
      # https://github.com/docker/login-action
      - name: Log into registry ghcr.io
        uses: docker/login-action@v3
        with:
          registry: ${{ env.REGISTRY }}
          username: ${{ secrets.REGISTRY_USER }}
          password: ${{ secrets.REGISTRY_TOKEN }}

      # Extract metadata (tags, labels) for Docker
      # https://github.com/docker/metadata-action
      - name: Extract Docker metadata
        id: meta
        uses: docker/metadata-action@v5
        with:
          images: ${{ env.REGISTRY }}/${{ env.IMAGE_NAME }}
          tags: |
            type=ref,event=branch
          

      - name: Set up QEMU
        uses: docker/setup-qemu-action@v3
      - name: Set up Docker Buildx
        uses: docker/setup-buildx-action@v3

      - name: Build and push the database image
        uses: docker/build-push-action@v5
        with:
          file: Dockerfile.seed
          context: .
          build-args: DATA_DIR=${{ env.TMP_DATA_DIR }}
          tags: ${{ env.REGISTRY }}/${{ env.IMAGE_NAME }}:latest
          platforms: linux/amd64,linux/arm64
          labels: ${{ steps.meta.outputs.labels }}
          annotations: ${{ steps.meta.outputs.annotations }}
          push: true
  
  notify_slack:
    runs-on: ubuntu-latest
    needs: [export-db] 
    if: ${{ always() }}  
    steps:
      - name: Send Notification to Slack on Failure
        if: ${{ needs.export-db.result == 'failure' }} 
        uses: 8398a7/action-slack@v3
        with:
          status: ${{ needs.export-db.result }}
          text: 'Attention: The db-trigger workflow has FAILED in project "${{ inputs.project }}" for repo "${{ github.repository }}"!'
          fields: 'project: ${{ inputs.project }}, repo: ${ { github.repository }}'
          mention: 'here'
          author_name: 'DPC TEAM'
          channel: '#dpc-events'
          color: 'danger'  
          if_mention: 'always'
        env:
          SLACK_WEBHOOK_URL: ${{ secrets.SLACK_WEBHOOK_URL }}