It seems that a librarian can edit the biblionumber for a given subscription, however, when receiving serials the biblionumber is pulled from the existing serials, so all new received issues are received on the old biblionumber This is problematic in light of bug 21901 - you can change the biblionumber, the serials still point to the old biblionumber - and deleting the biblio deletes the serials. Additionally, the update will delete serials where this was the case To recreate: 1 - Create a new subscription 2 - Receive an issue 3 - Edit the subscription and change the biblionumber 4 - Receive another issue 5 - SELECT * FROM serial 6 - Both have the old number 7 - Delete the old biblio 8 - The serials are deleted 9 - You also cannot now receive for this issue or generate serials
Humm.. should the old serials follow the biblionumber change too.. or should the logic be changed such that newly received serials get the update bib and old one's keep the old bib?
Why do we have serial.biblionumber? We should get the biblio link using serial->subscription->biblionumber
Created attachment 117565 [details] [review] Bug 27842: Add tests
Created attachment 117566 [details] [review] Bug 27842: Update serial.biblionumber when a subscription is linked with another record It seems that a librarian can edit the biblionumber for a given subscription, however, when receiving serials the biblionumber is pulled from the existing serials, so all new received issues are received on the old biblionumber. This is problematic in light of bug 21901 - you can change the biblionumber, the serials still point to the old biblionumber - and deleting the biblio deletes the serials. Additionally, the update will delete serials where this was the case Test plan: 1 - Create a new subscription 2 - Receive an issue 3 - Edit the subscription and change the biblionumber 4 - Receive another issue 5 - SELECT biblionumber FROM serial => the biblionumber link must have been updated
Created attachment 117567 [details] [review] Bug 27842: Update serial.biblionumber when a subscription is linked with another record It seems that a librarian can edit the biblionumber for a given subscription, however, when receiving serials the biblionumber is pulled from the existing serials, so all new received issues are received on the old biblionumber. This is problematic in light of bug 21901 - you can change the biblionumber, the serials still point to the old biblionumber - and deleting the biblio deletes the serials. Additionally, the update will delete serials where this was the case Test plan: 1 - Create a new subscription 2 - Receive an issue 3 - Edit the subscription and change the biblionumber 4 - Receive another issue 5 - SELECT biblionumber FROM serial => the biblionumber link must have been updated Signed-off-by: Petro Vashchuk <stalkernoid@gmail.com>
Created attachment 117677 [details] [review] Bug 27842: Add tests Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 117696 [details] [review] Bug 27842: Add tests Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 117697 [details] [review] Bug 27842: Add tests Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 117698 [details] [review] Bug 27842: Update serial.biblionumber when a subscription is linked with another record It seems that a librarian can edit the biblionumber for a given subscription, however, when receiving serials the biblionumber is pulled from the existing serials, so all new received issues are received on the old biblionumber. This is problematic in light of bug 21901 - you can change the biblionumber, the serials still point to the old biblionumber - and deleting the biblio deletes the serials. Additionally, the update will delete serials where this was the case Test plan: 1 - Create a new subscription 2 - Receive an issue 3 - Edit the subscription and change the biblionumber 4 - Receive another issue 5 - SELECT biblionumber FROM serial => the biblionumber link must have been updated Signed-off-by: Petro Vashchuk <stalkernoid@gmail.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 117712 [details] [review] Bug 27842: (follow-up) Correct existing serail/subscriptionhistory before deletion
I added a followup which should be tested - before we add the constraints and delete we should update serial and subscription history To test: 1 - Checkout a version before 21901 2 - Reset db 3 - Add a subscription 4 - Edit the subscription to a new biblionumber 5 - Update database 6 - Confirm the subscriptionid still has entries in serial and subscriptionhistory
Can someone test the last patch please?
I wonder if we are on the right track here by changing the information in past serials or if this doesn't destroy useful information. Serials are known to be odd. When you look at the national serials database they have a graph view on their record detail pages where you can see how often they change their names, merge with others... divide etc. It's pretty, but also scary! And for our catalogers every change like this means a new record. So it makes sense that we can change which record a subscription is attached to. The name change would not necessarily affect the subscription itself. So could it be by design that the original biblionumber was stored? As you said, we don't actually need it, as we have the current biblionumber in the subscription. If it was thought to be for keeping some history, it could make sense and the fix would maybe to stop it from deleting the issues when the record is deleted. We could also think about updating the biblionumber of the next expected issue when the biblionumber is changed. Not sure about this - just something I was wondering about when I noticed the biblionumber in serials in the past.
Nicolas, Séverine, could you give us your input on this one?
(In reply to Jonathan Druart from comment #14) > Nicolas, Séverine, could you give us your input on this one? This is far beyond my own skills and from my point of view, understanding serials is a bit like understanding the Necronomicon. Nevertheless, Séverine is working on it with our serials specialists :).
The database update from 21901 causes data loss - while we can discuss future handling, currently a serial that changed biblios and the old record was deleted will remove all subscriptionhistory and serial entries for the subscription upon upgrade. Should I move that patch to its own bug to prevent data loss for libraries so we can move that forward?
The data loss angle is the one I am worried about as well. With the solution proposed here we lose the former history. That's why I was thinking we might want to check on why the field was there in the first place. Should we just have a patch undoing the constraint for now?
(In reply to Katrin Fischer from comment #17) > The data loss angle is the one I am worried about as well. With the solution > proposed here we lose the former history. That's why I was thinking we might > want to check on why the field was there in the first place. > > Should we just have a patch undoing the constraint for now? I don't think we are losing anything by setting the biblionumber to the current. In the case of a serial having had 2,3, or more biblionumbers, we are only using the first one - so while one previous connection was "preserved" any future ones were not. Even with one change preserved I don't see any evidence that it was intentional
(In reply to Nick Clemens from comment #18) > (In reply to Katrin Fischer from comment #17) > > The data loss angle is the one I am worried about as well. With the solution > > proposed here we lose the former history. That's why I was thinking we might > > want to check on why the field was there in the first place. > > > > Should we just have a patch undoing the constraint for now? > > I don't think we are losing anything by setting the biblionumber to the > current. > > In the case of a serial having had 2,3, or more biblionumbers, we are only > using the first one - so while one previous connection was "preserved" any > future ones were not. Even with one change preserved I don't see any > evidence that it was intentional Can you explain about only using the first one? As I read it we are updating all of them: UPDATE serial JOIN subscription USING (subcriptionid) SET serial.biblionumber = subscription.biblionumber WHERE serial.biblionumber != subscription.biblionumber Why not just update the receive process to use the subscription for pulling the biblionumber and change the constraint to set null on delete?
(In reply to Katrin Fischer from comment #19) > (In reply to Nick Clemens from comment #18) > > (In reply to Katrin Fischer from comment #17) > > > The data loss angle is the one I am worried about as well. With the solution > > > proposed here we lose the former history. That's why I was thinking we might > > > want to check on why the field was there in the first place. > > > > > > Should we just have a patch undoing the constraint for now? > > > > I don't think we are losing anything by setting the biblionumber to the > > current. > > > > In the case of a serial having had 2,3, or more biblionumbers, we are only > > using the first one - so while one previous connection was "preserved" any > > future ones were not. Even with one change preserved I don't see any > > evidence that it was intentional > > Can you explain about only using the first one? As I read it we are updating > all of them: > > UPDATE serial JOIN subscription USING (subcriptionid) SET > serial.biblionumber = subscription.biblionumber WHERE serial.biblionumber != > subscription.biblionumber > > Why not just update the receive process to use the subscription for pulling > the biblionumber and change the constraint to set null on delete? Sorry I meant, the serial biblionumber is always the same, i.e.: Create subscription on biblionumber 1 and recieve a serial, it uses biblionumber1 Edit subscription to use biblionumber 2 and receive a serial, it uses biblionumber 1 Edit subscription to use biblionumber 3 and receive a serial, it uses biblionumber on Edit subscription to use biblionumber 4...and so on We are not keeping a history of the biblionumber, but rather keeping only the original biblionumber I don't see that storing 'NULL' or one older biblionumber gains us much over storing the current bibloionumber
> Sorry I meant, the serial biblionumber is always the same, i.e.: > Create subscription on biblionumber 1 and recieve a serial, it uses > biblionumber1 > Edit subscription to use biblionumber 2 and receive a serial, it uses > biblionumber 1 > Edit subscription to use biblionumber 3 and receive a serial, it uses > biblionumber on > Edit subscription to use biblionumber 4...and so on > > We are not keeping a history of the biblionumber, but rather keeping only > the original biblionumber > > I don't see that storing 'NULL' or one older biblionumber gains us much over > storing the current bibloionumber Thx for clearing this up! In that case I see no issue in changing the data.
I did the test plan of the 1st patch and got this result in the DB 100 is the initial bibnumber and 101 is the edited one. +----------+--------------+--------+ | serialid | biblionumber | status | +----------+--------------+--------+ | 1 | 101 | 2 | | 2 | 101 | 2 | | 3 | 100 | 1 | +----------+--------------+--------+ This is not expected right?
Filed bug 28133 for my thoughts of a possible future use of the biblionumber field in serials.
I bet you get it if you receive another serial item. It's because GetSubscription does not return subscription.biblionumber as the biblionumber, but subscriptionhistory.biblionumber instead. So 2 questions: - Should we update subscriptionhistory.biblionumber=subscription.biblionumber when a subscription is linked to a new biblio? - If so, what's the point of subscriptionhistory.biblionumber? Shouldn't we remove it and use the subscriptionid FK?
> I bet you get it if you receive another serial item. IIUC another serial item <=> another issue. So yes, if I got right on the step 4 of the test plan (had to impro--, hum, learn a lot about serial)
Created attachment 120239 [details] [review] Bug 27842: Add tests Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Created attachment 120240 [details] [review] Bug 27842: Update serial.biblionumber when a subscription is linked with another record It seems that a librarian can edit the biblionumber for a given subscription, however, when receiving serials the biblionumber is pulled from the existing serials, so all new received issues are received on the old biblionumber. This is problematic in light of bug 21901 - you can change the biblionumber, the serials still point to the old biblionumber - and deleting the biblio deletes the serials. Additionally, the update will delete serials where this was the case Test plan: 1 - Create a new subscription 2 - Receive an issue 3 - Edit the subscription and change the biblionumber 4 - Receive another issue 5 - SELECT biblionumber FROM serial => the biblionumber link must have been updated Signed-off-by: Petro Vashchuk <stalkernoid@gmail.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Created attachment 120241 [details] [review] Bug 27842: (follow-up) Correct existing serail/subscriptionhistory before deletion Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Pushed to master for 21.05, thanks to everybody involved!
Pushed to 20.11.x for 20.11.06
Hello, Changes on DBRev 19.12.00.076 break the updatedatabase (typo). I fixed that on bug 28298. Appart from that, shouldn't these changes be done in a new DBRev ? I think Koha 20.05 or 20.11 will never get the DB fix.
I remove from 20.11.x since it needs more work
Please follow-up on bug 28298 ASAP.
Pushed to 20.05.x for 20.05.12
Just tested, 19.11.x not affected, no backport.