set_shard is a special method added by the horizontal sharding extension. you can do cross schema queries if you organize the schema names in terms of which ones apply to the "dynamic" shard and which ones to the "fixed" shard, if that's how it works.
If OTOH you literally need to join against multiple, dynamically named shards at one time, then you need to spell those out explicitly. it gets more ugly but if you want a Table that is on the fly linked to a certain schema explicitly you can use table.tometadata(), see http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html?highlight=tometadata#sqlalchemy.schema.Table.tometadata. On May 6, 2014, at 3:06 AM, Julien Meyer <[email protected]> wrote: > 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 <[email protected]> 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 [email protected]. >> To post to this group, send email to [email protected]. >> 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
