Posts Tagged ‘SQL’

Find All Restricted Items Within DSpace

Friday, August 19th, 2011

Here is an SQL query you can copy-and-paste into DSpace to find all items which have restricted access or contain bundles / bitstreams which are restricted. Restricted means that the object does not have an authorization policy enabling anonymous read.

It’s actually quite hard to find the absence of something with SQL. After trying various methods the way I came up with to solve this problem is a sub select that counts how many anonymous access policies exist for each object and if there are none then report those. The query is broken down into three distant parts one for each object time. Then all the objects are combined via PostgreSQL set operators and sub selects (again!). This means that if you have a huge number of restricted items in your repository the query might fail or take an obscene amount of time/memory to run. I tried using a left outer join but couldn’t get it to handle the case where both no access policies exists and only non anonymous access policies exist.

The approach used here is inelegant and has some serious performance problems. However it worked my immediate purpose. We had no idea how many or which items are restricted in our repository (answer: just under 300). This task is a good candidate for a DSpace curation task, to find all items in a collection which are have restricted access. Or the opposite, find all items which are NOT restricted.

(more…)