scratchpad:random_magic_spells
Differences
This shows you the differences between two versions of the page.
scratchpad:random_magic_spells [2015/06/12 12:33] – [Regularly Scheduled Report Output Purging] rjs7 | scratchpad:random_magic_spells [2022/02/10 13:34] (current) – external edit 127.0.0.1 | ||
---|---|---|---|
Line 58: | Line 58: | ||
WHERE a.org_unit=1; | WHERE a.org_unit=1; | ||
</ | </ | ||
+ | |||
+ | 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 " | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | |||
+ | 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, | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
===== Cancel Query/ | ===== Cancel Query/ | ||
Line 175: | Line 207: | ||
status, | status, | ||
(SELECT holdable FROM config.copy_status WHERE id = status) AS " | (SELECT holdable FROM config.copy_status WHERE id = status) AS " | ||
+ | (SELECT opac_visible FROM config.copy_status WHERE id = status) AS " | ||
location, | location, | ||
(SELECT circulate FROM asset.copy_location WHERE id = location) AS "loc circulate", | (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 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, | circulate, | ||
holdable, | holdable, | ||
+ | opac_visible, | ||
age_protect, | age_protect, | ||
circ_modifier, | circ_modifier, | ||
Line 193: | Line 228: | ||
netscape.security.PrivilegeManager.enablePrivilege(" | netscape.security.PrivilegeManager.enablePrivilege(" | ||
</ | </ | ||
+ | |||
+ | ====== Performance ====== | ||
+ | =====Preload Your database into RAM===== | ||
+ | Find your database' | ||
+ | < | ||
+ | psql -U evergreen -h localhost -d postgres -c " | ||
+ | </ | ||
+ | |||
+ | Then, as the root or postgres user, run the following commands | ||
+ | < | ||
+ | cd / | ||
+ | cat * > /dev/null | ||
+ | </ | ||
+ | |||
+ | Replace ${pgversion} with your PostgreSQL version number, ex. 9.5. Replace ${oid} with the number returned by the database query. | ||
====== Utility ====== | ====== Utility ====== | ||
Line 427: | Line 477: | ||
</ | </ | ||
+ | ===== 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::XML, ARRAY[ARRAY[' | ||
+ | ), | ||
+ | ' ' | ||
+ | ) | ||
+ | FROM biblio.record_entry WHERE ... | ||
+ | </ | ||
====== Statistics ====== | ====== Statistics ====== | ||
Line 490: | Line 554: | ||
; | ; | ||
</ | </ | ||
+ | |||
+ | |||
+ | =====Show statistic usage on reports=== | ||
+ | |||
+ | <code sql> | ||
+ | select thecount.name, | ||
+ | reporter.template rt, | ||
+ | ( | ||
+ | select rtinner.name " | ||
+ | (select naming.name, | ||
+ | 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= | ||
+ | group by 1 | ||
+ | ) rtinner | ||
+ | |||
+ | where rr.template = any((' | ||
+ | group by 1 | ||
+ | |||
+ | ) as thecount | ||
+ | where | ||
+ | thecount.name = rt.name and | ||
+ | rt.owner= | ||
+ | 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 | ||
+ | |||
+ | </ | ||
+ | |||
+ | You will need to edit that query to look for reports owned by a particular actor.usr.id. Replace " | ||
====== Development ====== | ====== Development ====== | ||
Line 600: | Line 696: | ||
=====Listing disabled triggers===== | =====Listing disabled triggers===== | ||
- | < | + | <code sql> |
select | select | ||
(select nspname from pg_namespace where oid = (select relnamespace from pg_class where oid = tgrelid)) | (select nspname from pg_namespace where oid = (select relnamespace from pg_class where oid = tgrelid)) | ||
Line 613: | Line 709: | ||
</ | </ | ||
+ | =====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, | ||
+ | FROM actor.org_unit next | ||
+ | JOIN orgs prev ON next.parent_ou = prev.id | ||
+ | ) | ||
+ | SELECT id, shortname, name, parent_ou | ||
+ | FROM orgs; | ||
+ | |||
+ | -- depth-first (" | ||
+ | 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, | ||
+ | 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; | ||
+ | </ | ||
====== Cstore/ | ====== Cstore/ | ||
Line 619: | Line 745: | ||
You know how in SQL a SELECT statement' | You know how in SQL a SELECT statement' | ||
- | < | + | < |
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. | 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. | ||
Line 666: | Line 792: | ||
like the type of file you get when you use Export from the Receipt Template | like the type of file you get when you use Export from the Receipt Template | ||
Editor. | Editor. | ||
+ | |||
+ | **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/ | ||
+ | if (typeof JSAN != ' | ||
+ | var r = new XMLHttpRequest(); | ||
+ | //request the OU specific print_list_templates files | ||
+ | r.open(" | ||
+ | r.send(null); | ||
+ | if (r.status != 200) { | ||
+ | // | ||
+ | | ||
+ | | ||
+ | } | ||
+ | if (r.status == 200) { | ||
+ | JSAN.use(' | ||
+ | var custom_data = new OpenILS.data(); | ||
+ | var custom_templates = JSON2js( r.responseText ); | ||
+ | for (var i in custom_templates) { | ||
+ | custom_data.print_list_templates[i] = custom_templates[i]; | ||
+ | } | ||
+ | custom_data.stash(' | ||
+ | dump(' | ||
+ | } | ||
+ | } | ||
+ | } catch(E) { | ||
+ | dump(' | ||
+ | } | ||
+ | </ | ||
+ | |||
+ | First / | ||
+ | / | ||
+ | exist. | ||
+ | like the type of file you get when you use Export from the Receipt Template | ||
+ | Editor. | ||
+ | |||
+ | ===== Clear the added content cache (book covers, etc) from the command line ===== | ||
+ | |||
+ | While newer versions of Evergreen offer the ability to [[http:// | ||
+ | |||
+ | 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; | ||
+ | </ | ||
+ | |||
+ | 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 | ||
+ | </ |
scratchpad/random_magic_spells.1434126789.txt.gz · Last modified: 2022/02/10 13:33 (external edit)