Ouch, that sounds painful! Not an ORM friendly setup. Hell, not friendy, full stop.
So, the rules state that the data should be physically separated, but handling them concurrently in the same application is not a problem? Random things to test: -Use a session factory per database and TransactionScope/MSDTC to handle transactions spanning multiple databases. TransactionScope+NH is not without its fair share of problems, but maybe you could limit its use. -(Assuming the silos look the same) Create views in one database, which pulls data from the silos and merges it with UNION ALL, including its identity in one column. If necessary, use this identity as part of the key. Handle data manipulation using INSTEAD OF triggers, redirecting inserts etc to the corresponding database. Such views and triggers should be possible to create with a bit of "scripting" -If possible use different setups (mapping, session factories, maybe even separate code domains) for single vs. multi silo scenarios. -Maybe, just maybe, consider if the use of NHibernate, or any other OR mapper for that matter, really will make you more productive. /G 2013/8/23 Patrick Doran <[email protected]> > Gunnar absolutely. > > The dynamic databases represent data that must remain "siloed" (stored on > separate discs) this is for compliance purposes and a restriction on our > design (and also something I happened to inherit :( ). What this means is > that for each new "silo" we create a new database, it is placed on to its > new disc/partition and we store a master record of each silo in one of our > Master databases. > > The need for transactions to span multiple databases is for a few reasons > (I had considered a session factory per siloed database but the transaction > limitation breaks that down) > > 1) We have certain objects that are stored in the silos however we store > pointers to those objects in a master table in one of the shared databases. > 2) We have some business requirements that ask that data in silo 1 can be > related to something in silo 2. So this means we have to query Silo 1 for > some guid, then point to silo 2 and save off a record that links some item > back to a record, that happens > to exist in silo 1. If any of those steps fail, transaction is rolled > back, hence the need for a transaction > 3) In certain scenarios We have to be able to loop over all of the silos > aggregate the data and return the results > > Feel free to ask more questions, I know the design is rather unorthodox > and I am really trying to make lazy loading work, but I am banging my head > against my desk trying ;\ I appreciate your interest in helping! > > > > On Thursday, August 22, 2013 11:12:14 AM UTC-4, Gunnar Liljas wrote: > >> Could you describe the reasoning behind the design and how it is used? I >> wont suggest that you change it, but I need to understand it in order to >> propose something. >> >> >> 2013/8/22 Patrick Doran <[email protected]> >> >>> The database setup we are using currently is 3 "standard" databases >>> where their mapping schema is mapped to "database.schema", however we also >>> have a set of mapped items where the mapping of the schema is to >>> .schema. This database schema/architecture design may sound unorthodox but >>> it something I can't change, so please don't suggest that. >>> >>> The current use for this DAL is in a MVC web application, we are using >>> Autofac MVC dependency resolver to scope a session the full lifetime of a >>> web request. We then use Castle Proxies to intercept requests to our >>> Services that are marked with a transaction attribute or requests to >>> Repositories, and sniff the httpcontext information to determine which >>> database to point to. We create a unit of work with the interceptor, which >>> based on the sniffed database value calls >>> Session.Connection.**ChangeDatabase(database); >>> This solution works great with lazy loading turned off. >>> >>> We had a different setup before that was not very flexible and have >>> moved to this setup. Previously we had lazy loading turned off and we are >>> trying to, with our new solution, turn it back on. In our integration tests >>> we are able to lazy load successfully with the static schema objects, >>> however the "dynamic schema" database lazy loading is problematic, because >>> when it goes to lazy load it will encounter an ADOException "Invalid object >>> name '.schema.table'", which makes sense. >>> >>> We also need to be able to switch databases during a transaction, so we >>> can't tie the session directly to the database we want to connect to. >>> >>> I am curious if there is anyway to intercept the lazy loading requests >>> and execute a Session.Connection.**ChangeDatabase command. This of >>> course also has the requirement that I can somehow store which database a >>> particular entity came from so that I can reapply when I intercept the >>> request which database to target. My guess is I may need to implement an >>> interceptor and wire it in differently IE config.SetInterceptor (in the >>> code below) and perhaps during Instantiate I could set some property that >>> is available to all "Dynamic Database" models IE Database, and then during >>> lazy load intercept and issue the ChangeDatabase on the session. >>> >>> Here is what the Autofac registeration looks like that we use now: >>> >>> >>> builder.Register(c => { >>> string connect**ionString = >>> String.Format(Conf**iguration.Settings.Infrastruct**ure.ConnectionString, >>> DEFAULT_**DATABASE); >>> var config = n**ew Configuration(); >>> config.DataBas**eIntegration(db => { >>> db.Driver<**SqlClientDriver>(); >>> db.Dialect**<MsSql2008Dialect>(); >>> db.Connect**ionString = connectionString; >>> }); >>> config.AddAsse**mbly(typeof(UnitOfWork).Assemb**ly); >>> // maybe config.SetInterceptor(new SomeCustomInterceptor()) >>> ISessionFactor**y factory = config.BuildSessio**nFactory(); >>> return new Red**actedSessionFactory(DEFAULT_**DATABASE, >>> factory); >>> }).As<IRedactedSes**sionFactory>() >>> .SingleInstance(**); >>> >>> >>> // Repository **Interceptor. >>> builder.Register((**c, p) => { >>> IRedactedSessi**onFactory factory = >>> c.Resolve<**IRedactedSessionFactory>(); >>> >>> string name = **GetDatabaseNameFromEnvironment**(c, p); >>> name = name ??** DEFAULT_DATABASE; >>> factory.Databa**se = name; >>> return new Uni**tOfWorkInterceptor(factory, na**me); >>> }).InstancePerLife**timeScope(); >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "nhusers" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to nhusers+u...@**googlegroups.com. >>> To post to this group, send email to [email protected]. >>> >>> Visit this group at >>> http://groups.google.com/**group/nhusers<http://groups.google.com/group/nhusers> >>> . >>> For more options, visit >>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >>> . >>> >> >> -- > You received this message because you are subscribed to the Google Groups > "nhusers" 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/nhusers. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "nhusers" 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/nhusers. For more options, visit https://groups.google.com/groups/opt_out.
