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.
I strongly support the idea to add deleted_on column!
Deletion time information is very important for statistics reason, AND SHOULD NOT CHANGE. This ON UPDATE is messing up weeding stats for the libraries.
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.
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
What about existing deleteditems rows? Setting deleted_on=timestamp (but might be wrong), or NULL?
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.
(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.
(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)
So yes, it's what the patch does :)
I tried to test this on a sandbox, but even if I "Refresh Schema", there is no deleted_on column in deleteditems.
You are right, Caroline, the patch is missing the database update bits.
Created attachment 138115 [details] [review] bug 30392: DB changes
Oops, sorry, I forgot to attach one patch.
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.
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.
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.)
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
Created attachment 138233 [details] [review] Bug 30392: DB changes
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>
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>
Created attachment 138483 [details] [review] Bug 30392: (QA follow-up) Correct COMMENT Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Pushed to master for 22.11. Nice work everyone, thanks!
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>
Enhancement will not be backported to 22.05.x series
This is a good step for https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=20271
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.