[Engine-devel] Move SQL out of stored procedures

[Mike Kolesnik]

----- Original Message -----
> On 03/27/2013 01:36 PM, Mike Kolesnik wrote:
> > ----- Original Message -----
> >> Hello,
> >>
> >> according to my experiences Hibernate/JPA is the best solution for
> >> application
> >> which has to support multiple databases.
> >
> > +1
> >
> > JPA would be much easier to maintain than the current approach.
> > In most cases the stored procedures we use are for CRUD operations,
> > and can be easily replaced.
> > The exceptions can be dealt with when necessary, but generally it
> > seems like an excellent direction to me.
> >
> >> Even when I was part of the
> >> team who
> >> migrated application with business login written in Oracle PL/SQL
> >> procedures
> >> to JBoss using Hibernate (application ran only on Oracle), it
> >> became
> >> much easier
> >> to maintain this applications and also customer was pleased that
> >> application
> >> ran much better.
> >>
> >> Now imagine the scenario, that for example Postgresql, MySQL,
> >> Oracle
> >> and MS SQL would be
> >> supported. I you need to change some stored procedure you should
> >> do
> >> this on 4 places using
> >> 4 different database dialects.
> >>
> >> Like any other technologies, Hibernate/JPA has some drawbacks, but
> >> when it's used properly
> >> and database objects are redesigned to fit Hibernate and
> >> portability
> >> needs, it works fine.
> >
> > I don't think our DB/POJO design is very problematic in this
> > regard..
> > I think we can replace most of the existing DAOs with ORM backed
> > implementations with very little work.
> >
> > What we need to make sure is not break the DAO API.
> > For example, if I fetch an entity from a Session,
> > it would reflect any change that happens to it automatically to the
> > DB.
> > This is not how the current API works, so this feature should be
> > disabled
> > or otherwise we would have a hard time hunting the bugs that will
> > spawn
> > from this change of behavior.
> >
> 
> This is in my opinion the main disadvantage of using Hibernate (or
> any
> other JPA implementation) with our current architecture. However
> Hibernate provides the stateless session concept, which is not
> standard
> but could help.

Alternatively, we could detach from session on fetch, and re-attach
on save/update.

Anyway, it still adds the benefit of ORM which would still simplify
much of the code, and provide the desired portability.

Also I think if we move to the direction of ORM, it would be easier
to change the rest of the application code to behave differently,
should we choose to do it.

> 
> >>
> >>
> >>
> >> Martin Perina
> >>
> >>
> >> ----- Original Message -----
> >>> From: "Alon Bar-Lev" <alonbl at redhat.com>
> >>> To: "Juan Hernandez" <jhernand at redhat.com>
> >>> Cc: engine-devel at ovirt.org
> >>> Sent: Tuesday, March 26, 2013 7:39:16 PM
> >>> Subject: Re: [Engine-devel] Move SQL out of stored procedures
> >>>
> >>>
> >>>
> >>> ----- Original Message -----
> >>>> From: "Juan Hernandez" <jhernand at redhat.com>
> >>>> To: engine-devel at ovirt.org
> >>>> Sent: Tuesday, March 26, 2013 7:34:04 PM
> >>>> Subject: [Engine-devel] Move SQL out of stored procedures
> >>>>
> >>>> Hello,
> >>>>
> >>>> I would like to start a discussion about the subject. I think
> >>>> this
> >>>> is
> >>>> something we need to do if one day we want to be able to use any
> >>>> database other than PostgreSQL.
> >>>
> >>> Hello,
> >>>
> >>> I think that database layer is a software interface like any
> >>> other
> >>> software interface, if done properly, a dba can convert the
> >>> stored
> >>> procedure to any other database without any code change.
> >>>
> >>> This way the database specific implementation lives within the
> >>> database and maintained by the designated dba.
> >>>
> >>> Fixups and optimizations can be done in database without touching
> >>> the
> >>> code.
> >>>
> >>> Backward compatibility layer is much simpler to implement based
> >>> on
> >>> stored procedures than complex set of views and tables.
> >>>
> >>> Also, accessing the database via different technologies is
> >>> simpler
> >>> if
> >>> there is maintained database interface (stored procedures).
> >>>
> >>> I've seen hibernate based java applications that promised to be
> >>> database independent but at the edges when performance counts,
> >>> the
> >>> DAO became HQL, then a special dialect and finally database
> >>> specific
> >>> SQLS.
> >>>
> >>> Regards,
> >>> Alon Bar-Lev.
> >>> _______________________________________________
> >>> Engine-devel mailing list
> >>> Engine-devel at ovirt.org
> >>> http://lists.ovirt.org/mailman/listinfo/engine-devel
> >>>
> >> _______________________________________________
> >> Engine-devel mailing list
> >> Engine-devel at ovirt.org
> >> http://lists.ovirt.org/mailman/listinfo/engine-devel
> >>
> 
> 
> --
> Dirección Comercial: C/Jose Bardasano Baos, 9, Edif. Gorbea 3, planta
> 3ºD, 28016 Madrid, Spain
> Inscrita en el Reg. Mercantil de Madrid – C.I.F. B82657941 - Red Hat
> S.L.
>