Hi, in helping a group to migrate from Alice/Oasis to Koha, I've been asked to automatically import the current loans into Koha. I haven't noticed any scripts to achieve this so I'm having a bash at it here in perl. I'm currently figuring out how exactly the data maps between the two systems. I wonder if I could ask one or two questions about the Koha database. I gather the data I need to create is in the "issues" table which looks like this: +-----------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------+------+-----+---------+-------+ | borrowernumber | int(11) | | MUL | 0 | | | itemnumber | int(11) | | MUL | 0 | | | date_due | date | YES | | NULL | | | branchcode | char(4) | YES | | NULL | | | issuingbranch | char(18) | YES | | NULL | | | returndate | date | YES | | NULL | | | lastreneweddate | date | YES | | NULL | | | return | char(4) | YES | | NULL | | | renewals | tinyint(4) | YES | | NULL | | | timestamp | timestamp(14) | YES | | NULL | | +-----------------+---------------+------+-----+---------+-------+ - The borrowernumber and itemnumber obviously relate to the primary keys in items and borrowers. - Do all loans stay in this table indefinitely even after they are returned? ie is this where the loan history is kept? - In terms of the dates, it looks like "date_due" is the current due date (duh!), "returndate" must then be the date the book is returned -- NULL initially, lastreneweddate is the date of last renewal. If this is so, do you store the date the book was first lent or not? Also, do you store the "renew history" of a given loan and where? - Do I guess correctly that there a single entry in this table per loan, not one for the initial loan and one for every renewal? - What exactly goes in the "return" field? - Is "renewals" an integer count of the number of times this particular loan has been renewed? - Should the timestamp be updated every time any record is changed or at record creation? - If I wanted statistics of how many loans/renewals were given out on a given day some time ago, could I do this? It appears not. I've looked over the wiki but i can't find this stuff directly. I'm happy wo wikifi an "issues table" page based on answers so that this info will be more easily available. Same for borrowers which I've already been over. Only if people thing this is a good idea of course. I guess it might complement: http://wiki.koha.org/doku.php?id=en:development:dbschema Many thanks, Gavin
Hi Gavin Gavin McCullagh wrote:
- The borrowernumber and itemnumber obviously relate to the primary keys in items and borrowers.
Yep
- Do all loans stay in this table indefinitely even after they are returned? ie is this where the loan history is kept?
Partially :-) See my answer to the questions below. The loans stay in this table after returned but its not where the totality of the history is kept.
- In terms of the dates, it looks like "date_due" is the current due date (duh!), "returndate" must then be the date the book is returned -- NULL initially, lastreneweddate is the date of last renewal. If this is so, do you store the date the book was first lent or not? Also, do you store the "renew history" of a given loan and where?
Yes, but not in the issues table. In the statistics table instead. The statistics table looks like this +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ | datetime | branch | proccode | value | type | other | usercode | itemnumber | itemtype | borrowernumber | +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ | 2006-08-05 13:01:23 | L | NULL | 0.0000 | issue | | NULL | 139167 | BJN | 8846 | | 2006-08-05 13:01:19 | L | NULL | 0.0000 | issue | | NULL | 136284 | BJN | 8846 | | 2006-08-05 13:01:16 | L | NULL | 0.0000 | issue | | NULL | 76508 | BJN | 8846 | +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+
- Do I guess correctly that there a single entry in this table per loan, not one for the initial loan and one for every renewal? Thats right a single entry per loan.
- What exactly goes in the "return" field?
Nothing currently.
- Is "renewals" an integer count of the number of times this particular loan has been renewed?
Yup
- Should the timestamp be updated every time any record is changed or at record creation?
MySQL does this for you.
- If I wanted statistics of how many loans/renewals were given out on a given day some time ago, could I do this? It appears not.
Yes indeed, using the statistics table EG select * from statistics where type='renew' and datetime > '2006-07-20' and datetime <='2006-07-21' limit 3; +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ | datetime | branch | proccode | value | type | other | usercode | itemnumber | itemtype | borrowernumber | +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ | 2006-07-20 09:34:55 | S | NULL | 0.0000 | renew | | NULL | 121757 | NULL | NULL | | 2006-07-20 09:41:06 | NULL | NULL | 0.0000 | renew | | NULL | 84985 | NULL | NULL | | 2006-07-20 09:41:25 | NULL | NULL | 0.0000 | renew | | NULL | 35666 | NULL | NULL | +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ 3 rows in set (0.01 sec) You can of course add and itemnumber=something as well. So on your import you might like to populate the statistics table also. Hope this helps Chris
Hi Chris, many thanks for the helpful reply. I mostly now just need to understand the output format of the other program. One small follow-up: On Sun, 06 Aug 2006, Chris Cormack wrote:
Gavin McCullagh wrote:
- If I wanted statistics of how many loans/renewals were given out on a given day some time ago, could I do this? It appears not. Yes indeed, using the statistics table
EG select * from statistics where type='renew' and datetime > '2006-07-20' and datetime <='2006-07-21' limit 3; +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ | datetime | branch | proccode | value | type | other | usercode | itemnumber | itemtype | borrowernumber | +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ | 2006-07-20 09:34:55 | S | NULL | 0.0000 | renew | | NULL | 121757 | NULL | NULL | | 2006-07-20 09:41:06 | NULL | NULL | 0.0000 | renew | | NULL | 84985 | NULL | NULL | | 2006-07-20 09:41:25 | NULL | NULL | 0.0000 | renew | | NULL | 35666 | NULL | NULL | +---------------------+--------+----------+--------+-------+-------+----------+------------+----------+----------------+ 3 rows in set (0.01 sec)
Is there a reason that borrowernumber is NULL in all of your "renew" examples? Does Koha leave this blank? If so, why? I am in a position to fill this in correctly during my import. Should I or would I be better off behaving as much like Koha as possible? It seems like a partial set of statistics might be confusing?
So on your import you might like to populate the statistics table also.
Great. I will do this. Gavin PS I'll see if I can write this into the wiki.
On Sat, Aug 12, 2006 at 12:45:49PM +0100, Gavin McCullagh said:
Hi Chris,
many thanks for the helpful reply. I mostly now just need to understand the output format of the other program. One small follow-up:
Is there a reason that borrowernumber is NULL in all of your "renew" examples? Does Koha leave this blank? If so, why? I am in a position to fill this in correctly during my import. Should I or would I be better off behaving as much like Koha as possible? It seems like a partial set of statistics might be confusing?
No harm filling it with the import, there is a subroutine call updatestats() in the renew subroutine. It may be not passing in the borrowernumber, that should be an easy fix. But you can figure out the borrower from the issues line the Statistics table. Having the number would be better though. Chris -- Chris Cormack Programmer 027 4500 789 Katipo Communications Ltd chris@katipo.co.nz www.katipo.co.nz
Gavin, there is a layout of the Koha database structure at the end of the Users Guide (http://www.kohadocs.org/usersguide/), Appendix A. Stephen On 8/12/06, Chris Cormack <chris@katipo.co.nz> wrote:
On Sat, Aug 12, 2006 at 12:45:49PM +0100, Gavin McCullagh said:
Hi Chris,
many thanks for the helpful reply. I mostly now just need to understand the output format of the other program. One small follow-up:
Is there a reason that borrowernumber is NULL in all of your "renew" examples? Does Koha leave this blank? If so, why? I am in a position to fill this in correctly during my import. Should I or would I be better off behaving as much like Koha as possible? It seems like a partial set of statistics might be confusing?
No harm filling it with the import, there is a subroutine call updatestats() in the renew subroutine. It may be not passing in the borrowernumber, that should be an easy fix. But you can figure out the borrower from the issues line the Statistics table. Having the number would be better though.
Chris
-- Chris Cormack Programmer 027 4500 789 Katipo Communications Ltd chris@katipo.co.nz www.katipo.co.nz _______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
participants (3)
-
Chris Cormack -
Gavin McCullagh -
Stephen Hedges