[Engine-devel] Move SQL out of stored procedures

[Alissa Bonas]

I suggest that there should be 2 different (though related) issues to consider here in this thread:

1. How the code works with database in runtime (CRUD) - and this is what the Hibernate/JPA/etc. discussion is so far about.
2. Maintenance/upgrade/modification of the database schema - which is also very important, done pretty frequently in this project, and is also sometimes database vendor sensitive (for example - column types/indexes differ, syntax differs a bit). Doing changes in the database structure is not done in runtime, but should be still very easy and maintainable for the developers (and transparent to them wherever possible to hide vendor differences). 
I had good experience with Liquibase for creating/upgrading database schemas so I propose to evaluate it.
http://www.liquibase.org/

----- Original Message -----
> From: "Itamar Heim" <iheim at redhat.com>
> To: "Alon Bar-Lev" <alonbl at redhat.com>
> Cc: "Juan Hernandez" <jhernand at redhat.com>, engine-devel at ovirt.org
> Sent: Wednesday, March 27, 2013 2:53:45 PM
> Subject: Re: [Engine-devel] Move SQL out of stored procedures
> 
> On 03/26/2013 08:39 PM, Alon Bar-Lev wrote:
> >
> >
> > ----- 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.
> 
> there may be db specific optimization/logic, but I don't see why we
> need
> STPs for 80% (if not more) of the CRUD and basic queries.
> 
> I also agree with Tal later in the thread that its a good question if
> we
> can't find a better solution than re-writing the sql's in the code
> 
> _______________________________________________
> Engine-devel mailing list
> Engine-devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel
>