ZODB (and OODB) question

[Bill Tate]

nadsinoz at hotmail.com (chris) wrote in message news:<b8996f29.0201071319.31cd67b9 at posting.google.com>...
> I am writing an application using zope and I am saving my objects
> (customers, transactions, etc.) in the ZODB rather than using a RDBMS
> such as MySQL.
> 
> In a RDBMS I can easily run batch update jobs, for example, running
> nightly jobs to  update customer's status depending on whether or not
> the customer pays an invoice on-time.
> 
> In the RDBMS, I would perform something like the following to do this:
> 
> UPDATE customer SET status = 'stopped' WHERE ...
> 
> How can I perform the same update using an OODB?  The only way I can
> think of doing this at the moment is to traverse through each and
> every customer object and set their status individually depending on
> my criteria - not very efficient!
> 
> I would appreciate any help on this, even if I just get pointed to
> relevant documentation that would help me.
> 
> Thanks in advance,
> Chris Snow

AFAIK, ZODB doesn't come with a querying facility (I've heard it
talked about but I'm not aware of anything as yet).  However, I would
strongly caution against using an OODBMS to store the kind of data you
are mentioning.  Most OODBMS are very well suited when you want
everything under a particular database root or you employing a query
that is based on criteria associated with the state of an object at
the top level of an object graph.  Performance deteriorates rapidly if
you have to employ queries that use criteria based on the state of
deeply nested object instances.  Add complex "and" "or" to query
clauses makes matters worse - :>(

ZODB was also optimized for frequent reads and wasn't intended to be a
transaction intensive database.  I think you'll find that using an
RDBMS will give you the speed and flexibility you need.  You might
want to check out Postgre SQL - it has some very nice features that
can make your life a lot easier.  Since PG has essentially an
equivalent to Oracle's PL/SQL, I think you'll find that using pgPL/SQL
to be particularly useful way of working with SQL.

Most RDBMS' make it pretty easy to evolve table schema these days
whereas before you used to have to go through a lot of hoops just to
drop a column from a DB table. That can be a definite plus over time. 
Not to mention that you normally don't know all the questions you want
to ask ahead of time.

ZODB is a superior solution to storing web content or "document data"
than using an RDBMS but, unfortunately, the same cannot be said of
working with customer and transaction data. The queries you'll most
likely need over time would probably cause any ODBMS to eventually
choke - there are exceptions, but you message suggests that this isn't
one of them.