Porting to another database system

For those who ventured too far - just hope they know their way back :)

Moderator: neilg

Porting to another database system

Postby gerald » Sat Mar 04, 2006 3:54 am

Hi,

I am trying to add code to Compiere to allow it to use Derby as the database. From the looks of the file listings of Compiere from CVS, it appears there are people attempting to add support for DB2, Oracle, and PostgreSQL. I would like some advice on which files I would have to modify/add.

Thanks,
Gerald
gerald
 
Posts: 5
Joined: Wed Feb 22, 2006 5:10 am

Postby gerald » Tue Mar 07, 2006 1:18 am

I have a few questions below.

It appears I have to add a file to allow connections to the Derby database: /compiere-all/dbPort/src/org/compiere/db/DB_Derby.java.
(1) How does an instance of Compiere know whether it is a server or a client?
(1a) Does it matter whether an instance of Compiere know whether it is a server or a client?

Under /compiere-all/db/database/, there are directories Access, Create, Functions, Procedures, Startup. Only Startup, has directories for each database system.
(2) Were the files in the Startup directory generated using the Oracle-dependent SQL statements with a conversion using functions in Convert.java?
(3) Why is Startup the only directory that has directories for each database system?
/compiere-all/dbPort/src/org/compiere/dbPort/Convert.java
/compiere-all/dbPort/src/org/compiere/dbPort/ConvertMap.java

Any help is appreciated.

Gerald
gerald
 
Posts: 5
Joined: Wed Feb 22, 2006 5:10 am

Postby moyses » Tue Mar 07, 2006 7:14 am

It seems that Compiere Inc. is working on this also.

http://www.networkworld.com/news/2006/0 ... piere.html
moyses
 
Posts: 41
Joined: Wed Sep 01, 2004 10:22 pm
Location: Mexico

Compiere starts up on Hsqldb

Postby neilg » Fri Mar 10, 2006 9:31 am

I've got Compiere starting up on Hsqldb. And you can edit a business partner and create a sales order and thats about all.

See the url http://www.knowledgesuccess.com there is a link to sourceforge where you can download the source code.

The SQL compatibility is the biggest problem. I've tried two approaches (actually combined them) - patching a bit of Hsqldb and patching a bit of Compiere.
neilg
Regular
 
Posts: 141
Joined: Thu Jun 16, 2005 1:58 pm

Client or server

Postby neilg » Fri Mar 10, 2006 9:39 am

Compiere.startup(false/true) -- false = client true = server

As in
Code: Select all
public static synchronized boolean startup (boolean isClient)
.

This is called with different value for isClient depending on if we are client or server. The main method in Compiere starts up as client.

And regarding the access and startup directories first time I noticed them myself.
neilg
Regular
 
Posts: 141
Joined: Thu Jun 16, 2005 1:58 pm

Postby gerald » Sat Mar 11, 2006 5:12 am

Thanks for your responses. It appears Compiere's database independence architecture will make it difficult to port to Derby. Using ConvertMap's regular expression method of converting SQL implementation does not guarantee an arbitrary DBMS's SQL implementation can be mapped from Oracle. I will have to document my problems and have someone else from my organization look over this. If you can answer anymore of my questions, please do because the modifications, to Compiere to allow it to use Derby, will be contributed to Compiere's CVS repository.

Gerald
gerald
 
Posts: 5
Joined: Wed Feb 22, 2006 5:10 am

DB Independance

Postby neilg » Sat Mar 11, 2006 11:32 pm

I'm busy looking at ConvertMap now. First time I've heard of it !

I've documented all my trials and tribulations in converting to Hsqldb in http://www.knowledgesuccess.com/local/doc/docbook/guide/guide.html please could you look over it maybe we can collaborate somewhere.

I worked out the idea of translating Sql would be more practical than patching the target database (quite late in the excercise actually). Maybe a combination of the approaches would work.

I'm presently stuck on this piece of ugly sql code:

Code: Select all
UPDATE C_Invoice i
       SET (Description,POReference)=
         (SELECT Description,POReference "
         FROM C_Order o WHERE i.C_Order_ID=o.C_Order_ID)
      WHERE DocStatus NOT IN ('RE','CL') AND C_Order_ID=1


I've decided to write a translator / mapper for it to map to two different queries because Hsql allows update of one column with subquery.

This SQL runs when you try and complete an Invoice.
gerald wrote:Thanks for your responses. It appears Compiere's database independence architecture will make it difficult to port to Derby. Using ConvertMap's regular expression method of converting SQL implementation does not guarantee an arbitrary DBMS's SQL implementation can be mapped from Oracle. I will have to document my problems and have someone else from my organization look over this. If you can answer anymore of my questions, please do because the modifications, to Compiere to allow it to use Derby, will be contributed to Compiere's CVS repository.

Gerald
neilg
Regular
 
Posts: 141
Joined: Thu Jun 16, 2005 1:58 pm

Postby gerald » Tue Mar 14, 2006 6:25 am

Neil, I read from your documentation that you have been patching HSQL and Compiere. I have never tried that with Derby and I will probably not be able to modify the Derby code because I do not have enough time in my co-op work placement to do something that large. Your documentation is very detailed. I wished I read your documentation when I was starting to read the Compiere code. I had been relying on searching for the "oracle" string to guide me through the database reliant java files.

I can't find any examples of other DBMS porting in Compiere to figure out how other DBMS support in Compiere handle non-existent SQL functions. Derby is similar to DB2 in their SQL structure. A problem in porting to Derby is the stored procedure structure in Derby does not follow anything similar to other DBMS. This means regular expressions cannot do the trick and stored procedures have to be re-written in Derby's stored procedure structure.

I might get back on working on porting Compiere to Derby depending on what my manager has planned.
gerald
 
Posts: 5
Joined: Wed Feb 22, 2006 5:10 am

Hsqldb - got surprisingly far

Postby neilg » Tue Mar 14, 2006 6:59 am

Hi Gerald,

Yes I seem to have got surprisingly far with Hsqldb. The main thing is that Compiere no longer uses stored procedures it uses Sqlj. So your db just needs to support Sqlj.

I think it is a big task though. The list of oracle specific pl/sql functions is quite long...

Regards Neil
neilg
Regular
 
Posts: 141
Joined: Thu Jun 16, 2005 1:58 pm

sql update converter

Postby armen » Fri Mar 17, 2006 12:59 pm

Neil,

we've got stuck also in this very same problem when try porting to PostgreSQL.

Here is some ideas, create a converter function that will be called in ExecuteUpdate in DB.java

public String convertUpdateSQL(String sql) {
if (sql.substring(0,6).equals("UPDATE")
{
//insert your logic here
}
return sql;
}

I am happy if I could help.

cheers,

Armen

Starprise - Compiere for Indonesia project
http://starprise.sourceforge.net
Goodwill Consulting
Jakarta, Indonesia
armen
 
Posts: 3
Joined: Fri Jun 10, 2005 4:04 pm

Re: sql update converter

Postby neilg » Sat Mar 18, 2006 2:45 am

Hi Armen,

I've got a solution under http://knowledgesuccess.com and now a sales order completes successfully under hsqldb.

You can get the source code from the sourceforge CVS server.

Please see org.hsqldb.CtfOracleSubstituter.translateMulticolumnUpdate on the CVS server (HEAD) for details.

Yes I would welcome any and all help and you're welcome to use any code from my project if it will help you (remember though it is GPL license).

Thank you.

Neil

armen wrote:Neil,

we've got stuck also in this very same problem when try porting to PostgreSQL.

Here is some ideas, create a converter function that will be called in ExecuteUpdate in DB.java

public String convertUpdateSQL(String sql) {
if (sql.substring(0,6).equals("UPDATE")
{
//insert your logic here
}
return sql;
}

I am happy if I could help.

cheers,

Armen

Starprise - Compiere for Indonesia project
http://starprise.sourceforge.net
neilg
Regular
 
Posts: 141
Joined: Thu Jun 16, 2005 1:58 pm

Worthwhile pursuit?

Postby neilg » Tue Apr 04, 2006 3:54 am

I've been wondering how worthwhile this project really is....

In terms of providing a test framework I think it would be invaluable. So for anyone doing any kind of dev it would be great and make Compiere more portable.

The database conversions are ugly though. What we are doing is writing a parser to parse some written-on-the-fly oracle specific code to something another db will tolerate. (This is in order to get the big prize - compiere running on a db like hsql). So we must first understand (parse) the sql statement.

Where we are now - some pointers for other people - ldbc (sourceforge) already did the work of taking an antlr (sourceforge) lexer/parser written for oracle 7 and writing an adapter to sanatize the code. Needs some mods... one of the most difficult ones is the rownum function in oracle. Then again lets say you translate some sql and move the placeholders (?) around? What a headache.

With the rownum function ... a statement

Code: Select all
select rownum, myvalue from mytable where rownum=1


in oracle should only return the first row. We converted this code to:

Code: Select all
select 1 as rownum, myvalue from mytable where rownum=1


(in the ldbc adapter). Then all rows return one and all would have been selected... unless we hadn't first patched the resultset to have a maximumsize of 1... but only when we sense 'rownum' in the where clause.

Thats the sum total of the solution and will only work for one case.

The other solution was more elegant ... patching hsqldb itself to provide a rownum.. but it only takes so long patching a product and then reapplying patches with new releases when one becomes tired of this method.

Actually we could convert the framework into a real test framework now as long as the java code we are testing stayed away from compiere objects. Not really a reasonable request.

When you are thinking... well most of this sql code shouldn't exist here anyway... it should be in an o/r layer or not sql at all.
(Reminder to self... never a stitch of important code without writing a test case first)

This is an interesting excercise for the sake of itself though and the subject of a few potential commercial products (weighing dev effort against product life expectancy and market size). And for some reason has generated quite some traffic... which makes it worthwhile on its own.

P.S. the knowledgesuccess site is down for a few days...
neilg
Regular
 
Posts: 141
Joined: Thu Jun 16, 2005 1:58 pm

Fyracle reviewed ...

Postby neilg » Wed Apr 05, 2006 5:57 am

Fyracle has been reviewed (briefly) and the knowledgesuccess site is back online again. Please take part in the surveys and contribute content. All are welcome and encouraged.

Porting to hsqldb has been dropped because it is futile . Due diligence would have discovered this before the fact. (See previous porting efforts and ones with degrees of success).

The site has a new theme open source crm erp in general honest open discussion. No holds barred no punches pulled. Sorry guys. All previous content still available.
neilg
Regular
 
Posts: 141
Joined: Thu Jun 16, 2005 1:58 pm

Postby trifon » Sat Apr 08, 2006 1:11 am

Greeting dear database indepenece fans.

I woul like just to ask you some questions and i would be happy to have some answers.

1) Do you really belive that anyone from Compiere Inc. is able to provide/develop database independece for Compiere. I know that they have been paid good amount of money to do it and after 2 years result is still negative. So do you really believe that they can do it now?

2) Why should anyone invest money and time for DB independence when there is Oracle XE and Compiere can run on it(at least i managed to run it). So for small companies Oracle XE will give cheap ERP solutions and for big companies money invested in Oracle support are not so big problem.

This time i'm from the side posting questions and answers are appreciated.

Regards,
Trifon
Trifon Trifonov
trifon
Senior
 
Posts: 324
Joined: Thu Dec 23, 2004 11:20 pm
Location: Bulgaria, Sofia

No

Postby neilg » Sat Apr 08, 2006 2:58 am

My short answer is NO.

I think Compiere saw an opening in the Open source CRM/ERP market and they went at it with what they had. Which was oracle dependent code.

They would have to rewrite their persistence layer and rework compiere into a M-V-C style framework. Then it will be a different product and the opportunity has passed. Compiere is waiting for the pretender to the throne for example ofbiz which will already run on derby mysql postgress.

I can't believe they expect us to adopt SQL translation as a database independence strategy. This would take a large commercial effort as I discovered in my recent experiment with Hsqldb.
neilg
Regular
 
Posts: 141
Joined: Thu Jun 16, 2005 1:58 pm

Postby trifon » Sat Apr 08, 2006 3:25 am

Thank's for the answer Neil,

did you saw my comments at http://www.knowledgesuccess.com/compiere/?

Personaly i do not belive that Compiere Inc. can do such thing.

Also strategy they choosed with SQLJ is bringing more problems, generally problems with speed and also it is very stupid to put SQL code inside Java code.
They could choose to use iBatis and separate SQL from java but obviously they do not see it.

I think that future is demanding xml based framework with some of the capabilities of Compiere. Probably a mix between Compiere, OFBiz and OpenXava.

Regards,
Trifon
Trifon Trifonov
trifon
Senior
 
Posts: 324
Joined: Thu Dec 23, 2004 11:20 pm
Location: Bulgaria, Sofia


Return to Strictly Technical

Who is online

Users browsing this forum: No registered users and 2 guests

cron