Evergreen Data Models and Access
This chapter was taken from Dan Scott's Developer Workshop, February 2010.
Exploring the Database Schema
The database schema is tied pretty tightly to PostgreSQL. Although PostgreSQLdatabasesPostgreSQL
adheres closely to ANSI SQL standards, the use of schemas, SQL functionsANSI
implemented in both plpgsql and plperl, and PostgreSQL’s native full-text
search would make it… challenging… to port to other database platforms.
A few common PostgreSQL interfaces for poking around the schema and
manipulating data are:
psql (the command line client)databasesPostgreSQLpsql
pgadminIII (a GUI client).databasesPostgreSQLpgadminIII
Or you can read through the source files in Open-ILS/src/sql/Pg.
Let’s take a quick tour through the schemas, pointing out some highlights
and some key interdependencies:
actor.org_unit → asset.copy_location
actor.usr → actor.card
biblio.record_entry → asset.call_number → asset.copy
config.metabib_field → metabib.*_field_entry
This documentation also contains an Appendix for the Evergreen .
Database access methods
You could use direct access to the database via Perl DBI, JDBC, etc,
but Evergreen offers several database CRUD services for
creating / retrieving / updating / deleting data. These avoid tying
you too tightly to the current database schema and they funnel database
access through the same mechanism, rather than tying up connections
with other interfaces.
Evergreen Interface Definition Language (IDL)
Evergreen Interface Definition Language (IDL)
Defines properties and required permissions for Evergreen classes.
To reduce network overhead, a given object is identified via a
class-hint and serialized as a JSON array of properties (no named properties).
As of 1.6, fields will be serialized in the order in which they appear
in the IDL definition file, and the is_new / is_changed / is_deleted
properties are automatically added. This has greatly reduced the size of
the fm_IDL.xml file and makes DRY people happier :)
… oils_persist:readonly tells us, if true, that the data lives in the database, but is pulled from the SELECT statement defined in the <oils_persist:source_definition>
child element
IDL basic example (config.language_map)
<class id="clm" controller="open-ils.cstore open-ils.pcrud"
oils_obj:fieldmapper="config::language_map"
oils_persist:tablename="config.language_map"
reporter:label="Language Map" oils_persist:field_safe="true">
<fields oils_persist:primary="code" oils_persist:sequence="">
<field reporter:label="Language Code" name="code"
reporter:selector="value" reporter:datatype="text"/>
<field reporter:label="Language" name="value"
reporter:datatype="text" oils_persist:i18n="true"/>
</fields>
<links/>
<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
<actions>
<create global_required="true" permission="CREATE_MARC_CODE">
<retrieve global_required="true"
permission="CREATE_MARC_CODE UPDATE_MARC_CODE DELETE_MARC_CODE">
<update global_required="true" permission="UPDATE_MARC_CODE">
<delete global_required="true" permission="DELETE_MARC_CODE">
</actions>
</permacrud>
</class>
The class element defines the attributes and permissions for classes,
and relationships between classes.
Evergreen Interface Definition Language (IDL)class element
The id attribute on the class element defines the class hint that is
used everywhere in Evergreen.
The controller attribute defines the OpenSRF
services that provide access to the data for the class objects.
The oils_obj::fieldmapper attribute defines the name of the class that
is generated by OpenILS::Utils::Fieldmapper.
The oils_persist:tablename attribute defines the name of the table
that contains the data for the class objects.
The reporter interface uses reporter:label attribute values in
the source list to provide meaningful class and attribute names. The
open-ils.fielder service generates a set of methods that provide direct
access to the classes for which oils_persist:field_safe is true. For
example,
srfsh# request open-ils.fielder open-ils.fielder.clm.atomic \
{"query":{"code":{"=":"eng"}}}
Received Data: [
{
"value":"English",
"code":"eng"
}
]
The fields element defines the list of fields for the class.
Evergreen Interface Definition Language (IDL)fields element
The oils_persist:primary attribute defines the column that acts as
the primary key for the table.
The oils_persist:sequence attribute holds the name of the database
sequence.
Each field element defines one property of the class.
Evergreen Interface Definition Language (IDL)field element
The name attribute defines the getter/setter method name for the field.
The reporter:label attribute defines the attribute name as used in
the reporter interface.
The reporter:selector attribute defines the field used in the reporter
filter interface to provide a selectable list. This gives the user a more
meaningful access point than the raw numeric ID or abstract code.
The reporter:datatype attribute defines the type of data held by
this property for the purposes of the reporter.
The oils_persist:i18n attribute, when true, means that
translated values for the field’s contents may be accessible in
different locales.
Evergreen Interface Definition Language (IDL)permacrud element
The permacrud element defines the permissions (if any) required
to create, retrieve, update,
and delete data for this
class. open-ils.permacrud must be defined as a controller for the class
for the permissions to be applied.
Each action requires one or more permission values that the
user must possess to perform the action.
If the global_required attribute is true, then the user must
have been granted that permission globally (depth = 0) to perform
the action.
The context_field attribute denotes the <field> that identifies
the org_unit at which the user must have the pertinent permission.
Evergreen Interface Definition Language (IDL)action element
An action element may contain a <context_field> element that
defines the linked class (identified by the link attribute) and
the field in the linked class that identifies the org_unit where
the permission must be held.
Evergreen Interface Definition Language (IDL)context_field element
If the <context_field> element contains a jump attribute,
then it defines a link to a link to a class with a field identifying
the org_unit where the permission must be held.
Reporter data types and their possible values
bool: Boolean true or false
id: ID of the row in the database
int: integer value
interval: PostgreSQL time interval
link: link to another class, as defined in the <links>
element of the class definition
money: currency amount
org_unit: list of org_units
text: text value
timestamp: PostgreSQL timestamp
IDL example with linked fields (actor.workstation)
Just as tables often include columns with foreign keys that point
to values stored in the column of a different table, IDL classes
can contain fields that link to fields in other classes. The <links>
element defines which fields link to fields in other classes, and
the nature of the relationship:
<class id="aws" controller="open-ils.cstore"
oils_obj:fieldmapper="actor::workstation"
oils_persist:tablename="actor.workstation"
reporter:label="Workstation">
<fields oils_persist:primary="id"
oils_persist:sequence="actor.workstation_id_seq">
<field reporter:label="Workstation ID" name="id"
reporter:datatype="id"/>
<field reporter:label="Workstation Name" name="name"
reporter:datatype="text"/>
<field reporter:label="Owning Library" name="owning_lib"
reporter:datatype="org_unit"/>
<field reporter:label="Circulations" name="circulations"
oils_persist:virtual="true" reporter:datatype="link"/>
</fields>
<links>
<link field="owning_lib" reltype="has_a" key="id"
map="" class="aou"/>
<link field="circulations" reltype="has_many" key="workstation"
map="" class="circ"/>
<link field="circulation_checkins" reltype="has_many"
key="checkin_workstation" map="" class="circ"/>
</links>
</class>
This field includes an oils_persist:virtual attribute with the value of
true, meaning that the linked class circ is a virtual class.
The <links> element contains 0 or more <link> elements.
Each <link> element defines the field (field) that links to a different
class (class), the relationship (rel_type) between this field and the target
field (key). If the field in this class links to a virtual class, the (map)
attribute defines the field in the target class that returns a list of matching
objects for each object in this class.
open-ils.cstore data access interfaces
cstore
For each class documented in the IDL, the open-ils.cstore service
automatically generates a set of data access methods, based on the
oils_persist:tablename class attribute.
For example, for the class hint clm, cstore generates the following
methods with the config.language_map qualifer:
open-ils.cstore.direct.config.language_map.id_list {"code" { "like": "e%" } }
Retrieves a list composed only of the IDs that match the query.
open-ils.cstore.direct.config.language_map.retrieve "eng"
Retrieves the object that matches a specific ID.
open-ils.cstore.direct.config.language_map.search {"code" : "eng"}
Retrieves a list of objects that match the query.
open-ils.cstore.direct.config.language_map.create <_object_>
Creates a new object from the passed in object.
open-ils.cstore.direct.config.language_map.update <_object_>
Updates the object that has been passed in.
open-ils.cstore.direct.config.language_map.delete "eng"
Deletes the object that matches the query.
open-ils.pcrud data access interfaces
pcrud
For each class documented in the IDL, the open-ils.pcrud service
automatically generates a set of data access methods, based on the
oils_persist:tablename class attribute.
For example, for the class hint clm, open-ils.pcrud generates the following
methods that parallel the open-ils.cstore interface:
open-ils.pcrud.id_list.clm <_authtoken_>, { "code": { "like": "e%" } }
open-ils.pcrud.retrieve.clm <_authtoken_>, "eng"
open-ils.pcrud.search.clm <_authtoken_>, { "code": "eng" }
open-ils.pcrud.create.clm <_authtoken_>, <_object_>
open-ils.pcrud.update.clm <_authtoken_>, <_object_>
open-ils.pcrud.delete.clm <_authtoken_>, "eng"
Transaction and savepoint control
Both open-ils.cstore and open-ils.pcrud enable you to control database transactions
to ensure that a set of operations either all succeed, or all fail,
atomically:
open-ils.cstore.transaction.begin
open-ils.cstore.transaction.commit
open-ils.cstore.transaction.rollback
open-ils.pcrud.transaction.begin
open-ils.pcrud.transaction.commit
open-ils.pcrud.transaction.rollback
At a more granular level, open-ils.cstore and open-ils.pcrud enable you to set database
savepoints to ensure that a set of operations either all succeed, or all
fail, atomically, within a given transaction:
open-ils.cstore.savepoint.begin
open-ils.cstore.savepoint.commit
open-ils.cstore.savepoint.rollback
open-ils.pcrud.savepoint.begin
open-ils.pcrud.savepoint.commit
open-ils.pcrud.savepoint.rollback
Transactions and savepoints must be performed within a stateful
connection to the open-ils.cstore and open-ils.pcrud services.
In srfsh, you can open a stateful connection using the open
command, and then close the stateful connection using the close
command - for example:
srfsh# open open-ils.cstore
... perform various transaction-related work
srfsh# close open-ils.cstore
JSON Queries
JSON
Beyond simply retrieving objects by their ID using the \*.retrieve
methods, you can issue queries against the \*.delete and \*.search
methods using JSON to filter results with simple or complex search
conditions.
For example, to generate a list of barcodes that are held in a
copy location that allows holds and is visible in the OPAC:
srfsh# request open-ils.cstore open-ils.cstore.json_query
{"select": {"acp":["barcode"], "acpl":["name"]},
"from": {"acp":"acpl"},
"where": [
{"+acpl": "holdable"},
{"+acpl": "opac_visible"}
]}
Received Data: {
"barcode":"BARCODE1",
"name":"Stacks"
}
Received Data: {
"barcode":"BARCODE2",
"name":"Stacks"
}
Invoke the json_query service.
Select the barcode field from the acp class and the name
field from the acpl class.
Join the acp class to the acpl class based on the linked field
defined in the IDL.
Add a where clause to filter the results. We have more than one
condition beginning with the same key, so we wrap the conditions inside
an array.
The first condition tests whether the boolean value of the holdable
field on the acpl class is true.
The second condition tests whether the boolean value of the
opac_visible field on the acpl class is true.
For thorough coverage of the breadth of support offered by JSON
query syntax, see JSON Queries: A Tutorial.
Fleshing linked objects
A simplistic approach to retrieving a set of objects that are linked to
an object that you are retrieving - for example, a set of call numbers
linked to the barcodes that a given user has borrowed - would be to:
1. Retrieve the list of circulation objects (circ class)
for a given user (usr class).
2. For each circulation object, look up the target copy (target_copy
field, linked to the acp class).
3. For each copy, look up the call number for that copy (call_number
field, linked to the acn class).
However, this would result in potentially hundreds of round-trip
queries from the client to the server. Even with low-latency connections,
the network overhead would be considerable. So, built into the open-ils.cstore and
open-ils.pcrud access methods is the ability to flesh linked fields -
that is, rather than return an identifier to a given linked field,
the method can return the entire object as part of the initial response.
Most of the interfaces that return class instances from the IDL offer the
ability to flesh returned fields. For example, the
open-ils.cstore.direct.\*.retrieve methods allow you to specify a
JSON structure defining the fields you wish to flesh in the returned object.
Fleshing fields in objects returned by open-ils.cstore
srfsh# request open-ils.cstore open-ils.cstore.direct.asset.copy.retrieve 1, \
{
"flesh": 1,
"flesh_fields": {
"acp": ["location"]
}
}
The flesh argument is the depth at which objects should be fleshed.
For example, to flesh out a field that links to another object that includes
a field that links to another object, you would specify a depth of 2.
The flesh_fields argument contains a list of objects with the fields
to flesh for each object.
Let’s flesh things a little deeper. In addition to the copy location,
let’s also flesh the call number attached to the copy, and then flesh
the bibliographic record attached to the call number.
Fleshing fields in fields of objects returned by open-ils.cstore
request open-ils.cstore open-ils.cstore.direct.asset.copy.retrieve 1, \
{
"flesh": 2,
"flesh_fields": {
"acp": ["location", "call_number"],
"acn": ["record"]
}
}
Adding an IDL entry for ResolverResolver
Most OpenSRF methods in Evergreen define their object interface in the
IDL. Without an entry in the IDL, the prospective caller of a given
method is forced to either call the method and inspect the returned
contents, or read the source to work out the structure of the JSON
payload. At this stage of the tutorial, we have not defined an entry
in the IDL to represent the object returned by the
open-ils.resolver.resolve_holdings method. It is time to complete
that task.
The open-ils.resolver service is unlike many of the other classes
defined in the IDL because its data is not stored in the Evergreen
database. Instead, the data is requested from an external Web service
and only temporarily cached in memcached. Fortunately, the IDL
enables us to represent this kind of class by setting the
oils_persist:virtual class attribute to true.
So, let’s add an entry to the IDL for the open-ils.resolver.resolve_holdings
service:
And let’s make ResolverResolver.pm return an array composed of our new
rhr classes rather than raw JSON objects:
Once we add the new entry to the IDL and copy the revised ResolverResolver.pm
Perl module to /openils/lib/perl5/OpenILS/Application/, we need to:
Copy the updated IDL to both the /openils/conf/ and
/openils/var/web/reports/ directories. The Dojo approach to
parsing the IDL uses the IDL stored in the reports directory.
Restart the Perl services to make the new IDL visible to the services
and refresh the open-ils.resolver implementation
Rerun /openils/bin/autogen.sh to regenerate the JavaScript versionsautogen
of the IDL required by the HTTP translator and gateway.
We also need to adjust our JavaScript client to use the nifty newJavaScript
objects that open-ils.resolver.resolve_holdings now returns.
The best approach is to use the support in Evergreen’s Dojo extensionsDojo toolkit
to generate the JavaScript classes directly from the IDL XML file.
Accessing classes defined in the IDL via Fieldmapper
Load the Dojo core.
fieldmapper.AutoIDL reads /openils/var/reports/fm_IDL.xml to
generate a list of class properties.
fieldmapper.dojoData seems to provide a store for Evergreen data
accessed via Dojo.
fieldmapper.Fieldmapper converts the list of class properties into
actual classes.
fieldmapper.standardRequest invokes an OpenSRF method and returns
an array of objects.
The first argument to fieldmapper.standardRequest is an array
containing the OpenSRF service name and method name.
The second argument to fieldmapper.standardRequest is an array
containing the arguments to pass to the OpenSRF method.
As Fieldmapper has instantiated the returned objects based on their
class hints, we can invoke getter/setter methods on the objects.