Thread: 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

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

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

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