Move calculated dewey ranges/blocks to their own reporting view

[Jason Boyer]

Eg 3.2.3

One of the few reasons to still use the Classic Item View reporting source is that it has calculated fields for dewey blocks and ranges that can make it very easy to build reports that circ or weeding by subject block(s). Because there aren't really any other benefits to this view those calculated fields should be moved to their own view off of asset.call_number so they're available for use in reporting if needed, but have no effect whatsoever if they're not.

[Jason Boyer (jboyer)]

Note: Because of some current issues with might_have links in the IDL in web reports the proposed fix (or some equivalent) to bug 1796945 will be required to use this view.

[Jason Boyer (jboyer)]

Here we go:
http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/jboyer/lp1813191_dewey_reports / working/user/jboyer/lp1813191_dewey_reports

[Jane Sandberg (sandbergja)]

Thanks for this branch, Jason. It will need some brief release notes. Also, will the reporter.asset_call_number_dewey view need to be added to the seed data too, not just the upgrade script?

[Jason Boyer (jboyer)]

After far too long I finally return to take care of this. Release note and db build scripts are finally in order in this branch: https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/jboyer/lp1813191_dewey_reports_rebase / working/user/jboyer/lp1813191_dewey_reports_rebase

Obviously works as expected on Concerto, but if someone would test it against a larger dataset (especially for filtering on these fields) that would be great.

[Jessica Woolford (jwoolford)]

Not sure if this is a deal breaker since a library who has LC call numbers probably won't be making use of this source, but it does look like it assigned a Dewey Block to LC call numbers in the Concerto data. All else works as expected.

[Terran McCanna (tmccanna)]

The only scenario I can think of where that would be an issue if is a library were using both LC and Dewey in the same catalog for some reason... maybe if two libraries merged? Personally, I would consider that too much of a corner case to prevent this from moving forward, but it's not a hill I would die on one way or the other.

[Jessica Woolford (jwoolford)]

I agree, Terran.

I have tested this code and consent to signing off with my name, Jessica Woolford, and email, <email address hidden>.

[Galen Charlton (gmc)]

I ran into a call number (in a real database) that breaks the view if the underlying call number row is referenced: "14.11.01":

select label from asset.call_number where id = XXX;
select * from reporter.asset_call_number_dewey where call_number = XXX;
ERROR: invalid input syntax for type double precision: "14.11.01"

As that would terminate any query that processes a row with such a value, I think more error-guarding is needed.

[Jason Boyer (jboyer)]

There's a better version of this available at https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/jboyer/lp1813191_dewey_reports_rebase2 / working/user/jboyer/lp1813191_dewey_reports_rebase2 that tightens up the check on call_number_dewey's output. Now it ensures that there is either 0 or 1 dot rather than just making sure there's a combination of digits and dots after the initial 3 digits.

[Terran McCanna (tmccanna)]

Works well for me! My sign off at:

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/mccanna/lp1813191_dewey_reports_rebase2_signoff

[Galen Charlton (gmc)]

I was able to break it again with real data, in this case, a call number whose label is '92J':

select * from reporter.asset_call_number_dewey where call_number = XXX; -- label is 92J
ERROR: invalid input syntax for type double precision: "92J"

[Jason Boyer (jboyer)]

On errant dots my toe is stubbed
Any character is therein subbed
A single letter then joins the fun
Sadness comes when my view is run
With the addition of slashes few
Now the letters it will eschew
And call numbers can continue

Anyway, new commit just dropped; thinking it's finally locked down.

[Galen Charlton (gmc)]

18,677,437 call numbers lined up
Hoping on an exploded regexp to sup
18,677,437 call numbers lined up
Back home they went disappointed, yup

Merged to master for inclusion in 3.8. Thanks, Jason and Terran!