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] <javascript:>> > >> 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 connectionString = >> String.Format(Configuration.Settings.Infrastructure.ConnectionString, >> DEFAULT_DATABASE); >> var config = new Configuration(); >> config.DataBaseIntegration(db => { >> db.Driver<SqlClientDriver>(); >> db.Dialect<MsSql2008Dialect>(); >> db.ConnectionString = connectionString; >> }); >> config.AddAssembly(typeof(UnitOfWork).Assembly); >> // maybe config.SetInterceptor(new SomeCustomInterceptor()) >> ISessionFactory factory = config.BuildSessionFactory(); >> return new RedactedSessionFactory(DEFAULT_DATABASE, factory); >> }).As<IRedactedSessionFactory>() >> .SingleInstance(); >> >> >> // Repository Interceptor. >> builder.Register((c, p) => { >> IRedactedSessionFactory factory = >> c.Resolve<IRedactedSessionFactory>(); >> >> string name = GetDatabaseNameFromEnvironment(c, p); >> name = name ?? DEFAULT_DATABASE; >> factory.Database = name; >> return new UnitOfWorkInterceptor(factory, name); >> }).InstancePerLifetimeScope(); >> >> -- >> 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] <javascript:>. >> To post to this group, send email to [email protected]<javascript:> >> . >> 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.
