database_ud.yml

name: Create Giswater Schema SAMPLE (UD)

on:
  push:
    branches: [dev-3.6]
  pull_request:
    branches: [dev-3.6]

jobs:
  setup-and-test-db:
    runs-on: ubuntu-latest

    steps:
      - name: Checkout repository
        uses: actions/checkout@v2

      - name: Set up Python
        uses: actions/setup-python@v2
        with:
          python-version: "3.x"

      - name: Install Python dependencies
        run: |
          python -m pip install --upgrade pip
          pip install -r test/requirements.txt

      - name: Setup PostgreSQL and PostGIS for Linux/macOS/Windows
        uses: nyurik/action-setup-postgis@v2.1
        with:
          database: giswater_test_db

      - name: Install pgrouting & postgis_raster
        env:
          PGPASSWORD: postgres
        run: |
          sudo apt-get install postgresql-14-pgrouting
          psql -h localhost -U postgres -d giswater_test_db -c 'CREATE EXTENSION pgrouting;'
          psql -h localhost -U postgres -d giswater_test_db -c 'CREATE EXTENSION postgis_raster;'

      - name: Install pgTAP from package
        env:
          PGPASSWORD: postgres
        run: |
          sudo apt-get install -y postgresql-14-pgtap
          psql -h localhost -U postgres -d giswater_test_db -c 'CREATE EXTENSION pgtap;'

      - name: Replace variables in SQL files
        run: python test/replace_vars.py ud

      - name: Create Sample Schema
        env:
          PGPASSWORD: postgres
        run: python test/execute_sql_files.py ud

      - name: Run SQL Tests with pgTAP and pg_prove (PL/SQL)
        env:
          PGPASSWORD: postgres
        run: |
          pg_prove -h localhost -U postgres -d giswater_test_db test/plsql/ud/*.sql

      - name: Run SQL Tests with pgTAP and pg_prove (UPSERT)
        env:
          PGPASSWORD: postgres
        run: |
          pg_prove -h localhost -U postgres -d giswater_test_db test/upsert/ud/*.sql

      - name: Run SQL Tests with pgTAP and pg_prove (STRUCTURE)
        env:
          PGPASSWORD: postgres
        run: |
          pg_prove -h localhost -U postgres -d giswater_test_db test/structure/ud/*.sql