1:34 p.m.
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.
I did an small example of what it takes and how it looks like to have
the SQL code into the DAOs:
http://gerrit.ovirt.org/13347
It isn't rocket science, it isn't an exciting task, it isn't fun, but
something I think we should eventually do.
I appreciate any comment about how and when to do this, including those
saying that instead of this primitive approach we should use this or
that ORM framework.
Regards,
Juan Hernandez
--
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.
2:39 p.m.
----- 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.
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.
Regards,
Alon Bar-Lev.
1:59 a.m.
Hello,
according to my experiences Hibernate/JPA is the best solution for application
which has to support multiple databases. Even when I was part of the team who
migrated application with business login written in Oracle PL/SQL procedures
to JBoss using Hibernate (application ran only on Oracle), it became much easier
to maintain this applications and also customer was pleased that application
ran much better.
Now imagine the scenario, that for example Postgresql, MySQL, Oracle and MS SQL would be
supported. I you need to change some stored procedure you should do this on 4 places
using
4 different database dialects.
Like any other technologies, Hibernate/JPA has some drawbacks, but when it's used
properly
and database objects are redesigned to fit Hibernate and portability needs, it works
fine.
Martin Perina
----- Original Message -----
From: "Alon Bar-Lev" <alonbl(a)redhat.com>
To: "Juan Hernandez" <jhernand(a)redhat.com>
Cc: engine-devel(a)ovirt.org
Sent: Tuesday, March 26, 2013 7:39:16 PM
Subject: Re: [Engine-devel] Move SQL out of stored procedures
----- 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.
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.
Regards,
Alon Bar-Lev.
_______________________________________________
Engine-devel mailing list
Engine-devel(a)ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-devel
8:36 a.m.
----- Original Message -----
Hello,
according to my experiences Hibernate/JPA is the best solution for
application
which has to support multiple databases.
+1
JPA would be much easier to maintain than the current approach.
In most cases the stored procedures we use are for CRUD operations,
and can be easily replaced.
The exceptions can be dealt with when necessary, but generally it
seems like an excellent direction to me.
Even when I was part of the
team who
migrated application with business login written in Oracle PL/SQL
procedures
to JBoss using Hibernate (application ran only on Oracle), it became
much easier
to maintain this applications and also customer was pleased that
application
ran much better.
Now imagine the scenario, that for example Postgresql, MySQL, Oracle
and MS SQL would be
supported. I you need to change some stored procedure you should do
this on 4 places using
4 different database dialects.
Like any other technologies, Hibernate/JPA has some drawbacks, but
when it's used properly
and database objects are redesigned to fit Hibernate and portability
needs, it works fine.
I don't think our DB/POJO design is very problematic in this regard..
I think we can replace most of the existing DAOs with ORM backed
implementations with very little work.
What we need to make sure is not break the DAO API.
For example, if I fetch an entity from a Session,
it would reflect any change that happens to it automatically to the DB.
This is not how the current API works, so this feature should be disabled
or otherwise we would have a hard time hunting the bugs that will spawn
from this change of behavior.
Martin Perina
----- Original Message -----
> From: "Alon Bar-Lev" <alonbl(a)redhat.com>
> To: "Juan Hernandez" <jhernand(a)redhat.com>
> Cc: engine-devel(a)ovirt.org
> Sent: Tuesday, March 26, 2013 7:39:16 PM
> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>
>
>
> ----- 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.
>
> 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.
>
> Regards,
> Alon Bar-Lev.
> _______________________________________________
> 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
9:35 a.m.
On 03/27/2013 01:36 PM, Mike Kolesnik wrote:
----- Original Message -----
> Hello,
>
> according to my experiences Hibernate/JPA is the best solution for
> application
> which has to support multiple databases.
+1
JPA would be much easier to maintain than the current approach.
In most cases the stored procedures we use are for CRUD operations,
and can be easily replaced.
The exceptions can be dealt with when necessary, but generally it
seems like an excellent direction to me.
> Even when I was part of the
> team who
> migrated application with business login written in Oracle PL/SQL
> procedures
> to JBoss using Hibernate (application ran only on Oracle), it became
> much easier
> to maintain this applications and also customer was pleased that
> application
> ran much better.
>
> Now imagine the scenario, that for example Postgresql, MySQL, Oracle
> and MS SQL would be
> supported. I you need to change some stored procedure you should do
> this on 4 places using
> 4 different database dialects.
>
> Like any other technologies, Hibernate/JPA has some drawbacks, but
> when it's used properly
> and database objects are redesigned to fit Hibernate and portability
> needs, it works fine.
I don't think our DB/POJO design is very problematic in this regard..
I think we can replace most of the existing DAOs with ORM backed
implementations with very little work.
What we need to make sure is not break the DAO API.
For example, if I fetch an entity from a Session,
it would reflect any change that happens to it automatically to the DB.
This is not how the current API works, so this feature should be disabled
or otherwise we would have a hard time hunting the bugs that will spawn
from this change of behavior.
This is in my opinion the main disadvantage of using Hibernate (or any
other JPA implementation) with our current architecture. However
Hibernate provides the stateless session concept, which is not standard
but could help.
>
>
>
> Martin Perina
>
>
> ----- Original Message -----
>> From: "Alon Bar-Lev" <alonbl(a)redhat.com>
>> To: "Juan Hernandez" <jhernand(a)redhat.com>
>> Cc: engine-devel(a)ovirt.org
>> Sent: Tuesday, March 26, 2013 7:39:16 PM
>> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>>
>>
>>
>> ----- 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.
>>
>> 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.
>>
>> Regards,
>> Alon Bar-Lev.
>> _______________________________________________
>> 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
>
--
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.
10:04 a.m.
----- Original Message -----
On 03/27/2013 01:36 PM, Mike Kolesnik wrote:
> ----- Original Message -----
>> Hello,
>>
>> according to my experiences Hibernate/JPA is the best solution for
>> application
>> which has to support multiple databases.
>
> +1
>
> JPA would be much easier to maintain than the current approach.
> In most cases the stored procedures we use are for CRUD operations,
> and can be easily replaced.
> The exceptions can be dealt with when necessary, but generally it
> seems like an excellent direction to me.
>
>> Even when I was part of the
>> team who
>> migrated application with business login written in Oracle PL/SQL
>> procedures
>> to JBoss using Hibernate (application ran only on Oracle), it
>> became
>> much easier
>> to maintain this applications and also customer was pleased that
>> application
>> ran much better.
>>
>> Now imagine the scenario, that for example Postgresql, MySQL,
>> Oracle
>> and MS SQL would be
>> supported. I you need to change some stored procedure you should
>> do
>> this on 4 places using
>> 4 different database dialects.
>>
>> Like any other technologies, Hibernate/JPA has some drawbacks, but
>> when it's used properly
>> and database objects are redesigned to fit Hibernate and
>> portability
>> needs, it works fine.
>
> I don't think our DB/POJO design is very problematic in this
> regard..
> I think we can replace most of the existing DAOs with ORM backed
> implementations with very little work.
>
> What we need to make sure is not break the DAO API.
> For example, if I fetch an entity from a Session,
> it would reflect any change that happens to it automatically to the
> DB.
> This is not how the current API works, so this feature should be
> disabled
> or otherwise we would have a hard time hunting the bugs that will
> spawn
> from this change of behavior.
>
This is in my opinion the main disadvantage of using Hibernate (or
any
other JPA implementation) with our current architecture. However
Hibernate provides the stateless session concept, which is not
standard
but could help.
Alternatively, we could detach from session on fetch, and re-attach
on save/update.
Anyway, it still adds the benefit of ORM which would still simplify
much of the code, and provide the desired portability.
Also I think if we move to the direction of ORM, it would be easier
to change the rest of the application code to behave differently,
should we choose to do it.
>>
>>
>>
>> Martin Perina
>>
>>
>> ----- Original Message -----
>>> From: "Alon Bar-Lev" <alonbl(a)redhat.com>
>>> To: "Juan Hernandez" <jhernand(a)redhat.com>
>>> Cc: engine-devel(a)ovirt.org
>>> Sent: Tuesday, March 26, 2013 7:39:16 PM
>>> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>>>
>>>
>>>
>>> ----- 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.
>>>
>>> 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.
>>>
>>> Regards,
>>> Alon Bar-Lev.
>>> _______________________________________________
>>> 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
>>
--
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.
3:52 a.m.
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".
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.
4: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.
5:34 a.m.
On 03/27/2013 09:19 AM, Alon Bar-Lev wrote:
----- 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...
I talking about relational databases, most self called nosql databases
are not relational.
>
>> 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.
In my opinion doing that is a waste of resources when you can do just
one persistence layer with standard SQL.
>
>> 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.
It doesn't need to be database dependent. In fact most of the stored
procedures that we use today are just plain wrappers for SQL statements
that are perfectly standard SQL (there are exceptions, of course).
The pre-defined interface of the database to the external world should
be well structured data, with restrictions that ensure consistency.
The language you chose depends on database capabilities, for Oracle
and DB2 you can write stored procedure in Java...
So it would be acceptable to write that logic in Java and deploy it
inside the database, but it isn't acceptable to write that same logic in
java and deploy it inside the application?
This code is part of application, it is maintained within the same
release milestones, commits etc.
Yes, the n versions of the stored procedures are maintained and kept in
sync. Far from ideal.
>
>> 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.
That is very wrong, in my opinion. A relational database is not a place
for your logic and it is not a procedural system. The SQL language is a
declarative language, not procedural, and the nature of the querying
concept, even without the SQL language, is declarative, not procedural.
The procedural languages have been added to relational databases as
after thought extensions, and they have proven to be very good tools to
lock users to their database vendors. Ask anyone who has tried to escape
from Oracle's PL/SQL (or any other similar thing).
Regarding performance it is much more relevant to reduce the number of
queries sent to the database and to improve the performance of complex
queries than having/not having them in stored procedures.
Regarding complexity, I don't really see how a SQL query is more complex
than a call to a stored procedure than in turns executes the same SQL
query, I would say the opposite: the stored procedure is just an
additional step, an additional thing to maintain, so it increases
complexity.
Anyhow, look at most of the stored procedures that we currently have and
you will see that they just wrap a plain SQL statement. And when
something is changed, adding a new column, for example, a lot of things
have to change: the call from the data access layer, the signature of
the procedure, and the query inside. Most of the time this stored
procedures just get in the middle without any added value.
>
>> 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.
I've seen the opposite in very complex applications, when it comes to
performance and scalability.
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.
The fact that SQL statements are out of stored procedures doesn't mean
that they have to be maintained by a different type of developer. If
they are currently maintained by relational database specialists they
can continue to be maintained by relational database specialists once
they are outside of the stored procedures.
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.
This is wrong. Hibernate is a good solution exactly when it comes to
performance and scalability, there is where it shines, if used correctly.
Anyhow, the subject is not Hibernate (or any other ORM solution) versus
stored procedures, it is moving the SQL code out of stored procedures,
with or without 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.
--
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.
8:53 a.m.
On 03/26/2013 08: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.
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
11:01 a.m.
I suggest that there should be 2 different (though related) issues to consider here in
this thread:
1. How the code works with database in runtime (CRUD) - and this is what the
Hibernate/JPA/etc. discussion is so far about.
2. Maintenance/upgrade/modification of the database schema - which is also very important,
done pretty frequently in this project, and is also sometimes database vendor sensitive
(for example - column types/indexes differ, syntax differs a bit). Doing changes in the
database structure is not done in runtime, but should be still very easy and maintainable
for the developers (and transparent to them wherever possible to hide vendor differences).
I had good experience with Liquibase for creating/upgrading database schemas so I propose
to evaluate it.
http://www.liquibase.org/
----- Original Message -----
From: "Itamar Heim" <iheim(a)redhat.com>
To: "Alon Bar-Lev" <alonbl(a)redhat.com>
Cc: "Juan Hernandez" <jhernand(a)redhat.com>, engine-devel(a)ovirt.org
Sent: Wednesday, March 27, 2013 2:53:45 PM
Subject: Re: [Engine-devel] Move SQL out of stored procedures
On 03/26/2013 08: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.
>
> 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
11:04 a.m.
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>
>> 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
11:31 a.m.
----- Original Message -----
From: "Libor Spevak" <lspevak(a)redhat.com>
To: "Itamar Heim" <iheim(a)redhat.com>
Cc: "Juan Hernandez" <jhernand(a)redhat.com>, 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).
Usually companies have one database and they are trying to stick to that one. Having two
doubles the resource needs, you need one more DBA team, care for mirrors, backups. So it
almost doubles the costs.
This is why I frequently hear people asking if we plan to support XyDB in the future.
PostgreSQL is cool, but those who already use MySQL/MariaDB, they just do not want one
more.
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)
With a JPA we could support most mainstream relational databases, but in my opinion 99
percent of people run oracle, mysql/mariadb or postgresql. So maybe we do not have to
think in big number of database engines.
This is theoretical since JPA is still on wishlist :(
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>
>>> 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
5:35 p.m.
----- Original Message -----
From: "Laszlo Hornyak" <lhornyak(a)redhat.com>
To: "Libor Spevak" <lspevak(a)redhat.com>
Cc: "Juan Hernandez" <jhernand(a)redhat.com>, engine-devel(a)ovirt.org
Sent: Thursday, March 28, 2013 5:31:34 PM
Subject: Re: [Engine-devel] Move SQL out of stored procedures
----- Original Message -----
> From: "Libor Spevak" <lspevak(a)redhat.com>
> To: "Itamar Heim" <iheim(a)redhat.com>
> Cc: "Juan Hernandez" <jhernand(a)redhat.com>, 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...
Sorry for joining this discussion so late (I was in a vacation)
anyway
two points missing from SQL VS. SP are
1) security - With plain SQL we will have to handle SQL Injection
2) It is more economic to pass a call to SP than the full SQL on the wire...
>
> Some points:
> 1. Is there a real need by end-users/customers to run it on e.g.
> Oracle
> only? (performance, stability, easier administration).
Usually companies have one database and they are trying to stick to that one.
Having two doubles the resource needs, you need one more DBA team, care for
mirrors, backups. So it almost doubles the costs.
Generally, I agree with Alon B L , if you have to support X DBs you are not doubling the
effort by X
Actually, we had already experience with that when we supported both MS SQL &
Postgres
I believe that as we have some customers with large installations, performance counts and
the best way (and sometimes teh only way) id the DB layer
This is why I frequently hear people asking if we plan to support
XyDB in the
future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they
just do not want one more.
> 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)
With a JPA we could support most mainstream relational databases, but in my
opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So
maybe we do not have to think in big number of database engines.
This is theoretical since JPA is still on wishlist :(
>
> 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.
IMHO , ETL & DWH are perfect candidates for NO SQL which is already supported by
Jasper
>
> 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...).
I had evaluated Liquibase and I think that managing your DB upgrades via XML is very
unfriendly and very limited as you reach complex upgrades as we had in the past.
Just think of the tables in which we change the key from long to UUID , there is no way to
do that in such tools
>
> 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.
Did it also , again , since our SQL is 90% simple , the effort of writing a SP for more
than one DB is not so high (and you have free converters you can use for that)
Finally, embedded SQL in the Java code is not a good idea, it will be hard to maintain it
and it is not advancing us in supporting more than one database.
We have already SQL generated in the Java code on the search engine and IMHO this is one
of the parts in the applications that needs a rewrite ...
>
>
> 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>
> >>> 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
>
_______________________________________________
Engine-devel mailing list
Engine-devel(a)ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-devel
1:24 a.m.
I also apologize for jumping in late...
I think concerning SQL injection we'll be covered by using PreparedStatements. Since
we're using SpringJDBC, most of our code uses PreparedStatements anyway.
Concerning ORM - I feel it won't really be beneficial to us. I know of very few
projects who can actually be cross-database, and just maintaining schema creation scripts
for different databases can be too difficult to maintain. Also, from a performance
perspective, ORM performs worse than regular SQL (or stored procedures), so it
wouldn't be the direction I choose.
I think we should keep using SpringJDBC with either SQL or stored procedures (doesn't
really matter, whatever is easier to maintain and performs faster) and maybe add a better,
more generic, RowMapper class.
----- Original Message -----
From: "Eli Mesika" <emesika(a)redhat.com>
To: engine-devel(a)ovirt.org
Sent: Tuesday, April 2, 2013 12:35:03 AM
Subject: Re: [Engine-devel] Move SQL out of stored procedures
----- Original Message -----
From: "Laszlo Hornyak" <lhornyak(a)redhat.com>
To: "Libor Spevak" <lspevak(a)redhat.com>
Cc: "Juan Hernandez" <jhernand(a)redhat.com>, engine-devel(a)ovirt.org
Sent: Thursday, March 28, 2013 5:31:34 PM
Subject: Re: [Engine-devel] Move SQL out of stored procedures
----- Original Message -----
> From: "Libor Spevak" <lspevak(a)redhat.com>
> To: "Itamar Heim" <iheim(a)redhat.com>
> Cc: "Juan Hernandez" <jhernand(a)redhat.com>, 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...
Sorry for joining this discussion so late (I was in a vacation)
anyway
two points missing from SQL VS. SP are
1) security - With plain SQL we will have to handle SQL Injection
2) It is more economic to pass a call to SP than the full SQL on the wire...
>
> Some points:
> 1. Is there a real need by end-users/customers to run it on e.g.
> Oracle
> only? (performance, stability, easier administration).
Usually companies have one database and they are trying to stick to that one.
Having two doubles the resource needs, you need one more DBA team, care for
mirrors, backups. So it almost doubles the costs.
Generally, I agree with Alon B L , if you have to support X DBs you are not doubling the
effort by X
Actually, we had already experience with that when we supported both MS SQL &
Postgres
I believe that as we have some customers with large installations, performance counts and
the best way (and sometimes teh only way) id the DB layer
This is why I frequently hear people asking if we plan to support
XyDB in the
future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they
just do not want one more.
> 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)
With a JPA we could support most mainstream relational databases, but in my
opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So
maybe we do not have to think in big number of database engines.
This is theoretical since JPA is still on wishlist :(
>
> 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.
IMHO , ETL & DWH are perfect candidates for NO SQL which is already supported by
Jasper
>
> 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...).
I had evaluated Liquibase and I think that managing your DB upgrades via XML is very
unfriendly and very limited as you reach complex upgrades as we had in the past.
Just think of the tables in which we change the key from long to UUID , there is no way to
do that in such tools
>
> 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.
Did it also , again , since our SQL is 90% simple , the effort of writing a SP for more
than one DB is not so high (and you have free converters you can use for that)
Finally, embedded SQL in the Java code is not a good idea, it will be hard to maintain it
and it is not advancing us in supporting more than one database.
We have already SQL generated in the Java code on the search engine and IMHO this is one
of the parts in the applications that needs a rewrite ...
>
>
> 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>
> >>> 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
>
_______________________________________________
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
2:34 a.m.
Hi Liran,
I agree that ORM tools in general have to add some mapping overhead, but that overhead is
very small compared to the time needed by the database interaction.
ORM tools sometimes generate SQL statements that we could imagine being better, I do not
think they are as hard for the DB as for example the ones generated by searchbackend.
Also, we can do rdbms specific optimizations when needed.
Plus we could finally have some caching in ovirt engine and the code would not have to
read e.g. the DC record again and again. There are some more like that.
Therefore having a JPA could improve the performance in engine.
Laszlo
----- Original Message -----
From: "Liran Zelkha" <lzelkha(a)redhat.com>
To: "Eli Mesika" <emesika(a)redhat.com>
Cc: engine-devel(a)ovirt.org
Sent: Tuesday, April 2, 2013 7:24:08 AM
Subject: Re: [Engine-devel] Move SQL out of stored procedures
I also apologize for jumping in late...
I think concerning SQL injection we'll be covered by using
PreparedStatements. Since we're using SpringJDBC, most of our code uses
PreparedStatements anyway.
Concerning ORM - I feel it won't really be beneficial to us. I know of very
few projects who can actually be cross-database, and just maintaining schema
creation scripts for different databases can be too difficult to maintain.
Also, from a performance perspective, ORM performs worse than regular SQL
(or stored procedures), so it wouldn't be the direction I choose.
I think we should keep using SpringJDBC with either SQL or stored procedures
(doesn't really matter, whatever is easier to maintain and performs faster)
and maybe add a better, more generic, RowMapper class.
----- Original Message -----
From: "Eli Mesika" <emesika(a)redhat.com>
To: engine-devel(a)ovirt.org
Sent: Tuesday, April 2, 2013 12:35:03 AM
Subject: Re: [Engine-devel] Move SQL out of stored procedures
----- Original Message -----
> From: "Laszlo Hornyak" <lhornyak(a)redhat.com>
> To: "Libor Spevak" <lspevak(a)redhat.com>
> Cc: "Juan Hernandez" <jhernand(a)redhat.com>, engine-devel(a)ovirt.org
> Sent: Thursday, March 28, 2013 5:31:34 PM
> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>
>
>
> ----- Original Message -----
> > From: "Libor Spevak" <lspevak(a)redhat.com>
> > To: "Itamar Heim" <iheim(a)redhat.com>
> > Cc: "Juan Hernandez" <jhernand(a)redhat.com>,
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...
Sorry for joining this discussion so late (I was in a vacation)
anyway
two points missing from SQL VS. SP are
1) security - With plain SQL we will have to handle SQL Injection
2) It is more economic to pass a call to SP than the full SQL on the wire...
> >
> > Some points:
> > 1. Is there a real need by end-users/customers to run it on e.g.
> > Oracle
> > only? (performance, stability, easier administration).
>
> Usually companies have one database and they are trying to stick to that
> one.
> Having two doubles the resource needs, you need one more DBA team, care for
> mirrors, backups. So it almost doubles the costs.
Generally, I agree with Alon B L , if you have to support X DBs you are not
doubling the effort by X
Actually, we had already experience with that when we supported both MS SQL &
Postgres
I believe that as we have some customers with large installations,
performance counts and the best way (and sometimes teh only way) id the DB
layer
> This is why I frequently hear people asking if we plan to support XyDB in
> the
> future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they
> just do not want one more.
>
> > 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)
>
> With a JPA we could support most mainstream relational databases, but in my
> opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So
> maybe we do not have to think in big number of database engines.
> This is theoretical since JPA is still on wishlist :(
>
> >
> > 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.
IMHO , ETL & DWH are perfect candidates for NO SQL which is already supported
by Jasper
> >
> > 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...).
I had evaluated Liquibase and I think that managing your DB upgrades via XML
is very unfriendly and very limited as you reach complex upgrades as we had
in the past.
Just think of the tables in which we change the key from long to UUID , there
is no way to do that in such tools
> >
> > 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.
Did it also , again , since our SQL is 90% simple , the effort of writing a
SP for more than one DB is not so high (and you have free converters you can
use for that)
Finally, embedded SQL in the Java code is not a good idea, it will be hard to
maintain it and it is not advancing us in supporting more than one database.
We have already SQL generated in the Java code on the search engine and IMHO
this is one of the parts in the applications that needs a rewrite ...
> >
> >
> > 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>
> > >>> 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
> >
> _______________________________________________
> 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
_______________________________________________
Engine-devel mailing list
Engine-devel(a)ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-devel
2:37 a.m.
Hi Laszlo,
I'm currently in the process of adding a caching layer on top of JdbcTemplate, which
would greatly reduce the number of database activities we have, so that would solve the
last item you raised.
I didn't mean the ORM performance is caused by the mapping. I think the problem lies
in the fact that we will modify our code to have batch updates for most insert activities
- a thing that is impossible in JPA/Hibernate. So, if we'll have some code in SQL and
some in ORM - I prefer we stick all code to SQL…
On Apr 2, 2013, at 9:34 AM, Laszlo Hornyak wrote:
Hi Liran,
I agree that ORM tools in general have to add some mapping overhead, but that overhead is
very small compared to the time needed by the database interaction.
ORM tools sometimes generate SQL statements that we could imagine being better, I do not
think they are as hard for the DB as for example the ones generated by searchbackend.
Also, we can do rdbms specific optimizations when needed.
Plus we could finally have some caching in ovirt engine and the code would not have to
read e.g. the DC record again and again. There are some more like that.
Therefore having a JPA could improve the performance in engine.
Laszlo
----- Original Message -----
> From: "Liran Zelkha" <lzelkha(a)redhat.com>
> To: "Eli Mesika" <emesika(a)redhat.com>
> Cc: engine-devel(a)ovirt.org
> Sent: Tuesday, April 2, 2013 7:24:08 AM
> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>
> I also apologize for jumping in late...
> I think concerning SQL injection we'll be covered by using
> PreparedStatements. Since we're using SpringJDBC, most of our code uses
> PreparedStatements anyway.
> Concerning ORM - I feel it won't really be beneficial to us. I know of very
> few projects who can actually be cross-database, and just maintaining schema
> creation scripts for different databases can be too difficult to maintain.
> Also, from a performance perspective, ORM performs worse than regular SQL
> (or stored procedures), so it wouldn't be the direction I choose.
> I think we should keep using SpringJDBC with either SQL or stored procedures
> (doesn't really matter, whatever is easier to maintain and performs faster)
> and maybe add a better, more generic, RowMapper class.
>
> ----- Original Message -----
> From: "Eli Mesika" <emesika(a)redhat.com>
> To: engine-devel(a)ovirt.org
> Sent: Tuesday, April 2, 2013 12:35:03 AM
> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>
>
>
> ----- Original Message -----
>> From: "Laszlo Hornyak" <lhornyak(a)redhat.com>
>> To: "Libor Spevak" <lspevak(a)redhat.com>
>> Cc: "Juan Hernandez" <jhernand(a)redhat.com>,
engine-devel(a)ovirt.org
>> Sent: Thursday, March 28, 2013 5:31:34 PM
>> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>>
>>
>>
>> ----- Original Message -----
>>> From: "Libor Spevak" <lspevak(a)redhat.com>
>>> To: "Itamar Heim" <iheim(a)redhat.com>
>>> Cc: "Juan Hernandez" <jhernand(a)redhat.com>,
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...
>
> Sorry for joining this discussion so late (I was in a vacation)
> anyway
> two points missing from SQL VS. SP are
> 1) security - With plain SQL we will have to handle SQL Injection
> 2) It is more economic to pass a call to SP than the full SQL on the wire...
>
>
>>>
>>> Some points:
>>> 1. Is there a real need by end-users/customers to run it on e.g.
>>> Oracle
>>> only? (performance, stability, easier administration).
>>
>> Usually companies have one database and they are trying to stick to that
>> one.
>> Having two doubles the resource needs, you need one more DBA team, care for
>> mirrors, backups. So it almost doubles the costs.
>
> Generally, I agree with Alon B L , if you have to support X DBs you are not
> doubling the effort by X
> Actually, we had already experience with that when we supported both MS SQL &
> Postgres
> I believe that as we have some customers with large installations,
> performance counts and the best way (and sometimes teh only way) id the DB
> layer
>
>> This is why I frequently hear people asking if we plan to support XyDB in
>> the
>> future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they
>> just do not want one more.
>>
>>> 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)
>>
>> With a JPA we could support most mainstream relational databases, but in my
>> opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So
>> maybe we do not have to think in big number of database engines.
>> This is theoretical since JPA is still on wishlist :(
>>
>>>
>>> 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.
>
> IMHO , ETL & DWH are perfect candidates for NO SQL which is already supported
> by Jasper
>
>>>
>>> 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...).
>
> I had evaluated Liquibase and I think that managing your DB upgrades via XML
> is very unfriendly and very limited as you reach complex upgrades as we had
> in the past.
> Just think of the tables in which we change the key from long to UUID , there
> is no way to do that in such tools
>
>>>
>>> 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.
>
> Did it also , again , since our SQL is 90% simple , the effort of writing a
> SP for more than one DB is not so high (and you have free converters you can
> use for that)
>
> Finally, embedded SQL in the Java code is not a good idea, it will be hard to
> maintain it and it is not advancing us in supporting more than one database.
> We have already SQL generated in the Java code on the search engine and IMHO
> this is one of the parts in the applications that needs a rewrite ...
>
>>>
>>>
>>> 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>
>>>>>> 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
>>>
>> _______________________________________________
>> 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
> _______________________________________________
> 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
3:15 a.m.
Hi all,
Sorry for my late response on the issue, I will try to cover as many issues as possible in
this email and other emails
----- Original Message -----
From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
To: "Laszlo Hornyak" <lhornyak(a)redhat.com>
Cc: engine-devel(a)ovirt.org
Sent: Tuesday, April 2, 2013 9:37:28 AM
Subject: Re: [Engine-devel] Move SQL out of stored procedures
Hi Laszlo,
I'm currently in the process of adding a caching layer on top of
JdbcTemplate, which would greatly reduce the number of database activities
we have, so that would solve the last item you raised.
+1 On that approach - some of us already talked about the need to have caching AT LEAST
for the static parts.
I didn't mean the ORM performance is caused by the mapping. I
think the
problem lies in the fact that we will modify our code to have batch updates
for most insert activities - a thing that is impossible in JPA/Hibernate.
So, if we'll have some code in SQL and some in ORM - I prefer we stick all
code to SQL…
On Apr 2, 2013, at 9:34 AM, Laszlo Hornyak wrote:
> Hi Liran,
>
> I agree that ORM tools in general have to add some mapping overhead, but
> that overhead is very small compared to the time needed by the database
> interaction.
> ORM tools sometimes generate SQL statements that we could imagine being
> better, I do not think they are as hard for the DB as for example the ones
> generated by searchbackend. Also, we can do rdbms specific optimizations
> when needed.
> Plus we could finally have some caching in ovirt engine and the code would
> not have to read e.g. the DC record again and again. There are some more
> like that.
>
> Therefore having a JPA could improve the performance in engine.
>
> Laszlo
>
> ----- Original Message -----
>> From: "Liran Zelkha" <lzelkha(a)redhat.com>
>> To: "Eli Mesika" <emesika(a)redhat.com>
>> Cc: engine-devel(a)ovirt.org
>> Sent: Tuesday, April 2, 2013 7:24:08 AM
>> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>>
>> I also apologize for jumping in late...
>> I think concerning SQL injection we'll be covered by using
>> PreparedStatements. Since we're using SpringJDBC, most of our code uses
>> PreparedStatements anyway.
>> Concerning ORM - I feel it won't really be beneficial to us. I know of
>> very
>> few projects who can actually be cross-database, and just maintaining
>> schema
>> creation scripts for different databases can be too difficult to maintain.
>> Also, from a performance perspective, ORM performs worse than regular SQL
>> (or stored procedures), so it wouldn't be the direction I choose.
>> I think we should keep using SpringJDBC with either SQL or stored
>> procedures
>> (doesn't really matter, whatever is easier to maintain and performs
>> faster)
>> and maybe add a better, more generic, RowMapper class.
+1 on that approach - I remind you all that our data model is a bit complex - for example
- we have entities that are composed of views - VM which is based on static, dynamic and
statistics information.
Modeling this with hibernate is problematic.
In addition, we will have to introduce a custom mapper for pgsql uuid to either out
Guid/NGuid or (as others already suggested) java.util.UUID , hence the desire to have 100%
portability already breaks.
Barein mind not all databases support UUID as native types - this is something we need to
think of (maybe outside the context of this discussion) - I can tell you that from what I
saw so far, mssql , postgresql and h2 databases DO support it.
In addition we have MLA related stored procedures which have to contain logic and trying
to model them as JPA queries will definitely hurt performance.
If we do want to go to hibernate approach (again) as lessons from last time I would:
a. Not try to solve the complex cases - keep hibernate/JPA for relatively CRUD operations
- for more complex ones - keep stored procedures (I remind you it is possible to invoke
native SQL/Stored procedures from JPA).
b. Consider having a layer of objects (DTOs) that their sole purpose is to work with the
JPA layer (let's say that they are in package of org.ovirt.engine.core.dal.entities)
and they will map to our existing business entities.
The advantage in this approach is that our business entities (which are currently shared
with frontend) will not need to be adjusted/annotated with hibernate/JPA annotations.
The disadvantages in this approach is that we will have a double group of entities - one
for DAL and one for BLL/frontend (and this brings up the question on what are the plans of
using the REST-API with frontend?)
>>
>> ----- Original Message -----
>> From: "Eli Mesika" <emesika(a)redhat.com>
>> To: engine-devel(a)ovirt.org
>> Sent: Tuesday, April 2, 2013 12:35:03 AM
>> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>>
>>
>>
>> ----- Original Message -----
>>> From: "Laszlo Hornyak" <lhornyak(a)redhat.com>
>>> To: "Libor Spevak" <lspevak(a)redhat.com>
>>> Cc: "Juan Hernandez" <jhernand(a)redhat.com>,
engine-devel(a)ovirt.org
>>> Sent: Thursday, March 28, 2013 5:31:34 PM
>>> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>>>
>>>
>>>
>>> ----- Original Message -----
>>>> From: "Libor Spevak" <lspevak(a)redhat.com>
>>>> To: "Itamar Heim" <iheim(a)redhat.com>
>>>> Cc: "Juan Hernandez" <jhernand(a)redhat.com>,
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...
>>
>> Sorry for joining this discussion so late (I was in a vacation)
>> anyway
>> two points missing from SQL VS. SP are
>> 1) security - With plain SQL we will have to handle SQL Injection
>> 2) It is more economic to pass a call to SP than the full SQL on the
>> wire...
>>
>>
>>>>
>>>> Some points:
>>>> 1. Is there a real need by end-users/customers to run it on e.g.
>>>> Oracle
>>>> only? (performance, stability, easier administration).
>>>
>>> Usually companies have one database and they are trying to stick to that
>>> one.
>>> Having two doubles the resource needs, you need one more DBA team, care
>>> for
>>> mirrors, backups. So it almost doubles the costs.
>>
>> Generally, I agree with Alon B L , if you have to support X DBs you are
>> not
>> doubling the effort by X
>> Actually, we had already experience with that when we supported both MS
>> SQL &
>> Postgres
>> I believe that as we have some customers with large installations,
>> performance counts and the best way (and sometimes teh only way) id the DB
>> layer
>>
>>> This is why I frequently hear people asking if we plan to support XyDB in
>>> the
>>> future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they
>>> just do not want one more.
>>>
>>>> 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)
>>>
>>> With a JPA we could support most mainstream relational databases, but in
>>> my
>>> opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So
>>> maybe we do not have to think in big number of database engines.
>>> This is theoretical since JPA is still on wishlist :(
>>>
>>>>
>>>> 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.
>>
>> IMHO , ETL & DWH are perfect candidates for NO SQL which is already
>> supported
>> by Jasper
+1 Here about NoSQL for ETL & DWH.
>>
>>>>
>>>> 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...).
>>
>> I had evaluated Liquibase and I think that managing your DB upgrades via
>> XML
>> is very unfriendly and very limited as you reach complex upgrades as we
>> had
>> in the past.
>> Just think of the tables in which we change the key from long to UUID ,
>> there
>> is no way to do that in such tools
>>
>>>>
>>>> 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.
>>
>> Did it also , again , since our SQL is 90% simple , the effort of writing
>> a
>> SP for more than one DB is not so high (and you have free converters you
>> can
>> use for that)
>>
>> Finally, embedded SQL in the Java code is not a good idea, it will be hard
>> to
>> maintain it and it is not advancing us in supporting more than one
>> database.
>> We have already SQL generated in the Java code on the search engine and
>> IMHO
>> this is one of the parts in the applications that needs a rewrite ...
>>
>>>>
>>>>
>>>> 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>
>>>>>>> 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
>>>>
>>> _______________________________________________
>>> 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
>> _______________________________________________
>> 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
_______________________________________________
Engine-devel mailing list
Engine-devel(a)ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-devel
5:29 a.m.
----- Original Message -----
From: "Yair Zaslavsky" <yzaslavs(a)redhat.com>
To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
Cc: engine-devel(a)ovirt.org
Sent: Tuesday, April 2, 2013 10:15:06 AM
Subject: Re: [Engine-devel] Move SQL out of stored procedures
Hi all,
Sorry for my late response on the issue, I will try to cover as many issues
as possible in this email and other emails
----- Original Message -----
> From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> To: "Laszlo Hornyak" <lhornyak(a)redhat.com>
> Cc: engine-devel(a)ovirt.org
> Sent: Tuesday, April 2, 2013 9:37:28 AM
> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>
> Hi Laszlo,
>
> I'm currently in the process of adding a caching layer on top of
> JdbcTemplate, which would greatly reduce the number of database activities
> we have, so that would solve the last item you raised.
+1 On that approach - some of us already talked about the need to have
caching AT LEAST for the static parts.
> I didn't mean the ORM performance is caused by the mapping. I think the
> problem lies in the fact that we will modify our code to have batch updates
> for most insert activities - a thing that is impossible in JPA/Hibernate.
> So, if we'll have some code in SQL and some in ORM - I prefer we stick all
> code to SQL…
>
> On Apr 2, 2013, at 9:34 AM, Laszlo Hornyak wrote:
>
> > Hi Liran,
> >
> > I agree that ORM tools in general have to add some mapping overhead, but
> > that overhead is very small compared to the time needed by the database
> > interaction.
> > ORM tools sometimes generate SQL statements that we could imagine being
> > better, I do not think they are as hard for the DB as for example the
> > ones
> > generated by searchbackend. Also, we can do rdbms specific optimizations
> > when needed.
> > Plus we could finally have some caching in ovirt engine and the code
> > would
> > not have to read e.g. the DC record again and again. There are some more
> > like that.
> >
> > Therefore having a JPA could improve the performance in engine.
> >
> > Laszlo
> >
> > ----- Original Message -----
> >> From: "Liran Zelkha" <lzelkha(a)redhat.com>
> >> To: "Eli Mesika" <emesika(a)redhat.com>
> >> Cc: engine-devel(a)ovirt.org
> >> Sent: Tuesday, April 2, 2013 7:24:08 AM
> >> Subject: Re: [Engine-devel] Move SQL out of stored procedures
> >>
> >> I also apologize for jumping in late...
> >> I think concerning SQL injection we'll be covered by using
> >> PreparedStatements. Since we're using SpringJDBC, most of our code
uses
> >> PreparedStatements anyway.
> >> Concerning ORM - I feel it won't really be beneficial to us. I know of
> >> very
> >> few projects who can actually be cross-database, and just maintaining
> >> schema
> >> creation scripts for different databases can be too difficult to
> >> maintain.
> >> Also, from a performance perspective, ORM performs worse than regular
> >> SQL
> >> (or stored procedures), so it wouldn't be the direction I choose.
> >> I think we should keep using SpringJDBC with either SQL or stored
> >> procedures
> >> (doesn't really matter, whatever is easier to maintain and performs
> >> faster)
> >> and maybe add a better, more generic, RowMapper class.
+1 on that approach - I remind you all that our data model is a bit complex -
for example - we have entities that are composed of views - VM which is
based on static, dynamic and statistics information.
Modeling this with hibernate is problematic.
In addition, we will have to introduce a custom mapper for pgsql uuid to
either out Guid/NGuid or (as others already suggested) java.util.UUID ,
hence the desire to have 100% portability already breaks.
Barein mind not all databases support UUID as native types - this is
something we need to think of (maybe outside the context of this discussion)
- I can tell you that from what I saw so far, mssql , postgresql and h2
databases DO support it.
In addition we have MLA related stored procedures which have to contain logic
and trying to model them as JPA queries will definitely hurt performance.
If we do want to go to hibernate approach (again) as lessons from last time I
would:
a. Not try to solve the complex cases - keep hibernate/JPA for relatively
CRUD operations - for more complex ones - keep stored procedures (I remind
you it is possible to invoke native SQL/Stored procedures from JPA).
b. Consider having a layer of objects (DTOs) that their sole purpose is to
work with the JPA layer (let's say that they are in package of
org.ovirt.engine.core.dal.entities) and they will map to our existing
business entities.
The advantage in this approach is that our business entities (which are
currently shared with frontend) will not need to be adjusted/annotated with
hibernate/JPA annotations.
The disadvantages in this approach is that we will have a double group of
entities - one for DAL and one for BLL/frontend (and this brings up the
question on what are the plans of using the REST-API with frontend?)
Hybrid solutions tend to waste much more time than they save ...
> >>
> >> ----- Original Message -----
> >> From: "Eli Mesika" <emesika(a)redhat.com>
> >> To: engine-devel(a)ovirt.org
> >> Sent: Tuesday, April 2, 2013 12:35:03 AM
> >> Subject: Re: [Engine-devel] Move SQL out of stored procedures
> >>
> >>
> >>
> >> ----- Original Message -----
> >>> From: "Laszlo Hornyak" <lhornyak(a)redhat.com>
> >>> To: "Libor Spevak" <lspevak(a)redhat.com>
> >>> Cc: "Juan Hernandez" <jhernand(a)redhat.com>,
engine-devel(a)ovirt.org
> >>> Sent: Thursday, March 28, 2013 5:31:34 PM
> >>> Subject: Re: [Engine-devel] Move SQL out of stored procedures
> >>>
> >>>
> >>>
> >>> ----- Original Message -----
> >>>> From: "Libor Spevak" <lspevak(a)redhat.com>
> >>>> To: "Itamar Heim" <iheim(a)redhat.com>
> >>>> Cc: "Juan Hernandez" <jhernand(a)redhat.com>,
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...
> >>
> >> Sorry for joining this discussion so late (I was in a vacation)
> >> anyway
> >> two points missing from SQL VS. SP are
> >> 1) security - With plain SQL we will have to handle SQL Injection
> >> 2) It is more economic to pass a call to SP than the full SQL on the
> >> wire...
> >>
> >>
> >>>>
> >>>> Some points:
> >>>> 1. Is there a real need by end-users/customers to run it on e.g.
> >>>> Oracle
> >>>> only? (performance, stability, easier administration).
> >>>
> >>> Usually companies have one database and they are trying to stick to
> >>> that
> >>> one.
> >>> Having two doubles the resource needs, you need one more DBA team,
care
> >>> for
> >>> mirrors, backups. So it almost doubles the costs.
> >>
> >> Generally, I agree with Alon B L , if you have to support X DBs you are
> >> not
> >> doubling the effort by X
> >> Actually, we had already experience with that when we supported both MS
> >> SQL &
> >> Postgres
> >> I believe that as we have some customers with large installations,
> >> performance counts and the best way (and sometimes teh only way) id the
> >> DB
> >> layer
> >>
> >>> This is why I frequently hear people asking if we plan to support XyDB
> >>> in
> >>> the
> >>> future. PostgreSQL is cool, but those who already use MySQL/MariaDB,
> >>> they
> >>> just do not want one more.
> >>>
> >>>> 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)
> >>>
> >>> With a JPA we could support most mainstream relational databases, but
> >>> in
> >>> my
> >>> opinion 99 percent of people run oracle, mysql/mariadb or postgresql.
> >>> So
> >>> maybe we do not have to think in big number of database engines.
> >>> This is theoretical since JPA is still on wishlist :(
> >>>
> >>>>
> >>>> 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.
> >>
> >> IMHO , ETL & DWH are perfect candidates for NO SQL which is already
> >> supported
> >> by Jasper
+1 Here about NoSQL for ETL & DWH.
> >>
> >>>>
> >>>> 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...).
> >>
> >> I had evaluated Liquibase and I think that managing your DB upgrades via
> >> XML
> >> is very unfriendly and very limited as you reach complex upgrades as we
> >> had
> >> in the past.
> >> Just think of the tables in which we change the key from long to UUID ,
> >> there
> >> is no way to do that in such tools
> >>
> >>>>
> >>>> 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.
> >>
> >> Did it also , again , since our SQL is 90% simple , the effort of
> >> writing
> >> a
> >> SP for more than one DB is not so high (and you have free converters you
> >> can
> >> use for that)
> >>
> >> Finally, embedded SQL in the Java code is not a good idea, it will be
> >> hard
> >> to
> >> maintain it and it is not advancing us in supporting more than one
> >> database.
> >> We have already SQL generated in the Java code on the search engine and
> >> IMHO
> >> this is one of the parts in the applications that needs a rewrite ...
> >>
> >>>>
> >>>>
> >>>> 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>
> >>>>>>> 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
> >>>>
> >>> _______________________________________________
> >>> 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
> >> _______________________________________________
> >> 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
>
> _______________________________________________
> 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
3:17 a.m.
Hi Liran,
----- Original Message -----
From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
To: "Laszlo Hornyak" <lhornyak(a)redhat.com>
Cc: "Liran Zelkha" <lzelkha(a)redhat.com>, engine-devel(a)ovirt.org
Sent: Tuesday, April 2, 2013 8:37:28 AM
Subject: Re: [Engine-devel] Move SQL out of stored procedures
Hi Laszlo,
I'm currently in the process of adding a caching layer on top of
JdbcTemplate, which would greatly reduce the number of database activities
we have, so that would solve the last item you raised.
That's a great news! Thank you!
I didn't mean the ORM performance is caused by the mapping. I
think the
problem lies in the fact that we will modify our code to have batch updates
for most insert activities - a thing that is impossible in JPA/Hibernate.
So, if we'll have some code in SQL and some in ORM - I prefer we stick all
code to SQL…
I think you can do this with a JPAQL in JPA, but anyway, yes, some code would very likely
have to be in rdbms-specific SQL statements.
On Apr 2, 2013, at 9:34 AM, Laszlo Hornyak wrote:
> Hi Liran,
>
> I agree that ORM tools in general have to add some mapping overhead, but
> that overhead is very small compared to the time needed by the database
> interaction.
> ORM tools sometimes generate SQL statements that we could imagine being
> better, I do not think they are as hard for the DB as for example the ones
> generated by searchbackend. Also, we can do rdbms specific optimizations
> when needed.
> Plus we could finally have some caching in ovirt engine and the code would
> not have to read e.g. the DC record again and again. There are some more
> like that.
>
> Therefore having a JPA could improve the performance in engine.
>
> Laszlo
>
> ----- Original Message -----
>> From: "Liran Zelkha" <lzelkha(a)redhat.com>
>> To: "Eli Mesika" <emesika(a)redhat.com>
>> Cc: engine-devel(a)ovirt.org
>> Sent: Tuesday, April 2, 2013 7:24:08 AM
>> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>>
>> I also apologize for jumping in late...
>> I think concerning SQL injection we'll be covered by using
>> PreparedStatements. Since we're using SpringJDBC, most of our code uses
>> PreparedStatements anyway.
>> Concerning ORM - I feel it won't really be beneficial to us. I know of
>> very
>> few projects who can actually be cross-database, and just maintaining
>> schema
>> creation scripts for different databases can be too difficult to maintain.
>> Also, from a performance perspective, ORM performs worse than regular SQL
>> (or stored procedures), so it wouldn't be the direction I choose.
>> I think we should keep using SpringJDBC with either SQL or stored
>> procedures
>> (doesn't really matter, whatever is easier to maintain and performs
>> faster)
>> and maybe add a better, more generic, RowMapper class.
>>
>> ----- Original Message -----
>> From: "Eli Mesika" <emesika(a)redhat.com>
>> To: engine-devel(a)ovirt.org
>> Sent: Tuesday, April 2, 2013 12:35:03 AM
>> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>>
>>
>>
>> ----- Original Message -----
>>> From: "Laszlo Hornyak" <lhornyak(a)redhat.com>
>>> To: "Libor Spevak" <lspevak(a)redhat.com>
>>> Cc: "Juan Hernandez" <jhernand(a)redhat.com>,
engine-devel(a)ovirt.org
>>> Sent: Thursday, March 28, 2013 5:31:34 PM
>>> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>>>
>>>
>>>
>>> ----- Original Message -----
>>>> From: "Libor Spevak" <lspevak(a)redhat.com>
>>>> To: "Itamar Heim" <iheim(a)redhat.com>
>>>> Cc: "Juan Hernandez" <jhernand(a)redhat.com>,
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...
>>
>> Sorry for joining this discussion so late (I was in a vacation)
>> anyway
>> two points missing from SQL VS. SP are
>> 1) security - With plain SQL we will have to handle SQL Injection
>> 2) It is more economic to pass a call to SP than the full SQL on the
>> wire...
>>
>>
>>>>
>>>> Some points:
>>>> 1. Is there a real need by end-users/customers to run it on e.g.
>>>> Oracle
>>>> only? (performance, stability, easier administration).
>>>
>>> Usually companies have one database and they are trying to stick to that
>>> one.
>>> Having two doubles the resource needs, you need one more DBA team, care
>>> for
>>> mirrors, backups. So it almost doubles the costs.
>>
>> Generally, I agree with Alon B L , if you have to support X DBs you are
>> not
>> doubling the effort by X
>> Actually, we had already experience with that when we supported both MS
>> SQL &
>> Postgres
>> I believe that as we have some customers with large installations,
>> performance counts and the best way (and sometimes teh only way) id the DB
>> layer
>>
>>> This is why I frequently hear people asking if we plan to support XyDB in
>>> the
>>> future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they
>>> just do not want one more.
>>>
>>>> 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)
>>>
>>> With a JPA we could support most mainstream relational databases, but in
>>> my
>>> opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So
>>> maybe we do not have to think in big number of database engines.
>>> This is theoretical since JPA is still on wishlist :(
>>>
>>>>
>>>> 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.
>>
>> IMHO , ETL & DWH are perfect candidates for NO SQL which is already
>> supported
>> by Jasper
>>
>>>>
>>>> 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...).
>>
>> I had evaluated Liquibase and I think that managing your DB upgrades via
>> XML
>> is very unfriendly and very limited as you reach complex upgrades as we
>> had
>> in the past.
>> Just think of the tables in which we change the key from long to UUID ,
>> there
>> is no way to do that in such tools
>>
>>>>
>>>> 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.
>>
>> Did it also , again , since our SQL is 90% simple , the effort of writing
>> a
>> SP for more than one DB is not so high (and you have free converters you
>> can
>> use for that)
>>
>> Finally, embedded SQL in the Java code is not a good idea, it will be hard
>> to
>> maintain it and it is not advancing us in supporting more than one
>> database.
>> We have already SQL generated in the Java code on the search engine and
>> IMHO
>> this is one of the parts in the applications that needs a rewrite ...
>>
>>>>
>>>>
>>>> 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>
>>>>>>> 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
>>>>
>>> _______________________________________________
>>> 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
>> _______________________________________________
>> 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
2:54 a.m.
----- Original Message -----
From: "Eli Mesika" <emesika(a)redhat.com>
To: engine-devel(a)ovirt.org
Sent: Monday, April 1, 2013 11:35:03 PM
Subject: Re: [Engine-devel] Move SQL out of stored procedures
----- Original Message -----
> From: "Laszlo Hornyak" <lhornyak(a)redhat.com>
> To: "Libor Spevak" <lspevak(a)redhat.com>
> Cc: "Juan Hernandez" <jhernand(a)redhat.com>, engine-devel(a)ovirt.org
> Sent: Thursday, March 28, 2013 5:31:34 PM
> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>
>
>
> ----- Original Message -----
> > From: "Libor Spevak" <lspevak(a)redhat.com>
> > To: "Itamar Heim" <iheim(a)redhat.com>
> > Cc: "Juan Hernandez" <jhernand(a)redhat.com>,
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...
Sorry for joining this discussion so late (I was in a vacation)
anyway
two points missing from SQL VS. SP are
1) security - With plain SQL we will have to handle SQL Injection
I do not understand this. What's wrong with PreparedStatement?
2) It is more economic to pass a call to SP than the full SQL on the
wire...
Ah that is not actually happening with postgresql :) I don't know about all the
specific DB's but I am quite sure most other DB does not do that either.
If you have a DataSource, like commons-dbcp, it is caching the PreparedStatements in the
background. A PreparedStatement executes a 'PREPARE' command in postgresql
http://www.postgresql.org/docs/9.2/static/sql-prepare.html
After that it will only send over the name of the query plan and the parameters.
I believe it usually does not save a lot on bandwidth, for example engine's SQL
statements fit in a single tcp/ip frame, but the query parser and planner needs to run
only once, when you create the query plan and that is a big win. I wrote a testfor this
once, quite long ago but I remember something around 10% win if the query execution was
simple enough. But of course it does not matter much if you have a pile of seqscan in your
query plan.
Anyway, this is kind of cool in PostgreSQL :)
> >
> > Some points:
> > 1. Is there a real need by end-users/customers to run it on e.g.
> > Oracle
> > only? (performance, stability, easier administration).
>
> Usually companies have one database and they are trying to stick to that
> one.
> Having two doubles the resource needs, you need one more DBA team, care for
> mirrors, backups. So it almost doubles the costs.
Generally, I agree with Alon B L , if you have to support X DBs you are not
doubling the effort by X
Actually, we had already experience with that when we supported both MS SQL &
Postgres
I believe that as we have some customers with large installations,
performance counts and the best way (and sometimes teh only way) id the DB
layer
Ok, then let's tell MySQL/MariaDB users to use PostgerSQL and see what happens.
> This is why I frequently hear people asking if we plan to support XyDB in
> the
> future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they
> just do not want one more.
>
> > 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)
>
> With a JPA we could support most mainstream relational databases, but in my
> opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So
> maybe we do not have to think in big number of database engines.
> This is theoretical since JPA is still on wishlist :(
>
> >
> > 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.
IMHO , ETL & DWH are perfect candidates for NO SQL which is already supported
by Jasper
> >
> > 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...).
I had evaluated Liquibase and I think that managing your DB upgrades via XML
is very unfriendly and very limited as you reach complex upgrades as we had
in the past.
Just think of the tables in which we change the key from long to UUID , there
is no way to do that in such tools
> >
> > 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.
Did it also , again , since our SQL is 90% simple , the effort of writing a
SP for more than one DB is not so high (and you have free converters you can
use for that)
Finally, embedded SQL in the Java code is not a good idea, it will be hard to
maintain it and it is not advancing us in supporting more than one database.
We have already SQL generated in the Java code on the search engine and IMHO
this is one of the parts in the applications that needs a rewrite ...
> >
> >
> > 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>
> > >>> 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
> >
> _______________________________________________
> 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
4:09 a.m.
Hi,
I would recommend always to avoid hard coding SQL into Java code. It is
very hard to maintain and read.
If there is something, which prevents using JPA/Hibernate, e.g. the
database relational model doesn't reflect the object-oriented domain
very well or we have to live with many stored procedures concurrently, I
would choose a framework, which enables to externalize the SQL code
(into XML).
I worked on a larger project(s) with a lot of PL/SQL code, we moved to
myBatis (previously iBatis) very soon for Java backend:
https://code.google.com/p/mybatis/
Libor
On 26.3.2013 18:34, Juan Hernandez wrote:
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.
I did an small example of what it takes and how it looks like to have
the SQL code into the DAOs:
http://gerrit.ovirt.org/13347
It isn't rocket science, it isn't an exciting task, it isn't fun, but
something I think we should eventually do.
I appreciate any comment about how and when to do this, including
those saying that instead of this primitive approach we should use
this or that ORM framework.
Regards,
Juan Hernandez
3:19 a.m.
----- Original Message -----
From: "Libor Spevak" <lspevak(a)redhat.com>
To: "Juan Hernandez" <jhernand(a)redhat.com>
Cc: engine-devel(a)ovirt.org
Sent: Wednesday, March 27, 2013 10:09:22 AM
Subject: Re: [Engine-devel] Move SQL out of stored procedures
Hi,
I would recommend always to avoid hard coding SQL into Java code. It is
very hard to maintain and read.
If there is something, which prevents using JPA/Hibernate, e.g. the
database relational model doesn't reflect the object-oriented domain
very well or we have to live with many stored procedures concurrently, I
would choose a framework, which enables to externalize the SQL code
(into XML).
I worked on a larger project(s) with a lot of PL/SQL code, we moved to
myBatis (previously iBatis) very soon for Java backend:
https://code.google.com/p/mybatis/
Libor
I used a similar approach at past project - not with iBatis though, but a "in
house" implementation of such framework.
I think this idea is worth considering.
On 26.3.2013 18:34, Juan Hernandez wrote:
> 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.
>
> I did an small example of what it takes and how it looks like to have
> the SQL code into the DAOs:
>
> http://gerrit.ovirt.org/13347
>
> It isn't rocket science, it isn't an exciting task, it isn't fun, but
> something I think we should eventually do.
>
> I appreciate any comment about how and when to do this, including
> those saying that instead of this primitive approach we should use
> this or that ORM framework.
>
> Regards,
> Juan Hernandez
_______________________________________________
Engine-devel mailing list
Engine-devel(a)ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-devel
3:21 a.m.
Hi
I think externalizing SQL can lead to a VERY difficult maintenance. But, as long as we
stick to SQL (or stored procedures, just not ORM), I don't mind…
On Apr 2, 2013, at 10:19 AM, Yair Zaslavsky wrote:
----- Original Message -----
> From: "Libor Spevak" <lspevak(a)redhat.com>
> To: "Juan Hernandez" <jhernand(a)redhat.com>
> Cc: engine-devel(a)ovirt.org
> Sent: Wednesday, March 27, 2013 10:09:22 AM
> Subject: Re: [Engine-devel] Move SQL out of stored procedures
>
> Hi,
> I would recommend always to avoid hard coding SQL into Java code. It is
> very hard to maintain and read.
> If there is something, which prevents using JPA/Hibernate, e.g. the
> database relational model doesn't reflect the object-oriented domain
> very well or we have to live with many stored procedures concurrently, I
> would choose a framework, which enables to externalize the SQL code
> (into XML).
>
> I worked on a larger project(s) with a lot of PL/SQL code, we moved to
> myBatis (previously iBatis) very soon for Java backend:
>
> https://code.google.com/p/mybatis/
>
> Libor
I used a similar approach at past project - not with iBatis though, but a "in
house" implementation of such framework.
I think this idea is worth considering.
>
>
> On 26.3.2013 18:34, Juan Hernandez wrote:
>> 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.
>>
>> I did an small example of what it takes and how it looks like to have
>> the SQL code into the DAOs:
>>
>> http://gerrit.ovirt.org/13347
>>
>> It isn't rocket science, it isn't an exciting task, it isn't fun,
but
>> something I think we should eventually do.
>>
>> I appreciate any comment about how and when to do this, including
>> those saying that instead of this primitive approach we should use
>> this or that ORM framework.
>>
>> Regards,
>> Juan Hernandez
>
> _______________________________________________
> 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
4:27 a.m.
Hi,
I agree with the idea. The stored procedures/UDF's are just one more place to go when
trying to find out what is really happening. It is not a good solution for database
portability.
Laszlo
----- Original Message -----
From: "Juan Hernandez" <jhernand(a)redhat.com>
To: engine-devel(a)ovirt.org
Sent: Tuesday, March 26, 2013 6: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.
I did an small example of what it takes and how it looks like to have
the SQL code into the DAOs:
http://gerrit.ovirt.org/13347
It isn't rocket science, it isn't an exciting task, it isn't fun, but
something I think we should eventually do.
I appreciate any comment about how and when to do this, including
those
saying that instead of this primitive approach we should use this or
that ORM framework.
Regards,
Juan Hernandez
--
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.
_______________________________________________
Engine-devel mailing list
Engine-devel(a)ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-devel
5:19 a.m.
Removing the stored procedures is indeed a blessing, but the
implementation you offered seems a bit too oldschool (i.e. coding sql
into the code), in my opinion if we go towards a joint effort to
refactor the DAO layer we should go the extra mile and move to Hibernate
(Take II :) )
On 03/26/2013 07:34 PM, Juan Hernandez wrote:
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.
I did an small example of what it takes and how it looks like to have
the SQL code into the DAOs:
http://gerrit.ovirt.org/13347
It isn't rocket science, it isn't an exciting task, it isn't fun, but
something I think we should eventually do.
I appreciate any comment about how and when to do this, including
those saying that instead of this primitive approach we should use
this or that ORM framework.
Regards,
Juan Hernandez
4284
days inactive
4291
days old
25 comments
13 participants
participants (13)
-
Alissa Bonas -
Alon Bar-Lev -
Eli Mesika -
Itamar Heim -
Juan Hernandez -
Laszlo Hornyak -
Libor Spevak -
Liran Zelkha -
Liran Zelkha -
Martin Perina -
Mike Kolesnik -
Tal Nisan -
Yair Zaslavsky