request open-ils.actor opensrf.open-ils.system.ils_version

Doesn't have to be open-ils.actor any service will work.

http://acq.open-ils.org/gateway?service=open-ils.actor&method=opensrf.open-ils.system.ils_version

  
 psql -U <dbuser> <dbname>
 # update actor.usr set passwd = 'open-ils' where usrname = 'admin';
 # \q

   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

   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;

Usage:

   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;

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.

select pg_cancel_backend(backend_pid);

The backend_pid comes from the procpid column of pg_stat_activity.

select procpid, now()-query_start, current_query as duration from pg_stat_activity where current_query <> '<IDLE>' order by 2;

This will give you the current queries, ordered by duration. Reports are easy to spot because the reporter uses MD5sum values for table aliases.

This is useful for auditing who has been granted privileges where:

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
;

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;

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

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;

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 );

   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

    ~/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

#!/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

(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:

$str =~ s/([^A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg;

$str can now be passed in the query string safely, for example:

<a ref="page_given_string.pl?str=$str">LINK</a>

Likely, “page_given_string.pl” will want to decode $str back into something useful:

$str =~ s/\%([A-Fa-f0-9]{2})/pack('C', hex($1))/seg;

 
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;

For this example, how to include tag 590's with keyword searches:

1. Create an indexing entry in config.metabib_field:

    INSERT INTO config.metabib_field (field_class,name,xpath,format) VALUES (
      'keyword',
      '590',
      $$//marcxml:datafield[@tag="590"]$$,
      'marcxml'
    );

2. Get the id of that new index definition

    SELECT  id
      FROM  config.metabib_field
      WHERE field_class = 'keyword' AND name = '590';

3. Then restart the pertinent services ('open-ils.storage', 'open-ils.ingest') or simply restart all Perl services (use the -l flag if you're forcing it to use 'localhost' for a hostname):

   osrf_ctl.sh -a restart_perl

4. Then:
   1. Pull the required data from existing records (assuming the output of #2 is 15)

       INSERT INTO metabib.keyword_field_entry (FIELD,source,value)
         SELECT  15, record, agg_text(value)
           FROM  metabib.full_rec
           WHERE tag = '590'
           GROUP BY 1, 2;

      Or, for the more correct, but slower solution:

   2. Reingest all affected records:

       \o records_to_ingest.txt
       SELECT DISTINCT record
         FROM metabib.full_rec
         WHERE tag IN ('590');

       # mv ~postgres/records_to_ingest.txt ~opensrf
       # chown opensrf:opensrf ~opensrf/records_to_ingest.txt
       # su - opensrf
       $ for i in `cat records_to_ingest.txt`; do \
           echo "request open-ils.ingest open-ils.ingest.full.biblio.record $i"; \
         done | srfsh

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.

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();

UPDATE biblio.record_entry SET marc = regexp_replace(marc,E'<datafield tag="059".+?</datafield>','','g');
DELETE FROM metabib.full_rec WHERE tag = '059'

Export a list of biblio.record_entry.id entries to a file called records_to_ingest.txt. If you're using the \o command in psql, remember to use \t (tuples only) or you'll have to hand-edit the output file and trim the trailing and leading lines that are not data, such as (2516 rows). Otherwise, use a command like:

psql --username=evergreen --host=localhost --dbname=evergreen -A -q -t --command="select id from biblio.record_entry;" --output=records_to_ingest.txt

Review the output file, then:

for i in `cat records_to_ingest.txt`; do
  echo "request open-ils.ingest open-ils.ingest.full.biblio.record $i"; 
done | sudo su - opensrf -c srfsh

  grep -Hn duration: pg.*.log|grep -v vacuum|awk '{print $16 " " $1}'| sed 's/:/ /g'|awk '{print $1 "\t" $2 ":" $3}'|sort -run|head

  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

(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

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.

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
;

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.

1. Create a scratch table containing the list of ISBNs for the potential purchase, then copy the ISBNs into that table:

   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;

2. 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.

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
;

svn2cl --group-by-day --separate-daylogs -r 7200:HEAD svn/1.0-ILS/

--with-perl --enable-integer-datetimes

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 Dojo documentation re: The Package System and Custom Builds 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 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/.

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.

  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}...]