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 at http://groups.google.com/group/nhusers?hl=en.
