fix(workflow): install postgis+pgrouting & verify database #3
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: Create Giswater Schema (WS) | |
| on: | |
| push: | |
| branches: [ dev-3.6 ] | |
| pull_request: | |
| branches: [ dev-3.6 ] | |
| jobs: | |
| setup-and-test-db: | |
| runs-on: ubuntu-latest | |
| services: | |
| postgres: | |
| image: postgres:latest | |
| ports: | |
| - 5432:5432 | |
| env: | |
| POSTGRES_DB: giswater_test_db | |
| POSTGRES_USER: postgres | |
| POSTGRES_PASSWORD: postgres | |
| options: >- | |
| --health-cmd "pg_isready -U $${POSTGRES_USER} -d $${POSTGRES_DB}" | |
| --health-interval 10s | |
| --health-timeout 5s | |
| --health-retries 5 | |
| 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 .github/workflows/requirements.txt | |
| - name: Wait for PostgreSQL to be ready | |
| run: | | |
| echo "Waiting for PostgreSQL to be ready..." | |
| while ! pg_isready -h localhost -p 5432 -U postgres; do | |
| sleep 1 | |
| done | |
| - name: Install PostGIS and pgRouting | |
| run: | | |
| # Install PostGIS and pgRouting extensions | |
| apt-get update | |
| apt-get install -y postgis postgresql-15-postgis-3 postgresql-15-pgrouting | |
| # Enable PostGIS and pgRouting extensions in the database | |
| psql -h localhost -U postgres -d giswater_test_db -c "CREATE EXTENSION postgis;" | |
| psql -h localhost -U postgres -d giswater_test_db -c "CREATE EXTENSION pgrouting;" | |
| - name: Replace variables in SQL files | |
| run: python .github/workflows/replace_vars.py | |
| - name: Create sample schema | |
| env: | |
| PGPASSWORD: postgres | |
| run: | | |
| # Define the root directories to process | |
| root_directories=("utils" "ws" "i18n") | |
| # Process each root directory and its subdirectories | |
| for root_dir in "${root_directories[@]}"; do | |
| echo "Processing root directory: $root_dir" | |
| find "$root_dir" -type f -name "*.sql" | sort | while read -r file; do | |
| echo "Executing $file..." | |
| psql -h localhost -U postgres -d giswater_test_db -f "$file" | |
| done | |
| done | |
| # Define the base updates directory | |
| updates_dir="updates/36" | |
| # Check if the updates directory exists | |
| if [ -d "$updates_dir" ]; then | |
| # Process "utils" and "ws" subdirectories within updates_dir | |
| find "$updates_dir" -type d -name "utils" -o -name "ws" | sort | while read -r subdir; do | |
| echo "Processing directory: $subdir" | |
| find "$subdir" -type f -name "*.sql" | sort | while read -r file; do | |
| echo "Executing $file..." | |
| psql -h localhost -U postgres -d giswater_test_db -f "$file" | |
| done | |
| done | |
| else | |
| echo "Directory $updates_dir does not exist" | |
| fi | |
| - name: Verify Database | |
| env: | |
| PGPASSWORD: postgres | |
| run: | | |
| # Check the presence of tables | |
| tables=$(psql -h localhost -U postgres -d giswater_test_db -t -c '\dt') | |
| if [ -z "$tables" ]; then | |
| echo "No tables found in the database." | |
| exit 1 | |
| else | |
| echo "Tables found:" | |
| echo "$tables" | |
| fi | |
| # Check PostGIS version | |
| postgis_version=$(psql -h localhost -U postgres -d giswater_test_db -t -c "SELECT postgis_full_version();") | |
| if [ -z "$postgis_version" ]; then | |
| echo "PostGIS extension not found or not installed correctly." | |
| exit 1 | |
| else | |
| echo "PostGIS version:" | |
| echo "$postgis_version" | |
| fi | |
| # Check pgRouting version | |
| pgrouting_version=$(psql -h localhost -U postgres -d giswater_test_db -t -c "SELECT * FROM pgr_version();") | |
| if [ -z "$pgrouting_version" ]; then | |
| echo "pgRouting extension not found or not installed correctly." | |
| exit 1 | |
| else | |
| echo "pgRouting version:" | |
| echo "$pgrouting_version" | |
| fi |