Page History

...

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

...

Code Block

language	sql

SELECT bitstream2item5ds5_bitstream2item(123);  -- 456
SELECT item2collectionhandle5ds5_item2collectionhandle(456);  -- '123456789/789'

SELECT ds5_metadata_field2id5field2id('dc', 'title');  -- 64
SELECT ds5_metadata_field2id5field2id('dc', 'description', 'abstract');  -- 27
SELECT ds5_metadata_id2field5id2field(27);  -- (dc,description,abstract)
SELECT (ds5_metadata_id2field5id2field(27)).element;  -- 'description'

...

Overview

argument → result ↓	bitstream	bundle	item	collection	community
bitstream	-	-	-	-	-
bundlebitstream2bundle5	ds5_bitstream2bundle	-	-	-	-
item	bitstream2item5 bitstream2itemhandle5	bundle2item5 bundle2itemhandle5	ds5_bitstream2item ds5_bitstream2itemhandle	ds5_bundle2item ds5_bundle2itemhandle	ds5_item2itemhandle ds5_itemhandle2itemitem2itemhandle5 itemhandle2item5	-	-
collection	bitstream2collection5 bitstream2collectionhandle5	bundle2collection5 bundle2collectionhandle5	item2collection5 item2collectionhandle5 itemhandle2collection5 itemhandle2collectionhandle5	ds5_bitstream2collection ds5_bitstream2collectionhandle	ds5_bundle2collection ds5_bundle2collectionhandle	ds5_item2collection ds5_item2collectionhandle ds5_itemhandle2collection ds5_itemhandle2collectionhandle	ds5_collection2collectionhandle ds5_collectionhandle2collectioncollection2collectionhandle5 collectionhandle2collection5	-
community	bitstream2community5 bitstream2communityhandle5	bundle2community5 bundle2communityhandle5	item2community5 item2communityhandle5 itemhandle2community5 itemhandle2communityhandle5	collection2community5 collection2communityhandle5 collectionhandle2community5 collectionhandle2communityhandle5	ds5_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_communityhandle2communitycommunity2communityhandle5 communityhandle2community5

Other functions:

ds5_metadata_id2field5id2field
ds5_metadata_field2id5field2id

Create functions

Code Block

language	sql

CREATE OR REPLACE FUNCTION bitstream2bundle5ds5_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 bitstream2item5ds5_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 bitstream2itemhandle5ds5_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 bitstream2collection5ds5_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 bitstream2collectionhandle5ds5_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 bitstream2community5ds5_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 bitstream2communityhandle5ds5_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 bundle2item5ds5_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 bundle2itemhandle5ds5_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 bundle2collection5ds5_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 bundle2collectionhandle5ds5_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 bundle2community5ds5_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 bundle2communityhandle5ds5_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 item2itemhandle5ds5_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 itemhandle2item5ds5_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 item2collection5ds5_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 item2collectionhandle5ds5_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 itemhandle2collection5ds5_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 itemhandle2collectionhandle5ds5_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 item2community5 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 item2communityhandle5ds5_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 itemhandle2community5ds5_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 itemhandle2communityhandle5ds5_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 collection2collectionhandle5ds5_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 collectionhandle2collection5ds5_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 collection2community5ds5_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 collection2communityhandle5ds5_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 collectionhandle2community5ds5_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 collectionhandle2communityhandle5ds5_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 community2communityhandle5ds5_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 communityhandle2community5ds5_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_id2field5id2field(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_field2id5field2id(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_field2id5field2id(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;

...

Code Block

language	sql

DROP FUNCTION bitstream2bundle5ds5_bitstream2bundle(integer);
DROP FUNCTION bitstream2item5ds5_bitstream2item(integer);
DROP FUNCTION bitstream2itemhandle5ds5_bitstream2itemhandle(integer);
DROP FUNCTION bitstream2collection5ds5_bitstream2collection(integer);
DROP FUNCTION bitstream2collectionhandle5ds5_bitstream2collectionhandle(integer);
DROP FUNCTION bitstream2community5ds5_bitstream2community(integer);
DROP FUNCTION bitstream2communityhandle5ds5_bitstream2communityhandle(integer);
DROP FUNCTION bundle2item5ds5_bundle2item(integer);
DROP FUNCTION bundle2itemhandle5ds5_bundle2itemhandle(integer);
DROP FUNCTION bundle2collection5ds5_bundle2collection(integer);
DROP FUNCTION bundle2collectionhandle5ds5_bundle2collectionhandle(integer);
DROP FUNCTION bundle2community5ds5_bundle2community(integer);
DROP FUNCTION bundle2communityhandle5ds5_bundle2communityhandle(integer);
DROP FUNCTION item2itemhandle5ds5_item2itemhandle(integer);
DROP FUNCTION itemhandle2item5ds5_itemhandle2item(varchar);
DROP FUNCTION item2collection5ds5_item2collection(integer);
DROP FUNCTION item2collectionhandle5ds5_item2collectionhandle(integer);
DROP FUNCTION itemhandle2collection5ds5_itemhandle2collection(varchar);
DROP FUNCTION itemhandle2collectionhandle5ds5_itemhandle2collectionhandle(varchar);
DROP FUNCTION item2community5ds5_item2community(integer);
DROP FUNCTION item2communityhandle5ds5_item2communityhandle(integer);
DROP FUNCTION itemhandle2community5ds5_itemhandle2community(varchar);
DROP FUNCTION itemhandle2communityhandle5ds5_itemhandle2communityhandle(varchar);
DROP FUNCTION collection2collectionhandle5ds5_collection2collectionhandle(integer);
DROP FUNCTION collectionhandle2collection5ds5_collectionhandle2collection(varchar);
DROP FUNCTION collection2community5ds5_collection2community(integer);
DROP FUNCTION collection2communityhandle5ds5_collection2communityhandle(integer);
DROP FUNCTION collectionhandle2community5ds5_collectionhandle2community(varchar);
DROP FUNCTION collectionhandle2communityhandle5ds5_collectionhandle2communityhandle(varchar);
DROP FUNCTION community2communityhandle5ds5_community2communityhandle(integer);
DROP FUNCTION communityhandle2community5ds5_communityhandle2community(varchar);
DROP FUNCTION ds5_metadata_id2field5id2field(integer);
DROP FUNCTION ds5_metadata_field2id5field2id(varchar, varchar, varchar);
DROP FUNCTION ds5_metadata_field2id5field2id(varchar, varchar);

Page tree

Versions Compared

Old Version 13

New Version 14

Key

Overview

Create functions