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: bitstream2item5(bitstream_id), returns item id (resource_id), works on DSpace 5.x
Example usage:
SELECT bitstream2item5(123); -- 456 SELECT (metadata_id2field5(124)).element; -- 'description' SELECT metadata_field2id5('dc', 'description', 'abstract'); -- 124
DSpace 5
Overview
argument → result ↓ | bitstream | bundle | item | collection | community |
---|---|---|---|---|---|
bitstream | - | - | - | - | - |
bundle | bitstream2bundle5 | - | - | - | - |
item | bitstream2item5 bitstream2itemhandle5 | bundle2item5 bundle2itemhandle5 | item2itemhandle5 | - | - |
collection | bitstream2collection5 bitstream2collectionhandle5 | bundle2collection5 bundle2collectionhandle5 | item2collection5 | collection2collectionhandle5 collectionhandle2collection5 | - |
community | bitstream2community5 bitstream2communityhandle5 | bundle2community5 bundle2communityhandle5 | item2community5 | collection2community5 collection2communityhandle5 collectionhandle2community5 collectionhandle2communityhandle5 | community2communityhandle5 communityhandle2community5 |
Other functions:
metadata_id2field5
metadata_field2id5
Create functions
CREATE OR REPLACE FUNCTION bitstream2bundle5(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 bitstream2item5(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 bitstream2itemhandle5(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 bitstream2collection5(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 bitstream2collectionhandle5(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 bitstream2community5(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 bitstream2communityhandle5(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 bundle2item5(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 bundle2itemhandle5(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 bundle2collection5(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 bundle2collectionhandle5(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 bundle2community5(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 bundle2communityhandle5(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 item2itemhandle5(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 itemhandle2item5(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 item2collection5(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 item2collectionhandle5(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 itemhandle2collection5(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 itemhandle2collectionhandle5(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(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 item2communityhandle5(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 itemhandle2community5(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 itemhandle2communityhandle5(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 collection2collectionhandle5(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 collectionhandle2collection5(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 collection2community5(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 collection2communityhandle5(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 collectionhandle2community5(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 collectionhandle2communityhandle5(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 community2communityhandle5(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 communityhandle2community5(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 metadata_id2field5(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 metadata_field2id5(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;
Drop functions
DROP FUNCTION bitstream2bundle5(); DROP FUNCTION bitstream2item5(); DROP FUNCTION bitstream2itemhandle5(); DROP FUNCTION bitstream2collection5(); DROP FUNCTION bitstream2collectionhandle5(); DROP FUNCTION bitstream2community5(); DROP FUNCTION bitstream2communityhandle5(); DROP FUNCTION bundle2item5(); DROP FUNCTION bundle2itemhandle5(); DROP FUNCTION bundle2collection5(); DROP FUNCTION bundle2collectionhandle5(); DROP FUNCTION bundle2community5(); DROP FUNCTION bundle2communityhandle5(); DROP FUNCTION item2itemhandle5(); DROP FUNCTION itemhandle2item5(); DROP FUNCTION item2collection5(); DROP FUNCTION item2collectionhandle5(); DROP FUNCTION itemhandle2collection5(); DROP FUNCTION itemhandle2collectionhandle5(); DROP FUNCTION item2community5(); DROP FUNCTION item2communityhandle5(); DROP FUNCTION itemhandle2community5(); DROP FUNCTION itemhandle2communityhandle5(); DROP FUNCTION collection2collectionhandle5(); DROP FUNCTION collectionhandle2collection5(); DROP FUNCTION collection2community5(); DROP FUNCTION collection2communityhandle5(); DROP FUNCTION collectionhandle2community5(); DROP FUNCTION collectionhandle2communityhandle5(); DROP FUNCTION community2communityhandle5(); DROP FUNCTION communityhandle2community5(); DROP FUNCTION metadata_id2field5(); DROP FUNCTION metadata_field2id5();