My real database schema is a little more complex. In reality, I have one database by company. In each database, I have multiple schemas who contain the same table structure.
The solution "schema name execution" will not work in the case when I need to access to more than one schema by request. The Horizontal sharding can work : one engine by schema and set the search path when creating the engine. During the request processing, I can identify wich schema to use and with the use of "set_shard" on the Query object (not found in the documentation, normal ?), I can easely select the good shard to use. But I don't know how I can make a cross schema query in this case? Le lundi 5 mai 2014 19:12:06 UTC+2, Michael Bayer a écrit : > > part of a feature that will make this kind of thing more direct is the > “schema name execution argument” feature, which is > https://bitbucket.org/zzzeek/sqlalchemy/issue/2685/default-schema-as-an-execution-argument > . > > This application is somewhat of a “multi-tenancy” application; technically > its horizontally partitioned but if you know “society” up front and for the > duration of an operation, you can just set that and be done with it. > > Assuming this is the case an easy way to do this for now is just to set > the “search path” on your postgresql connection before such an operation > proceeds. That way when you refer to table X or Y, it will be in terms of > whatever search path you’ve set, see 5.7.3 at > http://www.postgresql.org/docs/8.1/static/ddl-schemas.html. > > There’s no need in that case to use any kind of explicit “horizontal > sharding”. Only if you need queries that are going to refer to multiple > schemas at once does the HS feature come into play (and if that were the > case I’d look into PG table inheritance). > > > > > On May 5, 2014, at 8:41 AM, Julien Meyer <julien...@gmail.com<javascript:>> > wrote: > > I need some help and advices to create a mapping. > > The context : > - Multiple schemas on postgresql (dynamic number and name) who store the > "same" tables. > - SQLAlchemy used into a pyramid web application. > > Example : > A table "Customer" and a table "CustomerOrder" (link by customer.id) and > a schema by society (not know before running) > > > I read the documentation about horizontal, vertical sharding and entity > name but I'm a little bit confused about the good solution to solve my > problem. > > If I use "Entity name", I don't know how to configure the relationship > between my two dynamic classes because I need to specify a class at > configuration time but i really know the real subclasses only at runtime. > > If I use the "Horizontal sharding", I need to have an engine / schema (and > use search_path). The shard configurtion will be (or seems to be) tricky. > > If I use the "Vertical sharding", I need also an engine / schema and > re-configure the session several times with a new binds mapping. > > I made some google search with my context but it's not an usual case and i > didn't find some helpful posts.... > > I also posed the question on stackoverflow last year but my solution don't > really work : > http://stackoverflow.com/questions/20212165/one-entity-in-multiple-schemas-how-to-switch-schema-on-runtime > > Thanks in advance. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com<javascript:> > . > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
