Bug 11846 - Correct database update for debarments with 9999-12-31
Summary: Correct database update for debarments with 9999-12-31
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P5 - low major (vote)
Target Milestone: ---
Assignee: Fridolin SOMERS
QA Contact: Testopia
URL:
Whiteboard:
Keywords:
Depends on: 2720
Blocks:
  Show dependency treegraph
 
Reported: 2014-02-26 10:14 UTC by Fridolin SOMERS
Modified: 2014-12-07 20:05 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Bot Control: ---
When did the bot last check this:
Who signed the patch off:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 11846 - Correct database update for debarments with 9999-12-31 (2.66 KB, patch)
2014-02-26 11:02 UTC, Fridolin SOMERS
Details | Diff | Splinter Review
Bug 11846 - Correct database update for debarments with 9999-12-31 (2.64 KB, patch)
2014-03-25 08:05 UTC, Fridolin SOMERS
Details | Diff | Splinter Review
Bug 11846 - Correct database update for debarments with 9999-12-31 (3.15 KB, patch)
2014-04-14 09:46 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 11846 - Correct database update for debarments with 9999-12-31 (3.16 KB, patch)
2014-04-14 12:50 UTC, Kyle M Hall
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Fridolin SOMERS 2014-02-26 10:14:20 UTC
The borrowers with infinite debarments have borrowers.debarred with '9999-12-31'.
Database update of Bug 2720 3.13.00.035 contains :
  INSERT INTO borrower_debarments ( borrowernumber, expiration, comment ) SELECT borrowernumber, debarred, debarredcomment FROM borrowers WHERE debarred IS NOT NULL
For borrowers where borrowers.debarred is '9999-12-31', this value is copied into borrower_debarments.expiration.
This is not correct because borrower_debarments.expiration must be NULL for infinite debarments.

Set as major because it creates an heavy load on pages displaying this infinite date (using the TT plugin KohaDates).
Comment 1 Fridolin SOMERS 2014-02-26 11:02:20 UTC Comment hidden (obsolete)
Comment 2 M. Tompsett 2014-02-28 05:29:07 UTC
I'm just wondering why even fix the old one? It requires more testing to prove it works. If it upgrades incorrectly (because you upgrade an older one), the current one will fix it. If it already upgraded incorrectly (because you upgrade after the back patch fix), the current one will fix it. But because of the back patch fix portion, you need to test the upgrading prior to the back patch fix portion.
Comment 3 M. Tompsett 2014-02-28 06:25:11 UTC
You confused debarred (from borrowers) with expiration (from borrower_debarments).
Comment 4 Fridolin SOMERS 2014-03-03 07:50:00 UTC
(In reply to M. Tompsett from comment #3)
> You confused debarred (from borrowers) with expiration (from
> borrower_debarments).
Oh.
Is the patch wrong or is only the upgrade text wrong ?
Comment 5 Katrin Fischer 2014-03-03 08:23:05 UTC
Comment on attachment 25637 [details] [review]
Bug 11846 - Correct database update for debarments with 9999-12-31

Review of attachment 25637 [details] [review]:
-----------------------------------------------------------------

::: installer/data/mysql/updatedatabase.pl
@@ +8019,5 @@
>  
> +$DBversion = "3.15.00.XXX";
> +if ( CheckVersion($DBversion) ) {
> +    $dbh->do(q{
> +        UPDATE borrower_debarments SET debarred = NULL WHERE debarred = '9999-12-31'

This line is not right, as there is no debarred as a date in borrower_debarments. Please fix and test.
Comment 6 M. Tompsett 2014-03-03 13:22:33 UTC
Thank you for clarifying the problem, Katrin. That line. :)
Comment 7 Fridolin SOMERS 2014-03-07 13:39:42 UTC
Indeed. I correct my patch
Comment 8 Fridolin SOMERS 2014-03-25 08:05:27 UTC Comment hidden (obsolete)
Comment 9 M. Tompsett 2014-03-25 13:55:19 UTC
Thanks for the fix. However, you still didn't address my question in comment 2.

"why even fix the old one?"

If it upgrades incorrectly (because you upgrade an older one), the current one will fix it. If it already upgraded incorrectly (because you upgrade after the back patch fix), the current one will fix it. I see no reason to change historical versions.
Comment 10 Katrin Fischer 2014-03-25 14:28:14 UTC
Is there a reason not to fix the old update? :)
Comment 11 Marcel de Rooy 2014-04-14 09:45:32 UTC
(In reply to Katrin Fischer from comment #10)
> Is there a reason not to fix the old update? :)

I agree that we can fix older updates. And in this case testing the older dbrev is more or less a theoretical problem. (By resetting Version and adding one SetVersion line in updatedatabase, we can achieve the same result.)
Comment 12 Marcel de Rooy 2014-04-14 09:46:17 UTC Comment hidden (obsolete)
Comment 13 Kyle M Hall 2014-04-14 12:47:44 UTC
> I tested both db revisions by resetting Version of my database (and
> adding a SetVersion after the revised older dbrev). This was possible since
> the debarred field still exists in borrowers. (I have my doubts about that,
> but that goes outside the scope of this report.)

Yes, borrower.debarred was retained for both speed and the fact that we wouldn't need to modify all the code that checks this field.

> Also note that routine _UpdateBorrowerDebarmentFlags puts back 9999-12-31
> into borrowers for indefinite debarments (which looks not very consistent).

Agreed, but again this is due to the extended feature being "backwards compatible" with the original debarment system.
Comment 14 Kyle M Hall 2014-04-14 12:50:34 UTC
Created attachment 27093 [details] [review]
Bug 11846 - Correct database update for debarments with 9999-12-31

The borrowers with infinite debarments have borrowers.debarred with '9999-12-31'.
Database update for this bug contained :
  INSERT INTO borrower_debarments ( borrowernumber, expiration, comment ) SELECT borrowernumber, debarred, debarredcomment FROM borrowers WHERE debarred IS NOT NULL
For borrowers where borrowers.debarred is '9999-12-31', this value is copied into borrower_debarments.expiration.
This is not correct because borrower_debarments.expiration must be NULL for infinite debarments.

This patch corrects update 3.13.00.035 and also adds an update for databases already updated.

Test plan :
- Use a database with version 3.13.00.000
- Set a borrower as restricted for ever (leave until empty)
- Use sources to master + patch
- Perform updatedatabase
- Look at borrower details, tab "Restrictions"
=> Without patch, you see Expiration 31/12/9999
=> With patch, you see Expiration Infinite

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
I tested both db revisions by resetting Version of my database (and
adding a SetVersion after the revised older dbrev). This was possible since
the debarred field still exists in borrowers. (I have my doubts about that,
but that goes outside the scope of this report.)
Also note that routine _UpdateBorrowerDebarmentFlags puts back 9999-12-31
into borrowers for indefinite debarments (which looks not very consistent).

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 15 Galen Charlton 2014-04-14 16:23:15 UTC
Pushed to master.  Thanks, Fridolin!
Comment 16 Fridolin SOMERS 2014-04-23 15:34:27 UTC
Patch pushed to 3.14.x, will be in 3.14.06
(creates version 3.14.05.001)