...
The metadatavalue
table is an important table in DSpace because it holds the metadata values of DSpace objects (ie. article title, authors, identifiers). The table uses the so-called EAV (Entity-attribute-value) model as further explained in this article.
...
The typical operation is to look up a certain metadata value for a DSpace object with a given ID (DSpace 6+ uses a UUID in DSpace 5+the dspace_object_id
column, DSpace 5 uses an integer in the resource_id
column). First, we find the metadata field in the metadata registry. Say, we're looking for the metadata value (the list of authors) of an item (resource_type_id = 2) with a given UUID ID (dspace_object_id='f2cf9909-0357-4037-8305-4a426bf9a826') where the metadata field is "dc.contributor.author". First, we look up the ID of the "dc" schema in the metadataschemaregistry
table (example code for DSpace 6+):
Code Block |
---|
|
SELECT metadata_schema_id
FROM metadataschemaregistry
WHERE short_id = 'dc'; |
This gives us metadata_schema_id
of "1". Next, we look up the metadata field "dc.contributor.author" in the metadatafieldregistry
table. We use the metadata_schema_id of the "dc" schema we just found, we query for element = 'contributor' and qualifier = 'author':
Code Block |
---|
|
SELECT metadata_field_id
FROM metadatafieldregistry
WHERE metadata_schema_id = 1
AND element = 'contributor'
AND qualifier = 'author'; |
This gives us the metadatathe metadata_field_id
value of "9". Finally, we can use this value to ask for the metadata field containing authors in the metadatavalue
table:
Code Block |
---|
|
SELECT text_value
FROM metadatavalue
WHERE metadata_field_id = '9'
AND dspace_object_id='f2cf9909-0357-4037-8305-4a426bf9a826'; |
The same, written as a single query:
Code Block |
---|
|
SELECT text_value
FROM metadatavalue
WHERE metadata_field_id = (
SELECT metadata_field_id
FROM metadatafieldregistry, metadataschemaregistry
WHERE metadatafieldregistry.metadata_schema_id = metadataschemaregistry.metadata_schema_id
AND short_id = 'dc'
AND element = 'contributor'
AND qualifier = 'author'
)
AND dspace_object_id='f2cf9909-0357-4037-8305-4a426bf9a826'; |
...
If you use these queries often and in an ad-hoc manner, you may find it helpful to use these snippets of SQL as functions. You can find the definitions here: Helper SQL functions for DSpace 6
Code Block |
---|
|
SELECT text_value
FROM metadatavalue
WHERE metadata_field_id = ds6_metadata_field2id('contributor', 'author')
AND dspace_object_id='f2cf9909-0357-4037-8305-4a426bf9a826'; |
Example
The following query selects the titles and handles of collections whose titles end with "Research".
Code Block |
---|
language | sql |
---|
title | Example 1 |
---|
|
SELECT metadatavalue.text_value, handle.handle
FROM collection
INNER JOIN metadatavalue ON collection.uuid = metadatavalue.dspace_object_id
INNER JOIN handle ON collection.uuid = handle.resource_id
WHERE metadatavalue.metadata_field_id = 64
AND metadatavalue.text_value LIKE '%Research'; |
See also
...