Multiple database usage in Turbogears

[Bryn Divey]

Hi all,

We're building an application on TG 1.0.2 using SQLAlchemy 0.3.11 are
are busy considering our options wrt multiple customer support. We'd
be hosting the app servers, and there's a low upper-bound to the
traffic they'll be hit with, so we're trying to figure out a way to
support multiple customers on a single app instance.

The problem is that the system is quite complex and there were
limitations which made making our DB schemas multi-customer rather
difficult. This means that we'd basically need to figure out - per
request - which customer we're working with, and load their database
up for the lifetime. An experimental solution exists which hijacks
things like turbogears.database.get_engine and replaces them with a
function which determines the correct engine to be loaded. I'm still
wading through it to try get it up and running again (I'm getting
Visit attached to incorrect session errors atm.)

The question I have is: is this the correct solution (or, at least, on
the right path)? I'd love to hear from anyone else who's tried
something similar and get your experience of the problem.

Thanks,
Bryn

[Paul Johnston]

Hi Bryn,

There's some info on multiple databases here:
http://docs.turbogears.org/1.0/SQLAlchemy#multiple-databases

However, it sounds like you have slightly different requirements. You have multiple databases (one per customer) with the same schema. In this case, you probably want to use SQLAlchemy session binding. I've never done that myself, but it may be worth asking on the SA list about it. You probably will have to do some hacking at TG to make it work.

Paul

[Barry Hart]

>I've never done that myself, but it may be worth asking on the SA list about it. You probably will have to do
>some hacking at TG to make it work.

Our applications use a custom (non-SQL) database for their primary data store and identity tracking so we had some of the same requirements 'hijack' TurboGears normal database plumbing. (Our apps do use a SQL database but only for visit tracking.)

We've been able to do this without any modifying or monkey patching any parts of TurboGears. We used the following techniques:
- We use custom visit and identity modules. In TurboGears, these are plugins chosen via .cfg options.
- TG wraps transactions around controller methods using the 'generic method' feature. Thus you can replace it by writing your own run_with_transaction (registered using when or around).
- When our controller methods need to access the database, they use a different global variable (not the ones in turbogears.database).
- We copied and modified the @paginate decorator (the copy lives in our code). Having to copy code felt like a defeat compared to the other areas, but unfortunately paginate() was not designed to be extensible.

Barry

---

Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

[Lukasz Szybalski]

To connect to multiple databases in tg do I do:?

in dev.cfg
sqlalchemy.dburi = 'mysql://maindb/myapp'
sales.dburi = 'oracle://extserver/oldapp'

and then bind_meta_data? Does this function ONLY binds sqlalchemy.dburi?
or

in model.py
from turbogears.database import metadata
sales_metadata = MetaData('oracle://extserver/oldapp')
or can I call it this way?
sales_metadata = MetaData(sales.dburi) If I defined it in dev.cfg?

mytbl = Table('mytbl', metadata, # Creates in main db
...
tbl2 = Table('tbl2', sales_metadata, # Creates in sales db
...

Lucas

[Jorge Godoy]

I have multi-client apps (even changing the stylesheets applied to the
templates) but I used cookies for determining which client is accessing the
application and I have the client coded at the DB tables, i.e., something
differentiates a row from one client of a row from another client.

Going this route allowed me using TG as it is, without any monkeypatch or
change, and also allows me to get the most from the database, even sharing
common information among several clients.

If you use PostgreSQL, I'd suggest using multiple schemas and using the search
path to find the tables you want, then you can easily change things by simply
changing the search path.

--
Jorge Godoy <jgo...@gmail.com>

[Bryn Divey]

Thanks for the comments, everyone. I've using the "overwrite db-
session" route, and it appears to be working (along with a modified
VisitManager written by a colleague). I'll tell you how it goes (;

>  signature.asc
> 1KDownload