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

[Martijn Faassen]

Hi there,

On Wed, Sep 3, 2008 at 4:34 PM, Hermann Himmelbauer <dusty at qwer.tk> wrote:
[snip]
>> 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

Yes, true. But table descriptions need foreign key information, and
possibly you need table creation arguments. How would you deal with
relations? I guess those aren't part of the table description, but
would you want to express them in your schema?
.
> - In my mappers, I often have custom properties (e.g. for converting database
> values), which probably can not automatically be included in the schema.

I don't think I understand this bit. Could you elaborate?

> - 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'd argue that if you are going to store your data in a relational
database, the first step may very well be the design of the actual
database, not the interface. If you really first want to think about
python object interfaces only, why not simply use the ZODB then?

> 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.

My impression was the other way around: I find more fundamental
concepts missing in schemas that are needed for relational database
description than the other way around. Foreign keys, some uniqueness
constraints and indexes are

>
>> (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.

Well, for schema -> SA-Tables you'd need to supplement the schema with
relational database information, somehow find ways to express various
relational database concepts in Zope 3 schema.. invent something
SQLAlchemy already can do..

Regards,

Martijn