PostgreSQL migration

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.
Cleanup.png This article may require cleanup to meet the Wiki Documents standards. It may contain obsolete information or is in need of organization. Please improve this page if you can.


Introduction

This article talks about the migratition of a PostgreSQL-based ADempiere-310 to ADempiere-313. Hopefully it will become a manual people can follow step by to do the database-updates. Right now, however it is merely a report followed by a discussion.

General Procedure

  • Translate the sql-Scripts (which only run on Oracle) to the PostgreSQL dialect. This has been done using some java-Code of my own and is based on regular expressions (see forum entry [1]). However, the translation doesn't include views and functions, so
  • Get the sql functions for ADempiere-313
  • At least make a backup of the database you want to update (in fact I copied the whole thing to a fresh PostgreSQL installation to make sure, nothing importent would be put in danger)
  • Apply all the translated scripts to the DB to update
  • Apply the sql function-create-statments

Translation of the Oracle-SQL files

Like I said, I wrote a small java class (I don't want to call it tool yet) that translates strings using regular expressions. The I build some testcases and did bad hacking on the poor translator class until the following SQL-files (from /trunk/_Project-ID-ADempiere) were translated such that PostgreSQL 8.1 didn't raise any syntax errors anymore (probably out of wearyness):

  • sqls_3.1.0: 030_update_messages.sql
  • sqls_3.1.1: All files
  • sqls_3.1.2: All files but 005_recreate_M_PriceList_Create.sql, 006_recreate_M_Product_BOM_Check.sql and 011_PAYMENTTERMDUEDAYS.sql
  • sqls_3.1.3: All files

These are the sql-Scripts that don't declare functions.

As I was migrating from ADempiere-310, I also translated the two files in /trunk/migration/310-311 and /trunk/migration/311-312.

While working I had a lot of trouble with my regular expressions (along the lines of "Why %$! &/§# did/didn't this match right now?!"). It also turned out that I wasn't able to translate certain Oracle-statements that have to be split in more than one PostgreSQL-Statement. Example:

  ALTER TABLE PA_COLORSCHEMA MODIFY (
     ENTITYTYPE VARCHAR2(4)  DEFAULT NULL 
  );

needs to become

  ALTER TABLE PA_COLORSCHEMA ALTER 
    ENTITYTYPE TYPE VARCHAR(4);

  ALTER TABLE PA_COLORSCHEMA ALTER 
     ENTITYTYPE SET DEFAULT NULL;

At least that's what I think, but I am not very experienced with PostgreSQL. I then decided to fix these parts by hand because they occured only a few times in the large migration-files.

Insertion of the translated SQL data

Then I loaded the two large migration files into the nice SQL editor that comes with pgAdmin and hit the execute button until every part (besides the function declarations) was executed. If anyone is interested I can contribute the resulting files.

Next was execution of the "/trunk/_Project-ID-ADempiere" SQL files. Here it turned out that some of them (20_, 30_ and 50_ from sqls_3.1.1 and 004_ from sqls_3.1.2) can't be applied because apparently they were also in one of the large migration files. However, the rest of them actually worked :-).

Insertion of the ADempiere-313 functions

To get the fuctionis into the database I wanted to update I set up a whole fresh ADempiere-313 database (easily done thanks to this guide), dumped the whole schema and extracted the CREATE FUNCTION statements (they show up at the beginning of the dump) using a text editor. Then I changed the

  CREATE FUNCTION [...]

bits to

  CREATE OR REPLACE FUNCTION [...]

and executred the whole thing.

The result

Actually the outcome is not fully known yet. We are testing the database in a productive environment.

But there were also problems that are obviously related to the translated upgrade scripts. One is the "translation" of format strings that shouldn't be translated. After the upgrade we had the following string in our ad_message_trl table:

  Totaler Virtueller Speicher {0,NUMERIC,integer} kB - Frei {1,NUMERIC,integer} kB

Note the "NUMERIC" piece. It should actually say "NUMBER", but as the Oracle data type "NUMBER" was mapped to the PostgreSQL type "NUMERIC", so was this string. The result was an IllegalArgumentException when java tried to format a message using the string.

However, so far we could always identify and work our ways around all problems :-).

I also uploaded the resulting scripts to the wiki. They are available at http://www.adempiere.com/wiki/images/0/07/310-313_pg.zip.

Discussion

The approach of translating Oracle-Statements using regular expressions is imho not optimal. I think the error with the message string I explained above is a good example. The regexp-approach seems to me to be too low level and in order to do a proper job with it, one would have to build a sophisticated set of regular expressions.

I would rather propose to use (or build using e.g. Antlr?) a SQL analyser in order to get object representations of the statments and then use ddlUtils to get the SQL-Statements in the target dialect. The regexp-based approach doesn't seem to tackle the problem in general. It's just for one particualr target dialect and just for simple type mappings and the like. However, I'm not very good with regexp to start with, so someone else might be successful using that approach.