scratchpad:random_magic_spells
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
scratchpad:random_magic_spells [2016/03/10 16:15] – CLI instructions for clearing added content cache dbs | scratchpad:random_magic_spells [2024/08/30 13:01] (current) – mmorgan | ||
---|---|---|---|
Line 228: | 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 462: | 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 525: | 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 648: | 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 755: | Line 846: | ||
Thank you Jeff Godin for this tip! | 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 | ||
+ | </ | ||
+ | |||
+ | ===== Clear cached catalog search results for a single term ===== | ||
+ | |||
+ | This "cache killer" | ||
+ | |||
+ | <code bash> | ||
+ | puffins -oononromfh879ui | ||
+ | </ | ||
+ | |||
+ | Thank you Mike Rylander for this tip! |
scratchpad/random_magic_spells.1457644548.txt.gz · Last modified: 2022/02/10 13:33 (external edit)