Checkout history fails on serials checkouts / deleted copies

[Bill Erickson]

Evergreen 2.10.0

Two part bug. Both parts related.

1. If an action.circulation row exists with a target_copy value referencing a copy which has been deleted (removed from the database), the 2.10.0 DB upgrade script will fail with a foreign key constraint violation. This scenario is rare, but possible, since there is no foreign key constraint on action.circulation.target_copy. We have evidence of at least one EG system in the wild where such data exits.

As discussed in IRC, the plan here is simply to avoid creating circ history rows when a circulation refers to a nonexistent copy.

2. Checkouts of serial.unit's do not translate successfully into action.usr_circ_history rows since usr_circ_history has a foreign-key constraint on asset.copy. Foreign key constraints do not work seamlessly with table inheritance to serial.unit.

The solution here is to remove the foreign key constraint, consistent with action.circulation.

These changes will require new upgrade scripts and a modification to the 2.10.0 SQL upgrade script, since the upgrade script will fail if either of these conditions occurs.

[Bill Erickson (berick)]

One point of clarification, action.circulation does enforce constraints on asset.copy / serial.unit via evergreen.fake_fkey_tgr. (This is similar, but not as restrictive as a native constraint). We should add the same to action.usr_circ_history.

[Bill Erickson (berick)]

Fixes pushed:

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/berick/lp1564079-circ-history-serials-deleted

Contains 2 commits, one to support serials circ history, one to skip circs whose copies don't exist.

Notable section of the serials commit:

    This modifies the 2.9.3-2.10.0-upgrade-db.sql and 0960 upgrade scripts,
    since without this change, the scripts may fail.

    A separate upgrade script is added to drop the constraint for those who
    have already successfully run the the 2.9.3-2.10.0-upgrade-db.sql script
    (i.e. those who don't use serials). For simplicity, the
    evergreen.fake_fkey_tgr trigger is only applied in this separate upgrade
    script (in addition to the base schema, of course).

Adding pullrequest.

[Bill Erickson (berick)]

To test the serials change:

1. Enable circ history retention for a user.
2. Check out a serial copy (serial.unit) to the user.
3. The checkout should not explode.

[Kathy Lussier (klussier)]

Looks good to me. I merged the branch to master and backported it to 2.10.

Thanks Bill!