Created
January 26, 2018 14:10
-
-
Save stompro/e1f3a377ee6b7e044c285b482a1e0a50 to your computer and use it in GitHub Desktop.
Evergreen Circ History - Allow staff to enable/disable
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
begin; | |
-- Look for preference set to null, blank or false and remove it. | |
delete from actor.usr_setting aus | |
where | |
aus.name='history.circ.retention_start' | |
and (aus.value is null or aus.value = 'false' or aus.value='') | |
returning aus.* | |
; | |
-- Look for users with circ history but no preference. | |
create temp table user_orphan_history | |
on commit drop | |
as | |
select auch.usr, count(auch.id) | |
from action.usr_circ_history auch | |
left outer join actor.usr_setting aus on aus.usr=auch.usr and aus.name='history.circ.retention_start' | |
join actor.usr au on au.id=auch.usr | |
where | |
aus.value is null | |
and not au.deleted | |
-- Sanity check, don't do anything if the preference definition has changed or if it isn't used any more, | |
-- Just in case the preference name is changed in the future and automaticlaly updated to a new value | |
and exists (select 1 from config.usr_setting_type cust where cust.name='history.circ.retention_start') | |
and exists (select 1 from actor.usr_setting aus where name='history.circ.retention_start' limit 1) | |
group by 1; | |
select * from user_orphan_history; | |
delete from action.usr_circ_history auch | |
using user_orphan_history uoh | |
where | |
uoh.usr=auch.usr | |
returning auch.* | |
; | |
rollback; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment