AD Migration

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.

Status

Initial contribution from Adaxa https://sourceforge.net/tracker/?func=detail&atid=883808&aid=2806665&group_id=176962

Current Situation

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.

Problems include:

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

Migration1.1.png

Under each Migration there will a sequence of "Steps" that detail the actions to be performed. Each step will either be a "Application Dictionary (PO)" change, or a "SQL Statement" to be applied. Each step can be executed or rolled back independently, or as part of a migration. Errors in applying the step are displayed in the error message field, and the status of the step (unapplied, applied, failed) is flagged by the system.

The AD type changes refer to a PO that has either been added, deleted or updated. (AD Migration steps will be automatically generated by the system when "Log migration script" is enabled in the Preference form, using a method similar to the existing "Change Log".)

When the migration is run these AD type steps are applied using the standard PO save and delete methods, which will execute the normal before/after save methods -- updating translations etc automatically.

Migration2.1.png

Beneath the Step is a tab which displays the data that is to be altered. For each migration step, three values are recorded -- the old (reference) value of a standard system prior to the migration; the new value to which it will be migrated; and a backup of the value in the local system prior to the update. This means it will be possible to have a rule where a change is only applied if the existing local "backup" value matched the standard "old" value provided in the migration, thereby protecting local customisations from being overwritten. The user may wish to review the changes (a report could easily be produced using standard functionality), and may edit the data, or deactivate a step that they do not wish to be executed.

As a backup value is stored, rollback of this type of action can be achieved automatically, though some care will have to be taken with collateral effects (e.g. synchronising columns with the database).

Note that for an Update action only the updated columns would be included in the migration data.

The PO record key columns are flagged, allowing support for composite key type POs.

Migration4.1.png

The second type of migration step allows a sql statement to be executed against the database directly. The applicable database type can also be specified, so database specific sql can be used. A sql statement can also be provided to "rollback" the change to return the database to its pre-migration state.

Migration3.1.png

The AD migration record can be exported to XML for sharing with other Adempiere instances. See below for a generated sample output. The XML format suggested attempts to be reasonably human readable.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Migrations>
  <Migration EntityType="U" Name="Test Migration" ReleaseNo="3.5.3a" SeqNo="7">
    <Comments>Creates a table called "TestMigration" with all the columns copied from test.
Does not use central IDs so may fail with id conflicts.
Updates the "updated" column in the new table</Comments>
    <Step SeqNo="10" StepType="AD">
      <PO AD_Table_ID="100" Action="I" Record_ID="1000001" Table="AD_Table">
        <Data AD_Column_ID="355" Column="AD_Client_ID">0</Data>
        <Data AD_Column_ID="356" Column="AD_Org_ID">0</Data>
        <Data AD_Column_ID="100" Column="AD_Table_ID" keyColumn="true">1000001</Data>
        <Data AD_Column_ID="354" Column="AccessLevel">4</Data>
        <Data AD_Column_ID="50183" Column="CopyColumnsFromTable">N</Data>
        <Data AD_Column_ID="544" Column="Created">2009-06-14 23:29:56.349</Data>
        <Data AD_Column_ID="545" Column="CreatedBy">100</Data>
        <Data AD_Column_ID="6488" Column="EntityType">U</Data>
        <Data AD_Column_ID="6489" Column="ImportTable">N</Data>
        <Data AD_Column_ID="543" Column="IsActive">true</Data>
        <Data AD_Column_ID="8564" Column="IsChangeLog">false</Data>
        <Data AD_Column_ID="727" Column="IsDeleteable">true</Data>
        <Data AD_Column_ID="4196" Column="IsHighVolume">false</Data>
        <Data AD_Column_ID="726" Column="IsSecurityEnabled">false</Data>
        <Data AD_Column_ID="6125" Column="IsView">false</Data>
        <Data AD_Column_ID="108" Column="LoadSeq">0</Data>
        <Data AD_Column_ID="102" Column="Name">TestMigration</Data>
        <Data AD_Column_ID="9341" Column="ReplicationType">L</Data>
        <Data AD_Column_ID="107" Column="TableName">TestMigration</Data>
        <Data AD_Column_ID="546" Column="Updated">2009-06-14 23:29:56.349</Data>
        <Data AD_Column_ID="547" Column="UpdatedBy">100</Data>
      </PO>
    </Step>
...
    <Step SeqNo="330" StepType="AD">
      <PO AD_Table_ID="101" Action="I" Record_ID="1000061" Table="AD_Column">
        <Data AD_Column_ID="359" Column="AD_Client_ID">0</Data>
        <Data AD_Column_ID="109" Column="AD_Column_ID" keyColumn="true">1000061</Data>
        <Data AD_Column_ID="2608" Column="AD_Element_ID">608</Data>
        <Data AD_Column_ID="360" Column="AD_Org_ID">0</Data>
        <Data AD_Column_ID="226" Column="AD_Reference_ID">18</Data>
        <Data AD_Column_ID="227" Column="AD_Reference_Value_ID">110</Data>
        <Data AD_Column_ID="114" Column="AD_Table_ID">1000001</Data>
        <Data AD_Column_ID="116" Column="ColumnName">UpdatedBy</Data>
        <Data AD_Column_ID="549" Column="Created">2009-06-14 23:30:34.086</Data>
        <Data AD_Column_ID="550" Column="CreatedBy">100</Data>
        <Data AD_Column_ID="112" Column="Description">User who updated this records</Data>
        <Data AD_Column_ID="6482" Column="EntityType">U</Data>
        <Data AD_Column_ID="118" Column="FieldLength">22</Data>
        <Data AD_Column_ID="113" Column="Help">The Updated By field indicates the user who updated this record.</Data>
        <Data AD_Column_ID="548" Column="IsActive">true</Data>
        <Data AD_Column_ID="11617" Column="IsAlwaysUpdateable">false</Data>
        <Data AD_Column_ID="128" Column="IsEncrypted">N</Data>
        <Data AD_Column_ID="126" Column="IsIdentifier">false</Data>
        <Data AD_Column_ID="119" Column="IsKey">false</Data>
        <Data AD_Column_ID="124" Column="IsMandatory">true</Data>
        <Data AD_Column_ID="120" Column="IsParent">false</Data>
        <Data AD_Column_ID="6244" Column="IsSelectionColumn">false</Data>
        <Data AD_Column_ID="6483" Column="IsSyncDatabase">N</Data>
        <Data AD_Column_ID="125" Column="IsTranslated">false</Data>
        <Data AD_Column_ID="3360" Column="IsUpdateable">false</Data>
        <Data AD_Column_ID="111" Column="Name">Updated By</Data>
        <Data AD_Column_ID="127" Column="SeqNo">0</Data>
        <Data AD_Column_ID="551" Column="Updated">2009-06-14 23:30:34.086</Data>
        <Data AD_Column_ID="552" Column="UpdatedBy">100</Data>
        <Data AD_Column_ID="110" Column="Version">1</Data>
      </PO>
    </Step>
    <Step SeqNo="340" StepType="AD">
      <PO AD_Table_ID="101" Action="U" Record_ID="1000061" Table="AD_Column">
        <Data AD_Column_ID="1692" Column="Callout" isOldNull="true">callout.updated</Data>
        <Data AD_Column_ID="3360" Column="IsUpdateable" oldValue="false">true</Data>
      </PO>
    </Step>
    <Step DBType="ALL" SeqNo="70" StepType="SQL">
      <SQLStatement>CREATE OR REPLACE VIEW test_migrate_v
AS 
SELECT * FROM TestMigration;</SQLStatement>
    <RollbackStatement>DROP VIEW test_migrate_v</RollbackStatement>
    </Step>
  </Migration>
</Migrations>

The Migration window and import from XML process have been added to the System -> Application Dictionary menu.

Other processes could be added to:

  • run all migrations for a specified release
  • retrieve migrations from a central repository
  • generate a migration script from existing data or from change log
  • update a migration script with centralised IDs

MigrationMenu.png

Other approaches

It has been suggested that 2Pack could be used as a migration solution. However, 2pack itself is difficult to maintain (requiring an up to date handler for each PO type that may be exported.) It exports whole objects and their related objects, rather than just changes. This means that a Pack in can have many potential side-effects (overwriting customisations would be fairly likely). It works well for relatively independent modules, that are centrally controlled. I don't think it would fare well if multiple packages were attempting to make changes to relatively central AD elements.

Database comparison/synchronisation tools are useful for a monolithic application, but if the intent is to have modules that can be optionally installed it becomes impossible to define a single reference database. Each module must be allowed to maintain its own changes.