« SQL to find DSPACE collection info given item handle | Main | mismatch between handle table and collection/community tables »

Going from handles to metadata text value in Dspace

Handles that are items

DSPACE tables that map item handles to meta data text

The pdf below shows how the handle table connects to the metadata table for items. Item Handle to MetaData.pdf

sql to pull metadata for items

The query will pull all the metadata for handle value 30308 that is an item (resource_type_id=2).
select text_value from handle, metadatavalue where metadatavalue.item_id=handle.resource_id AND handle.resource_type_id=2 AND handle=30308;

Handles that are collections

DSPACE tables that map collection handles to meta data text

The pdf below shows how the handle table connects to the metadata table for items. Note for this to work we need to go through the collection table.
Collection Handle to MetaData.pdf

sql to pull metadata for collections

THe query below willpull all of the metadata associated with the collection that has a handle of 37244.
select text_value from handle, collection, metadatavalue where metadatavalue.item_id=collection.template_item_id AND collection.collection_id=handle.resource_id AND handle.resource_type_id=3 AND handle=37244;

Handles that are communities

It looks like the communities do not map to meta data fields. The diagram below shows the connection between the handle and the community table, but I do not see how to link to the metadatavalue table.
Community Handle to MetaData.pdf

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)