Merge pull request #41 from tt-rss/feature/validate-sql-schema-workflow

Add a 'Validate SQL' workflow

1 files changed, 144 insertions, 0 deletions

diff --git a/.github/workflows/validate-sql.yml b/.github/workflows/validate-sql.yml
new file mode 100644
index 000000000..470781385
--- /dev/null
+++ b/.github/workflows/validate-sql.yml
@@ -0,0 +1,144 @@
+name: Validate SQL
+
+on:
+  pull_request:
+    paths:
+      - 'sql/pgsql/schema.sql'
+      - 'sql/pgsql/migrations/*.sql'
+      - '.github/workflows/validate-sql.yml'
+  # Allow manual triggering
+  workflow_dispatch:
+  # Allow other workflows (e.g. Publish) to invoke this one.
+  workflow_call:
+
+
+permissions:
+  contents: read
+
+
+concurrency:
+  group: validate-sql-${{ github.ref }}
+  cancel-in-progress: true
+
+
+jobs:
+  validate-base-schema:
+    name: Validate base schema execution
+    runs-on: ubuntu-latest
+    timeout-minutes: 10
+
+    env:
+      POSTGRES_VERSION: '15' # Keep in sync with service version
+      PGPASSWORD: postgres
+      PGHOST: localhost
+      PGPORT: 5432
+      PGUSER: postgres
+      PGDATABASE: ttrss_validate
+
+    services:
+      postgres:
+        image: postgres:15-alpine
+        env:
+          POSTGRES_PASSWORD: postgres
+          POSTGRES_DB: ttrss_validate
+        options: >-
+          --health-cmd pg_isready
+          --health-interval 10s
+          --health-timeout 5s
+          --health-retries 5
+        ports:
+          - 5432:5432
+
+    steps:
+      - name: Checkout code
+        uses: actions/checkout@v4
+
+      - name: Wait for PostgreSQL
+        run: |
+          until pg_isready; do
+            echo 'Waiting for PostgreSQL...'
+            sleep 2
+          done
+
+      - name: Validate base schema execution
+        run: |
+          echo "Executing schema.sql against PostgreSQL ${POSTGRES_VERSION}..."
+          # Run the schema script with error checking - will fail on any SQL errors
+          if psql -v ON_ERROR_STOP=1 -f sql/pgsql/schema.sql; then
+            echo '✅ Base schema executed successfully'
+          else
+            echo '❌ Base schema execution failed'
+            exit 1
+          fi
+
+  validate-migration-files:
+    name: Validate migration files
+    runs-on: ubuntu-latest
+    timeout-minutes: 5
+
+    steps:
+      - name: Checkout code
+        uses: actions/checkout@v4
+
+      - name: Validate migration files
+        run: |
+          # Basic validation of migration files
+          # Note: These are incremental updates for older database states.
+          # Comment-only files are allowed, but empty/whitespace-only files are treated as errors
+
+          migration_count=0
+          sql_files=0
+          empty_files=0
+          comment_only_files=0
+          failed_count=0
+
+          echo 'Validating migration files...'
+          for migration in sql/pgsql/migrations/*.sql; do
+            if [ -f "$migration" ]; then
+              migration_count=$((migration_count + 1))
+              migration_name=$(basename "$migration")
+
+              # Check if file is empty or contains only whitespace
+              if [ ! -s "$migration" ] || ! grep -q '[^[:space:]]' "$migration"; then
+                # File is empty or whitespace-only - treat as error
+                echo "❌ $migration_name (empty or whitespace-only)"
+                empty_files=$((empty_files + 1))
+                failed_count=$((failed_count + 1))
+              elif grep -q ';' "$migration"; then
+                # Check if semicolons are only in comments
+                if grep -v '^\s*--' "$migration" | grep -q ';'; then
+                  echo "✅ $migration_name (contains SQL)"
+                  sql_files=$((sql_files + 1))
+                else
+                  echo "✅ $migration_name (comments only - no changes)"
+                  comment_only_files=$((comment_only_files + 1))
+                fi
+              else
+                # File has content but no semicolons - check if it's only comments
+                if grep -v '^\s*--' "$migration" | grep -q '[^[:space:]]'; then
+                  # File has non-comment, non-whitespace content without semicolons - invalid
+                  echo "❌ $migration_name (contains content without semicolons - invalid SQL)"
+                  empty_files=$((empty_files + 1))
+                  failed_count=$((failed_count + 1))
+                else
+                  # File only has comments or whitespace after removing comment lines
+                  echo "✅ $migration_name (comments only - no changes)"
+                  comment_only_files=$((comment_only_files + 1))
+                fi
+              fi
+            fi
+          done
+
+          echo ''
+          echo 'Migration file validation complete:'
+          echo "  Total files: $migration_count"
+          echo "  With SQL statements: $sql_files"
+          echo "  Comments only: $comment_only_files"
+          echo "  Empty/whitespace-only: $empty_files"
+
+          if [ $failed_count -gt 0 ]; then
+            echo "❌ $failed_count migration files are empty or contain only whitespace"
+            exit 1
+          else
+            echo '✅ All migration files have content'
+          fi