----- Original Message ----- > From: "Eli Mesika" <[email protected]> > To: [email protected] > Sent: Monday, April 1, 2013 11:35:03 PM > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > > > ----- Original Message ----- > > From: "Laszlo Hornyak" <[email protected]> > > To: "Libor Spevak" <[email protected]> > > Cc: "Juan Hernandez" <[email protected]>, [email protected] > > Sent: Thursday, March 28, 2013 5:31:34 PM > > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > > > > > > > ----- Original Message ----- > > > From: "Libor Spevak" <[email protected]> > > > To: "Itamar Heim" <[email protected]> > > > Cc: "Juan Hernandez" <[email protected]>, [email protected] > > > 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" <[email protected]> > > > >>> To: [email protected] > > > >>> 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 > > > > [email protected] > > > > http://lists.ovirt.org/mailman/listinfo/engine-devel > > > > > > _______________________________________________ > > > Engine-devel mailing list > > > [email protected] > > > http://lists.ovirt.org/mailman/listinfo/engine-devel > > > > > _______________________________________________ > > Engine-devel mailing list > > [email protected] > > http://lists.ovirt.org/mailman/listinfo/engine-devel > > > _______________________________________________ > Engine-devel mailing list > [email protected] > http://lists.ovirt.org/mailman/listinfo/engine-devel > _______________________________________________ Engine-devel mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-devel
