red1.org

gerald · Joined: 22 Feb 2006 Posts: 5

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 · Joined: 22 Feb 2006 Posts: 5

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

moyses · Joined: 01 Sep 2004 Posts: 41 Location: Mexico

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

http://www.networkworld.com/news/2006/013106-compiere.html

neilg · Regular Joined: 16 Jun 2005 Posts: 141

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 Joined: 16 Jun 2005 Posts: 141

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

As in

gerald · Joined: 22 Feb 2006 Posts: 5

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 Joined: 16 Jun 2005 Posts: 141

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:

gerald · Joined: 22 Feb 2006 Posts: 5

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.

neilg · Regular Joined: 16 Jun 2005 Posts: 141

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

armen · Joined: 10 Jun 2005 Posts: 3

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

neilg · Regular Joined: 16 Jun 2005 Posts: 141

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

neilg · Regular Joined: 16 Jun 2005 Posts: 141

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

neilg · Regular Joined: 16 Jun 2005 Posts: 141

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.

trifon · Posted: Sat Apr 08, 2006 1:11 am Post subject:

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

neilg · Regular Joined: 16 Jun 2005 Posts: 141

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.

trifon · Posted: Sat Apr 08, 2006 3:25 am Post subject:

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