[This message has also been posted.]
On Sun, 13 Jun 2010 13:27:34 -0700, Henry Precheur <he...@precheur.org> wrote:
> I'm starting a new project which is roughly hosting a bunch of sites. I
> want the sites to be isolated, they'll all have the same schema (data
> definition), but will store their data on different 'name-spaces'.
>
> The front end will be a python web app. And I'd like to have this
> application talk to all the 'name-spaces' at the same time:
>
> With a request on example.com/site1, the web app will use the
> 'name-space' site1, with example.com/site2 it will use site2.
>
> I am using Postgres. There are 2 options for the 'name-space': Database
> or Schema [1]:
> 1. One database per site
> 2. One database for all the sites and 1 schema per site
>
>
> Solution #1 would require to maintain 1 connection per site & per python
> process. That means: lots of connections, & lots of memory needed. One
> the other hand, this solution is supported by SQLAlchemy out-of-the-box.
> I'll have a dictionary like that:
>
> {'site1': Engine('postgres://.../site1',
> 'site2': Engine('postgres://.../site2', ...}
>
> And whenever a request comes in I get the right engine via this
> dictionary.
>
>
> Solution #2 is not supported natively by SQLAlchemy. Each time a request
> comes-in I'll have to issue an additional query "SET search_path TO
> MY_SITE" where MY_SITE is the schema associated with the site.
Sqlalchemy's table can take the qschema as argument, eg.
pheno_table = Table(
'pheno', metadata,
Column('patientid', String(60), primary_key=True),
Column('famid', String(60), nullable=True),
Column('sex_id', None, ForeignKey(schemaname+'.sex.val',
onupdate='CASCADE', ondelete='CASCADE'), index=True),
Column('race_id', None, ForeignKey(schemaname+'.race.val',
onupdate='CASCADE', ondelete='CASCADE'), index=True),
Column('phenotype', SmallInteger),
schema = schemaname,
)
So I don't think you do have to do that.
> Solution #2 seems much more lightweight to me. The only problem is the
> small overhead that might be created by the additional query.
I'm actually using multiple schemas in one db myself, and it seems to
me sqla supports this just fine. The only time I have to do
SET search_path TO MY_SITE
is when I access the db directly using psql. Of course, you might have
to worry whether the web end of things support schemas too.
Faheem.
> What do you guys think? Will I get into trouble with solution #2?
>
> If you have alternative suggestions I'd like to hear them :)
>
>
> Regards,
>
> [1]: http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html
>
> --
> Henry Prêcheur
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
