Refuse a Second Active Checkout

The schema permits several loan rows for one item because returned rows are valuable history. That also means the schema alone permits two active rows. The checkout operation must refuse the second one.

Open transactions.py. The new checkout_loan(connection, loan) receives one four-value tuple and already has its transaction and insert. Before the insert, run the supplied active-item query with (item_id,) and fetch one optional row.

If a row is present, raise this exact error:

raise ValueError("That item is already checked out.")

Keep the check inside with connection: beside the insert so a refusal exits through the same failure path as an insert error. Returned history does not match because its returned_on value is present.

This program handles one checkout at a time. With the connection settings used here, entering with connection: does not itself start a transaction; the first insert starts it. The earlier read does not reserve the item. Two programs writing at once would need additional protection against both seeing the item as available.

The function should still let database constraints speak for missing parent IDs or a duplicate loan ID. Do not catch those errors or replace them with the active-item message. If no active row matches, execute the static bound insert, let the context commit, and return the explicit loan_id from the tuple.

Press Run. The first fixed call uses item 1, which already has active loan 501, and should show:

ValueError: That item is already checked out.

On a separate database, Sam checks out available items 4 and 5. Both currently succeed because the function protects the item, not the member’s limit. The reopened active count for Sam becomes 3. That visible over-limit result shows the second rule the operation still needs.

Submit tests an active item, an item with only returned history, and a never-loaned item. It passes SQL-looking date text as ordinary data and uses IDs absent from the prompt. It also checks that missing items and members still raise sqlite3.IntegrityError, successful work is visible after reopening, and the supplied connection remains open.