Обсуждение: High-Profile Advocacy Opportunity: Vbulletin Forum Software
Vbulletin is a leading, high profile, forum software package and, IMHO, the most well thought-out. It currently uses MySQL but alternatives are being discussed in both their very active official forum (http://www.vbulletin.com/forum/) and the main vb hacking forum (http://www.vbulletin.org/forum/). A Postgresql edition of vbulletin would do a lot to raise awareness of Pgsql and highlight it's strengths. I would like to suggest that the time is ripe to see if we can swing vbulletin into our camp. In a recent thread in the official vb forum (http://www.vbulletin.com/forum/showthread.php?t=97747&highlight=PostgreSQL), one Pgsql advocate sang its praises, in particular advanced indexing, and a vb developer, Scott MacVicar, responded "I love Postgresql too but they are missing a vital part that we've become dependant on..". He then linked to the Pgsql ToDo list and indicated that the current lack of " Allow INSERT INTO tab (col1, ..) VALUES (val1, ..), (val2, ..)" was his bone of contention there. He rounded his comment off by saying "There is alot of things that could be used which would provide increased performance such as stored procedures and foreign keys along with views, we'll be having alot of discussion at our meeting next week and i'm sure that other dbms is something that will come up." That particular thread got quite heated and there were another 140 posts. Not all the vb developers are as enthusiastic as Scott; one would much rather see effort directed towards an Oracle edition that would allow them to pursue corporate customers. Another points out that, in terms of actual customer requests, there is far more demand for MS-SQL. The main consensus, however, seems to be that they should sit tight and wait for the introduction of stored procedures in MySQL 5. Personally, I'm convinced that extremely large forums are going become even more important over the next year or so and, for an extremely large forum you need an extremely capable db. I believe that vb should bite the bullet and make the necessary jump. The most compelling argument for Pgsql may, however, be its license: another thread points out that vbulletin is technically in breach of MySQL AB's license (http://www.mysql.com/products/licensing/commercial-license.html). Vb dev Scott responded "If we receive a letter from MySQL AB telling us other wise then we'll reconsider our position". Seems like a pretty shaky foundation for any company to build their one and only product upon! Is there any way we could get the Pgsql devs to take another look at whatever missing features are stopping the vb team basing their application upon Pgsql? The problem with the ToDo list is that, although it was updated just a couple of days ago, there is no indication or estimate of when those ToDos will be Done. Donnacha
Donnacha Mac Gloinn wrote: > Is there any way we could get the Pgsql devs to take another look at > whatever missing features are stopping the vb team basing their > application upon Pgsql? The problem with the ToDo list is that, > although it was updated just a couple of days ago, there is no > indication or estimate of when those ToDos will be Done. There is no TODO indication because we don't know when they will be done. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Wed, Jun 16, 2004 at 04:23:44PM -0800, Donnacha Mac Gloinn wrote: > In a recent thread in the official vb forum > (http://www.vbulletin.com/forum/showthread.php?t=97747&highlight=PostgreSQL), > one Pgsql advocate sang its praises, in particular advanced indexing, > and a vb developer, Scott MacVicar, responded "I love Postgresql too but > they are missing a vital part that we've become dependant on..". He > then linked to the Pgsql ToDo list and indicated that the current lack > of " Allow INSERT INTO tab (col1, ..) VALUES (val1, ..), (val2, ..)" was > his bone of contention there. You got to be kidding. Are you saying that they can't work around the lack of this SELECT syntax, but that they _did_ work around the lack of stored procedures (and who knows what else) in MySQL? Is there _no other_ feature they miss in PostgreSQL? Anyway, if they want a feature, they can as well fund development of it. INSERT with multiple values can't be that hard -- it's never been implemented because there has been no real push for it. I don't foresee it being implemented in any short term future. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Use it up, wear it out, make it do, or do without"
>You got to be kidding. Are you saying that they can't work around the >lack of this SELECT syntax, but that they _did_ work around the lack of >stored procedures (and who knows what else) in MySQL? > > > I have to second this. If they are that lazy, then why don't they just write a quick procedure to dynamically write out their queries? J >Is there _no other_ feature they miss in PostgreSQL? > >Anyway, if they want a feature, they can as well fund development of it. >INSERT with multiple values can't be that hard -- it's never been >implemented because there has been no real push for it. I don't foresee >it being implemented in any short term future. > > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
On Wed, 16 Jun 2004, Alvaro Herrera wrote: > INSERT with multiple values can't be that hard -- it's never been > implemented because there has been no real push for it. I don't foresee > it being implemented in any short term future. How hard it'd likely be depends on whether you'd want to just do it for INSERT or actually fully handle VALUES. AFAICS values can be used as a <query expression> which means as the body of table subqueries, on either side of UNION/EXCEPT/INSERSECT and other such places in addition to its usage in INSERT.
> Anyway, if they want a feature, they can as well fund development of it. > INSERT with multiple values can't be that hard -- it's never been > implemented because there has been no real push for it. I don't foresee > it being implemented in any short term future. MySQL requires that feature to have any chance of fast-loading data in a script. They have no COPY command (LOAD INFILE is different) so they need a bulk insert to get some actual load speed. Chris
Oops! postgresql.org@donnacha.com ("Donnacha Mac Gloinn") was seen spray-painting on a wall: > Vbulletin is a leading, high profile, forum software package and, IMHO, > the most well thought-out. It currently uses MySQL but alternatives are > being discussed in both their very active official forum > (http://www.vbulletin.com/forum/) and the main vb hacking forum > (http://www.vbulletin.org/forum/). > > A Postgresql edition of vbulletin would do a lot to raise awareness of > Pgsql and highlight it's strengths. I would like to suggest that the > time is ripe to see if we can swing vbulletin into our camp. > > In a recent thread in the official vb forum > (http://www.vbulletin.com/forum/showthread.php?t=97747&highlight=PostgreSQL), > one Pgsql advocate sang its praises, in particular advanced indexing, > and a vb developer, Scott MacVicar, responded "I love Postgresql too but > they are missing a vital part that we've become dependant on..". He > then linked to the Pgsql ToDo list and indicated that the current lack > of " Allow INSERT INTO tab (col1, ..) VALUES (val1, ..), (val2, ..)" was > his bone of contention there. This begs the question: - Are they using arbitrary INSERT statements of this sort? - Or could this be achieved by performing a stored procedure that might even be a better abstraction? If the goal of the exercise is to do something like creating lines in a message, then I'd suggest the thought of doing something like: select add_line(key, 'contents', 'some attribute'), add_line(key, 'more contents', 'some attribute'), add_line(key, 'still more contents', 'some attribute'), add_line(key, 'even contents', 'some attribute'); Frankly, I would find it surprising if this was a true "bottleneck" against choosing PostgreSQL. > Is there any way we could get the Pgsql devs to take another look at > whatever missing features are stopping the vb team basing their > application upon Pgsql? The problem with the ToDo list is that, > although it was updated just a couple of days ago, there is no > indication or estimate of when those ToDos will be Done. A valuable thing might also be to suggest workarounds... -- If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me http://www3.sympatico.ca/cbbrowne/rdbms.html If the odds are a million to one against something occuring, chances are 50-50 it will.
Donnacha Mac Gloinn wrote: >In a recent thread in the official vb forum >(http://www.vbulletin.com/forum/showthread.php?t=97747&highlight=PostgreSQL), > > At the last page there is a link to this site http://www.livejournal.com/users/alathren/ (the same article is also available at http://www.livejournal.com/users/alathren/853.html). Basically the author describes why and how they migreated their CMS from MySQL to PostgreSQL. I think this article could be well placed to the PostgreSQL advocacy website? Kaarel
"Alvaro Herrera" <alvherre@dcc.uchile.cl> said: > You got to be kidding. Are you saying that they can't work around the > lack of this SELECT syntax, but that they _did_ work around the lack of > stored procedures (and who knows what else) in MySQL? I don't know enough about DBs to precisely understand their concerns but the developer who seems most enthusiastic about PgSQL, Scott McVicar, has mentioned that it doesn't yet handle extended inserts. Scott: "The system that handles that area is due a re-write soon and it may include support for multiple inserts." You're quite right, they HAVE heavily optimized their application to handle MySQL's foibles, they don't use an abstraction layer, a PgSQL edition of vB would involve a major rewrite, I don't know why inserts are a particularly huge issue for them but I will ask them to clarify their position. "Alvaro Herrera" <alvherre@dcc.uchile.cl> said: > Is there _no other_ feature they miss in PostgreSQL? I will try to get as full a picture as possible. "Alvaro Herrera" <alvherre@dcc.uchile.cl> said: > it's never been > implemented because there has been no real push for it. I don't foresee > it being implemented in any short term future. Well, it's useful to be aware of what companies PERCEIVE to be problems with PgSQL. We are only aware of vB's issue with Inserts because they run a particularly open and active forum, it's almost certain that other people have developed precisely the same doubts/preconceptions with regard to PgSQL. As advocates, it makes sense for us to develop a deep understanding of how people who AREN'T PgSQL developers perceive it; it isn't enough for PgSQL to be better, to remain healthy every OSS project needs to keep attracting fresh users/contributors. I see vB as being particulary fertile ground because it has an absolutely huge community of hackers who are currently focussing their energies upon countering MySQL's weaknesses. If they could move their attention to PgSQL, we would all gain. "Alvaro Herrera" <alvherre@dcc.uchile.cl> said: > Anyway, if they want a feature, they can as well fund development of it. Agreed, as a company they certainly have a lot to gain by not putting all their eggs in one database, and the lack of clarity surrounding MySQL's licensing is worrying. "Alvaro Herrera" <alvherre@dcc.uchile.cl> said: > INSERT with multiple values can't be that hard It's probably something they could produce inhouse if they put their minds to it, their developers generally seem to know what they're doing and vBulletin 3 itself is a beautiful piece of work. "Joshua D. Drake" <jd@commandprompt.com> said: > If they are that lazy, then why don't they just > write a quick procedure to > dynamically write out their queries? I think it's quite short-sighted to presume developers are lazy just because they haven't yet got to grips with PgSQL, they've only recently completed a major release (3.0) and have a huge number of customer requests to deal with. Awareness of PgSQL is not that high and, so far, the number of knowledgeable people pushing it within both their dev team and their userbase is relatively low - this means that PgSQL must be pushed on it's merits rather than popular perception. As advocates, we can oil the wheels by putting a clear, comprehensive and compelling case for the adoption of PgSQL. It's worth doing that because, as the leader in it's field, where vB goes other forum packages will follow, each bringing with them a huge community of users. Within its small-to-medium Web Developer niche, VB WOULD BE AN EXCELLENT FLAGSHIP FOR PGSQL. "Alvaro Herrera" <alvherre@dcc.uchile.cl> said: > How hard it'd likely be depends on whether you'd want to just do it for > INSERT or actually fully handle VALUES. AFAICS values can be used as a > <query expression> which means as the body of table subqueries, on either > side of UNION/EXCEPT/INSERSECT and other such places in addition to its > usage in INSERT. "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> said: > MySQL requires that feature to have any chance of fast-loading data in a > script. They have no COPY command (LOAD INFILE is different) so they > need a bulk insert to get some actual load speed. I will pass on all the technical suggestions and insights I get here. "Christopher Browne" <cbbrowne@acm.org> said: > This begs the question: > > - Are they using arbitrary INSERT statements of this sort? > > - Or could this be achieved by performing a stored procedure that > might even be a better abstraction? > > If the goal of the exercise is to do something like creating lines in > a message, then I'd suggest the thought of doing something like: > > select add_line(key, 'contents', 'some attribute'), > add_line(key, 'more contents', 'some attribute'), > add_line(key, 'still more contents', 'some attribute'), > add_line(key, 'even contents', 'some attribute'); > > Frankly, I would find it surprising if this was a true "bottleneck" > against choosing PostgreSQL. I will ask the vB devs to address these questions and get back to you with their answers. "Kaarel" <kaarel@future.ee> said: > At the last page there is a link to this site > http://www.livejournal.com/users/alathren/ (the same article is also > available at http://www.livejournal.com/users/alathren/853.html). > Basically the author describes why and how they migreated their CMS from > MySQL to PostgreSQL. I think this article could be well placed to the > PostgreSQL advocacy website? Excellent article, it particularly stresses the suitability of PgSQL for large applications such as CMSes or, indeed, heavily-trafficked forums. Thanks Bruce, Alvaro, Joshua, Kaarel and the two Christophers, I will get back to you with a clearer picture of vB's position, I hope you agree that their adoption of PgSQL would be a positive development. Donnacha.
Donnacha, > You're quite right, they HAVE heavily optimized their application to > handle MySQL's foibles, they don't use an abstraction layer, a PgSQL > edition of vB would involve a major rewrite, I don't know why inserts > are a particularly huge issue for them but I will ask them to clarify > their position. My personal conjecture: The "extended insert" is not on the Postgres TODO list because: a) It's not ANSI SQL standard. b) We have COPY, which is better. So given their raising that ridiculously small point, as well as their flippant answer on MySQL licensing, I don't think the arguments they are advancing are real. They are excuses for not switching to *anything* else, because the vbulletin developers don't know, and don't want to learn, anything new, and *certainly* don't want to re-write their whole application to utilize an abstraction layer. I would bet you that the folks advocating Oracle will meet with a similar stonewall. Which is their perogative, it's their application. The reason I bring this up with you is that I'd rather not see *you* beat your head against a wall. From my perspective, this situation sounds exactly like that of Compiere, where the project leads have found excuses for 4 years not to work on compatibility with other databases. In PostgreSQL's case, it was lack of nested transactions. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: > My personal conjecture: > > The "extended insert" is not on the Postgres TODO list because: > a) It's not ANSI SQL standard. > b) We have COPY, which is better. Unfortunately that statement is mostly wrong: - It is on the TODO list. - It is standard. - It is better than COPY.
On Thu, Jun 17, 2004 at 08:13:19PM +0200, Peter Eisentraut wrote: > Josh Berkus wrote: > > My personal conjecture: > > > > The "extended insert" is not on the Postgres TODO list because: > > a) It's not ANSI SQL standard. > > b) We have COPY, which is better. > > Unfortunately that statement is mostly wrong: > - It is on the TODO list. > - It is standard. > - It is better than COPY. Why is it better than COPY? -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "No renuncies a nada. No te aferres a nada."
Alvaro Herrera wrote: > On Thu, Jun 17, 2004 at 08:13:19PM +0200, Peter Eisentraut wrote: > > Josh Berkus wrote: > > > My personal conjecture: > > > > > > The "extended insert" is not on the Postgres TODO list because: > > > a) It's not ANSI SQL standard. > > > b) We have COPY, which is better. > > > > Unfortunately that statement is mostly wrong: > > - It is on the TODO list. > > - It is standard. > > - It is better than COPY. > > Why is it better than COPY? Because you can't insert 1+1 using COPY. There are nontrivial extensions of that example, of course.
Peter, > - It is standard. Well, blow me to Bermuda! When did that get added? -- -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Enterprise vertical business josh@agliodbs.com and data analysis solutions (415) 752-2387 and database optimization fax 651-9224 utilizing Open Source technology San Francisco
On Thu, 17 Jun 2004, Josh Berkus wrote: > Peter, > > > - It is standard. > > Well, blow me to Bermuda! When did that get added? SQL92 (Full). ;)
Martha Stewart called it a Good Thing when josh@agliodbs.com (Josh Berkus) wrote: > Peter, > >> - It is standard. > > Well, blow me to Bermuda! When did that get added? I have an overview of "SQL 3;" it doesn't mention it. -- let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];; http://www.ntlug.org/~cbbrowne/linux.html Entia non sunt multiplicanda sine necessitate.
On Thu, 2004-06-17 at 19:45, Alvaro Herrera wrote: > On Thu, Jun 17, 2004 at 08:13:19PM +0200, Peter Eisentraut wrote: > > Josh Berkus wrote: > > > My personal conjecture: > > > > > > The "extended insert" is not on the Postgres TODO list because: > > > a) It's not ANSI SQL standard. > > > b) We have COPY, which is better. > > > > Unfortunately that statement is mostly wrong: > > - It is on the TODO list. > > - It is standard. > > - It is better than COPY. I agree with all 3 of these, because: - Yes, its on the TODO list - It is a standard because it is in use by many people, even though I strongly doubt its on the ANSI list. - It is "better" than COPY in certain situations. > Why is it better than COPY? COPY is designed for bulk data loading from files etc. Extended INSERTs are used to minimise the number of round-trips to the database when issuing a few number of similar INSERTs, as when you do an Order/Order-Line (i.e. Master and many similar Details records). Oracle's Array INSERT syntax allowed a very similar saving in round-trips. MySQL's REPLACE command is also a simpler form of MERGE command (UPDATE/INSERT), also designed to minimise number of round-trips to the database. Note that by doing so they minimise locking time and by doing so overcome difficulties with transaction isolation levels, both of which are problems for them - and the cause of scalability issues for their users. Donnacha: are your associates aware of such issues with MySQL? IMHO: Those MySQL two features illustrate the one thing that is great about MySQL: they aren't afraid to break the rules AND argue that by doing so they have actually improved things...great, but not good. On the technical side, I think we should have both extended INSERT and REPLACE on the TODO list... Incidentally, discussing other approaches: Teradata uses "multiple statement requests", which allow you to submit multiple otherwise unrelated SQL statements in a single request packet to the database. Best Regards, Simon Riggs
On Tue, Jun 22, 2004 at 08:51:07PM +0100, Simon Riggs wrote: > > Why is it better than COPY? > COPY is designed for bulk data loading from files etc. Extended INSERTs > are used to minimise the number of round-trips to the database when > issuing a few number of similar INSERTs, as when you do an > Order/Order-Line (i.e. Master and many similar Details records). Huh? I think you should be able to issue a single multivalued insert to a single table -- two queries in your Order/Order-Line example, not one. You can do the same in Postgres issuing several commands separated with semicolons. You can even use a single transaction by using BEGIN and COMMIT at the start and end of the string. I think the real gain of multivalued insert will come from using batch heap and index insertions, not roundtrip to the application. At least, the roundtrip savings can be had with today's Postgres. > On the technical side, I think we should have both extended INSERT and > REPLACE on the TODO list... I don't think REPLACE is a good idea (useless non standard MySQL-ism). MERGE and multivalued INSERT are. But they depend on someone actually doing the work. Both are on the TODO list, I think. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual)
On Tue, 2004-06-22 at 21:05, Alvaro Herrera wrote: > On Tue, Jun 22, 2004 at 08:51:07PM +0100, Simon Riggs wrote: > > > > Why is it better than COPY? > > > COPY is designed for bulk data loading from files etc. Extended INSERTs > > are used to minimise the number of round-trips to the database when > > issuing a few number of similar INSERTs, as when you do an > > Order/Order-Line (i.e. Master and many similar Details records). > > Huh? I think you should be able to issue a single multivalued insert to > a single table -- two queries in your Order/Order-Line example, not one. > Better explained, that would be: **If you're doing an Order/Order-Line insert you'll do 1 INSERT to Order and then multiple similar INSERTs to Order-Line: the multiple INSERTs into Order-Line are when you'd use the extended INSERT feature.** > You can do the same in Postgres issuing several commands separated with > semicolons. You can even use a single transaction by using BEGIN and > COMMIT at the start and end of the string. > > I think the real gain of multivalued insert will come from using batch > heap and index insertions, not roundtrip to the application. At least, > the roundtrip savings can be had with today's Postgres. > Yes, there are PostgreSQL ways of doing things, not my point, I was explaining the MySQL rationale as I saw it. > > On the technical side, I think we should have both extended INSERT and > > REPLACE on the TODO list... > > I don't think REPLACE is a good idea (useless non standard MySQL-ism). > MERGE and multivalued INSERT are. But they depend on someone actually > doing the work. Both are on the TODO list, I think. I think doing REPLACE is a trivial subset of MERGE, so should be easy enough to pull off once MERGE gets done. Regards, Simon Riggs
On Tue, 22 Jun 2004 20:51:07 +0100, "Simon Riggs" <simon@2ndquadrant.com> said: > > Donnacha: are your associates aware of such issues with MySQL? > Although some vB team members threw up technical complications as the barrier to creating a PostgreSQL edition, when I and a couple of others shook the tree a bit, it became apparent that, while there is some acceptance that it would be NICE not to have to rely entirely upon MySQL, there isn't the organisational will to actually put the effort in. The technical excuses (quite rightly derided on this list) are merely a fig leaf. The crystalisation of this was that, when the MySQL AB got in touch last week to hit them up for licensing fees, they quietly rolled over. I tried to highlight that this was precisely the sort of ransom situation you get when you lack supplier diversity and that it might be a good idea to move an PostgreSQL edition from being a vague aspiration to actually putting it onto some sort of timeline. The official response was more or less along the lines of "We are in contact with MySQL AB, we're working something out, it won't affect the customers, so, quit bugging us". Financially, vB are riding high at the moment, have the best product in their field and don't feel under any immediate pressure to pursue the long-term advantages that PostgreSQL could offer. That's understandable but frustrating for those of us who are thinking ahead, which is why I wrote the following response: <vbulletin.com post begins>>>>>>> "People seem to be blind to that fact that, as customers, anything vBulletin pays, we pay. I saw this same blindness when SCO successfully conned EV1, the reaction of most customers was "oh well, as long as EV1 is paying it and not me". Then everyone was surprised to see EV1's prices jump. Anything that increases vB's base costs will affect their price flexibility and competitiveness. I'm not sure how much MySQL AB will charge vB but Zak's comment in this thread regarding vB's price suggests that we are talking about a small percentage of each and every sale. That may not sound like a big deal but if serious competition to vB emerges, possibly one using a genuinely free DB like PostgreSQL, this extra tax will hobble vB's abiltiy to price-match. VB deservedly lead their niche but I'm alarmed that they seem to be a little too relaxed about their position. Things move fast in software and no-one's lead is unassailable. If they can't retain the hunger that got them to where they are today someone else will pop up to eat their lunch. The idea that something, such as PostgreSQL, which could make their product more powerful is relegated far down their To-Do list leaves the door wide open to others. I understand that any organisation only has a limited number of projects it can handle efficiently but I would have thought vB was in an ideal position to, in effect, outsource this problem by funding the development of extended inserts or whatever it is you need via Postgresql.org. Apparently, it wouldn't be hard to do, it wouldn't cost much (certainly nothing like what you'll be shelling out to MySQL AB) and you'd gain a lot of goodwill in the FOSS community. Of course, the more complicated side of the equation would be creating an edition of vB to remove the current MySQL work-arounds and to take advantage of PostgreSQL's features. That effort would, however, be more than justified by the extra manoeuvrability it would give your company. Also, a lot of the work you'd be doing on Stored Procedures etc is work you'll have to do anyway as soon as MySQL catches up. As the producers of an extremely popular software package, the vB team are inundated with feature requests and probably haven't even had a proper chance to regain their breath after the release of vB3. All the same, they should stand back and look at the longer-term picture. I've run vB's technical objections to PostgreSQL (as variously stated in these forums) past various people in a position to understand such things and the general consensus is "You've got to be kidding". I can't help getting the impression that the real argument against it is that dealing with another db just sounds like too much hassle. Somewhere out there, you can be pretty sure, there are hungry programmers to whom it won't be too much hassle." <<<<<<<vbulletin.com post ends> So, my current feeling is that, for organisational rather then technical reasons, PostgreSQL vB is NOT going to happen BUT I'm still extremely interested in understanding what their technical objections are because, hopefully, a decent FOSS forum project will turn up sometime and, when it does, understanding those issues will help me to make sure it takes full advantage of PostgreSQL. Essentially, what's needed is forum software that is at least as efficient as vB, as easy to use, as easy to manage and, hopefully, more scalable than vB. The most popular FOSS forum at the moment, PhpBB (PHP/MySQL), is soundly beaten by vB on all those scores and, unfortunately, when you're dealing with large forums, you need the very best, the licensing fee is pretty much immaterial because a lack in any of those areas will cost you plenty over time. On an idealistic "Communication as a Human Right" level, however, it's vital that everyone have access to completely free and technically excellent forum software. I believe that the best way to achieve this is to build it from PostgreSQL up. So, if it's okay with everyone here, I'll be getting back to you with a more indepth summation of vB's technical issues with PostgreSQL, hoping to glean the insights I'll need to get such a project rolling. Donnacha
On Tue, Jun 22, 2004 at 10:14:11PM +0100, Simon Riggs wrote: > On Tue, 2004-06-22 at 21:05, Alvaro Herrera wrote: > > On Tue, Jun 22, 2004 at 08:51:07PM +0100, Simon Riggs wrote: > > > > > > Why is it better than COPY? > > > > > COPY is designed for bulk data loading from files etc. Extended INSERTs > > > are used to minimise the number of round-trips to the database when > > > issuing a few number of similar INSERTs, as when you do an > > > Order/Order-Line (i.e. Master and many similar Details records). > > > > Huh? I think you should be able to issue a single multivalued insert to > > a single table -- two queries in your Order/Order-Line example, not one. > > Better explained, that would be: > **If you're doing an Order/Order-Line insert you'll do 1 INSERT to Order > and then multiple similar INSERTs to Order-Line: the multiple INSERTs > into Order-Line are when you'd use the extended INSERT feature.** Yeah, I thought you could mean that. Another nicety of multivalued insert compared to COPY is that you could use currval() of a sequence that you just used in the insert to the Order (of course, you can do that with a multiquery string too). > > You can do the same in Postgres issuing several commands separated with > > semicolons. You can even use a single transaction by using BEGIN and > > COMMIT at the start and end of the string. > > Yes, there are PostgreSQL ways of doing things, not my point, I was > explaining the MySQL rationale as I saw it. Ah, ok. > > I don't think REPLACE is a good idea (useless non standard MySQL-ism). > > MERGE and multivalued INSERT are. But they depend on someone actually > > doing the work. Both are on the TODO list, I think. > > I think doing REPLACE is a trivial subset of MERGE, so should be easy > enough to pull off once MERGE gets done. Hmm ... and what if later the SQL committee adds a (different) REPLACE command? Anyway, Postgres doesn't follow MySQL's lead ... some people here wants to rule the database world first and become the SQL editors next. *chuckle* -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)
On Tue, 22 Jun 2004 18:41:39 -0400, "Rod Taylor" <pg@rbt.ca> said: > > Funnily enough, lack of PostgreSQL support is the reason why we chose to > go with another BB software rather than vB. Maintenance of a MySQL > installation just for the BB software wasn't worth it. > > So, whether they want to acknowledge it or not, they're not the best > product in their field for some. > Which products do you consider better, which did you eventually settle upon and why?
> Financially, vB are riding high at the moment, have the best product in > their field and don't feel under any immediate pressure to pursue the > long-term advantages that PostgreSQL could offer. That's understandable > but frustrating for those of us who are thinking ahead, which is why I > wrote the following response: Funnily enough, lack of PostgreSQL support is the reason why we chose to go with another BB software rather than vB. Maintenance of a MySQL installation just for the BB software wasn't worth it. So, whether they want to acknowledge it or not, they're not the best product in their field for some.
On Tue, 2004-06-22 at 15:49, Donnacha Mac Gloinn wrote: > On Tue, 22 Jun 2004 20:51:07 +0100, "Simon Riggs" > <simon@2ndquadrant.com> said: > > > > Donnacha: are your associates aware of such issues with MySQL? > > > > Although some vB team members threw up technical complications as the > barrier to creating a PostgreSQL edition, when I and a couple of others > shook the tree a bit, it became apparent that, while there is some > acceptance that it would be NICE not to have to rely entirely upon > MySQL, there isn't the organisational will to actually put the effort > in. The technical excuses (quite rightly derided on this list) are > merely a fig leaf. > > The crystalisation of this was that, when the MySQL AB got in touch last > week to hit them up for licensing fees, they quietly rolled over. I > tried to highlight that this was precisely the sort of ransom situation > you get when you lack supplier diversity and that it might be a good > idea to move an PostgreSQL edition from being a vague aspiration to > actually putting it onto some sort of timeline. The official response > was more or less along the lines of "We are in contact with MySQL AB, > we're working something out, it won't affect the customers, so, quit > bugging us". They have made their beds, let them sleep in them. I use phpbb and am quite happy with it. I supports PostgreSQL quite well. Another nice project that supports PostgreSQL is phpwiki. Both are well written projects. While they tend to focus on initial development of new versions on MySQL, they use abstraction throughout, and the PostgreSQL support is ready by release time, even if it has some issues during development. If MySQL were a better product, it might be worth paying money for, but it's not a better product than postgresql, and when it starts costing money, it's time to ditch it, IMnsHO/
On Tue, 2004-06-22 at 18:45, Donnacha Mac Gloinn wrote: > On Tue, 22 Jun 2004 18:41:39 -0400, "Rod Taylor" <pg@rbt.ca> said: > > > > Funnily enough, lack of PostgreSQL support is the reason why we chose to > > go with another BB software rather than vB. Maintenance of a MySQL > > installation just for the BB software wasn't worth it. > > > > So, whether they want to acknowledge it or not, they're not the best > > product in their field for some. > > > > Which products do you consider better, which did you eventually settle > upon and why? The big one was the ability to integrate logins cleanly. Either via a transactional interface OR (in this case) via a PostgreSQL view. We're going to use phpbb and edit it to remove abilities like changing logins & passwords, instead relying on other interfaces for this.
On Tue, 22 Jun 2004 17:27:44 -0600, "Scott Marlowe" <smarlowe@qwest.net> said: > > They have made their beds, let them sleep in them. I use phpbb and am > quite happy with it. I supports PostgreSQL quite well. Another nice > project that supports PostgreSQL is phpwiki. Both are well written > projects. While they tend to focus on initial development of new > versions on MySQL, they use abstraction throughout, and the PostgreSQL > support is ready by release time, even if it has some issues during > development. Well, that's the problem, by deploying abstraction they ALLOW you to use PostgreSQL but they don't take advantage of it's strengths. Or, indeed, MySQL's strengths! What's the point? That's why I think we should encourage applications to be built from PostgreSQL up. You encourage people to code specifically FOR PostgreSQL by explaining that it's the only RDBMS that DEFINITELY won't expose them to possible future "shareholder value maximization strategies". Perhaps it could be expressed along the lines of PostgreSQL having both technical AND financial scalability - no matter how successful you get, no-one will come knocking, looking for licensing fees. > If MySQL were a better product, it might be worth paying money for, but > it's not a better product than postgresql, and when it starts costing > money, it's time to ditch it, IMnsHO/ It's not so much the money but, rather, the fact that, by building your product atop someone else's proprietary, you are ceding control of your work. I'm not saying that the current management at MySQL AB are bad people but who's to say that vultures won't take over at some point in the future - don't forget that SCO used to be highly respected. As a simple matter of logic, you should never dilute your control unless there's a highly compelling reason. That's why the BSD license rocks. Donnacha
On Tue, 2004-06-22 at 19:27, Scott Marlowe wrote: > They have made their beds, let them sleep in them. I use phpbb and am > quite happy with it. I supports PostgreSQL quite well. Another nice > project that supports PostgreSQL is phpwiki. Both are well written > projects. Another for that list is, of course, GForge... PostgreSQL all the way! Yours, Tom
""Donnacha Mac Gloinn"" <postgresql.org@donnacha.com> writes: > Well, that's the problem, by deploying abstraction they ALLOW you to use > PostgreSQL but they don't take advantage of it's strengths. Or, indeed, > MySQL's strengths! What's the point? > ... > As a simple matter of logic, you should never dilute your control unless > there's a highly compelling reason. > You just answered your own question. An abstraction rocks too unless it's badly written. It gives you full freedom to choose. While I don't doubt that PostgreSQL is an excellent choice in most cases, there might be reasons not to use it. The use of BSD licensed software is not an obvious choice in many organisations. Personally, I'd never encourage anyone to hardcode an application for a specific database. "never dilute control" == "always use an abstraction" Kind regards, Thomas Hallgren
Thomas, > Personally, I'd never encourage anyone to > hardcode an application for a specific database. > > "never dilute control" == "always use an abstraction" <grin> I just did a 45 minute presentation on this last night. "database independance" is very appropriate for some projects. Web bulletin boards are usually among them. If, however, your application depends on high performance, strong security, or rigorous and complex data integrity rules, the use of a client-side database abstraction layer is, at best, impractical. There is no one design strategy which is better than all others regardless of the application. Except maybe documenting your code. ;-) -- Josh Berkus Aglio Database Solutions San Francisco
> There is no one design strategy which is better than all others regardless of > the application. Except maybe documenting your code. ;-) > I agree, although good control can be achived using abstractions you sometimes need to compromize to get the best performance. I advocate you do this reluctantly rather than eagerly. Great performance can often be achived in conjunction with good separation of concern and use of sane design principles. Kind regards, Thomas Hallgren
Thomas, > I agree, although good control can be achived using abstractions you > sometimes need to compromize to get the best performance. I advocate you do > this reluctantly rather than eagerly. Great performance can often be > achived in conjunction with good separation of concern and use of sane > design principles. Nope, I'm still not communicating, I guess. Lemme give some examples: Open-source online bulletin board: database independance with abstracted classes. Portability is very important; data integrity is not. Legal billing and accounting application: heavy database dependance. Reasons: Security: data must be 99.9999% confidential and secure. This requires extensive use of in-database security mechanisms. Data Integity: transactions, especially in the GL, must be 99.9999% trustworthy. This requires extensive use of in-database data integrity controls. Performance: main GL is expected to grow to 2 million records over the first year. This requires database tuning and construction of queries aimed at maximizing performance on the chosen platform. "Database independence" is a strategy which is suitable for a limited class of web applications, and not much else. One can "abstract" the considerations in the 2nd example. However, it's an enormous amount of work; it amounts to re-implementing the application on each target platform, which only really the display code being the same; all of the middleware which touches the database will have to be refactored. I've seen some very large apps which did this, but it's not cheap. -- Josh Berkus Aglio Database Solutions San Francisco
Re: Design Strategy WAS: High-Profile Advocacy Opportunity:VbulletinForum
От
"Thomas Hallgren"
Дата:
Josh, > Nope, I'm still not communicating, I guess. Lemme give some examples: > You are communicating just fine. But I think we come from very different backgrounds. > Open-source online bulletin board: database independance with abstracted > classes. Portability is very important; data integrity is not. > > Legal billing and accounting application: heavy database dependance. > Reasons: Security: data must be 99.9999% confidential and secure. This > requires extensive use of in-database security mechanisms. > Data Integity: transactions, especially in the GL, must be 99.9999% > trustworthy. This requires extensive use of in-database data integrity > controls. > Performance: main GL is expected to grow to 2 million records over the first > year. This requires database tuning and construction of queries aimed at > maximizing performance on the chosen platform. > > "Database independence" is a strategy which is suitable for a limited class of > web applications, and not much else. > > One can "abstract" the considerations in the 2nd example. However, it's an > enormous amount of work; it amounts to re-implementing the application on > each target platform, which only really the display code being the same; all > of the middleware which touches the database will have to be refactored. > I've seen some very large apps which did this, but it's not cheap. > Still an abstraction might be worth it for many ISV's. The refactoring you mention is only needed if you don't cater for the independence from the very start. I could give you an example of financial vendor that deeply regret that they didn't listen to people that warned them about implementing all logic in Sybase stored procedures. I guess part of their decision was for the reasons you mention. In hindsight, and with a new design in place that performs extremely well, everyone asks why they didn't use an abstraction from the very start. Another fairly similar example is an ISV doing Supply Chain Execution. They are more or less dead now due to (unnecessary) database lock-in. It just wasn't possible to rewrite everything without starting from square one. I don't argue that data integrity should be dealt with in the database. And I don't argue that a heavy database dependency sometimes is the absolute best choice for the reasons you mention. All I'm saying is that in many cases, a careful analyze and design phase can result in a design where more than one database fit the needs. My guess is that many of the applications that you reckon would run very well if they where developed with a heavy dependency on PostgreSQL could gain comparable performance if they where heavily dependent on, say, Oracle. Now, to abstract the differences between the PostgreSQL and the Oracle solution without sacrificing performance nor integrity is perhaps not that hard (provided you do it from the very beginning). And for an ISV, it's generally worth to go that route since many customers have strict policies regarding databases. Independence is king if you develop for the J2EE market (not just for databases) and since I've been developing J2EE frameworks for many years I can assure you, it's not just a "limited class of web applications" that's being addressed. Kind regards, Thomas Hallgren
> Still an abstraction might be worth it for many ISV's. The refactoring you > mention is only needed if you don't cater for the independence from the very > start. One of the things that PostgreSQL is nice at is the ability to write your database procedures in the same language as your middleware in many cases (ignore java for now). With a little bit of abstraction around the database handle itself (libpq vs SPI) and now you can shove the procedure into the database or pull it back to the middware when you port to another db. Write in such a way that you rely on database triggers or application side triggers based on database type (easy enough). Not perfect by any means, but certainly can make life easier.
Rod Taylor wrote: >One of the things that PostgreSQL is nice at is the ability to write >your database procedures in the same language as your middleware in many >cases (ignore java for now). > > No :-) Please don't ignore java. Some months ago I published the pljava project on gborg. Major reason, I concur with what you say here and if any lanugage has a great potenital of actually enabling advanced server side logic in a database independent way, I think that will be Java. In addition, an increasing number of n-tier applications use Java in the middle tier. >With a little bit of abstraction around the database handle itself >(libpq vs SPI) and now you can shove the procedure into the database or >pull it back to the middware when you port to another db. > Right, and I include a JDBC implementation on top of SPI partly for that reason. >Write in such a way that you rely on database triggers or application >side triggers based on database type (easy enough). > >Not perfect by any means, but certainly can make life easier. > Yes, absolutely. This is an example of a design that would be part of the abstractions I'm rambling about. Add the some effort to actually make the triggers etc. as db neutral as possible and the switch between (competent) databases can be made with a bareable amount of pain. Kind regards, Thomas Hallgren
Thomas, > You are communicating just fine. But I think we come from very different > backgrounds. Nope, nope, I'm not. Sorry. I'm being completely obtuse. Ok, there's 2 knids of database independence: 1) impelemtation of a full abstraction layer, including optimized query and function calls for each class and method in the application, and often switched query choices depending on the database system installed. Implementation of schemas for each of several database systems to work with this. 2) "Dumbing down" your SQL calls to the point where they will run on almost anything. It's (2) I was saying was "only useful for a limited range of web applications" and (1) that "required a large budget". Hopefully that's clearer. > I could give you an example of financial vendor that deeply regret that > they didn't listen to people that warned them about implementing all logic > in Sybase stored procedures. I guess part of their decision was for the > reasons you mention. In hindsight, and with a new design in place that > performs extremely well, everyone asks why they didn't use an abstraction > from the very start. Another fairly similar example is an ISV doing Supply > Chain Execution. They are more or less dead now due to (unnecessary) > database lock-in. It just wasn't possible to rewrite everything without > starting from square one. But how is this unique to databases? Proprietary frameworks stagnate and die, programming languages become obsolete, and infrastructure components get discontinued. Even open source projects can die, leaving the one company still using their technology to try to keep the project up to date. Just talk to anyone who built a massive application in Cold Fusion or NetObjects in 1999. Seemed like a pretty good idea to management then, didn't it? > I don't argue that data integrity should be dealt with in the database. And > I don't argue that a heavy database dependency sometimes is the absolute > best choice for the reasons you mention. All I'm saying is that in many > cases, a careful analyze and design phase can result in a design where more > than one database fit the needs. My guess is that many of the applications > that you reckon would run very well if they where developed with a heavy > dependency on PostgreSQL could gain comparable performance if they where > heavily dependent on, say, Oracle. Now, to abstract the differences between > the PostgreSQL and the Oracle solution without sacrificing performance nor > integrity is perhaps not that hard (provided you do it from the very > beginning). I think you and I have different definitions of "not that hard". Or maybe it's just that my projects tend to have much more limited budgets and timelines than yours. The sort of abstraction you're talking about -- type #1 -- isn't real possible when the client wants the program 2 weeks ago, and cares more about the bill than the software's longevity. > And for an ISV, it's generally worth to go that route since > many customers have strict policies regarding databases. Why is this, anyway? It puzzles me. Managers don't tend to have opinions on programming languages, web servers, network devices, or power supplies. They'll use whatever is recommended. But they have strong, immovable, and often wholly uninformed opinions about databases. Why? > Independence is king if you develop for the J2EE market (not just for > databases) and since I've been developing J2EE frameworks for many years I > can assure you, it's not just a "limited class of web applications" that's > being addressed. Yeah, that would put you in type #1. -- Josh Berkus Aglio Database Solutions San Francisco
On Thu, 2004-06-24 at 16:12, Josh Berkus wrote: > Thomas, > > > You are communicating just fine. But I think we come from very different > > backgrounds. > > Nope, nope, I'm not. Sorry. I'm being completely obtuse. > > Ok, there's 2 knids of database independence: > > 1) impelemtation of a full abstraction layer, including optimized query and > function calls for each class and method in the application, and often > switched query choices depending on the database system installed. > Implementation of schemas for each of several database systems to work with > this. > > 2) "Dumbing down" your SQL calls to the point where they will run on almost > anything. > > It's (2) I was saying was "only useful for a limited range of web > applications" and (1) that "required a large budget". > Somewhere between 1 and 2 you have people designing schemas tailored to boosting performance on one database vs. another. For example, using count(*) table in postgresql apps that would be unneeded in my$ql, or using crazy table layouts to make up for a lack of views or partial indexes. Or maybe they add in extra application code to make up for a lack of triggers... > Hopefully that's clearer. > > > I could give you an example of financial vendor that deeply regret that > > they didn't listen to people that warned them about implementing all logic > > in Sybase stored procedures. I guess part of their decision was for the > > reasons you mention. In hindsight, and with a new design in place that > > performs extremely well, everyone asks why they didn't use an abstraction > > from the very start. Another fairly similar example is an ISV doing Supply > > Chain Execution. They are more or less dead now due to (unnecessary) > > database lock-in. It just wasn't possible to rewrite everything without > > starting from square one. > > But how is this unique to databases? Proprietary frameworks stagnate and > die, programming languages become obsolete, and infrastructure components get > discontinued. Even open source projects can die, leaving the one company > still using their technology to try to keep the project up to date. > > Just talk to anyone who built a massive application in Cold Fusion or > NetObjects in 1999. Seemed like a pretty good idea to management then, > didn't it? > Josh, I think you and I are too much like DBA's and not enough like java app developers. I always feel more comfortable putting logic into the database so that I can write multiple apps in multiple languages against it. > > And for an ISV, it's generally worth to go that route since > > many customers have strict policies regarding databases. > > Why is this, anyway? It puzzles me. Managers don't tend to have opinions > on programming languages, web servers, network devices, or power supplies. > They'll use whatever is recommended. But they have strong, immovable, and > often wholly uninformed opinions about databases. Why? > cause thats where the data is and the data is often the one thing that can't be replaced so they have to feel comfortable. you want to make sure your data is protected, and you don't want to swapping database every six months because doing that means take your data out of a nice container and hoping nothing gets munged in transit. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Josh, There's no disagreement. I'm definitely in type #1 and yes, in order to get a good abstraction, you do need to spend time and resources. A project directed to a single customer seldom (almost never) have that luxury. An ISV who wants to please as large customer base as possible, might. The latter is also true for people like myself who deal with tools and infrastructure aimed primarily towards ISV's and organisations that do their own development. Kind regards, Thomas Hallgren
Re: Design Strategy WAS: High-Profile Advocacy Opportunity:VbulletinForum
От
Christopher Browne
Дата:
Martha Stewart called it a Good Thing when josh@agliodbs.com (Josh Berkus) wrote: > "Database independence" is a strategy which is suitable for a > limited class of web applications, and not much else. Well, SAP has pretty consistently tried to apply this strategy to their R/3 application, which is _not_ a "limited class of web applications." Now, the way they have implemented "database independence" does mean that they have had to implement all data validation in their applications, with the result that it takes years for the code to mature, so it is certainly not immune to criticism ;-). But it's getting used by "Fortune 500" types, so it sure seems to be a counterexample to your claim... -- let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;; http://cbbrowne.com/info/lsf.html Rules of the Evil Overlord #210. "All guest-quarters will be bugged and monitored so that I can keep track of what the visitors I have for some reason allowed to roam about my fortress are actually plotting." <http://www.eviloverlord.com/>
Christopher, > Well, SAP has pretty consistently tried to apply this strategy to > their R/3 application, which is _not_ a "limited class of web > applications." Aha. The key word there is "suitable". I've supported some SAP-type applications, and they are messes of spaghetti code that underperform their hardware by a factor of 10. As well as having chronic data integrity problems that pretty much require an on-staff application expert just to keep the thing running. So I find SAP an excellent example of how *not* to write an application, unless your goal is to maximize your support revenue. -- -Josh Berkus Aglio Database Solutions San Francisco
Re: Design Strategy WAS: High-Profile Advocacy Opportunity:VbulletinForum
От
Christopher Browne
Дата:
Oops! josh@agliodbs.com (Josh Berkus) was seen spray-painting on a wall: > Christopher, >> Well, SAP has pretty consistently tried to apply this strategy to >> their R/3 application, which is _not_ a "limited class of web >> applications." > > Aha. The key word there is "suitable". I've supported some > SAP-type applications, and they are messes of spaghetti code that > underperform their hardware by a factor of 10. As well as having > chronic data integrity problems that pretty much require an on-staff > application expert just to keep the thing running. There's a few of us around that have been "certified," once upon a time, and I doubt many would argue that R/3 is based on an ideal design. But the fact is that F500 companies decided to go with it. By the way, the way that they avoid "chronic data integrity problems" is by actively deprecating direct access to the data. Automated data input is handled via something akin to programming CGI; the CGI-like scheme runs data through the same screen-oriented logic used to validate data entered interactively. Efficiency is pretty horrible, but there are some elegant aspects to it; if automatically-entered data is _nearly_ right, the borken transactions can be kept on record, and a user can walk them through online, perhaps to fix some minor problems. There are things they do that _are_ worth emulating... > So I find SAP an excellent example of how *not* to write an > application, unless your goal is to maximize your support revenue. It is essentially what you get if you write a way-sophisticated ERP system with lowest-common-denominator functionality that amounts to about what MySQL offers (modulo "transaction" support). Yes, it requires a huge phalanx of developers and administrators. Yes, it's highly vulnerable to data corruption unless you apply pretty draconian system management policies. And remarkably enough, contrary to some vendors' marketing spiels, performance sucks remarkably badly. The point still remains that it _is_ there, quite dominant in the ERP market... -- select 'cbbrowne' || '@' || 'cbbrowne.com'; http://www3.sympatico.ca/cbbrowne/oses.html Rules of the Evil Overlord #222. "I reserve the right to execute any henchmen who appear to be a little too intelligent, powerful, or devious. However if I do so, I will not at some subsequent point shout "Why am I surrounded by these incompetent fools?!" <http://www.eviloverlord.com/>
Robert Treat wrote: >On Thu, 2004-06-24 at 16:12, Josh Berkus wrote: > > >>Thomas, >> >> >> >>>You are communicating just fine. But I think we come from very different >>>backgrounds. >>> >>> >>Nope, nope, I'm not. Sorry. I'm being completely obtuse. >> >>Ok, there's 2 knids of database independence: >> >>1) impelemtation of a full abstraction layer, including optimized query and >>function calls for each class and method in the application, and often >>switched query choices depending on the database system installed. >>Implementation of schemas for each of several database systems to work with >>this. >> >>2) "Dumbing down" your SQL calls to the point where they will run on almost >>anything. >> >>It's (2) I was saying was "only useful for a limited range of web >>applications" and (1) that "required a large budget". >> >> >> > >Somewhere between 1 and 2 you have people designing schemas tailored to >boosting performance on one database vs. another. For example, using >count(*) table in postgresql apps that would be unneeded in my$ql, or >using crazy table layouts to make up for a lack of views or partial >indexes. Or maybe they add in extra application code to make up for a >lack of triggers... > > > Actually, under the assumption that you require updateable views, triggers, and functions to be supported by any database you want to support (it doesn;t limit the playing field too much to require these basic ideas), then you can abstract things simply by agreeing on an interface and then providing the abstraction *in the database.* Then only syntactic quirks need to be abstracted (PostgreSQL returns column names in lower case, Firebird returns them in upper case, that sort of thing). Again, you agree on a common interface, and if something doesn't conform to the standard, you make it conform by abstracting the difference. And the performance tweaks are hidden inside the box. This means bending the app a little, and bending the database a bit more so that they meet roughly at the point of greatest benefit. Best Wishes, Chris Travers
Chris, > The point still remains that it _is_ there, quite dominant in the ERP > market... Sure. And the "Model T" was the dominant automobile on the market for a decade, despite being low-accelleration, high-maintenence, not weatherproof, and only available in black. But you don't see many around now. -- Josh Berkus Aglio Database Solutions San Francisco
><grin> I just did a 45 minute presentation on this last night. > >"database independance" is very appropriate for some projects. Web bulletin >boards are usually among them. > >If, however, your application depends on high performance, strong security, or >rigorous and complex data integrity rules, the use of a client-side database >abstraction layer is, at best, impractical. > >There is no one design strategy which is better than all others regardless of >the application. Except maybe documenting your code. ;-) > > > There is a middle road. This limits the usefullness of databases like MySQL, but would allow interoperability between, say Firebird and PostgreSQL. This means abstracting the basic syntax differences betweent the two and then building robust and compatible backends on each. Yeah, it is more of a QA problem than anything else, but it is quite doable. Best Wishes, Chris Travers