This article is in progress. Please feel free to edit.
According to forums there is not a unique and efficient way of creating migration scripts and even migrating to a newer database is reported to sometimes have problems specially if the user has local customisations applied.
This article tries to gather the migration considerations in one place and conclude a reliable policy which perhaps leads to a developing a utility or selecting an existing tool which fits into our needs.
Let's assume there is
- A user database which is currently being used by her; we call it UDB.
- A trunk database which is the new seed that user wishes to migrate to; we call it TDB.
- A final database which will be the migrated database. Let's name it MDB.
- New record is considered as a SQL INSERT statement
- New column is considered as a SQL ALTER statement
- New table notifies a SQL CREATE TABLE statement.
- Modified record value points to a SQL UPDATE statement.
- Modified column points to a SQL ALTER statement
- Removed record notifies a SQL DELETE statement.
- Removed column notifies a SQL ALTER statement.
What Is Migration?
The migration is supposed to
- Add new (which doesn't exist in UDB) rows and columns in TDB to MDB.
- Include the modifications to existing UDB rows and columns in MDB.
- Add the existing rows and column in UDB.
There are three types of changes in database which we should take care of.
Looks straight-forward -as the user couldn't count on a non-exisintg column or row in her customisations therefore a new record/column/table can be simply added to MDB.
There may be some problems with additions that might effect the logic that user customisations count on. (I don't have any example for now and even don't know if I'm right at all!)
Basically modified rows/columns on UDB and TDB should be merged together into MDB.
However there are cases that the user has a customisation which are effected by the same row.
For example, suppose UDB has a row (ADempiere column) in AD_Column which is not encrypted and has a callout set. That row (ADempiere column) in TDB is encrypted and has no callout set or has a different callout. There are two policies to merge those two together:
- Merge the two columns into an encrypted one with the TDB value for callout set as the MDB record callout.
- Merge the two columns into a non-encrypted one with the UDB value for callout set as the MDB record callout and inform the user and provide him with the SQL script to change the column to encrypted with TDB callout as the MDB callout if the user decides to so.
Seems that finiding out if a merge effects the user customisation needs some sort of a heuristic algorithm, thus the easiest way is to inform the user as in (2).
- To remove the database column through the migration script you must add another two SQL statement:
- Delete column reference from AD_Field Table by AD_Column_ID.
- Alter Column from the table itself.
- For example:
- When you trying to delete Amount column from C_Amount table from table window from application dictionary, you found into the migration file the following SQL Statement:
-- Nov 20, 2008 7:06:23 PM IST-- New Event Model Validator DELETE FROM AD_Column_Trl WHERE AD_Column_ID=1000683 ; -- Nov 20, 2008 7:06:23 PM IST-- New Event Model Validator DELETE FROM AD_Column WHERE AD_Column_ID=1000683 ;
- But before execute this statements you must add the first statement to delete the column reference from AD_Field Column at the first and add the second statement to the end as the following:
-- Nov 20, 2008 7:06:23 PM IST -- New Event Model Validator Alter table C_Amount drop column Amount
- Then the column dropped successfully
- SF discussion on Creating Migration Scripts