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);
DSpace 5
Overview
argument → result ↓ | bitstream | bundle | item | collection | community |
---|---|---|---|---|---|
bitstream | - | - | - | - | - |
bundle | - | - | - | - | |
item | bitstream2item5 bitstream2itemhandle5 | item2itemhandle5 | - | - | |
collection | item2collection5 | - | |||
community |
Create functions
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 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 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 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 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;
Drop functions
DROP FUNCTION bitstream2item5; DROP FUNCTION bitstream2itemhandle5; DROP FUNCTION item2itemhandle5; DROP FUNCTION item2collection5; DROP FUNCTION item2collectionhandle5; DROP FUNCTION itemhandle2collectionhandle5; DROP FUNCTION itemhandle2item5;