Contribute to the DSpace Development Fund
The newly established DSpace Development Fund supports the development of new features prioritized by DSpace Governance. For a list of planned features see the fund wiki page.
This page was adapted for DSpace 6 based on the DSpace 5 version. Not all functions have been tested. If you run into a problem, please leave a comment.
This page is related to DSpace 6. For DSpace 5 see Helper SQL functions for DSpace 5.
If you are making custom SQL queries against the DSpace database, they can get very complex due to numerous joins between various levels of DSpace objects (bitstream, bundle, item, collection, community). These helper functions contain the joins so you don't have to write them every time. In other words, using one of these functions in your query saves you from typing the body of the function in your query.
Naming convention: ds6_bitstream2item(bitstream_id), returns item id (resource_id / UUID), works on DSpace 6.x
Example usage:
SELECT ds6_bitstream2item(123); -- 456 SELECT ds6_item2collectionhandle(456); -- '123456789/789' SELECT ds6_metadata_field2id('dc', 'title'); -- 64 SELECT ds6_metadata_field2id('dc', 'description', 'abstract'); -- 27 SELECT ds6_metadata_id2field(27); -- (dc,description,abstract) SELECT (ds6_metadata_id2field(27)).element; -- 'description'
Overview
argument → result ↓ | bitstream | bundle | item | collection | community |
---|---|---|---|---|---|
bitstream | - | - | - | - | - |
bundle | ds6_bitstream2bundle | - | - | - | - |
item | ds6_bitstream2item ds6_bitstream2itemhandle | ds6_bundle2item ds6_bundle2itemhandle | ds6_item2itemhandle | - | - |
collection | ds6_bitstream2collection ds6_bitstream2collectionhandle | ds6_bundle2collection ds6_bundle2collectionhandle | ds6_item2collection | ds6_collection2collectionhandle ds6_collectionhandle2collection | - |
community | ds6_bitstream2community ds6_bitstream2communityhandle | ds6_bundle2community ds6_bundle2communityhandle | ds6_item2community | ds6_collection2community ds6_collection2communityhandle ds6_collectionhandle2community ds6_collectionhandle2communityhandle | ds6_community2communityhandle ds6_communityhandle2community |
Other functions:
ds6_metadata_id2field
ds6_metadata_field2id
Create functions
CREATE OR REPLACE FUNCTION ds6_bitstream2bundle(uuid) RETURNS uuid AS 'SELECT bundle_id FROM bundle2bitstream WHERE bundle2bitstream.bitstream_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_bitstream2item(uuid) RETURNS uuid AS 'SELECT item2bundle.item_id FROM bundle2bitstream, item2bundle WHERE item2bundle.bundle_id = bundle2bitstream.bundle_id AND bundle2bitstream.bitstream_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_bitstream2itemhandle(uuid) RETURNS varchar AS 'SELECT handle FROM bundle2bitstream, item2bundle, handle WHERE handle.resource_type_id = 2 AND handle.resource_id = item2bundle.item_id AND item2bundle.bundle_id = bundle2bitstream.bundle_id AND bundle2bitstream.bitstream_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_bitstream2collection(uuid) RETURNS uuid AS 'SELECT collection2item.collection_id FROM bundle2bitstream, item2bundle, collection2item WHERE collection2item.item_id = item2bundle.item_id AND item2bundle.bundle_id = bundle2bitstream.bundle_id AND bundle2bitstream.bitstream_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_bitstream2collectionhandle(uuid) RETURNS varchar AS 'SELECT handle FROM bundle2bitstream, item2bundle, collection2item, handle WHERE handle.resource_type_id = 3 AND handle.resource_id = collection2item.collection_id AND collection2item.item_id = item2bundle.item_id AND item2bundle.bundle_id = bundle2bitstream.bundle_id AND bundle2bitstream.bitstream_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_bitstream2community(uuid) RETURNS uuid AS 'SELECT community2collection.community_id FROM bundle2bitstream, item2bundle, collection2item, community2collection WHERE community2collection.collection_id = collection2item.collection_id AND collection2item.item_id = item2bundle.item_id AND item2bundle.bundle_id = bundle2bitstream.bundle_id AND bundle2bitstream.bitstream_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_bitstream2communityhandle(uuid) RETURNS varchar AS 'SELECT handle FROM bundle2bitstream, item2bundle, collection2item, community2collection, handle WHERE handle.resource_type_id = 4 AND handle.resource_id = community2collection.community_id AND community2collection.collection_id = collection2item.collection_id AND collection2item.item_id = item2bundle.item_id AND item2bundle.bundle_id = bundle2bitstream.bundle_id AND bundle2bitstream.bitstream_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_bundle2item(uuid) RETURNS uuid AS 'SELECT item2bundle.item_id FROM item2bundle WHERE item2bundle.bundle_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_bundle2itemhandle(uuid) RETURNS varchar AS 'SELECT handle FROM item2bundle, handle WHERE handle.resource_type_id = 2 AND handle.resource_id = item2bundle.item_id AND item2bundle.bundle_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_bundle2collection(uuid) RETURNS uuid AS 'SELECT collection2item.collection_id FROM item2bundle, collection2item WHERE collection2item.item_id = item2bundle.item_id AND item2bundle.bundle_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_bundle2collectionhandle(uuid) RETURNS varchar AS 'SELECT handle FROM item2bundle, collection2item, handle WHERE handle.resource_type_id = 3 AND handle.resource_id = collection2item.collection_id AND collection2item.item_id = item2bundle.item_id AND item2bundle.bundle_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_bundle2community(uuid) RETURNS uuid AS 'SELECT community2collection.community_id FROM item2bundle, collection2item, community2collection WHERE community2collection.collection_id = collection2item.collection_id AND collection2item.item_id = item2bundle.item_id AND item2bundle.bundle_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_bundle2communityhandle(uuid) RETURNS varchar AS 'SELECT handle FROM item2bundle, collection2item, community2collection, handle WHERE handle.resource_type_id = 4 AND handle.resource_id = community2collection.community_id AND community2collection.collection_id = collection2item.collection_id AND collection2item.item_id = item2bundle.item_id AND item2bundle.bundle_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_item2itemhandle(uuid) RETURNS varchar AS 'SELECT handle FROM handle WHERE handle.resource_type_id = 2 AND handle.resource_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_itemhandle2item(varchar) RETURNS uuid AS 'SELECT resource_id FROM handle WHERE handle.resource_type_id = 2 AND handle = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_item2collection(uuid) RETURNS uuid AS 'SELECT collection2item.collection_id FROM collection2item WHERE collection2item.item_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_item2collectionhandle(uuid) RETURNS varchar AS 'SELECT handle FROM collection2item, handle WHERE handle.resource_type_id = 3 AND handle.resource_id = collection2item.collection_id AND collection2item.item_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_itemhandle2collection(varchar) RETURNS uuid AS 'SELECT collection_id FROM collection2item WHERE collection2item.item_id = ( SELECT resource_id FROM handle WHERE handle.resource_type_id = 2 AND handle = $1 )' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_itemhandle2collectionhandle(varchar) RETURNS varchar AS 'SELECT handle FROM collection2item, handle WHERE handle.resource_type_id = 3 AND handle.resource_id = collection2item.collection_id AND collection2item.item_id = ( SELECT resource_id FROM handle WHERE handle.resource_type_id = 2 AND handle = $1 )' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_item2community(uuid) RETURNS uuid AS 'SELECT community_id FROM collection2item, community2collection WHERE community2collection.collection_id = collection2item.collection_id AND collection2item.item_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_item2communityhandle(uuid) RETURNS varchar AS 'SELECT handle FROM collection2item, community2collection, handle WHERE handle.resource_type_id = 4 AND handle.resource_id = community2collection.community_id AND community2collection.collection_id = collection2item.collection_id AND collection2item.item_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_itemhandle2community(varchar) RETURNS uuid AS 'SELECT community_id FROM collection2item, community2collection WHERE community2collection.collection_id = collection2item.collection_id AND collection2item.item_id = ( SELECT resource_id FROM handle WHERE handle.resource_type_id = 2 AND handle = $1 )' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_itemhandle2communityhandle(varchar) RETURNS varchar AS 'SELECT handle FROM collection2item, community2collection, handle WHERE handle.resource_type_id = 4 AND handle.resource_id = community2collection.community_id AND community2collection.collection_id = collection2item.collection_id AND collection2item.item_id = ( SELECT resource_id FROM handle WHERE handle.resource_type_id = 2 AND handle = $1 )' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_collection2collectionhandle(uuid) RETURNS varchar AS 'SELECT handle FROM handle WHERE handle.resource_type_id = 3 AND handle.resource_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_collectionhandle2collection(varchar) RETURNS uuid AS 'SELECT resource_id FROM handle WHERE handle.resource_type_id = 3 AND handle = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_collection2community(uuid) RETURNS uuid AS 'SELECT community_id FROM collection2item, community2collection WHERE community2collection.collection_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_collection2communityhandle(uuid) RETURNS varchar AS 'SELECT handle FROM collection2item, community2collection, handle WHERE handle.resource_type_id = 4 AND handle.resource_id = community2collection.community_id AND community2collection.collection_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_collectionhandle2community(varchar) RETURNS uuid AS 'SELECT community_id FROM collection2item, community2collection WHERE community2collection.collection_id = ( SELECT resource_id FROM handle WHERE handle.resource_type_id = 3 AND handle = $1 )' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_collectionhandle2communityhandle(varchar) RETURNS varchar AS 'SELECT handle FROM collection2item, community2collection, handle WHERE handle.resource_type_id = 4 AND handle.resource_id = community2collection.community_id AND community2collection.collection_id = ( SELECT resource_id FROM handle WHERE handle.resource_type_id = 3 AND handle = $1 )' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_community2communityhandle(uuid) RETURNS varchar AS 'SELECT handle FROM handle WHERE handle.resource_type_id = 4 AND handle.resource_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_communityhandle2community(varchar) RETURNS uuid AS 'SELECT resource_id FROM handle WHERE handle.resource_type_id = 4 AND handle = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_metadata_id2field(integer) RETURNS TABLE (schema varchar, element varchar, qualifier varchar) AS 'SELECT short_id AS schema, element, qualifier FROM metadatafieldregistry, metadataschemaregistry WHERE metadatafieldregistry.metadata_schema_id = metadataschemaregistry.metadata_schema_id AND metadata_field_id = $1' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_metadata_field2id(varchar, varchar, varchar) RETURNS integer AS 'SELECT metadata_field_id FROM metadatafieldregistry, metadataschemaregistry WHERE metadatafieldregistry.metadata_schema_id = metadataschemaregistry.metadata_schema_id AND short_id = $1 AND element = $2 AND qualifier = $3' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION ds6_metadata_field2id(varchar, varchar) RETURNS integer AS 'SELECT metadata_field_id FROM metadatafieldregistry, metadataschemaregistry WHERE metadatafieldregistry.metadata_schema_id = metadataschemaregistry.metadata_schema_id AND short_id = $1 AND element = $2 AND qualifier IS NULL' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
Drop functions
DROP FUNCTION ds6_bitstream2bundle(uuid); DROP FUNCTION ds6_bitstream2item(uuid); DROP FUNCTION ds6_bitstream2itemhandle(uuid); DROP FUNCTION ds6_bitstream2collection(uuid); DROP FUNCTION ds6_bitstream2collectionhandle(uuid); DROP FUNCTION ds6_bitstream2community(uuid); DROP FUNCTION ds6_bitstream2communityhandle(uuid); DROP FUNCTION ds6_bundle2item(uuid); DROP FUNCTION ds6_bundle2itemhandle(uuid); DROP FUNCTION ds6_bundle2collection(uuid); DROP FUNCTION ds6_bundle2collectionhandle(uuid); DROP FUNCTION ds6_bundle2community(uuid); DROP FUNCTION ds6_bundle2communityhandle(uuid); DROP FUNCTION ds6_item2itemhandle(uuid); DROP FUNCTION ds6_itemhandle2item(varchar); DROP FUNCTION ds6_item2collection(uuid); DROP FUNCTION ds6_item2collectionhandle(uuid); DROP FUNCTION ds6_itemhandle2collection(varchar); DROP FUNCTION ds6_itemhandle2collectionhandle(varchar); DROP FUNCTION ds6_item2community(uuid); DROP FUNCTION ds6_item2communityhandle(uuid); DROP FUNCTION ds6_itemhandle2community(varchar); DROP FUNCTION ds6_itemhandle2communityhandle(varchar); DROP FUNCTION ds6_collection2collectionhandle(uuid); DROP FUNCTION ds6_collectionhandle2collection(varchar); DROP FUNCTION ds6_collection2community(uuid); DROP FUNCTION ds6_collection2communityhandle(uuid); DROP FUNCTION ds6_collectionhandle2community(varchar); DROP FUNCTION ds6_collectionhandle2communityhandle(varchar); DROP FUNCTION ds6_community2communityhandle(uuid); DROP FUNCTION ds6_communityhandle2community(varchar); DROP FUNCTION ds6_metadata_id2field(integer); DROP FUNCTION ds6_metadata_field2id(varchar, varchar, varchar); DROP FUNCTION ds6_metadata_field2id(varchar, varchar);