Unveiling Hidden Things in Evergreen

Galen Charlton
2017 Evergreen International Conference

Time...

First substantial commit on 4 February 2005

... and tide

commit 940e152e588a9b1c1b4f18cbbecf46691cb2f58c
Author: phasefx <phasefx@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date:   Fri Feb 4 22:08:15 2005 +0000

    Initial revision
    
    
    git-svn-id: svn://svn.open-ils.org/ILS/trunk@2 dcc99617-32d9-48b4-a31d-7c20da2025e4

Everything is better with a friend

Particularly if you're a cat who cannot be trusted with a mouse

If you give a cat a library card...

How does Evergreen manage your friends?

It puts them in buckets, of course

Cats on a Farm by davidmaitland on Flickr CC-BY

CREATE TABLE container.user_bucket (
    id  SERIAL  PRIMARY KEY,
    owner   INT NOT NULL
            REFERENCES actor.usr (id)
                ON DELETE CASCADE
                ON UPDATE CASCADE
                DEFERRABLE
                INITIALLY DEFERRED,
    name    TEXT    NOT NULL,
    btype   TEXT    NOT NULL DEFAULT 'misc' REFERENCES container.user_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
    description TEXT,
    pub BOOL    NOT NULL DEFAULT FALSE,
    create_time TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
    CONSTRAINT ub_name_once_per_owner UNIQUE (owner,name,btype)
);

CREATE TABLE container.user_bucket_item (
    id      SERIAL  PRIMARY KEY,
    bucket      INT NOT NULL
                REFERENCES container.user_bucket (id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
                    DEFERRABLE
                    INITIALLY DEFERRED,
    target_user INT NOT NULL
                REFERENCES actor.usr (id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
                    DEFERRABLE
                    INITIALLY DEFERRED,
    pos         INT,
    create_time TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW()
);

User
Buckets

           code           |            label            
--------------------------+-----------------------------
 misc                     | Miscellaneous
 folks                    | Friends
 folks:pub_book_bags.view | List Published Book Lists
 folks:pub_book_bags.add  | Add to Published Book Lists
 folks:circ.view          | View Circulations
 folks:circ.renew         | Renew Circulations
 folks:circ.checkout      | Checkout Items
 folks:hold.view          | View Holds
 folks:hold.cancel        | Cancel Holds

User
Bucket
Types

How does it work?

A bucket of type "folks" stores a user's friends

id          | 3
owner       | 236
name        | folks
btype       | folks
description | 
pub         | f
create_time | 2017-04-01 20:35:14.013986+00

id          | 3
bucket      | 3
target_user | 237
pos         | 
create_time | 2017-04-01 20:35:46.26597+00

Each entry in a bucket is a friend

But friendship counts only if it's mutual!

Handing over the keys

It's not enough to be friendly... specific permissions must be delegated. This is done usingthe other "folks:" bucket types.

id          | 2
owner       | 236
name        | folks:hold.view
btype       | folks:hold.view
description | 
pub         | f
create_time | 2017-04-01 20:28:32.672987+00

Permission delegation does NOT need to be mutual

// here be Freddie, operating srfsh

srfsh# request open-ils.actor open-ils.actor.friends.perms.apply \
       "cd365b8ca54495c900dd0a851ccb8cff" 236, 237, "hold.view"

Received Data: "1"

// and here I am later, checking on his hold request

srfsh# request open-ils.circ open-ils.circ.holds.retrieve \
       "9eb6c9709849ce2a85bb678c64c77449", 236                                                                   

Received Data: [
  { 
    "__c":"ahr",
    "__p":[
      null,
      null,
      null,
      null,
      "f",
      null,
      null,
      null,
      null,
      "T",
      null,
      263,

Delegation at work

Why is this a treasure?

• The underlying mechanism, an experiment from 2008/2009... still works.

• Expand the permission delegation mostly entails adding some calls to  OpenILS::Application::Actor::Friends->friend_perm_allowed()

• There are lots of things we could do with user buckets as well

Why is this buried?

• srfsh is not a recommended user interface
  • Though a staff-side UI for user buckets is coming soon!
• Nor do I recommend giving cats direct open-ils.cstore access
  • Though open-ils.pcrud permissions for user buckets are coming soon!
• Only "folks:hold_view" and "folks:circ_view" do anything at present

Now let's jump to 2010: qstore

It's full of queries

• qstore, or Query Store, provides an API for running stored, pre-defined queries

• Queries are stored in the query schema as structures that can be turned into SQL SELECT (and UNION, INTERSECT, and EXCEPT) statements

• Features
  • bind parameters
  • streaming or atomic returns
  • CASE expressions
  • Can use stored functions

Let's build a query

query.stored_query

query.from_relation

-[ RECORD 1 ]-+-------
id            | 26
type          | SELECT
use_all       | f
use_distinct  | f
from_clause   | 11
where_clause  | 
having_clause | 
limit_count   | 
offset_count  | 

-[ RECORD 1 ]---+------------------------------------
id              | 11
type            | RELATION
table_name      | reporter.materialized_simple_record
class_name      | 
subquery        | 
function_call   | 
table_alias     | rmsr
parent_relation | 
seq_no          | 1
join_type       | 
on_clause       | 

-[ RECORD 1 ]---+---------
id              | 11
type            | RELATION
table_name      | 
class_name      | rmsr
subquery        | 
function_call   | 
table_alias     | 
parent_relation | 
seq_no          | 1
join_type       | 
on_clause       | 

or

Let's build a query

query.expr_xcol

query.select_item

-[ RECORD 1 ]+-------
id           | 61
parenthesize | f
parent_expr  | 
seq_no       | 1
table_alias  | rmsr
column_name  | title
negate       | f
-[ RECORD 2 ]+-------
id           | 62
parenthesize | f
parent_expr  | 
seq_no       | 1
table_alias  | rmsr
column_name  | author
negate       | f

-[ RECORD 1 ]+---
id           | 48
stored_query | 26
seq_no       | 1
expression   | 61
column_alias | 
grouped_by   | f
-[ RECORD 2 ]+---
id           | 50
stored_query | 26
seq_no       | 2
expression   | 62
column_alias | 
grouped_by   | f

Querying the query

srfsh# open open-ils.qstore                                               
Service open-ils.qstore opened
srfsh# request open-ils.qstore open-ils.qstore.prepare 26                 

Received Data: {
  "token":"5_1491150953_5837",
  "bind_variables":{
    
  }
}

------------------------------------
Request Completed Successfully
Request Time in seconds: 0.006293
------------------------------------
srfsh# request open-ils.qstore open-ils.qstore.columns "5_1491150953_5837" 

Received Data: [
  "title",
  "author"
]

Querying the query

srfsh# request open-ils.qstore open-ils.qstore.sql "5_1491150953_5837"    

// turned the "\n" into literal newlines for this slide
Received Data: "SELECT
   \"rmsr\".title,
   \"rmsr\".author 
FROM
   reporter.materialized_simple_record AS \"rmsr\";
"

Running the query

srfsh# request open-ils.qstore open-ils.qstore.execute "5_1491150953_5837"

Received Data: [
  "la canzone italiana del novecento",
  "baldazzi, gianfranco"
]

Received Data: [
  "le concerto",
  "ferchault, guy"
]

Received Data: [
  "piano concertos nos 2, 3 & 4",
  "kabalevsky, dmitry borisovich"
]

Received Data: [
  "huntington student recital, dec 6 1999",
  null
]

qstore methods

• open-ils.qstore.prepare
• open-ils.qstore.sql
• open-ils.qstore.columns
• open-ils.qstore.param_list
• open-ils.qstore.bind_param
• open-ils.qstore.execute[.atomic]
• open-ils.qstore.messages
• open-ils.qstore.finish
  

Why is this a treasure?

• qstore can do some things json_query cannot
  • UNIONs, INTERSECTs, and EXCEPTs
  • CASE expressions
  • Not tied to the IDL (although it's there when you want it)
• The batch patron edit feature will start using it.

Why is this buried?

• Only available documentation was sitting in a tarball in the open-ils-dev archives
  • Until now!  See LP#1678638.
• No editor... or really, help of any kind, for constructing the stored queries
  • Pg::SQL::Parser or its equivalent could be basis for an SQL-to-stored-query converter.

srfsh.py

srfsh.py in action

opensrf@dev-galen:~$ /home/gmc/OpenSRF/src/python/srfsh.py
Loading module oils.srfsh...OK.
srfsh# idl show class acp
Loading and parsing IDL...OK
------------------------------------------------------------
Item [acp] asset.copy
------------------------------------------------------------
[0]  age_protect [link @crahp]
[1]  alert_message [text]
[2]  barcode [text]
[3]  call_number [link @acn]
[4]  circ_as_type [text]
[5]  circ_lib [org_unit]
[6]  circ_modifier [link @ccm]
[7]  circulate [bool]
[8]  copy_number [text]
[9]  create_date [timestamp]
[10] active_date [timestamp]
[11] creator [link @au]

Another example

srfsh# router service-stats

Received Data: {
    "open-ils.circ":161,
    "open-ils.qstore":10,
    "open-ils.cat":0,
    "open-ils.actor":11,
    "open-ils.trigger":0,
    "open-ils.permacrud":0,
    "open-ils.auth":2,
    "open-ils.auth_proxy":0,
    "open-ils.url_verify":0,
    "open-ils.supercat":26,
    "open-ils.collections":0,
    "open-ils.storage":2,
    "open-ils.pcrud":0,
    "open-ils.vandelay":0,
    "opensrf.dbmath":0,

Configuration

<?xml version="1.0"?>
<!-- This file follows the standard bootstrap config file layout found in opensrf_core.xml -->
<srfsh>
  <router_name>router</router_name>
  <domain>private.localhost</domain>
  <username>opensrf</username>
  <passwd>IAmTheVeryModelOfAModernMajorPassword</passwd>
  <port>5222</port>
  <logfile>/openils/var/log/srfsh.log</logfile>
  <loglevel>4</loglevel>
  <client>true</client>
  <plugins>
    <plugin module="oils.srfsh"/>
  </plugins>
</srfsh

Plugins

def handle_org_setting(srfsh, args):
    ''' Retrieves the requested org setting.

    Arguments:
        org unit id,
        org setting name
    '''

    org_unit = args[0]
    setting = args[1]

    srfsh.handle_request([
        'open-ils.actor', 
        'open-ils.actor.ou_setting.ancestor_default', 
        org_unit, 
        ',"%s"' % setting
    ])

# ...

srfsh.add_command(command = 'org_setting', handler = handle_org_setting)

Buried...

• Well, it's a bit of Python living in a C/Perl/JavaScript project (hi Bill!)

Treasure

• Easily extendable
  • Has notion of plugins
  • No recompile steps needed

What buried treasure do you know of?

Thanks

Galen Charlton
Equinox Open Library Initiative
@gmcharlt