docu.yml

name: Create documentation

on:
  push:
    branches-ignore:
      - 'dependabot/**'
    paths:
      - '**/*.sql'
  pull_request:
    paths:
      - '**/*.sql'

jobs:
  documentation:
    runs-on: ubuntu-22.04
    permissions:
      contents: write

    services:
      postgres:
        image: postgis/postgis:15-3.4
        env:
          POSTGRES_PASSWORD: postgres
          POSTGRES_USER: postgres
          POSTGRES_DB: postgres
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - name: Check out repository code
        uses: actions/checkout@v4
        with:
          fetch-depth: 0
          ref: main
          token: ${{ secrets.GH_PAT_SOC }}

      - name: Install apt dependencies
        run: sudo apt install postgis gnupg2
        
      - name: Load postgresql apt keys
        run: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

      - name: Add postgresql apt repository
        run: echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | sudo tee  /etc/apt/sources.list.d/pgdg.list

      - name: Update apt repository
        run: sudo apt update

      - name: Install postgresql15 postgis3
        run: sudo apt install postgresql-15-postgis-3
 
      - name: Install python3 dependencies
        run: pip3 install -r requirements.txt
        working-directory: tools

      - name: Create tables and insert data
        run: psql -U postgres -h localhost -d postgres -p 5432 < data/flensburg_stadtteile.sql
        env:
          PGPASSWORD: postgres

      - name: Create tables and insert data
        run: psql -U postgres -h localhost -d postgres -p 5432 < data/flensburg_sozialatlas.sql
        env:
          PGPASSWORD: postgres

      - name: Create meta tables and insert data
        run: psql -U postgres -h localhost -d postgres -p 5432 < data/flensburg_sozialatlas_metadaten.sql
        env:
          PGPASSWORD: postgres

      - name: Generate documentation
        run: python3 generate_documentation.py ../wiki/TABLES.md
        env:
          DB_NAME: postgres
          DB_PASS: postgres
          DB_USER: postgres
          DB_HOST: localhost
          DB_PORT: 5432
        working-directory: tools

      - name: Commit sql documentation
        run: |
          if [[ $(git status --porcelain | wc -l) -gt 0 ]]; then
            git config user.name "$(git log -n 1 --pretty=format:%an)"
            git config user.email "$(git log -n 1 --pretty=format:%ae)"
            git add wiki/TABLES.md
            git commit -m 'add automated sql documentation'
            git push origin HEAD
          fi