[Engine-devel] Move SQL out of stored procedures

[Itamar Heim]

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