Bug 30392 - Add a deleted_on column to deleteditems
Summary: Add a deleted_on column to deleteditems
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Cataloging (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Jonathan Druart
QA Contact: Katrin Fischer
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-03-29 09:44 UTC by Katrin Fischer
Modified: 2023-12-28 20:43 UTC (History)
10 users (show)

See Also:
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 (2.41 KB, patch)
2022-07-25 08:09 UTC, Jonathan Druart
Details | Diff | Splinter Review
bug 30392: DB changes (3.68 KB, patch)
2022-07-26 06:54 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 30392: Set deleted_on when an item is (marked as) deleted (2.41 KB, patch)
2022-07-28 21:24 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 30392: DB changes (3.68 KB, patch)
2022-07-28 21:24 UTC, Katrin Fischer
Details | Diff | Splinter Review
bug 30392: DB changes (3.79 KB, patch)
2022-08-01 17:32 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 30392: Set deleted_on when an item is (marked as) deleted (2.52 KB, patch)
2022-08-01 17:32 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 30392: (QA follow-up) Correct COMMENT (3.82 KB, patch)
2022-08-01 17:32 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 30392: (QA follow-up) Remove deleted_on from the API response (1.31 KB, patch)
2022-08-02 11:45 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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
Comment 25 Christopher Brannon 2023-04-10 16:17:27 UTC
This is a good step for https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=20271
Comment 26 Caroline Cyr La Rose 2023-04-17 15:50:07 UTC
Not sure what needs to be documented here. It doesn't affect the user interface. I'm closing, but feel free to reopen with more details as to what needs to be added to the manual.