Difference between revisions of "SUNScholar/SQL Tips"
Jump to navigation
Jump to search
m (→DSpace =< 4.X) |
m |
||
| Line 13: | Line 13: | ||
==[[SUNScholar/SQL_Tips/4.X|DSpace =< 4.X]]== | ==[[SUNScholar/SQL_Tips/4.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 | ||
| + | <pre> | ||
| + | SELECT handle.handle, resourcepolicy.* | ||
| + | FROM resourcepolicy, handle | ||
| + | WHERE resourcepolicy.resource_type_id = '2' | ||
| + | AND resourcepolicy.resource_id = handle.resource_id | ||
| + | </pre> | ||
==References== | ==References== | ||
Revision as of 12:47, 4 July 2016
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.
- Find all hidden items
- select * from item where discoverable =false;
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
