Foreign key dependency between repository items
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
deepak.azad wrote:
I am trying to add a foreign key dependency between items stored in 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 |
|
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 |
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 |
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.