Skip to content

Instantly share code, notes, and snippets.

@stompro
Created January 26, 2018 14:10
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save stompro/e1f3a377ee6b7e044c285b482a1e0a50 to your computer and use it in GitHub Desktop.
Save stompro/e1f3a377ee6b7e044c285b482a1e0a50 to your computer and use it in GitHub Desktop.
Evergreen Circ History - Allow staff to enable/disable
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