update-orm.yml

name: Check if ORM update is needed

on:
  workflow_call:
    inputs:
      DATABASE_SCHEMA:
        required: true
        type: string

jobs:
  orm-update:
    name: Update ORM
    runs-on: ubuntu-latest
    permissions:
      contents: write
      pull-requests: write

    services:
      mariadb:
        image: mariadb:10.6
        env:
          MARIADB_DATABASE: ispybtest
          MARIADB_ROOT_PASSWORD: mariadb_root_pwd
        ports:
          - 3306:3306
        options: --health-cmd="healthcheck.sh --connect --innodb_initialized" --health-interval=10s --health-timeout=5s --health-retries=3
    outputs:
      needsUpdate: ${{ steps.checkUpdate.outputs.needsUpdate }}

    steps:
      - uses: actions/checkout@v4
      - name: Use Python 3.10
        uses: actions/setup-python@v5
        with:
          python-version: "3.10"

      - name: Check if update is required
        id: checkUpdate
        run: |
          set -eu
          CURRENT_VERSION=$(grep __schema_version__ _auto_db_schema.py | cut -d'"' -f2)
          if [ ${CURRENT_VERSION} = ${{ inputs.DATABASE_SCHEMA }} ]; then
            echo "##[section]ORM is up to date (${CURRENT_VERSION})"
            echo "needsUpdate=false" >> $GITHUB_OUTPUT
          else
            echo "##[warning]ORM needs to be updated (${CURRENT_VERSION} -> ${{ inputs.DATABASE_SCHEMA }})."
            echo "needsUpdate=true" >> $GITHUB_OUTPUT
          fi
        working-directory: ./src/ispyb/sqlalchemy

      - name: Install sqlacodegen
        if: ${{ steps.checkUpdate.outputs.needsUpdate == 'true' }}
        run: |
          set -eux
          pip install -r requirements_orm.txt
          pip install pre-commit

      - name: Get database schema
        if: ${{ steps.checkUpdate.outputs.needsUpdate == 'true' }}
        uses: actions/download-artifact@v4
        with:
          name: package-distributions
          path: dist/

      - uses: shogo82148/actions-setup-mysql@v1
        if: ${{ steps.checkUpdate.outputs.needsUpdate == 'true' }}
        with:
          distribution: 'mariadb'
          mysql-version: '10.6'
          auto-start: false

      - name: Set up reference database schema
        if: ${{ steps.checkUpdate.outputs.needsUpdate == 'true' }}
        run: |
          set -eu
          cat > .my.cnf <<EOF
          [client]
          user = root
          password = mariadb_root_pwd
          host = 127.0.0.1
          port = 3306
          database = ispybtest
          EOF

          tar xfz "dist/ispyb-database.tar.gz"
          patch -p1 < "src/ispyb/sqlalchemy/sqlacodegen.patch"
      
          printf 'Waiting for MySQL database to accept connections'
          until mariadb --defaults-file=.my.cnf -e "SHOW DATABASES" >/dev/null; do printf '.'; sleep 0.5; done
          printf '\n'
      
          echo "Installing reference database"
          ./build.sh
      
          echo
          echo "Installed tables:"
          mariadb --defaults-file=.my.cnf -D ispyb_build -e "SHOW TABLES"

      - name: Generate ORM
        if: ${{ steps.checkUpdate.outputs.needsUpdate == 'true' }}
        run: |
          set -eux
          sqlacodegen mysql+mysqlconnector://root:mariadb_root_pwd@127.0.0.1/ispyb_build --noinflect --nojoined --outfile _auto_db_schema.py.in
          # This code produces false positives due to non-deterministic ordering.
          # Add an identifier to the file, and only update/PR the changes when the
          # identifier indicates the file is out of date.
          cat <(echo "__schema_version__ = \"${{ inputs.DATABASE_SCHEMA }}\"") _auto_db_schema.py.in > _auto_db_schema.py
          rm _auto_db_schema.py.in
          pre-commit run --files _auto_db_schema.py || echo pre-commit exited with non-zero return code
        working-directory: ./src/ispyb/sqlalchemy

      - name: Show differences
        if: ${{ steps.checkUpdate.outputs.needsUpdate == 'true' }}
        run: git diff

      - name: Store artifact
        if: ${{ steps.checkUpdate.outputs.needsUpdate == 'true' }}
        uses: actions/upload-artifact@v4
        with:
          name: ORM
          path: ./src/ispyb/sqlalchemy/_auto_db_schema.py

      - name: Create ORM pull request
        if: ${{ steps.checkUpdate.outputs.needsUpdate == 'true' }}
        run: .github/workflows/scripts/create-orm-update-pull-request
        env:
          GITHUB_TOKEN: ${{ github.token }}
          DATABASE_SCHEMA: ${{ inputs.DATABASE_SCHEMA }}
          BRANCH: ${{ github.ref_name }}