Evergreen Wordpress Library Bookshelf API Update

Readme.txt

Steps to setup remote updating of library bookshelfs for Evergreen libraries.

1. As of Library Bookshelf version 4.9 the plugin nees some changes made to allow updating via the REST API.

Apply the changes in the patch file.

patch -i librarybookshelf.patch

2. Install the application password wordpress plugin.  Then edit your wordpress user account and add an application password.  Record the password.

3. Setup a script on your evergreen utility server that uses the password you were given.

4. Run scripts on a regular schedule with cron


Testing:
You can use a curl command like the following to test out updating a bookshelf.

#This updates the library bookshelf with an ID of 5
curl --user 'username:aaaa bbbb cccc dddd eeee ffff' -i https://wordpress.example.org/wp-json/wp/v2/bookshelves/5 -X POST -d '{"meta":{"isbn":["341361","337570","337571","339433"],"alt":["one","two","three","four"]}}' -H "Content-Type: application/json"

#Grab the post to see results
curl --user 'username:aaaa bbbb cccc dddd eeee ffff' -i https://wordpress.example.org/wp-json/wp/v2/bookshelves/5 -X GET

larl-new-release-update.pl

#!/usr/bin/perl
use feature qw(fc);
use lib qw(../);
use File::Path qw(make_path remove_tree);
use strict;
use Data::Dumper;
use DateTime;
use utf8;
use Encode;
use DateTime;
use XML::Simple;
use Getopt::Long;
use DBI;
use LWP::UserAgent;
use MIME::Base64;
use JSON;


##########
my $catalogurl = 'https://egcatalog.larlexample.org';  #set to your catalog
my $imgsize = 'medium'; # 'small','medium','large'

#Wordpress site settings
my $wpsite = 'https://larlexample.org';  #Your wordpress site
my $wpusername = 'username'; #wordpress username, for an account that can update the library bookshelf posts
my $wppassword = 'aaaa bbbb cccc dddd eeee'; #suggest using application password plugin for wordpress

my $xmlconf = "/openils/conf/opensrf.xml";

### You will need to adjust the SQL queries to match your local stat cats 


GetOptions (
"xmlconfig=s" => \$xmlconf
)
or die("Error in command line arguments\nYou can specify
--xmlconfig  pathto_opensrf.xml
\n");

if(! -e $xmlconf)
{
        print "I could not find the xml config file: $xmlconf\nYou can specify the path when executing this script --xmlconfig configfilelocation\n";
        exit 0;
}

our $dbHandler;

my $dt = DateTime->now(time_zone => "local");
my $fdate = $dt->ymd;
my $ftime = $dt->hms;
my $dateString = "$fdate $ftime";

my %dbconf = %{getDBconnects($xmlconf)};

#print Dumper(%dbconf);

my $dbh = DBI->connect("DBI:Pg:dbname=$dbconf{db};host=$dbconf{dbhost};port=$dbconf{port}",$dbconf{dbuser},$dbconf{dbpass},{AutoCommit => 0})
  or die "Couldn't connect to database: " . DBI->errstr;

#Query to create temp table that holds all new items
my $temptbl = <<'SQL';
CREATE TEMPORARY TABLE new_items
ON COMMIT DROP
AS (
-- New Items

select rssr.id bibid, rssr.title, rssr.author, concat_ws(' ',acnp.label, acn.label) "Call Number"
, rssr.pubdate, acp.circ_modifier
, string_agg(distinct aou.shortname, ', ') "Branches", count(acp.id) "Copy Count"
, string_agg(distinct ccs.name,', ') "Copy Status", min(acp.active_date) "First Active Date"
, string_agg(distinct acl.name,', ') "Shelving Locations"
, string_agg(distinct asce21.value, ', ') as "Book Genre"
, string_agg(distinct asce20.value, ', ') as "Special Collections"
, string_agg(distinct asce19.value, ', ') as "Fiction/NF"
, string_agg(distinct asce18.value, ', ') as "Format"
, string_agg(distinct asce17.value, ', ') as "Reading Watching Level"
, string_agg(distinct asce16.value, ', ') as "Genre Music"
, string_agg(distinct asce15.value, ', ') as "Genre Movie"
, first(rssr.isbn[1]) ISBN
from asset.copy acp
join actor.org_unit aou on aou.id=acp.circ_lib
join config.copy_status ccs on ccs.id=acp.status
join asset.call_number acn on acn.id=acp.call_number
left outer join asset.call_number_prefix acnp on acnp.id=acn.prefix and acnp.id!=(-1)
join biblio.record_entry bre on acn.record=bre.id
join reporter.super_simple_record rssr on rssr.id=bre.id
join asset.copy_location acl on acl.id=acp.location
-- 15 = Movie Genre
left outer join asset.stat_cat_entry_copy_map ascecm15 on ascecm15.owning_copy=acp.id and ascecm15.stat_cat=15
left outer join asset.stat_cat_entry asce15 on asce15.id=ascecm15.stat_cat_entry

-- 16 = Music Genre
left outer join asset.stat_cat_entry_copy_map ascecm16 on ascecm16.owning_copy=acp.id and ascecm16.stat_cat=16
left outer join asset.stat_cat_entry asce16 on asce16.id=ascecm16.stat_cat_entry

-- 17 = Reading Level
left outer join asset.stat_cat_entry_copy_map ascecm17 on ascecm17.owning_copy=acp.id and ascecm17.stat_cat=17
left outer join asset.stat_cat_entry asce17 on asce17.id=ascecm17.stat_cat_entry

-- 18 = Format
left outer join asset.stat_cat_entry_copy_map ascecm18 on ascecm18.owning_copy=acp.id and ascecm18.stat_cat=18
left outer join asset.stat_cat_entry asce18 on asce18.id=ascecm18.stat_cat_entry

-- 19 = Fiction/NF
left outer join asset.stat_cat_entry_copy_map ascecm19 on ascecm19.owning_copy=acp.id and ascecm19.stat_cat=19
left outer join asset.stat_cat_entry asce19 on asce19.id=ascecm19.stat_cat_entry

-- 20 - Special Collections
left outer join asset.stat_cat_entry_copy_map ascecm20 on ascecm20.owning_copy=acp.id and ascecm20.stat_cat=20
left outer join asset.stat_cat_entry asce20 on asce20.id=ascecm20.stat_cat_entry

-- 21 - Book Genre
left outer join asset.stat_cat_entry_copy_map ascecm21 on ascecm21.owning_copy=acp.id and ascecm21.stat_cat=21
left outer join asset.stat_cat_entry asce21 on asce21.id=ascecm21.stat_cat_entry

where
acp.circ_lib in (SELECT id FROM actor.org_unit_descendants(101))
and acp.active_date > now()-'5 months'::interval
-- and acp.location=122
and acp.status in (0,1,6,8,5)
--and acp.circ_modifier='Large Print Book'
and acp.circ_modifier not in ('Magazine','ILL')
and not acp.deleted
and acp.opac_visible
and bre.create_date > now()-'1 year'::interval

group by 1,2,3,4,5,6
order by 10 desc
limit 4000
)
;
SQL


### Queries and metadata for library bookshelves on larl.org
# For each library bookshelf you want to update, add an entry in the next data
# structure.  The first key is the wordpress ID for the bookshelf.
# The query pulls out data from the results of the main new books query.
# the Label and Desc are just for documentation
my %shelfdata = ( '36147' => {
                     'label' => 'New Adult Books',
                     'desc' => 'Latest New Adult Books that have arrived',
                     'sql' => qq{
select bibid,title,author from
new_items ni
where
ni."Shelving Locations"~'New'
and ni."Format" = 'Book'
and ni."Reading Watching Level" = 'Adult'

order by ni."First Active Date" desc
limit 60
;
                     },
             },
             '761' => {
                     'label' => 'New Young Adult Books',
                     'desc' => 'Latest New Young Adult Books that have arrived',
                     'sql' => qq{
select bibid,title,author from
new_items ni
where
ni."Shelving Locations"~'New'
and ni."Format" = 'Book'
and ni."Reading Watching Level" = 'Youth'

order by ni."First Active Date" desc
limit 60
;
                     },
            },
             '36148' => {
                     'label' => 'New DVDS',
                     'desc' => 'Latest DVDS that have arrived',
                     'sql' => qq{
select bibid,title,author from
new_items ni
where
--ni."Shelving Locations"~'New'
ni."Format" = 'DVD'
--and ni."Reading Watching Level" = 'Juvenile'

order by ni."First Active Date" desc
limit 90
;
                     },
             },
             '36280' => {
                     'label' => 'New Audiobooks',
                     'desc' => 'Latest Audiobooks that have arrived.',
                     'sql' => qq{
select bibid,title,author from
new_items ni
where
--ni."Shelving Locations"~'New'
ni."Format" in ('CD MP3 AudioBk','CD AudioBk')
and ni."Reading Watching Level" = 'Adult'

order by ni."First Active Date" desc
limit 70
;
                     },
             },
             '36216' => {
                     'label' => 'New Books for Children',
                     'desc' => 'Latest Childrens Books that have arrived.',
                     'sql' => qq{
select bibid,title,author from
new_items ni
where
ni."Shelving Locations"~'New'
and ni."Format" = 'Book'
and ni."Reading Watching Level" = 'Juvenile'

order by ni."First Active Date" desc
limit 70
;
                     },
             },

);

$dbh->do($temptbl) or die $dbh->errstr;

#Run through shelf data, and run queries

foreach my $shelfid (keys %shelfdata) {

  print "ShelfID = $shelfid\n";
  $shelfdata{$shelfid}{'results'} = $dbh->selectall_hashref($shelfdata{$shelfid}{'sql'},'bibid');

}

$dbh->disconnect();

#print Dumper(\%shelfdata);

#print Dumper(\$data);

### Check each bibid to see if we have cover art or not, remove if we don't
my $ua = LWP::UserAgent->new(timeout => 10);

foreach my $shelfid (keys %shelfdata) {
  foreach my $bibid (keys $shelfdata{$shelfid}{'results'}) {
    my $url = $catalogurl.'/opac/extras/ac/jacket/'.$imgsize.'/r/'.$bibid;
    my $response = $ua->get($url);
    if($response->is_error){
        delete $shelfdata{$shelfid}{'results'}{$bibid};
        print "Cover art for $bibid doesn't exist, exclude - $url\n";
    }
    else { #image exists - add to arrays
      push(@{$shelfdata{$shelfid}{'meta'}{'isbn'} }, $bibid);
      push(@{$shelfdata{$shelfid}{'meta'}{'alt'} }, $shelfdata{$shelfid}{'results'}{$bibid}{'title'});
    }
  }
}

print Dumper(\%shelfdata);

#### Create json data and update bookshelves

my $json = JSON->new;
my $encoded = encode_base64($wpusername.':'.$wppassword);

#my uawp = LWP::UserAgent->new(timeout => 10);

foreach my $shelfid (keys %shelfdata) {
  print 'ShelfID: '.$shelfid.'  Name: '.$shelfdata{$shelfid}{'label'}."\n";
  my $wpdata = $json->utf8(1)->encode( { 'meta' => $shelfdata{$shelfid}{'meta'}} );
  print $wpdata."\n\n";

  my $url = $wpsite.'/wp-json/wp/v2/bookshelves/'.$shelfid;
  my $header = ['Content-Type' => 'application/json; charset=UTF-8',
                'Authorization' => 'Basic '.$encoded];

  my $req = HTTP::Request->new('POST', $url, $header, $wpdata);
  my $resp = $ua->request($req);
  #print $url."\n";
  #print $req->as_string;
  #print $resp->as_string;

  if ($resp->is_success) {
    my $message = $resp->decoded_content;
    #print $json->decode($message);
  }
  else {
    print "HTTP POST error code: ", $resp->code, "\n";
    print "HTTP POST error message: ", $resp->message, "\n";
  }
}

exit;


sub getDBconnects
{
        my $openilsfile = @_[0];
        my $xml = new XML::Simple;
        my $data = $xml->XMLin($openilsfile);
        my %conf;
        $conf{"dbhost"}=$data->{default}->{apps}->{"open-ils.storage"}->{app_settings}->{databases}->{database}->{host};
        $conf{"db"}=$data->{default}->{apps}->{"open-ils.storage"}->{app_settings}->{databases}->{database}->{db};
        $conf{"dbuser"}=$data->{default}->{apps}->{"open-ils.storage"}->{app_settings}->{databases}->{database}->{user};
        $conf{"dbpass"}=$data->{default}->{apps}->{"open-ils.storage"}->{app_settings}->{databases}->{database}->{pw};
        $conf{"port"}=$data->{default}->{apps}->{"open-ils.storage"}->{app_settings}->{databases}->{database}->{port};
        ##print Dumper(\%conf);
        return \%conf;

}

exit;

librarybookshelf.patch

*** class-bookshelves-post-type.php     2019-12-18 00:17:36.000000000 -0600
--- ../../wordpress-test/wp-content/plugins/library-bookshelves/class-bookshelves-post-type.php 2020-03-05 11:23:38.000000000 -0600
***************
*** 55,63 ****
                                'public'       => true,
                                'show_in_rest' => true,
                                'description'  => __( 'This is a bookshelf' ),
!                               'supports'     => array( 'title', 'revisions', 'page-attributes' ),
                        )
                );
                flush_rewrite_rules();
        }

--- 55,93 ----
                                'public'       => true,
                                'show_in_rest' => true,
                                'description'  => __( 'This is a bookshelf' ),
!                               'supports'     => array( 'title', 'revisions', 'page-attributes', 'custom-fields' ),
                        )
                );
+                 register_post_meta('bookshelves', 'isbn',
+                   array(
+                    'single'       => true,
+                    'type'         => 'array',
+                    'show_in_rest' => array(
+                      'schema' => array(
+                        'type'  => 'array',
+                        'items' => array(
+                          'type' => 'string',
+                        ),
+                      ),
+                    ),
+                  )
+                 );
+
+                 register_post_meta('bookshelves', 'alt',
+                   array(
+                    'single'       => true,
+                    'type'         => 'array',
+                    'show_in_rest' => array(
+                      'schema' => array(
+                        'type'  => 'array',
+                        'items' => array(
+                          'type' => 'string',
+                        ),
+                      ),
+                    ),
+                  )
+                 );
+
                flush_rewrite_rules();
        }