Problem description
Let assume you have created a number of Ad Hoc views and Domain JRXML reports using some "New_Domain". You store them in different repository folders. When you try deleting "New_Domain" you will get warning message that prevent you from deleting this domain:
"The selected item(s) cannot be removed since they are referenced by other objects"
Solution
It is possible to determine repository resources you need to delete in order to be able to delete affected Domain by running query on Repository Database.
- Open log file (./jasperserver-pro/WEB-INF/logs/jasperserver.log) on back-end to determine key ID you will use in SQL query later. Here is an example of stack-trace:
2014-01-09 18:20:18,691 ERROR JDBCExceptionReporter,http-8080-8:101 - ERROR: update or delete on table "jiresource" violates foreign key constraint "fk200a2ac9324cfecb" on table "jiadhocdataview" Detail: Key (id)=(16461) is still referenced from table "jiadhocdataview".
- Update query below with key ID found in the log file:
select jiresource.label as "dependant_resource_label", jiresource.resourcetype, jiresourcefolder.uri as "parent_folder_path", jiresourcefolder.name as "parent_folder_name" from jiresource left outer join jiresourcefolder ON (jiresource.parent_folder = jiresourcefolder.id) where jiresource.id IN (select id from jireportunit where reportdatasource='16461' union select id from jiadhocdataview where reportdatasource='16461');
In my case, I have one Domain report and two Ad Hoc Views that use "New_Domain":
dependant_resource_label | resourcetype | parent_folder_path | parent_folder_name -------------------------+--------------------------------------------------------------------------+---------------------------------------+-------------------- table_domain | com.jaspersoft.jasperserver.api.metadata.jasperreports.domain.ReportUnit | /public/cases/Domain_report | Domain_report Ad Hoc View | com.jaspersoft.ji.adhoc.AdhocDataView | /organizations | organizations Cars owned by account | com.jaspersoft.ji.adhoc.AdhocDataView | /organizations/organization_1/reports | reports (3 rows)
After deleting those resources I was able to delete "New_Domain".
Limitations
This solution has been tested only with Postgres 9.1 repository DB.
This will not work with Oracle repository DB (looks like Hibernate doesn't provide key ID in stack trace). This might work with MySQL, MS SQL etc but you will have to update SQL accordingly.
Recommended Comments