Currently migration of database changes is conducted using sql scripts, which are simple to generate and have so far been fairly effective.
However, sql scripts are rather cumbersome to maintain, and are not a particularly user friendly to apply. In addition, it is difficult to store metadata about the scripts – currently the only usable information is embedded in the file name and path – making them impractical for using with a modular system.
Applying the scripts can be done in two ways: either one by one, which is extremely time-consuming; or as a batch, in which case a single error in one script may cascade into many consequent errors requiring the whole process to be restarted.
Requiring DB specific scripts means more work to maintain migrations. Hand editing of the scripts is often required and must be carefully applied to all versions equally, occasionally leading to mismatches between database versions.
There is lots of noise in the generated scripts. For example most AD changes trigger updates of translations etc. This makes it harder to identify the significant issues.
The user needs some degree of technical knowledge to run the scripts (or even to find them on svn!). If errors occur it can be difficult to track down where the problem originated.
It's difficult to track which scripts have been applied (though there is a "Migration script" window which apparently does this, although its use does not seem to have caught on).
Reapplying sql scripts causes errors (as they are not idempotent).
These problems will all be compounded as we change to a more modular system, because we will need to be able to have different scripts executed for installed packages.
There is no provision for rollback/undo of changes, which would also be nice to have.
Proposed new approach to managing migrations.
There exist several third party db independent migration solutions for java applications, notably Liquibase and dbmigrate. They take a similar approach of storing migration information in a database independent fashion, with sufficient metadata to enable them to manage migrations, rollbacks etc. Adempiere already has a means of doing this through its Persistent Objects. The aim is to create a tool for managing database migration within the AD, allowing migrations to be managed entirely from within Adempiere. Migration scripts will be logged automatically generated and logged in the database, and then can be edited and exported to XML for distribution, to be applied on other systems from within the application.
Each migration "script" will be stored in a new table "AD_Migration" which provides information about which package it belongs to, its purpose, the order it should be applied in, and which release it applies to. A process (executed by the "Apply" button) allows the user to apply a migration, and once applied to rollback (if supported by the original script creator).
Thank you very much, Steven, Paul for this big work