Bhavya-Sonigra opened a new pull request, #6309: URL: https://github.com/apache/fineract/pull/6309
## Description This PR fixes FINERACT-2704 by rebaselining the core database schemas (initial schema and initial data) using native SQL dumps (`pg_dump` and `mysqldump`) instead of relying on Liquibase's flawed legacy XML translation layer. Historically, Fineract's core database initialization failed on PostgreSQL because the legacy XML baselines generated invalid PostgreSQL syntax (such as missing casting in `m_account_transfer_details` and improperly mapped auto-increment structures). By executing native SQL directly, we guarantee 100% compliant schemas for both MariaDB and PostgreSQL out of the box. **Key Changes:** 1. Created `0001_initial_schema_<dbms>.sql` and `0002_initial_data_<dbms>.sql` for both PostgreSQL and MariaDB/MySQL. 2. Updated `changelog-tenant.xml` to dynamically load the appropriate native SQL baselines based on the active JDBC connection (`dbms`). 3. Set `splitStatements="false"` for PostgreSQL to prevent Liquibase from corrupting complex stored procedures and triggers. 4. Bypassed the legacy XML baselines in `initial-switch-changelog-tenant.xml`. ## Architectural Decision: Modular Rebaseline vs. "Whole Database" Dump When fixing the broken Liquibase migrations, we faced two architectural choices: 1. **The "Whole" Approach:** Take a massive dump of the fully-evolved database today, delete all 240+ incremental Liquibase XML patches, and replace the entire project's database history with a single massive SQL file. 2. **The "Modular Core" Approach (Chosen):** Replace *only* the baseline `0001_initial_schema` and `0002_initial_data` scripts with native SQL dumps, and leave all subsequent incremental patches (from `0242` onwards) exactly as they are. **Why the "Whole" approach is a bad idea:** Deleting the incremental changelogs and replacing them with one giant dump would break Fineract for every existing deployment globally. Existing databases rely on the `databasechangelog` tracking table to know exactly which incremental scripts have already been applied. Wiping the history would cause massive upgrade conflicts. **Why we picked the "Modular Core" approach:** By only replacing the baseline `0001` and `0002` scripts, we achieve the best of both worlds. * **For New Deployments:** They get a perfectly clean, native SQL boot sequence that works flawlessly on both MariaDB and PostgreSQL, followed by the seamless application of all subsequent incremental patches. * **For Existing Deployments:** They are completely unaffected. They ran `0001` and `0002` years ago, and will simply continue picking up the newest incremental patches as usual. * **For Developers:** It preserves the entire chronological migration history in Git and Liquibase, making it easy to track how the schema evolved over time. ## JIRA Ticket [FINERACT-2704](https://issues.apache.org/jira/browse/FINERACT-2704) ## Type of change - [x] Bug fix (non-breaking change which fixes an issue) - [ ] New feature (non-breaking change which adds functionality) - [x] Breaking change (fix or feature that would cause existing functionality to not work as expected) ## How Has This Been Tested? - [x] Verified `bootRun` successfully completes tenant store and default tenant upgrades natively against PostgreSQL (`org.postgresql.Driver`). - [x] Verified `bootRun` successfully completes tenant store and default tenant upgrades natively against MariaDB (`org.mariadb.jdbc.Driver`). - [x] Executed E2E Cucumber tests against both MariaDB and PostgreSQL backends successfully. - [x] Ran formatting checks (`spotlessApply`) and licensing checks (`rat`). ## Checklist - [x] Write the commit message as per our guidelines - [x] Acknowledge that we will not review PRs that are not passing the build ("green") - it is your responsibility to get a proposed PR to pass the build, not primarily the project's maintainers. - [x] Create/update unit or integration tests for verifying the changes made. - [x] Follow our coding conventions. - [ ] Add required Swagger annotation and update API documentation at `fineract-provider/src/main/resources/static/legacy-docs/apiLive.htm` with details of any API changes *(N/A - No API changes)* - [x] This PR must not be a "code dump". Large changes can be made in a branch, with assistance. Ask for help on the developer mailing list. - [x] If merging this PR resolves a JIRA issue, I will mark that issue as resolved and set "Fix Version/s" appropriately. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
