[Zope-dev] Zope and Storm (SQL)

Sat May 24 04:47:54 EDT 2008

There seems to be some interest on the use of SQL databases with Zope.

Lovelysystems is now using SQL databases as the primary storage for their
applications. We use Zope and Postgres with Storm as ORM.
The main reason for switching to SQL database were speed issues with
queries.

Here is a short summary of my thougt's and experiences while using Storm and
Zope for about 3 Month now.

RelStorage:
Relstorage doesn't solve the speed problems. Doing queries with SQL is much
faster than doing it with ZODB. If you work with a lot and with large BTrees
you need to load them all into the memory of each Zope client. This has to
be done with Relstorage too.

Indexes:
You don't need to implement catalog indexes, this is all done on the
database side. When implementing and using your content types, at first you
don't need to think about indexes, later you optimize the database without
touching your python code.

A speed example :
We had to find similar users based on items a user has collected. Doing this
with ZODB took minutes to calculate for users with a lot of items. We had to
implement a lot of code to do the calculation asynchronously to not block
the users request.
Doing the same with SQL was possible with a single (of course complex) query
within 300ms, no async things needed, just implement the query and optimize
the indexes on the server, finished ! Relstorage will not help you here.

Content implementation:
While we are porting our existing ZODB based packages to SQL, we found that
implementing them with Storm is as easy as using ZODB. We still can use the
full power of Zope's component architecture. This is because Storm objects
are extremely easy to implement. You can implement a storm object like a
Persistent object, just derive from Storm instead of Persistent, add
__storm_table__ and define the properties as Storm properties.

For me a big mistake when switching from ZODB to SQL is trying to use the
container pattern at any cost.
A container is nothing but a  1:N relation and this is exactly what an SQL
database provides : Relations

class Content(Storm):
    id = Int(primary=True)
    content = ReferenceSet(id, 'Contained.somethingId')
c = Content()

Now you can
 - add data : c.content.add(content)
 - iterate : for a in c.content:
 - search : c.content.find(...)
 - sort : c.content.find().sort_by(...)
 - do anything a Storm ResultSet is providing

But of course it is possible to put an adapter around the Content class
which will provide IContainer.

Annotation:
Annotations are 1:1 relations, so it's as easy as the above.
We use annotations like simple adapters to other tables.

class ToBeAnnotated(Storm):
    interface.implements(ICanHaveData)
    id = Int(primary=True)

Note that the "annotated" storm table is implemented as an adapter :

class Data(Storm):
    interface.implements(IData)
    interface.adapts(ICanHaveData)
    id = Int(primary=True)
    __parent__ = Reference(id, ToBeAnnotated.id)
    def _init__(self, context):
        # a dummy to make the adapter happy
        pass

We can now register "Data" as an adapter.
We use a special adapter factory like zope.annotation.factory to autocreate
adapted content.

def contentAdapter(table, autocreate=True):
    # an adapter on content for content contained in other tables. Just like
    # the annotation adapter, an instance is created if autocreate is True.
    adapts = component.adaptedBy(table)
    if adapts is None:
        raise TypeError("Missing 'zope.component.adapts' on table")
    @component.adapter(list(adapts)[0])
    @interface.implementer(list(component.implementedBy(table))[0])
    def getAdapter(context):
        unsafeContext = removeSecurityProxy(context)
        obj = getStore().find(table, table.__parent__ ==
unsafeContext).one()
        if obj is None and autocreate:
            obj = table(context)
            obj.__parent__ = context
        return obj
    return getAdapter

Now you can define a factory for the adapter:

dataFactory = contentAdapter(Data)

And register "dataFactory" as an adapter.

DublinCore:
If you want to use the full DublinCore implementation from Zope you need to
do a generic implementation.
Usually only parts of the DublinCore interface is used.
We usually implement IDCTimes and IDCDescriptiveProperties. All you need to
do for this is :

class DCStormContent(Storm):
    interface.implements(IDCTimes, IDCDescriptiveProperties)
    created = DateTime()
    modified = DateTime()
    title = Unicode()
    description = Unicode()

That's it!
You can now use IDCTimes and IDCDescriptiveProperties for your formlib
form_fields.

There are two way's to update "modified" :
 - write an event handler for ObjectModifiedEvent
 - do it on the database side with triggers
I prefer using the event handler because the database trigger is doing the
update only when writing to the database which can be to late.

Schema's and Storm objects:
We don't use schema's to create our Storm objects or the database table from
it. Right now for us it is not worth the time to implement such a feature.

Traversing and URL's:
Usually our customers whant to have special URL's for their pages. In any
way (ZODB or SQL) we need to implement special traverser's to provide the
URL's. Usually we use z3c.traverser to do this.
Because of the special URL's we also need to implement absolute URL
adapters.

Transaction handling:
Storm already has a DataManager for zope's transaction package.
All you need to do is to register a utility for each of the database you
want to use.

ZMI:
Hmm, don't work out of the box. If really needed we build traversers for the
ZMI.

Data transparency:
At any time you can use any database administration tool you like to
directly view and/or manipulate you data in the database.

Jürgen
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.zope.org/pipermail/zope-dev/attachments/20080524/3ab8d3a7/attachment-0001.html