11:19 a.m.
----- Original Message -----
From: "Juan Hernandez" <jhernand(a)redhat.com>
To: "Alon Bar-Lev" <alonbl(a)redhat.com>
Cc: engine-devel(a)ovirt.org, "Eli Mesika" <emesika(a)redhat.com>, "Yair
Zaslavsky" <yzaslavs(a)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(a)redhat.com>
>> To: engine-devel(a)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.