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 is related to DSpace 5. For DSpace 6 see Helper SQL functions for DSpace 6.

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: ds5_bitstream2item(bitstream_id), returns item id (resource_id), works on DSpace 5.x

Example usage: 

SELECT ds5_bitstream2item(123);  -- 456
SELECT ds5_item2collectionhandle(456);  -- '123456789/789'

SELECT ds5_metadata_field2id('dc', 'title');  -- 64
SELECT ds5_metadata_field2id('dc', 'description', 'abstract');  -- 27
SELECT ds5_metadata_id2field(27);  -- (dc,description,abstract)
SELECT (ds5_metadata_id2field(27)).element;  -- 'description'

Overview

argument →
result ↓		bitstreambundleitemcollectioncommunity
bitstream-----
bundleds5_bitstream2bundle----
itemds5_bitstream2item
ds5_bitstream2itemhandle		ds5_bundle2item
ds5_bundle2itemhandle

ds5_item2itemhandle
ds5_itemhandle2item

--
collectionds5_bitstream2collection
ds5_bitstream2collectionhandle		ds5_bundle2collection
ds5_bundle2collectionhandle

ds5_item2collection
ds5_item2collectionhandle
ds5_itemhandle2collection

ds5_itemhandle2collectionhandle		ds5_collection2collectionhandle
ds5_collectionhandle2collection		-
communityds5_bitstream2community
ds5_bitstream2communityhandle		ds5_bundle2community
ds5_bundle2communityhandle

ds5_item2community
ds5_item2communityhandle
ds5_itemhandle2community
ds5_itemhandle2communityhandle

ds5_collection2community
ds5_collection2communityhandle
ds5_collectionhandle2community
ds5_collectionhandle2communityhandle 		ds5_community2communityhandle
ds5_communityhandle2community

Other functions:

ds5_metadata_id2field
ds5_metadata_field2id

Create functions

CREATE OR REPLACE FUNCTION ds5_bitstream2bundle(integer) RETURNS integer
AS 'SELECT bundle_id
FROM bundle2bitstream
WHERE bundle2bitstream.bitstream_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION ds5_bitstream2item(integer) RETURNS integer
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 ds5_bitstream2itemhandle(integer) 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 ds5_bitstream2collection(integer) RETURNS integer
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 ds5_bitstream2collectionhandle(integer) 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 ds5_bitstream2community(integer) RETURNS integer
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 ds5_bitstream2communityhandle(integer) 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 ds5_bundle2item(integer) RETURNS integer
AS 'SELECT item2bundle.item_id
FROM item2bundle
WHERE item2bundle.bundle_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION ds5_bundle2itemhandle(integer) 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 ds5_bundle2collection(integer) RETURNS integer
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 ds5_bundle2collectionhandle(integer) 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 ds5_bundle2community(integer) RETURNS integer
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 ds5_bundle2communityhandle(integer) 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 ds5_item2itemhandle(integer) 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 ds5_itemhandle2item(varchar) RETURNS integer
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 ds5_item2collection(integer) RETURNS integer
AS 'SELECT collection2item.collection_id
FROM collection2item
WHERE collection2item.item_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION ds5_item2collectionhandle(integer) 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 ds5_itemhandle2collection(varchar) RETURNS integer
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 ds5_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 ds5_item2community(integer) RETURNS integer
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 ds5_item2communityhandle(integer) 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 ds5_itemhandle2community(varchar) RETURNS integer
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 ds5_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 ds5_collection2collectionhandle(integer) 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 ds5_collectionhandle2collection(varchar) RETURNS integer
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 ds5_collection2community(integer) RETURNS integer
AS 'SELECT community_id
FROM collection2item, community2collection
WHERE community2collection.collection_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION ds5_collection2communityhandle(integer) 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 ds5_collectionhandle2community(varchar) RETURNS integer
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 ds5_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 ds5_community2communityhandle(integer) 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 ds5_communityhandle2community(varchar) RETURNS integer
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 ds5_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 ds5_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 ds5_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 ds5_bitstream2bundle(integer);
DROP FUNCTION ds5_bitstream2item(integer);
DROP FUNCTION ds5_bitstream2itemhandle(integer);
DROP FUNCTION ds5_bitstream2collection(integer);
DROP FUNCTION ds5_bitstream2collectionhandle(integer);
DROP FUNCTION ds5_bitstream2community(integer);
DROP FUNCTION ds5_bitstream2communityhandle(integer);
DROP FUNCTION ds5_bundle2item(integer);
DROP FUNCTION ds5_bundle2itemhandle(integer);
DROP FUNCTION ds5_bundle2collection(integer);
DROP FUNCTION ds5_bundle2collectionhandle(integer);
DROP FUNCTION ds5_bundle2community(integer);
DROP FUNCTION ds5_bundle2communityhandle(integer);
DROP FUNCTION ds5_item2itemhandle(integer);
DROP FUNCTION ds5_itemhandle2item(varchar);
DROP FUNCTION ds5_item2collection(integer);
DROP FUNCTION ds5_item2collectionhandle(integer);
DROP FUNCTION ds5_itemhandle2collection(varchar);
DROP FUNCTION ds5_itemhandle2collectionhandle(varchar);
DROP FUNCTION ds5_item2community(integer);
DROP FUNCTION ds5_item2communityhandle(integer);
DROP FUNCTION ds5_itemhandle2community(varchar);
DROP FUNCTION ds5_itemhandle2communityhandle(varchar);
DROP FUNCTION ds5_collection2collectionhandle(integer);
DROP FUNCTION ds5_collectionhandle2collection(varchar);
DROP FUNCTION ds5_collection2community(integer);
DROP FUNCTION ds5_collection2communityhandle(integer);
DROP FUNCTION ds5_collectionhandle2community(varchar);
DROP FUNCTION ds5_collectionhandle2communityhandle(varchar);
DROP FUNCTION ds5_community2communityhandle(integer);
DROP FUNCTION ds5_communityhandle2community(varchar);
DROP FUNCTION ds5_metadata_id2field(integer);
DROP FUNCTION ds5_metadata_field2id(varchar, varchar, varchar);
DROP FUNCTION ds5_metadata_field2id(varchar, varchar);

Functions modifying data

-- returns eperson_id or NULL if no such eperson was found
CREATE OR REPLACE FUNCTION ds5_eperson_delete_by_eperson_id(int) RETURNS int
AS $$
BEGIN
  DELETE FROM metadatavalue WHERE resource_type_id = 7 AND resource_id = $1;
  DELETE FROM subscription WHERE eperson_id = $1;
  DELETE FROM epersongroup2eperson WHERE eperson_id = $1;
  DELETE FROM eperson WHERE eperson_id = $1;
  RETURN $1;
END
$$ LANGUAGE plpgsql;

-- returns eperson_id or NULL if no such eperson was found
CREATE OR REPLACE FUNCTION ds5_eperson_delete_by_email(character varying) RETURNS int
AS $$
#print_strict_params on
BEGIN
  RETURN ds5_eperson_delete_by_eperson_id((SELECT eperson_id FROM eperson WHERE email = $1));
END
$$ LANGUAGE plpgsql;


SELECT ds5_eperson_delete_by_email('johndoe@example.com');


DROP FUNCTION ds5_eperson_delete_by_eperson_id(int);
DROP FUNCTION ds5_eperson_delete_by_email(character varying);
  • No labels

All content on the LYRASIS Wiki is licensed under the CC BY (Attribution) license, unless otherwise noted.