All-circs DB view could be leaner for real-time access

[Bill Erickson]

Evergreen circa 2.12

Primarily as a result of bug #1497335, aged circulation data is now exposed to the staff client (e.g. copy status interface). The data is exposed through the action.all_circulation DB view, which was originally created as a reporting tool. At query time, the view collects extra data on each circulation, specifically copy, call number, patron, and patron address data. It does this so the active circ data can mimic the anonymized form of the the aged circ data. While this is useful for reporting, the extra data collection and munging is overkill for the application, which generally just wants a thing that looks like an action.circulation.

I propose a leaner form of the view that only returns data living on the action.circulation and action.aged_circulation objects. Something like:

CREATE OR REPLACE VIEW action.all_circulation_slim AS
  SELECT * FROM action.circulation UNION ALL
  SELECT
   id,
   NULL AS usr,
   xact_start,
   -- list remaining columns that are shared by both tables
  FROM action.aged_circulation;

(Alternate VIEW name suggestions welcome)

This view would be used instead of action.all_circulation in the copy details API, action.all_circ_chain, action.summarize_all_circ_chain(), and any direct references to 'combcirc' in the browser client.

The goal is to reduce the effort required on the DB to load aged circ objects, while allowing the caller to flesh the copy data as needed. In cases where anonymized user data is required, then action.all_circulation will still be there.

[Bill Erickson (berick)]

A quick glance suggests the _slim view could also be used in rating.percent_time_circulating().

[Jason Stephenson (jstephenson)]

I don't think that there is an action.combined_circulation table/view or object. That could be an alternate if action.all_circulation_slim is not deemed good enough.

I like the idea as I've often run into the slowness of using action.all_circulation in my own queries/scripts and have often done a custom join when I didn't need the calculated fields from the existing view.

So, +1 from me! :)

[Bill Erickson (berick)]

Thanks, Jason. My only concern with action.combined_circulation is possible confusion with "combcirc" as the IDL class name for action.all_circulation. (It's really sucking the air out of the room). And I'll resist seriously suggesting action.circulations_amalgamated. action.unified_circulation?

[Jason Stephenson (jstephenson)]

I'm fine with action.all_circulation_slim. Is there already an aacs object?

[Bill Erickson (berick)]

Branch pushed (using "aacs").

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/berick/lp1695007-all-circs-slim

Tip commit is a minor bug fix to how circulation summary data is accessed in the browser client copy status. I kept it separate in case we want to merge it first/separately.

From the 2nd commit:

Adds a new view action.all_circulation_slim which collects all action.circulation and action.aged_circulation rows into a single set, without the added joins for copy and patron data imposed by the existing action.aged_circulation view.

The new leaner view is now used by various other views and APIs in place of the all_circulation view:

DB: action.all_circ_chain()
DB: action.summarize_all_circ_chain()
DB: rating.percent_time_circulating()
API: open-ils.circ.retrieve
API: open-ils.circ.copy_checkout_history.retrieve
API: open-ils.circ.copy_details.retrieve
API: open-ils.circ.renewal_chain.retrieve_by_circ[.summary]
API: open-ils.circ.prev_renewal_chain.retrieve_by_circ[.summary]

To Test (webstaff style):

[1] Open a copy in the web staff Item Status / Detail View interface that has live and aged circulations.
[2] Open the Recent Circ History tab
[3] Confirm relevant fields for both types of circs display as expected and that the Patron field for the aged circulation shows a value of <Aged Circulation>

[Mike Rylander (mrylander)]

Merged to master. Thanks, Bill!