Page History

...

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+):

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':

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:

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:

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

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".

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

• Metadata for all DSpace objects
• Storage Layer#RDBMS/DatabaseStructure
• Metadata and Bitstream Format Registries
• Helper SQL functions for DSpace 6

...