Difference between revisions of "Migration Solution"

Revision as of 01:44, 29 September 2008

This article is in progress. Please feel free to edit.

Introduction

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.

Pre-assumptions

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.

The term

• 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.

Considerations

There are three types of changes in database which we should take care of.

Additions

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!)

Modifications

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:

1. Merge the two columns into an encrypted one with the TDB value for callout set as the MDB record callout.
2. 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).

Removals

Solution

See Also

• Migration
• PostgreSQL migration

External Links

• SF discussion on Creating Migration Scripts