[Engine-devel] Move SQL out of stored procedures

[Alon Bar-Lev]

----- Original Message -----
> From: "Juan Hernandez" <jhernand at redhat.com>
> To: "Alon Bar-Lev" <alonbl at redhat.com>
> Cc: engine-devel at ovirt.org, "Eli Mesika" <emesika at redhat.com>, "Yair Zaslavsky" <yzaslavs at redhat.com>
> Sent: Wednesday, March 27, 2013 9:52:35 AM
> Subject: Re: [Engine-devel] Move SQL out of stored procedures
> 
> On 03/26/2013 07: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.
> >
> 
> You probably mean "any other database that supports stored
> procedures",
> which is not the same that "any other database".

Right.

> 
> It is very clear what is the interface of a relational database: a
> set
> of relations with a set of restrictions.

We can find a lot of definitions, there is a trend no of nosql... which falls into the above...

> 
> > This way the database specific implementation lives within the
> > database and maintained by the designated dba.
> 
> I don't now exactly what you mean by "dba", but if you mean "database
> administrator" I really don't see typical database administrators
> rewriting stored procedures provided by a product to suite their own
> database management system.
> 
> Maybe by "dba" you mean "the developer of the persistence layer".
> Will
> your proposal be maintaining different sets of stored procedures,
> written in different languages for different database management
> systems?

Yes, this what I mean.

> 
> > Fixups and optimizations can be done in database without touching
> > the code.
> 
> Seems that you think that stored procedures aren't "code". What are
> they
> then?

Yes they are, stored procedure are code, which is database dependent, with pre-defined interface to the external world.

The language you chose depends on database capabilities, for Oracle and DB2 you can write stored procedure in Java...

This code is part of application, it is maintained within the same release milestones, commits etc.

> 
> > 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).
> >
> 
> It also means that you make the database a procedural system, and it
> shouldn't be. A database should not contain logic, only data. Logic
> changes quite frequently and data needs to survive for a long long
> time.

This is your definition... I think the opposite... database should contain logic, it is procedural system. Implementing the logic within database enable you to enjoy the performance provided by the database, and simplifying your application.

This logic is part of your code, a change in application derives a change in the database layer as well, nothing prevents you in changing logic.

> 
> > 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.
> 
> I've seen exactly the opposite, if that matters.

Yes, I've seen the opposite in simple applications.

And I understand the need of the developers to control everything, not distributing logic to other components and technologies which are out of reach of the common developer. The undesired dependency with dba (data model layer developer) for every change in schema or entity.

When optimization, porting or backward/forward compatibility is required the problem falls at the developer's side and usually solved with less knowledge or flexibility.

Just wanted to step in and write that...

As I know what most of the Java developer will probably prefer.

Bottom line, it is all a question of how complex our data model is, and what performance we need out of the data layer. If the model is simple and the performance are insignificant, by all mean, use hibernate.

While we discuss that, I think that best if statistics (or any data which is rolling) will be written to nosql database instead of sql database as there is much less overhead, storage and the need for vacuum and such, it is not a matter of postgesql/mysql I have experience with Oracle and DB2 not coping with rolling data.

Thanks,
Alon.