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

[Hermann Himmelbauer]

Am Mittwoch 03 September 2008 17:30:23 schrieb 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?
 .
Hmmm, I'm thinking about something like List(Objects) here. (Don't know the 
exact schema syntax). Thing is, that most often, if I have a relation, I also 
have a mapper that represents this relation. And that mapper will be either 
an object (1:1, 1:n), or a list (n:1, n:n).

And yes, I'd probably define extra schema attributes, perhaps by creating a 
mixin for schema fields (class SATextLine(TextLine, SASchema)) that extends 
the schema syntax.

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

For instance, in one of my databases emails are splitted and the 
username/hostname part is stored seperately for some reason. However, in my 
form I'd rather want one field, therefore I have some property that 
splits/combines this value and stores/reads it on/from the username/hostname 
part.

> > - 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 do use and like ZODB, but it does not scale well for write operations, 
indexing is complicated, relations can not be easily implemented, all in all 
simply inappropriate for many applications.

Although ZODB may not be useable for a specific case, why not use a 
Zope3-style design process? So I'd start out with interfaces that describe my 
content objects, then I'd model relations, with interfaces too. And when I'm 
done, I'd derive the SQLAlchemy tables from that.

But, you know, that's theory, you may be right that this approach is not 
possible.

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

Yes, true. Interfaces would have to be extended to cover that. But that should 
be possible, I assume.

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

Yes, that's certainly a challenge. Nevertheless this would give interfaces the 
value they preserve, because at this state, I think, interfaces for SA-Tables 
are to some degree obsolete (see my other mail).

Best Regards,
Hermann

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