many-to-many association table clean up
"Tony" <[email protected]> Wed, 22 Oct 2008 06:09:53 +0100
| Newsgroups | gmane.comp.windows.dotnet.nhibernate.devel |
|---|---|
| Message-ID | <[email protected]> |
This is a multipart message in MIME format.
--===============8756534649639412805==
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0001_01C9340C.CE100CA0"
Content-Language: en-gb
This is a multipart message in MIME format.
------=_NextPart_000_0001_01C9340C.CE100CA0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
I have a scenario in a system which I've tried to simplify as best as I can.
We have a table of (lets call them) artefacts, artefacts can be accessed by
any number of security roles and security roles can access any number of
artefacts. As such, we have 3 tables in the database - one describing
artefacts, one describing roles and a many-to-many association table linking
artefact ID to Role ID.
Domain wise, we have two classes - one for a role and one for an artefact.
the artefact class has an IList property that returns a list of roles that
can access it. (Roles however do not offer a property to get artefacts that
can be accessed).
As such, the nhibernate mapping for artefact contains the following;
<bag name="AccessRoles" table="ArtefactAccess" order-by="RoleID"
lazy="true" access="field.camelcase-underscore" optimistic-lock="false">
<key column="ArtefactID"/>
<many-to-many class="Role" column="RoleID"/>
</bag>
This all works fine and if I delete an artefact, the association table is
cleaned up appropriately and all references between the removed artefact and
roles are removed (the role isn't deleted though, correctly - as we don't
want orphans deleted).
The problem is - how to delete a role and have it clear up the association
table automatically. If I presently try to delete a role, I get a reference
constraint as there are still entries in the association table for the role.
The only way to successfully delete a role is to query for all artefacts
that link to that role, remove the role from the artefact's role collection,
update the artefacts and then delete the role - not very efficient or nice,
especially when in the un-simplified system, roles can be associated with
any number of other tables/objects.
I've tried adding a protected IList bag to role that links up to artefact's
(even though I don't want this in my domain model) and set this as the
inverse relationship (inverse="true" on the bag) but this doesn't solve the
problem.
I need to be able to hint to NHibernate that I want this association table
cleared whenever I delete a role - is this possible, and if so - how do I do
it?
Thanks for any help.
------=_NextPart_000_0001_01C9340C.CE100CA0
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
p.Preformatted, li.Preformatted, div.Preformatted
{mso-style-name:Preformatted;
mso-style-priority:99;
margin:0cm;
margin-bottom:.0001pt;
text-autospace:none;
font-size:10.0pt;
font-family:"Courier New";}
.MsoChpDefault
{mso-style-type:export-only;}
@page Section1
{size:595.3pt 841.9pt;
margin:70.85pt 63.65pt 2.0cm 63.65pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3DEN-GB link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
<p class=3DMsoNormal =
style=3D'mso-margin-top-alt:5.0pt;margin-right:0cm;margin-bottom:
5.0pt;margin-left:0cm;text-autospace:none'><span =
style=3D'font-size:12.0pt;
font-family:"Times New Roman","serif"'>I have a scenario in a system =
which I've
tried to simplify as best as I can. We have a table of (lets call them)
artefacts, artefacts can be accessed by any number of security roles and
security roles can access any number of artefacts. As such, we have 3 =
tables in
the database - one describing artefacts, one describing roles and a
many-to-many association table linking artefact ID to Role =
ID.<o:p></o:p></span></p>
<p class=3DMsoNormal =
style=3D'mso-margin-top-alt:5.0pt;margin-right:0cm;margin-bottom:
5.0pt;margin-left:0cm;text-autospace:none'><span =
style=3D'font-size:12.0pt;
font-family:"Times New Roman","serif"'>Domain wise, we have two classes =
- one
for a role and one for an artefact. the artefact class has an IList =
property
that returns a list of roles that can access it. (Roles however do not =
offer a
property to get artefacts that can be accessed).<o:p></o:p></span></p>
<p class=3DMsoNormal =
style=3D'mso-margin-top-alt:5.0pt;margin-right:0cm;margin-bottom:
5.0pt;margin-left:0cm;text-autospace:none'><span =
style=3D'font-size:12.0pt;
font-family:"Times New Roman","serif"'>As such, the nhibernate mapping =
for
artefact contains the following;<o:p></o:p></span></p>
<p class=3DMsoNormal style=3D'text-autospace:none'><span =
style=3D'font-size:10.0pt;
font-family:"Courier New"'><bag name=3D"AccessRoles"
table=3D"ArtefactAccess" order-by=3D"RoleID" <br>
lazy=3D"true"
access=3D"field.camelcase-underscore"
optimistic-lock=3D"false"><br>
<key column=3D"ArtefactID"/><br>
<many-to-many class=3D"Role"
column=3D"RoleID"/><br>
</bag><o:p></o:p></span></p>
<p class=3DMsoNormal =
style=3D'mso-margin-top-alt:5.0pt;margin-right:0cm;margin-bottom:
5.0pt;margin-left:0cm;text-autospace:none'><span =
style=3D'font-size:12.0pt;
font-family:"Times New Roman","serif"'>This all works fine and if I =
delete an
artefact, the association table is cleaned up appropriately and all =
references
between the removed artefact and roles are removed (the role isn't =
deleted
though, correctly - as we don't want orphans =
deleted).<o:p></o:p></span></p>
<p class=3DMsoNormal =
style=3D'mso-margin-top-alt:5.0pt;margin-right:0cm;margin-bottom:
5.0pt;margin-left:0cm;text-autospace:none'><span =
style=3D'font-size:12.0pt;
font-family:"Times New Roman","serif"'>The problem is - how to delete a =
role
and have it clear up the association table automatically. If I presently =
try to
delete a role, I get a reference constraint as there are still entries =
in the
association table for the role. The only way to successfully delete a =
role is to
query for all artefacts that link to that role, remove the role from the
artefact's role collection, update the artefacts and then delete the =
role - not
very efficient or nice, especially when in the un-simplified system, =
roles can
be associated with any number of other =
tables/objects.<o:p></o:p></span></p>
<p class=3DMsoNormal =
style=3D'mso-margin-top-alt:5.0pt;margin-right:0cm;margin-bottom:
5.0pt;margin-left:0cm;text-autospace:none'><span =
style=3D'font-size:12.0pt;
font-family:"Times New Roman","serif"'>I’ve tried adding a =
protected IList
bag to role that links up to artefact’s (even though I don’t =
want
this in my domain model) and set this as the inverse relationship =
(inverse=3D”true”
on the bag) but this doesn’t solve the =
problem.<o:p></o:p></span></p>
<p class=3DMsoNormal =
style=3D'mso-margin-top-alt:5.0pt;margin-right:0cm;margin-bottom:
5.0pt;margin-left:0cm;text-autospace:none'><span =
style=3D'font-size:12.0pt;
font-family:"Times New Roman","serif"'>I need to be able to hint to =
NHibernate
that I want this association table cleared whenever I delete a role - is =
this
possible, and if so - how do I do it?<o:p></o:p></span></p>
<p class=3DMsoNormal =
style=3D'mso-margin-top-alt:5.0pt;margin-right:0cm;margin-bottom:
5.0pt;margin-left:0cm;text-autospace:none'><span =
style=3D'font-size:12.0pt;
font-family:"Times New Roman","serif"'>Thanks for any =
help.<o:p></o:p></span></p>
<p class=3DMsoNormal><o:p> </o:p></p>
</div>
</body>
</html>
------=_NextPart_000_0001_01C9340C.CE100CA0--
--===============8756534649639412805==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
--===============8756534649639412805==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
Nhibernate-development mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/nhibernate-development
--===============8756534649639412805==--