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 follow the steps below: (PLEASE NOTE: Doing the following creates a security risk. You have been warned.)

Step 1

Enable access from other hosts.

Modify the postgresql.conf file and change the listen_addresses parameter to allow "wildcard" hosts to connect.

See example below.

listen_addresses = '*'  		# what IP address(es) to listen on;
					# comma-separated list of addresses;
					# defaults to 'localhost'; use '*' for all
					# (change requires restart)

Step 2

Modify the pg_hba.conf file to allow other hosts to connect.

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

References

• http://wiki.lib.sun.ac.za/index.php/SUNScholar/Prepare_Ubuntu/S06
• 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

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/Helper+SQL+functions+for+DSpace
• 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