Bug 30392

Summary: Add a deleted_on column to deleteditems
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: CatalogingAssignee: Jonathan Druart <jonathan.druart+koha>
Status: Needs documenting --- QA Contact: Katrin Fischer <katrin.fischer>
Severity: enhancement    
Priority: P5 - low CC: anneli.osterman, caroline.cyr-la-rose, jonathan.druart+koha, jrobb, lucas, m.de.rooy, mathsabypro, pasi.kallinen, tomascohen
Version: master   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23081
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31190
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
22.11.00
Attachments: Bug 30392: Set deleted_on when an item is (marked as) deleted
bug 30392: DB changes
Bug 30392: Set deleted_on when an item is (marked as) deleted
Bug 30392: DB changes
bug 30392: DB changes
Bug 30392: Set deleted_on when an item is (marked as) deleted
Bug 30392: (QA follow-up) Correct COMMENT
Bug 30392: (QA follow-up) Remove deleted_on from the API response

Description Katrin Fischer 2022-03-29 09:44:02 UTC
Bug 23081 did an udpate on the data in deleteditems resulting in a lot of timestamps being reset, leaving libraries without the cataloguing log without any information about when the item was deleted.

To avoid this in the future, it would be much better to have a separate column to store the deletion date of an item.

The catalouging log is not always a good option in big libraries as we don't differentiate between item, bibliographic and authority record updates, so it creates a lot of data and it might also be deleted in some.

The information about when an item was deleted is needed in some libraries to avoid having to document this on paper in an inventory book and as such should be saved in a save spot.
Comment 1 Anneli Österman 2022-07-14 09:48:06 UTC
I strongly support the idea to add deleted_on column!
Comment 2 paxed 2022-07-14 09:49:07 UTC
Deletion time information is very important for statistics reason, AND SHOULD NOT CHANGE. This ON UPDATE is messing up weeding stats for the libraries.
Comment 3 Jason Robb 2022-07-19 15:11:49 UTC
This is very much needed.

We currently don't keep logs past 6 months due to our size but need to be able to report annually on how many items each branch has weeded. The only way to do that at present is by counting deleted items based on timestamp, so when that table gets updated and the timestamps change, we lose the ability to accurately retrieve that information.
Comment 4 Jonathan Druart 2022-07-25 08:09:50 UTC
Created attachment 138073 [details] [review]
Bug 30392: Set deleted_on when an item is (marked as) deleted

We could like to track down when an item has been moved to the
deleteditems table.

Test plan:
Created a new item, delete it
Not that the deleteditems.deleted_on value is set correctly
Comment 5 Jonathan Druart 2022-07-25 08:10:49 UTC
What about existing deleteditems rows? Setting deleted_on=timestamp (but might be wrong), or NULL?
Comment 6 Katrin Fischer 2022-07-25 10:28:43 UTC
Maybe NULL is better, we messed with the date in a recent update, so they are most likely wrong. We can still use COALESCE in reports if needed.
Comment 7 Jonathan Druart 2022-07-25 11:36:59 UTC
(In reply to Katrin Fischer from comment #6)
> Maybe NULL is better, we messed with the date in a recent update, so they
> are most likely wrong. We can still use COALESCE in reports if needed.

What does the patch then.
Comment 8 Katrin Fischer 2022-07-25 12:26:44 UTC
(In reply to Jonathan Druart from comment #7)
> (In reply to Katrin Fischer from comment #6)
> > Maybe NULL is better, we messed with the date in a recent update, so they
> > are most likely wrong. We can still use COALESCE in reports if needed.
> 
> What does the patch then.

Not sure I understand. The idea is to give a reliable date, that is not accidentally overwritten by changes to deleteditems (as we did in the last update, which triggered the timestamp).

And we start with NULL for the old entries (would be my suggestion)
Comment 9 Jonathan Druart 2022-07-25 12:54:32 UTC
So yes, it's what the patch does :)
Comment 10 Caroline Cyr La Rose 2022-07-25 17:54:17 UTC
I tried to test this on a sandbox, but even if I "Refresh Schema", there is no deleted_on column in deleteditems.
Comment 11 Katrin Fischer 2022-07-25 20:49:13 UTC
You are right, Caroline, the patch is missing the database update bits.
Comment 12 Jonathan Druart 2022-07-26 06:54:44 UTC
Created attachment 138115 [details] [review]
bug 30392: DB changes
Comment 13 Jonathan Druart 2022-07-26 06:55:00 UTC
Oops, sorry, I forgot to attach one patch.
Comment 14 Anneli Österman 2022-07-26 07:33:35 UTC
I tested in Bywater's sandbox.

If I deleted item via batch item delete (backgroun jobs), it is deleted but deleted_on is empty in deleteditems table.

If I delete the item from "additem.pl?op=edititem&biblionumber=" page (Actions -> Delete) I get this error message:

DBIx::Class::Row::store_column(): No such column 'deleted_on' on Koha::Schema::Result::Deleteditem at /kohadevbox/koha/Koha/Item.pm line 341
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77

There is deleted_on column on items and deleteditems table.
Comment 15 Katrin Fischer 2022-07-26 07:46:22 UTC
Hi Anneli, are you testing using the sandboxes or ktd?

If ktd, run: dbic
If sandboxes, got to Actions > Refresh schema

This should hopefully make the error go away.
Comment 16 Anneli Österman 2022-07-26 09:55:20 UTC
Katrin, thanks, refreshing the schema helped! :)

There is a deleted_on column on both items and deleteditems table and it is updated when item is deleted (regardless of the method used, batch delete or actions/delete in modify page.)
Comment 17 Katrin Fischer 2022-07-28 21:24:31 UTC
Created attachment 138232 [details] [review]
Bug 30392: Set deleted_on when an item is (marked as) deleted

We could like to track down when an item has been moved to the
deleteditems table.

Test plan:
Created a new item, delete it
Not that the deleteditems.deleted_on value is set correctly
Comment 18 Katrin Fischer 2022-07-28 21:24:37 UTC
Created attachment 138233 [details] [review]
Bug 30392: DB changes
Comment 19 Tomás Cohen Arazi 2022-08-01 17:32:25 UTC
Created attachment 138481 [details] [review]
bug 30392: DB changes

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 20 Tomás Cohen Arazi 2022-08-01 17:32:30 UTC
Created attachment 138482 [details] [review]
Bug 30392: Set deleted_on when an item is (marked as) deleted

We could like to track down when an item has been moved to the
deleteditems table.

Test plan:
Created a new item, delete it
Not that the deleteditems.deleted_on value is set correctly

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 21 Tomás Cohen Arazi 2022-08-01 17:32:35 UTC
Created attachment 138483 [details] [review]
Bug 30392: (QA follow-up) Correct COMMENT

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 22 Tomás Cohen Arazi 2022-08-01 17:38:11 UTC
Pushed to master for 22.11.

Nice work everyone, thanks!
Comment 23 Tomás Cohen Arazi 2022-08-02 11:45:40 UTC
Created attachment 138500 [details] [review]
Bug 30392: (QA follow-up) Remove deleted_on from the API response

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 24 Lucas Gass 2022-09-29 15:56:24 UTC
Enhancement will not be backported to 22.05.x series