Selective db sessionmaker for multiple databases

[Juparave]

Hello TG Group,

Examining TG2.2 I found that now it has an option to support load balancing using SQLAlchemy Master Slave Load Balancing.

What it does is to subclass sqlalchemy.orm.Session, overrides get_bind method, and sets it in the sessionmaker as class_; much like A. Molina explain in other post but now I kind of get it.

I'm interested in beeing able to select a database depending from where my app gets the request.  So I figure to copy the balanced session method and adapt it to my needs.

I have two scenarios, 

The request comes from:

http://db_one.myapp.com I want to select the database db_one

       ------

The request comes from:

http://myapp.com/db_one

                 ------

                 

In the first one

dbname = request.host.split('.myapp.com')[0]

db_url = config['sqlalchemy.url'].replace('dbname', dbname)

engine = create_engine(db_url, pool_recycle=3600)

The second one I haven't realized yet, maybe I'll try with a parameter like ac=db_one on the url.

When I try to implement this for the first scenario I get 

'TypeError: No object (name: request) has been registered for this thread'

When the method is called 'request' object is 'request: <paste.registry.StackedObjectProxy object at 0x90a4e0c>' meaning is not yet defined.

Looking at the TG stack

WSGI Server

  PasteCascade - serves one of a list of WSGI apps.

    StaticFile Server - serves static files from /public

 OR

    TurboGears Application: - the TG stack

    Registry Manager - sets up the request proxy, etc.

      Error Middleware - if the path goes to _debug handle the request

        Database Session Manager - setup the DBSession

          Transaction Manager -

            Authentication - add info to the environ if user is authenticated

              Authorization - add more info to the environ for authorization.

                ToscaWidgets - nothing on the way in.

                  Cache - sets up the cache

                    Session - sets up the web session

                      Routes - parses the URL and adds info to environ

                        Custom Middleware - User defined middleware

                          TurboGearsApp – calls WSGI style controller

                            ObjectDispatchController – gets params, do validation, etc

                               Your Controller Code – does anything!

                            ObjectDispatchController – renders response, etc.

                ToscaWidgets - injects resources used by widgets

          Transaction Manager - commits or rolls back transaction

        Database Session Manager - cleans up the DBSession

      Error Middleware - displays error pages, etc

      

The request proxy is set before the db session manager so I don't know why is request getting called before assignment.

Any orientation will be appreciated.

Thanks

[Alessandro Molina]

The repoze.who auth layer gets called before turbogears has registered
request, probably for that reason you get the error when repoze.who
tries to fetch the user from database before the Session can decide
where to look for the user.

For that reason the load balancing session provided a default case
that always relied on the master node.

To make authentication requests go to different databases you must
write your own Authenticator which should set some thread local
variable that the get_bind method can use to force a database.
While it did it a lot of time ago by patching the engine instead of
using the session ACRCms did the same exact thing, you can probably
give a look at AcrAuthenticatorPlugin to get some help.

ArcAuthenticatorPlugin and AcrMultisiteEngine are available at:
https://bitbucket.org/axant/acrcms/src/1bb149ad9c0d/acr_cms/acr/lib/multisite.py

> --
> You received this message because you are subscribed to the Google Groups
> "TurboGears" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/turbogears/-/jeP6Efo1afkJ.
> To post to this group, send email to turbo...@googlegroups.com.
> To unsubscribe from this group, send email to
> turbogears+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/turbogears?hl=en.

[Juparave]

Thanks Alessandro,

I've seen your multisite.py file before and I think I can make the modifications needed.  I'm migrating to TG2.2 and it would not support repoze.what anymore, so how would you change your final lines.

# IMetadataProdiver
    def add_metadata(self, environ, identity):
        identity['user'] = self.get_user(environ, identity['repoze.who.userid'])
        identity['groups'] = self.get_groups(environ, identity['user'])

        environ['repoze.what.credentials'] = {}
        environ['repoze.what.credentials']['groups'] = identity['groups']

Where do you set the credentials dict now?

Cheers