[Grok-dev] Re: STORM howto

[Laurence Rowe]

Martijn Faassen wrote:
> Laurence Rowe wrote:
>> Martijn Faassen wrote:
> [snip]
>>> You do generate the schemas in memory and then write them out? What 
>>> prompted the decision to write them out?
>>
>> So I could see them and then more easily know how to subclass them - 
>> for instance I needed to change the types of some fields (e.g. choice 
>> instead of int, validators). Actually, recently I've moved away a bit 
>> from the whole SQL first approach, mainly because I've changed 
>> database twice in the past month.
> 
> Interesting. I've had some discussions with the Storm people, and 
> they're heavily in favor of the SQL first approach, as they feel no 
> schema generation could create schemas that are really right (let alone 
> accepted by DBAs if they're involved). My perspective is that there are 
> situations (with smaller applications, without DBAs involved, just 
> starting to develop) where it'd be nicer to have the schema be generated 
> from Python, so it's a use case I'd like to try supporting at least to 
> some level.
> 
> Seeing that you came from the SQL first approach and are now moving the 
> other way around, what is your reasoning surrounding this issue?

SQL first makes sense until you want to change database. I actually 
ended up using metadata.reflect(pg_engine); 
metadata.create_all(oracle_engine) - but with some fix ups in between 
surrounding PassiveDefaults.

The differences between the sql dialects becomes frustrating too.

PassiveDefault(sql.text('some_func()')) gives you the power to customize 
the DDL statement sufficiently.

> I think Grok should end up supporting both scenarios in some fashion, 
> though that's a bit too much wishy-washy to be really nice to explain.
> 
> Concerning generated schemas, I agree there are complexities involved 
> when generating them during run-time. We need to play around with this 
> some to see what's possible. I generally try to avoid code generation.
> 
>> Starting now I would probably hack around with the declaritive stuff 
>> to work out how to annotate the field info to the Column definitions 
>> and autogenerate if not supplied.
> 
> I'm not sure I follow you entirely, and I'm quite curious about this 
> approach. :) Annotate what field info how? Zope 3 schema field info or 
> something else?

Pure speculation, but the declarative example looks like:

class SomeClass(Base):
     __tablename__ = 'some_table'
     id = Column('id', Integer, primary_key=True)
     name =  Column('name', String(50))

Maybe it could be:

class SomeClass(Base):
     __tablename__ = 'some_table'
     id = Column('id', Integer, primary_key=True, field=schema.Int(...))
     name =  Column('name', String(50), field=schema.TextLine(...))

but generate a default field if none is supplied. No code generation 
would be required.

It might then auto generate a SomeClass.__schema__ to go with the 
__table__ and __mapper__ attributes.


> [snip]
>>> [collective.lead]
>>>> strictly zodb3 is not required, only transaction. The elro-tpc branch
>>>> will become 2.0 fairly soon. It pulls together some work done by
>>>> bitranch using scoped sessions and supporting the MyClass.query magic
>>>> and automatically associating new instances with sessions as well as
>>>> adding twophase and savepoint support. I just need to do a bit of
>>>> tidying up before I merge to trunk.
>>>
>>> I'm not sure what features you're referring to here; I'm not that 
>>> familiar with SQLAlchemy. Are scoped sessions and MyClass.query magic 
>>> both SQLAlchemy features? Would you recommend a new project starts 
>>> with this branch?
>>
>> ScopedSessions provide per thread sessions and are a good thing. 
>> Randomly they also seem to provide the option (using a special 
>> session.mapper) to annotate your original class with information about 
>> which metadata/session they belong too. Thinking again, I don't 
>> recommend you use them. Too much magic. I've moved it to an 
>> experimental module for now.
> 
> So ScopedSessions are good, but the special mapper story is too much 
> magic? Is that the MyClass.query magic you're talking about?

For the Plone story (and I assume the grok story too) it would limit 
classes to a single session, when you may want to create multiple sites, 
each with their own database, but sharing the same code. See 
http://www.sqlalchemy.org/docs/04/session.html#unitofwork_contextual_associating


>> The more I think of it, I would limit the IDatabase interface to 
>> session, engine and connection. It seems that the declarative approach 
>> is to keep metadata separate. This should allow for having multiple 
>> sites with their own databases using the same code for mappers.
> 
> Where would one keep metadata around in this case?

It goes with the model. All related model objects would inherit from the 
Base = declarative_base() class. The metadata object is then available 
as Base.metadata, and presumably on all of the subclasses too.


> [snip]


Laurence