From 601c29f5def899d167946faa6c7eac49632f8eba Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 8 Jul 2014 09:50:08 -0400 Subject: [PATCH] [PASSED QA] Bug 1861 - Unique patrons logins not (totally) enforced The Koha 3.0 patron admin page will prevent you from creating a user who has an identical OPAC login as someone else (though I would change the error message from "Login/password already exists." to "Login already exists.", since it's not the password that's the problem). However, there's nothing enforced in the MySQL database to prevent patrons from being imported or manually inserted with duplicate logins. Would it be better to change the MySQL spec from this: KEY `userid` (`userid`) to this: UNIQUE KEY `userid` (`userid`) Test plan: 1) Enter mysql console 2) Attempt to create two borrowers with identical user id's 3) Note you are able to do so 4) Delete these bororwers 5) Apply this patch 6) Run updatedatabase.pl 7) Repeat step 2 8) Note you are now unable to do so Signed-off-by: Bernardo Gonzalez Kriegel Work as described, no koha-qa errors Signed-off-by: Katrin Fischer Leaves deletedborrowers as it is, deleting patrons still works ok. Works according to test plan. Database update will only be successful, when no duplicate entries exist in the database. --- installer/data/mysql/kohastructure.sql | 2 +- installer/data/mysql/updatedatabase.pl | 18 ++++++++++++++++++ 2 files changed, 19 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index b46dc6a..e146d4a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -269,7 +269,7 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons PRIMARY KEY `borrowernumber` (`borrowernumber`), KEY `categorycode` (`categorycode`), KEY `branchcode` (`branchcode`), - KEY `userid` (`userid`), + UNIQUE KEY `userid` (`userid`), KEY `guarantorid` (`guarantorid`), KEY `surname_idx` (`surname`(255)), KEY `firstname_idx` (`firstname`(255)), diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 3f1af57..84a1b4d 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8600,6 +8600,24 @@ $DBversion = "3.17.00.013"; if ( CheckVersion($DBversion) ) { $dbh->do("INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('maxreserves',50,'System-wide maximum number of holds a patron can place','','Integer')"); print "Upgrade to $DBversion done (Re-add system preference maxreserves)\n"; + SetVersion ($DBversion); +} + + + +$DBversion = "3.17.00.XXX"; +if ( CheckVersion($DBversion) ) { + $dbh->do(q{ + UPDATE borrowers SET userid = NULL where userid = "" + }); + + $dbh->do(q{ + ALTER TABLE borrowers + DROP INDEX userid , + ADD UNIQUE userid (userid) + }); + + print "Upgrade to $DBversion done (Bug 1861 - Unique patrons logins not (totally) enforced)\n"; SetVersion($DBversion); } -- 1.9.1