It's all about the answers!

Ask a question

Foreign key dependency between repository items


Deepak Azad (211) | asked Jan 31 '08, 9:49 a.m.
JAZZ DEVELOPER
Hi,

I am trying to add a foreign key dependency between items stored in the repository, so that when the referenced item is deleted all its references are also cleaned up.For example, if we have 'user(id,password)' and 'permission(user_id,resource_id)' and a user is deleted, all the permissions referencing the user must also be deleted.

How to go about it?

Thanks
Deepak Azad

3 answers



permanent link
Chris Daly (61651) | answered Jan 31 '08, 1:38 p.m.
JAZZ DEVELOPER
deepak.azad wrote:
I am trying to add a foreign key dependency between items stored in
the repository, so that when the referenced item is deleted all its
references are also cleaned up.For example, if we have
'user(id,password)' and 'permission(user_id,resource_id)' and a user
is deleted, all the permissions referencing the user must also be
deleted.

How to go about it?


There are two ways I can think of doing something like this. First, if
it makes sense to model your dependent type as a Helper instead of an
Item then what you want should happen automatically (because Helpers are
always deleted when their containing Item is deleted).

Second, if you need the dependent type to be an Item then you can create
queries to find the dependents so that they can be deleted at the right
time. To be a bit more specific. You will have written some service
method like deleteUser() which will be making a call to
IRepositoryItemService.deleteItem(). Within this method you would run
your query for dependent permissions and delete them at the same time.
You will probably want to wrap the query and the deletes in a transaction.

I don't think there are any simpler ways of handling deletion of
dependent storage types, but if anybody has other ideas and suggestions,
please chime in.

--
Chris Daly
Jazz Component Development Team

permanent link
Deepak Azad (211) | answered Jan 31 '08, 11:18 p.m.
JAZZ DEVELOPER
Modeling my dependent type as a Helper will not work for me as my dependent type is not contained in an item. In the example stated, user_id attribute of 'Permission' is referencing a 'User' and the 'User' cannot have any reference to 'Permission'.

The second option is also not possible since User has to be completely unaware of 'Permission'.

What I am looking for is a way to add a simple 'foreign key' constraint in Permission table schema in the underlying database, and hence pass all responsibility of cleaning up to the database.

Can this be done? If not are there any plans to provide this feature?

Thanks
Deepak Azad

permanent link
Ritchie Schacher - IBM (47611) | answered Jan 31 '08, 11:58 p.m.
FORUM MODERATOR / JAZZ DEVELOPER
By design we do not create foreign keys in the tables, in order to reduce
contention and deadlocking and for performance. The data integrity is
managed by the application.

It also helps to understand how Jazz persists items in the database. The
item itself is stored in a single table, in a serialized and compressed
form, for efficient loading and saving of items. We call this "document
persistence". Additionally, a subset of the item properties are written to
Object-Relational-Mapped (ORM) tables to support query. Deletes of an item
result in the deletion of rows from both the doc persistence tables and the
ORM tables. Moreover, some of this happens asynchronously to minimize
locking and concurrency problems.

To obtain the cascading delete behavior you desire, you can use Chris's
prior suggestion of finding and removing all the dependent items in the same
transaction as the deletion of the parent. One consideration when taking
such an approach is the number of dependent items to be deleted. For large
numbers, this can lead to locking and concurrency problems between
contending requests. In such a scenario, the pattern that has worked for
other components is to delete the parent item in the request thread, and
delete the dependent items in a scheduled task that runs periodically to
clean up.

--
Ritchie

**********************************************
Ritchie Schacher
Jazz Repository component lead
IBM/Rational RTP Lab
schacher@us.ibm.com
"deepak.azad" <deepak.azad@in.ibm-dot-com.no-spam.invalid> wrote in message
news:fnu747$p4d$1@localhost.localdomain...
Modeling my dependent type as a Helper will not work for me as my
dependent type is not contained in an item. In the example stated,
user_id attribute of 'Permission' is referencing a 'User' and the
'User' cannot have any reference to 'Permission'.

The second option is also not possible since User has to be completely
unaware of 'Permission'.

What I am looking for is a way to add a simple 'foreign key'
constraint in Permission table schema in the underlying database, and
hence pass all responsibility of cleaning up to the database.

Can this be done? If not are there any plans to provide this feature?

Thanks
Deepak Azad

Your answer


Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.