Hi there, In some earlier discussions a number of approaches to integrate SQLAlchemy into Zope were discussed. Following up on that, I've tried a particular approach that tries to use ScopedSessions with a custom scope that isn't just per thread, but also per site (application). The benefit of this approach is that it should allow the following to just work: from z3c.sa_integration import Session ... def somewhere_in_a_view(self): session = Session() return session.query(Test).all() where 'Session' is a custom SA scoped session. I've checked in my (documented) experiment in here: svn://svn.zope.org/repos/main/Sandbox/faassen/rdbintegration/trunk The interesting bits are here: http://svn.zope.org/Sandbox/faassen/rdbintegration/trunk/src/rdbintegration/... See the documentation in that file for more information. The one bit I'm not happy about yet is the way engines need to be global utilities now. I'd like it to be possible for users to edit the database connection parameters through the web. I have a sketchy idea about how to solve this by registering engines as non-persistent local utilities, but it's still unclear how they'd get created at the right time. It might also be possible to simply implement the engine method on IDatabase to create the engine when called the first time, and then cache it in a volatile property. I think this is something Laurence Rowe has been talking about. I'm not sure whether it's a good idea to rely on the ZODB cache, but it might be possible to simply cache the engines in a global registry... Comments? Suggestions? Regards, Martijn
Martijn Faassen wrote:
Hi there,
In some earlier discussions a number of approaches to integrate SQLAlchemy into Zope were discussed. Following up on that, I've tried a particular approach that tries to use ScopedSessions with a custom scope that isn't just per thread, but also per site (application). The benefit of this approach is that it should allow the following to just work:
from z3c.sa_integration import Session
... def somewhere_in_a_view(self): session = Session() return session.query(Test).all()
where 'Session' is a custom SA scoped session.
I've checked in my (documented) experiment in here:
svn://svn.zope.org/repos/main/Sandbox/faassen/rdbintegration/trunk
The interesting bits are here:
http://svn.zope.org/Sandbox/faassen/rdbintegration/trunk/src/rdbintegration/...
See the documentation in that file for more information.
The one bit I'm not happy about yet is the way engines need to be global utilities now. I'd like it to be possible for users to edit the database connection parameters through the web. I have a sketchy idea about how to solve this by registering engines as non-persistent local utilities, but it's still unclear how they'd get created at the right time. It might also be possible to simply implement the engine method on IDatabase to create the engine when called the first time, and then cache it in a volatile property. I think this is something Laurence Rowe has been talking about. I'm not sure whether it's a good idea to rely on the ZODB cache, but it might be possible to simply cache the engines in a global registry...
Comments? Suggestions?
zope.sqlalchemy recycles sessions, so I don't believe it's necessary to keep a registry of engines, or even to cache them on a volatile property. They are only required during session creation, which happens only once per thread per configuration. The session itself always retains a reference to the engine of course. The downside of doing session.close() rather than session.remove() at the end of a transaction is that it becomes difficult to change configurations, as the configuration() method is not called again. I guess the way to work around this is to build a revision into the scopeing function, perhaps using the _p_mtime of the configuration object. (it would be nice to use _p_oid in the scope, but that is not set until the transaction that creates the configuration is committed). There would be straggling sessions and open db connections left in the scoped session with this approach, but I don't really see a way to avoid that without lots of messy locking. Laurence
On Mon, Jun 16, 2008 at 08:40:24PM +0200, Martijn Faassen wrote:
Hi there,
In some earlier discussions a number of approaches to integrate SQLAlchemy into Zope were discussed. Following up on that, I've tried a particular approach that tries to use ScopedSessions with a custom scope that isn't just per thread, but also per site (application). The benefit of this approach is that it should allow the following to just work:
from z3c.sa_integration import Session
... def somewhere_in_a_view(self): session = Session() return session.query(Test).all()
For some reason this raises a warning bell in my head. I keep on thinking: this is zope, the session is a classic case for a utility, we should be getting it in views by an interface. That may be because I've already become too fixed in the way I think about things. However, Laurence's point about database re-connection is relevant here. If the Session were looked up by an interface, someone could implement seamless database re-connection (lots of complex code) without polluting zope.sqlalchemy.
where 'Session' is a custom SA scoped session.
I've checked in my (documented) experiment in here:
svn://svn.zope.org/repos/main/Sandbox/faassen/rdbintegration/trunk
The interesting bits are here:
http://svn.zope.org/Sandbox/faassen/rdbintegration/trunk/src/rdbintegration/...
This looks very nice. I'm not sure IDatabase.engine is necessary, it seems to be only used internally to Database. Also, any view code should really be getting the connection directly from the session.
See the documentation in that file for more information.
The one bit I'm not happy about yet is the way engines need to be global utilities now. I'd like it to be possible for users to edit the database connection parameters through the web. I have a sketchy idea about how to solve this by registering engines as non-persistent local utilities, but it's still unclear how they'd get created at the right time. It might also be possible to simply implement the engine method on IDatabase to create the engine when called the first time, and then cache it in a volatile property. I think this is something Laurence Rowe has been talking about. I'm not sure whether it's a good idea to rely on the ZODB cache, but it might be possible to simply cache the engines in a global registry...
Comments? Suggestions?
As Laurence has suggested, the session persists, so IDatabase.configuration will only be called once per thread. Not sure if creating one new engine per thread is bad.
Regards,
Martijn
_______________________________________________ Zope-Dev maillist - Zope-Dev@zope.org http://mail.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope )
-- Brian Sutherland
Brian Sutherland wrote:
On Mon, Jun 16, 2008 at 08:40:24PM +0200, Martijn Faassen wrote:
Hi there,
In some earlier discussions a number of approaches to integrate SQLAlchemy into Zope were discussed. Following up on that, I've tried a particular approach that tries to use ScopedSessions with a custom scope that isn't just per thread, but also per site (application). The benefit of this approach is that it should allow the following to just work:
from z3c.sa_integration import Session
... def somewhere_in_a_view(self): session = Session() return session.query(Test).all()
For some reason this raises a warning bell in my head. I keep on thinking: this is zope, the session is a classic case for a utility, we should be getting it in views by an interface.
FWIW, I had the same though. I think there's a trade-off here: we can use patterns that SQLAlchemy and Pylons and others use directly (use a "global" that isn't actually global) or we can use patterns that are ubiquitous in Zope (look up utilities by interface). To my mind, the latter is better because it makes us internally consistent, and because it promotes one of the formalisms that IMHO makes Zope 3 easier to work with. Martin -- Author of `Professional Plone Development`, a book for developers who want to work with Plone. See http://martinaspeli.net/plone-book
Martin Aspeli wrote:
Brian Sutherland wrote: [snip]
For some reason this raises a warning bell in my head. I keep on thinking: this is zope, the session is a classic case for a utility, we should be getting it in views by an interface.
FWIW, I had the same though.
I think there's a trade-off here: we can use patterns that SQLAlchemy and Pylons and others use directly (use a "global" that isn't actually global) or we can use patterns that are ubiquitous in Zope (look up utilities by interface).
To my mind, the latter is better because it makes us internally consistent, and because it promotes one of the formalisms that IMHO makes Zope 3 easier to work with.
The former integrates smoothly with SQLAlchemy. It also is closer to the SQLAlchemy documentation. It's also quite likely that someone writing a larger application that does use the interface lookup pattern will get bored and write something like: def Session(): return component.getUtility(ISession) The Zope component architecture is not about seeing explicit calls into it everywhere. That's not the point of it. The point of it is about making applications more flexible by allowing people to plug in components. My approach allows you to do that. Anyway, the balance can come out somewhere else. People are free to write their own integration approaches, it's just that mine is actually about trying to make exactly this pattern work in the first place. Then when I succeed people want it changed. :) Anyway, no surprise: I knew that some want other patterns, and I'll be curious to see the other approaches as well. Regards, Martijn
On Tue, Jun 17, 2008 at 11:58:56AM +0200, Martijn Faassen wrote:
Anyway, the balance can come out somewhere else. People are free to write their own integration approaches, it's just that mine is actually about trying to make exactly this pattern work in the first place. Then when I succeed people want it changed. :) Anyway, no surprise: I knew that some want other patterns, and I'll be curious to see the other approaches as well.
Hmm, how about this approach. It builds on and is almost compatible with yours, but allows me a plug in point where I need it. My use case is the simplest in that I have no local utilities and no need of application scoped sessions (plain old thread scoping works just fine). But I would like to have the option of using code others write for zope/sqlalchemy and allow others to use our code. The total code I would write in zope.sqlalchemy would be this: class ISession(Interface): def __call__(): """Return a SQLAlchemy Session object""" def Session(): return getUtility(ISession)() I would use it in view code like: from zope.sqlalchemy import Session class View: def some_view_method(self): session = Session() session.do_stuff() I do believe that the application scoping approach you showed us could be implemented by registering your session as the ISession utility. It may be that zope.sqlalchemy provides different implementations of sessions, but I would prefer to see no ISession utility actually registered.
Regards,
Martijn
_______________________________________________ Zope-Dev maillist - Zope-Dev@zope.org http://mail.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope )
-- Brian Sutherland
Hi there, Just as some context: I'm not proposing to extend zope.sqlalchemy at all. I'm proposing to write an extension that has the configuration pattern I sketched out. I also don't intend to write the last SQLAlchemy integration layer; I wouldn't be so presumptious. :) That said, if we can come up with something we all feel comfortable with, hopefully the configuration package will be used by others as well. On Tue, Jun 17, 2008 at 3:36 PM, Brian Sutherland <brian@vanguardistas.net> wrote: [snip]
Hmm, how about this approach. It builds on and is almost compatible with yours, but allows me a plug in point where I need it. My use case is the simplest in that I have no local utilities and no need of application scoped sessions (plain old thread scoping works just fine).
But I would like to have the option of using code others write for zope/sqlalchemy and allow others to use our code.
The total code I would write in zope.sqlalchemy would be this:
class ISession(Interface):
def __call__(): """Return a SQLAlchemy Session object"""
def Session(): return getUtility(ISession)()
I would use it in view code like:
from zope.sqlalchemy import Session
class View:
def some_view_method(self): session = Session() session.do_stuff()
I do believe that the application scoping approach you showed us could be implemented by registering your session as the ISession utility.
I'm not sure it makes sense to add an extra utility lookup each time I get a session. Right now the scoped session just gets the thing if it's already cached, and there's already a local utility lookup to get the scope UID. What about this? class IDatabase(Interface): def scopefunc(): """The scopefunc""" def session_factory(): """The session factory""" def scopefunc(): util = component.getUtility(IDatabase) return util.scopefunc() This will allow you to implement a global IDatabase utility that's global and uses a thread-local scope only. Similarly, we can delegate session creation to the IDatabase utility completely: def session_factory(): util = component.getUtility(IDatabase) return util.session_factory() I think that's better than what I have now, as you can then completely control session creation through a utility. It doesn't add a new plugin point or utility lookup, but allows you to implement your use case, right? We'll just need to implement a number of utilities that fulfill the various use cases. Regards, Martijn
On Tue, Jun 17, 2008 at 03:52:51PM +0200, Martijn Faassen wrote:
Hi there,
Just as some context: I'm not proposing to extend zope.sqlalchemy at all.
I was;)
I'm proposing to write an extension that has the configuration pattern I sketched out. I also don't intend to write the last SQLAlchemy integration layer; I wouldn't be so presumptious. :) That said, if we can come up with something we all feel comfortable with, hopefully the configuration package will be used by others as well.
I see.
On Tue, Jun 17, 2008 at 3:36 PM, Brian Sutherland <brian@vanguardistas.net> wrote: [snip]
Hmm, how about this approach. It builds on and is almost compatible with yours, but allows me a plug in point where I need it. My use case is the simplest in that I have no local utilities and no need of application scoped sessions (plain old thread scoping works just fine).
But I would like to have the option of using code others write for zope/sqlalchemy and allow others to use our code.
The total code I would write in zope.sqlalchemy would be this:
class ISession(Interface):
def __call__(): """Return a SQLAlchemy Session object"""
def Session(): return getUtility(ISession)()
I would use it in view code like:
from zope.sqlalchemy import Session
class View:
def some_view_method(self): session = Session() session.do_stuff()
I do believe that the application scoping approach you showed us could be implemented by registering your session as the ISession utility.
I'm not sure it makes sense to add an extra utility lookup each time I get a session. Right now the scoped session just gets the thing if it's already cached, and there's already a local utility lookup to get the scope UID.
Yep.
What about this?
It's much better than what I suggested:) It also fills my usecase completely. I'm +lots on it.
class IDatabase(Interface): def scopefunc(): """The scopefunc"""
def session_factory(): """The session factory"""
def scopefunc(): util = component.getUtility(IDatabase) return util.scopefunc()
This will allow you to implement a global IDatabase utility that's global and uses a thread-local scope only. Similarly, we can delegate session creation to the IDatabase utility completely:
def session_factory(): util = component.getUtility(IDatabase) return util.session_factory()
I think you missed the final piece: Session = scoped_session(session_factory, scopefunc=scopefunc)
I think that's better than what I have now, as you can then completely control session creation through a utility.
Yep
It doesn't add a new plugin point or utility lookup, but allows you to implement your use case, right? We'll just need to implement a number of utilities that fulfill the various use cases.
Yes, it looks pretty trivial to plug in a normal sqlalchemy session.
Regards,
Martijn
-- Brian Sutherland
On Tue, Jun 17, 2008 at 05:25:56PM +0200, Brian Sutherland wrote:
class IDatabase(Interface): def scopefunc(): """The scopefunc"""
def session_factory(): """The session factory"""
def scopefunc(): util = component.getUtility(IDatabase) return util.scopefunc()
This will allow you to implement a global IDatabase utility that's global and uses a thread-local scope only. Similarly, we can delegate session creation to the IDatabase utility completely:
def session_factory(): util = component.getUtility(IDatabase) return util.session_factory()
I think you missed the final piece:
Session = scoped_session(session_factory, scopefunc=scopefunc)
I think that's better than what I have now, as you can then completely control session creation through a utility.
Yep
It doesn't add a new plugin point or utility lookup, but allows you to implement your use case, right? We'll just need to implement a number of utilities that fulfill the various use cases.
Yes, it looks pretty trivial to plug in a normal sqlalchemy session.
This would probably be close to what I would write for my usecase: class Database: implements(IDatabase) def __init__(self, *args, **kw): self._args = args self._kw = kw def scopefunc(self): return None # use default per-thread scoping def session_factory(self): return sessionmaker(*self._args, **self._kw) -- Brian Sutherland
Brian Sutherland wrote: [snip]
This would probably be close to what I would write for my usecase:
class Database:
implements(IDatabase)
def __init__(self, *args, **kw): self._args = args self._kw = kw
def scopefunc(self): return None # use default per-thread scoping
def session_factory(self): return sessionmaker(*self._args, **self._kw)
I don't think you can use sessionmaker, as that creates a class and you need to create an actual session in this place. You'd need create_session. You'd also need to implement your scopefunc, otherwise you get *no* scoping at all, not even per thread, so return thread.get_ident(). Like this: class Database: implements(IDatabase) def __init__(self, *args, **kw): self._args = args self._kw = kw def scopefunc(self): return thread.get_ident() def session_factory(self): return create_session(*self._args, **self._kw) we're ignoring the details of what creates the engine, but if you pass 'bind' along when you create Database that should take care of it. Regards, Martijn
On Tue, Jun 17, 2008 at 06:05:10PM +0200, Martijn Faassen wrote:
Brian Sutherland wrote: [snip]
This would probably be close to what I would write for my usecase:
class Database:
implements(IDatabase)
def __init__(self, *args, **kw): self._args = args self._kw = kw
def scopefunc(self): return None # use default per-thread scoping
def session_factory(self): return sessionmaker(*self._args, **self._kw)
I don't think you can use sessionmaker, as that creates a class and you need to create an actual session in this place. You'd need create_session. You'd also need to implement your scopefunc, otherwise you get *no* scoping at all, not even per thread, so return thread.get_ident().
Whoops! There goes my reputation, if I had one;)
Like this:
class Database: implements(IDatabase)
def __init__(self, *args, **kw): self._args = args self._kw = kw
def scopefunc(self): return thread.get_ident()
def session_factory(self): return create_session(*self._args, **self._kw)
we're ignoring the details of what creates the engine, but if you pass 'bind' along when you create Database that should take care of it.
Yep. As I don't try store anything in the ZODB, my case is much simpler. I can just instantiate the Database object as a module level global and register is via ZCML.
Regards,
Martijn
_______________________________________________ Zope-Dev maillist - Zope-Dev@zope.org http://mail.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope )
-- Brian Sutherland
Hey Brian, I've updated the code in svn to be in synch with our latest insights concerning what IDatabase should look like. Looks like the next area of interest for at least my use case will be a way to set up engines. I'm one of those people that would at least like to offer the option to configure the engine connection parameters through a web UI. Hermann Himmelbauer sent me some code that might be useful for that; I need to study it. For your use case we're pretty far along though. Thanks for the feedback! Regards, Martijn
Martijn Faassen wrote:
Martin Aspeli wrote:
Brian Sutherland wrote: [snip]
For some reason this raises a warning bell in my head. I keep on thinking: this is zope, the session is a classic case for a utility, we should be getting it in views by an interface. FWIW, I had the same though.
I think there's a trade-off here: we can use patterns that SQLAlchemy and Pylons and others use directly (use a "global" that isn't actually global) or we can use patterns that are ubiquitous in Zope (look up utilities by interface).
To my mind, the latter is better because it makes us internally consistent, and because it promotes one of the formalisms that IMHO makes Zope 3 easier to work with.
The former integrates smoothly with SQLAlchemy.
Sure, that's why I said "trade-off". :)
It also is closer to the SQLAlchemy documentation.
This is a good point. But is it "the same" or "almost the same"? If it's the latter, the explicitly different is better than faux identity.
It's also quite likely that someone writing a larger application that does use the interface lookup pattern will get bored and write something like:
def Session(): return component.getUtility(ISession)
Mmmm, I'm not so sure, but maybe.
The Zope component architecture is not about seeing explicit calls into it everywhere. That's not the point of it. The point of it is about making applications more flexible by allowing people to plug in components. My approach allows you to do that.
Sure, but I also think that the CA has given us a few very basic, very flexible idioms (adapters, utilities, subscribers) that permeate any and all zope 3 applications. Internal consistency is a very good thing in a framework. A not-quite-global Session variable is another pattern to achieve what we do elsewhere with global unnamed utilities when we use them as effective singletons. Having two patterns to do the same thing is not good. Put the question another way - a new user may ask, "why don't I have a global thing with a capital first letter like Session to look up other singletons?".
Anyway, the balance can come out somewhere else. People are free to write their own integration approaches, it's just that mine is actually about trying to make exactly this pattern work in the first place. Then when I succeed people want it changed. :) Anyway, no surprise: I knew that some want other patterns, and I'll be curious to see the other approaches as well.
I'm not saying it's wrong, and I do think "natural" SQLAlchemy is a strong selling point, since I'd wager there are more SQLAlchemy users than Zope 3 users out there. I just think we need to be careful about what patterns we promote and how it fits with the rest of our "story". Martin -- Author of `Professional Plone Development`, a book for developers who want to work with Plone. See http://martinaspeli.net/plone-book
Martin Aspeli wrote:
Martijn Faassen wrote:
Martin Aspeli wrote:
Brian Sutherland wrote: [snip]
For some reason this raises a warning bell in my head. I keep on thinking: this is zope, the session is a classic case for a utility, we should be getting it in views by an interface. FWIW, I had the same though.
I think there's a trade-off here: we can use patterns that SQLAlchemy and Pylons and others use directly (use a "global" that isn't actually global) or we can use patterns that are ubiquitous in Zope (look up utilities by interface).
To my mind, the latter is better because it makes us internally consistent, and because it promotes one of the formalisms that IMHO makes Zope 3 easier to work with.
The former integrates smoothly with SQLAlchemy.
Sure, that's why I said "trade-off". :)
It also is closer to the SQLAlchemy documentation.
This is a good point. But is it "the same" or "almost the same"? If it's the latter, the explicitly different is better than faux identity.
It's also quite likely that someone writing a larger application that does use the interface lookup pattern will get bored and write something like:
def Session(): return component.getUtility(ISession)
Mmmm, I'm not so sure, but maybe.
The Zope component architecture is not about seeing explicit calls into it everywhere. That's not the point of it. The point of it is about making applications more flexible by allowing people to plug in components. My approach allows you to do that.
Sure, but I also think that the CA has given us a few very basic, very flexible idioms (adapters, utilities, subscribers) that permeate any and all zope 3 applications. Internal consistency is a very good thing in a framework.
A not-quite-global Session variable is another pattern to achieve what we do elsewhere with global unnamed utilities when we use them as effective singletons. Having two patterns to do the same thing is not good.
Put the question another way - a new user may ask, "why don't I have a global thing with a capital first letter like Session to look up other singletons?".
Anyway, the balance can come out somewhere else. People are free to write their own integration approaches, it's just that mine is actually about trying to make exactly this pattern work in the first place. Then when I succeed people want it changed. :) Anyway, no surprise: I knew that some want other patterns, and I'll be curious to see the other approaches as well.
I'm not saying it's wrong, and I do think "natural" SQLAlchemy is a strong selling point, since I'd wager there are more SQLAlchemy users than Zope 3 users out there. I just think we need to be careful about what patterns we promote and how it fits with the rest of our "story".
I don't think scoped sessions really map well to a utility: session = getUtility(ISession)() is just too ugly. Better to stick with Session() or ISession(context) Laurence
Laurence Rowe wrote:
Martin Aspeli wrote:
Martijn Faassen wrote:
Martin Aspeli wrote:
Brian Sutherland wrote: [snip]
For some reason this raises a warning bell in my head. I keep on thinking: this is zope, the session is a classic case for a utility, we should be getting it in views by an interface. FWIW, I had the same though.
I think there's a trade-off here: we can use patterns that SQLAlchemy and Pylons and others use directly (use a "global" that isn't actually global) or we can use patterns that are ubiquitous in Zope (look up utilities by interface).
To my mind, the latter is better because it makes us internally consistent, and because it promotes one of the formalisms that IMHO makes Zope 3 easier to work with. The former integrates smoothly with SQLAlchemy. Sure, that's why I said "trade-off". :)
It also is closer to the SQLAlchemy documentation. This is a good point. But is it "the same" or "almost the same"? If it's the latter, the explicitly different is better than faux identity.
It's also quite likely that someone writing a larger application that does use the interface lookup pattern will get bored and write something like:
def Session(): return component.getUtility(ISession) Mmmm, I'm not so sure, but maybe.
The Zope component architecture is not about seeing explicit calls into it everywhere. That's not the point of it. The point of it is about making applications more flexible by allowing people to plug in components. My approach allows you to do that. Sure, but I also think that the CA has given us a few very basic, very flexible idioms (adapters, utilities, subscribers) that permeate any and all zope 3 applications. Internal consistency is a very good thing in a framework.
A not-quite-global Session variable is another pattern to achieve what we do elsewhere with global unnamed utilities when we use them as effective singletons. Having two patterns to do the same thing is not good.
Put the question another way - a new user may ask, "why don't I have a global thing with a capital first letter like Session to look up other singletons?".
Anyway, the balance can come out somewhere else. People are free to write their own integration approaches, it's just that mine is actually about trying to make exactly this pattern work in the first place. Then when I succeed people want it changed. :) Anyway, no surprise: I knew that some want other patterns, and I'll be curious to see the other approaches as well. I'm not saying it's wrong, and I do think "natural" SQLAlchemy is a strong selling point, since I'd wager there are more SQLAlchemy users than Zope 3 users out there. I just think we need to be careful about what patterns we promote and how it fits with the rest of our "story".
I don't think scoped sessions really map well to a utility:
session = getUtility(ISession)()
is just too ugly. Better to stick with Session() or ISession(context)
Agreed. Why can't it just be getUtility(ISession) ? Note that I'm only playing devil's advocate here; I don't feel very strongly about this. Cheers, Martin -- Author of `Professional Plone Development`, a book for developers who want to work with Plone. See http://martinaspeli.net/plone-book
Martin Aspeli wrote: [snip]
Why can't it just be
getUtility(ISession)
?
Because the internals won't let you. As I said in my reply, a Session is not quite like a local utility. Trying to pigeonhole it that way isn't easy. It'd need some clever component wizardry to make sessions be created on the fly, and cached per thread when you make this call. Laurence's call is easy enough to implement. You could do it by registering my Session under the ISession interface as a utility, in fact. I don't see the point, though (and it's ugly). Regards, Martijn
Martin Aspeli wrote:
Martijn Faassen wrote: [snip]
The Zope component architecture is not about seeing explicit calls into it everywhere. That's not the point of it. The point of it is about making applications more flexible by allowing people to plug in components. My approach allows you to do that.
Sure, but I also think that the CA has given us a few very basic, very flexible idioms (adapters, utilities, subscribers) that permeate any and all zope 3 applications. Internal consistency is a very good thing in a framework.
A not-quite-global Session variable is another pattern to achieve what we do elsewhere with global unnamed utilities when we use them as effective singletons. Having two patterns to do the same thing is not good.
It's not quite the same. While a utility can be scoped per application, the Session class also takes care of scoping per thread. I'm not sure whether utilities can be convinced to do so (sites are local per thread, but utilities?). SA's ScopedSession also takes care of creating and caching individual sessions for each thread (and application), something utilities don't really do either. So you'd still have to use ScopedSession and register that as a local utility somehow, or do some componentry I can't wrap my head around yet. I have a strong suspicion it'll become a lot more complicated than my approach (and not more flexible). I'm trying to go with the flow for *both* frameworks. The code is pretty simple right now, and this is a good sign.
Put the question another way - a new user may ask, "why don't I have a global thing with a capital first letter like Session to look up other singletons?".
A new user won't ask such a thing, as it looks like he's importing and instantiating a class. Classes are singletons, and they're imported. I've actually wished for a long time that utility lookups could be made to look more like imports. :) It's a question a very experienced user might ask.
Anyway, the balance can come out somewhere else. People are free to write their own integration approaches, it's just that mine is actually about trying to make exactly this pattern work in the first place. Then when I succeed people want it changed. :) Anyway, no surprise: I knew that some want other patterns, and I'll be curious to see the other approaches as well.
I'm not saying it's wrong, and I do think "natural" SQLAlchemy is a strong selling point, since I'd wager there are more SQLAlchemy users than Zope 3 users out there. I just think we need to be careful about what patterns we promote and how it fits with the rest of our "story".
I want to promote the "import Session and instantiate it" pattern. :) If people want to override certain behavior of session creation, *then* I want to promote the "implement and register a utility that provides this interface" pattern. I hope to supply the user with enough pre-implemented IDatabase utilities that this is rare, so that they just end up registering them. A simple global one, and a local one that allows for flexible engine configuration somehow. What the Zope 3 integration I propose changes is how Session is set up. By using utilities we can offer flexibility to set it up globally or locally with ZODB-stored configuration. We use SA's mechanism of actually creating and managing sessions. I think that's a good division of labor: hook into SA with a bit of Zope 3 component architecture for configuration and pluggability, and leave the rest to SA. Regards, Martijn
Brian Sutherland wrote:
On Mon, Jun 16, 2008 at 08:40:24PM +0200, Martijn Faassen wrote: [snip]
from z3c.sa_integration import Session
... def somewhere_in_a_view(self): session = Session() return session.query(Test).all()
For some reason this raises a warning bell in my head. I keep on thinking: this is zope, the session is a classic case for a utility, we should be getting it in views by an interface.
Well, it's also SQLAlchemy, where people get a session this way. That said, integrating the two frameworks produces lots of questions about which framework takes precedence. I've chosen to try to make the SQLAlchemy examples work in the way I interpret them they should work, and try to use SQLAlchemy infrastructure as much as possible, but this is certainly not the only choice.
That may be because I've already become too fixed in the way I think about things.
However, Laurence's point about database re-connection is relevant here. If the Session were looked up by an interface, someone could implement seamless database re-connection (lots of complex code) without polluting zope.sqlalchemy.
And what is seamless database re-connection and why is this a job for Zope and not for SQLAlchemy?
where 'Session' is a custom SA scoped session.
I've checked in my (documented) experiment in here:
svn://svn.zope.org/repos/main/Sandbox/faassen/rdbintegration/trunk
The interesting bits are here:
http://svn.zope.org/Sandbox/faassen/rdbintegration/trunk/src/rdbintegration/...
This looks very nice. I'm not sure IDatabase.engine is necessary, it seems to be only used internally to Database.
Yes, but it is policy someone may want to change. In this case I implemented the policy to look up the engine by name, but it might be the default policy would be to simply look up IEngine locally or something.
Also, any view code should really be getting the connection directly from the session.
I don't understand that what you mean by 'connection'. Oh, you mean which engine is used? That's really just some testing code, I imagine it could directly manipulate the session in realer code. [snip]
As Laurence has suggested, the session persists, so IDatabase.configuration will only be called once per thread. Not sure if creating one new engine per thread is bad.
I don't know either. I've assumed that one wouldn't want to recreate the engine for each thread, but perhaps that's fine and we don't break any, say, connection pooling mechanisms that way? I do not know. Regards, Martijn
On Tue, Jun 17, 2008 at 11:52:49AM +0200, Martijn Faassen wrote:
However, Laurence's point about database re-connection is relevant here. If the Session were looked up by an interface, someone could implement seamless database re-connection (lots of complex code) without polluting zope.sqlalchemy.
And what is seamless database re-connection and why is this a job for Zope and not for SQLAlchemy?
I mean in the situation where you change the database connection parameters on an existing connection and don't want to restart zope to use the new connection parameters. Why people seem to want the above escapes me, but people do seem to want it. I totally agree that Zope should not be responsible for re-connecting on error.
where 'Session' is a custom SA scoped session.
I've checked in my (documented) experiment in here:
svn://svn.zope.org/repos/main/Sandbox/faassen/rdbintegration/trunk
The interesting bits are here:
http://svn.zope.org/Sandbox/faassen/rdbintegration/trunk/src/rdbintegration/...
This looks very nice. I'm not sure IDatabase.engine is necessary, it seems to be only used internally to Database.
Yes, but it is policy someone may want to change. In this case I implemented the policy to look up the engine by name, but it might be the default policy would be to simply look up IEngine locally or something.
Just commenting that IDatabase.engine is not used by any code external to the Database object. There's no use case for it to be public.
Also, any view code should really be getting the connection directly from the session.
I don't understand that what you mean by 'connection'. Oh, you mean which engine is used? That's really just some testing code, I imagine it could directly manipulate the session in realer code.
I was more thinking if you wanted to execute raw sql from a view class you would do that via the engine. But it seems that the session has an execute() method for that.
[snip]
As Laurence has suggested, the session persists, so IDatabase.configuration will only be called once per thread. Not sure if creating one new engine per thread is bad.
I don't know either. I've assumed that one wouldn't want to recreate the engine for each thread, but perhaps that's fine and we don't break any, say, connection pooling mechanisms that way? I do not know.
Unfortunately I don't either...
Regards,
Martijn
_______________________________________________ Zope-Dev maillist - Zope-Dev@zope.org http://mail.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope )
-- Brian Sutherland
Hi there, On Tue, Jun 17, 2008 at 2:30 PM, Brian Sutherland <brian@vanguardistas.net> wrote: [snip]
Just commenting that IDatabase.engine is not used by any code external to the Database object. There's no use case for it to be public.
While I am not sure we have a strong use case for engine anyway, I like methods that people can implement to affect behavior (especially when subclassing) to be public. I don't feel comfortable implementing something that starts with an underscore. [snip]
[snip]
As Laurence has suggested, the session persists, so IDatabase.configuration will only be called once per thread. Not sure if creating one new engine per thread is bad.
I don't know either. I've assumed that one wouldn't want to recreate the engine for each thread, but perhaps that's fine and we don't break any, say, connection pooling mechanisms that way? I do not know.
Unfortunately I don't either...
I'll ask Mike Bayer. Regards, Martijn
Martijn Faassen wrote:
Hi there,
On Tue, Jun 17, 2008 at 2:30 PM, Brian Sutherland <brian@vanguardistas.net> wrote: [snip]
Just commenting that IDatabase.engine is not used by any code external to the Database object. There's no use case for it to be public.
While I am not sure we have a strong use case for engine anyway, I like methods that people can implement to affect behavior (especially when subclassing) to be public. I don't feel comfortable implementing something that starts with an underscore.
Why not just have: class IDatabase(Interface): """A utility that specifies the database. """ def session_factory(): """Create a new session """ def id(): """Get unique id for this database configuration. This should be unique per site (application). """ class Database(grok.LocalUtility): grok.implements(IDatabase) def session_factory(self): engine = create_engine( 'postgres:///experiment', convert_unicode=True) return create_session( bind=engine, autocommit=True, autoflush=True, extension=ZopeTransactionExtension()) def id(self): # we use the application name as the unique id. Can we use # something more clever and universally working? return self.__parent__.__name__
[snip]
[snip]
As Laurence has suggested, the session persists, so IDatabase.configuration will only be called once per thread. Not sure if creating one new engine per thread is bad. I don't know either. I've assumed that one wouldn't want to recreate the engine for each thread, but perhaps that's fine and we don't break any, say, connection pooling mechanisms that way? I do not know. Unfortunately I don't either...
I'm not sure connection pooling is really useful in a threaded environment with recycled sessions. You want n threads = n connections. If we started creating new sessions each request then things would be different. Laurence
Laurence Rowe wrote: [snip]
Why not just have:
class IDatabase(Interface): """A utility that specifies the database. """
def session_factory(): """Create a new session """
def id(): """Get unique id for this database configuration.
This should be unique per site (application). """
This looks quite a lot like my proposed new IDatabase, which in fact also has a scopefunc() method, so that you can have a simple per-thread scope if you don't care about per-application scopes.
class Database(grok.LocalUtility): grok.implements(IDatabase)
def session_factory(self): engine = create_engine( 'postgres:///experiment', convert_unicode=True) return create_session( bind=engine, autocommit=True, autoflush=True, extension=ZopeTransactionExtension())
def id(self): # we use the application name as the unique id. Can we use # something more clever and universally working? return self.__parent__.__name__
Yes, that would be good. I'm just making sure it's wise to recreate an engine for each thread/application. Regards, Martijn
Hey Laurence, Laurence Rowe wrote: [snip]
I'm not sure connection pooling is really useful in a threaded environment with recycled sessions. You want n threads = n connections. If we started creating new sessions each request then things would be different.
Mike Bayer says the following:
you should reuse a single engine. It contains a pool of connections, so recreating engines means new connections are constantly rebuilt and it defeats the purpose of the pool. Other than that the creation of an engine is not very expensive but even a small expense is needless here.
I explained to him we'd only end up creating a single engine per thread (and app), and we'll see what he has to say about that. I'm leaning towards trying to recycle the engines, though. Regards, Martijn
Hey, [replying to myself] Martijn Faassen wrote:
Laurence Rowe wrote: [snip]
I'm not sure connection pooling is really useful in a threaded environment with recycled sessions. You want n threads = n connections. If we started creating new sessions each request then things would be different.
Mike Bayer says the following:
you should reuse a single engine. It contains a pool of connections, so recreating engines means new connections are constantly rebuilt and it defeats the purpose of the pool. Other than that the creation of an engine is not very expensive but even a small expense is needless here.
I explained to him we'd only end up creating a single engine per thread (and app), and we'll see what he has to say about that.
Mike responded like this:
yeah that also kind of defeats the connection pool's purpose as it's intended to handle all connections for a particular database across threads. Having a single point of pooling for a certain database has the advantage that you can tune the total number of connections at a single configuration point, and also that a database restart can be detected just once, resulting in a bounce of the entire pool (this is a distinct advantage of using a pool over a non-pooled approach).
So, it looks like we need to make sure we maintain a single engine per database. Regards, Martijn
On Jun 17, 2008, at 10:09 AM, Laurence Rowe wrote:
I'm not sure connection pooling is really useful in a threaded environment with recycled sessions. You want n threads = n connections. If we started creating new sessions each request then things would be different.
For this to be efficient in SQLA (if there were no pool), you'd need to bind each Session to a Connection individually. If you create_engine() using a pool like NullPool then you'd get an unpooled engine that creates new connections on each checkout. In theory this would be fine, although we don't have any current test coverage for the "auto-reconnect" logic using NullPool with persistently- checked-out Connections so we'd have to build that up and ensure its working. But if you are binding Sessions directly to Connections, theres still not really any disadvantage to the usual QueuePool implementation remaining in place. If you set it with max_overflow=-1 it will just hand out any number of connections. I still prefer the Session bound to Engine approach, whereby each transaction uses a pooled connection, and returns the connection on rollback/commit. That way you don't need to worry about stale connections hanging around for sessions/threads that fall out of use.
2008/6/17 Michael Bayer <mike_mp@zzzcomputing.com>:
On Jun 17, 2008, at 10:09 AM, Laurence Rowe wrote:
I'm not sure connection pooling is really useful in a threaded environment with recycled sessions. You want n threads = n connections. If we started creating new sessions each request then things would be different.
For this to be efficient in SQLA (if there were no pool), you'd need to bind each Session to a Connection individually. If you create_engine() using a pool like NullPool then you'd get an unpooled engine that creates new connections on each checkout. In theory this would be fine, although we don't have any current test coverage for the "auto-reconnect" logic using NullPool with persistently-checked-out Connections so we'd have to build that up and ensure its working.
But if you are binding Sessions directly to Connections, theres still not really any disadvantage to the usual QueuePool implementation remaining in place. If you set it with max_overflow=-1 it will just hand out any number of connections.
I still prefer the Session bound to Engine approach, whereby each transaction uses a pooled connection, and returns the connection on rollback/commit. That way you don't need to worry about stale connections hanging around for sessions/threads that fall out of use.
What connection pooling is used by default? e.g. with create_engine('sqlite:///:memory:') I think we are only talking about the difference between using four pools of one connection versus one pool of four connections (assuming the standard four threads in zope). I don't see that making a lot of difference in practice. Laurence
On Jun 17, 2008, at 12:41 PM, Laurence Rowe wrote:
What connection pooling is used by default? e.g. with create_engine('sqlite:///:memory:')
sqlite is a special case, it uses the SingletonThreadPool. This pool holds onto one connection per thread. This is used in SQLite because of a sometimes-restriction that a sqlite connection can only be used in the same thread in which it was created. The pool in normal use is QueuePool.
I think we are only talking about the difference between using four pools of one connection versus one pool of four connections (assuming the standard four threads in zope). I don't see that making a lot of difference in practice.
in practice, a single pool of four connections means if one of those connections encounters a "connection lost" exception, the exception is raised, and the entire pool is recycled; meaning that only one exception is raised for the whole application during a database restart. It also means that the total connections used by the application for a particular database can be configured/throttled in one place. To me thats a significant difference.
Hey, Michael Bayer wrote: [snip comments on connection pooling] Thanks for showing up here and participating in the discussion, Michael. To me it's clear we should try to reuse engines as much as we can. Regards, Martijn
participants (5)
-
Brian Sutherland -
Laurence Rowe -
Martijn Faassen -
Martin Aspeli -
Michael Bayer