Created attachment 31924 [details] [review] Bug 13007: Add a foreign key for suggestions.budgetid The DB field suggestions.budgetid should be linked to aqbudgets.budget_id. If the fund is removed, this column should be set to NULL. Test plan: 0/ Verify it is not possible to create an suggestion not linked to a fund via the Koha interface. 1/ Using your SQL CLI (or equivalent), create or update 1+ suggestions and set "0" in the budgetid field. 2/ Execute the updatedabase script. 3/ Verify that your suggestion is unlinked to the nonexistent fund. 4/ Verify the constraint has correctly been added (show create table suggestions).
*** Bug 13005 has been marked as a duplicate of this bug. ***
Created attachment 31945 [details] [review] Bug 13007: Add a foreign key for suggestions.budgetid The DB field suggestions.budgetid should be linked to aqbudgets.budget_id. If the fund is removed, this column should be set to NULL. Test plan: 0/ Verify it is not possible to create an suggestion not linked to a fund via the Koha interface. 1/ Using your SQL CLI (or equivalent), create or update 1+ suggestions and set "0" in the budgetid field. 2/ Execute the updatedabase script. 3/ Verify that your suggestion is unlinked to the nonexistent fund. 4/ Verify the constraint has correctly been added (show create table suggestions).
I've tried to apply the patches against master 3.17.00.029 At first I've applied the 6 patches of 12627. --------------------------------------- The patch "Bug 13007: Add a foreign key for suggestions.budgetid" needs a trivial rebase. Moreover, this wrong line of the patch 13007: + print "Upgrade to $DBversion done (Bug 13007 - Add new foreign key suggestions.budgetid\n"); should be changed in the following right one: + print "Upgrade to $DBversion done (Bug 13007 - Add new foreign key suggestions.budgetid)\n"; --------------------------------------- About the first step: 0/ Verify it is not possible to create a suggestion not linked to a fund via the Koha interface. in intranet, "any" is an option in the dropdown list on "Acquisition information" for selecting a fund to link to the suggestion. So IMO it's still easy to create suggestions not linked to a fund. So I pass the patch to "Failed QA" STATUS.
Created attachment 33241 [details] [review] Bug 13007: Add a foreign key for suggestions.budgetid The DB field suggestions.budgetid should be linked to aqbudgets.budget_id. If the fund is removed, this column should be set to NULL. Test plan: 1/ Using your SQL CLI (or equivalent), create or update 1+ suggestions and set "0" in the budgetid field (or a nonexistent budget id). 2/ Execute the updatedabase script. 3/ Verify that your suggestion is unlinked to the nonexistent fund. 4/ Verify the constraint has correctly been added (show create table suggestions). 5/ Check that this change does not affect the behavior on adding a suggestion (linked to a fund or not).
Created attachment 33242 [details] [review] Bug 13007: Add a foreign key for suggestions.budgetid The DB field suggestions.budgetid should be linked to aqbudgets.budget_id. If the fund is removed, this column should be set to NULL. Test plan: 1/ Using your SQL CLI (or equivalent), create or update 1+ suggestions and set "0" in the budgetid field (or a nonexistent budget id). 2/ Execute the updatedabase script. 3/ Verify that your suggestion is unlinked to the nonexistent fund. 4/ Verify the constraint has correctly been added (show create table suggestions). 5/ Check that this change does not affect the behavior on adding a suggestion (linked to a fund or not).
(In reply to Paola Rossi from comment #4) > About the first step: > 0/ Verify it is not possible to create a suggestion not linked to a fund via > the Koha interface. > > in intranet, "any" is an option in the dropdown list on "Acquisition > information" for selecting a fund to link to the suggestion. So IMO it's > still easy to create suggestions not linked to a fund. > > So I pass the patch to "Failed QA" STATUS. I don't remember why I wrote that... If you don't choose a fund, the value in DB will be NULL. Which is acceptable and normal. I updated the test plan.
I've applied the patch against master 3.17.00.055 The patch needed some trivial rebasing. The number of tests is not increased (error). When I tried to add a new suggestion linked to "Any" fund, koha said: Software error: DBIx::Class::ResultSet::create(): Cannot add or update a child row: a foreign key constraint fails (`koha`.`suggestions`, CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE) at /var/root-koha/.../C4/Suggestions.pm line 449 On the contrary, adding a new suggestion linked to a real fund is OK. So I pass the patch to "Failed QA" status. The "show create table suggestions" command gave: CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE )
Created attachment 33643 [details] [review] Bug 13007: Add a foreign key for suggestions.budgetid The DB field suggestions.budgetid should be linked to aqbudgets.budget_id. If the fund is removed, this column should be set to NULL. Test plan: 1/ Using your SQL CLI (or equivalent), create or update 1+ suggestions and set "0" in the budgetid field (or a nonexistent budget id). 2/ Execute the updatedabase script. 3/ Verify that your suggestion is unlinked to the nonexistent fund. 4/ Verify the constraint has correctly been added (show create table suggestions). 5/ Check that this change does not affect the behavior on adding a suggestion (linked to a fund or not).
Created attachment 33644 [details] [review] Bug 13007: Special case budgetid == '' In this special case (the suggestion is linked to "all funds"), the budgetid value should be NULL in DB.
Created attachment 33651 [details] [review] Bug 13007: Add a foreign key for suggestions.budgetid I've applied the patches against master 3.17.00.057 Everything is OK. So I pass the patch to "Signed Off" status.
Created attachment 33653 [details] [review] Bug 13007: Special case budgetid == ''
Created attachment 33807 [details] [review] [PASSED QA] Bug 13007: Add a foreign key for suggestions.budgetid The DB field suggestions.budgetid should be linked to aqbudgets.budget_id. If the fund is removed, this column should be set to NULL. Test plan: 1/ Using your SQL CLI (or equivalent), create or update 1+ suggestions and set "0" in the budgetid field (or a nonexistent budget id). 2/ Execute the updatedabase script. 3/ Verify that your suggestion is unlinked to the nonexistent fund. 4/ Verify the constraint has correctly been added (show create table suggestions). 5/ Check that this change does not affect the behavior on adding a suggestion (linked to a fund or not). Signed-off-by: Paola Rossi <paola.rossi@cineca.it> Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Created attachment 33808 [details] [review] [PASSED QA] Bug 13007: Special case budgetid == '' In this special case (the suggestion is linked to "all funds"), the budgetid value should be NULL in DB. Signed-off-by: Paola Rossi <paola.rossi@cineca.it> Signed-off-by: Katrin Fischer <Katrin.Fischer.83@web.de> Works as described, passes old and new tests.
Hi Jonathan, tried testing 13014, but this dependency no longer applies, can you please rebase?
Created attachment 35601 [details] [review] Bug 13007: Add a foreign key for suggestions.budgetid The DB field suggestions.budgetid should be linked to aqbudgets.budget_id. If the fund is removed, this column should be set to NULL. Test plan: 1/ Using your SQL CLI (or equivalent), create or update 1+ suggestions and set "0" in the budgetid field (or a nonexistent budget id). 2/ Execute the updatedabase script. 3/ Verify that your suggestion is unlinked to the nonexistent fund. 4/ Verify the constraint has correctly been added (show create table suggestions). 5/ Check that this change does not affect the behavior on adding a suggestion (linked to a fund or not). Signed-off-by: Paola Rossi <paola.rossi@cineca.it> Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Created attachment 35602 [details] [review] Bug 13007: Special case budgetid == '' In this special case (the suggestion is linked to "all funds"), the budgetid value should be NULL in DB. Signed-off-by: Paola Rossi <paola.rossi@cineca.it> Signed-off-by: Katrin Fischer <Katrin.Fischer.83@web.de> Works as described, passes old and new tests.
Last patches fixed conflict with bug 13378.
Patches pushed to master. Thanks Jonathan!