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 1881-1886 CREATE TABLE `statistics` ( -- information related to transactions (circulation Link Here
1881
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1881
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1882
1882
1883
--
1883
--
1884
-- Table structure for table pseudonymized_transactions
1885
--
1886
1887
DROP TABLE IF EXISTS pseudonymized_transactions;
1888
CREATE TABLE `pseudonymized_transactions` (
1889
  `id` INT(11) NOT NULL AUTO_INCREMENT,
1890
  `hashed_borrowernumber` VARCHAR(60) NOT NULL,
1891
  `has_cardnumber` TINYINT(1) NOT NULL DEFAULT 0,
1892
  `title` LONGTEXT,
1893
  `city` LONGTEXT,
1894
  `state` MEDIUMTEXT default NULL,
1895
  `zipcode` varchar(25) default NULL,
1896
  `country` MEDIUMTEXT,
1897
  `branchcode` varchar(10) NOT NULL default '',
1898
  `categorycode` varchar(10) NOT NULL default '',
1899
  `dateenrolled` date default NULL,
1900
  `sex` varchar(1) default NULL,
1901
  `sort1` varchar(80) default NULL,
1902
  `sort2` varchar(80) default NULL,
1903
  `datetime` datetime default NULL,
1904
  `transaction_branchcode` varchar(10) default NULL,
1905
  `transaction_type` varchar(16) default NULL,
1906
  `itemnumber` int(11) default NULL,
1907
  `itemtype` varchar(10) default NULL,
1908
  `holdingbranch` varchar(10) default null,
1909
  `location` varchar(80) default NULL,
1910
  `itemcallnumber` varchar(255) default NULL,
1911
  `ccode` varchar(80) default NULL,
1912
  PRIMARY KEY (`id`),
1913
  CONSTRAINT `pseudonymized_transactions_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
1914
  CONSTRAINT `pseudonymized_transactions_borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`),
1915
  CONSTRAINT `pseudonymized_transactions_borrowers_ibfk_3` FOREIGN KEY (`transaction_branchcode`) REFERENCES `branches` (`branchcode`)
1916
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1917
1918
--
1884
-- Table structure for table subscription_frequencies
1919
-- Table structure for table subscription_frequencies
1885
--
1920
--
1886
1921
(-)a/installer/data/mysql/sysprefs.sql (+3 lines)
Lines 514-519 INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, ` Link Here
514
('PrivacyPolicyURL','',NULL,'This URL is used in messages about GDPR consents.', 'Free'),
514
('PrivacyPolicyURL','',NULL,'This URL is used in messages about GDPR consents.', 'Free'),
515
('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'),
515
('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'),
516
('ProtectSuperlibrarianPrivileges','1',NULL,'If enabled, non-superlibrarians cannot set superlibrarian privileges','YesNo'),
516
('ProtectSuperlibrarianPrivileges','1',NULL,'If enabled, non-superlibrarians cannot set superlibrarian privileges','YesNo'),
517
('Pseudonymization','0',NULL,'If enabled patrons and transactions will be copied in a separate table for statistics purpose','YesNo'),
518
('PseudonymizationPatronFields','','title,city,state,zipcode,country,branchcode,categorycode,dateenrolled,sex,sort1,sort2','Patron fields to copy to the pseudonymized_transactions table','multiple'),
519
('PseudonymizationTransactionFields','','datetime,branchcode,transaction_type,itemnumber,itemtype,holdingbranch,location,itemcallnumber,ccode','Transaction fields to copy to the pseudonymized_transactions table','multiple'),
517
('PurgeSuggestionsOlderThan', '', NULL, 'If this script is called without the days parameter', 'Integer'),
520
('PurgeSuggestionsOlderThan', '', NULL, 'If this script is called without the days parameter', 'Integer'),
518
('QueryAutoTruncate','1',NULL,'If ON, query truncation is enabled by default','YesNo'),
521
('QueryAutoTruncate','1',NULL,'If ON, query truncation is enabled by default','YesNo'),
519
('QueryFuzzy','1',NULL,'If ON, enables fuzzy option for searches','YesNo'),
522
('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 321-323 Patrons: Link Here
321
         - pref: FailedLoginAttempts
321
         - pref: FailedLoginAttempts
322
           class: integer
322
           class: integer
323
         - failed login attempts.
323
         - failed login attempts.
324
- 
324
     -
325
         - pref: Pseudonymization
326
           choices:
327
               yes: Enable
328
               no: Disable
329
         - "pseudonymization to comply with GDPR for personal information."
330
         - "Patrons and transactions will be copied to a separate table for statistics purpose."
331
         - "<br/>Use the following fields for patrons:"
332
         - pref: PseudonymizationPatronFields
333
           multiple:
334
             title: "Patron's title"
335
             city: "City"
336
             state: "State"
337
             zipcode: "Zipcode"
338
             country: "Country"
339
             branchcode: "Patron's library"
340
             categorycode: "Patron's category"
341
             dateenrolled: "Date the patron was added to Koha"
342
             sex: "Patron's gender"
343
             sort1: "Sort1"
344
             sort2: "Sort2"
345
         - "<br/> And the following fields for the transactions:"
346
         - pref: PseudonymizationTransactionFields
347
           multiple:
348
             datetime: "Date and time of the transaction"
349
             transaction_branchcode: "Library where the transaction occurred"
350
             transaction_type: "Transaction type"
351
             itemnumber: "Itemnumber"
352
             itemtype: "Item type"
353
             holdingbranch: "Holding branch"
354
             location: "Location"
355
             itemcallnumber: "Item's callnumber"
356
             ccode: "Collection code"

Return to bug 24151