stocknumber and invertory books in Koha
I need help from people who use stocknumbers in their Koha's installs. I assume this is mostly Europe-specific since we are required by low to keep invetory books with each item in our library but I hope that other people might have similar legal requirements. For a start, why is index defined on items.stocknumber non-unique? I would assume that it has to be unique like barcode is (at least that's our legal requirement). Other than that, we need to have some representation of "inventory book" which I plan to implement using additional local table in which I will record stocknumber, biblionumber and various other fields required for invertory book reporting. This duplicates data (which is bad if you ever heard for normal forms in RDBMS), but it's required to cover case of erasing items. If we don't have a copy of item, erased it would also erase entry from invetory book which is very, very bad. During implementation of this feature, I stumbled upon bug 7451[1] which breaks AJAX value_builder in which I plan to implement assigment of next invetory number and copying of data into separate invetory audit table. Would this value_builder be useful to other Koha users, or is it too specific for our use-case? 1: http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7451 -- Dobrica Pavlinusic 2share!2flame dpavlin@rot13.org Unix addict. Internet consultant. http://www.rot13.org/~dpavlin
Hi Dobrica, Il 17/01/2012 16:32, Dobrica Pavlinusic ha scritto:
I need help from people who use stocknumbers in their Koha's installs. [..] For a start, why is index defined on items.stocknumber non-unique? I would assume that it has to be unique like barcode is (at least that's our legal requirement).
I think it is impossiblr to ask for a unique value at MySQL level. In my experience I see US libraries with not-unique or NULL inventories. In fact in Aleph500 there is the same situation (barcode unique, inventory what do you want). Bye Zeno -- Dott. Zeno Tajoli tajoliAT_SPAM_no_prendiATcilea.it fax +39 02 2135520 CILEA - Consorzio Interuniversitario http://www.cilea.it/disclaimer
On Tue, Jan 17, 2012 at 04:42:38PM +0100, Zeno Tajoli wrote:
Hi Dobrica,
Il 17/01/2012 16:32, Dobrica Pavlinusic ha scritto:
I need help from people who use stocknumbers in their Koha's installs. [..] For a start, why is index defined on items.stocknumber non-unique? I would assume that it has to be unique like barcode is (at least that's our legal requirement).
I think it is impossiblr to ask for a unique value at MySQL level.
It's not totally impossible. You just have to use trasactions and additional table to hold last value[1] or in case on invetory all values and a little bit of additional meta data[2] 1: http://git.rot13.org/?p=koha.git;a=commitdiff;h=01580945128cb727a404249c7898... This is first implementation of sequences which uses mysql routine to provide increment but also requires modification of CGI code to call it on save action. 2: http://git.rot13.org/?p=koha.git;a=blob;f=cataloguing/value_builder/ffzg-sto... This is a value_builder only implementation which moves mysql routine into AJAX callback with transaction. This nicely side-steps problem with uniqueness (since every AJAX request will produce new value and populate one row in invetory table) but it's triggered on plugin invocation (dots in Koha interface) as opposed to save time. I would really love if value builder plugins would have some kind of "on_save" hook, in which I can get item record and save some additional data into inventory table. This would also help with cases in which plugin is activated (and number reserved) but item is never saved. For now I will have to do this via cron script (which I plan to run daily, so you can tweak item data during same day when new stock number is issued and invetory will reflect state at end of this day). -- Dobrica Pavlinusic 2share!2flame dpavlin@rot13.org Unix addict. Internet consultant. http://www.rot13.org/~dpavlin
Hi Dobrica I think Zeno didn't mean technically impossible to provide uniqueness at the DB level, just that lots of libraries do not want unique stock numbers, so enforcing it at db level (with a unique constraint isn't possible in Koha). Enforcing it in the code, as you have done, with the caveat that it needs to be controlled by a syspref so that those who want it unique or not can both be satisfied is certainly possibly Chris On Wednesday, 18 January 2012, Dobrica Pavlinusic wrote:
Hi Dobrica,
Il 17/01/2012 16:32, Dobrica Pavlinusic ha scritto:
I need help from people who use stocknumbers in their Koha's installs. [..] For a start, why is index defined on items.stocknumber non-unique? I would assume that it has to be unique like barcode is (at least
On Tue, Jan 17, 2012 at 04:42:38PM +0100, Zeno Tajoli wrote: that's
our legal requirement).
I think it is impossiblr to ask for a unique value at MySQL level.
It's not totally impossible. You just have to use trasactions and additional table to hold last value[1] or in case on invetory all values and a little bit of additional meta data[2]
1: http://git.rot13.org/?p=koha.git;a=commitdiff;h=01580945128cb727a404249c7898...
This is first implementation of sequences which uses mysql routine to provide increment but also requires modification of CGI code to call it on save action.
2: http://git.rot13.org/?p=koha.git;a=blob;f=cataloguing/value_builder/ffzg-sto...
This is a value_builder only implementation which moves mysql routine into AJAX callback with transaction.
This nicely side-steps problem with uniqueness (since every AJAX request will produce new value and populate one row in invetory table) but it's triggered on plugin invocation (dots in Koha interface) as opposed to save time.
I would really love if value builder plugins would have some kind of "on_save" hook, in which I can get item record and save some additional data into inventory table. This would also help with cases in which plugin is activated (and number reserved) but item is never saved.
For now I will have to do this via cron script (which I plan to run daily, so you can tweak item data during same day when new stock number is issued and invetory will reflect state at end of this day).
-- Dobrica Pavlinusic 2share!2flame dpavlin@rot13.org <javascript:;> Unix addict. Internet consultant. http://www.rot13.org/~dpavlin _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org <javascript:;> 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/
Hi to all, Il 18/01/2012 07:20, Chris Cormack ha scritto:
I think Zeno didn't mean technically impossible to provide uniqueness at the DB level, just that lots of libraries do not want unique stock numbers, so enforcing it at db level (with a unique constraint isn't possible in Koha).
Enforcing it in the code, as you have done, with the caveat that it needs to be controlled by a syspref so that those who want it unique or not can both be satisfied is certainly possibly
sorry for delay, but Chris writes exactly what I means. The problem is that is not possible to enforce unique stock numbers at db level (like barcode). BUT I like very much the idea to give the option to enforce it at code level, with a syspref to swith ON/OFF. For Dobrica: Can you write the translation of 'signatura zatvorenog spremista' and 'Implementirati višestruke nizove signatura prema formatu'. from: http://git.rot13.org/?p=koha.git;a=commitdiff;h=01580945128cb727a404249c7898... Google translate doesn't help. Bye Zeno Tajoli -- Dott. Zeno Tajoli tajoliAT_SPAM_no_prendiATcilea.it fax +39 02 2135520 CILEA - Consorzio Interuniversitario http://www.cilea.it/disclaimer
On Wed, Jan 18, 2012 at 03:51:06PM +0100, Zeno Tajoli wrote:
Il 18/01/2012 07:20, Chris Cormack ha scritto:
I think Zeno didn't mean technically impossible to provide uniqueness at the DB level, just that lots of libraries do not want unique stock numbers, so enforcing it at db level (with a unique constraint isn't possible in Koha).
Enforcing it in the code, as you have done, with the caveat that it needs to be controlled by a syspref so that those who want it unique or not can both be satisfied is certainly possibly
sorry for delay, but Chris writes exactly what I means. The problem is that is not possible to enforce unique stock numbers at db level (like barcode).
Sorry, I was probably too much into code that day :-)
BUT I like very much the idea to give the option to enforce it at code level, with a syspref to swith ON/OFF.
Chris, do we allready have some mechanisam to enforce uniqueness at code level for fields in MARC or items table? I'm asking this because we are mostly dropping MySQL errors on the floor as opposed to returning errors to users (adding items in acquisition is my current example of this) so enforcing it with unique index on database field is not really a solution...
For Dobrica: Can you write the translation of 'signatura zatvorenog spremista' and 'Implementirati višestruke nizove signatura prema formatu'. from: http://git.rot13.org/?p=koha.git;a=commitdiff;h=01580945128cb727a404249c7898...
Google translate doesn't help.
We have closed stocks on basement level in our library where shelfing system and callnumbers are based on physical dymensions of items (prefix in callnumber). In next iteration of this, I will probably also rewrite this plugin to use AJAX instread of mysql functions. This will make it database independent, and mysqldump doesn't drop function by defaults (and flag is called --routines just to be confusing), so backups are also tricky (and reson to abondon mysql functions). -- Dobrica Pavlinusic 2share!2flame dpavlin@rot13.org Unix addict. Internet consultant. http://www.rot13.org/~dpavlin
participants (3)
-
Chris Cormack -
Dobrica Pavlinusic -
Zeno Tajoli