SUNScholar/SQL Tips

From Libopedia
Jump to navigation Jump to search 
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

DSpace => 5.X

DSpace =< 4.X

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

Retrieved from "http://wiki.lib.sun.ac.za/index.php?title=SUNScholar/SQL_Tips&oldid=44512"