Re: [Koha-devel] [Koha] null itemnumbers and borrowernumbers in issues table
2009/4/11 Joe Atzberger <ohiocore@gmail.com>:
On Fri, Apr 10, 2009 at 11:19 AM, Jeffrey LePage <jeffrey_lepage@yahoo.com> wrote:
Greetings,
We've just discovered 4 records in the issues table that have nulls where there should never be nulls. Specifically, 2 records with null borrowernumber and 2 records with null itemnumber. As a result, there are two overdue books checked out to a NULL patron and two patrons who've checked out NULL books.
I notice that the issues table is constructed thus: mysql> desc issues; +-----------------+-------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+-------------------+-------+ | borrowernumber | int(11) | YES | MUL | NULL | | | itemnumber | int(11) | YES | MUL | NULL | | | date_due | date | YES | | NULL | | | branchcode | varchar(10) | YES | | NULL | | | issuingbranch | varchar(18) | YES | | NULL | | | returndate | date | YES | | NULL | | | lastreneweddate | date | YES | | NULL | | | return | varchar(4) | YES | | NULL | | | renewals | tinyint(4) | YES | | NULL | | | timestamp | timestamp | NO | | CURRENT_TIMESTAMP | | | issuedate | date | YES | | NULL | | +-----------------+-------------+------+-----+-------------------+-------+
Notice that borrowernumber and itemnumber are NULL'able. Isn't this a _bad_ idea? I don't know why/how the null itemnumbers and borrowernumbers got into the database, but it seems that making them non-nullable would be a good idea.
Comments?
This is a known issue, basically a compromise to allow offline circulation to work in 3.0 (where the borrowernumber or itemnumber might not actually exist when the insertions are done). But in short, yes, I agree DB constraints need to be imposed for itemnumber and borrowernumber in issues.
Shifting to the devel list. Shouldn't we store the offline circs in their own table, with nullable fields if they need it. Then reconcile them into the issues table? That way we can put the constraints back on the issues table. Chris
The moment of upload should catch failed insertions, optionally saving them in a secondary table as described, with feedback to the user. Itemnumber really shouldn't be NULLable at all, since there isn't any point tracking the transaction if you don't know what it is. You won't even know what rule to have circ'd it under. The error message will be more useful than the mangled transaction line. --joe On Fri, Apr 10, 2009 at 3:29 PM, Chris Cormack <chris@bigballofwax.co.nz>wrote:
2009/4/11 Joe Atzberger <ohiocore@gmail.com>:
On Fri, Apr 10, 2009 at 11:19 AM, Jeffrey LePage <
jeffrey_lepage@yahoo.com>
wrote:
Greetings,
We've just discovered 4 records in the issues table that have nulls
where
there should never be nulls. Specifically, 2 records with null borrowernumber and 2 records with null itemnumber. As a result, there are two overdue books checked out to a NULL patron and two patrons who've checked out NULL books.
I notice that the issues table is constructed thus: mysql> desc issues;
+-----------------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+-------------------+-------+
| borrowernumber | int(11) | YES | MUL | NULL | | | itemnumber | int(11) | YES | MUL | NULL | | | date_due | date | YES | | NULL | | | branchcode | varchar(10) | YES | | NULL | | | issuingbranch | varchar(18) | YES | | NULL | | | returndate | date | YES | | NULL | | | lastreneweddate | date | YES | | NULL | | | return | varchar(4) | YES | | NULL | | | renewals | tinyint(4) | YES | | NULL | | | timestamp | timestamp | NO | | CURRENT_TIMESTAMP | | | issuedate | date | YES | | NULL | |
+-----------------+-------------+------+-----+-------------------+-------+
Notice that borrowernumber and itemnumber are NULL'able. Isn't this a _bad_ idea? I don't know why/how the null itemnumbers and borrowernumbers got into the database, but it seems that making them non-nullable would be a good idea.
Comments?
This is a known issue, basically a compromise to allow offline circulation to work in 3.0 (where the borrowernumber or itemnumber might not actually exist when the insertions are done). But in short, yes, I agree DB constraints need to be imposed for itemnumber and borrowernumber in issues.
Shifting to the devel list.
Shouldn't we store the offline circs in their own table, with nullable fields if they need it. Then reconcile them into the issues table? That way we can put the constraints back on the issues table.
Chris
participants (2)
-
Chris Cormack -
Joe Atzberger