DSpace SQL Snippets

Posted On June 3, 2014

Here are some SQL queries you can copy and paste into DSpace.

Select/update the access policy to Anonymous for all thumbnails that have their group set as 2 (e.g. anu-archives) :

UPDATE resourcepolicy
SET    epersongroup_id = 0
--select count('x') from resourcepolicy
WHERE  epersongroup_id = 2
       AND policy_id IN (SELECT rp.policy_id
                         FROM   resourcepolicy rp,
                                bundle2bitstream bb,
                                bundle b
                         WHERE  b.NAME = 'THUMBNAIL'
                                AND b.bundle_id = bb.bundle_id
                                AND bb.bitstream_id = rp.resource_id);

UPDATE resourcepolicy

SET epersongroup_id = 0

--select count('x') from resourcepolicy

WHERE epersongroup_id = 2

AND policy_id IN (SELECT rp.policy_id

FROM resourcepolicy rp,

bundle2bitstream bb,

bundle b

WHERE b.NAME = 'THUMBNAIL'

AND b.bundle_id = bb.bundle_id

AND bb.bitstream_id = rp.resource_id);

Or do an individual one by adding:

and rp.policy_id=1234567

1	and rp.policy_id=1234567

where that ID is found on the authorisations page in the Full Item Record view.

Change the above to BRANDED_PREVIEW to affect the permissions on those as well.

Remove this SQL from the above to affect ALL thumbnails/previews, not just those in group

Find all items that have their original bitstream open to the public:

SELECT bits.name,
       c.name,
       h.handle
FROM   bitstream bits,
       bundle2bitstream b2b,
       bundle buns,
       resourcepolicy rp,
       item2bundle i2b,
       item i,
       collection2item c2i,
       collection c,
       handle h
WHERE  bits.bitstream_id = b2b.bitstream_id
       AND buns.bundle_id = b2b.bundle_id
       AND rp.resource_id = b2b.bitstream_id
       AND i2b.bundle_id = b2b.bundle_id
       AND i.item_id = i2b.item_id
       AND c2i.collection_id = i.owning_collection
       AND c.collection_id = c2i.collection_id
       AND buns.name = 'ORIGINAL'
       AND rp.epersongroup_id = '0'
       AND h.resource_id = buns.bundle_id
LIMIT  1000000

SELECT bits.name,

c.name,

h.handle

FROM bitstream bits,

bundle2bitstream b2b,

bundle buns,

resourcepolicy rp,

item2bundle i2b,

item i,

collection2item c2i,

collection c,

handle h

WHERE bits.bitstream_id = b2b.bitstream_id

AND buns.bundle_id = b2b.bundle_id

AND rp.resource_id = b2b.bitstream_id

AND i2b.bundle_id = b2b.bundle_id

AND i.item_id = i2b.item_id

AND c2i.collection_id = i.owning_collection

AND c.collection_id = c2i.collection_id

AND buns.name = 'ORIGINAL'

AND rp.epersongroup_id = '0'

AND h.resource_id = buns.bundle_id

LIMIT 1000000

NB. This needs tweaking:

Using subselects to filter results for various conditions:

This SQL selects the relation.ispartof field (42) for all handles in the sub query (i.e. the ones that have NARU in the affiliation field (121)).

SELECT handle,
       text_value
FROM   handle,
       metadatavalue
WHERE  metadatavalue.metadata_field_id = 42
       AND metadatavalue.item_id = handle.resource_id
       AND handle.handle IN (SELECT handle
                             FROM   handle,
                                    item,
                                    metadatavalue
                             WHERE  metadatavalue.item_id = handle.resource_id
                                    AND metadatavalue.metadata_field_id = 121
                                    AND handle.resource_type_id = 2
                                    AND handle.resource_id = item.item_id
                                    AND text_value LIKE '%NARU%'
                             ORDER  BY text_value)

SELECT handle,

text_value

FROM handle,

metadatavalue

WHERE metadatavalue.metadata_field_id = 42

AND metadatavalue.item_id = handle.resource_id

AND handle.handle IN (SELECT handle

FROM handle,

item,

metadatavalue

WHERE metadatavalue.item_id = handle.resource_id

AND metadatavalue.metadata_field_id = 121

AND handle.resource_type_id = 2

AND handle.resource_id = item.item_id

AND text_value LIKE '%NARU%'

ORDER BY text_value)

Find massive files:

SELECT *
FROM   bitstream
WHERE  deleted = false
ORDER  BY size_bytes DESC
LIMIT  10

SELECT *

FROM bitstream

WHERE deleted = false

ORDER BY size_bytes DESC

LIMIT 10

Get the handle of these big files:

SELECT h.handle,
       b.size_bytes,
       b.name
FROM   bitstream b,
       bundle2bitstream b2b,
       item2bundle i2b,
       handle h
WHERE  b2b.bitstream_id = b.bitstream_id
       AND b2b.bundle_id = i2b.bundle_id
       AND i2b.item_id = h.resource_id
       AND b.deleted = false
ORDER  BY b.size_bytes DESC
LIMIT  2

SELECT h.handle,

b.size_bytes,

b.name

FROM bitstream b,

bundle2bitstream b2b,

item2bundle i2b,

handle h

WHERE b2b.bitstream_id = b.bitstream_id

AND b2b.bundle_id = i2b.bundle_id

AND i2b.item_id = h.resource_id

AND b.deleted = false

ORDER BY b.size_bytes DESC

LIMIT 2

Find all handles that correspond to a specific Metadata Field within a given collection:

SELECT DISTINCT handle,
                metadatavalue.text_value
FROM   handle,
       item,
       metadatavalue
WHERE  metadatavalue.item_id = handle.resource_id
       AND metadata_field_id = 121
       AND handle.resource_type_id = 2
       AND item.withdrawn = 'f'
       AND handle.resource_id = item.item_id
       AND item.owning_collection = '107'
ORDER  BY handle

SELECT DISTINCT handle,

metadatavalue.text_value

FROM handle,

item,

metadatavalue

WHERE metadatavalue.item_id = handle.resource_id

AND metadata_field_id = 121

AND handle.resource_type_id = 2

AND item.withdrawn = 'f'

AND handle.resource_id = item.item_id

AND item.owning_collection = '107'

ORDER BY handle

One Comment

Edward Safari

Hi
Can you come up with a code that will generate all metadata values i.e author, subject, title, citation all the fields
July 28, 2020 Reply

Add a Comment