Need advice for Bug 10758 - Show bibliographic information of deleted records in acquisitions
Hello Currently, when a biblio is deleted, it is moved to deletedbiblio (and biblioitems to deletedbiblioitems). Acquisition pages (basket.pl for example) are looking for bibliographic information about orders only in biblio and biblioitems table. So, if the biblio is deleted, the title cannot be found, while it is present in database, in deletedbiblio table. Same thing for statistics guided reports : if the biblio is deleted, the reports are unable to get information on the document. This is *very *annoying for us librarians, and taking into account the deleted records would be nice for us. So I am planning to make some patches to improve that: bug http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10758 Katrin Fisher has taken part to the discussion on this bug, but we are not sure of the best way of improving Koha's behavior. I suppose we could make a "union all" between (biblio and deletedbiblio) and (biblio and deletedbiblio) in the subs getting information about orders in C4::Acquisitions. ...BUT we cannot do that currently, because of the "ON DELETE SET NULL" constraint in table aqorders for biblio.biblionumber column (the biblionumber is deleted when the biblio is deleted...) So, I suppose we need to change the structure of aqorders - either by removing the foreign key from acqorders.biblionumber to biblio.biblionumber - or editing the constraint (I don't think it is possible) - or by creating a new column 'deletedbiblionumber" in acqorders I would prefer the 3d option (the "cleaner" for me, as I suppose removing foreign key is not good for integrity of DB), but I am very very far from being a database expert or a real developer. So, what is your opinion on that subject? Regards, M. Saby -- Mathieu Saby Service d'Informatique Documentaire Service Commun de Documentation Université Rennes 2 Téléphone : 02 99 14 12 65 Courriel : mathieu.saby@univ-rennes2.fr
Of course, I suppose if a column "deletedbiblionumber" is added in acqorders, we won't be able to make a "union all" between biblio and deletedbiblio... But that's not the more complex part of the problem. M. Saby Le 15/09/2013 15:41, Mathieu Saby a écrit :
Hello Currently, when a biblio is deleted, it is moved to deletedbiblio (and biblioitems to deletedbiblioitems). Acquisition pages (basket.pl for example) are looking for bibliographic information about orders only in biblio and biblioitems table. So, if the biblio is deleted, the title cannot be found, while it is present in database, in deletedbiblio table. Same thing for statistics guided reports : if the biblio is deleted, the reports are unable to get information on the document.
This is *very *annoying for us librarians, and taking into account the deleted records would be nice for us.
So I am planning to make some patches to improve that: bug http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10758 Katrin Fisher has taken part to the discussion on this bug, but we are not sure of the best way of improving Koha's behavior.
I suppose we could make a "union all" between (biblio and deletedbiblio) and (biblio and deletedbiblio) in the subs getting information about orders in C4::Acquisitions. ...BUT we cannot do that currently, because of the "ON DELETE SET NULL" constraint in table aqorders for biblio.biblionumber column (the biblionumber is deleted when the biblio is deleted...) So, I suppose we need to change the structure of aqorders - either by removing the foreign key from acqorders.biblionumber to biblio.biblionumber - or editing the constraint (I don't think it is possible) - or by creating a new column 'deletedbiblionumber" in acqorders
I would prefer the 3d option (the "cleaner" for me, as I suppose removing foreign key is not good for integrity of DB), but I am very very far from being a database expert or a real developer.
So, what is your opinion on that subject?
Regards, M. Saby
-- Mathieu Saby Service d'Informatique Documentaire Service Commun de Documentation Université Rennes 2 Téléphone : 02 99 14 12 65 Courriel :mathieu.saby@univ-rennes2.fr
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- Mathieu Saby Service d'Informatique Documentaire Service Commun de Documentation Université Rennes 2 Téléphone : 02 99 14 12 65 Courriel : mathieu.saby@univ-rennes2.fr
Hi, On Sun, Sep 15, 2013 at 6:41 AM, Mathieu Saby <mathieu.saby@univ-rennes2.fr>wrote:
- or by creating a new column 'deletedbiblionumber" in acqorders
I would prefer the 3d option (the "cleaner" for me, as I suppose removing foreign key is not good for integrity of DB), but I am very very far from being a database expert or a real developer.
For the sake of listing more possibilities, another option would be to merge deletedbiblio into biblio and add a Boolean column to indicate whether the bib is logically deleted or not. Regards, Galen -- Galen Charlton Manager of Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
Yep, that was one of my ideas too. So, less work will be necessary on C4 subs. But as said K. Fisher, if there are "deleted..." tables, it is probably for one reason?? Mathieu Galen Charlton a écrit :
Hi,
On Sun, Sep 15, 2013 at 6:41 AM, Mathieu Saby <mathieu.saby@univ-rennes2.fr <mailto:mathieu.saby@univ-rennes2.fr>> wrote:
- or by creating a new column 'deletedbiblionumber" in acqorders
I would prefer the 3d option (the "cleaner" for me, as I suppose removing foreign key is not good for integrity of DB), but I am very very far from being a database expert or a real developer.
For the sake of listing more possibilities, another option would be to merge deletedbiblio into biblio and add a Boolean column to indicate whether the bib is logically deleted or not.
Regards,
Galen -- Galen Charlton Manager of Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com <mailto:gmc@esilibrary.com> direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
-- Mathieu Saby Service d'Informatique Documentaire Service Commun de la Documentation Université Rennes 2 Téléphone : 02 99 14 12 65 Courriel : mathieu.saby@univ-rennes2.fr
Hi, On Mon, Sep 16, 2013 at 9:33 AM, Mathieu Saby <mathieu.saby@univ-rennes2.fr>wrote:
** Yep, that was one of my ideas too. So, less work will be necessary on C4 subs. But as said K. Fisher, if there are "deleted..." tables, it is probably for one reason??
Here are some pros and cons of keeping them in the same table, but with a deleted flag. PROs -------------- [1] Foreign key relationships can be expressed and enforced without contortions. [2] Certain types of UNION queries would not required. CONs ---------- [1] Most SQL queries would need the equivalent of a 'AND NOT deleted' clause tacked on to them. Easier to do cleanly with DBIx::Class, perhaps, but that would still leave SQL reports (and legacy code). [2] Code would have to ensure that you don't end up with active items attached to logically deleted bibs. [3] There may be a point where a library or consortium that has been running Koha for a long time ends up with a significant portion of their biblio table containing logically deleted records, to the positive detriment of query performance. Not really a clear win either way. With some reluctance, as it seems a bit messy, I guess I lean towards creating an aqorders.deletedbiblionumber column. Regards, Galen -- Galen Charlton Manager of Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
I know in our previous ILS (Loris), there were a specific table for deleted records, like now in Koha. By the way, if this solution is approved, don't you think a field with the date of suppression, like in aqorders would maybe more useful than a "1/0" flag ? Galen Charlton a écrit :
Hi,
On Mon, Sep 16, 2013 at 9:33 AM, Mathieu Saby <mathieu.saby@univ-rennes2.fr <mailto:mathieu.saby@univ-rennes2.fr>> wrote:
Yep, that was one of my ideas too. So, less work will be necessary on C4 subs. But as said K. Fisher, if there are "deleted..." tables, it is probably for one reason??
Here are some pros and cons of keeping them in the same table, but with a deleted flag.
PROs -------------- [1] Foreign key relationships can be expressed and enforced without contortions. [2] Certain types of UNION queries would not required.
CONs ---------- [1] Most SQL queries would need the equivalent of a 'AND NOT deleted' clause tacked on to them. Easier to do cleanly with DBIx::Class, perhaps, but that would still leave SQL reports (and legacy code). [2] Code would have to ensure that you don't end up with active items attached to logically deleted bibs. [3] There may be a point where a library or consortium that has been running Koha for a long time ends up with a significant portion of their biblio table containing logically deleted records, to the positive detriment of query performance. For your 3 : I think it would be easy to write a maintenance script deleting from database all deleted biblios, or all biblios deleted since a certain date, with an option for keeping those used in acqorders table for non-cancelled-ordered.
Regards, Mathieu
Not really a clear win either way.
With some reluctance, as it seems a bit messy, I guess I lean towards creating an aqorders.deletedbiblionumber column.
Regards,
Galen -- Galen Charlton Manager of Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com <mailto:gmc@esilibrary.com> direct: +1 770-709-5581 <tel:%2B1%20770-709-5581> cell: +1 404-984-4366 <tel:%2B1%20404-984-4366> skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
-- Mathieu Saby Service d'Informatique Documentaire Service Commun de la Documentation Université Rennes 2 Téléphone : 02 99 14 12 65 Courriel : mathieu.saby@univ-rennes2.fr
Hi As performance issue on search or circulation would be more harmful that performance issue on acquisition module, what about - keeping the deleted* tables - adding a new column "detetedbiblionumber" in aqorders - editing some subs in C4 for taking into using deleted records as well as existing one - (maybe doing the same work for deleteditems) If you agree with the idea, I can try to make the changes. Mathieu Galen Charlton a écrit :
Hi,
On Mon, Sep 16, 2013 at 9:33 AM, Mathieu Saby <mathieu.saby@univ-rennes2.fr <mailto:mathieu.saby@univ-rennes2.fr>> wrote:
Yep, that was one of my ideas too. So, less work will be necessary on C4 subs. But as said K. Fisher, if there are "deleted..." tables, it is probably for one reason??
Here are some pros and cons of keeping them in the same table, but with a deleted flag.
PROs -------------- [1] Foreign key relationships can be expressed and enforced without contortions. [2] Certain types of UNION queries would not required.
CONs ---------- [1] Most SQL queries would need the equivalent of a 'AND NOT deleted' clause tacked on to them. Easier to do cleanly with DBIx::Class, perhaps, but that would still leave SQL reports (and legacy code). [2] Code would have to ensure that you don't end up with active items attached to logically deleted bibs. [3] There may be a point where a library or consortium that has been running Koha for a long time ends up with a significant portion of their biblio table containing logically deleted records, to the positive detriment of query performance.
Not really a clear win either way.
With some reluctance, as it seems a bit messy, I guess I lean towards creating an aqorders.deletedbiblionumber column.
Regards,
Galen -- Galen Charlton Manager of Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com <mailto:gmc@esilibrary.com> direct: +1 770-709-5581 <tel:%2B1%20770-709-5581> cell: +1 404-984-4366 <tel:%2B1%20404-984-4366> skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
-- Mathieu Saby Service d'Informatique Documentaire Service Commun de la Documentation Université Rennes 2 Téléphone : 02 99 14 12 65 Courriel : mathieu.saby@univ-rennes2.fr
Hi, On Fri, Sep 20, 2013 at 12:41 AM, Mathieu Saby <mathieu.saby@univ-rennes2.fr
wrote:
** As performance issue on search or circulation would be more harmful that performance issue on acquisition module, what about - keeping the deleted* tables - adding a new column "detetedbiblionumber" in aqorders - editing some subs in C4 for taking into using deleted records as well as existing one - (maybe doing the same work for deleteditems)
Works for me. Regards, Galen -- Galen Charlton Manager of Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
Thanks I will try to work on that if I have time enough. Mathieu Le 20/09/2013 17:53, Galen Charlton a écrit :
Hi,
On Fri, Sep 20, 2013 at 12:41 AM, Mathieu Saby <mathieu.saby@univ-rennes2.fr <mailto:mathieu.saby@univ-rennes2.fr>> wrote:
As performance issue on search or circulation would be more harmful that performance issue on acquisition module, what about - keeping the deleted* tables - adding a new column "detetedbiblionumber" in aqorders - editing some subs in C4 for taking into using deleted records as well as existing one - (maybe doing the same work for deleteditems)
Works for me.
Regards,
Galen -- Galen Charlton Manager of Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com <mailto:gmc@esilibrary.com> direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
-- Mathieu Saby Service d'Informatique Documentaire Service Commun de Documentation Université Rennes 2 Téléphone : 02 99 14 12 65 Courriel : mathieu.saby@univ-rennes2.fr
Patch submitted ;-) I have made this one dependant from 10869, because 10869 is a needed bugfix, that will maybe be applied to maintenance versions, while 10758 is an ENH, and maybe won't be applied to maintenance versions. Mathieu Le 20/09/2013 18:07, Mathieu Saby a écrit :
Thanks I will try to work on that if I have time enough.
Mathieu
Le 20/09/2013 17:53, Galen Charlton a écrit :
Hi,
On Fri, Sep 20, 2013 at 12:41 AM, Mathieu Saby <mathieu.saby@univ-rennes2.fr <mailto:mathieu.saby@univ-rennes2.fr>> wrote:
As performance issue on search or circulation would be more harmful that performance issue on acquisition module, what about - keeping the deleted* tables - adding a new column "detetedbiblionumber" in aqorders - editing some subs in C4 for taking into using deleted records as well as existing one - (maybe doing the same work for deleteditems)
Works for me.
Regards,
Galen -- Galen Charlton Manager of Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com <mailto:gmc@esilibrary.com> direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
-- Mathieu Saby Service d'Informatique Documentaire Service Commun de Documentation Université Rennes 2 Téléphone : 02 99 14 12 65 Courriel :mathieu.saby@univ-rennes2.fr
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- Mathieu Saby Service d'Informatique Documentaire Service Commun de Documentation Université Rennes 2 Téléphone : 02 99 14 12 65 Courriel : mathieu.saby@univ-rennes2.fr
participants (2)
-
Galen Charlton -
Mathieu Saby