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. 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 lzel...@redhat.com To: Eli Mesika emes...@redhat.com Cc: engine-devel@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 emes...@redhat.com To: engine-devel@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 lhorn...@redhat.com To: Libor Spevak lspe...@redhat.com Cc: Juan Hernandez jhern...@redhat.com, engine-devel@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 lspe...@redhat.com To: Itamar Heim ih...@redhat.com Cc: Juan Hernandez jhern...@redhat.com, engine-devel@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
Re: [Engine-devel] Move SQL out of stored procedures
- Original Message - From: Eli Mesika emes...@redhat.com To: engine-devel@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 lhorn...@redhat.com To: Libor Spevak lspe...@redhat.com Cc: Juan Hernandez jhern...@redhat.com, engine-devel@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 lspe...@redhat.com To: Itamar Heim ih...@redhat.com Cc: Juan Hernandez jhern...@redhat.com, engine-devel@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
Re: [Engine-devel] Move SQL out of stored procedures
Hi Liran, - Original Message - From: Liran Zelkha liran.zel...@gmail.com To: Laszlo Hornyak lhorn...@redhat.com Cc: Liran Zelkha lzel...@redhat.com, engine-devel@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 lzel...@redhat.com To: Eli Mesika emes...@redhat.com Cc: engine-devel@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 emes...@redhat.com To: engine-devel@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 lhorn...@redhat.com To: Libor Spevak lspe...@redhat.com Cc: Juan Hernandez jhern...@redhat.com, engine-devel@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 lspe...@redhat.com To: Itamar Heim ih...@redhat.com Cc: Juan Hernandez jhern...@redhat.com, engine-devel@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
Re: [Engine-devel] Move SQL out of stored procedures
- Original Message - From: Libor Spevak lspe...@redhat.com To: Juan Hernandez jhern...@redhat.com Cc: engine-devel@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
Re: [Engine-devel] Move SQL out of stored procedures
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 lspe...@redhat.com To: Juan Hernandez jhern...@redhat.com Cc: engine-devel@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
Re: [Engine-devel] Move SQL out of stored procedures
- Original Message - From: Yair Zaslavsky yzasl...@redhat.com To: Liran Zelkha liran.zel...@gmail.com Cc: engine-devel@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.zel...@gmail.com To: Laszlo Hornyak lhorn...@redhat.com Cc: engine-devel@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 lzel...@redhat.com To: Eli Mesika emes...@redhat.com Cc: engine-devel@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
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 emes...@redhat.com To: engine-devel@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 lhorn...@redhat.com To: Libor Spevak lspe...@redhat.com Cc: Juan Hernandez jhern...@redhat.com, engine-devel@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 lspe...@redhat.com To: Itamar Heim ih...@redhat.com Cc: Juan Hernandez jhern...@redhat.com, engine-devel@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
Re: [Engine-devel] Move SQL out of stored procedures
Hi, apart from SQL vs. stored procedures discussion, I am trying to understand what we can get if we support more databases... Some points: 1. Is there a real need by end-users/customers to run it on e.g. Oracle only? (performance, stability, easier administration). What is the future of PostgreSQL? 2. Is it decided by architectural board, what kind of databases we would like to support? (cannot support any db) 3. Are we talking about the Engine only, or there will be a need to rewrite ETL mappings and upgrade DWH database, or maybe modify JasperReports templates (simply, some DB types behave differently)? Maybe we can look at JasperSoft solution, they support more databases. 4. Current full/incremental upgrade process of PostgreSQL is IMHO very good tuned (it is similar to dbmaintain.org tool - Java implementation - I used successfully on one project - after some changes of course). I do not believe we can use or easily develop general upgrade/migration tool, and XML based (I am sorry Alissa, not sure about Liquibase, I haven't studied it deeply, but there is a need to incrementally change db objects, but sometimes also to migrate data to new structures, the most flexible and quickest is to do it using native SQL, but yes, it depends on the project needs...). 5. As a developer, with every new column I need to write upgrade scripts, prepare test environments and test all scenarios several times on different databases, so time-consuming. On 27.3.2013 13:53, Itamar Heim wrote: On 03/26/2013 08:39 PM, Alon Bar-Lev wrote: - Original Message - From: Juan Hernandez jhern...@redhat.com To: engine-devel@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
Re: [Engine-devel] Move SQL out of stored procedures
- Original Message - From: Libor Spevak lspe...@redhat.com To: Itamar Heim ih...@redhat.com Cc: Juan Hernandez jhern...@redhat.com, engine-devel@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 jhern...@redhat.com To: engine-devel@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
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 jhern...@redhat.com To: engine-devel@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. ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
Re: [Engine-devel] Move SQL out of stored procedures
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 jhern...@redhat.com To: engine-devel@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
Re: [Engine-devel] Move SQL out of stored procedures
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 alo...@redhat.com To: Juan Hernandez jhern...@redhat.com Cc: engine-devel@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 jhern...@redhat.com To: engine-devel@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel ___ Engine-devel mailing list Engine-devel@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. ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
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 jhern...@redhat.com To: engine-devel@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
Re: [Engine-devel] Move SQL out of stored procedures
- 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 alo...@redhat.com To: Juan Hernandez jhern...@redhat.com Cc: engine-devel@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 jhern...@redhat.com To: engine-devel@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel ___ Engine-devel mailing list Engine-devel@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. ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
Re: [Engine-devel] Move SQL out of stored procedures
- 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 alo...@redhat.com To: Juan Hernandez jhern...@redhat.com Cc: engine-devel@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 jhern...@redhat.com To: engine-devel@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
Re: [Engine-devel] Move SQL out of stored procedures
- Original Message - From: Juan Hernandez jhern...@redhat.com To: engine-devel@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
