[Koha-bugs] [Bug 18003] New: Should deletedborrowers.borrowernumber be a primary key?

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18003

            Bug ID: 18003
           Summary: Should deletedborrowers.borrowernumber be a primary
                    key?
 Change sponsored?: ---
           Product: Koha
           Version: unspecified
          Hardware: All
                OS: All
            Status: NEW
          Severity: enhancement
          Priority: P5 - low
         Component: Architecture, internals, and plumbing
          Assignee: gmcharlt at gmail.com
          Reporter: jonathan.druart at bugs.koha-community.org
        QA Contact: testopia at bugs.koha-community.org
        Depends on: 17782

(Raised from bug 17782)

DBIC needs a primary key to work correctly.
We could be tempt to make borrowernumber a primary key, but a use case could
bring thing. Indeed if a table is empty and the mysql (mariadb behaves
identically) server restarted, the Auto Increment (AI) is reset.

Problematic use case:
1. Install Koha
2. Create a patron (borrowers.borrowernumber = 1)
3. Delete it (deletedborrowers.borrowernumer = 1, and AI is set to 2
4. Restart mysql
=> borrowers is empty so AI is reset
5. Create a patron (borrowers.borrowernumber = 1)
6. Delete it
=> We will try to insert a new row with an existing borrowernumber (1) into the
deletedborrowers table: BOOM

Solution 1:
Do not care about this problematic use case and prevent to delete the last
patron.

Solution 2:
Add a AI primary key (id) to deletedborrowers
=> The 2 tables borrowers and deletedborrowers will differ, bugs will be
expected

Solution 3:
Deal with that at code level: when a patron is created, guess the
borrowernumber it will get and check if it does not already exist in the
deletedborrowers table. If so, force it before inserting it

Solution 4:
Do not enforce this constraint at DB level but set the primary key to DBIC
schema:
  Koha::Schema::Result::Deletedborrower:
__PACKAGE__->set_primary_key("borrowernumber");
Problem:
  Koha::Database->new->schema->resultset('Deletedborrower')->find(42);
will raise a warning "DBIx::Class::Storage::DBI::select_single(): Query
returned more than one row.  SQL that returns multiple rows is DEPRECATED for
->find and ->single" if 42 is duplicated, and the first matching row will be
picked

Another solution?


Referenced Bugs:

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17782
[Bug 17782] Patron updated_on field should be set to current timestamp when
borrower is deleted
-- 
You are receiving this mail because:
You are watching all bug changes.