Summary: | Delete a subscription deletes the linked order | ||
---|---|---|---|
Product: | Koha | Reporter: | Séverine Queune <severine.queune> |
Component: | Serials | Assignee: | Katrin Fischer <katrin.fischer> |
Status: | CLOSED FIXED | QA Contact: | Testopia <testopia> |
Severity: | blocker | ||
Priority: | P5 - low | CC: | fridolin.somers, jonathan.druart, matt.blenkinsop, michaela.sieber |
Version: | Main | ||
Hardware: | All | ||
OS: | All | ||
Change sponsored?: | --- | Patch complexity: | Small patch |
Documentation contact: | Documentation submission: | ||
Text to go in the release notes: |
When an order had been created using the 'from a subscription' option and the subscription was deleted, the order line would be deleted with it, independent of its status or age. This caused problems with funds and budgets. With this patch, we will unlink order line and subscription on delete, but the order line will remain.
|
Version(s) released in: |
23.11.00,23.05.03,22.11.09
|
Circulation function: | |||
Bug Depends on: | 5343 | ||
Bug Blocks: | |||
Attachments: |
Bug 30451: Update FK constraint on aqorders.subscriptionid
Bug 30451: Update FK constraint on aqorders.subscriptionid Bug 30451: Update FK constraint on aqorders.subscriptionid |
Description
Séverine Queune
2022-04-04 15:31:20 UTC
+1 The least would be a warning, but I also think it should just 'unlink' the subscription from the order. I'd like to give this another push, the current behaviour means that we are losing data that is still needed in acq. This is a major data loss issue, upgrading severity. The problem is, that the budgets and fund values will be wrong for the current and past years. Sums don't add up etc. We just had a library that deleted 2 print serials that have now been bought as electronic versions. All data old data, subscriptions and acquisition data was lost when the record was deleted. 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)', `deleted_biblionumber` int(11) DEFAULT NULL COMMENT 'links the order to the deleted bibliographic record (deletedbiblio.biblionumber)', `entrydate` date DEFAULT NULL COMMENT 'the date the bib was added to the basket', [...] CONSTRAINT `aqorders_invoice_currency` FOREIGN KEY (`invoice_currency`) REFERENCES `currency` (`currency`) ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT `aqorders_subscriptionid` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; The constraint needs to be updated to be: ON DELETE SET NULL +1 Created attachment 153799 [details] [review] Bug 30451: Update FK constraint on aqorders.subscriptionid This updates the FK constrant from ON DELETE CASCADE to ON DELETE SET NULL. This means that if a subscription linked to an order is deleted, we no longer will also delete the order, but we will just set subscrptinid in the order to NULL. This will avoid data loss that can cause the budgets/funds not to add up anymore with the real espenses of the library. To test: Preparation: * Create 2 subscriptions on different records * Create a new basket * Use the "order from subscription" functionality to create order lines for both of your subscriptions * Close basket Without patch: * Delete the first subscription * Verify the order line for this subscription is gone from your basket Apply patch: * Run database update and restart_all * Delete the second subscription * Verify the order line now remained in the basket Created attachment 153818 [details] [review] Bug 30451: Update FK constraint on aqorders.subscriptionid This updates the FK constrant from ON DELETE CASCADE to ON DELETE SET NULL. This means that if a subscription linked to an order is deleted, we no longer will also delete the order, but we will just set subscrptinid in the order to NULL. This will avoid data loss that can cause the budgets/funds not to add up anymore with the real espenses of the library. To test: Preparation: * Create 2 subscriptions on different records * Create a new basket * Use the "order from subscription" functionality to create order lines for both of your subscriptions * Close basket Without patch: * Delete the first subscription * Verify the order line for this subscription is gone from your basket Apply patch: * Run database update and restart_all * Delete the second subscription * Verify the order line now remained in the basket Signed-off-by: Michaela Sieber <michaela.sieber@kit.edu> Created attachment 153966 [details] [review] Bug 30451: Update FK constraint on aqorders.subscriptionid This updates the FK constrant from ON DELETE CASCADE to ON DELETE SET NULL. This means that if a subscription linked to an order is deleted, we no longer will also delete the order, but we will just set subscrptinid in the order to NULL. This will avoid data loss that can cause the budgets/funds not to add up anymore with the real espenses of the library. To test: Preparation: * Create 2 subscriptions on different records * Create a new basket * Use the "order from subscription" functionality to create order lines for both of your subscriptions * Close basket Without patch: * Delete the first subscription * Verify the order line for this subscription is gone from your basket Apply patch: * Run database update and restart_all * Delete the second subscription * Verify the order line now remained in the basket Signed-off-by: Michaela Sieber <michaela.sieber@kit.edu> JD amended patch: perl tidy Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> I would not consider this a blocker, this is an historical behavior. (In reply to Jonathan Druart from comment #9) > I would not consider this a blocker, this is an historical behavior. Historical yes, but we defined blocker by data loss and that's what it does. And I think rather unexpectedly, just often hard to detect right away. Pushed to master for 23.11. Nice work everyone, thanks! Pushed to 23.05.x for 23.05.03 Nice work everyone! Pushed to 22.11.x for next release Ah recent push to master : 9bb23b7d5e Bug 30451: DBIC schema Pushed to 23.05.x for 23.05.04 Follow-up patch also backported to 22.11.x |