[DB-SIG] How to *you* access your database objects?

Kevin Jacobs jacobs@penguin.theopalgroup.com
Wed, 16 Oct 2002 08:19:46 -0400 (EDT)


On Wed, 16 Oct 2002, Eivind Tagseth wrote:
> But what do you actually do with the data you retrieve.  And how do you
> retrieve it?

A simple question... a rather long and complex answer.

We use the following components:

  1) A database row object that supports tuple, dictionary and object-like
     access.  It is implemented without allocating a dictionary per-object
     using some of the new Python 2.2 (and 2.3) features.  See 'dbrow' on

        http://opensource.theopalgroup.com/

     for details on a very early version of this concept.  Our current
     version has evolved far beyond that, and is implemented in both Python
     and as a C extension module, so there is effectively no performance
     hit compared to returning rows as tuples.

  2) A connection configuration and abstraction layer, that allows the
     creation of database connections to various backend drivers with common
     connection arguments (since DB-API does not fully specify this).

  3) A low-level backend abstraction layer that implements per-connection
     primitives for SQL literal and identifier escaping, date formatting and
     conversion, and type mapping.

  4) A high-level SQL dialect re-writer that can translate one SQL dialect
     to another via a source-to-source transformation.  e.g., MS SQL <->
     Oracle <-> Sybase <-> PostgreSQL.

  5) Intelligent caching objects that can handle simple queries directly
     from cache, if present, and forward them to the database backend if
     not.

...plus a lot more...

If you haven't guessed, our systems have to be very sociable with dozens of
diverse database backends, and switch very naturally between them.

> Personally, I think it makes sense to create a python class for each
> database table I have, and make the entity attributes available as a
> dictionary.  I think this is cleaner than directly accessing attributes
> and more convenient than adding getters and setters as one would do in
> e.g. Java.

We use meta-class constructors to build these 'per-table' (or result schema)
object types.  They use our database introspection layer to dynamically
build the field names and types.

Just food for thought...  we're still working on making as much of this
framework available as open source, though it has been slow going due to the
massive volume of paying work I have to do.

Best regards,
-Kevin Jacobs

--
Kevin Jacobs
The OPAL Group - Enterprise Systems Architect
Voice: (216) 986-0710 x 19         E-mail: jacobs@theopalgroup.com
Fax:   (216) 986-0714              WWW:    http://www.theopalgroup.com