feat(archives): RiC-CM full roadmap (Phases 1-6) — RiC-O Linked Data, agents/activities/places, admin UI, OAI-PMH ric-o (v0.7.12, #122) #627
Workflow file for this run
This file contains hidden or 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: Test Database Migrations | |
| on: | |
| push: | |
| paths: | |
| - 'installer/database/migrations/**' | |
| - 'installer/database/schema.sql' | |
| - 'version.json' | |
| - '.github/workflows/test-migrations.yml' | |
| pull_request: | |
| paths: | |
| - 'installer/database/migrations/**' | |
| - 'installer/database/schema.sql' | |
| - 'version.json' | |
| - '.github/workflows/test-migrations.yml' | |
| workflow_dispatch: | |
| permissions: | |
| contents: read | |
| concurrency: | |
| group: ${{ github.workflow }}-${{ github.ref }} | |
| cancel-in-progress: true | |
| jobs: | |
| # ─── Job 1: verify all migrate_*.sql have version ≤ version.json ─────────── | |
| # Catches the silent-skip bug: a migration named higher than the release | |
| # version is silently ignored by the updater (version_compare guard). | |
| verify-versions: | |
| name: Verify migration versions ≤ release | |
| runs-on: ubuntu-latest | |
| steps: | |
| - uses: actions/checkout@v6 | |
| - name: Setup PHP | |
| uses: shivammathur/setup-php@v2 | |
| with: | |
| php-version: '8.2' | |
| coverage: none | |
| github-token: '' | |
| - name: Check all migrate_*.sql ≤ version.json | |
| run: | | |
| TARGET=$(php -r "echo json_decode(file_get_contents('version.json'))->version;") | |
| echo "Release version: $TARGET" | |
| FAILED=0 | |
| for f in installer/database/migrations/migrate_*.sql; do | |
| V=$(basename "$f" | sed 's/migrate_//;s/\.sql//') | |
| if php -r "exit(version_compare('$V', '$TARGET', '<=') ? 0 : 1);"; then | |
| echo " ✓ $(basename $f) ($V)" | |
| else | |
| echo " ✗ $(basename $f) ($V > $TARGET) — would be silently skipped by updater!" | |
| FAILED=1 | |
| fi | |
| done | |
| exit $FAILED | |
| # ─── Job 2: full migration chain — schema.sql + every migration in order ─── | |
| # Tests idempotency: migrations use CREATE TABLE IF NOT EXISTS / SET @s := IF(@c=0,...) | |
| # patterns so they should run cleanly on top of the current schema. | |
| # Some (e.g. 0.3.0 column rename) will error because the schema already has the | |
| # correct name — those are expected and handled with || true; the final schema | |
| # assertion is the real pass/fail gate. | |
| test-full-chain: | |
| name: Full migration chain (schema → all migrations) | |
| runs-on: ubuntu-latest | |
| services: | |
| mysql: | |
| image: mysql:8.0 | |
| env: | |
| MYSQL_ROOT_PASSWORD: root | |
| MYSQL_DATABASE: pinakes_test | |
| ports: | |
| - 3306:3306 | |
| options: >- | |
| --health-cmd="mysqladmin ping" | |
| --health-interval=10s | |
| --health-timeout=5s | |
| --health-retries=5 | |
| steps: | |
| - uses: actions/checkout@v6 | |
| - name: Setup PHP | |
| uses: shivammathur/setup-php@v2 | |
| with: | |
| php-version: '8.2' | |
| extensions: mysqli | |
| coverage: none | |
| github-token: '' | |
| - name: Wait for MySQL | |
| run: until mysqladmin ping -h"127.0.0.1" --silent; do sleep 1; done | |
| - name: Install base schema | |
| run: mysql -h 127.0.0.1 -u root -proot pinakes_test < installer/database/schema.sql | |
| - name: Apply all migrations in version order | |
| run: | | |
| FAILED=0 | |
| for sql in $(ls installer/database/migrations/migrate_*.sql | sort -V); do | |
| echo "→ $(basename $sql)" | |
| OUTPUT=$(mysql --force -h 127.0.0.1 -u root -proot pinakes_test < "$sql" 2>&1) || true | |
| # Check each ERROR line individually — skip only known-idempotent ones | |
| while IFS= read -r line; do | |
| if echo "$line" | grep -qiE '^ERROR'; then | |
| if echo "$line" | grep -qiE 'duplicate (column name|key|index)|table .* already exists|check constraint .* already exists|can.t drop'; then | |
| echo " ↳ idempotent: $line" | |
| else | |
| echo " ✗ $line" | |
| FAILED=1 | |
| fi | |
| fi | |
| done <<< "$OUTPUT" | |
| done | |
| [ "$FAILED" -eq 0 ] || exit 1 | |
| - name: Verify tables from 0.5.x migrations | |
| run: | | |
| COUNT=$(mysql -h 127.0.0.1 -u root -proot pinakes_test -sN -e " | |
| SELECT COUNT(*) FROM information_schema.tables | |
| WHERE table_schema='pinakes_test' | |
| AND table_name IN ('archival_units','authority_records','collane','volumi','libri_collane'); | |
| ") | |
| echo "Tables found: $COUNT / 5" | |
| [ "$COUNT" -eq 5 ] && echo "✓ All expected tables present" || { echo "✗ Missing tables"; exit 1; } | |
| - name: Verify columns from 0.5.9.x migrations | |
| run: | | |
| COUNT=$(mysql -h 127.0.0.1 -u root -proot pinakes_test -sN -e " | |
| SELECT COUNT(*) FROM information_schema.columns | |
| WHERE table_schema='pinakes_test' | |
| AND ( | |
| (table_name='archival_units' AND column_name IN ('version_note','ark_identifier','iiif_manifest_url','rights_statement_url')) | |
| OR | |
| (table_name='collane' AND column_name IN ('parent_id','tipo','gruppo_serie')) | |
| ); | |
| ") | |
| echo "Columns found: $COUNT / 7" | |
| [ "$COUNT" -eq 7 ] && echo "✓ All expected columns present" || { echo "✗ Missing columns"; exit 1; } | |
| - name: Verify ENUM values after full migration chain | |
| run: | | |
| FAILED=0 | |
| # oai_deleted_records.entity_type must include 'archival_unit' (not the old typo 'archive_unit') | |
| V=$(mysql -h 127.0.0.1 -u root -proot pinakes_test -sN -e \ | |
| "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS \ | |
| WHERE TABLE_SCHEMA='pinakes_test' \ | |
| AND TABLE_NAME='oai_deleted_records' \ | |
| AND COLUMN_NAME='entity_type'") | |
| echo "oai_deleted_records.entity_type: $V" | |
| if echo "$V" | grep -q "archival_unit"; then | |
| echo " ✓ 'archival_unit' present" | |
| else | |
| echo " ✗ 'archival_unit' missing — got: $V" | |
| FAILED=1 | |
| fi | |
| if echo "$V" | grep -q "'archive_unit'"; then | |
| echo " ✗ old typo 'archive_unit' still present — fix schema.sql" | |
| FAILED=1 | |
| fi | |
| # archival_units.level must include core ISAD(G) levels | |
| V=$(mysql -h 127.0.0.1 -u root -proot pinakes_test -sN -e \ | |
| "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS \ | |
| WHERE TABLE_SCHEMA='pinakes_test' \ | |
| AND TABLE_NAME='archival_units' \ | |
| AND COLUMN_NAME='level'") | |
| if [ -n "$V" ]; then | |
| echo "archival_units.level: $V" | |
| for val in fonds series file item; do | |
| if echo "$V" | grep -q "'$val'"; then | |
| echo " ✓ '$val' present" | |
| else | |
| echo " ✗ '$val' missing" | |
| FAILED=1 | |
| fi | |
| done | |
| fi | |
| [ "$FAILED" -eq 0 ] || exit 1 | |
| # ─── Job 3: existing single-migration test (upgrade 0.3.x → 0.4.0) ───────── | |
| test-migrations: | |
| runs-on: ubuntu-latest | |
| services: | |
| mysql: | |
| image: mysql:8.0 | |
| env: | |
| MYSQL_ROOT_PASSWORD: root | |
| MYSQL_DATABASE: pinakes_test | |
| ports: | |
| - 3306:3306 | |
| options: >- | |
| --health-cmd="mysqladmin ping" | |
| --health-interval=10s | |
| --health-timeout=5s | |
| --health-retries=5 | |
| steps: | |
| - uses: actions/checkout@v6 | |
| - name: Setup PHP | |
| uses: shivammathur/setup-php@v2 | |
| with: | |
| php-version: '8.2' | |
| extensions: mysqli, pdo_mysql | |
| github-token: '' | |
| - name: Wait for MySQL | |
| run: | | |
| while ! mysqladmin ping -h"127.0.0.1" --silent; do | |
| sleep 1 | |
| done | |
| - name: Install base schema (simulating v0.3.0) | |
| run: | | |
| # First install the base schema | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test < installer/database/schema.sql | |
| # Remove the GDPR tables/columns to simulate pre-0.4.0 state | |
| # MySQL 8.0 doesn't support "DROP COLUMN IF EXISTS", so we use a workaround | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e " | |
| DROP TABLE IF EXISTS consent_log; | |
| DROP TABLE IF EXISTS gdpr_requests; | |
| DROP TABLE IF EXISTS user_sessions; | |
| " | |
| # Drop columns with error suppression (MySQL 8.0 compatibility) | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e "ALTER TABLE utenti DROP COLUMN privacy_policy_version;" 2>/dev/null || true | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e "ALTER TABLE utenti DROP COLUMN data_accettazione_privacy;" 2>/dev/null || true | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e "ALTER TABLE utenti DROP COLUMN privacy_accettata;" 2>/dev/null || true | |
| echo "=== Simulated v0.3.0 state ===" | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e "SHOW COLUMNS FROM utenti;" | grep -E "privacy|consent" || echo "No GDPR columns found (expected)" | |
| - name: Create test user | |
| run: | | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e " | |
| INSERT INTO utenti (codice_tessera, nome, cognome, email, password, stato, tipo_utente, email_verificata, data_registrazione) | |
| VALUES ('TEST001', 'Test', 'User', 'test@example.com', 'hash', 'attivo', 'admin', 1, NOW()); | |
| " | |
| - name: Run migration 0.4.0 | |
| run: | | |
| # Remove comments and run migration | |
| sed '/^--/d' installer/database/migrations/migrate_0.4.0.sql | \ | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test | |
| - name: Verify migration results | |
| run: | | |
| # Check columns exist | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e " | |
| SELECT privacy_accettata, data_accettazione_privacy, privacy_policy_version | |
| FROM utenti LIMIT 1; | |
| " | |
| # Check tables exist | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e "SHOW TABLES LIKE 'user_sessions';" | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e "SHOW TABLES LIKE 'gdpr_requests';" | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e "SHOW TABLES LIKE 'consent_log';" | |
| # Verify backfill worked | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e " | |
| SELECT COUNT(*) as backfilled FROM utenti WHERE privacy_accettata = 1; | |
| " | |
| - name: Test idempotency (run migration again) | |
| run: | | |
| # Running migration again should only produce expected errors (duplicate column/table already exists) | |
| set +e | |
| OUTPUT=$(sed '/^--/d' installer/database/migrations/migrate_0.4.0.sql | \ | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test 2>&1) | |
| EXIT_CODE=$? | |
| set -e | |
| if [ $EXIT_CODE -ne 0 ]; then | |
| echo "$OUTPUT" | |
| # Check if errors are only the expected idempotent ones | |
| if echo "$OUTPUT" | grep -qiE 'duplicate|already exists'; then | |
| echo "✓ Migration is idempotent (expected duplicate/exists errors only)" | |
| else | |
| echo "✗ Unexpected error during idempotency test" | |
| exit 1 | |
| fi | |
| else | |
| echo "✓ Migration re-ran without errors" | |
| fi | |
| - name: Final verification | |
| run: | | |
| echo "=== Final Table Count ===" | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e " | |
| SELECT COUNT(*) as table_count FROM information_schema.tables | |
| WHERE table_schema = 'pinakes_test'; | |
| " | |
| echo "=== GDPR Tables ===" | |
| mysql -h 127.0.0.1 -u root -proot pinakes_test -e " | |
| SELECT table_name FROM information_schema.tables | |
| WHERE table_schema = 'pinakes_test' | |
| AND table_name IN ('user_sessions', 'gdpr_requests', 'consent_log'); | |
| " |