[Zope-DB] SQL optimisation: follow up

[Charlie Clark]

On 2002-11-08 at 09:32:46 [+0100], M.-A. Lemburg wrote:
> 
> Funny that nobody ever considers using the open-source SAP DB. That's a 
> full-blown RDBMS with a long history of being reliable and fast.

I know you (Marc-Andr=E9) recommend the SAP DB as I noted in a previous 
post. I can't use it on my current project as it's not supported by my ISP.=
 
Whether I could get it to work on my notebook is a different matter as is 
whether I should be doing this myself at all... There is a Zope DA for it
http://www.zope.org/Members/jack-e/ZsapdbDA
which is in development stage for those interested.

My post was not really supposed to be pro PostgreSQL. It is actually pro 
ACID databases and anti-quick fixes and default installs. Referential 
integrity is really important even though I was close to removing it in my 
SQL as I got one error after another. It was good to perservere and I've 
now got some examples to help explain things with examples to other people.

The internet "revolution" is full of people like me coming to IT with some 
useful skills but insufficient knowledge of software issues. A lot of 
things are done because they are "easy" and the market has oriented itself 
to address this group. When I started to work with databases this year I 
knew I would need help and good literature to do it. I went into the 
biggest bookshop in D=FCsseldorf to look for stuff on SQL databases: there 
are loads on individual databases mainly MySQL, Oracle, MS SQL Server and 
DB/2 but virtually nothing on database design which is probably the biggest=
 
problem. Most books are just full of bits of SQL or stored procedures but 
little addressing the questions before implementation. I picked up an SQL 
in a Nutshell (which is really weird to read in German) and PostgreSQL: 
professionell und praxisnah. There wasn't anything on the SAP DB although a=
 
book is due for publication by the end of the year. A how-to on Zope.org is=
 
pretty much a must as well as are compiled versions for Windows for those 
who don't have a compiler.

Using a non-ACID database fits in with the LAMP approach which I don't 
agree with: quick fixes which are difficult to adapt to changing 
requirements. That's why I'm using PostgreSQL, Python and Zope.

I'm using Zope because I use Python because I use the BeOS because I liked 
the idea of OO and clean APIs in OS/2s OpenDoc and NextSTEP (before I could=
 
write a line of code). I'm using PostgreSQL because I understood the need 
for referential integrity and it was possible for this project and the 
support in the community seems to be good: I need that support!

I guess one of things I haven't taken into consideration is the way the 
RDBMS, Python and Zope work together. It seems there are considerable 
differences between the way the Python driver / Zope DA combination work 
which might be important when choosing a DB particularly how connections 
are pooled. The standard Zope connection pooling is apparently somewhat 
flakey. psycopg talks about pooling a lot and I know that the upcoming 
mxODBC-DA has its own connection pooling and being able to use ODBC could 
make life a lot easier.

Any ideas which RDBMS people are using with Zope?
Judging by the list it seems Oracle is very common for commercial work and 
PostgreSQL seems to be very common as well.

So what combination are you using and why?

Charlie