[ZODB-Dev] AdaptableStorage and OR mapping strategy (was: AdaptableStorage and ZClass instances)

[Roché Compaan]

> > But then one still has to write SQL for each class that you want to
> > persist. My idea was to have a default properties gateway that generates
> > SQL at runtime for all classes that doesn't have a specific gateway.
> > This seems problematic if one doesn't have access to the object and
> > since this is a default gateway one wouldn't now anything about the
> > class either. All one really needs is the classname and the property
> > types and values on the object.  Is this at all possible?  The classname
> > can then be used in the load and store methods of the gateway to query
> > the right table and property types will be looked up in a typemap for 
> > the RDBMS.
> 
> This will work if the serializer does all the property introspection 
> ahead of time.  Ideally the class name should be managed exclusively by 
> the classification mechanism, but for simplicity I would suggest 
> ignoring that requirement initially and forging ahead. :-)

I had some time this weekend and decided to prototype a SQL properties
mapper that does not map all instance attributes to a single SQL table
but maps properties of a specific metatype to its own table. Instead of
each property taking up a record of its own I mapped properties to
columns of the equivalent type in the RDMBS. So if I have a class Person
with properties Name and Surname, I end up with a table with the same
name in my RDMBS and the columns Name and Surname of the type "varying
character (255)" (when using Postgres). The benefit is that it makes the
data much more queryable, joins simpler and provides a better fit for
the query power that the RDBMS offers.

I came to the conclusion that it doesn't make much sence to have such a
mapper as the default mapper for properties. If one feels that a table
per metatype is a better mapping strategy for your application then one
needs to approach this differently.

My main reason for coming to this conclusion is that you do not
necessarily have a fixed schema for a specific metatype. Take a Folder
for example. It only has the properties 'id' and 'title' at
instantiation but can potentially have any other properties added to it.
This will lead to table alterations each time somebody adds a property
with a different name to a folder which I find quite messy.

So one still needs to create a gateway per domain class manually (which
I don't mind as long as it requires less work than it currently does). I
think a very simple solution would be to have a SQLGatewayBase that have
methods that generate SQL based on a very simple schema definition in
subclasses. To take the current SQLProperties class as an example, it
should only need to have to look like this:

    class SQLProperties:

        __implements__ = IGateway

        schema = RowSequenceSchema()
        schema.addField('id', 'string', 1)
        schema.addField('type', 'string')
        schema.addField('data', 'string')

        table_base_name = 'properties'

This will give SQLGatewayBase more than enough to generate all the
necessary SQL and handle load and store automatically for simple
mappings. I'll give this a bash with my next try. 

-- 
Roché Compaan
Upfront Systems                 http://www.upfrontsystems.co.za