From d2b5b913e3f95e6bcb6032386e35f73f6084fe52 Mon Sep 17 00:00:00 2001 From: Jonathan Druart 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