Skip to content

Instantly share code, notes, and snippets.

Josh Stompro stompro

Block or report user

Report or block stompro

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@stompro
stompro / Auditor tables bloat cleanup.sql
Last active Jul 15, 2019
Evergreen ILS Index and Table Bloat Notes
View Auditor tables bloat cleanup.sql
-- Auditor Bloat and cluster
--------------------------------
-- cluster asset_copy_history
-- Create index to cluster on
create index aud_asset_cp_cluster on auditor.asset_copy_history using btree(id,audit_id);
cluster verbose auditor.asset_copy_history using aud_asset_cp_cluster;
--remove cluster index
@stompro
stompro / evergreen
Created Feb 8, 2019
cat /etc/init.d/evergreen - Evergreen startup scripts
View evergreen
#!/bin/bash
### BEGIN INIT INFO
# Provides: evergreen
# Required-Start: $remote_fs $syslog ejabberd
# Should-Start: postgresql memcached apache2
# Required-Stop: $remote_fs $syslog
# Should-Stop: apache2 memcached postgresql
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
@stompro
stompro / auth-purge-00-notes.txt
Last active Jun 16, 2018
Purge unused authority records - Evergreen ILS
View auth-purge-00-notes.txt
Before you try to clean up your authority records, you should have run the authority linking scripts,
and then set them up to run on a regular basis via cron.
#Link bibs to authorities
/openils/bin/authority_control_fields.pl -a
#Link authorities to other authorities.
/openils/bin/authority_authority_linker.pl -a
@stompro
stompro / stat-cat-cross-tab.sql
Created Apr 6, 2018
Stat Cat Cross Tab Report
View stat-cat-cross-tab.sql
rollback;
begin;
select larl_temp.colpivot('_output',$$
select --acn.record
acp.id
, acp.barcode
, acp.circ_modifier
,ascc.name, asce.value
View series fixes.sql
-- List of all 490 a
select mrfr.value,count(distinct mrfr.record),string_agg(distinct mrfr.record::text,', ') from metabib.real_full_rec mrfr
where
mrfr.tag='490'
and mrfr.subfield='a'
--limit 100
group by mrfr.value
@stompro
stompro / allow-larl-holds.sql
Created Mar 2, 2018
Disable age hold protection when item has sat on the shelf for a certain amount of time.
View allow-larl-holds.sql
\pset format html
\echo <h1>Allow LARL holds on NWRL age protected items</h1>
\echo <h2>NWRL Items that could fill LARL holds</h2>
begin;
-- Create temp table to store NWRL copies that are canidates for removing age hold protection.
create temporary table nwrl_new
on commit drop
@stompro
stompro / batch purge circ history.sql
Created Jan 26, 2018
Evergreen Circ History - Allow staff to enable/disable
View batch purge circ history.sql
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.*
;
@stompro
stompro / AT Template
Last active Jan 22, 2018
SendSMSFlowRoute Reactor for Evergreen ILS
View AT Template
[% phone = target.0.sms_notify | replace('[\s\-\(\)]', '') -%]
[% cid = target.0.pickup_lib.phone | replace('[\s\-\(\)]', '') -%]
[% IF phone.match('^[2-9]') %][% country = 1 %][% ELSE %][% country = '' %][% END -%]
[%-
user = target.0.usr
system_shortname = user.home_ou.parent_ou.shortname
#target.0.sms_notify
realphone = country _ phone
tophone = '15555552110'
fromphone = '15555556040'
@stompro
stompro / actor.card adding creation date.sql
Created Jul 20, 2017
Evergreen actor.card adding creation date and time
View actor.card adding creation date.sql
-- Use history to find the date and staff member that changed the card
select * from (
select aauh.audit_id, aauh.audit_time, aauh.audit_user, aauh.card, aauh.id
,lead(aauh.card) OVER (PARTITION BY aauh.id ORDER BY aauh.audit_time) as next_card
from auditor.actor_usr_lifecycle aauh
where aauh.id > 112900
--and aauh.id=124212
View super simple length checks.sql
-- Check max length after super simple changes
select x.*, length(x.title) from (
SELECT r.id,
r.fingerprint,
r.quality,
r.tcn_source,
r.tcn_value,
FIRST(title.value) AS title,
FIRST(author.value) AS author,
STRING_AGG(DISTINCT publisher.value, ', ') AS publisher,
You can’t perform that action at this time.