Migrating data from legacy systems

From OpenPetra Wiki
Jump to navigation Jump to search

Introduction

We are working on a specific migration path for Petra 2.x databases to OpenPetra. But the tools and ideas can be helpful for any other migration as well.

The goal is to dump the data from the old database. It is recommended to write a tool that in the end produces an .sql.gz file similar to the one that is created by a openpetraorg-server.sh backup call. This sql file will be specific to the database system you are using.

The data needs to be collected from the existing table structure into the OpenPetra table structure. Sometimes data must be cleaned up to fulfill the constraints defined in the OpenPetra database.

To collect the data, the best idea would be to use .Net programs to access the old database. But this is not always possible, perhaps the SQL/ODBC connection is far too slow, so you might need to dump first with the legacy system, and then clean up the data and reformat.

The code to collect the data can be auto-generated, if the table structure of the old system is quite similar to the OpenPetra database structure.

Examples

Migration from Petra 2.x

The code for the migration from Petra 2.x is in csharp/ICT/PetraTools/DataDumpPetra2.

Preparations

Since the old database system is quite outdated, and does not support access to the SQL engine over ODBC very well, we have to generate a program in the 4GL language to dump the data to text files.

To generate the 4GL dump program, you have to run Ict.Tools.DataDumpPetra2.exe -operation:createProgressCode with the xml database definition file of Petra 2.x. This will generate fulldump23.p. You need to compile this program with the Progress IDE, but make sure you compile against a local standalone Petra 2.3 database, compiling against a network database will take forever.

Then copy the resulting fulldump23.r file to the same directory where your Ict.Tools.DataDumpPetra2.exe is.

Install Mono 2.10 or greater

Our legacy system runs on CentOS, and the latest Mono for our CentOS was 2.6.7, which is too old for OpenPetra. You can download this archive http://files.pokorra.de/mono/centos/mono-2.10.8-32bitcentos.tar.gz and unzip it to eg. /opt/mono-2.10

Create a dump of the data ready for restore to OpenPetra

You must set the environment variables for your Petra 2.x database, so the best is to run first:

. /usr/local/petra/db23/df/etc/petraenv.sh

Now you call Ict.Tools.DataDumpPetra2.exe this way, depending which version of Mono you are using:

PATH=/opt/mono-2.10/bin:$PATH
mono Ict.Tools.DataDumpPetra2.exe

This will assume the files petra23.xml in the current directory with the database structure of Petra 2.3, and petra.xml with the database structure of OpenPetra in the same directory as well. Note: the old petra23.xml file is not included in the public bazaar, since Petra 2.x was not Open Source and the file would not benefit anyone else. If you have the petra23.xml file, you need to also rename the datastructure.dtd to datastructure23.dtd and change that reference at the top of the file petra23.xml as well.

You must have write access to that directory, since the tool will create a subdirectory called fulldump, where we will store a .d text file with the dump from the database.

Restore file to OpenPetra

To load the resulting .sql.gz file, please use

openpetraorg-server.sh restore fulldump/_load.sql.gz

or

nant loadDatabase -D:file=_load.sql.gz

Debugging

If you use the parameter -table:<table name>, eg. mono Ict.Tools.DataDumpPetra2.exe -table:a_account, only that single table will be dumped.

The parameter -debuglevel:1 will show more details, and will not delete the .d.gz file from the fulldump directory, after the file has been processed. If a .d.gz file already exists in the fulldump directory, it will be reused and not fetched from the Petra 2.x database again. -debuglevel:10 prints every value, so this is only recommended when you are testing a small file with a specific error.

If you get errors from the CSV parser of a .d file, please note: Since there are line breaks in the CSV values, the line counter does not fit exactly. The Linux editor vi does have different count, since it does not count single newline characters as a line break, but only carriage returns. If you open the .d file in Windows in Notepad++, you get the same line count as the output of DumpPetra2xToOpenPetra.