I am moving this out from bug 10758 that was filed for the display issue. But that is actually only the symptom, while we are actually facing a data loss issue. Libraries need to be able to account for the money they spent even after the items and records have been removed from their collection. At the moment we lose the connection between order and bibliographic record when the record is deleted: -- -- Table structure for table `aqorders` -- DROP TABLE IF EXISTS `aqorders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `aqorders` ( `ordernumber` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key and unique identifier assigned by Koha to each line', `biblionumber` int(11) DEFAULT NULL COMMENT 'links the order to the biblio being ordered (biblio.biblionumber)', [...] CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, We do have the information about the deleted record and the items, but we need to preserve the link between order and deleted record in some way.
What are you suggesting? The only way to do it correctly is bug 20271...
(In reply to Jonathan Druart from comment #1) > What are you suggesting? > > The only way to do it correctly is bug 20271... We have waited for this for years now. It's a big data issue, I think it's time to look for alternative solutions. We had some ideas, but I'd really like to get some people together to discuss this next week: * Remove the FK for now * Add a separate column to store the deleted biblionumber to aqorders * Add a separate column for title information to aqorders. Maybe using a TT template for configuring/how what to store.
We should lower the priority here, it's not a blocker and it's a long standing bug.
blocker is defined as data loss. I hope to provide a patch before freezes.
Created attachment 150647 [details] [review] Bug 33262: Add deleted_biblionumber column to aqorders table This adds a new column deleted_biblionumber to the aqorders table. This will allow us to store the biblionumber of a deleted record, so we will still be able to tell what has been ordered once the record is deleted. Test plan in last patch.
Created attachment 150657 [details] [review] Bug 33262: Add deleted_biblionumber column to aqorders table This adds a new column deleted_biblionumber to the aqorders table. This will allow us to store the biblionumber of a deleted record, so we will still be able to tell what has been ordered once the record is deleted.
Created attachment 150658 [details] [review] Bug 33262: [DO NOT PUSH] Schema update
Created attachment 150659 [details] [review] Bug 33262: Store the biblionumber of a deleted record in the order line When a bibliographic record is deleted and linked to an order in the acquisition module, the biblionumber in the order line is currently deleted as well. This makes it impossible to tell what was ordered in the first place. This is a big issue for libraries that need to be able to track how money was spent over long periods of time. With this patch the biblionumber of the deleted record will be moved to a new column deleted_biblionumber in the order line, keeping the FK on biblionumber. To test: * Apply patch, run dataase update * Create a order/basket in the acquisitions module * Order several things, you can use existing records from the sample data * Make sure you order one of the records at least twice * Take a look at the aqorders table in your database, using a report: SELECT biblionumber, deleted_biblionumber, ordernumber from aqorders; * From the catalog, delete the items on the records and then the records Note: There is a warning about attached orders warning about consequences for acquisitions. I have chosen to keep it at this point in time, as there are still visible side effects of deleting the record, like bug 10758. * Re-run the report and compare results to the first run. deleted_biblionumber should now be filled with the biblionumber of the deleted record. biblionumber should be NULL/empty.
Created attachment 150660 [details] [review] Bug 33262: Unit tests prove t/db_dependent/Biblio.t
Created attachment 150661 [details] [review] Bug 33262: Add deleted_biblionumber column to aqorders table This adds a new column deleted_biblionumber to the aqorders table. This will allow us to store the biblionumber of a deleted record, so we will still be able to tell what has been ordered once the record is deleted.
Created attachment 150662 [details] [review] Bug 33262: [DO NOT PUSH) Schema update
Created attachment 150663 [details] [review] Bug 33262: Store the biblionumber of a deleted record in the order line When a bibliographic record is deleted and linked to an order in the acquisition module, the biblionumber in the order line is currently deleted as well. This makes it impossible to tell what was ordered in the first place. This is a big issue for libraries that need to be able to track how money was spent over long periods of time. With this patch the biblionumber of the deleted record will be moved to a new column deleted_biblionumber in the order line, keeping the FK on biblionumber. To test: * Apply patch, run dataase update * Create a order/basket in the acquisitions module * Order several things, you can use existing records from the sample data * Make sure you order one of the records at least twice * Take a look at the aqorders table in your database, using a report: SELECT biblionumber, deleted_biblionumber, ordernumber from aqorders; * From the catalog, delete the items on the records and then the records Note: There is a warning about attached orders warning about consequences for acquisitions. I have chosen to keep it at this point in time, as there are still visible side effects of deleting the record, like bug 10758. * Re-run the report and compare results to the first run. deleted_biblionumber should now be filled with the biblionumber of the deleted record. biblionumber should be NULL/empty.
Created attachment 150664 [details] [review] Bug 33262: Unit tests prove t/db_dependent/Biblio.t
"Deleted bibliographic record, can't find title" should not we adjust that then?
(In reply to Jonathan Druart from comment #14) > "Deleted bibliographic record, can't find title" should not we adjust that > then? This would be the next step, but I wanted to keep things small and manageable. this is taking care of the data only, but there are already bug 10758 and bug 10869 filed and linked.
Created attachment 150680 [details] [review] Bug 33262: Add deleted_biblionumber column to aqorders table This adds a new column deleted_biblionumber to the aqorders table. This will allow us to store the biblionumber of a deleted record, so we will still be able to tell what has been ordered once the record is deleted.
Created attachment 150681 [details] [review] Bug 33262: [DO NOT PUSH) Schema update
Created attachment 150682 [details] [review] Bug 33262: Store the biblionumber of a deleted record in the order line When a bibliographic record is deleted and linked to an order in the acquisition module, the biblionumber in the order line is currently deleted as well. This makes it impossible to tell what was ordered in the first place. This is a big issue for libraries that need to be able to track how money was spent over long periods of time. With this patch the biblionumber of the deleted record will be moved to a new column deleted_biblionumber in the order line, keeping the FK on biblionumber. To test: * Apply patch, run dataase update * Create a order/basket in the acquisitions module * Order several things, you can use existing records from the sample data * Make sure you order one of the records at least twice * Take a look at the aqorders table in your database, using a report: SELECT biblionumber, deleted_biblionumber, ordernumber from aqorders; * From the catalog, delete the items on the records and then the records Note: There is a warning about attached orders warning about consequences for acquisitions. I have chosen to keep it at this point in time, as there are still visible side effects of deleting the record, like bug 10758. * Re-run the report and compare results to the first run. deleted_biblionumber should now be filled with the biblionumber of the deleted record. biblionumber should be NULL/empty.
Created attachment 150683 [details] [review] Bug 33262: Unit tests prove t/db_dependent/Biblio.t
Created attachment 150684 [details] [review] Bug 33262: Add new field to REST API route for orders Without this patch the API driven list of pending orders on the order receive page was broken. With the patch, all should be well now. prove -v t/db_dependent/api/v1/acquisitions_orders.t
Created attachment 150702 [details] [review] Bug 33262: Add deleted_biblionumber column to aqorders table This adds a new column deleted_biblionumber to the aqorders table. This will allow us to store the biblionumber of a deleted record, so we will still be able to tell what has been ordered once the record is deleted. Signed-off-by: David Nind <david@davidnind.com>
Created attachment 150703 [details] [review] Bug 33262: [DO NOT PUSH) Schema update Signed-off-by: David Nind <david@davidnind.com>
Created attachment 150704 [details] [review] Bug 33262: Store the biblionumber of a deleted record in the order line When a bibliographic record is deleted and linked to an order in the acquisition module, the biblionumber in the order line is currently deleted as well. This makes it impossible to tell what was ordered in the first place. This is a big issue for libraries that need to be able to track how money was spent over long periods of time. With this patch the biblionumber of the deleted record will be moved to a new column deleted_biblionumber in the order line, keeping the FK on biblionumber. To test: * Apply patch, run dataase update * Create a order/basket in the acquisitions module * Order several things, you can use existing records from the sample data * Make sure you order one of the records at least twice * Take a look at the aqorders table in your database, using a report: SELECT biblionumber, deleted_biblionumber, ordernumber from aqorders; * From the catalog, delete the items on the records and then the records Note: There is a warning about attached orders warning about consequences for acquisitions. I have chosen to keep it at this point in time, as there are still visible side effects of deleting the record, like bug 10758. * Re-run the report and compare results to the first run. deleted_biblionumber should now be filled with the biblionumber of the deleted record. biblionumber should be NULL/empty. Signed-off-by: David Nind <david@davidnind.com>
Created attachment 150705 [details] [review] Bug 33262: Unit tests prove t/db_dependent/Biblio.t Signed-off-by: David Nind <david@davidnind.com>
Created attachment 150706 [details] [review] Bug 33262: Add new field to REST API route for orders Without this patch the API driven list of pending orders on the order receive page was broken. With the patch, all should be well now. prove -v t/db_dependent/api/v1/acquisitions_orders.t Signed-off-by: David Nind <david@davidnind.com>
Created attachment 150734 [details] [review] Bug 33262: Add deleted_biblionumber column to aqorders table This adds a new column deleted_biblionumber to the aqorders table. This will allow us to store the biblionumber of a deleted record, so we will still be able to tell what has been ordered once the record is deleted. Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Created attachment 150735 [details] [review] Bug 33262: [DO NOT PUSH) Schema update Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Created attachment 150736 [details] [review] Bug 33262: Store the biblionumber of a deleted record in the order line When a bibliographic record is deleted and linked to an order in the acquisition module, the biblionumber in the order line is currently deleted as well. This makes it impossible to tell what was ordered in the first place. This is a big issue for libraries that need to be able to track how money was spent over long periods of time. With this patch the biblionumber of the deleted record will be moved to a new column deleted_biblionumber in the order line, keeping the FK on biblionumber. To test: * Apply patch, run dataase update * Create a order/basket in the acquisitions module * Order several things, you can use existing records from the sample data * Make sure you order one of the records at least twice * Take a look at the aqorders table in your database, using a report: SELECT biblionumber, deleted_biblionumber, ordernumber from aqorders; * From the catalog, delete the items on the records and then the records Note: There is a warning about attached orders warning about consequences for acquisitions. I have chosen to keep it at this point in time, as there are still visible side effects of deleting the record, like bug 10758. * Re-run the report and compare results to the first run. deleted_biblionumber should now be filled with the biblionumber of the deleted record. biblionumber should be NULL/empty. Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Created attachment 150737 [details] [review] Bug 33262: Unit tests prove t/db_dependent/Biblio.t Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Created attachment 150738 [details] [review] Bug 33262: Add new field to REST API route for orders Without this patch the API driven list of pending orders on the order receive page was broken. With the patch, all should be well now. prove -v t/db_dependent/api/v1/acquisitions_orders.t Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Well done, Katrina.
Pushed to master for 23.05. Nice work everyone, thanks!
Many hands makes light work, thankyou everyone! Pushed to 22.11.x for the next release
Backported to 22.05.x for upcoming 22.05.13
Conflicts when trying to apply to 21.11, can you provide a backport?