Porting SQL

Peter B
2002-10-18
2004-04-01
  • Peter B

    Peter B - 2002-10-18

    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....

    General
    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.

    Triggers
    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.

    Functions
    6) SQLServers function support is rubbish. Compiere uses such things as recursive calls etc. SQLServer severely limits what you can do in a function.

    Stored Procedures
    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.

    Peter

     
    • Vincenzo

      Vincenzo - 2002-10-18

      Hi Peter,
      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?

      Vincenzo

       
      • Toni Mueller

        Toni Mueller - 2002-10-18

        Hi,
        > 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...

         
    • Vincenzo

      Vincenzo - 2002-10-18

      >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"

      Vincenzo

       
    • Toni Mueller

      Toni Mueller - 2002-10-18

      > >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
      include "$dbtype.php";

      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).

       
      • Vincenzo

        Vincenzo - 2002-10-18

        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
        Vincenzo

         
      • Vincenzo

        Vincenzo - 2002-10-18

        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
        Vincenzo

         
    • Toni Mueller

      Toni Mueller - 2002-10-18

      > 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.

       
    • Ramon Rovira

      Ramon Rovira - 2002-10-19

      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.

      Ramon

       
    • Ramon Rovira

      Ramon Rovira - 2002-10-19

      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.

      Ramon

       
    • Graeme Thomson

      Graeme Thomson - 2003-09-16

      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)

      Graeme

       
      • Vipen Mahajan

        Vipen Mahajan - 2003-09-17

        Hi,

        You can read about a Compiere Implementation framework at www.leo-systems.com .

        Vipen

         
    • Graeme Thomson

      Graeme Thomson - 2003-09-18

      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?

      Graeme

       
      • Vipen Mahajan

        Vipen Mahajan - 2003-09-18

        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.
        Vipen

         
    • Hugo Cantave-Jean

      Hi Everybody,

      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 @ hcantave@attglobal.net

       
      • Jean-Michel POURE

        This is also my opinion. If the source code was more SQL 99 compliant, it should run on Oracle, DB2 and PostgreSQL.

         
    • Frerk Meyer

      Frerk Meyer - 2003-11-18

      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
      - fast
      - flexible
      - will be integrated into jBoss, see
      http://www.hibernate.org/136.html

      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.

      Any comment?

      Frerk

       
    • Mauricio Hernandez

      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.