Migrating legacy apps to OpenERP

A customer recently took over another business, which had a system that had been developed in-house. The application was developed in Visual Basic with a Microsoft SQL Server database back-end. My job was to integrate this application into my customer’s operational environment, which in this case was OpenERP.

The application consisted of a number of tables, most of which had a primary key. The table naming and column naming conventions that had been used were not the customary ones used with OpenERP.

I took the following steps, which I will explain in greater detail below:

  1. Migrate the data from the source database to the target test database;

  2. a. Create an OpenERP module with the data model of the new application;

    b. Mutate target database fields and tables to their expected new names and types;

  3. Insert or update the module in OpenERP;
  4. Continue to massage the data using another SQL script.

Once these steps had been taken, development was complete and the new application behaved as it used to. We could then take the production database and apply steps 1, 3 and 4 to it. The final step was to disable access to the old application and continue working solely with the application in OpenERP.

Step 1: Migrate the data from the source database to the target test database

I used a custom script for this. This script first read the table definitions of a set of tables from the source database and recreated them in the target database. In some cases I had to change the type of some fields in order to accommodate for differing types between the two database platforms. In order to enable easy identification of tables for the new module, I added a prefix to each table in the target database. This also helped to reduce the number of SQL statements required in step 2.

After all of the table definitions had been added to the target database, the script tried to copy the data from the source database to the target database. Again, some differences in type between the two platforms were taken into account.

In the final version of the script, I disabled the copying of a number of tables because they were irrelevant for the new application or because they were simply no longer being used in the original application. Only those tables appearing in the new OpenERP module needed to be copied.

At the beginning of development, it is a good idea to copy over all of the tables. This ensures easy access to all of the data, regardless of its usefulness. Nothing slows you down like having to access a table in a different database.

Step 2a: Create an OpenERP module with the data model of the new application

This involves some cut-and-paste work between the data model and the code for the new OpenERP model. I will assume you have some experience in writing OpenERP modules. The development of this module should be carried out in lockstep with the mutation of target database fields and tables.

As a rule, I start with the base tables first and I do one table at a time. This makes it easy to see progress, and provides me with an idea of the data available and how it is related to other tables in the database. At this point, the development of the SQL script and the OpenERP module should happen at the same time.

Step 2b: Mutate target database fields and tables to match the OpenERP module

Here are some of the assumptions OpenERP makes about the objects in the database:

  • every object has an ID field;
  • an ID field is NOT NULL, and has a separate sequence for numbering;
  • the name of the object can be found in the column name;
  • references to other tables will be suffixed with _id (e.g. product_id);
  • if an object can be deactivated, the Boolean field active is present.

Example: rename table fields as more conventional OpenERP names

ALTER TABLE xx_products RENAME product_id TO id;
ALTER TABLE xx_products RENAME product_name TO name;

Example: add primary key constraint for the ID column

ALTER TABLE xx_products ALTER id SET NOT NULL;
ALTER TABLE xx_products ADD CONSTRAINT xx_products_pkey PRIMARY KEY (id);

Example: add a sequence for the ID field of the table and set ownership

CREATE SEQUENCE xx_products_id_seq;
ALTER TABLE xx_products_id_seq OWNER TO openerp;

Example: link the sequence to the ID column and set ownership of the table

ALTER TABLE xx_products ALTER id SET DEFAULT nextval('xx_products_id_seq');
ALTER TABLE xx_products owner to openerp;

Example: update sequence to maximum value of ID

SELECT setval('xx_products_id_seq', max(id)) FROM xx_products;

Examples: alter a column with a reserved OpenERP name but of a different type

ALTER TABLE xx_products ADD isactive boolean;
UPDATE xx_products SET isactive = (active <> 0);
ALTER TABLE xx_products DROP active;
ALTER TABLE xx_products RENAME isactive TO active;

Examples: rename a many2many table to conform to OpenERP naming conventions

ALTER TABLE xx_products_items RENAME TO xx_products_item_rel;

Use the rich PostgreSQL query language to mould the tables into something that makes sense within the context of an OpenERP module. This is also the time to change some of the data structures for easier development in OpenERP.

Step 3: Insert or update the module in OpenERP

OpenERP will add a number of default fields to the objects in your module. For example, the columns create_uid, create_date, write_uid and write_date will be added to all objects in your OpenERP module. Some of these columns will need to have data in them in order for OpenERP to function correctly. This will happen in the final step.

Step 4: Continue to massage the data

This is an additional SQL script used, for example, to populate the new create_uid and create_date columns on a set of newly minted OpenERP objects.

The migrated data can also be scrubbed. In this case, for example, I deactivated a large number of products that had not been ordered in the last three years. This makes it easier for the users to see only items that are relevant.