From 12c8ebfe2d5214e26ff497898c580781dc25344c Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 22 Nov 2019 15:38:34 +0100 Subject: [PATCH] Bug 24151: DB changes Content-Type: text/plain; charset=utf-8 1 new table: * pseudonymized_transactions 3 new sysprefs: * Pseudonymization * PseudonymizationPatronFields * PseudonymizationTransactionFields Sponsored-by: Association KohaLa - https://koha-fr.org/ Signed-off-by: Signed-off-by: Sonia Bouis Signed-off-by: Marcel de Rooy --- installer/data/mysql/atomicupdate/bug_24151.perl | 53 ++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 35 ++++++++++++++ installer/data/mysql/sysprefs.sql | 3 ++ .../prog/en/modules/admin/preferences/patrons.pref | 33 ++++++++++++++ 4 files changed, 124 insertions(+) create mode 100644 installer/data/mysql/atomicupdate/bug_24151.perl diff --git a/installer/data/mysql/atomicupdate/bug_24151.perl b/installer/data/mysql/atomicupdate/bug_24151.perl new file mode 100644 index 0000000000..6dd12888f6 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_24151.perl @@ -0,0 +1,53 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + unless( TableExists( 'pseudonymized_transactions' ) ) { + $dbh->do(q| + CREATE TABLE `pseudonymized_transactions` ( + `id` INT(11) NOT NULL AUTO_INCREMENT, + `hashed_borrowernumber` VARCHAR(60) NOT NULL, + `has_cardnumber` TINYINT(1) NOT NULL DEFAULT 0, + `title` LONGTEXT, + `city` LONGTEXT, + `state` MEDIUMTEXT default NULL, + `zipcode` varchar(25) default NULL, + `country` MEDIUMTEXT, + `branchcode` varchar(10) NOT NULL default '', + `categorycode` varchar(10) NOT NULL default '', + `dateenrolled` date default NULL, + `sex` varchar(1) default NULL, + `sort1` varchar(80) default NULL, + `sort2` varchar(80) default NULL, + `datetime` datetime default NULL, + `transaction_branchcode` varchar(10) default NULL, + `transaction_type` varchar(16) default NULL, + `itemnumber` int(11) default NULL, + `itemtype` varchar(10) default NULL, + `holdingbranch` varchar(10) default null, + `location` varchar(80) default NULL, + `itemcallnumber` varchar(255) default NULL, + `ccode` varchar(80) default NULL, + PRIMARY KEY (`id`), + CONSTRAINT `pseudonymized_transactions_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), + CONSTRAINT `pseudonymized_transactions_borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`), + CONSTRAINT `pseudonymized_transactions_borrowers_ibfk_3` FOREIGN KEY (`transaction_branchcode`) REFERENCES `branches` (`branchcode`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + |); + } + + $dbh->do(q| + INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) + VALUES ('Pseudonymization','0',NULL,'If enabled patrons and transactions will be copied in a separate table for statistics purpose','YesNo') + |); + $dbh->do(q| + INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) + VALUES ('PseudonymizationPatronFields','','title,city,state,zipcode,country,branchcode,categorycode,dateenrolled,sex,sort1,sort2','Patron fields to copy to the pseudonymized_transactions table','multiple') + |); + $dbh->do(q| + INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) + VALUES ('PseudonymizationTransactionFields','','datetime,transaction_branchcode,transaction_type,itemnumber,itemtype,holdingbranch,location,itemcallnumber,ccode','Transaction fields to copy to the pseudonymized_transactions table','multiple') + |); + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 24151 - Add pseudonymized_transactions tables and sysprefs for Pseudonymization)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index c5a3879662..7a396bddc4 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1881,6 +1881,41 @@ CREATE TABLE `statistics` ( -- information related to transactions (circulation ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- +-- Table structure for table pseudonymized_transactions +-- + +DROP TABLE IF EXISTS pseudonymized_transactions; +CREATE TABLE `pseudonymized_transactions` ( + `id` INT(11) NOT NULL AUTO_INCREMENT, + `hashed_borrowernumber` VARCHAR(60) NOT NULL, + `has_cardnumber` TINYINT(1) NOT NULL DEFAULT 0, + `title` LONGTEXT, + `city` LONGTEXT, + `state` MEDIUMTEXT default NULL, + `zipcode` varchar(25) default NULL, + `country` MEDIUMTEXT, + `branchcode` varchar(10) NOT NULL default '', + `categorycode` varchar(10) NOT NULL default '', + `dateenrolled` date default NULL, + `sex` varchar(1) default NULL, + `sort1` varchar(80) default NULL, + `sort2` varchar(80) default NULL, + `datetime` datetime default NULL, + `transaction_branchcode` varchar(10) default NULL, + `transaction_type` varchar(16) default NULL, + `itemnumber` int(11) default NULL, + `itemtype` varchar(10) default NULL, + `holdingbranch` varchar(10) default null, + `location` varchar(80) default NULL, + `itemcallnumber` varchar(255) default NULL, + `ccode` varchar(80) default NULL, + PRIMARY KEY (`id`), + CONSTRAINT `pseudonymized_transactions_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), + CONSTRAINT `pseudonymized_transactions_borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`), + CONSTRAINT `pseudonymized_transactions_borrowers_ibfk_3` FOREIGN KEY (`transaction_branchcode`) REFERENCES `branches` (`branchcode`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- -- Table structure for table subscription_frequencies -- diff --git a/installer/data/mysql/sysprefs.sql b/installer/data/mysql/sysprefs.sql index 4ad73ef827..7816a5e834 100644 --- a/installer/data/mysql/sysprefs.sql +++ b/installer/data/mysql/sysprefs.sql @@ -514,6 +514,9 @@ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, ` ('PrivacyPolicyURL','',NULL,'This URL is used in messages about GDPR consents.', 'Free'), ('ProcessingFeeNote', '', NULL, 'Set the text to be recorded in the column note, table accountlines when the processing fee (defined in item type) is applied', 'textarea'), ('ProtectSuperlibrarianPrivileges','1',NULL,'If enabled, non-superlibrarians cannot set superlibrarian privileges','YesNo'), +('Pseudonymization','0',NULL,'If enabled patrons and transactions will be copied in a separate table for statistics purpose','YesNo'), +('PseudonymizationPatronFields','','title,city,state,zipcode,country,branchcode,categorycode,dateenrolled,sex,sort1,sort2','Patron fields to copy to the pseudonymized_transactions table','multiple'), +('PseudonymizationTransactionFields','','datetime,branchcode,transaction_type,itemnumber,itemtype,holdingbranch,location,itemcallnumber,ccode','Transaction fields to copy to the pseudonymized_transactions table','multiple'), ('PurgeSuggestionsOlderThan', '', NULL, 'If this script is called without the days parameter', 'Integer'), ('QueryAutoTruncate','1',NULL,'If ON, query truncation is enabled by default','YesNo'), ('QueryFuzzy','1',NULL,'If ON, enables fuzzy option for searches','YesNo'), diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref index 991de6668a..1f15cf2cf7 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref @@ -321,3 +321,36 @@ Patrons: - pref: FailedLoginAttempts class: integer - failed login attempts. + - + - pref: Pseudonymization + choices: + yes: Enable + no: Disable + - "pseudonymization to comply with GDPR for personal information." + - "Patrons and transactions will be copied to a separate table for statistics purpose." + - "
Use the following fields for patrons:" + - pref: PseudonymizationPatronFields + multiple: + title: "Patron's title" + city: "City" + state: "State" + zipcode: "Zipcode" + country: "Country" + branchcode: "Patron's library" + categorycode: "Patron's category" + dateenrolled: "Date the patron was added to Koha" + sex: "Patron's gender" + sort1: "Sort1" + sort2: "Sort2" + - "
And the following fields for the transactions:" + - pref: PseudonymizationTransactionFields + multiple: + datetime: "Date and time of the transaction" + transaction_branchcode: "Library where the transaction occurred" + transaction_type: "Transaction type" + itemnumber: "Itemnumber" + itemtype: "Item type" + holdingbranch: "Holding branch" + location: "Location" + itemcallnumber: "Item's callnumber" + ccode: "Collection code" -- 2.11.0