View | Details | Raw Unified | Return to bug 24151
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_24151.perl (+53 lines)
Line 0 Link Here
1
$DBversion = 'XXX'; # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
    unless( TableExists( 'pseudonymized_transactions' ) ) {
4
        $dbh->do(q|
5
            CREATE TABLE `pseudonymized_transactions` (
6
              `id` INT(11) NOT NULL AUTO_INCREMENT,
7
              `hashed_borrowernumber` VARCHAR(60) NOT NULL,
8
              `has_cardnumber` TINYINT(1) NOT NULL DEFAULT 0,
9
              `title` LONGTEXT,
10
              `city` LONGTEXT,
11
              `state` MEDIUMTEXT default NULL,
12
              `zipcode` varchar(25) default NULL,
13
              `country` MEDIUMTEXT,
14
              `branchcode` varchar(10) NOT NULL default '',
15
              `categorycode` varchar(10) NOT NULL default '',
16
              `dateenrolled` date default NULL,
17
              `sex` varchar(1) default NULL,
18
              `sort1` varchar(80) default NULL,
19
              `sort2` varchar(80) default NULL,
20
              `datetime` datetime default NULL,
21
              `transaction_branchcode` varchar(10) default NULL,
22
              `transaction_type` varchar(16) default NULL,
23
              `itemnumber` int(11) default NULL,
24
              `itemtype` varchar(10) default NULL,
25
              `holdingbranch` varchar(10) default null,
26
              `location` varchar(80) default NULL,
27
              `itemcallnumber` varchar(255) default NULL,
28
              `ccode` varchar(80) default NULL,
29
              PRIMARY KEY (`id`),
30
              CONSTRAINT `pseudonymized_transactions_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
31
              CONSTRAINT `pseudonymized_transactions_borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`),
32
              CONSTRAINT `pseudonymized_transactions_borrowers_ibfk_3` FOREIGN KEY (`transaction_branchcode`) REFERENCES `branches` (`branchcode`)
33
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
34
        |);
35
    }
36
37
    $dbh->do(q|
38
        INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type)
39
        VALUES ('Pseudonymization','0',NULL,'If enabled patrons and transactions will be copied in a separate table for statistics purpose','YesNo')
40
    |);
41
    $dbh->do(q|
42
        INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type)
43
        VALUES ('PseudonymizationPatronFields','','title,city,state,zipcode,country,branchcode,categorycode,dateenrolled,sex,sort1,sort2','Patron fields to copy to the pseudonymized_transactions table','multiple')
44
    |);
45
    $dbh->do(q|
46
        INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type)
47
        VALUES ('PseudonymizationTransactionFields','','datetime,transaction_branchcode,transaction_type,itemnumber,itemtype,holdingbranch,location,itemcallnumber,ccode','Transaction fields to copy to the pseudonymized_transactions table','multiple')
48
    |);
49
50
    # Always end with this (adjust the bug info)
51
    SetVersion( $DBversion );
52
    print "Upgrade to $DBversion done (Bug 24151 - Add pseudonymized_transactions tables and sysprefs for Pseudonymization)\n";
53
}
(-)a/installer/data/mysql/kohastructure.sql (+35 lines)
Lines 1884-1889 CREATE TABLE `statistics` ( -- information related to transactions (circulation Link Here
1884
  KEY `ccode_idx` (`ccode`)
1884
  KEY `ccode_idx` (`ccode`)
1885
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1885
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1886
1886
1887
--
1888
-- Table structure for table pseudonymized_transactions
1889
--
1890
1891
DROP TABLE IF EXISTS pseudonymized_transactions;
1892
CREATE TABLE `pseudonymized_transactions` (
1893
  `id` INT(11) NOT NULL AUTO_INCREMENT,
1894
  `hashed_borrowernumber` VARCHAR(60) NOT NULL,
1895
  `has_cardnumber` TINYINT(1) NOT NULL DEFAULT 0,
1896
  `title` LONGTEXT,
1897
  `city` LONGTEXT,
1898
  `state` MEDIUMTEXT default NULL,
1899
  `zipcode` varchar(25) default NULL,
1900
  `country` MEDIUMTEXT,
1901
  `branchcode` varchar(10) NOT NULL default '',
1902
  `categorycode` varchar(10) NOT NULL default '',
1903
  `dateenrolled` date default NULL,
1904
  `sex` varchar(1) default NULL,
1905
  `sort1` varchar(80) default NULL,
1906
  `sort2` varchar(80) default NULL,
1907
  `datetime` datetime default NULL,
1908
  `transaction_branchcode` varchar(10) default NULL,
1909
  `transaction_type` varchar(16) default NULL,
1910
  `itemnumber` int(11) default NULL,
1911
  `itemtype` varchar(10) default NULL,
1912
  `holdingbranch` varchar(10) default null,
1913
  `location` varchar(80) default NULL,
1914
  `itemcallnumber` varchar(255) default NULL,
1915
  `ccode` varchar(80) default NULL,
1916
  PRIMARY KEY (`id`),
1917
  CONSTRAINT `pseudonymized_transactions_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
1918
  CONSTRAINT `pseudonymized_transactions_borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`),
1919
  CONSTRAINT `pseudonymized_transactions_borrowers_ibfk_3` FOREIGN KEY (`transaction_branchcode`) REFERENCES `branches` (`branchcode`)
1920
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1921
1887
--
1922
--
1888
-- Table structure for table subscription_frequencies
1923
-- Table structure for table subscription_frequencies
1889
--
1924
--
(-)a/installer/data/mysql/sysprefs.sql (+3 lines)
Lines 506-511 INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, ` Link Here
506
('PrivacyPolicyURL','',NULL,'This URL is used in messages about GDPR consents.', 'Free'),
506
('PrivacyPolicyURL','',NULL,'This URL is used in messages about GDPR consents.', 'Free'),
507
('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'),
507
('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'),
508
('ProtectSuperlibrarianPrivileges','1',NULL,'If enabled, non-superlibrarians cannot set superlibrarian privileges','YesNo'),
508
('ProtectSuperlibrarianPrivileges','1',NULL,'If enabled, non-superlibrarians cannot set superlibrarian privileges','YesNo'),
509
('Pseudonymization','0',NULL,'If enabled patrons and transactions will be copied in a separate table for statistics purpose','YesNo'),
510
('PseudonymizationPatronFields','','title,city,state,zipcode,country,branchcode,categorycode,dateenrolled,sex,sort1,sort2','Patron fields to copy to the pseudonymized_transactions table','multiple'),
511
('PseudonymizationTransactionFields','','datetime,branchcode,transaction_type,itemnumber,itemtype,holdingbranch,location,itemcallnumber,ccode','Transaction fields to copy to the pseudonymized_transactions table','multiple'),
509
('PurgeSuggestionsOlderThan', '', NULL, 'If this script is called without the days parameter', 'Integer'),
512
('PurgeSuggestionsOlderThan', '', NULL, 'If this script is called without the days parameter', 'Integer'),
510
('QueryAutoTruncate','1',NULL,'If ON, query truncation is enabled by default','YesNo'),
513
('QueryAutoTruncate','1',NULL,'If ON, query truncation is enabled by default','YesNo'),
511
('QueryFuzzy','1',NULL,'If ON, enables fuzzy option for searches','YesNo'),
514
('QueryFuzzy','1',NULL,'If ON, enables fuzzy option for searches','YesNo'),
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref (-1 / +33 lines)
Lines 257-259 Patrons: Link Here
257
         - days and remove anonymized patron accounts after
257
         - days and remove anonymized patron accounts after
258
         - pref: PatronRemovalDelay
258
         - pref: PatronRemovalDelay
259
         - "days.<br>IMPORTANT: No action is performed when these delays are empty (no text). But a zero value ('0') is interpreted as no delay (do it now)! The actions are performed by the cleanup database cron job."
259
         - "days.<br>IMPORTANT: No action is performed when these delays are empty (no text). But a zero value ('0') is interpreted as no delay (do it now)! The actions are performed by the cleanup database cron job."
260
- 
260
     -
261
         - pref: Pseudonymization
262
           choices:
263
               yes: Enable
264
               no: Disable
265
         - "pseudonymization to comply with GDPR for personal information."
266
         - "Patrons and transactions will be copied to a separate table for statistics purpose."
267
         - "<br/>Use the following fields for patrons:"
268
         - pref: PseudonymizationPatronFields
269
           multiple:
270
             title: "Patron's title"
271
             city: "City"
272
             state: "State"
273
             zipcode: "Zipcode"
274
             country: "Country"
275
             branchcode: "Patron's library"
276
             categorycode: "Patron's category"
277
             dateenrolled: "Date the patron was added to Koha"
278
             sex: "Patron's gender"
279
             sort1: "Sort1"
280
             sort2: "Sort2"
281
         - "<br/> And the following fields for the transactions:"
282
         - pref: PseudonymizationTransactionFields
283
           multiple:
284
             datetime: "Date and time of the transaction"
285
             transaction_branchcode: "Library where the transaction occurred"
286
             transaction_type: "Transaction type"
287
             itemnumber: "Itemnumber"
288
             itemtype: "Item type"
289
             holdingbranch: "Holding branch"
290
             location: "Location"
291
             itemcallnumber: "Item's callnumber"
292
             ccode: "Collection code"

Return to bug 24151