Monday Minutes: Reporting on the Deleted
Nick, a developer with ByWater Solutions, joins Kelly and Jessie to share some handy reports on how a library can report on deleted items, bibs, and patrons from their Koha system.
Helpful SQL for Deleted Patrons
SELECT surname, firstname, cardnumber, branchcode, categorycode
WHERE date(updated_on) BETWEEN <<Start date|date>> AND <<End date|date>>
Helpful SQL for Deleted Items
SELECT IF(deletedbiblio.biblionumber is NULL, 'active', 'deleted')as 'present' ,COALESCE(deletedbiblio.title,biblio.title) as 'Title', COALESCE(deletedbiblio.author,biblio.author) as 'Author', itemcallnumber, location, barcode
LEFT JOIN deletedbiblio USING (biblionumber)
LEFT JOIN biblio USING (biblionumber)
WHERE deleteditems.timestamp BETWEEN <<Start date|date>> AND <<End date|date>>
Helpful SQL for Deleted Bibs
SELECT title, author
WHERE timestamp BETWEEN <<Start date|date>> AND <<End date|date>>
Clean Up Database Cron
There is a clean-up database cron that we can run on your site, if you would like to limit how long this data is stored.
The portion of the cleanup database cron for deleting information about Items and Bibs is called, 'deleted-catalog', and this can be told to purge records deleted more than X days ago. This will purge information from the following tables: deleteditems, deletedbiblioitems, deletedbiblio_metadata, and deletedbiblio.
The portion of the cleanup cron for deleting patron deletions is called, 'deleted-patrons', which will also go by the number of days from when the patron was deleted.
Read more by Kelly McElligott