Differences

This shows you the differences between two versions of the page.

--- troubleshooting:random_magic_spells [2025/07/26 16:34] – removed - external edit (Unknown date) 127.0.0.1
+++ troubleshooting:random_magic_spells [2025/07/26 16:46] (current) – sleary
@@ Line 1: / Line 1: @@
+====== Troubleshooting: Random Magic Spells ======
+=====Check Evergreen version via srfsh=====
+   request open-ils.actor opensrf.open-ils.system.ils_version
+Doesn't //have// to be open-ils.actor any service will work.
+=====Check Evergreen version via http=====
+  http://acq.open-ils.org/osrf-gateway-v1?service=open-ils.actor&method=opensrf.open-ils.system.ils_version
+Replace acq.open-ils.org with the hostname for the server to test.
+=====Unable to login through the web interface or staff client after a successful install?  Try Dan Scott's tip for resetting your password=====
+   psql -U <dbuser> <dbname>
+   # update actor.usr set passwd = 'open-ils' where usrname = 'admin';
+   # \q
+Another option with newer versions of Evergreen, as the opensrf user:
+<code bash>
+Open-ILS/src/support-scripts/eg_db_config.pl --admin-password new_password
+</code>
+=====See long-running database queries=====
+<code sql>
+select now() - xact_start as "duration", waiting, current_query from pg_stat_activity where current_query !~ '<IDLE>' and (now() - xact_start) > '30 seconds'::interval order by 1 desc;
+</code>
+=====Find unicode characters in a file=====
+     perl -Mencoding=utf8 -MUnicode::Normalize -e 'while(<>){ $_=NFC($_);while(/([\x{0080}-\x{fffd}])/gc){ print "line $., $1 -> ".sprintf("\%0.4x",ord($1))."\n"; } }' filename.ext
+=====Stored procedures to find and eliminate bad (truncated) UTF-8 characters in postgres=====
+<code sql>
+create or replace function perl_find_char(text,text) returns text as $$ use Encode; my $x=shift; Encode::_utf8_off($x);my $char=shift; my $re = qr/$char/; return $x if ($x =~ /$re/); return undef; $$ language plperlu;
+create or replace function perl_rm_char (text,text) returns text as $$use Encode; my $x=shift; Encode::_utf8_off($x);my $char=shift; my $re = qr/$char/; $x =~ s/$re/$1$2/g; Encode::_utf8_on($x); return $x; $$ language plperlu;
+</code>
+Usage:
+<code sql>
+BEGIN;
+UPDATE some_table SET the_field = perl_rm_char(the_field,E'([\\x00-\\x79])[\\x80-\\xff]([\\x00-\\x79])') WHERE perl_find_char(l_call_number,E'[\\x00-\\x79][\\x{80}-\\x{FF}][\\x{00}-\\x{79}]') IS NOT NULL;
+EXPLAIN ANALYZE SELECT LOWER(the_field) FROM some_table;
+-- If the last command does not throw an error, you have succeeded
+COMMIT;
+</code>
+===== Quick way to audit in-db circ rules for a particular org unit =====
+A quick way to view the circulation policies, in PostgreSQL, with the names instead of id values, similar to the Staff Client Circulation Policy Editor.  Substitute a.org_unit=1 with the id of your library from the actor.org_unit table.
+<code sql>
+SELECT a.id, a.active, a.circ_modifier, b.shortname, c.name, a.renewals, d.name, e.name, f.name FROM config.circ_matrix_matchpoint AS a
+	LEFT JOIN actor.org_unit b ON a.org_unit = b.id
+	LEFT JOIN permission.grp_tree c ON a.grp = c.id
+	LEFT JOIN config.rule_circ_duration d ON a.duration_rule = d.id
+	LEFT JOIN config.rule_recurring_fine e ON a.recurring_fine_rule = e.id
+	LEFT JOIN config.rule_max_fine f ON a.max_fine_rule = f.id
+WHERE a.org_unit=1;
+</code>
+Another approach, showing more columns and their human readable values, change the WHERE clause to get different ones or comment out WHERE clause to see all rules. By yboston, inspired by bshum,
+<code sql>
+SELECT ccmm.id AS "ID", ccmm.active AS "Active",
+   org.shortname AS "copy_circ_lib",
+   pgt.name AS "Patron Group", ccmm.circ_modifier AS "Circ Modifier",
+   loc.name AS "copy_location",
+   ccmm.is_renewal AS "Renewal?", ccmm.circulate AS "Circulate?",
+   crcd.name AS "Circ Duration", crrf.name AS "Recurring Fine",
+   ccmm.grace_period,
+   crmf.name AS "Max Fine", limit_set.items_out AS "Max Items Out",
+   array_to_string(array_agg(lscm.circ_mod), ', ') AS "Limited Circ Mods"
+FROM config.circ_matrix_matchpoint ccmm
+   INNER JOIN actor.org_unit aou ON aou.id = ccmm.org_unit
+   INNER JOIN permission.grp_tree pgt ON pgt.id = ccmm.grp
+   INNER JOIN config.rule_circ_duration crcd ON crcd.id = ccmm.duration_rule
+   INNER JOIN config.rule_recurring_fine crrf ON crrf.id = ccmm.recurring_fine_rule
+   INNER JOIN config.rule_max_fine crmf ON crmf.id = ccmm.max_fine_rule
+   LEFT OUTER JOIN config.circ_matrix_limit_set_map map ON map.matchpoint = ccmm.id
+   LEFT OUTER JOIN config.circ_limit_set limit_set ON limit_set.id = map.limit_set
+   LEFT OUTER JOIN config.circ_limit_set_circ_mod_map lscm ON lscm.limit_set = limit_set.id
+   LEFT OUTER JOIN actor.org_unit org on (ccmm.copy_circ_lib = org.id)
+   LEFT OUTER JOIN asset.copy_location loc on (loc.id = ccmm.copy_location)
+WHERE ccmm.copy_circ_lib = 101 -- OPTIONAL: set one or more org unit id numbers here
+GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9,10, 11, 12, 13
+ORDER BY copy_circ_lib, pgt.name, ccmm.circ_modifier;
+</code>
+===== Cancel Query/Report =====
+If you need to cancel a query/report that is stuck and is locking up the queue we use pg_cancel_backend(backend_pid). This will kill the query and allow the clark process to 1) notice and 2) clean up after the canceled query.
+<code sql>
+SELECT pg_cancel_backend(backend_pid);
+</code>
+The backend_pid comes from the procpid column of pg_stat_activity.
+<code sql>
+SELECT procpid, now()-query_start AS duration, current_query FROM pg_stat_activity WHERE current_query <> '<IDLE>' ORDER BY 2;
+</code>
+This will give you the current queries, ordered by duration. Reports are easy to spot because the reporter uses MD5sum values for table aliases.
+For databases running PostgreSQL 9.2+, the pg_stat_activity table is a little different, so this query is more helpful perhaps:
+<code sql>
+SELECT pid, now()-query_start AS duration, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY 2;
+</code>
+===== List Reporter Folders with Bad Sharing Depths =====
+Given a simple org tree of A->B->C, if a user at C shares a report folder with all of C and then shares a subfolder with A or B, the display of shared folders for all users at A or B will be broken (the location of the breakage depends on the sort order of the username). To fix this, the "top-level" folder has to be shared the most widely with the subfolders either at the same or a more restrictive level. This SQL will list the folders that are shared more widely than their parents:
+<code sql>
+-- 3 types of folders, 3 queries.
+select rpt.id as "Template Folder id", rpt.name as "Name", au.usrname as "User", rpt.share_with as "Shared With", prpt.share_with as "Parent Shared With" from reporter.template_folder rpt join reporter.template_folder prpt on (rpt.parent = prpt.id) join actor.usr au on (rpt.owner = au.id) where rpt.shared is true and (prpt.share_with not in (select id from actor.org_unit_ancestors(rpt.share_with)) or prpt.shared is false);
+select rpt.id as "Report Folder id", rpt.name as "Name", au.usrname as "User", rpt.share_with as "Shared With", prpt.share_with as "Parent Shared With" from reporter.report_folder rpt join reporter.report_folder prpt on (rpt.parent = prpt.id) join actor.usr au on (rpt.owner = au.id) where rpt.shared is true and (prpt.share_with not in (select id from actor.org_unit_ancestors(rpt.share_with)) or prpt.shared is false);
+select rpt.id as "Output Folder id", rpt.name as "Name", au.usrname as "User", rpt.share_with as "Shared With", prpt.share_with as "Parent Shared With" from reporter.output_folder rpt join reporter.output_folder prpt on (rpt.parent = prpt.id) join actor.usr au on (rpt.owner = au.id) where rpt.shared is true and (prpt.share_with not in (select id from actor.org_unit_ancestors(rpt.share_with)) or prpt.shared is false);
+</code>
+Using Bucardo's check_postgres script with Nagios/Icincga? Add checks for these:
+<code>
+check_postgres --query="SELECT COUNT(rpt.id) AS result, COALESCE(STRING_AGG(rpt.id::TEXT, ','), 'none') AS ids FROM reporter.template_folder AS rpt JOIN reporter.template_folder AS prpt ON (rpt.parent = prpt.id) WHERE rpt.shared IS TRUE AND (prpt.share_with NOT IN (SELECT id FROM actor.org_unit_ancestors(rpt.share_with)) OR prpt.shared IS FALSE);" -c 1 -u evergreen -H localhost --action=custom_query -db evergreen
+check_postgres --query="SELECT COUNT(rpt.id) AS result, COALESCE(STRING_AGG(rpt.id::TEXT, ','), 'none') AS ids FROM reporter.report_folder AS rpt JOIN reporter.report_folder AS prpt ON (rpt.parent = prpt.id) WHERE rpt.shared IS TRUE AND (prpt.share_with NOT IN (SELECT id FROM actor.org_unit_ancestors(rpt.share_with)) OR prpt.shared IS FALSE);" -c 1 -u evergreen -H localhost --action=custom_query -db evergreen
+check_postgres --query="SELECT COUNT(rpt.id) AS result, COALESCE(STRING_AGG(rpt.id::TEXT, ','), 'none') AS ids FROM reporter.output_folder AS rpt JOIN reporter.output_folder AS prpt ON (rpt.parent = prpt.id) WHERE rpt.shared IS TRUE AND (prpt.share_with NOT IN (SELECT id FROM actor.org_unit_ancestors(rpt.share_with)) OR prpt.shared IS FALSE);" -c 1 -u evergreen -H localhost --action=custom_query -db evergreen
+</code>
+===== Listing users with permissions by work_ou =====
+This is useful for auditing who has been granted privileges where:
+<code sql>
+SELECT distinct au.id, au.first_given_name, au.family_name, puwom.work_ou, aou.shortname, pugm.grp, pgt.name
+  FROM actor.usr au
+    INNER JOIN permission.usr_grp_map pugm ON au.id = pugm.usr
+    INNER JOIN permission.usr_work_ou_map puwom ON au.id = puwom.usr
+    INNER JOIN permission.grp_tree pgt ON pugm.grp = pgt.id
+    INNER JOIN actor.org_unit aou ON puwom.work_ou = aou.id
+  -- WHERE puwom.work_ou IN (110, 125, 126) -- uncomment this clause and adjust to limit to specific org_units
+  -- AND pgt.id IN (17, 18) -- uncomment this clause and adjust to limit to members of specific perm groups
+  ORDER BY family_name, work_ou, grp
+;
+</code>
+===== Comparing permissions between multiple permission groups =====
+<code sql>
+SELECT
+  (SELECT code FROM permission.perm_list WHERE id = perm) AS "Perm",
+  (SELECT 'Depth: ' || depth || ' Grantable: ' || CASE WHEN grantable THEN 'True' ELSE 'False' END
+     from permission.grp_perm_map AS b WHERE b.grp = ID_FOR_GROUP_X AND b.perm = a.perm) AS "Group X",
+  (SELECT 'Depth: ' || depth || ' Grantable: ' || CASE WHEN grantable THEN 'True' ELSE 'False' END
+     FROM permission.grp_perm_map AS b WHERE b.grp = ID_FOR_GROUP_Y AND b.perm = a.perm) AS "Group Y"
+FROM permission.grp_perm_map AS a WHERE grp IN (ID_FOR_GROUP_X,ID_FOR_GROUP_Y) ORDER BY 1;
+</code>
+===== Comparing permissions between multiple permission groups (variant: uses complete permission list) =====
+<code sql>
+SELECT
+  code AS "Perm",
+  (SELECT 'Depth: ' || depth || ' Grantable: ' || CASE WHEN grantable THEN 'True' ELSE 'False' END
+     from permission.grp_perm_map AS b WHERE b.grp = ID_FOR_GROUP_X AND b.perm = a.id) AS "Group X",
+  (SELECT 'Depth: ' || depth || ' Grantable: ' || CASE WHEN grantable THEN 'True' ELSE 'False' END
+     FROM permission.grp_perm_map AS b WHERE b.grp = ID_FOR_GROUP_Y AND b.perm = a.id) AS "Group Y"
+FROM permission.perm_list as a order by 1
+</code>
+===== Finding mis-matched depths with org units and org unit types =====
+<code sql>
+SELECT
+	child.ou_id  AS child_id,
+	child.depth  AS child_depth,
+	parent.ou_id AS parent_id,
+	parent.depth AS parent_depth
+FROM
+	(
+		SELECT
+			aou.id         AS ou_id,
+			aou.parent_ou  AS parent,
+			aout.depth     AS depth
+		FROM
+			actor.org_unit AS aou
+				LEFT JOIN actor.org_unit_type AS aout
+					ON ( aou.ou_type = aout.id )
+	) AS parent,
+	(
+		SELECT
+			aou.id         AS ou_id,
+			aou.parent_ou  AS parent,
+			aout.depth     AS depth
+		FROM
+			actor.org_unit AS aou
+				LEFT JOIN actor.org_unit_type AS aout
+					ON ( aou.ou_type = aout.id )
+	) AS child
+WHERE
+	child.parent = parent.ou_id
+	AND child.depth <> parent.depth + 1;
+</code>
+=====Checking Circ/Hold Flags on an Item=====
+<code sql>
+SELECT
+	id,
+	circ_lib,
+	(SELECT owning_lib FROM asset.call_number WHERE id = call_number) AS "owning_lib",
+	status,
+	(SELECT holdable FROM config.copy_status WHERE id = status) AS "status holdable",
+	(SELECT opac_visible FROM config.copy_status WHERE id = status) AS "status OPAC visible",
+	location,
+	(SELECT circulate FROM asset.copy_location WHERE id = location) AS "loc circulate",
+	(SELECT holdable FROM asset.copy_location WHERE id = location) AS "loc holdable",
+	(SELECT opac_visible FROM asset.copy_location WHERE id = location) AS "loc OPAC visible",
+	circulate,
+	holdable,
+	opac_visible,
+	age_protect,
+	circ_modifier,
+	circ_AS_type, ref,
+	alert_message is not null AS "alert"
+FROM asset.copy
+WHERE id = #item_id_here#;
+</code>
+=====Where is my workstation registration ws_info file?=====
+Assuming an admin/super-user or a login with the DEBUG_CLIENT permission, press Control+Shift+F7 from within the staff client to open a debug textbox near the top of the interface.  Enter the following into that textbox and press Execute:
+<code>
+netscape.security.PrivilegeManager.enablePrivilege("UniversalXPConnect"); JSAN.use('util.file'); var f = new util.file('ws_info'); alert(f._file.path);
+</code>
+====== Performance ======
+=====Preload Your database into RAM=====
+Find your database's oid and make a note of it:
+<code>
+psql -U evergreen -h localhost -d postgres -c "select oid from pg_database where datname = 'evergreen'"
+</code>
+Then, as the root or postgres user, run the following commands
+<code>
+cd /var/lib/postgresql/${pgversion}/main/base/${oid}
+cat * > /dev/null
+</code>
+Replace ${pgversion} with your PostgreSQL version number, ex. 9.5.  Replace ${oid} with the number returned by the database query.
+====== Utility ======
+=====Regularly Scheduled Report Output Purging=====
+Ben Shum asked me to share the scripts Evergreen Indiana utilizes to purge reports older than 30 days
+=== Cron Tab Entries ===
+<code>15 3 * * * cd /var/evergreen/scripts && psql -U evergreen < purgereports.sql >/dev/null 2>&1
+3 * * * cd /openils/bin && ./deleteoldreports.sh >/dev/null 2>&1</code>
+=== purgereports.sql ===
+<code sql>DELETE FROM reporter.schedule WHERE complete_time < (now() - '30 days'::INTERVAL);
+DELETE FROM reporter.report WHERE create_time < (now() - '30 days'::INTERVAL) AND recur = FALSE;
+DELETE FROM reporter.report WHERE create_time < (now() - '30 days'::INTERVAL) AND id NOT IN (SELECT report FROM reporter.schedule);</code>
+=== deleteoldreports.sh ===
+<code>reportdelete=`./find_orphaned_reports.pl`;
+for i in $reportdelete; do
+rm -r $i; done</code>
+===== Use srfsh to search the OPAC =====
+Example from Lebbeous Fogle-Weekley (see [[http://www.open-ils.org/irc_logs/evergreen/2013-08/%23evergreen.23-Fri-2013.log#line154|here]] for context):
+<code>
+request open-ils.search open-ils.search.biblio.multiclass.query {} "piano limit(10) offset(10)"
+</code>
+Another example from Bill Erickson in the same discussion (from JSPAC logs):
+<code>
+request open-ils.search open-ils.search.biblio.multiclass.query {"org_unit":1,"depth":0,"limit":10,"offset":0,"visibility_limit":3000,"default_class":"keyword"}, "concerto", 1
+</code>
+===== Copying permissions from one group to another =====
+<code sql>
+INSERT INTO permission.grp_perm_map (grp,perm,depth)
+    SELECT ID_FOR_TARGET_GROUP, perm, depth FROM permission.grp_perm_map
+        WHERE grp = ID_FOR_SOURCE_GROUP AND perm NOT ( SELECT perm FROM permission.grp_perm_map WHERE grp = ID_FOR_TARGET_GROUP );
+</code>
+=====Transcoding a flat file from MARC8 to UTF-8=====
+     perl -MUnicode::Normalize -MEncode -MMARC::Charset=marc8_to_utf8 -e 'binmode(STDIN,":bytes");binmode(STDOUT,":utf8");while(<>){ Encode::_utf8_off($_); chomp; $_=marc8_to_utf8($_); print NFC($_)."\n"}' in-file > out-file
+=====Magical command line for creating loader SQL=====
+      ~/cvs/ILS/Open-ILS/src/extras/import/marc2bre.pl -k ../catflex.keys -c /opt/openils/conf/bootstrap.conf ../marc/xaa.mrc | ~/cvs/ILS/Open-ILS/src/extras/import/direct_ingest.pl -c /opt/openils/conf/bootstrap.conf -t 1 | ~/cvs/ILS/Open-ILS/src/extras/import/pg_loader.pl -c /opt/openils/conf/bootstrap.conf -or bre -or mrd -or mfr -or mtfe -or mafe -or msfe -or mkfe -or msefe -a mrd -a mfr -a mtfe -a mafe -a msfe -a mkfe -a msefe > xaa.sql
+=====Dan Scott asked for a web service that can turn MARC into XML ... well, here it is=====
+<code perl>
+#!/usr/bin/perl
+use CGI;
+use MARC::Record;
+use MARC::File::XML;
+print "Content-type: application/marc+xml\n\n" . MARC::Record->new_from_usmarc( CGI->new->param('marc') )->as_xml_record;
+__END__
+To test this, run the chunk below from the command line:
+$ perl ./marc2xml.cgi marc=00514cam%20%2022001451%20%204500001001800000008004100018050002100059100003100080245005500111260004500166300004100211490003500252500007400287999000700361%1E%20%20%2001031483%20%2F%2Fr25%1E921016s1901%20%20%20%20nyuab%20%20%20%20%20%20%20%20%2000010%20eng%20%20%1E0%20%1FaPS2472%1Fb%2EO3%201901%1E1%20%1FaNorris%2C%20Frank%2C%1Fd1870%2D1902%2E%1E14%1FaOctopus%3B%1Fba%20story%20of%20California%2C%1Fcby%20Frank%20Norris%2E%1E0%20%1FaNew%20York%2C%1FbDoubleday%2C%20Page%20%26%20co%2E%2C%1Fc1901%2E%1E%20%20%1Fa4%20p%2El%2E%2C%20652%20p%2E%1Fbillus%2E%20%28map%29%1Fc20%20cm%2E%1E0%20%1FaHis%20The%20epic%20of%20the%20wheat%2E%20%5BI%5D%1E%20%20%1Fa%22Deals%20with%20the%20war%20between%20the%20wheat%20grower%20and%20the%20railroad%20trust%2E%22%1E%20%20%1Faa1%1E%1D
+</code>
+=====How to turn the freshmeat (new bib) feed into a JSPAC result page===
+First, stick this in /openils/var/web/opac/extras/new-records.cgi:
+<code>
+#!/bin/bash
+echo -n "Location: /"
+wget -o /dev/null -O - 'http://localhost/opac/extras/feed/freshmeat/rss2/biblio/import/10/' | grep 'rel="OPAC"' | cut -f2 -d'>' | cut -f1 -d'<' | cut -f4- -d\/
+echo "Status: 302"
+echo ""
+</code>
+And then add this to your eg_vhost.conf
+<code>
+<Directory "/openils/var/web/opac/extras">
+    <Files new-records.cgi>
+        ForceType cgi-script
+    </Files>
+    Options ExecCGI
+</Directory>
+</code>
+=====Perl URL Encode & Decode String=====
+(stolen shamelessly from http://support.internetconnection.net/CODE_LIBRARY/Perl_URL_Encode_and_Decode.shtml ... I don't want to loose the tip.)
+Here's an excellent way to encode strings that are going to be placed in a URL:
+<code perl>$str =~ s/([^A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg;</code>
+$str can now be passed in the query string safely, for example:
+<code perl><a ref="page_given_string.pl?str=$str">LINK</a></code>
+Likely, "page_given_string.pl" will want to decode $str back into something useful:
+<code perl>$str =~ s/\%([A-Fa-f0-9]{2})/pack('C', hex($1))/seg;</code>
+====== Bib-work ======
+=====Get MARC field for a record=====
+Get all 650a fields for record 133027
+<code sql>
+SELECT val FROM oils_xpath_table('id','marc','biblio.record_entry', '//*[@tag="650"]/*[@code="a"]', 'id=' || 133027) AS foo (id INT, val TEXT);
+</code>
+=====How to generate metarecords for a newly loaded bib batch=====
+<code sql>
+BEGIN;
+INSERT INTO metabib.metarecord (fingerprint, master_record)
+        SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
+          FROM  biblio.record_entry b
+          WHERE NOT b.deleted
+                AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
+                AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
+          ORDER BY b.fingerprint, b.quality DESC;
+INSERT INTO metabib.metarecord_source_map (metarecord, source)
+        SELECT  m.id, r.id
+          FROM  biblio.record_entry r
+                JOIN metabib.metarecord m USING (fingerprint)
+          WHERE NOT r.deleted
+                AND r.id IN (SELECT b.id FROM biblio.record_entry b LEFT JOIN metabib.metarecord_source_map k ON (k.source = b.id) WHERE k.id IS NULL);
+-- and if all's well ...
+COMMIT;
+</code>
+=====How to include a specific MARC field with a specific search class.=====
+For this example, how to include tag 590's with keyword searches:
+  - Create an indexing entry in config.metabib_field:<code sql>
+ INSERT INTO config.metabib_field (field_class,name,xpath,format) VALUES (
+   'keyword',
+   '590',
+   $$//marc:datafield[@tag="590"]$$,
+   'marcxml'
+ );
+</code>
+  - Follow the [[#reingesting_bib_and_authority_records_20_and_later|instructions for reingesting]] your bib records.  To save time, restrict the updates of ''biblio.record_entry'' to just records that have the desired tag.
+=====Sync the 001 field of your serials records to the linked bibliographic record ID=====
+This would be easily adapted to force the 001 of your bibliographic records to be the record ID, as well, effectively making the record ID the system control number.
+An enhanced version would test to see if there is a 001 field in the first place, and create it if it's not there.
+<code sql>
+CREATE OR REPLACE FUNCTION serial.record_entry() RETURNS trigger AS $serial_record_entry$
+BEGIN
+    NEW.marc := REGEXP_REPLACE(NEW.marc, E'^(.*?<controlfield tag="001">).*?(<.*$)', E'\\1' || NEW.record || E'\\2', 's');
+    RETURN NEW;
+END;
+$serial_record_entry$ LANGUAGE plpgsql;
+CREATE TRIGGER serial_record_entry_update_001
+    BEFORE INSERT OR UPDATE ON serial.record_entry
+    FOR EACH ROW
+    EXECUTE PROCEDURE serial.record_entry();
+</code>
+=====How to prune a tag under the hood=====
+Please use with caution!
+<code sql>
+update biblio.record_entry set marc = regexp_replace(marc,E'<datafield[^>]*?tag="059".+?</datafield>','','g');
+</code>
+After that, it is best to reingest the record (using the method appropriate to your Evergreen version listed below).
+===== Reingesting Bib and Authority Records (2.0 and later) =====
+As of 2.0, record ingest is handled by stored procedures in the database.  The OpenSRF ingest methods should **not** be used at present, as they bypass the stored procedures and no longer can be guaranteed to have the same results.  To selectively reingest a bib record:
+<code sql>
+-- update flag to force reingesting the record even if the MARC hasn't changed
+update config.internal_flag set enabled = true where name = 'ingest.reingest.force_on_same_marc';
+update biblio.record_entry set id = id where id = 123;
+update config.internal_flag set enabled = false where name = 'ingest.reingest.force_on_same_marc';
+</code>
+The same spell can be used to reingest an authority record:
+<code sql>
+update config.internal_flag set enabled = true where name = 'ingest.reingest.force_on_same_marc';
+update authority.record_entry set id = id where id = 123;
+update config.internal_flag set enabled = false where name = 'ingest.reingest.force_on_same_marc';
+</code>
+There are several other flags that can be tweaked to selectively control which index tables get updated.  These include:
+  * ingest.metarecord_mapping.skip_on_update (enabling this can significantly speed up the reingest of a large number of records)
+  * ingest.disable_located_uri
+  * ingest.disable_metabib_full_rec
+  * ingest.disable_metabib_rec_descriptor
+  * ingest.disable_metabib_field_entry
+Some flags apply when inserting new bibs:
+  * ingest.metarecord_mapping.skip_on_insert (enabling this can significantly speed up importing a large number of bibs; use the spell for creating metarecords after loading the bibs)
+  * ingest.assume_inserts_only
+Other flags apply when dealing with authority records or changes to authorities:
+  * ingest.disable_authority_linking
+  * ingest.disable_authority_auto_update
+===== Find missing or mismatched 901$c =====
+From Galen Charlton:
+-- query to identify all bibs that are missing 901s or whose first 901$c is not equal to the bib ID
+-- this *will* take a long time in a big database
+<code sql>
+SELECT id
+FROM biblio.record_entry
+WHERE (XPATH('//marc:datafield[@tag="901"][1]/marc:subfield[@code="c"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT IS NULL
+OR    (XPATH('//marc:datafield[@tag="901"][1]/marc:subfield[@code="c"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT <> id::TEXT;
+</code>
+===== Generating a SQL report with natural titles =====
+While the reporter.materialized_simple_record view is convenient, sometimes those all-lowercase titles are annoying (for example, in shelf list reports that you're preparing for public consumption). In those cases, a little XPATH can go a long way--but you need to correct for the XML array that XPATH() returns, which you can do with ARRAY_TO_STRING() like the following:
+<code sql>
+SELECT
+  ARRAY_TO_STRING(
+    XPATH('//marc:datafield[@tag="245"][1]//text()',
+      marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]
+    ),
+    ' '
+  )
+FROM biblio.record_entry WHERE ...
+</code>
+====== Statistics ======
+=====To find the worst queries for the day, in the logger Pg logs=====
+    grep -Hn duration: pg.*.log|grep -v vacuum|awk '{print $16 " " $1}'| sed 's/:/ /g'|awk '{print $1 "\t" $2 ":" $3}'|sort -run|head
+=====Generate counts for normalized query strings from activity.log=====
+    grep multiclass activity.log |grep 'offset":0,'| PERL5LIB=/root/ perl -MJSON -pe 's/^.+{"searches":(.+),"org.+$/$1/gso;eval {$_ = (values(%{JSON->JSON2perl($_)}))[0]->{term};s/"//gso;s/^\s*(.+)\s*/$1/gso;s/\s+/ /gso;$_=lc($_)."\n"};$_="\n" if ($@)'|sort|uniq -c|sort -nr > query-counts.txt
+=====Example of generating monthly search counts from the activity log=====
+<code>(for i in `perl -e 'print join " " => map { sprintf "\%02d" => $_ } 1 .. 31;'`; do (cd $i; echo $i; ~/stats/top_searches.sh 2>/dev/null ); done) | tee may_searches.txt</code>
+===== Counting number of copies held by a library by LC call number range =====
+This is useful for getting a reasonable count of the number of copies of monographs held by a given library based on the first character of the LC call number. For Dewey you could adjust the substring length accordingly.
+<code sql>
+SELECT SUBSTRING(UPPER(acn.label), 1, 1) AS "LC class", COUNT(ac.id) AS "Count"
+  FROM asset.copy ac
+    INNER JOIN asset.call_number acn ON ac.call_number = acn.id
+    INNER JOIN actor.org_unit aou ON aou.id = acn.owning_lib
+    INNER JOIN metabib.rec_descriptor mrd ON mrd.record = acn.record
+  WHERE aou.id = 103
+    AND mrd.bib_level IN ('a','m') -- limit to monographs; use 'b','s' for serials
+    AND mrd.item_form NOT IN ('s') -- limit to non-electronic resources
+--    AND acn.label NOT LIKE 'XX%' -- avoid ranges of legacy ephemeral garbage
+    AND ac.deleted IS FALSE
+    AND acn.deleted IS FALSE
+  GROUP BY SUBSTRING(UPPER(acn.label), 1, 1)
+  ORDER BY 1
+;
+</code>
+===== Counting titles that match a list of ISBNs owned by a given library =====
+A common task when considering the purchase of a bulk set of new titles is to check for titles that a given library already owns. One way to do this is to compare the ISBNs in the existing records vs. the potential set of new titles, assuming that you have been given a title list that includes ISBNs.
+  - Create a scratch table containing the list of ISBNs for the potential purchase, then copy the ISBNs into that table:<code sql>
+BEGIN;
+CREATE TABLE scratchpad.isbn_list (isbn text);
+-- Replace ISBN_1, ISBN_2, etc with the list of ISBNs, one per line
+COPY scratchpad.isbn_list(isbn) FROM stdin;
+ISBN_1
+ISBN_2
+\.
+COMMIT;
+</code>
+  - Then match the ISBNs against the records for a given library. We make the assumption that if you have an undeleted call number attached to the pertinent record, that you own at least one copy of the record. The only real "trick" is that '''reporter.super_simple_record.isbn''' is an ARRAY column, so we have to match against the contents of that column using the '''ANY''' operator. Warning: this is slow; that's the price you pay for a non-normalized database schema.
+<code sql>
+SELECT COUNT(*)
+  FROM asset.call_number
+  WHERE record IN (
+    SELECT id
+      FROM reporter.super_simple_record rssr
+        INNER JOIN scratchpad.isbn_list sil
+        ON sd.isbn = ANY (rssr.isbn)
+    )
+  AND owning_lib = 103
+  AND deleted IS FALSE
+;
+</code>
+=====Show statistic usage on reports===
+<code sql>
+select thecount.name,create_time,thecount.lasttime,thecount.count from
+reporter.template rt,
+(
+select rtinner.name "name",max(run_time) "lasttime",count(*) "count" from reporter.schedule rs,reporter.report rr,
+(select naming.name,string_agg(clones.id||'',',') ids from
+reporter.template naming ,
+reporter.template clones
+where
+clones.data=naming.data and
+naming.folder in(select id from reporter.template_folder where owner= !!!REPLACE ME WITH OWNER ID!!! and shared and share_with=1) and
+naming.owner=  !!!REPLACE ME WITH OWNER ID!!!
+group by 1
+) rtinner
+where rr.template = any(('{'||rtinner.ids||'}')::int[]) and rr.id=rs.report
+group by 1
+) as thecount
+where
+thecount.name = rt.name and
+rt.owner=  !!!REPLACE ME WITH OWNER ID!!!  and
+rt.folder in(select id from reporter.template_folder where owner= !!!REPLACE ME WITH OWNER ID!!! and shared and share_with=1)
+order by 4 desc
+</code>
+You will need to edit that query to look for reports owned by a particular actor.usr.id. Replace "!!!REPLACE ME WITH OWNER ID!!!" with the ID number. This is useful when you have centralized report templates for everyone to use. It also takes into account cloned reports from the same source template!
+====== Development ======
+=====Generating the release ChangeLog from Git=====
+[[http://git.661346.n2.nabble.com/Generating-GNU-style-Changelog-from-git-commits-td6279979.html|This email thread]] lists a couple ideas.
+=====Flags to use for custom builds of postgres=====
+<code>--with-perl --enable-integer-datetimes</code>
+===== Custom Dojo Build =====
+Creating a build for dojo in Evergreen will decrease the loading time for each page significantly, providing that there are enough modules being called to warrant a build in the first place.  See the [[http://dojotoolkit.org/reference-guide/build/pre17/build.html#build-pre17-build|Dojo Build System (Pre-1.7) documentation]] for details.
+To start, you want to understand the construction of a dojo build.  Each build is a profile, each profile has layers.  Each layer is made up of dojo modules.  You can see ''Open-ILS/examples/openils.profile.js'' to illustrate this concept. Later, these layers will be called in your markup as scripts. Therefore, you can potentially organize your dojo builds to work harmoniously with your custom set up, utilizing custom 'layers' of grouped modules for each UI.
+First, retrieve and extract a copy of the [[http://download.dojotoolkit.org/current-stable/|Dojo source]].  **NOTE:** You //must// use the source distribution for this, which contains ''src'' in the name, as only the source version contains the required layer building scripts!  Under the top-level extracted directory you should have four dirs; dijit, dojo, dojox, and util. Copy ''Open-ILS/examples/openils.profile.js'' into the ''util/buildscripts/profiles'' directory.
+To create the build:
+   cd util/buildscripts
+   ./build.sh profile=openils action=release version=1.3.1 # (if using Dojo 1.3.1, otherwise make this the Dojo version you are currently running)
+Once you run the build script you should then have a directory named ''release/'' as a peer to ''util/'', ''dojo/'', ''dijit/'' and ''dojox/'' in the Dojo source.  Copy the contents of ''release/dojo/'', optionally excluding ''release/dojo/util'', into ''/openils/var/web/js/dojo/''.
+==== Creating a Custom Build ====
+If you decide to create a custom build for your organization, you may want to create custom layers in your profile. The dojo URL referenced above should explain how to go about doing that.
+Your first step is to find each file where you include 'dojo.require'.
+Then you want to find the module that is being used for each template or interface.
+dojofind.pl will do this for you, with slight tweaking for the file path, if necessary.
+===== URL Format for "browse indexes" =====
+    http://{hostname}/opac/extras/browse/{format, such as: xml (callnumber axis only), html[-full], marcxml[-full], or other unapi format}/{title|author|subject|topic|series|item-age|callnumber|authority.title|authority.author|authority.subject|authority.topic}/{org shortname, or -, ignored by authority.* axes but required positionally}/{target string}[/{page number offset, can be negative, defaults to 0}]?[&?status={number}...][&?copyLocation={number}...]
+==== I18N build spell =====
+In a fresh checkout of the repo, after the autotools autogen.sh, run the following:
+<code>cd build/i18n; make newpot; for i in `ls po/lang.dtd/|cut -f1 -d.|grep -v lang`; do make LOCALE=$i install; done</code>
+to make some fancy i18n stuffs.
+====== SQL Tips & Tricks ======
+=====SQL for System Administrators=====
+Dan Scott's presentation:
+[[http://coffeecode.net/archives/212-Introduction-to-SQL-for-Evergreen-administrators.html|Introduction to SQL for Evergreen Administrators]]
+=====Using SQL to Generate SQL=====
+One trick that is useful in many situations is to use SQL to generate a script. For instance, after adjusting the settings on penalties, you might need to update the penalties on a given class of patrons. While there's a nice SQL function to do that, it's not convenient to call it on multiple users. Instead, use SQL to create a script you can then run. Suppose I want to run the actor.calculate_system_penalties on all Adult patrons. I could generate a script to do that via this query:
+   select 'insert into actor.usr_standing_penalty '
+       || '( org_unit, usr, standing_penalty, staff, set_date, stop_date, note ) '
+       || 'select org_unit, usr, standing_penalty, staff, set_date, stop_date, note '
+       || '  from actor.calculate_system_penalties( ' || usr.id || ', ' || ou.id || ' ) ; '
+     from actor.usr usr
+     join actor.org_unit ou
+       on ( usr.home_ou = ou.id )
+     join permission.grp_tree pgt
+       on ( usr.profile = pgt.id )
+    where pgt.name = 'Adult'
+   ;
+The results of this query will look something like this (I've split the lines up a bit for clarity--each entry would be one long line):
+   ?column?
+   insert into actor.usr_standing_penalty
+        ( org_unit, usr, standing_penalty, staff, set_date, stop_date, note )
+   select org_unit, usr, standing_penalty, staff, set_date, stop_date, note
+     from actor.calculate_system_penalties( 82111, 104 ) ;
+   insert into actor.usr_standing_penalty
+        ( org_unit, usr, standing_penalty, staff, set_date, stop_date, note )
+   select org_unit, usr, standing_penalty, staff, set_date, stop_date, note
+     from actor.calculate_system_penalties( 158001, 109 ) ;
+   ...
+So, to make use of this, you'd capture the output of the query, and then run it as a script. In psql //\o fileName// allows you to save your output to a file; use //\o// (no file name) to turn off the output. You may need to edit the file to remove a spurious column heading and rowcounts. You can obviate the need to deal with column headers and rowcounts by setting psql to omit those from the output via this psql command: //\t//
+Another way to eliminate the need to deal with column headings is to give the column a name which is a comment (this is particularly handy if you might need to run the script again):
+   select 'insert into actor.usr_standing_penalty '
+       || '( org_unit, usr, standing_penalty, staff, set_date, stop_date, note ) '
+       || 'select org_unit, usr, standing_penalty, staff, set_date, stop_date, note '
+       || '  from actor.calculate_system_penalties( ' || usr.id || ', ' || ou.id || ' ) ; '
+       as "/* recalc Adult penalties */"
+      ...
+Note that using a column name that is itself a comment does not work with the //\t// command (because it turns off column headings as well as footers); if you want to keep the column heading, but eliminate the footer, you can use this command: //\pset footer off//
+If you prefer to use GUI tools for SQL (some of us do, at least frequently), in pgAdmin III, although you can capture the output easily enough, by default, it most inconveniently surrounds it in double-quotes. You can turn this behavior off in the main window's File > Options dialog's Query Tool tab: Change //Result copy quoting// to //None//
+=====Using LIMIT to check your results=====
+Frequently when creating a query, especially a complex one that will return many rows or just run for a long time, it's helpful to have a relatively small number of rows returned so you can make sure you've got the conditions correct. For instance, in the above example (Using SQL to Generate SQL), it's easy to miss a critical piece (like the final ; ), for instance. Use the LIMIT keyword to return a small number of rows until you're confident your SQL is working the way you want it to:
+   select 'insert into actor.usr_standing_penalty select ...'
+   ...
+    LIMIT 20 -- only return 20 rows
+   ;
+Just do not forget to comment out or delete the LIMIT before you run it for real, or you may fall victim to the but-I-could-have-sworn-there-were-more-than-that-many syndrome.
+=====Listing disabled triggers=====
+<code sql>
+select
+    (select nspname from pg_namespace where oid = (select relnamespace from pg_class where oid = tgrelid))
+    || '.'
+    || (select relname from pg_class where oid = tgrelid) as "table",
+    tgname as "trigger",
+    tgenabled
+from
+    pg_trigger
+where
+    tgenabled = 'D';
+</code>
+=====Getting a hierarchically-ordered list of org units=====
+<code sql>
+-- breadth-first (i.e. all first-level org units, then all second-level OUs, etc.)
+WITH RECURSIVE orgs AS
+(
+  SELECT id, shortname, name, parent_ou
+  FROM actor.org_unit
+  WHERE parent_ou IS NULL
+  UNION ALL
+  SELECT next.id, next.shortname, next.name, next.parent_ou
+  FROM actor.org_unit next
+  JOIN orgs prev ON next.parent_ou = prev.id
+)
+SELECT id, shortname, name, parent_ou
+FROM orgs;
+-- depth-first ("expanded tree" order, as in most org selector dropdown menus)
+WITH RECURSIVE orgs AS
+(
+  SELECT id, shortname, name, parent_ou, array[id] AS path
+  FROM actor.org_unit
+  WHERE parent_ou IS NULL
+  UNION
+  SELECT next.id, next.shortname, next.name, next.parent_ou, (prev.path || next.id) AS path
+  FROM actor.org_unit next
+  JOIN orgs prev ON next.parent_ou = prev.id
+)
+SELECT id, shortname, name, parent_ou
+FROM orgs ORDER BY path;
+</code>
+====== Cstore/json_query tips and tricks ======
+===== order_by using expressions =====
+You know how in SQL a SELECT statement's ORDER BY clause can order on expressions?  Like this:
+<code sql>SELECT foo FROM bar ORDER BY foo = 7 DESC, foo ASC;</code>
+That would give you a set of foo where 7 is your first result, and the rest of the results are ordered in ascending order.  There are times when this query (or similar) is handy. Really!
+json_query doesn't provide an obvious way to accomplish this, but you can achieve the equivalent like so (just showing the order_by clause for brevity's sake):
+<code>
+"order_by": [
+  {"class": "bar", "field": "foo", "transform": "numeric_eq", "params": [7], "direction": "desc"},
+  {"class": "bar", "field": "foo", "direction": "asc"}
+]
+</code>
+Since operators aren't directly supported in the order_by clause of a json_query, this uses the built-in function behind the = operator, which is NUMERIC_EQ().
+====== EG Behavior ======
+===== Override Receipt Templates Globally =====
+/openils/var/web/xul/server/skin/custom.js should contain:
+<code javascript>
+//////////////////////////////////////////////////////////////////////////
+// Overriding receipt templates globally
+try {
+    // In practice, this should be true in menu.js/opac.js, but not util_overlay.xul
+    if (typeof JSAN != 'undefined' && typeof JSON2js != 'undefined' && typeof xulG != 'undefined') {
+        var r = new XMLHttpRequest();
+        r.open("GET", xulG.url_prefix('/xul/server/skin/print_list_templates'), false);
+        r.send(null);
+        if (r.status == 200) {
+            JSAN.use('OpenILS.data');
+            var custom_data = new OpenILS.data(); custom_data.stash_retrieve();
+            var custom_templates = JSON2js( r.responseText );
+            for (var i in custom_templates) {
+                custom_data.print_list_templates[i] = custom_templates[i];
+            }
+            custom_data.stash('print_list_templates');
+            dump('Overriding receipt templates via custom.js\n');
+        }
+    }
+} catch(E) {
+    dump('Error overriding receipt templates in custom.js: ' + E + '\n');
+}
+</code>
+And /openils/var/web/xul/server/skin/print_list_templates should be a file just
+like the type of file you get when you use Export from the Receipt Template
+Editor.  You could trim the contents to override only specific templates.
+**Alternate version with per workstation OU settings**
+If you would like to manage print templates centrally with the ability to have different settings
+per Org Unit, this variant allows you to have default print_list_template and then a version for specific Org units, print_list_template-oushortname.
+<code javascript>
+//////////////////////////////////////////////////////////////////////////
+// Overriding receipt templates globally
+try {
+    // In practice, this should be true in menu.js/opac.js, but not util_overlay.xul
+    if (typeof JSAN != 'undefined' && typeof JSON2js != 'undefined' && typeof xulG != 'undefined') {
+        var r = new XMLHttpRequest();
+        //request the OU specific print_list_templates files
+        r.open("GET", xulG.url_prefix('/xul/server/skin/print_list_templates') + '-' + ses('ws_ou_shortname'), false);
+        r.send(null);
+        if (r.status != 200) {
+           //request the default print_list_templates file.
+           r.open("GET", xulG.url_prefix('/xul/server/skin/print_list_templates'), false);
+           r.send(null);
+        }
+        if (r.status == 200) {
+            JSAN.use('OpenILS.data');
+            var custom_data = new OpenILS.data(); custom_data.stash_retrieve();
+            var custom_templates = JSON2js( r.responseText );
+            for (var i in custom_templates) {
+                custom_data.print_list_templates[i] = custom_templates[i];
+            }
+            custom_data.stash('print_list_templates');
+            dump('Overriding receipt templates via custom.js\n');
+        }
+    }
+} catch(E) {
+    dump('Error overriding receipt templates in custom.js: ' + E + '\n');
+}
+</code>
+First /openils/var/web/xul/server/skin/print_list_templates-OUSHORTNAME is checked, then
+ /openils/var/web/xul/server/skin/print_list_templates is checked if the OU specific version doesn't
+exist.  Both files should be a file just
+like the type of file you get when you use Export from the Receipt Template
+Editor.  You could trim the contents to override only specific templates.
+===== Clear the added content cache (book covers, etc) from the command line =====
+While newer versions of Evergreen offer the ability to [[http://docs.evergreen-ils.org/2.9/_including_external_content_in_your_public_interface.html#_clear_external_added_content_cache|clear the cache for a given record through the staff client]], sometimes doing it from the command line is a better option.
+If you want to clear the cache for a given record by its database ID, you can do the following from the command line (substitute 12345678 for the record ID, and modify your memcached server address if necessary):
+<code bash>
+export MEMCACHED_SERVERS=127.0.0.1; memcdump | grep ^ac\\..*record_12345678 | xargs memcrm
+</code>
+Thank you Jeff Godin for this tip!
+===== Clear cached catalog search results =====
+Similar to the spell above, this can be used to clear all cached catalog search results and facets from memcached. This ordinarily isn't needed for production use, but can be useful when testing tweaks to search code or settings.
+<code bash>
+memcdump --servers localhost|grep open-ils.search | xargs -n 1 memcrm --servers localhost
+</code>
+===== Clear cached catalog search results for a single term =====
+This "cache killer" can be used to bypass cached results for a single search. Append your search term with a random unique string of characters preceded by a hyphen. As an example, a search term to re-execute a search for puffins, bypassing previous cached results could be:
+<code bash>
+puffins -oononromfh879ui
+</code>
+Thank you Mike Rylander for this tip!