Bug 1084 - would be nice to remove records from breeding tables.
Summary: would be nice to remove records from breeding tables.
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Staff Client (show other bugs)
Version: master
Hardware: PC All
: P2 enhancement (vote)
Assignee: Paul Poulain
QA Contact: Bugs List
URL:
Keywords:
Depends on:
Blocks: 8149
  Show dependency treegraph
 
Reported: 2006-05-31 06:12 UTC by Michael Hafen
Modified: 2015-06-04 23:23 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
proposed patches and new files. (4.93 KB, text/plain)
2006-05-31 06:13 UTC, Chris Cormack
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Chris Cormack 2010-05-20 23:38:40 UTC


---- Reported by mdhafen@tech.washk12.org 2006-05-31 06:12:54 ----

I have put together two methods for accomplish this.  First just remove the record when (if) it is moved out of the breeding table.  Second is to have a page for deleting records from the table.

Might be nice for the project.



---- Additional Comments From mdhafen@tech.washk12.org 2006-05-31 06:13:26 ----

Created an attachment
proposed patches and new files.





---- Additional Comments From jmf@liblime.com 2007-10-04 14:01:28 ----

this would still be nice, bumping up to 3.0



--- Bug imported by chris@bigballofwax.co.nz 2010-05-20 23:38 UTC  ---

This bug was previously known as _bug_ 1084 at http://bugs.koha.org/cgi-bin/bugzilla3/show_bug.cgi?id=1084
Imported an attachment (id=60)

Actual time not defined. Setting to 0.0
The original submitter of attachment 60 [details] is unknown.
   Reassigning to the person who moved it here: chris@bigballofwax.co.nz.

Comment 1 Paul Poulain 2011-08-02 10:12:44 UTC
already done (checked in 3.4)
Comment 2 Katrin Fischer 2011-08-02 10:34:48 UTC
How is it done in 3.4?
Is there a way to delete unused records from the breeding tables?
Comment 3 Paul Poulain 2011-08-02 11:07:07 UTC
when you've imported datas in your catalogue, you've a "clean" link to remove the content of this import
Comment 4 Katrin Fischer 2011-08-02 11:23:07 UTC
Yes, but by searching Z39.50 targets I thought more than the downloaded record was added to the breeding tables? I see no way to delete those at the moment.
Comment 5 Paul Poulain 2011-08-02 12:46:26 UTC
well spotted Katrin.

This could easily be achieved with the following SQL requests :

DELETE FROM import_records WHERE import_batch_id IN (SELECT import_batches.import_batch_id FROM import_batches WHERE import_batches.batch_type ="z3950") AND import_records.upload_timestamp<= DATE_SUB(now(), INTERVAL 1 DAY);

DELETE FROM import_batches WHERE import_batch_id NOT IN (SELECT DISTINCT import_batch_id from import_records);

The 1st request deletes all z3950 entries that are older than one day (too much from far, but harmless)
The 2nd request deletes the "fake batch header" that is generated on each z3950 query

We can put those SQLs in 2 places:
* on any z3950 search = before doing the new search, clean the database. Easy to setup, efficient, although a little loss of performances. I think this loss of performance is small compared to how long z3950 servers answer
* add a script to cronjob, that cleans every night. A little bit harder to write, and has to be setup during installation, which put some pain to the sysadmin.

I prefer the 1st option, let me know which one you prefer.
Comment 6 Chris Cormack 2012-04-08 07:22:37 UTC
First option seems fine by me, as does a simple clean Z39.50 search results button.
Comment 7 Michael Hafen 2012-04-09 14:45:58 UTC
(In reply to comment #5)
> well spotted Katrin.
> 
> This could easily be achieved with the following SQL requests :
> 
> DELETE FROM import_records WHERE import_batch_id IN (SELECT
> import_batches.import_batch_id FROM import_batches WHERE
> import_batches.batch_type ="z3950") AND import_records.upload_timestamp<=
> DATE_SUB(now(), INTERVAL 1 DAY);
> 
> DELETE FROM import_batches WHERE import_batch_id NOT IN (SELECT DISTINCT
> import_batch_id from import_records);
> 
> The 1st request deletes all z3950 entries that are older than one day (too
> much from far, but harmless)
> The 2nd request deletes the "fake batch header" that is generated on each
> z3950 query
> 
> We can put those SQLs in 2 places:
> * on any z3950 search = before doing the new search, clean the database.
> Easy to setup, efficient, although a little loss of performances. I think
> this loss of performance is small compared to how long z3950 servers answer
> * add a script to cronjob, that cleans every night. A little bit harder to
> write, and has to be setup during installation, which put some pain to the
> sysadmin.
> 
> I prefer the 1st option, let me know which one you prefer.

Either the first option, or adding those sql statements to the cleanup_database script with a default of 1 day.

Either of those options would be fine with me.  I think I'd prefer having it in cleanup_database if only so the delay to removal can be set by the sysadmin.  But I'm fine with which ever gets the job done.
Comment 8 Katrin Fischer 2014-10-10 23:00:45 UTC
I believe this is fixed by an option of the cleanup_database cronjob:

62    --z3950            purge records from import tables that are the result
63                       of Z39.50 searches

Please open a new bug if more functionality is needed.