Hello Diego, I understand that my question sounds a bit overwhelming, because setting the context to my problem is not something easy.
I think the sql statements gives a comprehensive overview of what I'm trying to achieve in the most concise manner besides the textual description. Also, my mail ends with a sample usage code with NH which shows how I intend to use the mapped classes. Now I agree with the fact that the questions are very open. Thanks for letting me know of theses issues. Best regards On Apr 22, 8:12 am, Diego Mijelshon <[email protected]> wrote: > Gauthier, > > It's unlikely that you'll get many responses to a very long email which > makes no references to classes, mappings, or anything NH-related (only SQL), > while asking very open questions. > I recommend that you start with a small proof-of-concept and ask specific > questions when you found roadblocks (and after reading the documentation, > that is) > > Diego > > On Thu, Apr 22, 2010 at 11:51, Gauthier Segay <[email protected]>wrote: > > > > > > > I'm bumping this one, > > > if the description is not clear, is there anything I can do to help > > your understanding of the problem? > > > On Mar 16, 10:30 am, Gauthier Segay <[email protected]> wrote: > > > I'm working on an application which has no ORM and a rather special data > > model. > > > > The model is made of several entities that fits a relational model on > > > the logical side, one entity may be related to another, etc. But the > > > physical implementation is relying on a specific mechanism targeting > > > issue with copying of all the data in a specific use case. > > > > The application is managing large sets of data composing a case. Each > > > case (or "top case") references a number of subcases. > > > The user is allowed to create a new case from an existing one; > > > physically, creating a new case is just adding a row to the case > > > table, this row referencing each set of entities (i.e. the row holds > > > the subcase identifiers), the application starts duplication of > > > a subset of data only if any data is changed in subcase data that is > > > shared among multiple cases. > > > > So, what is special, is that the model is not using strong foreign > > > keys to maintain the relations, it's somewhat handled by the > > > application code and what I would call loose coupled relations. > > > > The relation is made of the subcase identfier (coming from the case > > > row for a given entity) and an arbitrary identifier. > > > > here is a sample schema script (mssql) > > > > ---------------------- > > > create table cases ( > > > [key] int identity > > > , shop_case_key int > > > , product_case_key int > > > , product_by_shop_case_key int > > > , name nvarchar(100) > > > ); > > > > create table shop_cases ( > > > [key] int identity > > > , subcase_name nvarchar(100) > > > ); > > > > create table product_cases ( > > > [key] int identity > > > , subcase_name nvarchar(100) > > > ); > > > > create table product_by_shop_cases ( > > > [key] int identity > > > , subcase_name nvarchar(100) > > > ); > > > > create table shops ( > > > shop_case_key int > > > , shop_id int > > > , name nvarchar(100) > > > , product_space int > > > ); > > > > create table products ( > > > product_case_key int > > > , product_id int > > > , name nvarchar(100) > > > , price decimal > > > ); > > > > create table product_by_shop ( > > > product_by_shop_case_key int > > > , product_id int > > > , shop_id int > > > ); > > > > insert into shop_cases (subcase_name) values ('shopcase1'); > > > insert into shop_cases (subcase_name) values ('shopcase2'); > > > > insert into product_cases (subcase_name) values ('productcase1'); > > > insert into product_cases (subcase_name) values ('productcase2'); > > > > insert into product_by_shop_cases (subcase_name) values > > ('productbyshopcase1'); > > > > insert into cases (shop_case_key, product_case_key, > > > product_by_shop_case_key, name) > > > select 1,1,1,'case 1' > > > union all > > > select 1,2,1,'case 2' > > > union all > > > select 2,1,1,'case 3' > > > > insert into shops (shop_case_key, shop_id, name, product_space) > > > values(1, 1, 'shop 1', 100); > > > insert into shops (shop_case_key, shop_id, name, product_space) > > > values(1, 2, 'shop 2', 200); > > > insert into shops (shop_case_key, shop_id, name, product_space) > > > select 2, shop_id, name, product_space*2 from shops > > > > insert into products (product_case_key, product_id, name, price) > > > values (1, 1, 'product 1', 1); > > > insert into products (product_case_key, product_id, name, price) > > > values (1, 2, 'product 2', 10); > > > insert into products (product_case_key, product_id, name, price) > > > select 2, product_id, name, price * 3 from products; > > > > insert into product_by_shop (product_by_shop_case_key , product_id, > > > shop_id) values (1, 1, 1); > > > insert into product_by_shop (product_by_shop_case_key , product_id, > > > shop_id) values (1, 2, 1); > > > insert into product_by_shop (product_by_shop_case_key , product_id, > > > shop_id) values (1, 1, 2); > > > ---------------------- > > > > for a given case key and shop id, looking at the product list for a > > > given shop, the SQL whould look like that > > > > select > > > c.*, s.shop_id, p.* > > > from > > > cases c > > > inner join shop_cases sc on c.shop_case_key = sc.[key] > > > inner join product_cases pc on c.product_case_key = pc.[key] > > > inner join product_by_shop_cases pbsc on > > c.product_by_shop_case_key = > > > pbsc.[key] > > > inner join product_by_shop pbs on pbs.product_by_shop_case_key > > > = pbsc.[key] > > > inner join shops s on > > > sc.[key] = s.shop_case_key > > > and pbs.shop_id = s.shop_id > > > inner join products p on > > > pc.[key] = p.product_case_key > > > and pbs.product_id = p.product_id > > > where > > > c.[key] = @caseKey > > > and s.shop_id = @shopId > > > > as you see this is turning pretty complicated with a simple model, the > > > application is quite larger than this. > > > > I'm investigating possibilities to fit this in a normal NH model where > > > I could use such things: > > > > using(var session = CraftSessionForCaseKey(1) /* some black magic in > > > setting the session or session factory */){ > > > var shop = session.GetByKey<Shop>(1); > > > var products = shop.Products; // will issue the previous select > > > statement (with product columns) > > > > } > > > > Assuming I can't change the way we put the model, is there something > > > that you think is remotely feasible, even putting some amount on > > > effort to override some key components involved in the way ISession or > > > ISessionFactory works? > > > > I currently haven't come up with lots of ideas on how to overcome this > > > issue (session filter aren't enough to manipulate entity > > > relationships). Things are done this way to allow the end user to > > > create new cases at will and only incurring the performance hit of > > > duplicating data to a subset each time a tweak is made in an entity > > > set which is used across multiple top cases. > > > > I've thought about the following: > > > > - dynamically crafting views with triggers > > > - storing changes as diff > > > > but these seem to be seriously complex to me, NH could be of help with > > > the first one, but for the second it involves a lot of > > > application / model logic. > > > > Alternatively, did anyone encountered such a situation? what > > > alternative to such model would you find applicable? > > > > Thanks a bunch for your insight. > > > -- > > You received this message because you are subscribed to the Google Groups > > "nhusers" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]<nhusers%[email protected] > > > > > . > > For more options, visit this group at > >http://groups.google.com/group/nhusers?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group > athttp://groups.google.com/group/nhusers?hl=en. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
