Bugzilla – Attachment 95959 Details for
Bug 24151
Add a pseudonymization process for patrons and transactions
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 24151: DB Changes
Bug-24151-DB-Changes.patch (text/plain), 6.55 KB, created by
Jonathan Druart
on 2019-12-03 18:11:07 UTC
(
hide
)
Description:
Bug 24151: DB Changes
Filename:
MIME Type:
Creator:
Jonathan Druart
Created:
2019-12-03 18:11:07 UTC
Size:
6.55 KB
patch
obsolete
>From d2b5b913e3f95e6bcb6032386e35f73f6084fe52 Mon Sep 17 00:00:00 2001 >From: Jonathan Druart <jonathan.druart@bugs.koha-community.org> >Date: Fri, 22 Nov 2019 15:26:01 +0100 >Subject: [PATCH] Bug 24151: DB Changes > >Add tables anonymized_borrowers and anonymized_transactions > >Sponsored-by: Association KohaLa - https://koha-fr.org/ >--- > installer/data/mysql/atomicupdate/bug_XXXXX.perl | 46 ++++++++++++++++++++++++ > installer/data/mysql/kohastructure.sql | 43 ++++++++++++++++++++++ > 2 files changed, 89 insertions(+) > create mode 100644 installer/data/mysql/atomicupdate/bug_XXXXX.perl > >diff --git a/installer/data/mysql/atomicupdate/bug_XXXXX.perl b/installer/data/mysql/atomicupdate/bug_XXXXX.perl >new file mode 100644 >index 0000000000..0704ce0af9 >--- /dev/null >+++ b/installer/data/mysql/atomicupdate/bug_XXXXX.perl >@@ -0,0 +1,46 @@ >+$DBversion = 'XXX'; # will be replaced by the RM >+if( CheckVersion( $DBversion ) ) { >+ unless( TableExists( 'anonymized_borrowers' ) ) { >+ $dbh->do(q| >+ CREATE TABLE `anonymized_borrowers` ( >+ `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, >+ PRIMARY KEY `hashed_borrowernumber` (`hashed_borrowernumber`), >+ CONSTRAINT `anon_borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), >+ CONSTRAINT `anon_borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) >+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; >+ |); >+ } >+ unless( TableExists( 'anonymized_transactions' ) ) { >+ $dbh->do(q| >+ CREATE TABLE `anonymized_transactions` ( >+ `hashed_borrowernumber` VARCHAR(60) NOT NULL, >+ `datetime` datetime default NULL, >+ `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, >+ CONSTRAINT `anonymized_transactions_ibfk_1` FOREIGN KEY (`hashed_borrowernumber`) REFERENCES `anonymized_borrowers` (`hashed_borrowernumber`) >+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; >+ |); >+ } >+ >+ # Always end with this (adjust the bug info) >+ SetVersion( $DBversion ); >+ print "Upgrade to $DBversion done (Bug XXXXX - description)\n"; >+} >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index 67495ab962..0b1cf94bcf 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -1597,6 +1597,49 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons > ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; > > -- >+-- Table structure for table `anonymized_borrowers` >+-- >+ >+DROP TABLE IF EXISTS `anonymized_borrowers`; >+CREATE TABLE `anonymized_borrowers` ( >+ `hashed_borrowernumber` VARCHAR(60) NOT NULL, >+ `has_cardnumber` TINYINT(1) NOT NULL DEFAULT 0, -- if the patron has a cardnumber (boolean field) >+ `title` LONGTEXT, -- patron/borrower's title, for example: Mr. or Mrs. >+ `city` LONGTEXT, -- the city or town for your patron/borrower's primary address >+ `state` MEDIUMTEXT default NULL, -- the state or province for your patron/borrower's primary address >+ `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address >+ `country` MEDIUMTEXT, -- the country for your patron/borrower's primary address >+ `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch >+ `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category >+ `dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD) >+ `sex` varchar(1) default NULL, -- patron/borrower's gender >+ `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library >+ `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library >+ PRIMARY KEY `hashed_borrowernumber` (`hashed_borrowernumber`), >+ CONSTRAINT `anon_borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), >+ CONSTRAINT `anon_borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; >+ >+-- >+-- Table structure for table `anonymized_transactions` >+-- >+ >+DROP TABLE IF EXISTS `anonymized_transactions`; >+CREATE TABLE `anonymized_transactions` ( >+ `hashed_borrowernumber` VARCHAR(60) NOT NULL, >+ `datetime` datetime default NULL, -- date and time of the transaction >+ `branchcode` varchar(10) default NULL, -- foreign key, branch where the transaction occurred >+ `transaction_type` varchar(16) default NULL, -- transaction type (localuse, issue, return, renew, writeoff, payment) >+ `itemnumber` int(11) default NULL, -- foreign key from the items table, links transaction to a specific item >+ `itemtype` varchar(10) default NULL, -- foreign key from the itemtypes table, links transaction to a specific item type >+ `holdingbranch` varchar(10) default null, >+ `location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c) >+ `itemcallnumber` varchar(255) default NULL, -- item's itemcallnumber >+ `ccode` varchar(80) default NULL, -- foreign key from the items table, links transaction to a specific collection code >+ CONSTRAINT `anonymized_transactions_ibfk_1` FOREIGN KEY (`hashed_borrowernumber`) REFERENCES `anonymized_borrowers` (`hashed_borrowernumber`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; >+ >+-- > -- Table structure for table `borrower_attributes` > -- > >-- >2.11.0
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 24151
:
95959
|
95960
|
95961
|
95962
|
95963
|
95964
|
95965
|
95966
|
95967
|
95968
|
95969
|
95970
|
95971
|
95972
|
95973
|
95974
|
95975
|
95976
|
97240
|
97241
|
97242
|
97243
|
97244
|
97245
|
97246
|
97247
|
97248
|
97249
|
97250
|
97251
|
97252
|
101034
|
101035
|
101036
|
101037
|
101038
|
101039
|
101040
|
101041
|
101042
|
101043
|
101044
|
101045
|
101046
|
101047
|
101048
|
101049
|
105801
|
105802
|
105803
|
105804
|
105805
|
105806
|
105807
|
105808
|
105809
|
105810
|
105811
|
105812
|
105813
|
105814
|
105815
|
105816
|
106032
|
106033
|
106034
|
106035
|
106036
|
106037
|
106038
|
106039
|
106040
|
106041
|
106042
|
106043
|
106044
|
106045
|
106046
|
106047
|
106048
|
106049
|
106195
|
106255
|
107127
|
107225