[Zope] Zope's database capability

[Jim Penny]

On Thu, Jan 24, 2002 at 06:25:10PM -0000, Tom Nixon wrote:
> I am in the process of evaluating Zope and so far it looks great. 
> 
> I have one question about Zope's database: How does it compare in
> performance to a relational database server? I am think about a scenario
> such as a recruitment site with a large database of jobs and candidates.
> Would the Zope database manage it OK or is it normal to run a database
> server such as mySQL alongside it for the heavy-duty stuff? Is it
> possible to build complex queries on the Zope database? Has anyone had
> any experience of building a big database-driven site in Zope, and if so
> did you build it all using the Zope database?
> 

As the previous reply noted -- it is both possible and normal to run a
SQL database along with ZODB.  This is done by using a "Database
Adaptor".  There are tons of them.  These include adaptors to postgres
(psycopg and PoPy), Oracle, ODBC (windows only), ODBC and others
(SQL-Relay), SAP, Interbase, MySQL, Sybase, Jet, Solid, and probably
several I have missed.

Choosing a DB is a somewhat religious issue.  I belong to the PostGres
religion.  (and PoPy)

I have tables with about 50,000 rows.  I do transitive closure queries
on some of them.  You do want to concentrate on speed of query.  You want
to have enough RAM.

But development is not hard at all.  I find working with ZSQL methods
incredibly easy, especially with DTML.

Some hints:

Do not be afraid of defining many special purpose queries.  They are
easier to test and guarantee correctness than more complicated queries.
Make your queries obviously correct!

Using DTML is easier for ZSQL tables, especially in the beginning, than 
using python methods; mostly because it does a lot of magic marshalling 
and unmarshalling of arguments for you.

I even use SQL queries to create pull-downs and such.  By using an
external database and maintenance forms; I can step back from much
customization.  That is, many of my forms customize themselves according
to the contents of a table.  (This could be done with ZODB, as well)

ZODB works by adding changed/new items to the end of the database and
marking invalid/superceded the old item.  This means that ZODB can
require regular packing, or that it can grow very large very quickly.
Never store things in ZODB that change often (things like page counters
are prime examples.)

Jim Penny