From f833f91e10495af99af52dc8107b1f259f6a5ef1 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 27 Nov 2019 14:49:17 +0100 Subject: [PATCH] Bug 24151: DB changes - anonymized_borrower_attributes Add a new DB table anonymized_borrower_attributes Sponsored-by: Association KohaLa - https://koha-fr.org/ --- installer/data/mysql/atomicupdate/bug_XXXXX.perl | 18 ++++++++++++++++++ installer/data/mysql/kohastructure.sql | 17 +++++++++++++++++ 2 files changed, 35 insertions(+) diff --git a/installer/data/mysql/atomicupdate/bug_XXXXX.perl b/installer/data/mysql/atomicupdate/bug_XXXXX.perl index 1a9118f9fe..bceb2883b3 100644 --- a/installer/data/mysql/atomicupdate/bug_XXXXX.perl +++ b/installer/data/mysql/atomicupdate/bug_XXXXX.perl @@ -53,6 +53,24 @@ if( CheckVersion( $DBversion ) ) { ('PseudonymizationTransactionFields','','datetime,branchcode,transaction_type,itemnumber,itemtype,holdingbranch,location,itemcallnumber,ccode','Transaction fields to copy to the anonymized_transactions table','multiple'), |); + unless( TableExists( 'anonymized_borrower_attributes' ) ) { + $dbh->do(q| + DROP TABLE IF EXISTS `borrower_attributes`; + CREATE TABLE `borrower_attributes` ( + `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, + `borrowernumber` int(11) NOT NULL, + `code` varchar(10) NOT NULL, + `attribute` varchar(255) default NULL, + KEY `borrowernumber` (`borrowernumber`), + KEY `code_attribute` (`code`, `attribute` (191)), + CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`) + ON DELETE CASCADE ON UPDATE CASCADE + ) 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 0b1cf94bcf..aa6718d3ba 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4489,6 +4489,23 @@ CREATE TABLE return_claims ( CONSTRAINT `rc_resolved_by_ibfk` FOREIGN KEY (`resolved_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- +-- Table structure for table borrower_attributes +-- + +DROP TABLE IF EXISTS anonymized_borrower_attributes; +CREATE TABLE anonymized_borrower_attributes( -- association table between anonymized_borrowers and borrower_attributes + `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, -- Row id field + `hashed_borrowernumber` VARCHAR(60) NOT NULL, -- foreign key from the anonymized_borrowers table, defines which anonymized patron has this attribute + `code` varchar(10) NOT NULL, -- foreign key from the borrower_attribute_types table, defines which custom field this value was entered for + `attribute` varchar(255) default NULL, -- custom patron field value + KEY `hashed_borrowernumber` (`hashed_borrowernumber`), + CONSTRAINT `anonymized_borrower_attributes_ibfk_1` FOREIGN KEY (`hashed_borrowernumber`) REFERENCES `anonymized_borrowers` (`hashed_borrowernumber`) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `anonymized_borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; -- 2.11.0