Re: [Engine-devel] Move SQL out of stored procedures

From: "Libor Spevak" <lspevak(a)redhat.com&gt; To: "Itamar Heim" <iheim(a)redhat.com&gt; Cc: "Juan Hernandez" <jhernand(a)redhat.com&gt;, engine-devel(a)ovirt.org Sent: Thursday, March 28, 2013 4:04:20 PM Subject: Re: [Engine-devel] Move SQL out of stored procedures Hi, apart from SQL vs. stored procedures discussion, I am trying to understand what we can get if we support more databases... Some points: 1. Is there a real need by end-users/customers to run it on e.g. Oracle only? (performance, stability, easier administration).

What is the future of PostgreSQL? 2. Is it decided by architectural board, what kind of databases we would like to support? (cannot support any db)

3. Are we talking about the Engine only, or there will be a need to rewrite ETL mappings and upgrade DWH database, or maybe modify JasperReports templates (simply, some DB types behave differently)? Maybe we can look at JasperSoft solution, they support more databases. 4. Current full/incremental upgrade process of PostgreSQL is IMHO very good tuned (it is similar to dbmaintain.org tool - Java implementation - I used successfully on one project - after some changes of course). I do not believe we can use or easily develop general upgrade/migration tool, and XML based (I am sorry Alissa, not sure about Liquibase, I haven't studied it deeply, but there is a need to incrementally change db objects, but sometimes also to migrate data to new structures, the most flexible and quickest is to do it using native SQL, but yes, it depends on the project needs...). 5. As a developer, with every new column I need to write upgrade scripts, prepare test environments and test all scenarios several times on different databases, so time-consuming. On 27.3.2013 13:53, Itamar Heim wrote: > On 03/26/2013 08:39 PM, Alon Bar-Lev wrote: >> >> >> ----- Original Message ----- >>> From: "Juan Hernandez" <jhernand(a)redhat.com&gt; >>> 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. >> >> 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(a)ovirt.org > http://lists.ovirt.org/mailman/listinfo/engine-devel _______________________________________________ Engine-devel mailing list Engine-devel(a)ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel