This report is a follow-up report on bug 10869. Instead of 'manually' deleting the records in aqorders_items in routine DelOrder, we should just have MySQL do it with a cascaded delete.
Created attachment 24742 [details] [review] Bug 11617: Add itemnumber constraint to aqorders_items This patch adds a foreign key constraint to aqorders_items on itemnumber. It is a follow-up on report 10869 with changes to DelOrder. This removes the need to delete these records in DelOrder. Test plan: Do a new install, and check that the constraint on itemnumber is created in the table aqorders_items. Run an upgrade via install.pl?step=3&op=updatestructure. Check again.
Created attachment 24783 [details] [review] [SIGNED OFF] Bug 11617: Add itemnumber constraint to aqorders_items This patch adds a foreign key constraint to aqorders_items on itemnumber. It is a follow-up on report 10869 with changes to DelOrder. This removes the need to delete these records in DelOrder. REVISED TEST PLAN --------- 0) Backup DB, because you may want it back after. 1) go into mysql client, use the koha database and check: SHOW CREATE TABLE aqorders_items; -- should only be one constraint. 2) apply the patch 3) ./installer/mysql/data/updatedatabase.pl 4) go into mysql client, use the koha database and check: SHOW CREATE TABLE aqorders_items; -- should be two constraints. 5) drop the koha database and recreate it. 6) go to the staff client and do the web install. 7) go into mysql client, use the koha database and check: SHOW CREATE TABLE aqorders_items; -- should be two constraints. 8) Restore DB Note that the constraints are in different orders, but identical. The added constraint is: CONSTRAINT `aqorders_items_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
I am wondering if we really should get rid of the acquisition information when deleting an item. When an item is deleted, it's moved to deleted_items and can still be looked up for reporting purposes. It might also be interesting at that point to find out about the other history of the item, acquisition history included.
(In reply to Katrin Fischer from comment #3) > I am wondering if we really should get rid of the acquisition information > when deleting an item. When an item is deleted, it's moved to deleted_items > and can still be looked up for reporting purposes. It might also be > interesting at that point to find out about the other history of the item, > acquisition history included. Hello So you think a 'cancelled' status could be used for aqorders_items as well as for aqorders ? Mathieu
Maybe more a 'deleted' status to distinguish between orders being cancelled and items being deleted because of that and items being deleted from cataloguing? But this is just a thought, maybe it will make things too complicated and deleting is what people want?
Hm, thinking more about it, I think when cancelling an order the items are deleted and the order is marked as cancelled - do we really need to delete aqorders_items then? They would still refernece the original items and could be looked up in deleted_items. When the item is deleted later from cataloguing - would that currently touch the acquisitions data?
(In reply to Katrin Fischer from comment #3) > I am wondering if we really should get rid of the acquisition information > when deleting an item. When an item is deleted, it's moved to deleted_items > and can still be looked up for reporting purposes. It might also be > interesting at that point to find out about the other history of the item, > acquisition history included. Thanks for bringing that up, Katrin. In the current codebase deleteditems is actually only used in tools/export.pl. So like you mentioned, someone should use this connection in reporting. Note that this is (somewhat) theoretical, but possible. Koha currently removes the biblionumber from aqorders when a biblio is deleted (talking about removing acquisition history..) Does that make keeping the itemnumber in aqorders_items inconsistent? Or do we now stumble over the last way to retrieve the biblionumber still, via this itemnumber :) Also note that this discussion is in the scope of the AcqCreateItem pref. If you set that to Cataloging, you will have no itemnumbers at all in aqorders_items. That makes it a special table in the first place. The use of this table is more or less only relevant during the acquisition stage and no longer after ordering and receiving. With that in mind, I have a slight preference for cleaning up this table if an item is deleted. I will send a mail to the dev list and ask for some feedback. Setting to In Discussion.
I disagree with the assumption that the table is no longer interesting after the acquisition process is completed. The items tab where we added more and more information about acq is an example that libraries want to have these kind of information clearly visible. I also would say that an acquisiton process without items might be some kind of exception, but not the general rule right now. On the contrary I woudl argue that we should not remove the biblionumber on cancelling an order, as I can see no reason why this would be necessary.
(In reply to Katrin Fischer from comment #8) > On the contrary I woudl argue that we should not remove the > biblionumber on cancelling an order, as I can see no reason why this would > be necessary. If we keep the itemnumber, we should certainly keep the biblionumber too. Rather see a title (marked as deleted) instead of Deleted bibliographic notice, can't find title.
(In reply to M. de Rooy from comment #9) > (In reply to Katrin Fischer from comment #8) > > On the contrary I woudl argue that we should not remove the > > biblionumber on cancelling an order, as I can see no reason why this would > > be necessary. > If we keep the itemnumber, we should certainly keep the biblionumber too. > Rather see a title (marked as deleted) instead of Deleted bibliographic > notice, can't find title. It's a pity not having the foreign key constraints, but on balance there's too much potential need for library staff to need to refer to both ordered bibs and items for a period of time, even after the items or bibs are deleted in cataloging. This is going a bit far off, but I think we should consider adding a Boolean "is deleted" flag to biblio and items, to represent the case where an item or bib no longer is held by the library but is still referred to by other records that need to be kept for some archival purpose. This would make us more free to add FK constraints, at the cost of needing more code to distinguished between a merely deleted and a fully purged bib or item.
(In reply to Galen Charlton from comment #10) > It's a pity not having the foreign key constraints, but on balance there's > too much potential need for library staff to need to refer to both ordered > bibs and items for a period of time, even after the items or bibs are > deleted in cataloging. At least we have a FK on ordernumber in aqorders_items. Closing this report.