test: re-fix previously broken sql file #23
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: Setup PostgreSQL and PostGIS for Linux/macOS/Windows | |
| uses: nyurik/[email protected] | |
| 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: Replace variables in SQL files | |
| run: python .github/workflows/replace_vars.py | |
| - name: Create sample schema | |
| env: | |
| PGPASSWORD: postgres | |
| run: | | |
| # set -e # Exit immediately if a command exits with a non-zero status | |
| # Define the root directories to process | |
| root_directories=("utils" "ws" "i18n/en_US") | |
| # Function to execute SQL files and handle errors | |
| execute_sql() { | |
| local file=$1 | |
| echo "Executing $file..." | |
| # Check if the file path contains "fct", "ftrg", or "trg" | |
| if [[ "$file" != *"fct"* && "$file" != *"ftrg"* && "$file" != *"trg"* ]]; then | |
| # If not, include the -v ON_ERROR_STOP=1 option | |
| output=$(psql -v ON_ERROR_STOP=1 -h localhost -U postgres -d giswater_test_db -f "$file" 2>&1) | |
| else | |
| # Otherwise, do not include the option | |
| output=$(psql -h localhost -U postgres -d giswater_test_db -f "$file" 2>&1) | |
| fi | |
| echo "Output: $output" | |
| # Check the exit status of the psql command | |
| if [ $? -ne 0 ]; then | |
| # Print the captured output | |
| echo "Error executing $file" | |
| echo "Output:" | |
| echo "$output" | |
| exit 1 | |
| fi | |
| } | |
| # 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 | grep -v "ud_" | while read -r file; do | |
| execute_sql "$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 | grep -v "ud_" | while read -r file; do | |
| execute_sql "$file" | |
| done | |
| done | |
| else | |
| echo "Directory $updates_dir does not exist" | |
| fi | |
| # child views | |
| execute_sql "childviews/en_US/ws_schema_model.sql" | |
| # execute lastprocess | |
| psql -h localhost -U postgres -d giswater_test_db -c "SELECT ws_36.gw_fct_admin_schema_lastprocess('{\"client\":{\"device\":4, \"lang\":\"en_US\"}, \"data\":{\"isNewProject\":\"TRUE\", \"gwVersion\":\"3.6.012\", \"projectType\":\"WS\", \"epsg\":25831, \"descript\":\"ws_36\", \"name\":\"ws_36\", \"author\":\"postgres\", \"date\":\"29-07-2024\"}}');" | |
| # Define the example directory | |
| updates_dir="example/user/ws" | |
| # Check if the updates directory exists | |
| if [ -d "$updates_dir" ]; then | |
| # Process "utils" and "ws" subdirectories within updates_dir | |
| find "$updates_dir" -type d | sort | while read -r subdir; do | |
| echo "Processing directory: $subdir" | |
| find "$subdir" -type f -name "*.sql" | sort | grep -v "ud_" | while read -r file; do | |
| execute_sql "$file" | |
| done | |
| done | |
| else | |
| echo "Directory $updates_dir does not exist" | |
| fi | |
| - name: Verify Database | |
| env: | |
| PGPASSWORD: postgres | |
| run: | | |
| # 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 | |
| # Check features | |
| arcs=$(psql -h localhost -U postgres -d giswater_test_db -t -c "SELECT count(*) FROM ws_36.v_edit_arc;") | |
| if [ -z "$arcs" ]; then | |
| echo "No arcs found in the v_edit_arc table." | |
| exit 1 | |
| else | |
| echo "Arcs found:" | |
| echo "$tables" | |
| fi |