SUNScholar/SQL Tips

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