SUNScholar/Embargo Systems/Reports

From Libopedia
Jump to navigation Jump to search
Back to Embargo Systems

Resource Policies Report

Querying the database directly

For DSpace => 3.X

Please refer to: https://groups.google.com/forum/#!topic/dspace-tech/VyzAKZqc4m8

Connect to the database and then execute the following SQL query;

SELECT h.handle, rp.*
FROM resourcepolicy rp, bundle2bitstream b2b, item2bundle i2b, handle h
WHERE (start_date IS NOT NULL OR end_date IS NOT NULL)
AND rp.resource_type_id = 0
AND b2b.bitstream_id = rp.resource_id
AND i2b.bundle_id = b2b.bundle_id
AND i2b.item_id = h.resource_id
AND h.resource_type_id = 2;

For DSpace <= 1.X.X

Connect to the database and then execute the following SQL queries;

SELECT METADATA_FIELD_ID FROM METADATAFIELDREGISTRY WHERE ELEMENT LIKE 'embargo';

then find the item handles: (here done assuming the metadata field id is 81)

SELECT * FROM HANDLE WHERE RESOURCE_TYPE_ID = 2 AND RESOURCE_ID IN (SELECT RESOURCE_ID FROM METADATAVALUE WHERE METADATA_FIELD_ID = 81);