Skip to content

PSQL Queries for DSpace

Jump to bottom
Hui Zhang edited this page May 16, 2017 · 1 revision

Using batch DSpace metadata editing tool

  • export metadata of one Dspace collection:

/bin/dspace metadata-export -f /path_to_dir/export_name.csv -i /collection/handle

  • export metadata of all items in the repository

/bin/dspace metadata-export -f /path_to_dir/export_name.csv

Using postgres queries

  • handle2bitstream

SELECT bitstream.bitstream_id, bitstream.sequence_id, item.item_id, item.owning_collection, handle.handle, handle.resource_type_id FROM handle INNER JOIN item ON item.item_id = handle.resource_id AND handle.resource_type_id = 2 INNER JOIN item2bundle ON item2bundle.item_id = item.item_id INNER JOIN bundle2bitstream ON bundle2bitstream.bundle_id = item2bundle.bundle_id INNER JOIN bitstream ON bitstream.bitstream_id = bundle2bitstream.bitstream_id;

  • handle2item

SELECT item.item_id, item.owning_collection, handle.handle, handle.resource_type_id FROM handle INNER JOIN item ON item.item_id = handle.resource_id AND handle.resource_type_id = 2;

  • community2collection

SELECT community.community_id, community.name, collection.collection_id, collection.name FROM community, collection, community2collection WHERE community2collection.community_id = community.community_id AND community2collection.collection_id = collection.collection_id;

  • item,bitstream,collection for one community

SELECT handle.handle, bitstream.bitstream_id, bitstream.name, bitstream.size_bytes, item.item_id, community2collection.collection_id, item.owning_collection FROM handle, item, item2bundle, bundle2bitstream, bitstream, community2collection WHERE handle.resource_id = item.item_id AND handle.resource_type_id = 2 AND item.item_id = item2bundle.item_id AND item2bundle.bundle_id = bundle2bitstream.bundle_id AND bundle2bitstream.bitstream_id = bitstream.bitstream_id AND community2collection.community_id = 5 AND community2collection.collection_id = item.owning_collection;

Clone this wiki locally