Migrate - Compiling and Extending

From ADempiere ERP Wiki
Jump to navigationJump to search


Compiling Migrate[edit]

Normally there should be no need to compile Migrate, as it will be installed together with Adempiere.

However, there may be situations when you separately want to compile Migrate, either to modify the code to suit your personal needs, or to fix bugs or extend the code and hopefully contribute your enhancements to the Adempiere project.

Requirements[edit]

Migrate requires the Java Development Kit version 1.7 (JDK 7)8 and therefore also at least version 3.7.0lts of Adempiere.

Downloading and Compiling the Source Code[edit]

  1. Download the Adempiere source.

  2. You can either compile the complete Adempiere project or only the Migrate sub-project.

    • To compile the complete Adempiere project, change to directory utils_dev.

      cd utils_dev

    • To compile only the Migrate sub-project, change to directory migrate.

      cd migrate

  3. Then execute RUN_build.sh (or RUN_build.bat).

    ./RUN_build.sh

  4. The resulting JAR file (migrate.jar) will be created in the migrate project directory and also copied to the ../lib directory.

  5. This will also generate the API and user documentation, to be found in the migrate/apidoc and migrate/userdoc directories, respectively.

For details on how to work with Adempiere source code, consult the Adempieredocumentation.

Building and Running Migrate in Eclipse[edit]

Consult the Adempieredocumentation on how to compile and run Adempiere from within ECLIPSE.

Note that the JDBC drivers for installed databases must be in the classpath.

If you have installed Adempiere, they can be found in $ADEMPIERE_HOME/lib:

  • $ADEMPIERE_HOME/lib/oracle.jar for Oracle

  • $ADEMPIERE_HOME/lib/postgresql.jar for postgreSQL

Otherwise they can be found in subdirectories of your local database installation, for example

  • $ORACLE_HOME/jdbc/lib/ojdbc14.jar for Oracle

  • /usr/share/java/postgresql-jdbc.jar for postgreSQL

To add files or directories to the classpath in Eclipse (version 3.4.1), in the Run menu select Run Configurations…, select the Classpath tab and click the Add External JARs… button.

JDBC drivers must be set in the classpath for Migrate to run in Eclipse


Running the Migration Tool from Eclipse (additional)[edit]

In this section, it will be explained why and how to execute the migration process from Eclipse.

It is expected that the migration process runs without a single error. This can happen if the legacy database was implemented fully following the ADempiere best practices and no undue changes were done to the columns, tables, indexes, constraints, sequences, triggers, functions, etc. Mostly, customers do changes which vary from small to very invasive. This may cause errors and warnings during execution of the migration process, which are a sign that the migration has failed.

Examples of cases which lead to errors are creating or deleting constraints which are in conflict with the official ADempiere changes (which may also happen after installation!), creating custom columns with Entity type Dictionary (instead of User maintained or a custom Entity type) to name a few.

In order to run the migration process from Eclipse, create an Eclipse project and once the project has been created, define a Run/Debug configuration, with the project and the following main class: com.kkalice.adempiere.migrate.Migrate.

Looking at the code, you'll see that the migration process uses the database dictionary to do the migration. This is a great feature, as the process does follow the definitions as stored within the database while executing the migration.Also important is to know that for every step during the migration process there are save points which are separate transactions. This way you can put a breakpoint at every program line of the process and see after every save point how the target database is being changed.

The log files are deployed in $ADEMPIERE_HOME. They are the trace log (example: migration_201606132022.trace.log), the warnings log (example: migration_201606132022.warning.log) and the error log (example: migration_201606132022.error.log). It is important that the error log has no entries, so you know the migration process has been done correctly

I came across migration processes with thousands of errors. Mostly the first error was detected and the others had a message like not continuing until end of transaction block. In this case, you must restore the target database, correct the first error (mostly in the target database) and restart the process.

Another error was when the following constraint was about to be created: CREATE UNIQUE INDEX ppproductbomunique ON adempiere.pp_product_bom(ad_client_id, value). In the target database, the BOM value could be repeated in former versions, whereas in the new version, the creation of the index causes an error in cases of repeated values for BOMs. In such cases, you must restore the target database, correct the conflicting records and restart the migration process.

In another case, the migration tool truncated during execution of the method purgeOrphans() -due to some changes the customer had made- the tables m_production and m_productionline. This led to errors while creating constraints (foreign keys pointing to both tables)later on the process. In order to overcome this, I had to create a backup of both tables, stop the execution after execution of the method purgeOrphans(), restore the table contents and continue with the process

You have to bear in mind that official views and functions will be updated from the reference to the target database. This implies that if you have changed an official view, function or any other object in the database, it will be overwritten during the migration process and you will have to re-create them. This can be complex, perhaps needing to do it in cascade.

Look closely at the log files where you can detect inconsistencies which you must correct mostly manually

Extending Migrate[edit]

Source Files[edit]

Being open-source, Migrate has the advantage that you can modify the source code to fit your particular needs.

More than that, Migrate is designed to be easily extendable for localization and for handling additional database vendors, and you are invited to help and contribute your solutions to Adempiere.

To help you navigate the source files, they are listed here by category:

Table 4.1. Source Files

CategorySource Files

Main class

Migrate.java

Parameters and constants

Parameters.java

Graphical User Interface

Gui.java
HelpAbout.java
HelpInfo.java
images/*

Logging

MigrateLogger.java
MigrateLogger_Formatter.java
MigrateLogger_Filter.java
PreparedStatementWrapper.java

Localization

Messages.java

User Documentation

manual.xml
images/doc_.png

JDBC connection to database

DBConnection.java

Vendor-specific SQL-generation and database rules and conventions

DBEngine.java
DBEngineInterface.java

DBEngine_Oracle.java
DBEngine_Postgresql.java

Database objects

DBObject.java
DBObjectInterface.java
DBObjectDefinition.java

DBObject_Table.java
DBObject_Table_Column.java

DBObject_PrimaryKey.java
DBObject_PrimaryKey_Table.java
DBObject_PrimaryKey_Column.java

DBObject_ForeignKey.java
DBObject_ForeignKey_Table.java
DBObject_ForeignKey_Column.java

DBObject_Check.java
DBObject_Check_Table.java
DBObject_Check_Rule.java

DBObject_Unique.java
DBObject_Unique_Table.java
DBObject_Unique_Column.java

DBObject_Index.java
DBObject_Index_Table.java
DBObject_Index_Column.java

DBObject_View.java
DBObject_View_Definition.java

DBObject_Sequence.java
DBObject_Sequence_Counter.java

DBObject_Function.java
DBObject_Function_Argument.java
DBObject_Function_Body.java

DBObject_Operator.java
DBObject_Operator_Signature.java
DBObject_Operator_Definition.java

DBObject_Trigger.java
DBObject_Trigger_Table.java
DBObject_Trigger_Definition.java

Application Dictionary Objects

ADObject_TreeNode.java


Adding Languages and Locales[edit]

All messages are contained in the resource file Messages.java, which contains US-English text as default locale.

To add additional languages or locales, copy Messages.java to a new file following Java's Resource Bundle naming convention.

For example, to create a French resource file, name it Messages_fr.java.

To differentiate between French as spoken in France and French as spoken in Canada, create two resource files named Messages_fr_FR.java and Messages_fr_CA.java.

Of course the class declaration must be changed to match the file name, for example public class Messages extends ListResourceBundle { … would become public class Messages_fr_FR extends ListResourceBundle { ….

The file contains an array of {“key”, “localized String”} pairs. The keys should not be modified, as they are used to look up the localized string by the Resource Bundle. The localized string should be translated to the required language.

Note that while Resource Bundles generally accept {“key”, Object} pairs, Migrate can only handle String values such as in {“key”, “String”} pairs9.

Adding Database Vendors[edit]

To be able to communicate with different database vendors and follow their conventions and rules, Migrate uses a layer of “database engines” which answer to specific predefined requests and provide vendor-specific SQL statements.

These database engines are implemented as Java Interfaces and can therefore easily be extended to other database vendors. In this case, “easily” just means that interfaces for additional database vendors can easily be added, but the actual programming and debugging of such interfaces will still be a laborious task.

The interface definition, manifested in source file DBEngineInterface.java, defines which functions a vendor-specific database engine must contain, what arguments those functions will be given, and what Migrate expects as return values. Consult the [../apidoc/com/kkalice/adempiere/migrate/DBEngineInterface.html DBEngineInterface API] for details (it is generated by javadoc during compilation).

Two database engines are included with the original distribution of Migrate: one for Oracle and one for postgreSQL.

To add a new database engine, it is probably easiest to make a copy of the file which most closely matches the vendor you want to implement, name it according to the new vendor (for example, DBEngine_MySql.java, or DBEngine_AdabasD.java), and rename the class declaration inside the file (public class DBEngine_MySql implements DBEngine_Interface {…, or public class DBEngine_AdabasD implements DBEngine_Interface {…).

Then go through the methods step by step, compare the difference between DBEngine_Oracle.java and DBEngine_Postgresql.java, and figure out what your database vendor requires. After you are done programming the interface, extensive testing and debugging will follow.

To Do[edit]

The following are some features which would be nice for Migrate to have, but which have not been implemented yet.

The community is invited to submit contributions:

Identify Renamed Tables[edit]

In: Migrate.synchronizeTables()

Migrate drops tables not existing in the reference database and adds tables not existing in the target. So if a table has been renamed, the data contained in that table will be lost. It is therefore necessary to identify tables which have been renamed.

The obvious solution would be to check the AD_Element_ID of the table's primary key, but that method will fail:

In the past, when C_Allocation was renamed to C_AllocationLine, the primary key C_Allocation_ID (element 1380) became C_AllocationHdr_ID, and a new primary key C_AllocationLine_ID (element 2534) was created for the renamed table.

A different solution must be found.

Preserve Parent Links[edit]

In: Migrate.preserveParentLinks()

If a table in the live database does not contain a column existing in the reference database, that column will be created with a default value. But if the new column is used as part of a foreign key constraint in the reference database, the default value will not reference any parent record in the target database, which will result in an error when the foreign key is created.

Such "unlinked" fields should be linked to the correct parent, and it must be deduced from other data in the table what the correct parent is.

Currently the hints how to find the correct parent are hard-coded.

At some time, a C_Dunning_ID column was added to the C_DunningRun table, which was used as a foreign key to C_Dunning. When running an upgrade migration, the column is added and filled with 0 as default value. But 0 does not point to any parent in the C_Dunning table, and would thus result in an error when the foreign key is created.

It turns out that C_DunningRun contains a column called C_DunningLevel_ID, which links to the table C_DunningLevel. And C_DunningLevel has a link to the C_Dunning Table. So the correct target for the new C_Dunning_ID column can be deduced by following the link to C_DunningLevel_ID and from there to C_Dunning.

This hint is currently hard-coded.

Migrate should be able to find out by itself how to deduce the correct parent.

As long as that can not be done, such hints must continue to be hard-coded as additional situations of this type are encountered.

Populate New Parents[edit]

In: Migrate.populateNewParents()

If new tables exist in the reference database but not in the target, they might be parent tables which must be filled with data from already existing child records.

Originally there was only a table C_Allocation. At some point, that table was renamed C_AllocationLine, and a new parent table C_AllocationHdr was introduced.

At that time, C_AllocationHdr_ID had to be set to the value of C_AllocationLine_ID, and columns in C_AllocationHdr that also existed in C_AllocationLine had to be filled with the values from C_AllocationLine, using

INSERT INTO … SELECT …;

The link from the child to the new parent record had to be set, and since the parent record's C_AllocationHdr_ID now had the same value as the child's C_AllocationLine_ID, it could easily be done with:

UPDATE C_AllocationLine SET C_AllocationHdr_ID = C_AllocationLine_ID WHERE C_AllocationHdr_ID IS NULL;

Finally, any references from other tables pointing to the old child table had to be re-directed to point to the new parent table, for example

UPDATE Fact_Acct SET AD_Table_ID=735 WHERE AD_Table_ID=390;

(C_AllocationHdr has AD_Table_ID 735, C_AllocationLine has AD_Table_ID 390)

Above is actually not so difficult to implement, but the problem is how to find the primary child table.

For example, if C_InvoiceLine and C_InvoiceTax exist, and a new table C_Invoice is created, how do we know that C_InvoiceLine is the table from which C_Invoice should be populated, not C_InvoiceTax?

Another problem arises from inconsistent table naming:

C_Invoice - C_InvoiceLine (the short name is the parent, the long name is the child)
C_AllocationHdr - C_AllocationLine (both parent and child names are long)
GL_JournalBatch - GL_Journal - GL_JournalLine (the parent has a long name, the child has a short name, and the grandchild has a long name again)

Translation of Functions[edit]

In: DBEngine_vendor.translateFunctionBodyFull()

Migrate can more or less successfully translate views using regular expressions, but the translation of functions is much more difficult.

Any help to translate functions between the different procedural languages native to each database vendor would be highly appreciated.

Fail-Safe / Safe-Fail[edit]

Migrate requires the migration process not to be interrupted.

If it does get interrupted, for example because of a power outage, you need to restore the live database from your backup and start the migration process again from scratch. That is because Migrate drops views, functions, constraints, indexes etc. before starting the migration process. If the migration process is interrupted before those objects are recreated, they will be lost forever.

It would be nice if Migrate saved the meta-data it gathered and then used that saved meta-data to resume migrations which were interrupted.

Delete Client / Delete Transactions[edit]

The original Compiere migration tool had a facility to delete transactions (in effect “resetting” a client) or to delete a client entirely. It is probably better not to include such functionality in Migrate but rather have a specialized tool for such kind of task.

However, if anybody sees the need to add such functionality to Migrate, there already is a private dropClient() function in the main Migrate class which can be made public and used for such purpose. (It is currently used to drop the GardenWorld client).

There is no function yet to delete only transactions.



[8] There is actually only one reason for this limitation: Some JDBC drivers do not return a readable SQL statement but only an object reference when the toString() method is called on a prepared statement, rendering it useless for logging purposes. Therefore Migrate uses a wrapper around the PreparedStatement class, which overrides the toString() method and returns a human readable string to be used for logging. All other methods are caught to extract variable information which is used to generate the string, and then passed on to the original PreparedStatement class. As Java evolves, new methods are added to PreparedStatement, some of which also accept or return classes introduced in the new Java version. Since Java does not allow conditional compiling, a choice had to be made whether to be compatible with previous versions or with the newest version, and the decision went in favor of the newest version.

[9] For this reason, to translate keyboard codes for mnemonic highlighting of menu items, labels, or buttons, the keyboard code, which is an int, is converted to an Integer which is converted to a String, as in:

	…
	{"guiMenuHelp", "Help"},
	{"guiMenuHelpMnemonic", new Integer(KeyEvent.VK_H).toString()},
	…