Just thought I'd drop a note to outline some example problems I've been having while trying to port to SQLServer/2000 in case anyone else underestimates how difficult it is.
I've got the seed data loaded, and I have a version of Compiere running, but....
1) The SQL used in Compiere is peppered with Oracle-specific stuff like "FROM DUAL" and "ROWID". Also it uses non-standard outer join syntax "(+)". None of these work in SQLServer.
2) Other stuff has to be translated (e.g. "COALESCE" to "CASE", "SYSDATE" to "GETDATE()", "NVL" TO "ISNULL" etc.).
3) SQLServer's error handling is nowhere near as advanced as Oracle's.
4) SQLServer's trigger model is completely different to Oracle's. Each trigger has to be painstakingly re-written.
5) SQL Server doesn't support BEFORE triggers.
6) SQLServers function support is rubbish. Compiere uses such things as recursive calls etc. SQLServer severely limits what you can do in a function.
7) General syntax differences like SQLServer variable names have to begin with a "@" and have to be explicitly declared with a "DECLARE", "SELECT" into a variable etc. (there are loads of other differences).
Hope that helps.
your experince is very useful.
I think it enforces the idea that porting Compiere to a multiple database architecture (like JBoss + OFBiz) is the best one.
By reading the forum I guess there are many people doing the port by themself. So there is the need of some coordination, basically to see what has been done up to now and then not to overlap developers job.
Probably the best is to have a very detailed to do list, but in order to have a to do list we have to know exactly what CMP needs . I mean: do it needs a sort of standard sql, transaction, triggers and so on?
> Probably the best is to have a very detailed to do list, but
> in order to have a to do list we have to know exactly
> what CMP needs . I mean: do it needs a sort of standard
> sql, transaction, triggers and so on?
so far, but with limited experience in that area, I've never come across a portable SQL dialect that supports all of the features you mention. If portability is desired, my guess is that one needs to refrain from using the advanced features any SQL data base has to offer, or to detect the currently-used data base and provide individual code for each brand of SQL. The latter generally seems to be the way to faster applications and more maintenance hassle as well, chewing up the scarce resource of developer man power...
>to detect the currently-used data base and
>provide individual code for each brand of SQL.
mmmhhh that's the hardest way for developers!
Anyway I'm curious to know which is the opinion of all the people involved in developing Compiere and which idea they have about the route to make Compiere "Oracle-free"
> >to detect the currently-used data base and
> >provide individual code for each brand of SQL.
> mmmhhh that's the hardest way for developers!
Yes, of course. Please note that I don't specifically
advocate going this route, I only note that this is
what eg. your typical PHP application that you
can download on the Internet does:
- You set a config variable like $dbtype = "oracle";
and the program does something like
Not too elegant, and mostly this is only done to accommodate the different APIs in the first place and NOT, as is the primary topic of this discussion, to arrive at data base engine independency at the SQL level.
But it's one idea on how to do it, however practical
it may be (ie, if killing all advanced SQL features would
result in severe performance problems, there may be
no good alternative, but whether it does, I don't know).
When I started thinking to get involved in this project the strategy was this: moving the database (oracle) access in a middle tier and developing a new middle tier for Postgres. This is the best from a software performance point of view.
Then the strategy has changed for a more general implementation of a database indipendent middle tier (something connected to JBoss)
I've just donwloaded JBoss quick start guide. Chapter 9 is explaining CMP 2.0 technology. I will study it next WE, so I will have a clearer idea about the instruments we can use.
Unfortunately JBoss docs are not free and only the quick start guide is free.
Bye for now
> Unfortunately JBoss docs are not free and only the quick start guide is free.
Their web page says that this is part of their business
model. Afair the total cost for all JBoss docs combined
is somewhere in the range of $150, so this shouldn't
be a severe problem if you see a sound business model
behind using it.
I have been involved with MS SQL Server and Oracle DSMS in the past, and I could see how different an non-standard they are. Any attemp to automatically convert from the one to the other and viceversa will fail, or will be too expensive, because this is what their respective designers wanted to obtain: a "slave" user base that pays the due fees regularly.
As far as Compiere is concerned I think that, if what is intended is to make it database provider independant, is to write an intermediate layer that resides between the application and the real data base (My SQL, Postgre, etc). All triggers, functions and stored procedures that are presently written using PL/SQL should be rewritten in this environment using a standard language and pure SQL-92 statements.
This may not be the nicest method but I think it is the most realistic one, on the one that allows to use most Compiere software and benefit from the bussines knowledge that is buil into the package.
Continuation of my last message:
Writting an intermediate layer that allows a free database to "emulate" oracle is also the only way for Compiere ports to other data bases to benefit from maintenance and future releases of the application software, that is the important part of the game.
Seems to me that Ramon's suggestion is the best content I have read so far in this forum (only just started reading today!).
Does anyone else have any views?
Has Jorge indicated what methodology he is planning to use?
Is it really going to be released next month? (Q4)
You can read about a Compiere Implementation framework at www.leo-systems.com .
A couple of comments...
Vipen - I tried to open the URL in your message but received a DNS failure message. Is
"leo-systems" generally available?
Peter B - Are you aware of the COALESCE statement in SQL Server and if so, why is it necessary to convert Oracle COALESCE statements to SQL Server CASE statements?
Yes www.leo-systems.com has been online for the past 13 months. It is an ISP hosted site. Thank you for bringing this to my attention. Let me follow up with the ISP.
I was trying to port it to DB2 and convert most of the SQL to DB2. why not have a team with every body's experience port it to all the database or crate something independant with the best technology in place.
Hugo @ firstname.lastname@example.org
This is also my opinion. If the source code was more SQL 99 compliant, it should run on Oracle, DB2 and PostgreSQL.
We are currently comparing Object Relational Mappers and the result in short is
- Hibernate (Open Source) vs.
- TopLink (commercial, acquired by Oracle)
So far Hibernate is favorite:
- open source
- supports 23(!) DBs
- will be integrated into jBoss, see
I'm afraid there is much of the SQL Code
to be rewritten but it would mean
ultimate db independence.
I cannot predict about the performance but my feeling is good: Hibernate comes with some sort of Meta-Object-SQL which is compiled into optimized SQL code for each supported DB. Chances are good that some operations even gain performance.
This as an alternative to 'just' port to postgreSQL.
I have some experience with hibernate and it is an awesome mapper, it has loads of cool features, including pluggable cache and of course the supports different DB dialects. When I heard people talk about the possibility of porting it to Ofbiz I think Hibernate is a MUCH better idea!!
I would gladly help, but would like to hear Mr Jankes idea..
One issue that comes to mind is that Hibernate is designed to persist objects that map to tables (functional objects). From my understanding this is not the case with Compiere, (there is no Payment or Invoice object)!
Log in to post a comment.