SUNScholar/SQL Tips

Back to System Admin

WE CANNOT ACCEPT RESPONSIBILITY FOR ANY DATA LOSS OR CORRUPTION
BEFORE PROCEEDING, DO EXTENSIVE TESTING ON SPARE INFRASTRUCTURE
*** YOU PROCEED AT YOUR OWN RISK ***

Please note:

No DSpace version control is exercised with these SQL queries. They are only meant to be starting guides.

"pgadmin"

You can connect to your PostgreSQL database using "pgadmin".

https://www.pgadmin.org

However you must allow connections to the database from other hosts besides "localhost". To do this view the links below:

• http://dba.stackexchange.com/questions/48152/connecting-to-an-external-database-with-pgadmin-iii
• http://www.mad-hacking.net/documentation/linux/applications/postgres/connection-permissions.xml
• http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/c15679_002.htm

See example pg_hba.conf entries;

# Added by H Gibson for BIB IT access
host    all         all         146.232.125.245/24        md5 # Hilton
host    all         all         146.232.212.49/24         md5 # S Kroon
host    all         all         146.232.125.151/24        md5 # Wouter
host    all         all         146.232.129.78/24         md5 # libweb server

## DSpace DB user access
host    dspace       dspace      127.0.0.1/32         md5

General

Number of database connections

• SELECT count(*) FROM pg_stat_activity;

Display current query

• SELECT current_query FROM pg_stat_activity;

Permissions on bitstreams

SELECT * FROM resourcepolicy WHERE resource_type_id = '0'

Permissions on items

SELECT handle.handle, resourcepolicy.*
FROM resourcepolicy, handle
WHERE resourcepolicy.resource_type_id = '2'
AND resourcepolicy.resource_id = handle.resource_id

Find all hidden items

• select * from item where discoverable =false;

References

• https://wiki.duraspace.org/display/DSPACE/Metadata+for+all+DSpace+objects
• https://github.com/DSpace/DSpace/blob/master/dspace-api/src/main/java/org/dspace/core/Constants.java#L17