The pseudonymization process aims to duplicate the transactional circulation data from the statistics table in a way that does not allow borrowers to be identified. The statistics table also includes entries for payments and writeoffs that contain the borrowernumber of the impacted patron. We should also record ILL requests in pseudonymized_transactions when pseudonymization is enabled.
*** This bug has been marked as a duplicate of bug 22513 ***
Created attachment 178356 [details] [review] Bug 37901: Add ILL pseudonymization 1) Apply patches 2) Add a <bcrypt_settings> under <config> in /etc/koha/sites/kohadev/koha.conf.xml: $ <bcrypt_settings>$2a$08$9lmorEKnwQloheaCLFIfje</bcrypt_settings> <!-- taken from Pseudonymization.t --> 3) Restart plack & worker: $ koha-plack --restart kohadev && sudo koha-worker --restart kohadev 4) Enable ILLModule and Pseudonymization system preferences 5) Create an ILL request 6) Confirm the pseudonymized_transaction has been added: $ koha-mysql kohadev $ select * from pseudonymized_transactions; 7) Further testing with more data: Check all options from PseudonymizationPatronFields and PseudonymizationTransactionFields and repeat steps 5) and 6).
Created attachment 178956 [details] [review] Bug 37901: Preparation: [DONT PUSH] automated dbic
Created attachment 178957 [details] [review] Bug 37901: Preparation: db changes
Created attachment 178958 [details] [review] Bug 37901: Preparation: atomic update Rename the table pseudonymized_borrower_attributes to pseudonymized_metadata_values This is so that pseudonymizing other 'extended_attributes' type of data, such as illrequestsattributes, becomes possible.
Created attachment 178959 [details] [review] Bug 37901: Preparation: Update borrower attribute types logic This now conforms to the new pseudonymized_metadata_values instead of pseudonymized_borrower_attributes
Created attachment 178960 [details] [review] Bug 37901: Preparation: Update tests
Created attachment 178961 [details] [review] Bug 37901: Add 'illrequest_id' column to statistics
Created attachment 178962 [details] [review] Bug 37901: [DONT PUSH] dbic
Created attachment 178963 [details] [review] Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics
Created attachment 178964 [details] [review] Bug 37901: Consider illrequest_id when creating When a new PseudonymizedTransaction is created, consider the presence of illrequest_id and act on it accordingly.
Created attachment 178965 [details] [review] Bug 37901: Update Statistic.pm Consider 'ill' as a category and 'ill_request' as an allowed type
Created attachment 178966 [details] [review] Bug 37901: UpdateStats when new ILL request is created
Created attachment 178967 [details] [review] Bug 37901: Add updatedatabase information This warning will display if there are existing reports that contain 'pseudonymized_borrower_attributes', informing that these reports need to be updated
Created attachment 178968 [details] [review] Bug 37901: Add Tests Example reports that can be written for ILL pseudonymized data: --Total of requests of a given request type (article)-- SELECT COUNT(*) AS count FROM pseudonymized_transactions pt WHERE EXISTS ( SELECT 1 FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'type' AND pmv.value = 'article' ); --Add backend info to listing-- SELECT pt.*, (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'type') AS type, (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'backend') AS backend FROM pseudonymized_transactions pt; --List all pseudonymized transactions and respective metadata values-- SELECT pt.*, table1.metadata AS borrower_attributes, table2.metadata AS illrequestattributes FROM pseudonymized_transactions pt LEFT JOIN ( SELECT transaction_id, GROUP_CONCAT(DISTINCT CONCAT(`key`, ':', value)) AS metadata FROM pseudonymized_metadata_values WHERE tablename = 'borrower_attributes' GROUP BY transaction_id ) table1 ON pt.id = table1.transaction_id LEFT JOIN ( SELECT transaction_id, GROUP_CONCAT(DISTINCT CONCAT(`key`, ':', value)) AS metadata FROM pseudonymized_metadata_values WHERE tablename = 'illrequestattributes' GROUP BY transaction_id ) table2 ON pt.id = table2.transaction_id; --- Test plan: 1) Add a <bcrypt_settings> under <config> in /etc/koha/sites/kohadev/koha.conf.xml: $ <bcrypt_settings>$2a$08$9lmorEKnwQloheaCLFIfje</bcrypt_settings> <!-- taken from Pseudonymization.t --> 2) Restart plack & worker: $ koha-plack --restart kohadev && sudo koha-worker --restart kohadev 3) Enable ILLModule and Pseudonymization system preferences 4) Check 'Keep for pseudonymization' at the only existing patron attribute type: http://localhost:8081/cgi-bin/koha/admin/patron-attr-types.pl?op=edit_attribute_type&code=SHOW_BCODE 5) Save. Edit that attribute type for 'koha' patron: http://localhost:8081/cgi-bin/koha/members/memberentry.pl?op=edit_form&destination=circ&borrowernumber=51 6) Scroll down, set a value for 'Show barcode on the summary screen items listings:'. Save. 7) Do a checkout for that 'koha' patron. 8) Confirm the pseudonymized_transaction has been added alongside its metadata (SHOW_BCODE), run report #3 from the examples listed above. This concludes the 'preparation' side of testing, ensuring that borrower_attributes are kept for pseudonymization 9) Create a new ILL request of type 'book'. 10) Run the report from 8) again. Verify the ILL pseudonymized transaction and its related pseudonymized metadata is there. Run tests: prove t/db_dependent/Koha/BackgroundJob/PseudonymizeStatistic.t prove t/db_dependent/Koha/PseudonymizedTransaction.t prove t/db_dependent/Koha/Pseudonymization.t
Created attachment 178980 [details] [review] Bug 37901: Preparation: [DONT PUSH] automated dbic
Created attachment 178981 [details] [review] Bug 37901: Preparation: db changes
Created attachment 178982 [details] [review] Bug 37901: Preparation: atomic update Rename the table pseudonymized_borrower_attributes to pseudonymized_metadata_values This is so that pseudonymizing other 'extended_attributes' type of data, such as illrequestsattributes, becomes possible.
Created attachment 178983 [details] [review] Bug 37901: Preparation: Update borrower attribute types logic This now conforms to the new pseudonymized_metadata_values instead of pseudonymized_borrower_attributes
Created attachment 178984 [details] [review] Bug 37901: Preparation: Update tests
Created attachment 178985 [details] [review] Bug 37901: Add 'illrequest_id' column to statistics
Created attachment 178986 [details] [review] Bug 37901: [DONT PUSH] dbic
Created attachment 178987 [details] [review] Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics
Created attachment 178988 [details] [review] Bug 37901: Consider illrequest_id when creating When a new PseudonymizedTransaction is created, consider the presence of illrequest_id and act on it accordingly.
Created attachment 178989 [details] [review] Bug 37901: Update Statistic.pm Consider 'ill' as a category and 'ill_request' as an allowed type
Created attachment 178990 [details] [review] Bug 37901: UpdateStats after new ILL request is created
Created attachment 178991 [details] [review] Bug 37901: Add updatedatabase information This warning will display if there are existing reports that contain 'pseudonymized_borrower_attributes', informing that these reports need to be updated
Created attachment 178992 [details] [review] Bug 37901: Add Tests Example reports that can be written for ILL pseudonymized data: --Total of requests of a given request type (article)-- SELECT COUNT(*) AS count FROM pseudonymized_transactions pt WHERE EXISTS ( SELECT 1 FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'type' AND pmv.value = 'article' ); --Add backend info to listing-- SELECT pt.*, (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'type') AS type, (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'backend') AS backend FROM pseudonymized_transactions pt; --List all pseudonymized transactions and respective metadata values-- SELECT pt.*, table1.metadata AS borrower_attributes, table2.metadata AS illrequestattributes FROM pseudonymized_transactions pt LEFT JOIN ( SELECT transaction_id, GROUP_CONCAT(DISTINCT CONCAT(`key`, ':', value)) AS metadata FROM pseudonymized_metadata_values WHERE tablename = 'borrower_attributes' GROUP BY transaction_id ) table1 ON pt.id = table1.transaction_id LEFT JOIN ( SELECT transaction_id, GROUP_CONCAT(DISTINCT CONCAT(`key`, ':', value)) AS metadata FROM pseudonymized_metadata_values WHERE tablename = 'illrequestattributes' GROUP BY transaction_id ) table2 ON pt.id = table2.transaction_id; --- Test plan: 1) Add a <bcrypt_settings> under <config> in /etc/koha/sites/kohadev/koha.conf.xml: $ <bcrypt_settings>$2a$08$9lmorEKnwQloheaCLFIfje</bcrypt_settings> <!-- taken from Pseudonymization.t --> 2) Restart plack & worker: $ koha-plack --restart kohadev && sudo koha-worker --restart kohadev 3) Enable ILLModule and Pseudonymization system preferences 4) Check 'Keep for pseudonymization' at the only existing patron attribute type: http://localhost:8081/cgi-bin/koha/admin/patron-attr-types.pl?op=edit_attribute_type&code=SHOW_BCODE 5) Save. Edit that attribute type for 'koha' patron: http://localhost:8081/cgi-bin/koha/members/memberentry.pl?op=edit_form&destination=circ&borrowernumber=51 6) Scroll down, set a value for 'Show barcode on the summary screen items listings:'. Save. 7) Do a checkout for that 'koha' patron. 8) Confirm the pseudonymized_transaction has been added alongside its metadata (SHOW_BCODE), run report #3 from the examples listed above. This concludes the 'preparation' side of testing, ensuring that borrower_attributes are kept for pseudonymization 9) Create a new ILL request of type 'book'. 10) Run the report from 8) again. Verify the ILL pseudonymized transaction and its related pseudonymized metadata is there. Run tests: prove t/db_dependent/Koha/BackgroundJob/PseudonymizeStatistic.t prove t/db_dependent/Koha/PseudonymizedTransaction.t prove t/db_dependent/Koha/Pseudonymization.t
Hi Pedro, Is the test missing a step between 9 and 10? I created the ILL request for patron 42 and I still only have one entry (the one for the checkout) in the result for the report. Was I supposed to do something other than simply create the request? Also, the name of the file in step 1 is koha-conf.xml, not koha.conf.xml, if you want to correct it in the test plan. Thanks! Caroline
Created attachment 181008 [details] [review] Bug 37901: Preparation: [DONT PUSH] automated dbic
Created attachment 181009 [details] [review] Bug 37901: Preparation: db changes
Created attachment 181010 [details] [review] Bug 37901: Preparation: atomic update Rename the table pseudonymized_borrower_attributes to pseudonymized_metadata_values This is so that pseudonymizing other 'extended_attributes' type of data, such as illrequestsattributes, becomes possible.
Created attachment 181011 [details] [review] Bug 37901: Preparation: Update borrower attribute types logic This now conforms to the new pseudonymized_metadata_values instead of pseudonymized_borrower_attributes
Created attachment 181012 [details] [review] Bug 37901: Preparation: Update tests
Created attachment 181013 [details] [review] Bug 37901: Add 'illrequest_id' column to statistics
Created attachment 181014 [details] [review] Bug 37901: [DONT PUSH] dbic
Created attachment 181015 [details] [review] Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics
Created attachment 181016 [details] [review] Bug 37901: Consider illrequest_id when creating or completing When a new PseudonymizedTransaction is created, consider the presence of illrequest_id and act on it accordingly.
Created attachment 181017 [details] [review] Bug 37901: Update Statistic.pm Consider 'ill' as a category and 'illreq_created' and 'illreq_comp' as allowed types
Created attachment 181018 [details] [review] Bug 37901: UpdateStats after new ILL request is created or completed
Created attachment 181019 [details] [review] Bug 37901: Add updatedatabase information This warning will display if there are existing reports that contain 'pseudonymized_borrower_attributes', informing that these reports need to be updated
Created attachment 181020 [details] [review] Bug 37901: Add Tests Example reports that can be written for ILL pseudonymized data: --Total of requests of a given request type (article)-- SELECT COUNT(*) AS count FROM pseudonymized_transactions pt WHERE EXISTS ( SELECT 1 FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'type' AND pmv.value = 'article' ); --Add backend info to listing-- SELECT pt.*, (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'type') AS type, (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'backend') AS backend FROM pseudonymized_transactions pt; --List all pseudonymized transactions and respective metadata values-- SELECT pt.*, table1.metadata AS borrower_attributes, table2.metadata AS illrequestattributes FROM pseudonymized_transactions pt LEFT JOIN ( SELECT transaction_id, GROUP_CONCAT(DISTINCT CONCAT(`key`, ':', value)) AS metadata FROM pseudonymized_metadata_values WHERE tablename = 'borrower_attributes' GROUP BY transaction_id ) table1 ON pt.id = table1.transaction_id LEFT JOIN ( SELECT transaction_id, GROUP_CONCAT(DISTINCT CONCAT(`key`, ':', value)) AS metadata FROM pseudonymized_metadata_values WHERE tablename = 'illrequestattributes' GROUP BY transaction_id ) table2 ON pt.id = table2.transaction_id; --- Test plan: 1) Add a <bcrypt_settings> under <config> in /etc/koha/sites/kohadev/koha.conf.xml: $ <bcrypt_settings>$2a$08$9lmorEKnwQloheaCLFIfje</bcrypt_settings> <!-- taken from Pseudonymization.t --> 2) Restart plack & worker: $ koha-plack --restart kohadev && sudo koha-worker --restart kohadev 3) Enable ILLModule and Pseudonymization system preferences 4) Check 'Keep for pseudonymization' at the only existing patron attribute type: http://localhost:8081/cgi-bin/koha/admin/patron-attr-types.pl?op=edit_attribute_type&code=SHOW_BCODE 5) Save. Edit that attribute type for 'koha' patron: http://localhost:8081/cgi-bin/koha/members/memberentry.pl?op=edit_form&destination=circ&borrowernumber=51 6) Scroll down, set a value for 'Show barcode on the summary screen items listings:'. Save. 7) Do a checkout for that 'koha' patron. 8) Confirm the pseudonymized_transaction has been added alongside its metadata (SHOW_BCODE), run report #3 from the examples listed above. This concludes the 'preparation' side of testing, ensuring that borrower_attributes are kept for pseudonymization 9) Create a new ILL request of type 'book'. 10) Run the report from 8) again. Verify the ILL pseudonymized transaction and its related pseudonymized metadata is there. 11) Go back to that ILL request and click 'Confirm request'. After that click 'Mark completed'. 12) Repeat 10) Run tests: prove t/db_dependent/Koha/BackgroundJob/PseudonymizeStatistic.t prove t/db_dependent/Koha/PseudonymizedTransaction.t prove t/db_dependent/Koha/Pseudonymization.t Sponsored-by: UKHSA - UK Health Security Agency