Bug 24151: DB changes

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 <sonia.bouis@univ-lyon3.fr>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>

diff --git a/installer/data/mysql/atomicupdate/bug_24151.perl b/installer/data/mysql/atomicupdate/bug_24151.perl
new file mode 100644 (file)
index 0000000..6dd1288
--- /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 3c6ef33..537b41a 100644 (file)
--- 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 d67a02b..b0e3017 100644 (file)
--- 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 991de66..1f15cf2 100644 (file)
--- 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."
+         - "<br/>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"
+         - "<br/> 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"