[Koha-devel] [Koha] null itemnumbers and borrowernumbers in issues table

[Joe Atzberger]

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 at bigballofwax.co.nz>wrote:

> 2009/4/11 Joe Atzberger <ohiocore at gmail.com>:
> >
> >
> > On Fri, Apr 10, 2009 at 11:19 AM, Jeffrey LePage <
> jeffrey_lepage at 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </pipermail/koha-devel/attachments/20090410/5ae41931/attachment-0002.htm>