geonetwork / geonetwork/core-geonetwork

Migration / Cleaning up database tables and columns / PostGres DB / Version 3.12.6 to 4.2.2

Open
#7,979 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
521
Forks
514
Avg merge
6d 13h
Merged PRs (30d)
19

Description

**Describe the bug**
Cleanup of database tables and columns no longer needed after a database migration from GN 3.12.6 to GN 4.2.2 (Postgres). Obsolete tables and columns are not deleted during migration.

The following tables are still present after the database migration, but not in a bare new GN 4.2.2 instance. These tables should be cleaned up during the migration:
- services & serviceparameters: Contains data on the virtual CSW, which is not supported with GN 4.2.2, so these tables are no longer required.
- cswservercapabilitiesinfo
- guf_citation

Additionally, the guf_userfeedbacks table contains columns and one constraint that is no longer required. They might be deleted during migration.

**To Reproduce**
Steps to reproduce the behavior:
1. Start GeoNetwork migration from version 3.12.6 to version 4.2.2
2. Set up a bare new GN 4.2.2 instance
3. Compare the mentioned database tables between the migrated database and the database of the new GN 4.2.2 instance

**Expected behavior**
Migration takes cleaning up database tables and columns into account. The migrated database does not contain obsolete tables and columns.

**Desktop (please complete the following information):**
- GeoNetwork Version 4.2.2
- Server Application Tomcat 8

**Additional context**
Partial solution proposal:
```
-- Postgres SQL statement to clean up the guf_userfeedbacks table
ALTER TABLE IF EXISTS geonetwork.guf_userfeedbacks DROP CONSTRAINT IF EXISTS fk_e9nt5jgp7gi12nvavaacs7bbr;
ALTER TABLE IF EXISTS geonetwork.guf_userfeedbacks DROP COLUMN IF EXISTS comment;
ALTER TABLE IF EXISTS geonetwork.guf_userfeedbacks DROP COLUMN IF EXISTS date;
ALTER TABLE IF EXISTS geonetwork.guf_userfeedbacks DROP COLUMN IF EXISTS citation_id;
```

Contributor guide

Open the contributing guide

Research direction

Start by inspecting the migration path from GeoNetwork 3.12.6 to 4.2.2 and comparing it with the schema of a bare 4.2.2 instance. Verify how migrations define database changes, then ensure the obsolete services, serviceparameters, cswservercapabilitiesinfo, guf_citation, and guf_userfeedbacks columns and constraint are removed. Done when the migrated schema matches the fresh schema for these objects.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, sql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.