[Engine-devel] Move SQL out of stored procedures

[Juan Hernandez]

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

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

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

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

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

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

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