Page History

SELECT bitstream2item5(123);  -- 456
SELECT item2collectionhandle5(456);  -- '123456789/789'

SELECT metadata_field2id5('dc', 'title');  -- 64
SELECT metadata_field2id5('dc', 'description', 'abstract');  -- 27
SELECT metadata_id2field5(27);  -- (dc,description,abstract)
SELECT (metadata_id2field5(27)).element;  -- 'description'

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;
 
CREATE OR REPLACE FUNCTION metadata_field2id5(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 FUNCTION bitstream2bundle5(integer);
DROP FUNCTION bitstream2item5(integer);
DROP FUNCTION bitstream2itemhandle5(integer);
DROP FUNCTION bitstream2collection5(integer);
DROP FUNCTION bitstream2collectionhandle5(integer);
DROP FUNCTION bitstream2community5(integer);
DROP FUNCTION bitstream2communityhandle5(integer);
DROP FUNCTION bundle2item5(integer);
DROP FUNCTION bundle2itemhandle5(integer);
DROP FUNCTION bundle2collection5(integer);
DROP FUNCTION bundle2collectionhandle5(integer);
DROP FUNCTION bundle2community5(integer);
DROP FUNCTION bundle2communityhandle5(integer);
DROP FUNCTION item2itemhandle5(integer);
DROP FUNCTION itemhandle2item5(varchar);
DROP FUNCTION item2collection5(integer);
DROP FUNCTION item2collectionhandle5(integer);
DROP FUNCTION itemhandle2collection5(varchar);
DROP FUNCTION itemhandle2collectionhandle5(varchar);
DROP FUNCTION item2community5(integer);
DROP FUNCTION item2communityhandle5(integer);
DROP FUNCTION itemhandle2community5(varchar);
DROP FUNCTION itemhandle2communityhandle5(varchar);
DROP FUNCTION collection2collectionhandle5(integer);
DROP FUNCTION collectionhandle2collection5(varchar);
DROP FUNCTION collection2community5(integer);
DROP FUNCTION collection2communityhandle5(integer);
DROP FUNCTION collectionhandle2community5(varchar);
DROP FUNCTION collectionhandle2communityhandle5(varchar);
DROP FUNCTION community2communityhandle5(integer);
DROP FUNCTION communityhandle2community5(varchar);
DROP FUNCTION metadata_id2field5(integer);
DROP FUNCTION metadata_field2id5(varchar, varchar, varchar);
DROP FUNCTION metadata_field2id5(varchar, varchar);