[Zope-dev] Idea: Create SQL-Alchemy tables via interfaces

[Hermann Himmelbauer]

Am Mittwoch 03 September 2008 15:14:22 schrieb Martijn Faassen:
> Hey Hermann,
>
> Hermann Himmelbauer wrote:
> > In my current SQLAlchemy / Zope-based design, I need the following:
> >
> > - SQLAlchemy table definitions
> > - classes + mappers
> > - Zope interfaces
> >
> > The problem with this design is that much data has to be defined twice,
> > e.g. the datatype "varchar(50)" should be represented by an interface
> > with TextLine(max_length="50"). Moreover, any changes such as adding
> > columns etc. also have to be done in the interface and the table
> > definition.
> >
> > To overcome this, I just had the idea to use the interface/schema
> > definitions for the table definition itself. Probably I'm not the first
> > who had this idea, but I'm not aware of such an extension to interfaces.
> >
> > Any thoughts on this?
>
> I'm quite interested in reducing duplication myself.
>
> I believe z3c.dobbin is an approach in this direction, though it may be
> more ambitious than you need; I believe it tries to make RDB-backed
> objects feel like the ZODB.

I see - I did not look at z3c.dobbin at this point, however, as you already 
guessed, my ambition is not to make objects feel like the ZODB.

> In megrok.rdb we've sketched out the reverse of your approach: derive
> the Zope 3 schemas from the SQLAlchemy table definitions. This because
> it's more easy to derive a basic schema from a table definition without
> supplementary information than the other way.

Yes, I also thought about this, but I'm not quite sure about this approach for 
the following reasons:

- More schema types than SQL data types, for instance Text, TextLine, Email, 
etc. would all match a varchar.
- Constraints like min_length and values (in Choice) are not covered in the 
database
- In my mappers, I often have custom properties (e.g. for converting database 
values), which probably can not automatically be included in the schema. 
- In the design process, I think the first step is to define the interface. 
And the next step is the mapping of the interface (if it's a content object) 
to the underlying storage. For that reason, the interface->SA-Table approach 
seems more appealing to me.

I know, the interface->dbtable way does also have it's shortcomings and other 
things will not be covered by them (e.g. BLOB and varchar can both be 
represented by a Text etc.), but I assume that it would be easier to overcome 
them.

> (I fear though that as soon as a form needs to be made that *really*
> works properly, supplementation of the conversion process with more
> detailed schema information is still necessary. We have been thinking
> about good ways to express this)

Yes, for the SA-Tables -> schema approach, certainly.

> I believe that ore.alchemist or somesuch also implements this approach.
> This has been factored out by Laurence Rowe in something called
> collective.mercury. This code is more mature than megrok.rdb's
> conversion code, though in my opinion also a bit more complicated than
> it might be.

That's interesting and I'll have a look at it, although I'm not really 
convinced about the SA-Tables -> schema approach.

Thanks for your comments!

Best Regards,
Hermann

-- 
hermann at qwer.tk
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9  4902 64B4 D16B 2998 93C7